SQL Snippets

Some random commands I always forget when I need them

It isn’t 100% necessary to put your SQL commands in caps. It is, however, considered best practice as it helps differentiate between what are commands and what are arguments.

MySQL

Working with users

• Create a DB: CREATE DATABASE database_name;
• Create a user: CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';
• Grant user permissions: GRANT ALL ON database_name.* TO 'user_name'@'localhost' IDENTIFIED BY 'password';
• List all users: SELECT User FROM mysql.user;

Obviously you should change password to an actually secure password, user_name to something identifiable, and the same for database_name. Even if it’s one user per database, it’s better to use different names for each one.

Working with databases

• List all databases: SHOW DATABASES;
• Select a database to interact with: USE database;
• List tables: SHOW TABLES;
• Show contents of a table: SELECT * FROM table;
• Delete a row: DELETE FROM table WHERE column=value;
• Dumping databases for backups: mysqldump dbname > dbname_$(date -u -Iseconds).sql (run as shell command)
  • Dumps with compression: mysqldump dbname | gzip -c - > /root/dumps/dbname_$(date -u -Iseconds).sql.gz
• Restore database from backup: mysql dbname < backup.sql
  • With compression: gunzip backup.sql.gz then run command above

Postgres

• List databases: \l
• Create user: CREATE ROLE app_user WITH LOGIN ENCRYPTED PASSWORD 'password';
• Create database: CREATE DATABASE app WITH OWNER app_user;
• Modify database owner: ALTER DATABASE app OWNER TO new_app_user;
• Connect/use database: \c <database>

Upgrading

Looks like a lot of work but it's not bad. This is just every little step.

• Install new version
• Check the config files for differences and selectively modify new version to match old
  • Configs are at /etc/postgresql/VER/main/postgresql.conf and …/main/pg_hba.conf
• Stop the postgresql systemd service
  • systemctl stop postgresql
• sudo su - postgres
• Check the clusters with the following (doesn't actually run any migrations)

/usr/lib/postgresql/NEW_VER/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/OLD_VER/main \
  --new-datadir=/var/lib/postgresql/NEW_VER/main \
  --old-bindir=/usr/lib/postgresql/OLD_VER/bin \
  --new-bindir=/usr/lib/postgresql/NEW_VER/bin \
  --old-options '-c config_file=/etc/postgresql/OLD_VER/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/NEW_VER/main/postgresql.conf' \
  --check

• Assuming it all looks good, run the migration with the above snippet without --check
• Log out of postgres and back into root
• Swap postgres's ports in the config files
  • In the new config, replace port = 5433 with port = 5432
  • In the old config, replace port = 5432 with port = 5433
• Start the postgresql systemd service
  • systemctl start postgresql
• Log into postgres user again and make sure it's running the right version
  • psql -c "SELECT version();"
• Run the generated script
  • ./analyze_new_cluster.sh
• Exit to root user
• List installed packages and grep for postgres to remove old versions
  • apt list --installed | grep postgres
  • apt remove postgres-OLD_VER postgres-OLD_VER…
• Make sure things are running smoothly then rm -rf /etc/postgresql/OLD_VER
• Log back into postgres user again and drop the old cluster data with ./delete_old_cluster.sh