preface

The key to secure database management is to back up the database regularly. Select the most suitable backup method based on the data volume, the number of MySQL servers, and the database workload. Backup is classified into the following types:

  • Cold standby: library shutdown, service shutdown, and backup
  • Temperature: keep the database, lock the table when backup
  • Hot backup: The database is not stored. Tables are not locked during backup

The backup

By backup type

Cold standby

If you can shut down the MySQL server, you can do a physical backup. The benefits are that the integrity of the database is guaranteed, the backup process is simple, and the recovery time is relatively fast. But only if shutting down the MySQL server means the system needs to shut down.

Cold standby process

  • The MySQL service is stopped
  • Copy InnoDB data files (.ibdata files and.ibd files)
  • Copy InnoDB logfile (ib_logfile file)
  • Copy all my.cnf configuration files

WenBei

Temperature preparation is performed during the running of the database, but may affect the operations on the current database. For example, add a global read lock to ensure the consistency of backup data.

Hot standby

In contrast to cold backup, hot backup is the backup when the database is running and does not affect existing services. Hot backup can be divided into logical backup and raw file backup.

Logical backup

Logical backup means that the contents of the files backed up are readable, usually text files. The content consists of SQL statements or actual data in the table. The advantage of this method is that the content of the exported file can be observed. It is generally applicable to the upgrade and migration of the database. But the disadvantage is that the recovery time is often longer.

The mysqldump tools

Mysqldump is a command tool provided by MySQL. The backup process of mysqldump is to find the data to be backed up from the buffer. If it is not in the buffer, it is removed from the disk to find the buffer and back up again. Finally, an editable backup file is formed.

The backup

# Backup database
mysqldump [arguments] > file_name

[arguments] :
--host(-h)				# Server IP address
--port(-p)				# Server port number
--user(-u)				# MySQL user name
--pasword(-p)			# MySQL password
--databases			# Specifies the database to back up
--all-databases		# Backup all databases
--databases d1 d2	# Back up the specified database
--compact			# Compression mode, producing less output
--comments			# Add comment information
--single-transaction test 	Before backing up the test database, run the start Transaction command to ensure backup consistency
--lock-tables		Lock all database tables before backup
--add-locks			Lock database table when backing up database table
--force				# Continue the backup operation when an error occurs
--default-character-set		Specifies the default character set
Copy the code

Execute the backup command:

mysqldump --single-transaction -uroot -p123456 dbname > file_name

After backing up SQL files, if MySQL deployed by Docker is used, transfer THE SQL files from the container to the host, and run the following command on the host:

Docker CP container ID: DIRECTORY of SQL files in the container Host directory where SQL files are transferred

To view the contents of the SQL file:

-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database: fei2_backup
-- ------------------------------------------------------
- Server version 8.0.26

/ *! 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 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED=/ *! 80000 a '+' * / '62cd056a-e9f1-11eb-9218-0242ac110002:1-12';

--
-- Table structure for table `tb_class`
--

DROP TABLE IF EXISTS `tb_class`;
/ *! 40101 SET @saved_cs_client = @@character_set_client */;
/ *! 50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tb_class` (
  `id` int NOT NULL,
  `name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/ *! 40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tb_class`
--

LOCK TABLES `tb_class` WRITE;
/ *! 40000 ALTER TABLE `tb_class` DISABLE KEYS */;
INSERT INTO `tb_class` VALUES (1.'zhangsan'), (2.'lisi');
/ *! 40000 ALTER TABLE `tb_class` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/ *! 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 2021-07-24 1:34:54
Copy the code

restore

Execute this SQL file and execute the command in the container:

mysql -uroot -p123456 dbname < file_name

The select… into outfile

The select… Into Outfile is also a logical backup method, which is faster to restore than insert. Unlike mysqldump, which has many backup functions, it only backs up the data in the table, and does not contain the structure of the table. If the table is “dropped” after the backup is complete, the restore operation cannot be implemented. It exports the backup data to a text file and loads data to restore the data.

Common grammar:

select col1,col2... From table_name into outfile '/path/ backup file name '

The backup

Enter the mysql database and run the following command:

select * from user into outfile '/var/lib/mysql/tt.sql';

The output path is the path specified by mysql. Run the following command to query the output path.

show variables like 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | The/var/lib/mysql / | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

After the backup is successful, clear the data in test table user and load data to import the backup data files.

restore

mysql> LOAD DATA INFILE '/var/lib/mysql-files/tt.sql' INTO TABLE test.user; 2. To be deflected or Skipped. 2. To be deflected or Skipped. 2. To be deflected or SkippedCopy the code

Compared to insert, load data is about 12 times faster than INSERT.

others

The above two logical backup methods are introduced. There are other logical backup methods, such as myDumper and mysqlimport. These two logical backup methods are built-in tools of MySQL.

Mysqldump is a tool that supports single-thread work and can only export tables one by one. Mydumper is a high-performance multithreaded backup tool. The backup speed is much faster than mysqldump, and its backup mode is also logical backup. Data restore using myLoader tool, commonly known as “small steel gun”.

Mysqlimport is a command line program provided by MySQL. Essentially, it is the interface to the load Data Infile command, and most of the options are the same as the load Data Infile syntax. Unlike load Data infile, mysqlimport can import different files concurrently using the –user-thread parameter.

Raw File Backup

Raw file backup refers to the replication of the physical files of the database, either while the database is running or when the database is stopped. Because the data files are copied from the underlying layer, it is faster than logically backing up a single insert SQL statement. A good example of a raw file backup is XtraBackup

XtraBackup

XtraBackup is an open source project from Percona. The backup and recovery process is fast, secure and reliable, and the backup process does not lock tables and does not affect the existing business. However, it is currently unable to back up table structure files and other non-transactional types of tables. XtraBackup includes two tools: XtraBackup and Innobackupex:

  • Xtrabackup can only backup tables for InnoDB and Xtradb engines, but not for MyISAM engines
  • Innobackupex isa Perl script that encapsulates Xtrabackup. It supports backing up both InnoDB and Myisam, but requires a global read lock when backing up Myisam. Also, MyISAM does not support incremental backup

XtraBackup principle

XtraBackup is based on InnoDB’s Crash Recovery feature.

InnoDB internally maintains a redo log that contains all changes to InnoDB data. When InnoDB starts, it checks the Datafile and transaction log, rolls forward all committed transactions and rolls back uncommitted transactions.

XtraBackup does not lock tables, but copies InnoDB’s data page by page, so that the copied data is inconsistent. XtraBackup has another thread that monitors the redo log. Because the redo log is fixed in size and is written in a loop, the redo log is written from scratch after the last log is filled. That might overwrite the previous data. Once the log files have changed, copy the changed log pages and stop copying the redo log after all data files have been copied

XtraBackup advantages

  • Fast and reliable backup
  • The backup process does not interrupt ongoing transactions (no need to lock tables)
  • It can save disk space and traffic based on compression
  • Automatic backup check
  • Fast reduction speed
  • The backup can be transmitted to another machine
  • Back up data without adding to server load

By backup content

According to the contents of the backup database, the backup can be divided into:

  • A full backup
  • Incremental backup
  • Log backups

A full backup is a full backup of the database. Incremental backup backs up the changed data based on the last full backup. Log backup refers to the backup of the binlog of the MySQL database. Through a full backup of binlog replay, the point-in-time recovery of the database is completed. The principle of MySQL database replication is to apply the binlog redo transfer to the slave database asynchronously and in real time.

conclusion

This paper introduces some common backup methods of MySQL database, including cold backup, warm backup and hot backup. Hot backup includes logical backup, raw file backup, and full backup, incremental backup, and log backup based on the capacity. In actual backup, we will select a proper backup tool based on the actual situation. In addition, you can specify a backup policy based on the actual data volume and the frequency of full backup and backup addition.