MySQL Backup and Recovery
This article provides a quick guide to performing backup and recovery of MySQL databases. The article only describes those methods available without needing additional licensing.
Logical Backups
Logical Backup (mysqldump)
Amongst other things, the mysqldump command allows you to do logical backups of your database by producing the SQL statements necessary to rebuild all the schema objects. An example is shown below.
$ # All DBs $ mysqldump --user=root --password=mypassword --all-databases > all_backup.sql $ # Individual DB (or comma separated list for multiple DBs) $ mysqldump --user=root --password=mypassword mydatabase > mydatabase_backup.sql $ # Individual Table $ mysqldump --user=root --password=mypassword mydatabase mytable > mydatabase_mytable_backup.sql
The full syntax of the command can be found here.
Recovery from Logical Backup (mysql)
The logical backup created using the mysqldump command can be applied to the database using the MySQL command line tool, as shown below.
$ # All DBs $ mysql --user=root --password=mypassword < all_backup.sql $ # Individual DB $ mysql --user=root --password=mypassword --database=mydatabase < mydatabase_backup.sql
The full syntax of the MySQL command line tool can be found here.
Cold Backups
Cold backups are a type of physical backup as you copy the database files while the database is offline.
Cold Backup
The basic process of a cold backup involves stopping MySQL, copying the files, the restarting MySQL. You can use whichever method you want to copy the files (cp, scp, tar, zip etc.).
# service mysqld stop # cd /var/lib/mysql # tar -cvzf /tmp/mysql-backup.tar.gz ./* # service mysqld start
Recovery from Cold Backup
To recover the database from a cold backup, stop MySQL, restore the backup files and start MySQL again.
# service mysqld stop # cd /var/lib/mysql # tar -xvzf /tmp/mysql-backup.tar.gz # service mysqld start
For more information see:
Hope this helps. Regards Tim...
 |