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:
To install mysqldump on CentOS, Fedora or RedHat:
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:
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.
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.
Finally, release the global lock to the databases.
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.
Did you find this tutorial helpful? Then please be generous and support Xmodulo!