Rehmat Alam A blogger who has fallen in love with coding! Here at SupportiveHands.net, I share things that relate to coding, servers, E-marketing, and other related fields.

How to Backup and Restore a Large MySQL Database

1 min read

phpMyAdmin is the most familiar tool for database management on small and medium-sized websites and blogs. This free tool makes the task of database management simple but phpMyAdmin is a no-no when it comes to export and import the backups of larger databases. Bigger websites’ MySQL data comes in GBs and the best way to export a large database is to use command-line.

If your database size is in a few hundred MBs, then you can run a one-session task and stay on the terminal until the backup is generated or if it is imported. But if the database is in GBs, then your server needs time to process the data and in such a scenario, you need to run a background task that should keep running even if you close the terminal.

Exporting MySQL Dump

SSH into your server on any terminal and run this command:

mysqldump -u dbusername -p dbname > database_dump.sql

Once you will press enter, you will be asked to provide the password for the MySQL user and then the backup will be exported to database_dump.sql.

If you want to download this backup over HTTP, then first browse to any secure directory on your domain and generate the backup file there.

cd /var/www/yourdomain.com/html/backups/ # provide correct path
mysqldump -u dbusername -p dbname > database_dump.sql

Once the dump is generated, you can download it at http://yourdomain.com/backups/database_dump.sql but you must remember that this is a serious security risk to place data backups in publicly accessible places, even for a very short period of time.

If your database size is in GBs, then the best way to export the backup is to initiate a background task. This will ensure that your terminal’s session doesn’t interrupt the backup process. To achieve this goal, you can run a nohup job like this:

nohup mysqldump -u dbusername -pdbpass dbname > database_dump.sql &

Remember that now we are providing the password in the same command. The progress details will be written to a file nohup.out and you can review it later if needed. After initializing the process, you can terminate the terminal session and the system will keep running the task in the background unless the dump file is generated.

Restoring MySQL Dump

To restore a MySQL dump file, first browse into the directory that holds the SQL file and then run this command to restore the database:

mysql -u dbusername -p dbname < database_dump.sql

Remember that your old database data may be over-written if you are importing same tables.

For a large database, simply run a nohup task like this:

nohup mysql -u dbusername -pdbpass dbname < database_dump.sql &

Hint: You must use your actual details instead using dbusername, dbname and dbpass in the commands. Moreover, there shouldn’t be any space between -p and the password unlike -u and dbusername.

I hope this guide helps you in exporting a large database easily or to import a database dump.

 

Rehmat Alam A blogger who has fallen in love with coding! Here at SupportiveHands.net, I share things that relate to coding, servers, E-marketing, and other related fields.

PHPMailer: Called Mail() without being connected cPanel/WHM

PHPMailer is the most trusted PHP class to send emails that provide a complete set of features to enable you to send emails the easy...
Rehmat Alam
1 min read

How to bulk delete posts by a user in…

vBulletin is the most powerful forum software out there but sometimes its complex (or I can say that the ugly) administration control panel makes...
Rehmat Alam
1 min read

How Do I Permenantly Delete My Blog on Blogger

Blogger, the Google’s free blogging platform, allows you to create up to 100 blogs per account for free. If you have created a lot...
Rehmat Alam
1 min read

Leave a Reply

Your email address will not be published. Required fields are marked *