Difference between revisions of "Mysql backup and restore"

Revision as of 19:01, 25 February 2013

Backing up all databases

 mysqldump -u root -h localhost -p --all-databases > backup.sql

Restoring all databases

 mysql -u root -h localhost -p < backup.sql

Automated backup (not tested yet)

You can automate the backup process by making a small shell script which will create a daily backup file. How do you get cron to back up your database without overwriting the older backup? You can use a tiny shell script to add the date to your backup file. An example of a shell script you could use is shown below.

 #!/bin/sh
 date=`date -I`
 mysqldump --all-databases | gzip > /var/backup/backup-$date.sql.gz

Dump all databases for backup. Backup file is sql commands to recreate all db's.

 # [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

 # [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.

 # [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

 # [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.