One priceless benefit of having a web hosting provider that gives you SSH access to the server is that there’s a wealth of command line tools that can make data management very easy for you. In this post, I’m going to illustrate how you can use some of MySQL’s command line tools to back up (export) and restore (import) your database.
Backing Up Your MySQL Database
MySQL ships with a tool called
mysqldump. Just as the name implies,
mysqldump will dump your MySQL database data into a text file.
Here is how you would typically use
mysqldump via a Linux command line:
mysqldump -u your_mysql_username -p your_database > backup_file_name.sql
Of course you should replace your_mysql_username, your_database and backup_file_name.sql with your own MySQL username, your MySQL database name and your database backup file name.
Once you entered the above command, you will be prompted to enter the MySQL password for the username you entered. If you provided the correct password you will see the database backup file appear in the directory where you run the command.
As database dump files can be quite big depending on the amount of data stored, it’s recommended that you compress the data file to a
zip file before downloading or emailing it.
Restoring Your MySQL Database From A Dump File
Restoring a MySQL database from a dump file is a pretty straightforward exercise. I’m going to assume that your database dump file is called
database_backup.sql, and that it is in the current working directory. The command that you’d need to enter is:
mysql -u your_mysql_username -p your_database < database_backup.sql
Again, you should replace your_mysql_username and your_database with your own MySQL username and your MySQL database name.
You will be prompted for the password for the username you provided and the restoration process will only proceed if the password is correct.
Why Do Database Backups/Restores From The Command Line?
Those of you who have been using MySQL powered web sites would surely have heard of phpMyAdmin. It is a powerful web based frontend to manipulate and manage your MySQL databases. It also has backup and restore functions built into it. In addition to that, it also sports a very user-friendly interface. So why use the command line over phpMyAdmin?
Personally, I find that the main benefit of using the command line over phpMyAdmin is that it saves a lot of time and bandwidth. By using the command line, the dumps are created directly on the server, and it takes only a fraction of the time taken by phpMyAdmin. You can also opt to compress the dump file into a
zip archive format which could easily reduce the file size by up to 80%. Downloading this file would then take up less bandwidth and time.
Additionally, performing a database restore via phpMyAdmin would be impossible should the database dump file size be very big. Most web servers are configured to not allow huge files from being uploaded to the server via HTTP forms, and rightfully so! This could be a potential security issue not to mention a major bandwidth and resource hog.
In the end, the choice is yours, just make sure that you understand what the commands do or it can make you lose weight faster than Miracle burn. However, as I’ve mentioned there will definitely be situations where using the command line is your only option.