Data Centers

How to back up MySQL databases from the command line in Linux

Whether you're running a LAMP stack on Ubuntu or CentOS, you need to back up your MySQL databases. Learn how to do this with the help of the mysqldump command.

mysqlhero.jpg
Image: Jack Wallen

Your Ubuntu Server is up and running with a LAMP stack in your data center, and on that server runs a database (or multiple databases) that must be backed up. There are various software options you could install to make that happen, but if you want to keep that server platform down to the bare minimum to avoid possible issues, chances are you'll want to work with the built-in tools to perform the backup. It's a good thing the developers of MySQL thought of that.

I'll walk you through the process of backing up your MySQL databases without having to install third-party software. (Note: The process is the same for any Linux distribution with MySQL installed.) With this skill under your belt, you'll be able to script your own automated backups with the help of bash and cron.

SEE: Data Backup Policy (Tech Pro Research)

What you'll need

If MySQL is up and running, you have all the necessary software. To pull off this task, you will need the password for the MySQL user that has permissions to access the database(s) in question.

The mysqldump command

The command that does the heavy lifting is mysqldump. The command's structure looks like this:

mysqldump [options] [database_name] [backup_name]

The process is quite simple. Let's say you have a database named products, and you want to back it up to an external drive mounted at /mnt/backups/. The basic command to run that dump (aka backup) would be this:

sudo mysqldump -p products > /mnt/backups/products.sql

There are a couple of issues at hand. First and foremost, you must use the -p option; this instructs mysqldump that you will be entering the password associated with the MySQL user. If that fails (depending upon your setup), you may have to include the option to instruct mysqldump which user has said permission, like so (USER is the actual user that has permission to work with the databases, such as root):

mysqldump -u USER -p products > /mnt/backups/products.sql

Another issue might appear if the user doesn't have write permission to that directory; if this is the case, you will receive a Permission denied error. If you don't want to run the risk of opening the permissions on that backup directory, you can first issue the command sudo su and then run the mysqldump command. Remember: After completing the task, exit out of the root bash prompt with the exit command.

Backing up all your databases

If you want to back up all of your databases, you can accomplish this with the —all-databases option, like so:

mysqldump -u USER -p --all-databases > /mnt/backups/all_databases.sql

Compressing your backup

If your database is considerably larger and you plan on manually running backups on a daily basis, you might consider compressing the backups. This is another feature the developers of MySQL rolled in.

Let's stick with our same example as above, only with compression. To accomplish this, we'll use the -C option, like so (USER is the username that has permission to work with the databases):

mysqldump -u USER -p -C products > /mnt/backups/products.sql.tgz

The above command will dump a compressed version of your database backup in the defined file (in this case products.sql.tgz).

Keep it going

You're ready to continue the work. In a future article, I'll explain how to automate the process of backing up your databases, as well as how to restore a database from a backup. If you'd rather run your backups manually, you now have all you need to do so.

Also see

About Jack Wallen

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website jackwallen.com.

Editor's Picks

Free Newsletters, In your Inbox