Backing Up And Restoring Your MySQL Database Via Command Line

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 tar.gz, tar.bz2 or 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 tar.gz, tar.bz2 or 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.

5 responses to “Backing Up And Restoring Your MySQL Database Via Command Line”.

  1. ethel Says:

    Hi Azmeen! Er.. I don’t know much about MySQL except for the “MySQL” stuff I saw in my CPanel. 😛 So I cannot comment anything about it, ok?

    But I wanna take this opportunity to wish you a Happy New Year (lambat sikit lah..hehe) 2008! I only got my Internet connection a few days back.. 🙁 Hence the very late wishes. 😀 Hope this new year brings lots of blessings, money, good health and peace in your family! 🙂

  2. Site Admin Azmeen Says:

    Heya ethel,

    Happy new year to you too 🙂

    Congrats on getting your net connection up again.

  3. AdsenseRush.com Says:

    AdsenseRush.com is currently for sale on ebay for less than $20.

    Might wanna check it out since you own AdsenseRush.net!

  4. Site Admin Azmeen Says:

    I don’t own AdsenseRush.com, they’re just scraping my contents.

  5. jon Says:

    good nas