How to back up a MySQL server database

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.

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

Install mysqldump on Linux

Install mysqldump on Ubuntu or Debian

$ sudo apt-get install mysql-client

Install mysqldump on 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.

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;

Support Xmodulo

This website is made possible by minimal ads and your gracious donation via PayPal or credit card

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.