MySQL Data Backup

MySQL data backup is one of the very important tasks for database administrators. Unexpected system crashes or hardware damage can lead to data loss, so MySQL administrators should back up data regularly to minimize the loss in case of unexpected situations. This section describes 3 methods of data backup.

11.1.1 Backing up data with the mysqldump command

mysqldump is a very useful database backup tool provided by MySQL. mysqldump command, when executed, backs up the database into a text file that actually contains several CREATE and INSERT statements, using which you can recreate tables and insert data.

The basic syntax format for mysqldump backup database is as follows:

mysqldump  –u user –h host –ppassword dbname[tbname, [tbname...]]> 

user means user name; host means the host name of the logged-in user; password is the login password; dbname is the name of the database that needs to be backed up; tbname is the data table that needs to be backed up in the dbname database, you can specify more than one table to be backed up, and if you don't specify it, it means to back up all the data tables; the right arrow symbol (>) tells mysqldump to write the definition and data of the backup data table to the backup file; is the name of the backup file.

1. Backup all tables in a single database using mysqldump

[Example 11.1] Use the mysqldump command to backup all tables in a single database.

To better understand how the mysqldump tool works, a complete database example is given here. First log in to MySQL, create the booksDB database and each table according to the following database structure, and insert data records. The database and table definitions are as follows:

CREATE DATABASE booksDB;

use booksDB;

 

CREATE TABLE books

(

bk_id INT NOT NULL PRIMARY KEY,

bk_title VARCHAR(50) NOT NULL,

copyright YEAR NOT NULL

);

INSERT INTO books

VALUES (11078, 'Learning MySQL', 2010),

(11033, 'Study Html', 2011),

(11035, 'How to use php', 2003),

(11072, 'Teach yourself javascript', 2005),

(11028, 'Learning C++', 2005),

(11069, 'MySQL professional', 2009),

(11026, 'Guide to MySQL 9.0', 2024),

(11041, 'Inside VC++', 2011);

 

CREATE TABLE authors

(

auth_id INT NOT NULL PRIMARY KEY,

auth_name VARCHAR(20),

auth_gender CHAR(1)

);

INSERT INTO authors  

VALUES (1001, 'WriterX' ,'f'),

(1002, 'WriterA' ,'f'),

(1003, 'WriterB' ,'m'),

(1004, 'WriterC' ,'f'),

(1011, 'WriterD' ,'f'),

(1012, 'WriterE' ,'m'),

(1013, 'WriterF' ,'m'),

(1014, 'WriterG' ,'f'),

(1015, 'WriterH' ,'f');

 

CREATE TABLE authorbook

(

auth_id INT NOT NULL,

bk_id INT NOT NULL,

PRIMARY KEY (auth_id, bk_id),

FOREIGN KEY (auth_id) REFERENCES authors (auth_id),

FOREIGN KEY (bk_id) REFERENCES books (bk_id)

);

 

INSERT INTO authorbook

VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),

(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);

After completing the data insertion, open the operating system command line input window and enter the following backup command:

C:\ >mysqldump -u root -p booksdb > C:/backup/booksdb_20240301.sql

Enter password: **

After you enter the password, MySQL makes a backup of the database. Use a text viewer to open the files in the C:\backup folder that you just backed up, and some of the files look like this:

-- MySQL dump 10.13  Distrib 9.0.1, for Win64 (x86_64)

--

-- Host: localhost    Database: booksdb

-- ------------------------------------------------------

-- Server version    9.0.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!50503 SET NAMES utf8mb4 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `authorbook`

--

DROP TABLE IF EXISTS `authorbook`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!50503 SET character_set_client = utf8mb4 */;

CREATE TABLE `authorbook` (

  `auth_id` int NOT NULL,

  `bk_id` int NOT NULL,

  PRIMARY KEY (`auth_id`,`bk_id`),

  KEY `bk_id` (`bk_id`),

  CONSTRAINT `authorbook_ibfk_1` FOREIGN KEY (`auth_id`) REFERENCES `authors` (`auth_id`),

  CONSTRAINT `authorbook_ibfk_2` FOREIGN KEY (`bk_id`) REFERENCES `books` (`bk_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `authorbook`

--

LOCK TABLES `authorbook` WRITE;

/*!40000 ALTER TABLE `authorbook` DISABLE KEYS */;

INSERT INTO `authorbook` VALUES (1012,11026),(1004,11028),(1001,11033),(1002,11035),(1012,11041),(1014,11069),(1003,11072),(1011,11078);

/*!40000 ALTER TABLE `authorbook` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `authors`

--

DROP TABLE IF EXISTS `authors`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!50503 SET character_set_client = utf8mb4 */;

CREATE TABLE `authors` (

  `auth_id` int NOT NULL,

  `auth_name` varchar(20) DEFAULT NULL,

  `auth_gender` char(1) DEFAULT NULL,

  PRIMARY KEY (`auth_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*!40101 SET character_set_client = @saved_cs_client */;

 

--

-- Dumping data for table `authors`

--

LOCK TABLES `authors` WRITE;

/*!40000 ALTER TABLE `authors` DISABLE KEYS */;

INSERT INTO `authors` VALUES (1001,'WriterX','f'),(1002,'WriterA','f'),(1003,'WriterB','m'),(1004,'WriterC','f'),(1011,'WriterD','f'),(1012,'WriterE','m'),(1013,'WriterF','m'),(1014,'WriterG','f'),(1015,'WriterH','f');

/*!40000 ALTER TABLE `authors` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `books`

--

DROP TABLE IF EXISTS `books`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!50503 SET character_set_client = utf8mb4 */;

CREATE TABLE `books` (

  `bk_id` int NOT NULL,

  `bk_title` varchar(50) NOT NULL,

  `copyright` year NOT NULL,

  PRIMARY KEY (`bk_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `books`

--

LOCK TABLES `books` WRITE;

/*!40000 ALTER TABLE `books` DISABLE KEYS */;

INSERT INTO `books` VALUES (11026,'Guide to MySQL 9.0',2024),(11028,'Learning C++',2005),(11033,'Study Html',2011),(11035,'How to use php',2003),(11041,'Inside VC++',2011),(11069,'MySQL professional',2009),(11072,'Teach yourself javascript',2005),(11078,'Learning MySQL',2010);

/*!40000 ALTER TABLE `books` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-07-25 18:39:42

As you can see, the backup file contains a number of pieces of information. The beginning of the file first indicates the version number of the mysqldump utility used for the backup file; then the name and host information of the backup account, as well as the name of the database that is being backed up; and finally, the version number of the MySQL server, which is 9.0.1 in this case.

Next are some SET statements that assign some system variable values to user-defined variables to ensure that the system variables of the database being restored are the same as they were during the original backup, for example:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

This SET statement assigns the value of the current system variable character_set_client to the user-defined variable @old_character_ set_client. other variables are similar.

The last few lines of the backup file use SET statements to restore the original values of the server's system variables, for example:

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

This statement assigns the value stored in the user-defined variable @old_character_set_client to the actual system variable character_set_client.

Statements in the backup file that begin with the "--" character are comment statements; statements that begin with "/*!" statements that start with "/*!" and end with "*/" are executable MySQL comments, which can be executed by MySQL but will be ignored as comments in other database management systems to improve database portability.

In addition, some of the executable comment statements in the backup file begin with a number, which represents the MySQL version number, indicating that these statements can only be executed if the specified MySQL version is 4.01.01 or higher. For example, "40101" indicates that these statements can only be executed if the MySQL version number is 4.01.01 or higher.

2. Backup a table in a single database using mysqldump

mysqldump can also backup a table in the database.

The difference between the statement to back up a table and the statement to back up all the tables in the database is to specify the name of the table to be backed up after the database name dbname.

[Example 11.2] Back up the table books in the booksDB database with the following SQL statement:

mysqldump -u root -p booksDB books > C:/backup/books_20240301.sql 

This statement creates a backup file with the name "books_20240301.sql", which contains the SET statement introduced earlier; the difference is that the file contains only the CREATE and INSERT statements for the books table.

3. Backing up multiple databases with mysqldump

If you want to backup multiple databases using mysqldump, you need to use the --databases parameter. The syntax format for backing up multiple databases is as follows:

mysqldump  –u user –h host –p --databases  [dbname, [dbname...]] > 

After using the --databases parameter, you must specify the name of at least one database, with spaces separating multiple database names.

[Example 11.3] Use mysqldump to backup the databases booksDB and test_db with the following SQL statement:

mysqldump -u root -p --databases  booksDB test_db>C:\backup\books_testDB_20240301.sql

This statement creates a backup file named "books_testDB_20240301.sql" that contains all the statements necessary to create the two databases booksDB and test_db.

In addition, you can backup all the databases in the system by using the --all-databases parameter with the following SQL statement:

mysqldump  –u user –h host –p --all-databases > 

When using the parameter --all-databases, you do not need to specify a database name.

[Example 11.4] Use mysqldump to back up all databases in the server by entering the following statement:

mysqldump  -u root -p --all-databases > C:/backup/

This statement creates a backup file with the name "" that contains backup information for all databases on the system.

11.1.2 Directly copying an entire database directory

Since MySQL tables are saved as files, you can directly copy the storage directory and files of MySQL database for backup.The location of MySQL database directory may not be the same. Settings\All Users\Application Data\MySQL\MySQL Server 9.0\data" or other user-defined directories; on Linux, the database directory is usually located in "/var/lib/ MySQL/". Under Linux, the database directory is usually located at "/var/lib/MySQL/", and the directory may be different under different Linux versions, so readers should find the directory under their own platform.

This is a simple, fast, and effective way to backup. To keep the backup consistent, you need to perform a LOCK TABLES operation on the relevant tables before the backup, and then perform a FLUSH TABLES statement on the tables.The FLUSH TABLES statement ensures that all active index pages are written to the hard disk before you start the backup, so that other clients will be allowed to continue querying the tables when the files in the database catalog are copied. Of course, it is also possible to stop the MySQL service before performing the backup operation.

This method, although simple, is not the best because it does not work with tables from the InnoDB storage engine. Data backed up using this method is best restored to the same version of the server; different versions may not be compatible.

11.1.3 Fast Backups with the MySQLhotcopy Tool

MySQLhotcopy is a Perl script originally written and provided by Tim Bunce. It uses LOCK TABLES, FLUSH TABLES and cp or scp to quickly back up a database. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database directory is located and can only back up tables of type MyISAM.MySQLhotcopy runs on UNIX systems.

The syntax format of the MySQLhotcopy command is as follows:

mysqlhotcopy db_name_1, ... db_name_n  /path/to/new_directory

db_name_1,...,db_name_n are the names of the databases to be backed up; "/path/to/new_directory" specifies the directory of the backup file.

Example 11.5: Using MySQLhotcopy to backup the database test_db to the directory "/usr/backup", the SQL statement is as follows:

mysqlhotcopy  -u root –p test_db /usr/backup

To be able to perform MySQLhotcopy, you must have access to the backed up table files, have SELECT permissions for those tables, RELOAD permissions (so that you can perform FLUSH TABLES), and LOCK TABLES permissions.

MySQLhotcopy just copies the directory where the table is located to another location, it can only be used to backup MyISAM and ARCHIVE tables, an error message will appear when backing up InnoDB type data tables. It also cannot be ported to other hardware or operating systems because it copies files in native format.