🍄Sql Snippets

Some random commands I always forget when I need them

Note: 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 - > 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';

    • Alternatively, run this as root: $ sudo -u postgres createuser -P app_user

  • Create database: CREATE DATABASE app WITH OWNER app_user ENCODING = 'UTF8';

    • Alternatively, run this as root: $ sudo -u postgres createdb -O app_user app

  • Modify database owner: ALTER DATABASE app OWNER TO new_app_user;

  • Connect/use database: \c <database>

  • List tables in a database: \dt

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