How to backup a MySQL server database

If you are running a MySQL server with critical information, you may want to back up its databases periodically. A Linux command-line tool called mysqldump allows you to back up MySQL databases without needing to shut down a MySQL server. mysqldump generates as output a text file containing a series of MySQL commands that represent a current snapshot of MySQL databases being backed up. The mysqldump output file can easily be compressed and/or encrypted as needed.

In this tutorial, I will describe how to back up a MySQL server with mysqldump.

The mysqldump program is contained in MySQL client package. So you will need to install MySQL client package first.

To install mysqldump on Ubuntu or Debian:

$ sudo apt-get install mysql-client

To install mysqldump on CentOS, Fedora or RedHat:

$ sudo yum install mysql

In order to generate an online snapshot for a live MySQL server, you need to prevent any update to its databases while backup is being processed. How to achieve that depends on what storage engine you are using for the MySQL tables inside. So first find out which storage engine (e.g., MyISAM, Innodb) you are using. This guide will tell you how.

Backup InnoDB Databases

If all your MySQL tables use InnoDB, you can use "--single-transaction" option with mysqldump to make an online backup:

$ mysqldump -h[server-ip-address] -u[username] -p[password] --all-databases --single-transaction > backup.sql

Note that in the above mysqldump command, there is no space between argument options (e.g., -h, -u, -p) and argument values themselves (e.g., server-ip-address, username, password).

Backup MyISAM or InnoDB/MyISAM Databases

The above command, however, does not work if you are using MyISAM tables or a mix of InnoDB/MyISAM tables, since MyISAM does not support transactions. In that case, you need to explicitly lock all the tables while the backup is being done as follows.

mysql> flush tables with read lock;

The MySQL flush statement in the above closes all open tables in the MySQL server, and obtains read locks to all the tables in all existing databases, thereby preventing any write to the databases. This ensures a consistent snapshot of a running system. Now proceed to perform the backup.

$ mysqldump -h[server-ip-address] -u[username] -p[password] --all-databases > backup.sql

Finally, release the global lock to the databases.

mysql> unlock tables;

Subscribe to Xmodulo

Do you want to receive Linux FAQs, detailed tutorials and tips published at Xmodulo? Enter your email address below, and we will deliver our Linux posts straight to your email box, for free. Delivery powered by Google Feedburner.


Support Xmodulo

Did you find this tutorial helpful? Then please be generous and support Xmodulo!

The following two tabs change content below.
Dan Nanni is the founder and also a regular contributor of Xmodulo.com. He is a Linux/FOSS enthusiast who loves to get his hands dirty with his Linux box. He likes to procrastinate when he is supposed to be busy and productive. When he is otherwise free, he likes to watch movies and shop for the coolest gadgets.

Leave a comment

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