Last updated on November 28, 2020 by Dan Nanni
When you are running a MySQL server with critical information, you probably want to set up a protection mechanism such as replication or backup to prevent loss of data in the server due to unforseen events. While there are number of ways to back up MySQL databases, the simpliest solution is Linux command line. In particular, 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 program is contained in MySQL client package. So you will need to install MySQL client package first.
mysqldumpon Ubuntu or Debian
$ sudo apt-get install mysql-client
mysqldumpon CentOS, Fedora or RHEL
$ 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.
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).
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;
Please note that this article is published by Xmodulo.com under a Creative Commons Attribution-ShareAlike 3.0 Unported License. If you would like to use the whole or any part of this article, you need to cite this web page at Xmodulo.com as the original source.