Difference between revisions of "Mysql backup and restore"

From thelinuxwiki
Jump to: navigation, search
(Pushed from thelinuxwiki.com.)
 
Line 13: Line 13:
  
 
Dump all databases for backup. Backup file is sql commands to recreate all db's.
 
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
+
   # [mysql dir]/bin/mysqldump -u root -p <password> --opt >/tmp/alldatabases.sql
 
Dump one database for backup.
 
Dump one database for backup.
  
   # [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
+
   # [mysql dir]/bin/mysqldump -u username -p <password> --databases databasename >/tmp/databasename.sql
 
Dump a table from a database.
 
Dump a table from a database.
  
Line 22: Line 22:
 
Restore database (or database table) from backup.
 
Restore database (or database table) from backup.
  
   # [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
+
   # [mysql dir]/bin/mysql -u username -p <password> databasename < /tmp/databasename.sql
 
Create Table Example 1.
 
Create Table Example 1.

Revision as of 16:03, 26 September 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 -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.