mysqldump

Backup

To make a backup of a single database...

mysqldump -u myuser -p myDB > backup-myDB.sql

Backup entire DBMS (all tables consistent)...

mysqldump -A -R -E --single-transaction --quick --lock-tables=false >full-backup-$(date +%F).sql -u root -p

To create a dump file suitable for moving a database from one instance to another...

mysqldump -u myuser -p --single-transaction --databases myDB --result-file backup-myDB.sql

To dump just stored procedures, functions and triggers for all databases...

mysqldump -h myhost -u myuser -p -n -d -t --routines --all-databases > routines.sql

mysqldump -h myhost -u myuser -p --no-create-db --no-data --no-create-info --routines --all-databases > routines.sql

For a specific database...

mysqldump -h myhost -u myuser -p --no-create-db --no-data --no-create-info --routines --databases mydatabase > routines.sql

-h-u-p-n-d

-R-t>

-A-E
--host--user--password--no-create-db--no-data--triggers--skip-triggers--routines--no-create-info--result-file--single-transaction--databases--all-databases--events--lock-tables--skip-lock-tables--quick--ignore-table="my.table"--hex-blob--where="expression"
hostnameusernamepassword (if no password passed then prompt for password)Suppress the CREATE DATABASE ... IF EXISTS statement for each dumped databaseNo row informationDump triggers (this is the default)Don't dump triggersDump stored routines (functions and procedures)Suppress the CREATE TABLE statement for each dumped tableOutput filePreserve data consistencylist of databaseAll databasesDump Events

Dump tables row by row, avoiding difficulties of buffering large amounts of data in memoryIgnore named tableDumps binary strings to hexadecimalConditional dump

Restore

NOTE: The script creates databases, tables, indexes etc, but does not create users or make grants

To load the dump file back into the server:

mysql myDB < backup-myDB.sql

Alternatively...

mysql -e "source /path-to-backup/backup-myDB.sql" myDB

Backup Script

sudo -u mysql -s vi mysql-backup.sh

mysql_config_editor is a safer way of storing and using passwords in scripts.
#!/bin/bashrm /backup/full-backup.sqlOmv /backup/full-backup.sql /backup/full-backup.sqlOmysqldump -A -R -E --single-transaction -u root -p53curEpa$$w0rd! >/backup/full-backup.sql

sudo -u mysql -s crontab -e

00 22 * * * /bin/bash /backup/mysql-backup.sh >/tmp/mysql-backup.out 2>&1

Bibliography