How to backup and restore large MySQL databases by Mysqldump

marciayudkin

Member
Registered
Joined
Sep 13, 2014
Messages
34
Points
8
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:

Code:
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:

Code:
mysqldump -u root -pyourpassword  databasename>  /home/example.com/public_html/backup_folder/Your_DB_backup.sql
2. Restore database:

Code:
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

Code:
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

Member
Registered
Joined
Oct 26, 2015
Messages
31
Points
0
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 :p.

Regards,
Adam
 

Ron Killian

Well-known member
Registered
Joined
Dec 3, 2015
Messages
363
Points
0
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?
 

hostgliders

Member
Hosting Provider
Registered
Joined
Feb 17, 2017
Messages
51
Points
0
I agree with the tutorial because large DB can't be import via any tool like phpmyadmin and thus we need server backend and those commands do

mysqldump dbname > dbname.sql
mysql dbname < dbname.sql

above commands will also work as it is more simple and easy to use as root user.
 

HyperVMart

Member
Hosting Provider
Registered
Joined
Mar 21, 2017
Messages
55
Points
8
sounds great, we only had issues restoring relatively big database which never worked using phpmyadmin, so we had to contact the host to do it from their side, this goes to my favorite tutorial to refer back to in future if needed...
 
Newer Threads

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top