Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you the backup and recovery of MySQL, and analyze and describe it from a professional point of view. I hope you can get something after reading this article.

Full backup and recovery of MySQL

The main purpose of backup is disaster recovery. Backup can also test applications, roll back data modifications, query historical data, audit and so on.

1. The importance of data backup

In the enterprise, the value of data is very important, and the data ensures the normal operation of the enterprise business. Therefore, the security and reliability of data is the top priority of operation and maintenance, and any loss of data may have serious consequences for the enterprise. In general, there are several reasons for data loss:

Program error

Human operation error

Operation error

Disk failure

Disasters (fires, earthquakes) and theft

Third, common backup methods

II. Type of database backup

1. From the physical and logical point of view:

Database backup can be divided into physical backup and logical backup. A physical backup is a backup of physical files (such as data files, log files, etc.) of the database operating system. This type of backup is suitable for large and important databases that need to be quickly recovered when problems occur.

Physical backup can be divided into cold backup (offline backup), hot backup (online backup) and warm backup.

Cold backup: backup operation when the database is closed

Hot backup: performs a backup operation while the database is running, which relies on the log files of the database

Warm backup: backup in a database locked table (not writable, but readable)

A logical backup is a backup of database logical components (such as tables and other database objects), expressed as logical database structure (create database, create table statements) and content (insert statements or delimited text files). This type of backup is used to edit data values or small amounts of data with a table structure, or to recreate data on different machine systems.

2. From the point of view of database backup strategy:

From the point of view of database backup strategy, database backup can be divided into full backup, differential backup and incremental backup. Among them, full backup is the basis of differential and incremental backup.

Full backup: a complete backup of the data each time, that is, a backup of the entire database. The operation of backup and recovery is very simple, but there is a lot of data duplication, which will take up a lot of disk space, and the backup time is also very long.

Differential backup: back up all files that have been modified since the last full backup, the backup time is from the last full backup, the backup data will become larger and larger, when you restore the data, just restore the last full backup and the most recent differential backup.

Incremental backup: only those files that were modified after the last full backup or incremental backup will be backed up, taking the time of the last full backup or the last incremental backup as the time point, only the data changes between them are backed up, so the amount of data backed up is small, the space occupied is small, and the backup speed is fast, but when restoring, all the increments from the last full backup to the last incremental backup need to be restored in turn. Once the intermediate data is damaged, it will lead to the loss of data.

Third, common backup methods

MySQL database can be backed up in many ways, such as direct packaging of database files (physical cold backup), dedicated backup tool (mysqldump), binary log incremental backup, third-party tool backup and so on.

1. Physical cold backup

Physical cold backup needs to be in a closed state of the database, which can better ensure the integrity of the database. Physical cold backup is generally used for non-core business, which generally allows interruption. Physical cold backup is characterized by fast speed and the simplest recovery.

2. Dedicated backup tool mysqldump or mysqlhotcopy

Both the mysqldump program and mysqlhotcopy can be backed up. Mysqqldump is a common logical backup program on the client side, which can produce a set of SQL statements that are executed to reproduce the original database object definition and table data. It can dump one or more MySQL databases, back them up or transfer them to a remote SQL server. Mysqldump is more generic because it can back up various tables. Mysqlhotcopy is only available for some storage engines.

3. Incremental backup by enabling binary logs

MySQL supports incremental backups, and binary logging must be enabled for incremental backups. Binary log files provide users with replication and recovery of the information needed for database changes made after the backup point is executed. If you perform an incremental backup, which includes data modifications that have occurred since the last full backup or incremental backup, you need to refresh the binary log.

IV. Database full backup operation

1. Prepare before backup

[root@centos01] # mysqladmin-uroot password New password: Confirm new password: [root@centos01] # mysql-uroot-ppwd@123 mysql > create database benet; mysql > use benet; mysql > create table Class 1 students' scores (name char (3), class char (5), student number char (8), Chinese char (3), mathematics char (3), English char (3), ensemble char (3), primary key (student number)) Mysql > insert into Class 1 students' scores value ('Zhang San', 'Class 1', '20170822'); mysql > insert into Class 1 students' scores value ('Li Si', 'Class 1', '20170820', '95'', '115',' 115', '110',' 260') Mysql > insert into Class 1 students' scores value ('Wang Wu', 'Class 1', '20170818'); mysql > insert into Class 1 students' scores value ('Zhao Liu', 'Class 1', '20170816', '20170816'); mysql > select * from benet. The scores of the first class of students

2. Physical cold backup and recovery

Physical cold backup usually packages the database folder directly with the tar command, while the mysql service needs to be turned off using the "systemctl stop mysqld" command before the backup.

1) back up the database

Create a / bak directory as the backup data storage path and use tar to create backup files. The entire database folder backup is a full backup.

[root@centos01 ~] # systemctl stop mysqld [root@centos01 ~] mkdir / bak/ [root@centos01 ~] # tar zcf / bak/mysql_all-$ (date +% F). Mysql.gz / usr/local/mysql/data/ [root@centos01 ~] # ls / bak/-rw-r--r-- 1 root root 766598 October 31 03:57 / bak/mysql_all-2019-10-31.mysql.gz

2) restore the database

[root@centos01] mkdir test [root@centos01] # tar zxvf / bak/mysql_all-2019-10-31.mysql.gz-C. / test/ [root@centos01 data] # cd / usr/local/mysql/data/ [root@centos01 data] # rm-rf. / * [root@centos01 ~] # cd. / test/usr/local/mysql/data/ [root@centos01 date] # mv. / * / usr/local / mysql/data/ [root@centos01 ~] # systemctl start mysqld

3. Mysqldump backup and recovery

The mysqldump command allows you to export specified libraries, tables, or all libraries as SQL scripts, making it easy to use on different versions of MySQL servers.

1) backup and restore all databases

[root@centos01] # mysqldump-uroot-ppwd@123-- opt-- all-databases >. / test/benet_databases.sql [root@centos01 ~] # mysql-uroot-p Enter password: mysql > show databases +-+ | Database | +-+ | information_schema | | benet | | mysql | | performance_schema | | test | +-+ mysql > drop database benet; mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ [root@centos01 ~] # mysql-u root-p

< ./test/benet_databases.sql Enter password: mysql>

Show databases; +-+ | Database | +-+ | information_schema | | benet | | mysql | | performance_schema | | test | +-+ mysql > source. / test/benet_databases.sql

2) backup and restore the tables in the database

[root@centos01] # mysqldump-uroot-ppwd@123 benet Class 1 scores >. / test/benet_ Class 1 students' scores. SQL [root@centos01 ~] # mysql-uroot-p Enter password: mysql > use benet; mysql > drop table Class 1 scores; mysql > show tables; Empty set (0.00 sec) [root@centos01] # mysql-uroot-p benet

< ./test/benet_一班学生成绩.sql [root@centos01 ~]# mysql -uroot -p Enter password: mysql>

Use benet; Database changedmysql > show tables; +-+ | Tables_in_benet | +-+ | Grade of Class 1 | +-+ 1 row in set (0.00 sec)

V. MySQL incremental backup and recovery

Using mysqldump for full backup, the backup data is duplicated, and the backup time and recovery time are too long. An incremental backup is a backup of files or content that have been added or changed since the last backup.

1. The characteristics of MySQL incremental backup.

Unlike a full backup, an incremental backup has no duplicate data, a small amount of backup and a short time, but it is troublesome to restore, requiring the last full backup and all incremental backups after the last full backup to be recovered. and all incremental backups should be restored one by one. Incremental backups can be achieved indirectly through the binary logs provided by MySQL.

2. MySQL incremental backup and recovery

The binary log holds all operations that update or possibly update the database. The binary log starts to be recorded after starting the MySQL server, and after the file reaches the maximum value set by the binary log or receives the flush logs command, the new log file is recreated, the binary file sequence is generated, and these logs are saved to a safe storage location in time, then the incremental backup for a period of time can be completed.

To perform incremental backup of MySQL, you must first enable the binary log feature. The method to enable the binary log feature of MySQL is as follows:

[root@centos01] # vim / etc/my.cnf. Log-bin=mysql-bin [root@centos01 ~] # systemctl restart mysqld [root@centos01 ~] # ls-1 / usr/local/mysql/data/. -rw-rw---- 1 mysql mysql 27299 October 31 00:00 mysql-bin.000001-rw-rw---- 1 mysql mysql 1031892 October 31 00:00 mysql-bin.000002-rw-rw---- 1 mysql mysql 1574 October 31 14:13 mysql-bin.000003-rw-rw---- 1 mysql mysql 507535 November 1 09:37 mysql-bin.000004-rw-rw---- 1 mysql mysql 507229 November 1 09:40 mysql-bin .000005-rw-rw---- 1 mysql mysql 95 November 1 09:37 mysql-bin.indexdrwx- 2 mysql mysql 4096 October 31 00:00 performance_schemadrwxr-xr-x 2 mysql mysql 20 October 30 23:56 test

1) incremental backup

[root@centos01] # mysqladmin-uroot-ppwd@123 flush-logs [root@centos01] # ls-1 / usr/local/mysql/data/. -rw-rw---- 1 mysql mysql 27299 October 31 00:00 mysql-bin.000001-rw-rw---- 1 mysql mysql 1031892 October 31 00:00 mysql-bin.000002-rw-rw---- 1 mysql mysql 1574 October 31 14:13 mysql-bin.000003-rw-rw---- 1 mysql mysql 507535 November 1 09:37 mysql-bin.000004-rw-rw---- 1 mysql mysql 507272 November 1 09:49 mysql-bin .000005-rw-rw---- 1 mysql mysql 107 November 1 09:49 mysql-bin.000006-rw-rw---- 1 mysql mysql 11411 09:49 mysql-bin.indexdrwx- 2 mysql mysql 4096 October 31 00:00 performance_schemadrwxr-xr-x 2 mysql mysql 20 October 30 23:56 test [root@centos01] # mysql- uroot-ppwd@123 mysql > use benet Mysql > insert into Class 1 students' scores value ('Li Ning', 'Class 2', '20170824' 92P'98'); Query OK, 1 row affected (0.01 sec) mysql > insert into Class 1 students' scores value ('Chen Ming', 'Class two', '20170826' '111'', '107') Query OK, 1 row affected (0.00 sec) mysql > select * from Class 1 students' scores +-+ | name | Class | Student number | Chinese | Mathematics | English | Science Ensemble | +- -+ | Zhao Liu | Class 1 | 20170816 | 10912 | 265 | | Wang Wu | Class 1 | 20170818 | 95 | 103 | 108 | 270 | | Li Si | Class 1 | 20170820 | 95 | 11060 | Zhang San | Class 1 | 20170822 | | 20170824 | 92 | 295 | Li Ning | Class 2 | 20170824 | 92 | 98 | 105 | 235 | | Chen Ming | Class 2 | 20170826 | 107 | 96 | 204 | + -+ 6 rows in set (0.00 sec) [root@centos01] # mysqladmin-uroot-ppwd@123 flush-logs [root@centos01] # ls-1 / usr/local/mysql/data/. -rw-rw---- 1 mysql mysql 27299 October 31 00:00 mysql-bin.000001-rw-rw---- 1 mysql mysql 1031892 October 31 00:00 mysql-bin.000002-rw-rw---- 1 mysql mysql 1574 October 31 14:13 mysql-bin.000003-rw-rw---- 1 mysql mysql 507535 November 1 09:37 mysql-bin.000004-rw-rw---- 1 mysql mysql 507272 November 1 09:49 mysql-bin .000005-rw-rw---- 1 mysql mysql 649 November 1 09:58 mysql-bin.000006-rw-rw---- 1 mysql mysql 107 November 1 09:58 mysql-bin.000007-rw-rw---- 1 mysql mysql 133 November 1 09:58 mysql-bin.indexdrwx- 2 mysql mysql 4096 October 31 00:00 performance_schemadrwxr-xr-x 2 mysql mysql 20 October 30 23:56 test [ Root@centos01 ~] # cp / usr/local/mysql/data/mysql-bin.000006 / root/test/

2) simulated misoperation to delete the grade table of Class 1 students.

[root@centos01] # mysql-uroot-ppwd@123-e'drop table benet. Class one scores;'[root@centos01] # mysql-uroot-ppwd@123-e 'select * from benet. Class one scores;' ERROR 1146 (42S02) at line 1: Table 'benet.' doesn't exist

3) restore mistakenly deleted tables

[root@centos01] # mysql-uroot-ppwd@123 <. / test/benet_databases.sql [root@centos01] # mysql-uroot-ppwd@123-e 'select * from benet. The scores of the first class of students '+-+ | name | Class | Student number | Chinese | Mathematics | English | Science Ensemble | +- +-+-+ | Zhao Liu | Class 1 | 20170816 | 10912 | 265 | | Wang Wu | Class 1 | 20170818 | 95 | 103 | 108 | | Li Si | Class 1 | 20170820 | 95 | 115,110 | | | 20170822 | | Zhang San | Class 1 | 20170822 | 110 | 105 | 92 | 235 | +-+ [root@centos01 ~] # mysqlbinlog-- no-defaults / root/test/mysql-bin.000006 | mysql- u root | -p Enter password: [root@centos01] # mysql-uroot-ppwd@123-e'select * from benet. The scores of the first class of students '+-+ | name | Class | Student number | Chinese | Mathematics | English | Comprehensive Science | +-+-- -+-+ | Zhao Liu | Class 1 | 20170816 | 10912 | 265 | | Wang Wu | Class 1 | 20170818 | 95 | 103y108 | 270| Li Si | Class 1 | 20170820 | 95 | 115,110 | 260 | | | Zhang San | Class 1 | 20170822 | 110 | 92 | 92 | 235 | Li Ning | Class 2 | 20170824 | 92 | 98 | 105 | 235 | | Chen Ming | Class 2 | 20170826 | 20170826 | 96 | +-- | -+

3. Location-based recovery

[root@centos01] # mysql-uroot-ppwd@123-e'drop table benet. The scores of Class one students;'[root@centos01] # mysql-uroot-ppwd@123-e 'select * from benet. ERROR 1146 (42S02) at line 1: Table 'benet. Class 1 'doesn't exist [root@centos01] # mysql-uroot-ppwd@123 <. / test/benet_databases.sql [root@centos01] # mysql-uroot-ppwd@123-e' select * from benet. The scores of the first class of students '+-+ | name | Class | Student number | Chinese | Mathematics | English | Comprehensive Science | +-+- -+-+ | Zhao Liu | Class 1 | 20170816 | 10912 | 265 | | Wang Wu | Class 1 | 20170818 | 95 | 103 | 108 | 270 | | Li Si | Class 1 | 20170820 | 95 | 115,110 | 260 | | Zhang | Class 3 | Class 1 | 20170822 | 1105105 | 92 | 2335 | +-+ [root@centos01 ~] # mysqlbinlog-- no-defaults / root/test/mysql-bin.000006. # at 176176 # 191101 9:55:33 server id 1 end_log_pos 329 Query thread_id=9 exec_time=0 error_code=0use benetypes server id / *! /; # at 329 / 191101 9:55:33 server id 1 end_log_pos 356 Xid = 278COMITAR / # at 356 "191101 9:55:43 server id 1 end_log_pos 425 Query thread_id=9 exec_time=0 error_code=0SET timestamp 1572573343) / * [root@centos01] # mysqlbinlog-- no-defaults-- stop-position='425' / root/test/mysql-bin.000006 | mysql- uroot-p Enter password: [root@centos01 ~] # mysql- uroot-ppwd@123-e 'select * from benet. The scores of the first class of students '+-+ | name | Class | Student number | Chinese | Mathematics | English | Comprehensive Science | +-+- | | Class 1 | 20170822 | 110 | 105 | 92 | 235 | | Li Ning | Class 2 | 20170824 | 92 | 98 | 105 | +-+ |

The "--stop-position" in the above command specifies the location of the stop. if only the information of "Chen Ming" is restored and the information recovery of "Li Ning" is skipped, you can use the "--start-position" option to specify the location where the data recovery starts. At this point, the recovered data starts from the specified location to the end of the binary log file.

[root@centos01] # mysql-uroot-ppwd@123-e'drop table benet. The scores of Class one students;'[root@centos01] # mysql-uroot-ppwd@123-e 'select * from benet. ERROR 1146 (42S02) at line 1: Table 'benet. Class 1 'doesn't exist [root@centos01] # mysql- uroot-ppwd@123 <. / test/benet_databases.sql [root@centos01 ~] # mysqlbinlog-- no-defaults-- start-position='425' / root/test/mysql-bin.000006 | mysql- uroot-p Enter password: [root@centos01 ~] # mysql- uroot-ppwd@123-e' select * from benet. The scores of the first class of students '+-+ | name | Class | Student number | Chinese | Mathematics | English | Comprehensive Science | +-+- -+-+ | Zhao Liu | Class 1 | 20170816 | 10912 | 265 | | Wang Wu | Class 1 | 20170818 | 95 | 103 | 108 | 270 | | Li Si | Class 1 | 20170820 | 95 | 115,110 | 260 | | Zhang | | Class 3 | Class 1 | 20170822 | 1105,105 | 92 | 235| | Chen Ming | Class 2 | 20170826 | 111y107 | 96 | 204| +-+ |

4. Recovery based on point in time

The option to recover data based on a point-in-time is "--stop-datetime", and the specified time is also obtained from querying the binary log. By performing some operations, you can only restore the data before 9:55:43, that is, do not restore the information of "Chen Ming".

[root@centos01] # mysql-uroot-ppwd@123-e'drop table benet. The scores of Class one students;'[root@centos01] # mysql-uroot-ppwd@123-e 'select * from benet. ERROR 1146 (42S02) at line 1: Table 'benet. Class I students' doesn't exist [root@centos01 ~] # mysql- uroot-ppwd@123 <. / test/benet_databases.sql [root@centos01 ~] # mysqlbinlog-- no-defaults-- stop-datetime='2019-11-019: 55test/benet_databases.sql 43'/ root/test/mysql-bin.000006 | mysql- uroot-p Enter password: [root@centos01 ~] # Mysql-uroot-ppwd@123-e'select * from benet. The scores of the first class of students '+-+ | name | Class | Student number | Chinese | Mathematics | English | Science Ensemble | +-+ -+-+ | Zhao Liu | Class 1 | 20170816 | 10912 | 265 | | Wang Wu | Class 1 | 20170818 | 95 | 103y108 | 270| Li Si | Class 1 | 20170820 | 95 | 115,110 | | | 20170822 | | Zhang San | Class 1 | 20170822 | 110 | 105 | 92 | 235 | | Li Ning | Class 2 | 20170824 | 92 | 98 | 105 | +-+ |

Do the following to restore only the information of "Chen Ming" and skip the information recovery of "Li Ning"

[root@centos01] # mysql-uroot-ppwd@123-e'drop table benet. The scores of Class one students;'[root@centos01] # mysql-uroot-ppwd@123-e 'select * from benet. ERROR 1146 (42S02) at line 1: Table 'benet. Class 1 'doesn't exist [root@centos01 ~] # mysql- uroot-ppwd@123 <. / test/benet_databases.sql [root@centos01 ~] # mysqlbinlog-- no-defaults-- start-datetime='2019-11-019: 55:43'/root/test/mysql-bin.000006 | mysql- uroot-p Enter password: [root@centos01 ~ ] # mysql-uroot-ppwd@123-e'select * from benet. The scores of the first class of students '+-+ | name | Class | Student number | Chinese | Mathematics | English | Comprehensive Science | +-+-- -+-+ | Zhao Liu | Class 1 | 20170816 | 10912 | 265 | | Wang Wu | Class 1 | 20170818 | 95 | 103y108 | 270| Li Si | Class 1 | 20170820 | 95 | 115,110 | 260 | | | Zhang San | Class 1 | 20170822 | 110 | 105 | 92 | 235 | | Chen Ming | Class 2 | 20170826 | 1117,107 | 96 | 204| +-+ |

So much for the backup and recovery of MySQL. I hope the above content can be of some help to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.