How to backup and restore large MySQL databases by Mysqldump

[marciayudkin]

There are many tools to backup and restore databases like MySQL Dumper, Dumper Spydex, Bigdump or using wordpress plugins like BackWPUp, BackupBuddy and they are really very good. However if your database is too big upto several tens of GB then more common tools will get errors and could not handle them. So I would suggest to backup and restore databases by Mysqldump through the statements.

1. Backup database:

Access with Root account into SSH Server, run this command:

mysqldump -u username -p[username_password] databasename > /path_to_your_db/databasename_backup.sql

For example: if you backup database with user is root and pass is yourpassword and have the path to your database is /home/example.com/public_html/backup_folder/

Using this command:

mysqldump -u root -pyourpassword  databasename>  /home/example.com/public_html/backup_folder/Your_DB_backup.sql

2. Restore database:

mysql -u username -p[username_password] databasename < /path_to_your_db/databasename_backup.sql

For example, you want to restore database with user is root and pass is yourpassword then using this command

mysql -u root -pyourpassword  databasename < /home/example.com/public_html/backup_folder/Your_DB_backup.sql

If you have other ways to backup and restore big databases easily, please write down.

Good luck!

 

[LuxVM]

Hello marciayudkin,

This is a great tutorial and will help those needing to move large databases as well as those users who are unsure how to do this One thing i will say is ask a staff member of webmastersun to move this to the Web Hosting Tutorials section. That way users will know its a tutorial, instead of thinking it was a question on "moving large databases" like i thought .

Regards,
Adam

 

[Ron Killian]

I agree, nice tutorial. Sure it will be helpful to many.

I take the lazy, easy way out, using phpMyAdmin with import and export. Course that is limited in DB size, so your way might be needed for larger DB's?