MySQL Databases backup/restore using mysqldump and mysql tool

MySQL

Hi Friends, Today we will see how to backup and restore mysql database using mysqldump/mysql utitlity,

Single database backup (from server):

mysqldump -u root -p –routines –triggers –single-transaction –quick –lock-tables=false <db name> | gzip > /<backuppath>/<dbname>.sql.gz

All databases backup (from server):

mysqldump -u root -p –routines –triggers –single-transaction –quick –lock-tables=false –all-databases | gzip > /<backuppath>/<dbname>.sql.gz

Single Database Restore:

mysql -uroot -p <db name> < <dbname>.sql.

All databases Restore:

mysql -uroot -p < <dbname>.sql

Note:

–If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave’s data. These files are always needed to resume replication after you restore the slave’s data.

–If you have performance problems with your server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master.

–Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

— MySQL supports incremental backups: You need to start the server with the –log-bin option to enable binary logging; . At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained further below. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump –flush-logs, or mysqlhotcopy –flushlog. mysqldump A Database Backup Program and mysqlhotcopy. A Database Backup Program.

–If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave’s data. These files are always needed to resume replication after you restore the slave’s data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the –slave-load-tmpdir option. (This location defaults to the value of the tmpdir variable if not specified.) The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations.