How to backup and restore large MySQL databases by Mysqldump

Joined
Sep 13, 2014
Messages
34
Best answers
0
Ratings
13
Points
8
#1
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!
 
Joined
Oct 26, 2015
Messages
31
Best answers
0
Ratings
25
Points
0
#2
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
Joined
Dec 3, 2015
Messages
364
Best answers
0
Ratings
125
Points
0
#3
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?
 
Joined
Feb 17, 2017
Messages
51
Best answers
0
Ratings
8
Points
0
#5
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.
 
Joined
Mar 21, 2017
Messages
54
Best answers
0
Ratings
4
Points
0
#6
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...
 
Latest Threads
Replies
0
Views
8
Replies
3
Views
8
Replies
1
Views
9

Latest postsNew threads

Latest Hosting OffersNew Reviews

Sponsors

Latest Blog ArticlesMost Viewed Threads

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