MySQL and Postgres

DRSM includes backup, statistics and maintenance scripts for MySQL and Postgres servers. Each one can save the server's credentials the first time, so later runs work from cron.

Saved credentials

Run a script without a host name to be asked for the server and login, and choose to save them:

$ source ~/.drsm.sh
$ cd $DRSMHOME/mysql
$ ./mysql_dbflush.sh

MySQL HOST: cms.example.com
MySQL USER: dbadmin
MySQL PW: ***********
Do you want to save MySQL auth for cms.example.com (yes/no)> yes

After that, pass the host name and no prompt appears: ./mysql_dbflush.sh cms.example.com.

Saved credentials are plain text files in $DRSMHOME/.auth, protected by file permissions (mode 600). MySQL passwords are passed through a temporary private option file, never on the command line where ps would show them.

Backup settings

$ vi ~/.drsm.sh

export BACKUP_age=30          # days of backups to keep
export BACKUPdir=${HOME}/backups

MySQL

Script	Does
`mysql_backup.sh host`	Backs up every database with `--single-transaction`, so InnoDB tables are consistent without locking. Includes routines and triggers, skips the system schemas
`collect_mysql_stats.sh host`	Collects server statistics
`mysql_defragment.sh host`	Optimizes fragmented tables
`mysql_dbflush.sh host`	Flushes tables, logs, hosts, status and privileges. Works on MySQL 8, which removed the query cache

$ cd $DRSMHOME/mysql
$ ./mysql_backup.sh cms.example.com
$ ./mysql_defragment.sh cms.example.com
$ ./mysql_dbflush.sh cms.example.com

Postgres

Script	Does
`postgres_db_checks.sh host`	Tests the connection and saves credentials
`postgres_backup.sh host`	Backs up every database with `pg_dump`; errors go to its log
`postgres_optimize.sh host`	Runs `vacuumdb --full`
`postgres_stats.sh host`	Lists databases; statistics collection is still to do

postgres_optimize.sh runs vacuumdb --full, which locks each table while it runs. Schedule it outside busy hours.

Use client tools that match the server's version. Set them for every server in ~/.drsm.sh, or for one server in $DRSMHOME/.auth/<host>.pg:

export LD_LIBRARY_PATH="/usr/local/pgsql-16/lib"
export PSQL="/usr/local/pgsql-16/bin/psql"
export PG_DUMP="/usr/local/pgsql-16/bin/pg_dump"
export PG_RESTORE="/usr/local/pgsql-16/bin/pg_restore"
export VACUUMDB="/usr/local/pgsql-16/bin/vacuumdb"