MySql backup & restore commands

This article has some handy commands to backup and restore mysql databases. Some of the terms used below refer to
USERNAME = your username
MYSQL_PASSWORD = your password
DATABASE_1 = name of the database
LOCAL_DOMAIN.COM = local domain name
DESTINATION_DOMAIN.COM = remote domain name
Take MySql dump, gzip it and take it in a directory.

mysqldump -uUSERNAME -p DATABASE_1 | gzip > /abc/db-dump-2014-09-22.sql.gz

 
Take MySql dump and place it with the current date in file name.

mysqldump --user=USERNAME --password=MYSQL_PASSWORD --single-transaction --add-drop-table DATABASE_1 > backup`date +%Y%e%d`.sql

This command will take a mysql dump. It will then gzip the sql file and set to another server via ssh and take it in the requried directory.

mysqldump --host=LOCAL_DOMAIN.COM --port=3306 --opt -Q --hex-blob --routines --add-locks --disable-keys --quick --no-autocommit --databases DATABASE_1 DATABASE_2 --max_allowed_packet=64M -uUSERNAME --password=MYSQL_PASSWORD | gzip | ssh -C USERNAME@DESTINATION_DOMAIN.COM "cat - > /abc/2014-09-22-dbs.sql.gz"

unzip .sql.tar file

tar -xvf 2014-09-22-dbs.sql.tar

unzip .sql.gz file

gzip -d 2014-09-22-dbs.sql.gz

Run a database backup

mysql -uUSERNAME -p DATABASE_1 < 2014-09-22-dbs.sql

Backup a database via cronjob. This command will place the backups in a specific directory and also also remove backups which are more then 30 days old.

mysqldump --opt -Q --hex-blob --routines --add-locks --disable-keys --quick --no-autocommit --user=USERNAME --password=MYSQL_PASSWORD DATABASE_1 | gzip > /abc/backups/DATABASE_$(date +%Y-%m-%d).gz; find /abc/backups/* -mtime +30 -exec rm {} \;

Moving all databases and exclude few databases like mysql, information_schema, performance_schema.

mysql -uUSERNAME --password=MYSQL_PASSWORD --batch --skip-column-names --execute="SHOW DATABASES" | grep -v "mysql" | grep -v "information_schema" | grep -v "performance_schema" | grep -v "phpmyadmin" | xargs mysqldump --port=3306 --opt -Q --hex-blob --routines --add-locks --disable-keys --quick --no-autocommit --skip-add-locks --max_allowed_packet=64M -uUSERNAME --password=MYSQL_PASSWORD --databases | gzip > /abc/backups/databases-dump-2014-09-22.sql.gz

Import only one database from multiple database in a sql file

mysql -uUSERNAME -p --one-database DATABASE_NAME < FILE_NAME.sql