🍄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.|info}}


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)
--old-options '-c config_file=/etc/postgresql/OLD_VER/main/postgresql.conf'
--new-options '-c config_file=/etc/postgresql/NEW_VER/main/postgresql.conf'
*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
*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>

Category:Admin guides