How to backup and restore MySQL databases?
Posted: Tue Jun 01, 2010 4:15 pm
Backup
Use following command.
If there are multiple databases, then use following command.
If database is too large, we can dump it as a compressed file,
OR
Restore
Use following command.
If the dump file is compressed,
Use following command.
Code: Select all
mysqldump -h [hostname] -u [username] -p [password] [databasename] > [backupfile.sql]
[username] - this is your database username
[password] - this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the file to which the backup should be written.
Code: Select all
mysqldump -h [hostname] -u [username] -p [password] --databases [databasename1] [databasename2] > [backupfile.sql]Code: Select all
mysqldump -h [hostname] -u [username] -p [password] [databasename] | bzip2 -c >backupfile.sql.bz2 Code: Select all
mysqldump -h [hostname] -u [username] -p [password] [databasename] | gzip >backupfile.sql.gzUse following command.
Code: Select all
mysql -u [username] -p [password] [database_to_restore] < [backupfile.sql]Code: Select all
gunzip < [backupfile.sql.gz] | mysql -u [username] -p [password] [database_to_restore]