Difference between revisions of "Mysql backup and restore"

Latest revision as of 04:01, 12 June 2018

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

Restoring a single database from a complete MySQL database dump

mysql -u root -p --one-database destdbname < alldatabases.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 -p <password> --opt >/tmp/alldatabases.sql

Dump one database for backup.

 # [mysql dir]/bin/mysqldump -u username -p <password> --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 -p <password> databasename < /tmp/databasename.sql

Create Table Example 1.