How to backup and restore MySQL databases?

[Saman]

Backup
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.

If there are multiple databases, then use following command.

Code: Select all

mysqldump -h [hostname] -u [username] -p [password] --databases [databasename1] [databasename2] > [backupfile.sql]

If database is too large, we can dump it as a compressed file,

Code: Select all

mysqldump -h [hostname] -u [username] -p [password] [databasename] | bzip2 -c >backupfile.sql.bz2 

OR

Code: Select all

mysqldump -h [hostname] -u [username] -p [password] [databasename] | gzip >backupfile.sql.gz

Restore
Use following command.

Code: Select all

mysql -u [username] -p [password] [database_to_restore] < [backupfile.sql]

If the dump file is compressed,

Code: Select all

gunzip < [backupfile.sql.gz] | mysql -u [username] -p [password] [database_to_restore]

[Rksk]

https://robot.lk/viewtopic.php?f=67&t=456