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.