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.|info}}
MySQL =
Working with users ==
-
Create a DB: <code>CREATE DATABASE database_name;</code>
-
Create a user: <code>CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';</code>
-
Grant user permissions: <code>GRANT ALL ON database_name.* TO 'user_name'@'localhost' IDENTIFIED BY 'password';</code>
-
List all users: <code>SELECT User FROM mysql.user;</code>
Obviously you should change <code>password</code> to an actually secure password, <code>user_name</code> to something identifiable, and the same for <code>database_name</code>. Even if it’s one user per database, it’s better to use different names for each one.
Working with databases ==
*List all databases: <code>SHOW DATABASES;</code>
*Select a database to interact with: <code>USE database;</code>
*List tables: <code>SHOW TABLES;</code>
*Show contents of a table: <code>SELECT * FROM table;</code>
*Delete a row: <code>DELETE FROM table WHERE column=value;</code>
*Dumping databases for backups: <code>mysqldump dbname > dbname_$(date -u -Iseconds).sql</code> (run as shell command)
Dumps with compression: <code>mysqldump dbname | gzip -c - > dbname_$(date -u -Iseconds).sql.gz</code>
*Restore database from backup: <code>mysql dbname < backup.sql</code>
With compression: <code>gunzip backup.sql.gz</code> then run command above
Postgres =
*List databases: <code>l</code>
*Create user: <code>CREATE ROLE app_user WITH LOGIN ENCRYPTED PASSWORD 'password';</code>
Alternatively, run this as root: <code>$ sudo -u postgres createuser -P app_user</code>
*Create database: <code>CREATE DATABASE app WITH OWNER app_user ENCODING = 'UTF8';</code>
Alternatively, run this as root: <code>$ sudo -u postgres createdb -O app_user app</code>
*Modify database owner: <code>ALTER DATABASE app OWNER TO new_app_user;</code>
*Connect/use database: <code>c <database></code>
*List tables in a database: <code>dt</code>
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 <code>/etc/postgresql/VER/main/postgresql.conf</code> and <code>…/main/pg_hba.conf</code>
*Stop the <code>postgresql</code> systemd service
<code>systemctl stop postgresql</code>
*<code>sudo su - postgres</code>
*Check the clusters with the following (doesn't actually run any migrations)
<pre>
/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
</pre>
*Assuming it all looks good, run the migration with the above snippet ''without'' <code>--check</code>
*Log out of postgres and back into root
*Swap postgres's ports in the config files
In the new config, replace <code>port = 5433</code> with <code>port = 5432</code>
In the old config, replace <code>port = 5432</code> with <code>port = 5433</code>
*Start the <code>postgresql</code> systemd service
<code>systemctl start postgresql</code>
*Log into <code>postgres</code> user again and make sure it's running the right version
<code>psql -c "SELECT version();"</code>
*Run the generated script
<code>./analyze_new_cluster.sh</code>
*Exit to root user
*List installed packages and grep for postgres to remove old versions
<code>apt list --installed | grep postgres</code>
<code>apt remove postgres-OLD_VER postgres-OLD_VER…</code>
*Make sure things are running smoothly then <code>rm -rf /etc/postgresql/OLD_VER</code>
*Log back into <code>postgres</code> user again and drop the old cluster data with <code>./delete_old_cluster.sh</code>