MySQL备份和恢复方法汇总

博主： Tehran
发布时间：2021 年 10 月 27 日
370 次浏览
暂无评论
79016字数
分类： Mysql

一：CTAS
二：create table table_name_bak like table_name;
三：SELECT * FROM t1 INTO OUTFILE/load data infile/mysqlimport
四: mysqldump
五: mysqlbinlog恢复
六：mysqldumper/myloader
七：xtrabackup/innobackupex
八: MEB(MySQL Enterprise Backup)
九：冷备
十：闪回
十一: 快照备份
十二：mysqlhotcopy

Mysql备份类型

按照备份时对数据库的影响分为

Hot backup（热备）：也叫在线备份。指在数据库运行中直接备份，对正在运行的数据库没有任何影响。
Cold backup（冷备）：也叫离线备份。指在数据库停止的情况下备份。
Warm backup（温备）：在数据库运行时备份，会加一个全局锁以保证数据的一致性，会对当前数据库的操作有影响

创建测试数据：

MySQL [cjcdb]> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [cjcdb]> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

MySQL [cjcdb]> create index i_t1_id on t1(id);

MySQL [cjcdb]> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | i_t1_id  |            1 | id          | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

方式一：CTAS

表级备份，备份到数据库。

MySQL [cjcdb]> create table t1_bak as select * from t1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

MySQL [cjcdb]> create table t1_bak as select * from t1 where 1=2;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

由于启动了GTID，不允许CTAS操作。

因为create table ...select语句会生成两个sql，一个是DDL创建表SQL，一个是insert into插入数据的sql。

由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID，如果强制执行会导致和上面更新非事务引擎一样的结果。

但是不能因为执行不了CTAS而关闭GTID，可以将CTAS操作拆成如下两部分。

craete table xx as ...用来创建相同表结构并复制源表数据,不包含索引等。

create table xx like ...用来创建完整表结构和全部索引。

方式二：create table table_name_bak like table_name;

MySQL [cjcdb]> create table t1_bak like t1;
Query OK, 0 rows affected (0.04 sec)

MySQL [cjcdb]> select * from t1_bak;
Empty set (0.00 sec)

MySQL [cjcdb]> show create table t1_bak;
+--------+--------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                       |
+--------+--------------------------------------------------------------------------------------------------------------------+
| t1_bak | CREATE TABLE `t1_bak` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [cjcdb]> show index from t1_bak;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1_bak |          1 | i_t1_id  |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

MySQL [cjcdb]> insert into t1_bak select * from t1;
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0

MySQL [cjcdb]> select * from t1_bak;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

还原备份的表

MySQL [cjcdb]> rename table t1 to t1_old;
Query OK, 0 rows affected (0.04 sec)

MySQL [cjcdb]> rename table t1_bak to t1;
Query OK, 0 rows affected (0.05 sec)

MySQL [cjcdb]> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [cjcdb]> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | i_t1_id  |            1 | id          | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

MySQL [cjcdb]> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

方式三：select into outfile

Mysql select into outfile命令

在Mysql中，与load data infile命令作用相反的一个命令是select into outfile命令。

MySQL [cjcdb]> SELECT * FROM t1 INTO OUTFILE "/home/mysql/t1_001.txt";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

MySQL [cjcdb]> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

查看 secure_file_priv 的值，默认为NULL，表示限制不能导入导出。

查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。
secure_file_priv 为 tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。
secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

又因为secure_file_priv 参数是只读参数，不能使用set global命令修改。

MySQL [cjcdb]> set global secure_file_priv='/home/mysql';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
[root@mysql01 ~]# vim etc/my.cnf
secure_file_priv='/home/mysql'

[mysql@mysql01 ~]$ service mysqld restart
Shutting down MySQL............ SUCCESS! 
Starting MySQL. SUCCESS!

MySQL [(none)]> show variables like '%secure%';
+--------------------------+--------------+
| Variable_name            | Value        |
+--------------------------+--------------+
| require_secure_transport | OFF          |
| secure_auth              | ON           |
| secure_file_priv         | home/mysql/ |
+--------------------------+--------------+
3 rows in set (0.00 sec)

MySQL [(none)]> use cjcdb;
MySQL [cjcdb]> SELECT * FROM t1 INTO OUTFILE "/home/mysql/t1_001.txt";
Query OK, 9 rows affected (0.00 sec)

也可以指定分隔符和包围符。

MySQL [cjcdb]> SELECT * FROM t1 INTO OUTFILE "/home/mysql/t1_002.txt" FIELDS TERMINATED BY "," ENCLOSED BY '"';
Query OK, 9 rows affected (0.00 sec)

MySQL [cjcdb]> system cat home/mysql/t1_001.txt
1
2
3
4
5
6
7
8
9

MySQL [cjcdb]> system cat home/mysql/t1_002.txt
"1"
"2"
"3"
"4"
"5"
"6"
"7"
"8"
"9"

恢复：

MySQL [cjcdb]> help load data;
Name: 'LOAD DATA'
Description:
Syntax:
LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]
......

MySQL [cjcdb]> delete from t1;
Query OK, 9 rows affected (0.03 sec)

MySQL [cjcdb]> select * from t1;
Empty set (0.00 sec)

MySQL [cjcdb]> load data infile "/home/mysql/t1_001.txt" into table t1;
Query OK, 9 rows affected (0.03 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [cjcdb]> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

mysqlimport客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。

mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。

[mysql@mysql01 ~]$ mysql -uroot -p cjcdb -e "select count(*) from t1"
Enter password: 
+----------+
| count(*) |
+----------+
|        9 |
+----------+

mysql -uroot -p cjcdb -e "delete  from t1"

[mysql@mysql01 ~]$ mysqlimport -u root -p --local cjcdb "/home/mysql/t1_001.txt"
Enter password: 
mysqlimport: Error: 1146, Table 'cjcdb.t1_001' doesn't exist, when using table: t1_001

[mysql@mysql01 ~]$ mv t1_001.txt t1.txt
[mysql@mysql01 ~]$ mysqlimport -u root -p --local cjcdb "/home/mysql/t1.txt"
Enter password: 
cjcdb.t1: Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

[mysql@mysql01 ~]$ mysql -uroot -p cjcdb -e "select count(*) from t1"
Enter password: 
+----------+
| count(*) |
+----------+
|        9 |
+----------+

mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
password *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

$ mysqlimport -u root -p --local --columns=b,c,a \

    mytbl dump.txt
password *****

mysqlimport -uroot -p cjcdb home/mysql/t1_001.txt 

[mysql@mysql01 ~]$ man mysqlimport
shell> mysqlimport --local test imptest.txt
           test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
           shell> mysql -e ´SELECT * FROM imptest´ test
           +------+---------------+
           | id   | n             |
           +------+---------------+
           |  100 | Max Sydow     |
           |  101 | Count Dracula |
           +------+---------------+

方式四：Mysqldump

mysqldump是逻辑备份工具，支持MyISAM和InnoDB引擎。数据库运行时，MyISAM引擎只支持温备，InnoDB支持热备和温备。

7.4.1 Dumping Data in SQL Format with mysqldump
https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html

默认情况下，mysqldump将信息作为SQL语句写入标准输出。可以将输出保存在文件中：

备份所有数据库

要转储所有数据库，请使用--all databases选项调用mysqldump。

[mysql@mysql01 backup]$ mysqldump -uroot -p --all-databases > dump.sql
[mysql@mysql01 backup]$ ll -rth
total 1.5M
-rw-rw-r-- 1 mysql mysql 1.5M Sep 19 13:27 dump.sql
[mysql@mysql01 backup]$ vim dump.sql
--
-- Current Database: `cjcdb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cjcdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `cjcdb`;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `t1_old`
--
......
--
-- Current Database: `mysql`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mysql`;
--
-- Table structure for table `columns_priv`
--
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `columns_priv`
--
......

备份指定数据库:

要仅转储特定数据库，请在命令行中命名它们并使用--databases选项：

mysqldump -uroot -p --databases cjc cjcdb > dump1.sql

--databases选项使命令行上的所有名称都被视为数据库名称。

如果没有此选项，mysqldump将第一个名称视为数据库名称，后面的名称视为表名称。

例如：

导出cjc库test1表。

mysqldump -uroot -p cjc test1 > dump2.sql

对于--all databases或--databases，mysqldump在每个数据库的转储输出之前编写CREATE DATABASE和USE语句。

这样可以确保在重新加载转储文件时，如果每个数据库不存在，它将创建每个数据库，并使其成为默认数据库，以便将数据库内容加载到它们来自的同一个数据库中。

如果要使转储文件在重新创建数据库之前强制删除每个数据库，请同时使用--add drop database选项。

在本例中，mysqldump在每条CREATE DATABASE语句之前写入一条DROP DATABASE语句。

例如：

备份cjc数据库，同时在转储文件中记录删除cjc数据库的语句。

mysqldump -uroot -p --databases --add-drop-database cjc  > dump3.sql

如果不加--databases选项，转储文件中不会记录包含CREATE DATABASE或USE语句。

例如：

mysqldump -uroot -p cjc  > dump4.sql

仅导出表结构，不导出数据。

mysqldump -uroot -p --no-data cjcdb t1 > dump6.sql

对于仅定义转储，添加--routines和--events选项，以还包括存储过程和事件定义：

mysqldump -uroot -p --no-data --routines --events test > dump-defs.sql

仅导出表数据，不导出表结构。

mysqldump -uroot -p --no-create-info cjcdb t1 > dump7.sql

MySQLdump 备份时如何保持数据的一致性( –single-transaction)

MySQLdump备份不得不提的两个关键参数

--single-transcation
--master-data=2

--master-data=2，--master-data=1 默认的话会--lock-all-tables，会锁住整个mysql数据库中的所有表。但是如果加上--single-transaction会加上事务，不会锁表。

--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；
--master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；
 
--dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；
--dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；

关于mysqldump工作原理

1.调用FWRL（flush tables with read lock）,全局禁止读写
2.开启快照读，获取此期间的快照（仅仅对innodb起作用）
3.备份非innodb表数据（*.frm,*.myi,*.myd等）
4.非innodb表备份完毕之后，释放FTWRL
5.逐一备份innodb表数据
6.备份完成

mysqldump -uroot -p --databases cjcdb --single-transaction --master-data=2 --default-character-set=utf8mb4 --flush-logs --quick > dump8.sql

查看：

-- CHANGE MASTER TO MASTER_LOG_FILE='on.000010', MASTER_LOG_POS=194;
--
-- Current Database: `cjcdb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cjcdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `cjcdb`;
......
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

–single-transaction 实际上通过做了下面两个操作 ：
(1)在开始的时候把该 session 的事务隔离级别设置成 repeatable read ；
(2)然后启动一个事务（执行 begin ），备份结束的时候结束该事务（执行 commit ）

有了这两个操作，在备份过程中，该 session 读到的数据都是启动备份时的数据（同一个点）。
可以理解为对于 innodb 引擎来说加了该参数，备份开始时就已经把要备份的数据定下来了，备份过程中的提交的事务时是看不到的，也不会备份进去。

对于不支持事务的引擎如 MyISAM ，只能通过锁表来保证数据一致性，这里分三种情况：
(1)导出全库 ：加 –lock-all-tables 参数 ， 这会在备份开始的时候启动一个全局读锁 （ 执行 flush tables with read lock ）， 其他 session 可以读取但不能更新数据 ， 备份过程中数据没有变化 ， 所以最终得到的数据肯定是完全一致的 ；
(3)导出单个库：加 –lock-tables 参数，这会在备份开始的时候锁该库的所有表，其他 session 可以读但不能更新该库的所有表，该库的数据一致；

恢复数据库:

恢复所有数据库：

mysql -uroot -p  <  dumpxxx.sql
或
mysql> show databases;
mysql> source dump.sql

方式五：mysqlbinlog恢复数据

mysqlbinlog常见的选项：
a、--start-datetime
b、--stop-datetime
c、--start-position
d、--stop-position

flush logs

flush 刷新log日志，自此刻开始产生一个新编号的binlog日志文件;

注意：

每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；

在mysqlddump备份数据时加-F选项也会刷新binlog日志；

reset master

重置（清空）所有binlog日志

a、查询最近的binlog日志：
show binlog events\G;

b、指定查询mysql-bin.000002这个文件
show binlog events in 'mysql-bin.000002'\G;

c、指定查询mysql-bin.000002这个文件，从pos点:624开始查起：
show binlog events in 'mysql-bin.000002' from 624\G;

d、指定查询mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）
show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

e、指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个）查询10条（即10条语句）。
show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

恢复场景如下：

备份cjcdb01库数据：

insert into t2 values(1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');
MySQL [cjcdb01]> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
|    4 | a    |
|    5 | a    |
+------+------+
5 rows in set (0.00 sec)

备份：

mysqldump -uroot -p --databases cjcdb01 --single-transaction --master-data=2 > /home/mysql/backup/dump03.sql

insert into t2 values(6,'a'),(7,'a');
update t2 set name='b' where id=5;
insert into t2 values(8,'a');
update t2 set name='c' where id=2;
update t2 set name='d' where id=3;
insert into t2 values(9,'a');
delete from t2 where id=9;
insert into t2 values(9,'f');

模拟误删除：

delete from t2;

MySQL [cjcdb01]> select * from t2;
Empty set (0.00 sec)

恢复：

查看备份结束位置

[mysql@cjcos02 backup]$ cat dump03.sql |grep MASTER
-- CHANGE MASTER TO MASTER_LOG_FILE='on.000005', MASTER_LOG_POS=7741;

MySQL [cjcdb01]> show binary logs;
+-----------+-----------+
| Log_name  | File_size |
+-----------+-----------+
| on.000001 |       177 |
| on.000002 |       177 |
| on.000003 |       177 |
| on.000004 |       573 |
| on.000005 |     10204 |
+-----------+-----------+
5 rows in set (0.00 sec)

MySQL [cjcdb01]> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

MySQL [cjcdb01]> SHOW BINLOG EVENTS IN 'on.000005' FROM 7741;
+-----------+-------+-------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name  | Pos   | Event_type  | Server_id | End_log_pos | Info                                                               |
+-----------+-------+-------------+-----------+-------------+--------------------------------------------------------------------+
| on.000005 |  7741 | Gtid        |         1 |        7806 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:33' |
| on.000005 |  7806 | Query       |         1 |        7881 | BEGIN                                                              |
| on.000005 |  7881 | Table_map   |         1 |        7932 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  7932 | Write_rows  |         1 |        7981 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  7981 | Xid         |         1 |        8012 | COMMIT /* xid=236 */                                               |
| on.000005 |  8012 | Gtid        |         1 |        8077 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:34' |
| on.000005 |  8077 | Query       |         1 |        8152 | BEGIN                                                              |
| on.000005 |  8152 | Table_map   |         1 |        8203 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  8203 | Update_rows |         1 |        8253 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  8253 | Xid         |         1 |        8284 | COMMIT /* xid=237 */                                               |
| on.000005 |  8284 | Gtid        |         1 |        8349 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:35' |
| on.000005 |  8349 | Query       |         1 |        8424 | BEGIN                                                              |
| on.000005 |  8424 | Table_map   |         1 |        8475 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  8475 | Write_rows  |         1 |        8517 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  8517 | Xid         |         1 |        8548 | COMMIT /* xid=238 */                                               |
| on.000005 |  8548 | Gtid        |         1 |        8613 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:36' |
| on.000005 |  8613 | Query       |         1 |        8688 | BEGIN                                                              |
| on.000005 |  8688 | Table_map   |         1 |        8739 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  8739 | Update_rows |         1 |        8789 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  8789 | Xid         |         1 |        8820 | COMMIT /* xid=239 */                                               |
| on.000005 |  8820 | Gtid        |         1 |        8885 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:37' |
| on.000005 |  8885 | Query       |         1 |        8960 | BEGIN                                                              |
| on.000005 |  8960 | Table_map   |         1 |        9011 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  9011 | Update_rows |         1 |        9061 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  9061 | Xid         |         1 |        9092 | COMMIT /* xid=240 */                                               |
| on.000005 |  9092 | Gtid        |         1 |        9157 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:38' |
| on.000005 |  9157 | Query       |         1 |        9232 | BEGIN                                                              |
| on.000005 |  9232 | Table_map   |         1 |        9283 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  9283 | Write_rows  |         1 |        9325 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  9325 | Xid         |         1 |        9356 | COMMIT /* xid=241 */                                               |
| on.000005 |  9356 | Gtid        |         1 |        9421 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:39' |
| on.000005 |  9421 | Query       |         1 |        9496 | BEGIN                                                              |
| on.000005 |  9496 | Table_map   |         1 |        9547 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  9547 | Delete_rows |         1 |        9589 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  9589 | Xid         |         1 |        9620 | COMMIT /* xid=242 */                                               |
| on.000005 |  9620 | Gtid        |         1 |        9685 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:40' |
| on.000005 |  9685 | Query       |         1 |        9760 | BEGIN                                                              |
| on.000005 |  9760 | Table_map   |         1 |        9811 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 |  9811 | Write_rows  |         1 |        9853 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 |  9853 | Xid         |         1 |        9884 | COMMIT /* xid=244 */                                               |
| on.000005 |  9884 | Gtid        |         1 |        9949 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:41' |
| on.000005 |  9949 | Query       |         1 |       10024 | BEGIN                                                              |
| on.000005 | 10024 | Table_map   |         1 |       10075 | table_id: 114 (cjcdb01.t2)                                         |
| on.000005 | 10075 | Delete_rows |         1 |       10173 | table_id: 114 flags: STMT_END_F                                    |
| on.000005 | 10173 | Xid         |         1 |       10204 | COMMIT /* xid=245 */                                               |
+-----------+-------+-------------+-----------+-------------+--------------------------------------------------------------------+
45 rows in set (0.00 sec)

第一次delete位置在9547，属于正常操作，第二次delete位置在10075属性误操作。

[mysql@cjcos02 backup]$ mysql -uroot -p -e"show binlog events in 'on.000005'"|grep Delete_rows
Enter password: 
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name  | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
on.0000055965Delete_rows16077table_id: 112 flags: STMT_END_F
on.0000057355Delete_rows17418table_id: 112 flags: STMT_END_F
on.0000059547Delete_rows19589table_id: 114 flags: STMT_END_F

获取表结构：

MySQL [cjcdb01]> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过备份文件获取t2表结构：

[mysql@cjcos02 ~]$ sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t2`/!d;q' dump01.sql

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

通过备份文件获取t2表数据：

[mysql@cjcos02 backup]$ grep 'INSERT INTO `t2`' dump03.sql
INSERT INTO `t2` VALUES (1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');

通过备份恢复表数据

rename table t2 to t2_bak;
create table t2 like t2_bak;
INSERT INTO `t2` VALUES (1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');
/usr/local/mysql/bin/mysqlbinlog --start-position=7741 --stop-position=10075 --database=cjcdb01 usr/local/mysql/data/on.000005 | usr/local/mysql/bin/mysql -u root -p

通过binlog获取数据

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2021-09-20 12:15:34" usr/local/mysql/data/on.000005

[mysql@cjcos02 data]$ usr/local/mysql/bin/mysqlbinlog --start-datetime="2021-09-20 12:15:34" usr/local/mysql/data/on.000005|grep -i del
DELIMITER *!*/;
#210920 12:34:53 server id 1  end_log_pos 6077 CRC32 0x01c2c864 Delete_rows: table id 112 flags: STMT_END_F
DELIMITER ;

/usr/local/mysql/bin/mysqlbinlog usr/local/mysql/data/on.000005 --start-datetime="2021-09-20 12:15:34" --stop-position 6077 >2.sql
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/on.000005 --start-datetime="2021-09-20 12:15:34" --stop-position 6077|mysql -uroot -p
/usr/local/mysql/bin/mysqlbinlog usr/local/mysql/data/on.000005 --start-datetime="2021-09-20 12:15:34" --skip-gtids --stop-position=6077 >01.sql

具体恢复方法，参考：MySQL备份和恢复

http://blog.itpub.net/29785807/viewspace-2156430/

方式六：mysqldumper/myloader

参考链接：https://www.jianshu.com/p/65f33c7eb630

mysqldump无法并行，这点与Oracle的expdp相比，存在一定的劣势，但是开源的mysqldumper是一个很好的补充
mydumper（http://www.mysqldumper.org/）是一个在GNU GPLv3许可下发布的高性能MySQL备份和恢复工具集。
mydumper是多线程的，他创建一个mysql备份就比随mysql发布的mysqldump工具要快得多。
mydumper也有从源端服务器恢复二进制日志的能力。

mysqldumper优点

1.多线程，可以是转存数据快很多
2.mydumper的输出已于管理和分析，因为他的表和元数据是分开的单独文件。
3.所有线程都维护有一直的快照，这边提供了精准的主从位置。
4.Mydumper支持Perl正则表达式，这样就既可以包括是数据库名和报名的模式匹配，也可以配置这种匹配。
5.通过名为myloader的多线程工具，mydumper工具集也可以从mydumper备份中恢复数据。

主要备份步骤概括

1.主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，以阻止DML语句写入，保证数据的一致性
2.读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用
3.N个（线程数可以指定，默认是4）dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT; 开启读一致的事物
4.dump non-InnoDB tables, 首先导出非事物引擎的表
5.主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁
6.dump InnoDB tables, 基于事物导出InnoDB表
7.事务结束

mysqldumper安装

数据库版本 : MySQL 5.7
操作系统版本: CentOS 7.8
软件地址:https://github.com/maxbube/mydumper/releases

http://www.mysqldumper.org/
http://rpm.pbone.net/info_idpl_55203713_distro_redhatel7_com_mydumper-0.9.1-4.1.x86_64.rpm.html

yum -y install cmake glib2 pcre pcre-devel mysql-devel
###yum -y install mydumper-0.9.5-2.el7.x86_64.rpm
[root@cjcos02 mysql]# yum install mydumper-0.9.1-4.1.x86_64.rpm 
......
Running transaction
  Installing : mydumper-0.9.1-4.1.x86_64                                                                                                                                                   1/1 
  Verifying  : mydumper-0.9.1-4.1.x86_64                                                                                                                                                   1/1 

Installed:
  mydumper.x86_64 0:0.9.1-4.1  
  
[root@cjcos02 mysql]# which mydumper
/usr/bin/mydumper
[root@cjcos02 mysql]# which myloader
/usr/bin/myloader

[root@cjcos02 mysql]# mydumper --help

Usage:
  mydumper [OPTION?] multi-threaded MySQL dumping

Help Options:
  -?, --help                  Show help options
Application Options:
  -B, --database              Database to dump
  -T, --tables-list           Comma delimited table list to dump (does not exclude regex option)
  -o, --outputdir             Directory to output files to
  -s, --statement-size        Attempted size of INSERT statement in bytes, default 1000000
  -r, --rows                  Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
  -F, --chunk-filesize        Split tables into chunks of this output file size. This value is in MB
  -c, --compress              Compress output files
  -e, --build-empty-files     Build dump files even if no data available from table
  -x, --regex                 Regular expression for 'db.table' matching
  -i, --ignore-engines        Comma delimited list of storage engines to ignore
  -m, --no-schemas            Do not dump table schemas with the data
  -d, --no-data               Do not dump table data
  -G, --triggers              Dump triggers
  -E, --events                Dump events
  -R, --routines              Dump stored procedures and functions
  -k, --no-locks              Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups
  --less-locking              Minimize locking time on InnoDB tables.
  -l, --long-query-guard      Set long query timer in seconds, default 60
  -K, --kill-long-queries     Kill long running queries (instead of aborting)
  -D, --daemon                Enable daemon mode
  -I, --snapshot-interval     Interval between each dump snapshot (in minutes), requires --daemon, default 60
  -L, --logfile               Log file name to use, by default stdout is used
  --tz-utc                    SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
  --skip-tz-utc               
  --use-savepoints            Use savepoints to reduce metadata locking issues, needs SUPER privilege
  --success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist
  --lock-all-tables           Use LOCK TABLE for all, instead of FTWRL
  -U, --updated-since         Use Update_time to dump only tables updated in the last U days
  --trx-consistency-only      Transactional consistency only
  -h, --host                  The host to connect to
  -u, --user                  Username with privileges to run the dump
  -p, --password              User password
  -P, --port                  TCP/IP port to connect to
  -S, --socket                UNIX domain socket file to use for connection
  -t, --threads               Number of threads to use, default 4
  -C, --compress-protocol     Use compression on the MySQL connection
  -V, --version               Show the program version and exit
  -v, --verbose               Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

-B 指定数据库
-T 指定表
-t 指定线程数，默认4
-v 输出显示info
-L 指定日志路径和名称
-o 指定备份文件路径和名称

备份：

mydumper -u root -p 1 -B cjcdb01 -T t1,t2  -t 4 -r 100000 -c --less-locking  -v 3 -D -L home/mysql/backup/mydumper01.log   -o home/mysql/backup/
[mysql@cjcos02 backup]$ ls
0  003.sql  01.sql  1  2.sql  33.sql  dump03.sql  mydumper01.log

[mysql@cjcos02 backup]$ cat mydumper01.log 
2021-09-20 19:35:00 [INFO] - Connected to a MySQL server
2021-09-20 19:35:00 [INFO] - Started dump at: 2021-09-20 19:35:00

2021-09-20 19:35:00 [INFO] - Written master status
2021-09-20 19:35:00 [INFO] - Thread 5 connected using MySQL connection ID 24
2021-09-20 19:35:00 [INFO] - Thread 6 connected using MySQL connection ID 25
2021-09-20 19:35:00 [INFO] - Thread 7 connected using MySQL connection ID 26
2021-09-20 19:35:00 [INFO] - Thread 8 connected using MySQL connection ID 27
2021-09-20 19:35:00 [INFO] - Thread 1 connected using MySQL connection ID 28
2021-09-20 19:35:00 [INFO] - Thread 2 connected using MySQL connection ID 29
2021-09-20 19:35:00 [INFO] - Thread 3 connected using MySQL connection ID 30
2021-09-20 19:35:00 [INFO] - Thread 4 connected using MySQL connection ID 31
2021-09-20 19:35:00 [INFO] - Thread 6 shutting down
2021-09-20 19:35:00 [INFO] - Thread 7 shutting down
2021-09-20 19:35:00 [INFO] - Thread 8 shutting down
2021-09-20 19:35:00 [INFO] - Thread 5 dumping data for `cjcdb01`.`t1`
2021-09-20 19:35:00 [INFO] - Non-InnoDB dump complete, unlocking tables
2021-09-20 19:35:00 [INFO] - Thread 2 dumping data for `cjcdb01`.`t2`
2021-09-20 19:35:00 [INFO] - Thread 3 dumping schema for `cjcdb01`.`t1`
2021-09-20 19:35:00 [INFO] - Thread 4 dumping schema for `cjcdb01`.`t2`
2021-09-20 19:35:00 [INFO] - Thread 5 shutting down
2021-09-20 19:35:00 [INFO] - Thread 3 shutting down
2021-09-20 19:35:00 [INFO] - Thread 2 shutting down
2021-09-20 19:35:00 [INFO] - Thread 4 shutting down
2021-09-20 19:35:00 [INFO] - Thread 1 shutting down
2021-09-20 19:35:00 [INFO] - Finished dump at: 2021-09-20 19:35:00

查看备份文件：

[mysql@cjcos02 backup]$ ll -rth 0/
total 24K
-rw-rw-rw- 1 mysql mysql  88 Sep 20 19:35 cjcdb01-schema-create.sql.gz
-rw-rw-rw- 1 mysql mysql 404 Sep 20 19:35 cjcdb01.t1-schema.sql.gz
-rw-rw-rw- 1 mysql mysql 151 Sep 20 19:35 cjcdb01.t2.sql.gz
-rw-rw-rw- 1 mysql mysql 170 Sep 20 19:35 cjcdb01.t2-schema.sql.gz
-rw-rw-rw- 1 mysql mysql 200 Sep 20 19:35 cjcdb01.t1.sql.gz
-rw-rw-rw- 1 mysql mysql 172 Sep 20 19:35 metadata

恢复：

[mysql@cjcos02 backup]$ myloader --help
Usage:
  myloader [OPTION?] multi-threaded MySQL loader

Help Options:
  -?, --help                        Show help options

Application Options:
  -d, --directory                   Directory of the dump to import
  -q, --queries-per-transaction     Number of queries per transaction, default 1000
  -o, --overwrite-tables            Drop tables if they already exist
  -B, --database                    An alternative database to restore into
  -s, --source-db                   Database to restore
  -e, --enable-binlog               Enable binary logging of the restore data
  -h, --host                        The host to connect to
  -u, --user                        Username with privileges to run the dump
  -p, --password                    User password
  -P, --port                        TCP/IP port to connect to
  -S, --socket                      UNIX domain socket file to use for connection
  -t, --threads                     Number of threads to use, default 4
  -C, --compress-protocol           Use compression on the MySQL connection
  -V, --version                     Show the program version and exit
  -v, --verbose                     Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
  
MySQL [cjcdb01]> select * from t1;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
| localhost | sys                | mysql.sys     | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | Y            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)

MySQL [cjcdb01]> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
|    4 | a    |
|    5 | a    |
+------+------+
5 rows in set (0.00 sec)

模拟误删除：

MySQL [cjcdb01]> delete from t1;
Query OK, 2 rows affected (0.06 sec)

MySQL [cjcdb01]> delete from t2;
Query OK, 5 rows affected (0.09 sec)

MySQL [cjcdb01]> delete from t1;
Query OK, 0 rows affected (0.00 sec)

MySQL [cjcdb01]> delete from t2;
Query OK, 0 rows affected (0.00 sec)

恢复：

myloader -u root -p 1  -B cjcdb01 -e -t 8  -d home/mysql/backup/ --overwrite-tables -v 3

[mysql@cjcos02 backup]$ myloader -u root -p 1  -B cjcdb01 -e -t 8  -d home/mysql/backup/0/ --overwrite-tables -v 3
** Message: 8 threads created
** Message: Dropping table or view (if exists) `cjcdb01`.`t1`
** Message: Creating table `cjcdb01`.`t1`
** Message: Dropping table or view (if exists) `cjcdb01`.`t2`
** Message: Creating table `cjcdb01`.`t2`
** Message: Thread 1 restoring `cjcdb01`.`t1` part 0
** Message: Thread 2 restoring `cjcdb01`.`t2` part 0
** Message: Thread 3 shutting down
** Message: Thread 4 shutting down
** Message: Thread 5 shutting down
** Message: Thread 6 shutting down
** Message: Thread 7 shutting down
** Message: Thread 8 shutting down
** Message: Thread 1 shutting down
** Message: Thread 2 shutting down

MySQL [cjcdb01]> select * from t1;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
| localhost | sys                | mysql.sys     | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | Y            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)

MySQL [cjcdb01]> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
|    4 | a    |
|    5 | a    |
+------+------+
5 rows in set (0.00 sec)

方式七：xtrbackup

获取xtrabackup备份工具
网站域名：http://www.percona.com
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/#
[root@mysql01 xtrabakcup]# rpm -ivh percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm 
warning: percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
libev.so.4()(64bit) is needed by percona-xtrabackup-80-8.0.26-18.1.el7.x86_64
  
[root@mysql01 xtrabakcup]# rpm -ivh percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm --nodeps
warning: percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing...                          ################################# [100%]
Updating  installing...
   1:percona-xtrabackup-80-8.0.26-18.1################################# [100%]

查看测试数据

MySQL [cjcdb]> select * from cjcdb.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

授权：

MySQL [cjcdb]> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
Query OK, 0 rows affected, 1 warning (0.08 sec)

MySQL [cjcdb]> flush privileges;
Query OK, 0 rows affected (0.10 sec)

查看xtrabackup命令

[mysql@mysql01 ~]$ man xtrabackup
$ xtrabackup --backup --target-dir=/home/mysql/xtrabakcup --datadir=/usr/local/mysql/data/

$ xtrabackup --backup --target-dir=/data/backups/inc1 \
          --incremental-basedir=/data/backups/base --datadir=/var/lib/mysql/

xtrabackup --defaults-file=/etc/my.cnf -uadmin  -p123 --target-dir=/home/mysql/xtrabakcup

[mysql@mysql02 bin]$ ./mysqld --verbose --help |grep -A 1 "Default options"
2021-09-16T14:11:47.119008Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2021-09-16T14:11:47.119163Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Default options are read from the following files in the given order:
/etc/my.cnf etc/mysql/my.cnf usr/local/mysql/etc/my.cnf ~/.my.cnf

备份：

$ innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 home/mysql/backup/
 
 [mysql@mysql02 ~]$ innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 home/mysql/backup/xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --tmpdir=/tmp --server-id=2 --log_bin=on 
xtrabackup: recognized client arguments: 
210916 22:44:18 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

210916 22:44:20  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'admin'  (using password: YES).
210916 22:44:20  version_check Connected to MySQL server
210916 22:44:20  version_check Executing a version check against the server...
210916 22:44:30  version_check Done.
210916 22:44:30 Connecting to MySQL server host: localhost, user: admin, password: set, port: not set, socket: not set
Using server version 5.7.35-log
innobackupex version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
210916 22:44:30 >> log scanned up to (2780419)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
210916 22:44:31 [01] Copying ./ibdata1 to home/mysql/backup/2021-09-16_22-44-18/ibdata1
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/plugin.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/plugin.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/servers.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/servers.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_topic.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/help_topic.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_category.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/help_category.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_relation.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/help_relation.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_keyword.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/help_keyword.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/time_zone_name.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/time_zone_name.ibd
......
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./sys/x@0024waits_global_by_latency.frm to home/mysql/backup/2021-09-16_22-44-18/sys/x@0024waits_global_by_latency.frm
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./sys/session_ssl_status.frm to home/mysql/backup/2021-09-16_22-44-18/sys/session_ssl_status.frm
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./cjcdb/db.opt to home/mysql/backup/2021-09-16_22-44-18/cjcdb/db.opt
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./cjcdb/t1.frm to home/mysql/backup/2021-09-16_22-44-18/cjcdb/t1.frm
210916 22:44:34 [01]        ...done
210916 22:44:34 Finished backing up non-InnoDB tables and files
210916 22:44:34 [00] Writing home/mysql/backup/2021-09-16_22-44-18/xtrabackup_binlog_info
210916 22:44:34 [00]        ...done
210916 22:44:34 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2780410'
xtrabackup: Stopping log copying thread.
.210916 22:44:34 >> log scanned up to (2780419)

210916 22:44:34 Executing UNLOCK TABLES
210916 22:44:34 All tables unlocked
210916 22:44:34 [00] Copying ib_buffer_pool to home/mysql/backup/2021-09-16_22-44-18/ib_buffer_pool
210916 22:44:34 [00]        ...done
210916 22:44:34 Backup created in directory '/home/mysql/backup/2021-09-16_22-44-18/'
MySQL binlog position: filename 'on.000005', position '719', GTID of the last change 'a2c030a0-1560-11ec-8c9d-08002762e5f2:1-8,
bd1b20b5-1563-11ec-9520-080027daf155:1'
210916 22:44:34 [00] Writing home/mysql/backup/2021-09-16_22-44-18/backup-my.cnf
210916 22:44:34 [00]        ...done
210916 22:44:34 [00] Writing home/mysql/backup/2021-09-16_22-44-18/xtrabackup_info
210916 22:44:34 [00]        ...done
xtrabackup: Transaction log of lsn (2780410) to (2780419) was copied.
210916 22:44:34 completed OK!
innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 --incremental homne/mysql/backup/1/
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental backups/ --incremental-basedir=/backups/2018-07-30_11-01-37
innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 --backup --target-dir=/home/mysql/backup/

恢复：

innobackupex --apply-log backups/2018-07-30_11-04-55/
innobackupex --copy-back --defaults-file=/etc/my.cnf  backups/2018-07-30_11-04-55/

方式八：MEB

MySQL MEB备份和恢复
基于MySQL MEB的备份恢复
MEB（MySQL Enterprise Backup）是MySQL商业版中提供的备份工具，属于物理备份。
mysql enterprise版比community在备份工具上有了提升，多了一个mysqlbackup的工具，即mysql企业备份工具。
当然遵循GPL的社区版也可以用：
该工具单独下载：
到官方的下载中心：
点Sign up登录
然后搜索并选择平台，搜索mysql，找到企业版
同XtraBackup一样，mysqlbackup的使用过程同样包含如下三个步骤：
备份（--backup）=> 应用日志（--apply-log）=> 恢复（--copy-back）

https://edelivery.oracle.com/osdc/faces/SoftwareDelivery
MySQL Enterprise Backup for Server 8.0 8.0.26 for Linux x86-64
V1010830-01.zipMySQL Enterprise Backup 8.0.26 RPM for Oracle Linux  RHEL 6 x86 (64bit), 36.0 MB
V1010832-01.zipMySQL Enterprise Backup 8.0.26 RPM for Oracle Linux  RHEL 7 x86 (64bit), 24.3 MB
V1010834-01.zipMySQL Enterprise Backup 8.0.26 RPM for Oracle Linux  RHEL 8 x86 (64bit), 21.8 MB
V1010835-01.zipMySQL Enterprise Backup 8.0.26 RPM for SuSE Enterprise Linux 15  openSUSE 15 x86 (64bit), 29.0 MB
V1010836-01.zipMySQL Enterprise Backup 8.0.26 RPM for SuSE Enterprise Linux 12 x86 (64bit), 23.3 MB
V1010837-01.zipMySQL Enterprise Backup 8.0.26 TAR for Oracle Linux  RHEL 7 x86 (64bit), 36.7 MB
V1010839-01.zipMySQL Enterprise Backup 8.0.26 TAR for Generic Linux x86 (64bit), 51.7 MB
V1010849-01.zipMySQL Enterprise Backup 8.0.26 DEB for Debian 10 Linux x86 (64bit), 42.8 MB
V1010850-01.zipMySQL Enterprise Backup 8.0.26 DEB for Ubuntu 18.04 Linux x86 (64bit), 42.8 MB
V1010852-01.zipMySQL Enterprise Backup 8.0.26 DEB for Ubuntu 20.04 Linux x86 (64bit), 44.1 MB
V1010969-01.zipMySQL Enterprise Backup 8.0.26 Minimal TAR for Generic Linux (glibc2.17) x86 (64bit), 4.3 MB

[mysql@cjcos02 MEB]$ unzip V1010837-01.zip 
Archive:  V1010837-01.zip
 extracting: mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz  
 extracting: mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz.asc  
 extracting: mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz.md5  
 extracting: README.txt  
 
[mysql@cjcos02 MEB]$ tar -zxvf mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libcrypto.so
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libcrypto.so.1.1
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libssl.so
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libssl.so.1.1
mysql-commercial-backup-8.0.26-el7-x86_64/docs/INFO_BIN
mysql-commercial-backup-8.0.26-el7-x86_64/docs/INFO_SRC
mysql-commercial-backup-8.0.26-el7-x86_64/bin/mysqlbackup
mysql-commercial-backup-8.0.26-el7-x86_64/LICENSE.meb
mysql-commercial-backup-8.0.26-el7-x86_64/README.meb

[mysql@cjcos02 MEB]$ mv mysql-commercial-backup-8.0.26-el7-x86_64 usr/local/mysql/meb/
[mysql@cjcos02 MEB]$ 

http://blog.itpub.net/29773961/viewspace-1992615/
[mysql@cjcos02 bin]$ mkdir home/mysql/backup/meb

全备：

[mysql@cjcos02 bin]$ ./mysqlbackup -u root -p --backup-dir=/home/mysql/backup/meb backup-and-apply-log
MySQL Enterprise Backup  Ver 8.0.26-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starting with following command line ...
./mysqlbackup
-u
root
-p
--backup-dir=/home/mysql/backup/meb
backup-and-apply-log

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".

Enter password: 
210920 21:23:31 MAIN     INFO: Establishing connection to server.
210920 21:23:31 MAIN     INFO: No SSL options specified.
210920 21:23:31 MAIN     INFO: MySQL server version is '5.7.35-log'
210920 21:23:31 MAIN     INFO: MySQL server compile os version is 'el7'
210920 21:23:32 MAIN    ERROR: MySQL query 'SELECT member_role, member_host, member_port, member_state FROM performance_schema.replication_group_members': 1054, Unknown column 'member_role' in 'field list'
210920 21:23:32 MAIN    ERROR: Connection initialization error.

mysqlbackup exit code: 19, Server returned error while executing sql

mysqlbackup failed with errors!

5.7版本，performance_schema.replication_group_members表没有member_role字段，在使用MEB8时，会报错。

5.7
MySQL [mysql]> select version();
+------------+
| version()  |
+------------+
| 5.7.35-log |
+------------+
1 row in set (0.00 sec)

MySQL [mysql]> desc performance_schema.replication_group_members;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| CHANNEL_NAME | char(64) | NO   |     | NULL    |       |
| MEMBER_ID    | char(36) | NO   |     | NULL    |       |
| MEMBER_HOST  | char(60) | NO   |     | NULL    |       |
| MEMBER_PORT  | int(11)  | YES  |     | NULL    |       |
| MEMBER_STATE | char(64) | NO   |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
5 rows in set (0.08 sec)


8.0
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.00 sec)
mysql> desc performance_schema.replication_group_members;
+----------------+-----------+------+-----+---------+-------+
| Field          | Type      | Null | Key | Default | Extra |
+----------------+-----------+------+-----+---------+-------+
| CHANNEL_NAME   | char(64)  | NO   |     | NULL    |       |
| MEMBER_ID      | char(36)  | NO   |     | NULL    |       |
| MEMBER_HOST    | char(255) | NO   |     | NULL    |       |
| MEMBER_PORT    | int       | YES  |     | NULL    |       |
| MEMBER_STATE   | char(64)  | NO   |     | NULL    |       |
| MEMBER_ROLE    | char(64)  | NO   |     | NULL    |       |
| MEMBER_VERSION | char(64)  | NO   |     | NULL    |       |
+----------------+-----------+------+-----+---------+-------+
7 rows in set (0.03 sec)

使用Mysql8.0.26版本进行测试：

D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin>mysqlbackup.exe  --backup-dir=D:\cjc\mysql\MEB\backup backup-and-apply-log -uroot -p
MySQL Enterprise Backup  Ver 8.0.26-commercial for Win64 on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Starting with following command line ...
mysqlbackup.exe
--backup-dir=D:\cjc\mysql\MEB\backup
backup-and-apply-log
-uroot
-p


IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".


Enter password: *
210921 11:42:32 MAIN     INFO: Establishing connection to server.
210921 11:42:32 MAIN     INFO: No SSL options specified.
210921 11:42:32 MAIN     INFO: MySQL server version is '8.0.26'
210921 11:42:32 MAIN     INFO: MySQL server compile os version is 'Win64'
210921 11:42:32 MAIN     INFO: SSL/TLS version used for connection is TLSv1.3
210921 11:42:32 MAIN     INFO: Got some server configuration information from running server.


210921 11:42:32 MAIN     INFO: Establishing connection to server for locking.
210921 11:42:32 MAIN     INFO: No SSL options specified.
210921 11:42:37 MAIN     INFO: Backup directory exists: 'D:\cjc\mysql\MEB\backup'
210921 11:42:37 MAIN     INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
210921 11:42:37 MAIN     INFO: Mysqlbackup component not installed.
210921 11:42:37 MAIN     INFO: MEB logfile created at D:\cjc\mysql\MEB\backup\meta\MEB_2021-09-21.11-42-37_backup-and-apply-log.log


210921 11:42:37 MAIN     INFO: The MySQL server has no active keyring.
--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                        = D:\cjc\mysql\mysql-8.0.26-winx64\data\
  innodb_data_home_dir           =
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = D:\cjc\mysql\mysql-8.0.26-winx64\data\
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = D:\cjc\mysql\mysql-8.0.26-winx64\data\
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32
--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                        = D:\cjc\mysql\MEB\backup\datadir
  innodb_data_home_dir           = D:\cjc\mysql\MEB\backup\datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = D:\cjc\mysql\MEB\backup\datadir
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = D:\cjc\mysql\MEB\backup\datadir
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32


210921 11:42:37 MAIN     INFO: Unique generated backup id for this is 16321957525444995


210921 11:42:37 MAIN     INFO: Copying the server config file 'D:/cjc/mysql/mysql-8.0.26-winx64/data/auto.cnf'
210921 11:42:38 MAIN     INFO: Creating 14 buffers each of size 16777216.
210921 11:42:38 MAIN     INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
210921 11:42:38 MAIN     INFO: Found checkpoint at lsn 151862887.
210921 11:42:38 MAIN     INFO: Starting log scan from lsn = 151862784 at offset = 867840 and checkpoint = 151862887 in file D:/cjc/mysql/mysql-8.0.26-winx64/data/ib_logfile1.
210921 11:42:38 MAIN     INFO: Full Backup with Apply-log operation starts with following threads
                               1 read-threads    6 process-threads    1 write-threads
210921 11:42:38 MAIN     INFO: Starting to copy all innodb files...
210921 11:42:38 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/ibdata1.
210921 11:42:38 RLP1     INFO: Starting to parse redo log at lsn = 151862811, whereas checkpoint_lsn = 151862887 and start_lsn = 151862784.
210921 11:42:38 RDR1     INFO: Starting to copy all undo files...
210921 11:42:39 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/undo_002.
210921 11:42:39 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/undo_001.
210921 11:42:40 RDR1     INFO: Starting to lock instance for backup...
210921 11:42:40 RDR1     INFO: The server instance is locked for backup.
210921 11:42:41 RDR1     INFO: The server instance is unlocked after 0.826 seconds.
210921 11:42:43 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/sys/sys_config.ibd.
210921 11:42:43 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/cjcdb/t1.ibd.
210921 11:42:43 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/cjcdb/t2.ibd.
......


210921 11:43:11 PCR1     INFO: Setting 'ib_logfile0' file size to 50331648
210921 11:43:16 PCR1     INFO: Setting 'ib_logfile1' file size to 50331648
210921 11:43:16 PCR1     INFO: Log file header:
         format = 4
         pad1 = 0
         start lsn = 151937024
         checkpoint lsn = 151937202
         checksum = 4172735474
         creator = MEB 8.0.26
210921 11:43:17 PCR1     INFO: We were able to parse ibbackup_logfile up to lsn 151937202.
210921 11:43:17 PCR1     INFO: Last MySQL binlog file position 0 156, file name binlog.000002
210921 11:43:17 PCR1     INFO: The first data file is 'D:/cjc/mysql/MEB/backup/datadir/ibdata1'
                               and the new created log files are at 'D:\cjc\mysql\MEB\backup\datadir'
210921 11:43:17 MAIN     INFO: Apply-log operation completed successfully.
210921 11:43:17 MAIN     INFO: Full backup prepared for recovery successfully.


mysqlbackup completed OK!

查看备份文件

D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin>cd D:\cjc\mysql\MEB\backup


D:\cjc\mysql\MEB\backup>dir
 驱动器 D 中的卷是 LENOVO
 卷的序列号是 568B-1BF9


 D:\cjc\mysql\MEB\backup 的目录
2021/09/21  11:43    <DIR>          .
2021/09/21  11:43    <DIR>          ..
2021/09/21  11:42               325 backup-my.cnf
2021/09/21  11:43    <DIR>          datadir
2021/09/21  11:43    <DIR>          meta
2021/09/21  11:43            20,032 server-all.cnf
2021/09/21  11:43               452 server-my.cnf
               3 个文件         20,809 字节
               4 个目录 82,218,188,800 可用字节

恢复：

D:\cjc\mysql\MEB\backup
mysql> use cjcdb;
Database changed


mysql> show tables;
+-----------------+
| Tables_in_cjcdb |
+-----------------+
| t1              |
| t2              |
| t3              |
| t5              |
| t6              |
| t7              |
+-----------------+
6 rows in set (0.01 sec)


mysql> drop database cjcdb;
Query OK, 6 rows affected (5.20 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

应用日志：

D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin>mysqlbackup --backup-dir=D:\cjc\mysql\MEB\backup apply-log
MySQL Enterprise Backup  Ver 8.0.26-commercial for Win64 on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Starting with following command line ...
mysqlbackup
--backup-dir=D:\cjc\mysql\MEB\backup
apply-log


IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".


210921 15:24:48 MAIN     INFO: read_backup_variables_txt_file: 'D:\cjc\mysql\MEB\backup\meta\backup_variables.txt'
210921 15:24:48 MAIN     INFO: backup variable mysql_version=8.0.26
210921 15:24:48 MAIN     INFO: MySQL server version is '8.0.26'
210921 15:24:48 MAIN     INFO: Restoring ...8.0.26 version
210921 15:24:48 MAIN     INFO: backup variable meb_version=8.0.26
210921 15:24:48 MAIN     INFO: backup variable start_lsn=151862784
......


  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32


210921 15:24:49 MAIN     INFO: Apply-log operation has already been done on that backup.


mysqlbackup completed OK!

恢复

D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin>
mysqlbackup --defaults-file=D:\cjc\mysql\mysql-8.0.26-winx64\my.ini --datadir=D:\cjc\mysql\mysql-8.0.26-winx64\data1 --backup-dir=D:\cjc\mysql\MEB\backup  copy-back --force
 D:\cjc\mysql\mysql-8.0.26-winx64\data1 的目录


2021/09/21  17:01    <DIR>          .
2021/09/21  17:01    <DIR>          ..
2021/09/21  17:01                56 backup-auto.cnf
2021/09/21  17:01               828 backup_variables.txt
2021/09/21  17:01        37,934,399 binlog.000001
2021/09/21  17:01               156 binlog.000002
2021/09/21  17:01                32 binlog.index
2021/09/21  17:01    <DIR>          cjcdb
2021/09/21  17:01        12,582,912 ibdata1
2021/09/21  17:01             8,724 ib_buffer_pool
2021/09/21  17:01        50,331,648 ib_logfile0
2021/09/21  17:01        50,331,648 ib_logfile1
2021/09/21  17:01    <DIR>          mysql
2021/09/21  17:01        25,165,824 mysql.ibd
2021/09/21  17:01    <DIR>          performance_schema
2021/09/21  17:01            20,032 server-all.cnf
2021/09/21  17:01               452 server-my.cnf
2021/09/21  17:01    <DIR>          sys
2021/09/21  17:01        16,777,216 undo_001
2021/09/21  17:01        16,777,216 undo_002
              14 个文件    209,931,143 字节
               6 个目录 82,003,558,400 可用字节

方式九：冷备

查看当前数据：

MySQL [cjcdb01]> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
|    2 | a    |
+------+------+
3 rows in set (0.00 sec)

查看数据文件目录：

MySQL [cjcdb01]> show variables like '%datadir%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

停库：

[mysql@cjcos02 ~]$ service mysqld status
 SUCCESS! MySQL running (3941)
 
[mysql@cjcos02 ~]$ service mysqld stop
Shutting down MySQL............ SUCCESS!

备份数据文件:

[mysql@cjcos02 ~]$ cd /usr/local/mysql/
[mysql@cjcos02 mysql]$ mkdir data_bak
[mysql@cjcos02 mysql]$ cp -r data/ data_bak/


[mysql@cjcos02 mysql]$ du -sh data*
122Mdata
122Mdata_bak

启库:

[mysql@cjcos02 mysql]$ service mysqld start
Starting MySQL.. SUCCESS!

模拟误操作：

MySQL [(none)]> drop database cjcdb01;
Query OK, 4 rows affected (0.04 sec)


MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cjcdb02            |
| cjcdb03            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)


MySQL [(none)]> select * from cjcdb01.test1;
ERROR 1146 (42S02): Table 'cjcdb01.test1' doesn't exist

恢复：

[mysql@cjcos02 mysql]$ service mysqld stop
Shutting down MySQL.. SUCCESS! 
[mysql@cjcos02 mysql]$ mv data data_01
[mysql@cjcos02 mysql]$ mv data_bak/data data
[mysql@cjcos02 mysql]$ service mysqld start

验证：

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cjcdb01            |
| cjcdb02            |
| cjcdb03            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)


MySQL [(none)]> select * from cjcdb01.test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
|    2 | a    |
+------+------+
3 rows in set (0.01 sec)

方式十：闪回

开源工具binlog2sql来进行实战演练。binlog2sql由美团点评DBA团队(上海)出品，多次在线上环境做快速回滚。

安装binlog2sql：

[mysql@cjcos02 ~]$ git clone https://github.com/danfengcao/binlog2sql.git
Cloning into 'binlog2sql'...
remote: Enumerating objects: 323, done.
remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323
Receiving objects: 100% (323/323), 153.26 KiB | 0 bytes/s, done.
Resolving deltas: 100% (170/170), done.


[mysql@cjcos02 ~]$ cd binlog2sql/
[mysql@cjcos02 binlog2sql]$ ls
binlog2sql  example  LICENSE  README.md  requirements.txt  tests


[mysql@cjcos02 binlog2sql]$ cat requirements.txt 
PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13


[mysql@cjcos02 binlog2sql]$ pip install -r requirements.txt
bash: pip: command not found...


[mysql@cjcos02 binlog2sql]$ curl -O https://bootstrap.pypa.io/pip/2.7/get-pip.py
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 1863k  100 1863k    0     0  1751k      0  0:00:01  0:00:01 --:--:-- 1751k
[mysql@cjcos02 binlog2sql]$ python get-pip.py
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Defaulting to user installation because normal site-packages is not writeable
Collecting pip<21.0
  Downloading pip-20.3.4-py2.py3-none-any.whl (1.5 MB)
     |████████████████████████████████| 1.5 MB 822 kB/s 
Collecting wheel
  Downloading wheel-0.37.0-py2.py3-none-any.whl (35 kB)
Installing collected packages: pip, wheel
Successfully installed pip-20.3.4 wheel-0.37.0


[mysql@cjcos02 binlog2sql]$ pip install -r requirements.txt
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Defaulting to user installation because normal site-packages is not writeable
Collecting PyMySQL==0.7.11
  Downloading PyMySQL-0.7.11-py2.py3-none-any.whl (78 kB)
     |████████████████████████████████| 78 kB 592 kB/s 
Collecting wheel==0.29.0
  Downloading wheel-0.29.0-py2.py3-none-any.whl (66 kB)
     |████████████████████████████████| 66 kB 671 kB/s 
Collecting mysql-replication==0.13
  Downloading mysql-replication-0.13.tar.gz (33 kB)
Building wheels for collected packages: mysql-replication
  Building wheel for mysql-replication (setup.py) ... done
  Created wheel for mysql-replication: filename=mysql_replication-0.13-py2-none-any.whl size=41107 sha256=164b5775a156b1de450699de8e05849d338623df15a15a465c2c1f3b75179c6b
  Stored in directory: /home/mysql/.cache/pip/wheels/7f/98/ad/902f1ff5bfac7e8ea16480601e85db4b87b6c608d26e294b81
Successfully built mysql-replication
Installing collected packages: PyMySQL, wheel, mysql-replication
  Attempting uninstall: wheel
    Found existing installation: wheel 0.37.0
    Uninstalling wheel-0.37.0:
      Successfully uninstalled wheel-0.37.0
Successfully installed PyMySQL-0.7.11 mysql-replication-0.13 wheel-0.29.0

创建测试数据

MySQL [cjcdb01]> create table test1(id int,name char(50));
Query OK, 0 rows affected (0.11 sec)


MySQL [cjcdb01]> insert into test1 values(1,'a'),(2,'a'),(3,'a');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0


MySQL [cjcdb01]> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
+------+------+
3 rows in set (0.00 sec)


MySQL [cjcdb01]> delete from test1 where id=2;
Query OK, 1 row affected (0.05 sec)


MySQL [cjcdb01]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-09-21 16:49:07 |
+---------------------+
1 row in set (0.06 sec)


MySQL [cjcdb01]> show master logs;
+-----------+-----------+
| Log_name  | File_size |
+-----------+-----------+
| on.000001 |       177 |
| on.000002 |       177 |
| on.000003 |       177 |
| on.000004 |       573 |
| on.000005 |     15257 |
| on.000006 |       217 |
| on.000007 |       927 |
+-----------+-----------+
7 rows in set (0.00 sec)


[mysql@cjcos02 binlog2sql]$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'1' -dcjcdb01 --start-file='on.000007' --start-datetime='2021-09-21 16:30:01' --stop-datetime='2021-09-21 18:55:15' > /home/mysql/0001.sql
[mysql@cjcos02 binlog2sql]$ cat /home/mysql/0001.sql 
USE cjcdb01;
create table test1(id int,name char(50));
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (1, 'a'); #start 444 end 629 time 2021-09-21 16:46:21
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (2, 'a'); #start 444 end 629 time 2021-09-21 16:46:21
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (3, 'a'); #start 444 end 629 time 2021-09-21 16:46:21
DELETE FROM `cjcdb01`.`test1` WHERE `id`=2 AND `name`='a' LIMIT 1; #start 725 end 896 time 2021-09-21 16:48:54


MySQL [cjcdb01]> show binlog events in 'on.000007';
+-----------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name  | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+-----------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| on.000007 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.35-log, Binlog ver: 4                              |
| on.000007 | 123 | Previous_gtids |         1 |         194 | c3c28ba2-19c3-11ec-971b-08002765a9b1:1-54                          |
| on.000007 | 194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:55' |
| on.000007 | 259 | Query          |         1 |         379 | use `cjcdb01`; create table test1(id int,name char(50))            |
| on.000007 | 379 | Gtid           |         1 |         444 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:56' |
| on.000007 | 444 | Query          |         1 |         519 | BEGIN                                                              |
| on.000007 | 519 | Table_map      |         1 |         573 | table_id: 121 (cjcdb01.test1)                                      |
| on.000007 | 573 | Write_rows     |         1 |         629 | table_id: 121 flags: STMT_END_F                                    |
| on.000007 | 629 | Xid            |         1 |         660 | COMMIT /* xid=69 */                                                |
| on.000007 | 660 | Gtid           |         1 |         725 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:57' |
| on.000007 | 725 | Query          |         1 |         800 | BEGIN                                                              |
| on.000007 | 800 | Table_map      |         1 |         854 | table_id: 121 (cjcdb01.test1)                                      |
| on.000007 | 854 | Delete_rows    |         1 |         896 | table_id: 121 flags: STMT_END_F                                    |
| on.000007 | 896 | Xid            |         1 |         927 | COMMIT /* xid=71 */                                                |
+-----------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
14 rows in set (0.00 sec)


[mysql@cjcos02 binlog2sql]$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'1' -dcjcdb01 --start-file='on.000007' --start-position=725 --stop-position=896 -B > /home/mysql/0002.sql
[mysql@cjcos02 binlog2sql]$ cat /home/mysql/0002.sql 
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (2, 'a'); #start 725 end 896 time 2021-09-21 16:48:54


[mysql@cjcos02 binlog2sql]$ mysql -h127.0.0.1 -P3306 -uroot -p'1'  < /home/mysql/0002.sql


MySQL [cjcdb01]> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
|    2 | a    |
+------+------+
3 rows in set (0.00 sec)

关于DDL的flashback
本文所述的flashback仅针对DML语句的快速回滚。
但如果误操作是DDL的话，是无法利用binlog做快速回滚的，因为即使在row模式下，binlog对于DDL操作也不会记录每行数据的变化。
要实现DDL快速回滚，必须修改MySQL源码，使得在执行DDL前先备份老数据。
目前有多个mysql定制版本实现了DDL闪回特性，阿里林晓斌团队提交了patch给MySQL官方，MariaDB预计在不久后加入包含DDL的flashback特性。
DDL闪回的副作用是会增加额外存储。考虑到其应用频次实在过低，本文不做详述，有兴趣的同学可以自己去了解，重要的几篇文章我在参考资料中做了引用。

方式十一: 快照备份

使用的比较少，需要存储或系统工程师配合完成。
LVM使用写时复制（copy-on-write）技术来创建快照
锁定所有表，或停库
FLUSH TABLES WITH READ LOCK;
创建快照
解锁

方式十二：mysqlhotcopy

mysql5.7已经没有这个命令了，多用于mysql5.5之前。
mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表，属于裸文件备份（物理备份），只能用于MyISAM引擎的数据库。
本质是使用锁表语句，然后cp或scp。
常用命令如下：


备份一个数据库到目标目录
mysqlhotcopy -u [用户名] -p [密码] [数据库名] [目标目录]


备份多个数据库到目标目录
mysqlhotcopy -u [用户名] -p [密码] [数据库名1 数据库名2...] [目标目录]


备份数据库中的某个表到指定目录
mysqlhotcopy -u [用户名] -p [密码] [数据库名]./[表名]/ [目标目录]


还原
在Linux中Mysql数据库都是以物理文件存在的，数据库对应的是目录。如果想要还原只要将原来的文件拷贝回原目录即可。
单个数据库备份还原方式：如果文件所有这和所属组不是mysql，请使用chown修改。
cp -ra [目标目录] /var/lib/mysql/[数据库名]/

最后修改：2021 年 10 月 27 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

MySQL备份和恢复方法汇总

Tehran • 2021 年 10 月 27 日

<div class="tip share">请注意，本文编写于 913 天前，最后修改于 913 天前，其中某些信息可能已经过时。</div>

<pre><code>一：CTAS
二：create table table_name_bak like table_name;
三：SELECT * FROM t1 INTO OUTFILE/load data infile/mysqlimport
四: mysqldump
五: mysqlbinlog恢复
六：mysqldumper/myloader
七：xtrabackup/innobackupex
八: MEB(MySQL Enterprise Backup)
九：冷备
十：闪回
十一: 快照备份
十二：mysqlhotcopy</code></pre><h3>Mysql备份类型</h3>按照备份时对数据库的影响分为<pre><code>Hot backup（热备）：也叫在线备份。指在数据库运行中直接备份，对正在运行的数据库没有任何影响。
Cold backup（冷备）：也叫离线备份。指在数据库停止的情况下备份。
Warm backup（温备）：在数据库运行时备份，会加一个全局锁以保证数据的一致性，会对当前数据库的操作有影响</code></pre>创建测试数据：<pre><code>MySQL [cjcdb]&gt; show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
 `id` int(11) DEFAULT NULL,
 KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)</code></pre><pre><code>MySQL [cjcdb]&gt; select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+------+
9 rows in set (0.00 sec)</code></pre><pre><code>MySQL [cjcdb]&gt; create index i_t1_id on t1(id);

MySQL [cjcdb]&gt; create table t1_bak as select * from t1 where 1=2;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.</code></pre>由于启动了GTID，不允许CTAS操作。因为create table ...select语句会生成两个sql，一个是DDL创建表SQL，一个是insert into插入数据的sql。由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID，如果强制执行会导致和上面更新非事务引擎一样的结果。但是不能因为执行不了CTAS而关闭GTID，可以将CTAS操作拆成如下两部分。<h3>craete table xx as ...用来创建相同表结构并复制源表数据,不包含索引等。</h3><h3>create table xx like ...用来创建完整表结构和全部索引。</h3><h3>方式二：create table table_name_bak like table_name;</h3><pre><code>MySQL [cjcdb]&gt; create table t1_bak like t1;
Query OK, 0 rows affected (0.04 sec)

MySQL [cjcdb]&gt; select * from t1_bak;
Empty set (0.00 sec)

MySQL [cjcdb]&gt; show create table t1_bak;
+--------+--------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                       |
+--------+--------------------------------------------------------------------------------------------------------------------+
| t1_bak | CREATE TABLE `t1_bak` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [cjcdb]&gt; insert into t1_bak select * from t1;
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0

MySQL [cjcdb]&gt; select * from t1_bak;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+------+
9 rows in set (0.00 sec)</code></pre>还原备份的表<pre><code>MySQL [cjcdb]&gt; rename table t1 to t1_old;
Query OK, 0 rows affected (0.04 sec)

MySQL [cjcdb]&gt; rename table t1_bak to t1;
Query OK, 0 rows affected (0.05 sec)

MySQL [cjcdb]&gt; show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [cjcdb]&gt; select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+------+
9 rows in set (0.00 sec)</code></pre><h3>方式三：select into outfile</h3>Mysql select into outfile命令在Mysql中，与load data infile命令作用相反的一个命令是select into outfile命令。<pre><code>MySQL [cjcdb]&gt; SELECT * FROM t1 INTO OUTFILE &quot;/home/mysql/t1_001.txt&quot;;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

MySQL [cjcdb]&gt; show variables like '%secure%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | NULL |
+--------------------------+-------+
3 rows in set (0.01 sec)</code></pre>查看 secure_file_priv 的值，默认为NULL，表示限制不能导入导出。查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。<pre><code>secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。
secure_file_priv 为 tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。
secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。</code></pre>又因为secure_file_priv 参数是只读参数，不能使用set global命令修改。<pre><code>MySQL [cjcdb]&gt; set global secure_file_priv='/home/mysql';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
[root@mysql01 ~]# vim etc/my.cnf
secure_file_priv='/home/mysql'

[mysql@mysql01 ~]$ service mysqld restart
Shutting down MySQL............ SUCCESS! 
Starting MySQL. SUCCESS!

MySQL [(none)]&gt; use cjcdb;
MySQL [cjcdb]&gt; SELECT * FROM t1 INTO OUTFILE &quot;/home/mysql/t1_001.txt&quot;;
Query OK, 9 rows affected (0.00 sec)</code></pre>也可以指定分隔符和包围符。<pre><code>MySQL [cjcdb]&gt; SELECT * FROM t1 INTO OUTFILE &quot;/home/mysql/t1_002.txt&quot; FIELDS TERMINATED BY &quot;,&quot; ENCLOSED BY '&quot;';
Query OK, 9 rows affected (0.00 sec)

MySQL [cjcdb]&gt; system cat home/mysql/t1_001.txt
1
2
3
4
5
6
7
8
9

MySQL [cjcdb]&gt; system cat home/mysql/t1_002.txt
&quot;1&quot;
&quot;2&quot;
&quot;3&quot;
&quot;4&quot;
&quot;5&quot;
&quot;6&quot;
&quot;7&quot;
&quot;8&quot;
&quot;9&quot;</code></pre>恢复：<pre><code>MySQL [cjcdb]&gt; help load data;
Name: 'LOAD DATA'
Description:
Syntax:
LOAD DATA
 [LOW_PRIORITY | CONCURRENT] [LOCAL]
 INFILE 'file_name'
 [REPLACE | IGNORE]
 INTO TABLE tbl_name
 [PARTITION (partition_name [, partition_name] ...)]
 [CHARACTER SET charset_name]
 [{FIELDS | COLUMNS}
 [TERMINATED BY 'string']
 [[OPTIONALLY] ENCLOSED BY 'char']
 [ESCAPED BY 'char']
 ]
 [LINES
 [STARTING BY 'string']
 [TERMINATED BY 'string']
 ]
 [IGNORE number {LINES | ROWS}]
 [(col_name_or_user_var
 [, col_name_or_user_var] ...)]
 [SET col_name={expr | DEFAULT}
 [, col_name={expr | DEFAULT}] ...]
......

MySQL [cjcdb]&gt; delete from t1;
Query OK, 9 rows affected (0.03 sec)

MySQL [cjcdb]&gt; select * from t1;
Empty set (0.00 sec)

MySQL [cjcdb]&gt; load data infile &quot;/home/mysql/t1_001.txt&quot; into table t1;
Query OK, 9 rows affected (0.03 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [cjcdb]&gt; select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+------+
9 rows in set (0.00 sec)</code></pre>mysqlimport客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。<pre><code>[mysql@mysql01 ~]$ mysql -uroot -p cjcdb -e &quot;select count(*) from t1&quot;
Enter password: 
+----------+
| count(*) |
+----------+
| 9 |
+----------+

mysql -uroot -p cjcdb -e &quot;delete  from t1&quot;

[mysql@mysql01 ~]$ mysqlimport -u root -p --local cjcdb &quot;/home/mysql/t1_001.txt&quot;
Enter password: 
mysqlimport: Error: 1146, Table 'cjcdb.t1_001' doesn't exist, when using table: t1_001

[mysql@mysql01 ~]$ mv t1_001.txt t1.txt
[mysql@mysql01 ~]$ mysqlimport -u root -p --local cjcdb &quot;/home/mysql/t1.txt&quot;
Enter password: 
cjcdb.t1: Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

[mysql@mysql01 ~]$ mysql -uroot -p cjcdb -e &quot;select count(*) from t1&quot;
Enter password: 
+----------+
| count(*) |
+----------+
| 9 |
+----------+</code></pre>mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：<pre><code>$ mysqlimport -u root -p --local --fields-terminated-by=&quot;:&quot; \
 --lines-terminated-by=&quot;\r\n&quot; mytbl dump.txt
password *****</code></pre>mysqlimport 语句中使用 --columns 选项来设置列的顺序：<pre><code>$ mysqlimport -u root -p --local --columns=b,c,a \

mytbl dump.txt
password *****

mysqlimport -uroot -p cjcdb home/mysql/t1_001.txt

[mysql@mysql01 ~]$ man mysqlimport
shell&gt; mysqlimport --local test imptest.txt
 test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
 shell&gt; mysql -e ´SELECT * FROM imptest´ test
 +------+---------------+
 | id | n |
 +------+---------------+
 | 100 | Max Sydow |
 | 101 | Count Dracula |
 +------+---------------+</code></pre><h3>方式四：Mysqldump</h3>mysqldump是逻辑备份工具，支持MyISAM和InnoDB引擎。数据库运行时，MyISAM引擎只支持温备，InnoDB支持热备和温备。<pre><code>7.4.1 Dumping Data in SQL Format with mysqldump
https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html</code></pre>默认情况下，mysqldump将信息作为SQL语句写入标准输出。可以将输出保存在文件中：备份所有数据库要转储所有数据库，请使用--all databases选项调用mysqldump。<pre><code>[mysql@mysql01 backup]$ mysqldump -uroot -p --all-databases &gt; dump.sql
[mysql@mysql01 backup]$ ll -rth
total 1.5M
-rw-rw-r-- 1 mysql mysql 1.5M Sep 19 13:27 dump.sql
[mysql@mysql01 backup]$ vim dump.sql
--
-- Current Database: `cjcdb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cjcdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `cjcdb`;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `t1_old`
--
......
--
-- Current Database: `mysql`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mysql`;
--
-- Table structure for table `columns_priv`
--
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
 `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
 PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `columns_priv`
--
......</code></pre>备份指定数据库:要仅转储特定数据库，请在命令行中命名它们并使用--databases选项：<pre><code>mysqldump -uroot -p --databases cjc cjcdb &gt; dump1.sql</code></pre>--databases选项使命令行上的所有名称都被视为数据库名称。如果没有此选项，mysqldump将第一个名称视为数据库名称，后面的名称视为表名称。例如：导出cjc库test1表。<pre><code>mysqldump -uroot -p cjc test1 &gt; dump2.sql</code></pre>对于--all databases或--databases，mysqldump在每个数据库的转储输出之前编写CREATE DATABASE和USE语句。这样可以确保在重新加载转储文件时，如果每个数据库不存在，它将创建每个数据库，并使其成为默认数据库，以便将数据库内容加载到它们来自的同一个数据库中。如果要使转储文件在重新创建数据库之前强制删除每个数据库，请同时使用--add drop database选项。在本例中，mysqldump在每条CREATE DATABASE语句之前写入一条DROP DATABASE语句。例如：备份cjc数据库，同时在转储文件中记录删除cjc数据库的语句。<pre><code>mysqldump -uroot -p --databases --add-drop-database cjc &gt; dump3.sql</code></pre>如果不加--databases选项，转储文件中不会记录包含CREATE DATABASE或USE语句。例如：<pre><code>mysqldump -uroot -p cjc &gt; dump4.sql</code></pre>仅导出表结构，不导出数据。<pre><code>mysqldump -uroot -p --no-data cjcdb t1 &gt; dump6.sql</code></pre>对于仅定义转储，添加--routines和--events选项，以还包括存储过程和事件定义：<pre><code>mysqldump -uroot -p --no-data --routines --events test &gt; dump-defs.sql</code></pre>仅导出表数据，不导出表结构。<pre><code>mysqldump -uroot -p --no-create-info cjcdb t1 &gt; dump7.sql</code></pre>MySQLdump 备份时如何保持数据的一致性( –single-transaction)MySQLdump备份不得不提的两个关键参数<pre><code>--single-transcation
--master-data=2

--master-data=2，--master-data=1 默认的话会--lock-all-tables，会锁住整个mysql数据库中的所有表。但是如果加上--single-transaction会加上事务，不会锁表。

--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；
--master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；
 
--dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；
--dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；</code></pre>关于mysqldump工作原理<pre><code>1.调用FWRL（flush tables with read lock）,全局禁止读写
2.开启快照读，获取此期间的快照（仅仅对innodb起作用）
3.备份非innodb表数据（*.frm,*.myi,*.myd等）
4.非innodb表备份完毕之后，释放FTWRL
5.逐一备份innodb表数据
6.备份完成</code></pre><pre><code>mysqldump -uroot -p --databases cjcdb --single-transaction --master-data=2 --default-character-set=utf8mb4 --flush-logs --quick &gt; dump8.sql</code></pre>查看：<pre><code>-- CHANGE MASTER TO MASTER_LOG_FILE='on.000010', MASTER_LOG_POS=194;
--
-- Current Database: `cjcdb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cjcdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `cjcdb`;
......
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;</code></pre><pre><code>–single-transaction 实际上通过做了下面两个操作 ：
(1)在开始的时候把该 session 的事务隔离级别设置成 repeatable read ；
(2)然后启动一个事务（执行 begin ），备份结束的时候结束该事务（执行 commit ）</code></pre><pre><code>有了这两个操作，在备份过程中，该 session 读到的数据都是启动备份时的数据（同一个点）。
可以理解为对于 innodb 引擎来说加了该参数，备份开始时就已经把要备份的数据定下来了，备份过程中的提交的事务时是看不到的，也不会备份进去。

对于不支持事务的引擎如 MyISAM ，只能通过锁表来保证数据一致性，这里分三种情况：
(1)导出全库 ：加 –lock-all-tables 参数 ， 这会在备份开始的时候启动一个全局读锁 （ 执行 flush tables with read lock ）， 其他 session 可以读取但不能更新数据 ， 备份过程中数据没有变化 ， 所以最终得到的数据肯定是完全一致的 ；
(3)导出单个库：加 –lock-tables 参数，这会在备份开始的时候锁该库的所有表，其他 session 可以读但不能更新该库的所有表，该库的数据一致；</code></pre>恢复数据库:恢复所有数据库：<pre><code>mysql -uroot -p &lt; dumpxxx.sql
或
mysql&gt; show databases;
mysql&gt; source dump.sql</code></pre><h3>方式五：mysqlbinlog恢复数据</h3><pre><code>mysqlbinlog常见的选项：
a、--start-datetime
b、--stop-datetime
c、--start-position
d、--stop-position</code></pre>flush logsflush 刷新log日志，自此刻开始产生一个新编号的binlog日志文件;注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqlddump备份数据时加-F选项也会刷新binlog日志；reset master重置（清空）所有binlog日志<pre><code>a、查询最近的binlog日志：
show binlog events\G;

b、指定查询mysql-bin.000002这个文件
show binlog events in 'mysql-bin.000002'\G;

c、指定查询mysql-bin.000002这个文件，从pos点:624开始查起：
show binlog events in 'mysql-bin.000002' from 624\G;

d、指定查询mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）
show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

e、指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个）查询10条（即10条语句）。
show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;</code></pre>恢复场景如下：备份cjcdb01库数据：<pre><code>insert into t2 values(1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');
MySQL [cjcdb01]&gt; select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 3 | a |
| 4 | a |
| 5 | a |
+------+------+
5 rows in set (0.00 sec)</code></pre>备份：<pre><code>mysqldump -uroot -p --databases cjcdb01 --single-transaction --master-data=2 &gt; /home/mysql/backup/dump03.sql

insert into t2 values(6,'a'),(7,'a');
update t2 set name='b' where id=5;
insert into t2 values(8,'a');
update t2 set name='c' where id=2;
update t2 set name='d' where id=3;
insert into t2 values(9,'a');
delete from t2 where id=9;
insert into t2 values(9,'f');</code></pre>模拟误删除：<pre><code>delete from t2;

MySQL [cjcdb01]&gt; select * from t2;
Empty set (0.00 sec)</code></pre>恢复：查看备份结束位置<pre><code>[mysql@cjcos02 backup]$ cat dump03.sql |grep MASTER
-- CHANGE MASTER TO MASTER_LOG_FILE='on.000005', MASTER_LOG_POS=7741;

MySQL [cjcdb01]&gt; show binary logs;
+-----------+-----------+
| Log_name  | File_size |
+-----------+-----------+
| on.000001 |       177 |
| on.000002 |       177 |
| on.000003 |       177 |
| on.000004 |       573 |
| on.000005 |     10204 |
+-----------+-----------+
5 rows in set (0.00 sec)

MySQL [cjcdb01]&gt; help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

MySQL [cjcdb01]&gt; SHOW BINLOG EVENTS IN 'on.000005' FROM 7741;
+-----------+-------+-------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------+-------+-------------+-----------+-------------+--------------------------------------------------------------------+
| on.000005 | 7741 | Gtid | 1 | 7806 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:33' |
| on.000005 | 7806 | Query | 1 | 7881 | BEGIN |
| on.000005 | 7881 | Table_map | 1 | 7932 | table_id: 114 (cjcdb01.t2) |
| on.000005 | 7932 | Write_rows | 1 | 7981 | table_id: 114 flags: STMT_END_F |
| on.000005 | 7981 | Xid | 1 | 8012 | COMMIT /* xid=236 */ |
| on.000005 | 8012 | Gtid | 1 | 8077 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:34' |
| on.000005 | 8077 | Query | 1 | 8152 | BEGIN |
| on.000005 | 8152 | Table_map | 1 | 8203 | table_id: 114 (cjcdb01.t2) |
| on.000005 | 8203 | Update_rows | 1 | 8253 | table_id: 114 flags: STMT_END_F |
| on.000005 | 8253 | Xid | 1 | 8284 | COMMIT /* xid=237 */ |
| on.000005 | 8284 | Gtid | 1 | 8349 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:35' |
| on.000005 | 8349 | Query | 1 | 8424 | BEGIN |
| on.000005 | 8424 | Table_map | 1 | 8475 | table_id: 114 (cjcdb01.t2) |
| on.000005 | 8475 | Write_rows | 1 | 8517 | table_id: 114 flags: STMT_END_F |
| on.000005 | 8517 | Xid | 1 | 8548 | COMMIT /* xid=238 */ |
| on.000005 | 8548 | Gtid | 1 | 8613 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:36' |
| on.000005 | 8613 | Query | 1 | 8688 | BEGIN |
| on.000005 | 8688 | Table_map | 1 | 8739 | table_id: 114 (cjcdb01.t2) |
| on.000005 | 8739 | Update_rows | 1 | 8789 | table_id: 114 flags: STMT_END_F |
| on.000005 | 8789 | Xid | 1 | 8820 | COMMIT /* xid=239 */ |
| on.000005 | 8820 | Gtid | 1 | 8885 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:37' |
| on.000005 | 8885 | Query | 1 | 8960 | BEGIN |
| on.000005 | 8960 | Table_map | 1 | 9011 | table_id: 114 (cjcdb01.t2) |
| on.000005 | 9011 | Update_rows | 1 | 9061 | table_id: 114 flags: STMT_END_F |
| on.000005 | 9061 | Xid | 1 | 9092 | COMMIT /* xid=240 */ |
| on.000005 | 9092 | Gtid | 1 | 9157 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:38' |
| on.000005 | 9157 | Query | 1 | 9232 | BEGIN |
| on.000005 | 9232 | Table_map | 1 | 9283 | table_id: 114 (cjcdb01.t2) |
| on.000005 | 9283 | Write_rows | 1 | 9325 | table_id: 114 flags: STMT_END_F |
| on.000005 | 9325 | Xid | 1 | 9356 | COMMIT /* xid=241 */ |
| on.000005 | 9356 | Gtid | 1 | 9421 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:39' |
| on.000005 | 9421 | Query | 1 | 9496 | BEGIN |
| on.000005 | 9496 | Table_map | 1 | 9547 | table_id: 114 (cjcdb01.t2) |
| on.000005 | 9547 | Delete_rows | 1 | 9589 | table_id: 114 flags: STMT_END_F |
| on.000005 | 9589 | Xid | 1 | 9620 | COMMIT /* xid=242 */ |
| on.000005 | 9620 | Gtid | 1 | 9685 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:40' |
| on.000005 | 9685 | Query | 1 | 9760 | BEGIN |
| on.000005 | 9760 | Table_map | 1 | 9811 | table_id: 114 (cjcdb01.t2) |
| on.000005 | 9811 | Write_rows | 1 | 9853 | table_id: 114 flags: STMT_END_F |
| on.000005 | 9853 | Xid | 1 | 9884 | COMMIT /* xid=244 */ |
| on.000005 | 9884 | Gtid | 1 | 9949 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:41' |
| on.000005 | 9949 | Query | 1 | 10024 | BEGIN |
| on.000005 | 10024 | Table_map | 1 | 10075 | table_id: 114 (cjcdb01.t2) |
| on.000005 | 10075 | Delete_rows | 1 | 10173 | table_id: 114 flags: STMT_END_F |
| on.000005 | 10173 | Xid | 1 | 10204 | COMMIT /* xid=245 */ |
+-----------+-------+-------------+-----------+-------------+--------------------------------------------------------------------+
45 rows in set (0.00 sec)</code></pre>第一次delete位置在9547，属于正常操作，第二次delete位置在10075属性误操作。<pre><code>[mysql@cjcos02 backup]$ mysql -uroot -p -e&quot;show binlog events in 'on.000005'&quot;|grep Delete_rows
Enter password: 
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
on.0000055965Delete_rows16077table_id: 112 flags: STMT_END_F
on.0000057355Delete_rows17418table_id: 112 flags: STMT_END_F
on.0000059547Delete_rows19589table_id: 114 flags: STMT_END_F</code></pre>获取表结构：<pre><code>MySQL [cjcdb01]&gt; show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
 `id` int(11) DEFAULT NULL,
 `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)</code></pre>通过备份文件获取t2表结构：<pre><code>[mysql@cjcos02 ~]$ sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t2`/!d;q' dump01.sql

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
 `id` int(11) DEFAULT NULL,
 `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;</code></pre>通过备份文件获取t2表数据：<pre><code>[mysql@cjcos02 backup]$ grep 'INSERT INTO `t2`' dump03.sql
INSERT INTO `t2` VALUES (1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');</code></pre>通过备份恢复表数据<pre><code>rename table t2 to t2_bak;
create table t2 like t2_bak;
INSERT INTO `t2` VALUES (1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');
/usr/local/mysql/bin/mysqlbinlog --start-position=7741 --stop-position=10075 --database=cjcdb01 usr/local/mysql/data/on.000005 | usr/local/mysql/bin/mysql -u root -p</code></pre>通过binlog获取数据<pre><code>/usr/local/mysql/bin/mysqlbinlog --start-datetime=&quot;2021-09-20 12:15:34&quot; usr/local/mysql/data/on.000005

[mysql@cjcos02 data]$ usr/local/mysql/bin/mysqlbinlog --start-datetime=&quot;2021-09-20 12:15:34&quot; usr/local/mysql/data/on.000005|grep -i del
DELIMITER *!*/;
#210920 12:34:53 server id 1  end_log_pos 6077 CRC32 0x01c2c864 Delete_rows: table id 112 flags: STMT_END_F
DELIMITER ;

/usr/local/mysql/bin/mysqlbinlog usr/local/mysql/data/on.000005 --start-datetime=&quot;2021-09-20 12:15:34&quot; --stop-position 6077 &gt;2.sql
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/on.000005 --start-datetime=&quot;2021-09-20 12:15:34&quot; --stop-position 6077|mysql -uroot -p
/usr/local/mysql/bin/mysqlbinlog usr/local/mysql/data/on.000005 --start-datetime=&quot;2021-09-20 12:15:34&quot; --skip-gtids --stop-position=6077 &gt;01.sql</code></pre>具体恢复方法，参考：MySQL备份和恢复<a href="https://uuks.cn/go/aHR0cDovL2Jsb2cuaXRwdWIubmV0LzI5Nzg1ODA3L3ZpZXdzcGFjZS0yMTU2NDMwLw" target="_blank" >http://blog.itpub.net/29785807/viewspace-2156430/</a><h3>方式六：mysqldumper/myloader</h3>参考链接：<a href="https://uuks.cn/go/aHR0cHM6Ly93d3cuamlhbnNodS5jb20vcC82NWYzM2M3ZWI2MzA" target="_blank" >https://www.jianshu.com/p/65f33c7eb630</a><pre><code>mysqldump无法并行，这点与Oracle的expdp相比，存在一定的劣势，但是开源的mysqldumper是一个很好的补充
mydumper（http://www.mysqldumper.org/）是一个在GNU GPLv3许可下发布的高性能MySQL备份和恢复工具集。
mydumper是多线程的，他创建一个mysql备份就比随mysql发布的mysqldump工具要快得多。
mydumper也有从源端服务器恢复二进制日志的能力。</code></pre>mysqldumper优点<pre><code>1.多线程，可以是转存数据快很多
2.mydumper的输出已于管理和分析，因为他的表和元数据是分开的单独文件。
3.所有线程都维护有一直的快照，这边提供了精准的主从位置。
4.Mydumper支持Perl正则表达式，这样就既可以包括是数据库名和报名的模式匹配，也可以配置这种匹配。
5.通过名为myloader的多线程工具，mydumper工具集也可以从mydumper备份中恢复数据。</code></pre>主要备份步骤概括<pre><code>1.主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，以阻止DML语句写入，保证数据的一致性
2.读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用
3.N个（线程数可以指定，默认是4）dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT; 开启读一致的事物
4.dump non-InnoDB tables, 首先导出非事物引擎的表
5.主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁
6.dump InnoDB tables, 基于事物导出InnoDB表
7.事务结束</code></pre>mysqldumper安装<pre><code>数据库版本 : MySQL 5.7
操作系统版本: CentOS 7.8
软件地址:https://github.com/maxbube/mydumper/releases

http://www.mysqldumper.org/
http://rpm.pbone.net/info_idpl_55203713_distro_redhatel7_com_mydumper-0.9.1-4.1.x86_64.rpm.html

yum -y install cmake glib2 pcre pcre-devel mysql-devel
###yum -y install mydumper-0.9.5-2.el7.x86_64.rpm
[root@cjcos02 mysql]# yum install mydumper-0.9.1-4.1.x86_64.rpm 
......
Running transaction
  Installing : mydumper-0.9.1-4.1.x86_64                                                                                                                                                   1/1 
  Verifying  : mydumper-0.9.1-4.1.x86_64                                                                                                                                                   1/1

Installed:
 mydumper.x86_64 0:0.9.1-4.1 
 
[root@cjcos02 mysql]# which mydumper
/usr/bin/mydumper
[root@cjcos02 mysql]# which myloader
/usr/bin/myloader</code></pre><pre><code>[root@cjcos02 mysql]# mydumper --help

Usage:
  mydumper [OPTION?] multi-threaded MySQL dumping

Help Options:
 -?, --help Show help options
Application Options:
 -B, --database Database to dump
 -T, --tables-list Comma delimited table list to dump (does not exclude regex option)
 -o, --outputdir Directory to output files to
 -s, --statement-size Attempted size of INSERT statement in bytes, default 1000000
 -r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
 -F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB
 -c, --compress Compress output files
 -e, --build-empty-files Build dump files even if no data available from table
 -x, --regex Regular expression for 'db.table' matching
 -i, --ignore-engines Comma delimited list of storage engines to ignore
 -m, --no-schemas Do not dump table schemas with the data
 -d, --no-data Do not dump table data
 -G, --triggers Dump triggers
 -E, --events Dump events
 -R, --routines Dump stored procedures and functions
 -k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups
 --less-locking Minimize locking time on InnoDB tables.
 -l, --long-query-guard Set long query timer in seconds, default 60
 -K, --kill-long-queries Kill long running queries (instead of aborting)
 -D, --daemon Enable daemon mode
 -I, --snapshot-interval Interval between each dump snapshot (in minutes), requires --daemon, default 60
 -L, --logfile Log file name to use, by default stdout is used
 --tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
 --skip-tz-utc 
 --use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege
 --success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist
 --lock-all-tables Use LOCK TABLE for all, instead of FTWRL
 -U, --updated-since Use Update_time to dump only tables updated in the last U days
 --trx-consistency-only Transactional consistency only
 -h, --host The host to connect to
 -u, --user Username with privileges to run the dump
 -p, --password User password
 -P, --port TCP/IP port to connect to
 -S, --socket UNIX domain socket file to use for connection
 -t, --threads Number of threads to use, default 4
 -C, --compress-protocol Use compression on the MySQL connection
 -V, --version Show the program version and exit
 -v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2</code></pre><pre><code>-B 指定数据库
-T 指定表
-t 指定线程数，默认4
-v 输出显示info
-L 指定日志路径和名称
-o 指定备份文件路径和名称</code></pre>备份：<pre><code>mydumper -u root -p 1 -B cjcdb01 -T t1,t2 -t 4 -r 100000 -c --less-locking -v 3 -D -L home/mysql/backup/mydumper01.log -o home/mysql/backup/
[mysql@cjcos02 backup]$ ls
0 003.sql 01.sql 1 2.sql 33.sql dump03.sql mydumper01.log

[mysql@cjcos02 backup]$ cat mydumper01.log 
2021-09-20 19:35:00 [INFO] - Connected to a MySQL server
2021-09-20 19:35:00 [INFO] - Started dump at: 2021-09-20 19:35:00

2021-09-20 19:35:00 [INFO] - Written master status
2021-09-20 19:35:00 [INFO] - Thread 5 connected using MySQL connection ID 24
2021-09-20 19:35:00 [INFO] - Thread 6 connected using MySQL connection ID 25
2021-09-20 19:35:00 [INFO] - Thread 7 connected using MySQL connection ID 26
2021-09-20 19:35:00 [INFO] - Thread 8 connected using MySQL connection ID 27
2021-09-20 19:35:00 [INFO] - Thread 1 connected using MySQL connection ID 28
2021-09-20 19:35:00 [INFO] - Thread 2 connected using MySQL connection ID 29
2021-09-20 19:35:00 [INFO] - Thread 3 connected using MySQL connection ID 30
2021-09-20 19:35:00 [INFO] - Thread 4 connected using MySQL connection ID 31
2021-09-20 19:35:00 [INFO] - Thread 6 shutting down
2021-09-20 19:35:00 [INFO] - Thread 7 shutting down
2021-09-20 19:35:00 [INFO] - Thread 8 shutting down
2021-09-20 19:35:00 [INFO] - Thread 5 dumping data for `cjcdb01`.`t1`
2021-09-20 19:35:00 [INFO] - Non-InnoDB dump complete, unlocking tables
2021-09-20 19:35:00 [INFO] - Thread 2 dumping data for `cjcdb01`.`t2`
2021-09-20 19:35:00 [INFO] - Thread 3 dumping schema for `cjcdb01`.`t1`
2021-09-20 19:35:00 [INFO] - Thread 4 dumping schema for `cjcdb01`.`t2`
2021-09-20 19:35:00 [INFO] - Thread 5 shutting down
2021-09-20 19:35:00 [INFO] - Thread 3 shutting down
2021-09-20 19:35:00 [INFO] - Thread 2 shutting down
2021-09-20 19:35:00 [INFO] - Thread 4 shutting down
2021-09-20 19:35:00 [INFO] - Thread 1 shutting down
2021-09-20 19:35:00 [INFO] - Finished dump at: 2021-09-20 19:35:00</code></pre>查看备份文件：<pre><code>[mysql@cjcos02 backup]$ ll -rth 0/
total 24K
-rw-rw-rw- 1 mysql mysql 88 Sep 20 19:35 cjcdb01-schema-create.sql.gz
-rw-rw-rw- 1 mysql mysql 404 Sep 20 19:35 cjcdb01.t1-schema.sql.gz
-rw-rw-rw- 1 mysql mysql 151 Sep 20 19:35 cjcdb01.t2.sql.gz
-rw-rw-rw- 1 mysql mysql 170 Sep 20 19:35 cjcdb01.t2-schema.sql.gz
-rw-rw-rw- 1 mysql mysql 200 Sep 20 19:35 cjcdb01.t1.sql.gz
-rw-rw-rw- 1 mysql mysql 172 Sep 20 19:35 metadata</code></pre>恢复：<pre><code>[mysql@cjcos02 backup]$ myloader --help
Usage:
 myloader [OPTION?] multi-threaded MySQL loader

Help Options:
  -?, --help                        Show help options

Application Options:
  -d, --directory                   Directory of the dump to import
  -q, --queries-per-transaction     Number of queries per transaction, default 1000
  -o, --overwrite-tables            Drop tables if they already exist
  -B, --database                    An alternative database to restore into
  -s, --source-db                   Database to restore
  -e, --enable-binlog               Enable binary logging of the restore data
  -h, --host                        The host to connect to
  -u, --user                        Username with privileges to run the dump
  -p, --password                    User password
  -P, --port                        TCP/IP port to connect to
  -S, --socket                      UNIX domain socket file to use for connection
  -t, --threads                     Number of threads to use, default 4
  -C, --compress-protocol           Use compression on the MySQL connection
  -V, --version                     Show the program version and exit
  -v, --verbose                     Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
  
MySQL [cjcdb01]&gt; select * from t1;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
| localhost | sys                | mysql.sys     | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | Y            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)

MySQL [cjcdb01]&gt; select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 3 | a |
| 4 | a |
| 5 | a |
+------+------+
5 rows in set (0.00 sec)</code></pre>模拟误删除：<pre><code>MySQL [cjcdb01]&gt; delete from t1;
Query OK, 2 rows affected (0.06 sec)

MySQL [cjcdb01]&gt; delete from t2;
Query OK, 5 rows affected (0.09 sec)

MySQL [cjcdb01]&gt; delete from t1;
Query OK, 0 rows affected (0.00 sec)

MySQL [cjcdb01]&gt; delete from t2;
Query OK, 0 rows affected (0.00 sec)</code></pre>恢复：<pre><code>myloader -u root -p 1 -B cjcdb01 -e -t 8 -d home/mysql/backup/ --overwrite-tables -v 3

[mysql@cjcos02 backup]$ myloader -u root -p 1  -B cjcdb01 -e -t 8  -d home/mysql/backup/0/ --overwrite-tables -v 3
** Message: 8 threads created
** Message: Dropping table or view (if exists) `cjcdb01`.`t1`
** Message: Creating table `cjcdb01`.`t1`
** Message: Dropping table or view (if exists) `cjcdb01`.`t2`
** Message: Creating table `cjcdb01`.`t2`
** Message: Thread 1 restoring `cjcdb01`.`t1` part 0
** Message: Thread 2 restoring `cjcdb01`.`t2` part 0
** Message: Thread 3 shutting down
** Message: Thread 4 shutting down
** Message: Thread 5 shutting down
** Message: Thread 6 shutting down
** Message: Thread 7 shutting down
** Message: Thread 8 shutting down
** Message: Thread 1 shutting down
** Message: Thread 2 shutting down

MySQL [cjcdb01]&gt; select * from t1;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
| localhost | sys                | mysql.sys     | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | Y            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)

MySQL [cjcdb01]&gt; select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 3 | a |
| 4 | a |
| 5 | a |
+------+------+
5 rows in set (0.00 sec)</code></pre><h3>方式七：xtrbackup</h3><pre><code>获取xtrabackup备份工具
网站域名：http://www.percona.com
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/#
[root@mysql01 xtrabakcup]# rpm -ivh percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm 
warning: percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
libev.so.4()(64bit) is needed by percona-xtrabackup-80-8.0.26-18.1.el7.x86_64
 
[root@mysql01 xtrabakcup]# rpm -ivh percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm --nodeps
warning: percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing... ################################# [100%]
Updating installing...
 1:percona-xtrabackup-80-8.0.26-18.1################################# [100%]</code></pre>查看测试数据<pre><code>MySQL [cjcdb]&gt; select * from cjcdb.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+------+
9 rows in set (0.00 sec)</code></pre>授权：<pre><code>MySQL [cjcdb]&gt; grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
Query OK, 0 rows affected, 1 warning (0.08 sec)

MySQL [cjcdb]&gt; flush privileges;
Query OK, 0 rows affected (0.10 sec)</code></pre>查看xtrabackup命令<pre><code>[mysql@mysql01 ~]$ man xtrabackup
$ xtrabackup --backup --target-dir=/home/mysql/xtrabakcup --datadir=/usr/local/mysql/data/

$ xtrabackup --backup --target-dir=/data/backups/inc1 \
          --incremental-basedir=/data/backups/base --datadir=/var/lib/mysql/

xtrabackup --defaults-file=/etc/my.cnf -uadmin  -p123 --target-dir=/home/mysql/xtrabakcup

[mysql@mysql02 bin]$ ./mysqld --verbose --help |grep -A 1 &quot;Default options&quot;
2021-09-16T14:11:47.119008Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2021-09-16T14:11:47.119163Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Default options are read from the following files in the given order:
/etc/my.cnf etc/mysql/my.cnf usr/local/mysql/etc/my.cnf ~/.my.cnf</code></pre>备份：<pre><code>$ innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 home/mysql/backup/
 
 [mysql@mysql02 ~]$ innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 home/mysql/backup/xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --tmpdir=/tmp --server-id=2 --log_bin=on 
xtrabackup: recognized client arguments: 
210916 22:44:18 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints &quot;completed OK!&quot;.

210916 22:44:20  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'admin'  (using password: YES).
210916 22:44:20  version_check Connected to MySQL server
210916 22:44:20  version_check Executing a version check against the server...
210916 22:44:30  version_check Done.
210916 22:44:30 Connecting to MySQL server host: localhost, user: admin, password: set, port: not set, socket: not set
Using server version 5.7.35-log
innobackupex version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
210916 22:44:30 &gt;&gt; log scanned up to (2780419)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
210916 22:44:31 [01] Copying ./ibdata1 to home/mysql/backup/2021-09-16_22-44-18/ibdata1
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/plugin.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/plugin.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/servers.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/servers.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_topic.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/help_topic.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_category.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/help_category.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_relation.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/help_relation.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/help_keyword.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/help_keyword.ibd
210916 22:44:31 [01]        ...done
210916 22:44:31 [01] Copying ./mysql/time_zone_name.ibd to home/mysql/backup/2021-09-16_22-44-18/mysql/time_zone_name.ibd
......
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./sys/x@0024waits_global_by_latency.frm to home/mysql/backup/2021-09-16_22-44-18/sys/x@0024waits_global_by_latency.frm
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./sys/session_ssl_status.frm to home/mysql/backup/2021-09-16_22-44-18/sys/session_ssl_status.frm
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./cjcdb/db.opt to home/mysql/backup/2021-09-16_22-44-18/cjcdb/db.opt
210916 22:44:34 [01]        ...done
210916 22:44:34 [01] Copying ./cjcdb/t1.frm to home/mysql/backup/2021-09-16_22-44-18/cjcdb/t1.frm
210916 22:44:34 [01]        ...done
210916 22:44:34 Finished backing up non-InnoDB tables and files
210916 22:44:34 [00] Writing home/mysql/backup/2021-09-16_22-44-18/xtrabackup_binlog_info
210916 22:44:34 [00]        ...done
210916 22:44:34 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2780410'
xtrabackup: Stopping log copying thread.
.210916 22:44:34 &gt;&gt; log scanned up to (2780419)

210916 22:44:34 Executing UNLOCK TABLES
210916 22:44:34 All tables unlocked
210916 22:44:34 [00] Copying ib_buffer_pool to home/mysql/backup/2021-09-16_22-44-18/ib_buffer_pool
210916 22:44:34 [00] ...done
210916 22:44:34 Backup created in directory '/home/mysql/backup/2021-09-16_22-44-18/'
MySQL binlog position: filename 'on.000005', position '719', GTID of the last change 'a2c030a0-1560-11ec-8c9d-08002762e5f2:1-8,
bd1b20b5-1563-11ec-9520-080027daf155:1'
210916 22:44:34 [00] Writing home/mysql/backup/2021-09-16_22-44-18/backup-my.cnf
210916 22:44:34 [00] ...done
210916 22:44:34 [00] Writing home/mysql/backup/2021-09-16_22-44-18/xtrabackup_info
210916 22:44:34 [00] ...done
xtrabackup: Transaction log of lsn (2780410) to (2780419) was copied.
210916 22:44:34 completed OK!
innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 --incremental homne/mysql/backup/1/
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental backups/ --incremental-basedir=/backups/2018-07-30_11-01-37
innobackupex --defaults-file=/etc/my.cnf --user=admin --password=123 --backup --target-dir=/home/mysql/backup/</code></pre>恢复：<pre><code>innobackupex --apply-log backups/2018-07-30_11-04-55/
innobackupex --copy-back --defaults-file=/etc/my.cnf backups/2018-07-30_11-04-55/</code></pre><h3>方式八：MEB</h3><pre><code>MySQL MEB备份和恢复
基于MySQL MEB的备份恢复
MEB（MySQL Enterprise Backup）是MySQL商业版中提供的备份工具，属于物理备份。
mysql enterprise版比community在备份工具上有了提升，多了一个mysqlbackup的工具，即mysql企业备份工具。
当然遵循GPL的社区版也可以用：
该工具单独下载：
到官方的下载中心：
点Sign up登录
然后搜索并选择平台，搜索mysql，找到企业版
同XtraBackup一样，mysqlbackup的使用过程同样包含如下三个步骤：
备份（--backup）=&gt; 应用日志（--apply-log）=&gt; 恢复（--copy-back）</code></pre><pre><code>https://edelivery.oracle.com/osdc/faces/SoftwareDelivery
MySQL Enterprise Backup for Server 8.0 8.0.26 for Linux x86-64
V1010830-01.zipMySQL Enterprise Backup 8.0.26 RPM for Oracle Linux RHEL 6 x86 (64bit), 36.0 MB
V1010832-01.zipMySQL Enterprise Backup 8.0.26 RPM for Oracle Linux RHEL 7 x86 (64bit), 24.3 MB
V1010834-01.zipMySQL Enterprise Backup 8.0.26 RPM for Oracle Linux RHEL 8 x86 (64bit), 21.8 MB
V1010835-01.zipMySQL Enterprise Backup 8.0.26 RPM for SuSE Enterprise Linux 15 openSUSE 15 x86 (64bit), 29.0 MB
V1010836-01.zipMySQL Enterprise Backup 8.0.26 RPM for SuSE Enterprise Linux 12 x86 (64bit), 23.3 MB
V1010837-01.zipMySQL Enterprise Backup 8.0.26 TAR for Oracle Linux RHEL 7 x86 (64bit), 36.7 MB
V1010839-01.zipMySQL Enterprise Backup 8.0.26 TAR for Generic Linux x86 (64bit), 51.7 MB
V1010849-01.zipMySQL Enterprise Backup 8.0.26 DEB for Debian 10 Linux x86 (64bit), 42.8 MB
V1010850-01.zipMySQL Enterprise Backup 8.0.26 DEB for Ubuntu 18.04 Linux x86 (64bit), 42.8 MB
V1010852-01.zipMySQL Enterprise Backup 8.0.26 DEB for Ubuntu 20.04 Linux x86 (64bit), 44.1 MB
V1010969-01.zipMySQL Enterprise Backup 8.0.26 Minimal TAR for Generic Linux (glibc2.17) x86 (64bit), 4.3 MB</code></pre><pre><code>[mysql@cjcos02 MEB]$ unzip V1010837-01.zip 
Archive: V1010837-01.zip
 extracting: mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz 
 extracting: mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz.asc 
 extracting: mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz.md5 
 extracting: README.txt 
 
[mysql@cjcos02 MEB]$ tar -zxvf mysql-commercial-backup-8.0.26-el7-x86_64.tar.gz
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libcrypto.so
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libcrypto.so.1.1
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libssl.so
mysql-commercial-backup-8.0.26-el7-x86_64/lib/private/libssl.so.1.1
mysql-commercial-backup-8.0.26-el7-x86_64/docs/INFO_BIN
mysql-commercial-backup-8.0.26-el7-x86_64/docs/INFO_SRC
mysql-commercial-backup-8.0.26-el7-x86_64/bin/mysqlbackup
mysql-commercial-backup-8.0.26-el7-x86_64/LICENSE.meb
mysql-commercial-backup-8.0.26-el7-x86_64/README.meb

[mysql@cjcos02 MEB]$ mv mysql-commercial-backup-8.0.26-el7-x86_64 usr/local/mysql/meb/
[mysql@cjcos02 MEB]$

http://blog.itpub.net/29773961/viewspace-1992615/
[mysql@cjcos02 bin]$ mkdir home/mysql/backup/meb</code></pre>全备：<pre><code>[mysql@cjcos02 bin]$ ./mysqlbackup -u root -p --backup-dir=/home/mysql/backup/meb backup-and-apply-log
MySQL Enterprise Backup Ver 8.0.26-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starting with following command line ...
./mysqlbackup
-u
root
-p
--backup-dir=/home/mysql/backup/meb
backup-and-apply-log

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-and-apply-log' run mysqlbackup
           prints &quot;mysqlbackup completed OK!&quot;.

Enter password: 
210920 21:23:31 MAIN     INFO: Establishing connection to server.
210920 21:23:31 MAIN     INFO: No SSL options specified.
210920 21:23:31 MAIN     INFO: MySQL server version is '5.7.35-log'
210920 21:23:31 MAIN     INFO: MySQL server compile os version is 'el7'
210920 21:23:32 MAIN    ERROR: MySQL query 'SELECT member_role, member_host, member_port, member_state FROM performance_schema.replication_group_members': 1054, Unknown column 'member_role' in 'field list'
210920 21:23:32 MAIN    ERROR: Connection initialization error.

mysqlbackup exit code: 19, Server returned error while executing sql

mysqlbackup failed with errors!</code></pre><pre><code>5.7版本，performance_schema.replication_group_members表没有member_role字段，在使用MEB8时，会报错。

5.7
MySQL [mysql]&gt; select version();
+------------+
| version()  |
+------------+
| 5.7.35-log |
+------------+
1 row in set (0.00 sec)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starting with following command line ...
mysqlbackup.exe
--backup-dir=D:\cjc\mysql\MEB\backup
backup-and-apply-log
-uroot
-p

Enter password: *
210921 11:42:32 MAIN     INFO: Establishing connection to server.
210921 11:42:32 MAIN     INFO: No SSL options specified.
210921 11:42:32 MAIN     INFO: MySQL server version is '8.0.26'
210921 11:42:32 MAIN     INFO: MySQL server compile os version is 'Win64'
210921 11:42:32 MAIN     INFO: SSL/TLS version used for connection is TLSv1.3
210921 11:42:32 MAIN     INFO: Got some server configuration information from running server.

210921 11:42:32 MAIN     INFO: Establishing connection to server for locking.
210921 11:42:32 MAIN     INFO: No SSL options specified.
210921 11:42:37 MAIN     INFO: Backup directory exists: 'D:\cjc\mysql\MEB\backup'
210921 11:42:37 MAIN     INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
210921 11:42:37 MAIN     INFO: Mysqlbackup component not installed.
210921 11:42:37 MAIN     INFO: MEB logfile created at D:\cjc\mysql\MEB\backup\meta\MEB_2021-09-21.11-42-37_backup-and-apply-log.log

210921 11:42:37 MAIN     INFO: The MySQL server has no active keyring.
--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                        = D:\cjc\mysql\mysql-8.0.26-winx64\data\
  innodb_data_home_dir           =
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = D:\cjc\mysql\mysql-8.0.26-winx64\data\
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = D:\cjc\mysql\mysql-8.0.26-winx64\data\
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32
--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                        = D:\cjc\mysql\MEB\backup\datadir
  innodb_data_home_dir           = D:\cjc\mysql\MEB\backup\datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = D:\cjc\mysql\MEB\backup\datadir
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = D:\cjc\mysql\MEB\backup\datadir
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

210921 11:42:37 MAIN     INFO: Unique generated backup id for this is 16321957525444995

210921 11:42:37 MAIN     INFO: Copying the server config file 'D:/cjc/mysql/mysql-8.0.26-winx64/data/auto.cnf'
210921 11:42:38 MAIN     INFO: Creating 14 buffers each of size 16777216.
210921 11:42:38 MAIN     INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
210921 11:42:38 MAIN     INFO: Found checkpoint at lsn 151862887.
210921 11:42:38 MAIN     INFO: Starting log scan from lsn = 151862784 at offset = 867840 and checkpoint = 151862887 in file D:/cjc/mysql/mysql-8.0.26-winx64/data/ib_logfile1.
210921 11:42:38 MAIN     INFO: Full Backup with Apply-log operation starts with following threads
                               1 read-threads    6 process-threads    1 write-threads
210921 11:42:38 MAIN     INFO: Starting to copy all innodb files...
210921 11:42:38 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/ibdata1.
210921 11:42:38 RLP1     INFO: Starting to parse redo log at lsn = 151862811, whereas checkpoint_lsn = 151862887 and start_lsn = 151862784.
210921 11:42:38 RDR1     INFO: Starting to copy all undo files...
210921 11:42:39 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/undo_002.
210921 11:42:39 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/undo_001.
210921 11:42:40 RDR1     INFO: Starting to lock instance for backup...
210921 11:42:40 RDR1     INFO: The server instance is locked for backup.
210921 11:42:41 RDR1     INFO: The server instance is unlocked after 0.826 seconds.
210921 11:42:43 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/sys/sys_config.ibd.
210921 11:42:43 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/cjcdb/t1.ibd.
210921 11:42:43 RDR1     INFO: Copying D:/cjc/mysql/mysql-8.0.26-winx64/data/cjcdb/t2.ibd.
......

210921 11:43:11 PCR1     INFO: Setting 'ib_logfile0' file size to 50331648
210921 11:43:16 PCR1     INFO: Setting 'ib_logfile1' file size to 50331648
210921 11:43:16 PCR1     INFO: Log file header:
         format = 4
         pad1 = 0
         start lsn = 151937024
         checkpoint lsn = 151937202
         checksum = 4172735474
         creator = MEB 8.0.26
210921 11:43:17 PCR1     INFO: We were able to parse ibbackup_logfile up to lsn 151937202.
210921 11:43:17 PCR1     INFO: Last MySQL binlog file position 0 156, file name binlog.000002
210921 11:43:17 PCR1     INFO: The first data file is 'D:/cjc/mysql/MEB/backup/datadir/ibdata1'
                               and the new created log files are at 'D:\cjc\mysql\MEB\backup\datadir'
210921 11:43:17 MAIN     INFO: Apply-log operation completed successfully.
210921 11:43:17 MAIN     INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!</code></pre>查看备份文件<pre><code>D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin&gt;cd D:\cjc\mysql\MEB\backup

D:\cjc\mysql\MEB\backup&gt;dir
 驱动器 D 中的卷是 LENOVO
 卷的序列号是 568B-1BF9

D:\cjc\mysql\MEB\backup 的目录
2021/09/21 11:43 &lt;DIR&gt; .
2021/09/21 11:43 &lt;DIR&gt; ..
2021/09/21 11:42 325 backup-my.cnf
2021/09/21 11:43 &lt;DIR&gt; datadir
2021/09/21 11:43 &lt;DIR&gt; meta
2021/09/21 11:43 20,032 server-all.cnf
2021/09/21 11:43 452 server-my.cnf
 3 个文件 20,809 字节
 4 个目录 82,218,188,800 可用字节</code></pre>恢复：<pre><code>D:\cjc\mysql\MEB\backup
mysql&gt; use cjcdb;
Database changed

mysql&gt; show tables;
+-----------------+
| Tables_in_cjcdb |
+-----------------+
| t1              |
| t2              |
| t3              |
| t5              |
| t6              |
| t7              |
+-----------------+
6 rows in set (0.01 sec)

mysql&gt; drop database cjcdb;
Query OK, 6 rows affected (5.20 sec)

mysql&gt; show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)</code></pre>应用日志：<pre><code>D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin&gt;mysqlbackup --backup-dir=D:\cjc\mysql\MEB\backup apply-log
MySQL Enterprise Backup Ver 8.0.26-commercial for Win64 on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starting with following command line ...
mysqlbackup
--backup-dir=D:\cjc\mysql\MEB\backup
apply-log

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'apply-log' run mysqlbackup
           prints &quot;mysqlbackup completed OK!&quot;.

210921 15:24:48 MAIN     INFO: read_backup_variables_txt_file: 'D:\cjc\mysql\MEB\backup\meta\backup_variables.txt'
210921 15:24:48 MAIN     INFO: backup variable mysql_version=8.0.26
210921 15:24:48 MAIN     INFO: MySQL server version is '8.0.26'
210921 15:24:48 MAIN     INFO: Restoring ...8.0.26 version
210921 15:24:48 MAIN     INFO: backup variable meb_version=8.0.26
210921 15:24:48 MAIN     INFO: backup variable start_lsn=151862784
......

innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

210921 15:24:49 MAIN     INFO: Apply-log operation has already been done on that backup.

mysqlbackup completed OK!</code></pre>恢复<pre><code>D:\cjc\mysql\MEB\mysql-commercial-backup-8.0.26-winx64\bin&gt;
mysqlbackup --defaults-file=D:\cjc\mysql\mysql-8.0.26-winx64\my.ini --datadir=D:\cjc\mysql\mysql-8.0.26-winx64\data1 --backup-dir=D:\cjc\mysql\MEB\backup copy-back --force
 D:\cjc\mysql\mysql-8.0.26-winx64\data1 的目录

2021/09/21 17:01 &lt;DIR&gt; .
2021/09/21 17:01 &lt;DIR&gt; ..
2021/09/21 17:01 56 backup-auto.cnf
2021/09/21 17:01 828 backup_variables.txt
2021/09/21 17:01 37,934,399 binlog.000001
2021/09/21 17:01 156 binlog.000002
2021/09/21 17:01 32 binlog.index
2021/09/21 17:01 &lt;DIR&gt; cjcdb
2021/09/21 17:01 12,582,912 ibdata1
2021/09/21 17:01 8,724 ib_buffer_pool
2021/09/21 17:01 50,331,648 ib_logfile0
2021/09/21 17:01 50,331,648 ib_logfile1
2021/09/21 17:01 &lt;DIR&gt; mysql
2021/09/21 17:01 25,165,824 mysql.ibd
2021/09/21 17:01 &lt;DIR&gt; performance_schema
2021/09/21 17:01 20,032 server-all.cnf
2021/09/21 17:01 452 server-my.cnf
2021/09/21 17:01 &lt;DIR&gt; sys
2021/09/21 17:01 16,777,216 undo_001
2021/09/21 17:01 16,777,216 undo_002
 14 个文件 209,931,143 字节
 6 个目录 82,003,558,400 可用字节</code></pre><h3>方式九：冷备</h3>查看当前数据：<pre><code>MySQL [cjcdb01]&gt; select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 3 | a |
| 2 | a |
+------+------+
3 rows in set (0.00 sec)</code></pre>查看数据文件目录：<pre><code>MySQL [cjcdb01]&gt; show variables like '%datadir%';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)</code></pre>停库：<pre><code>[mysql@cjcos02 ~]$ service mysqld status
 SUCCESS! MySQL running (3941)
 
[mysql@cjcos02 ~]$ service mysqld stop
Shutting down MySQL............ SUCCESS!</code></pre>备份数据文件:<pre><code>[mysql@cjcos02 ~]$ cd /usr/local/mysql/
[mysql@cjcos02 mysql]$ mkdir data_bak
[mysql@cjcos02 mysql]$ cp -r data/ data_bak/

[mysql@cjcos02 mysql]$ du -sh data*
122Mdata
122Mdata_bak</code></pre>启库:<pre><code>[mysql@cjcos02 mysql]$ service mysqld start
Starting MySQL.. SUCCESS!</code></pre>模拟误操作：<pre><code>MySQL [(none)]&gt; drop database cjcdb01;
Query OK, 4 rows affected (0.04 sec)

MySQL [(none)]&gt; select * from cjcdb01.test1;
ERROR 1146 (42S02): Table 'cjcdb01.test1' doesn't exist</code></pre>恢复：<pre><code>[mysql@cjcos02 mysql]$ service mysqld stop
Shutting down MySQL.. SUCCESS! 
[mysql@cjcos02 mysql]$ mv data data_01
[mysql@cjcos02 mysql]$ mv data_bak/data data
[mysql@cjcos02 mysql]$ service mysqld start</code></pre>验证：<pre><code>MySQL [(none)]&gt; show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cjcdb01 |
| cjcdb02 |
| cjcdb03 |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)

MySQL [(none)]&gt; select * from cjcdb01.test1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 3 | a |
| 2 | a |
+------+------+
3 rows in set (0.01 sec)</code></pre><h3>方式十：闪回</h3>开源工具binlog2sql来进行实战演练。binlog2sql由美团点评DBA团队(上海)出品，多次在线上环境做快速回滚。安装binlog2sql：<pre><code>[mysql@cjcos02 ~]$ git clone https://github.com/danfengcao/binlog2sql.git
Cloning into 'binlog2sql'...
remote: Enumerating objects: 323, done.
remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323
Receiving objects: 100% (323/323), 153.26 KiB | 0 bytes/s, done.
Resolving deltas: 100% (170/170), done.

[mysql@cjcos02 ~]$ cd binlog2sql/
[mysql@cjcos02 binlog2sql]$ ls
binlog2sql  example  LICENSE  README.md  requirements.txt  tests

[mysql@cjcos02 binlog2sql]$ cat requirements.txt 
PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13

[mysql@cjcos02 binlog2sql]$ pip install -r requirements.txt
bash: pip: command not found...

[mysql@cjcos02 binlog2sql]$ curl -O https://bootstrap.pypa.io/pip/2.7/get-pip.py
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 1863k  100 1863k    0     0  1751k      0  0:00:01  0:00:01 --:--:-- 1751k
[mysql@cjcos02 binlog2sql]$ python get-pip.py
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Defaulting to user installation because normal site-packages is not writeable
Collecting pip&lt;21.0
  Downloading pip-20.3.4-py2.py3-none-any.whl (1.5 MB)
     |████████████████████████████████| 1.5 MB 822 kB/s 
Collecting wheel
  Downloading wheel-0.37.0-py2.py3-none-any.whl (35 kB)
Installing collected packages: pip, wheel
Successfully installed pip-20.3.4 wheel-0.37.0

[mysql@cjcos02 binlog2sql]$ pip install -r requirements.txt
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Defaulting to user installation because normal site-packages is not writeable
Collecting PyMySQL==0.7.11
 Downloading PyMySQL-0.7.11-py2.py3-none-any.whl (78 kB)
 |████████████████████████████████| 78 kB 592 kB/s 
Collecting wheel==0.29.0
 Downloading wheel-0.29.0-py2.py3-none-any.whl (66 kB)
 |████████████████████████████████| 66 kB 671 kB/s 
Collecting mysql-replication==0.13
 Downloading mysql-replication-0.13.tar.gz (33 kB)
Building wheels for collected packages: mysql-replication
 Building wheel for mysql-replication (setup.py) ... done
 Created wheel for mysql-replication: filename=mysql_replication-0.13-py2-none-any.whl size=41107 sha256=164b5775a156b1de450699de8e05849d338623df15a15a465c2c1f3b75179c6b
 Stored in directory: /home/mysql/.cache/pip/wheels/7f/98/ad/902f1ff5bfac7e8ea16480601e85db4b87b6c608d26e294b81
Successfully built mysql-replication
Installing collected packages: PyMySQL, wheel, mysql-replication
 Attempting uninstall: wheel
 Found existing installation: wheel 0.37.0
 Uninstalling wheel-0.37.0:
 Successfully uninstalled wheel-0.37.0
Successfully installed PyMySQL-0.7.11 mysql-replication-0.13 wheel-0.29.0</code></pre>创建测试数据<pre><code>MySQL [cjcdb01]&gt; create table test1(id int,name char(50));
Query OK, 0 rows affected (0.11 sec)

MySQL [cjcdb01]&gt; insert into test1 values(1,'a'),(2,'a'),(3,'a');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

MySQL [cjcdb01]&gt; select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
+------+------+
3 rows in set (0.00 sec)

MySQL [cjcdb01]&gt; delete from test1 where id=2;
Query OK, 1 row affected (0.05 sec)

MySQL [cjcdb01]&gt; select now();
+---------------------+
| now()               |
+---------------------+
| 2021-09-21 16:49:07 |
+---------------------+
1 row in set (0.06 sec)

MySQL [cjcdb01]&gt; show master logs;
+-----------+-----------+
| Log_name  | File_size |
+-----------+-----------+
| on.000001 |       177 |
| on.000002 |       177 |
| on.000003 |       177 |
| on.000004 |       573 |
| on.000005 |     15257 |
| on.000006 |       217 |
| on.000007 |       927 |
+-----------+-----------+
7 rows in set (0.00 sec)

[mysql@cjcos02 binlog2sql]$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'1' -dcjcdb01 --start-file='on.000007' --start-datetime='2021-09-21 16:30:01' --stop-datetime='2021-09-21 18:55:15' &gt; /home/mysql/0001.sql
[mysql@cjcos02 binlog2sql]$ cat /home/mysql/0001.sql 
USE cjcdb01;
create table test1(id int,name char(50));
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (1, 'a'); #start 444 end 629 time 2021-09-21 16:46:21
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (2, 'a'); #start 444 end 629 time 2021-09-21 16:46:21
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (3, 'a'); #start 444 end 629 time 2021-09-21 16:46:21
DELETE FROM `cjcdb01`.`test1` WHERE `id`=2 AND `name`='a' LIMIT 1; #start 725 end 896 time 2021-09-21 16:48:54

MySQL [cjcdb01]&gt; show binlog events in 'on.000007';
+-----------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name  | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+-----------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| on.000007 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.35-log, Binlog ver: 4                              |
| on.000007 | 123 | Previous_gtids |         1 |         194 | c3c28ba2-19c3-11ec-971b-08002765a9b1:1-54                          |
| on.000007 | 194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:55' |
| on.000007 | 259 | Query          |         1 |         379 | use `cjcdb01`; create table test1(id int,name char(50))            |
| on.000007 | 379 | Gtid           |         1 |         444 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:56' |
| on.000007 | 444 | Query          |         1 |         519 | BEGIN                                                              |
| on.000007 | 519 | Table_map      |         1 |         573 | table_id: 121 (cjcdb01.test1)                                      |
| on.000007 | 573 | Write_rows     |         1 |         629 | table_id: 121 flags: STMT_END_F                                    |
| on.000007 | 629 | Xid            |         1 |         660 | COMMIT /* xid=69 */                                                |
| on.000007 | 660 | Gtid           |         1 |         725 | SET @@SESSION.GTID_NEXT= 'c3c28ba2-19c3-11ec-971b-08002765a9b1:57' |
| on.000007 | 725 | Query          |         1 |         800 | BEGIN                                                              |
| on.000007 | 800 | Table_map      |         1 |         854 | table_id: 121 (cjcdb01.test1)                                      |
| on.000007 | 854 | Delete_rows    |         1 |         896 | table_id: 121 flags: STMT_END_F                                    |
| on.000007 | 896 | Xid            |         1 |         927 | COMMIT /* xid=71 */                                                |
+-----------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
14 rows in set (0.00 sec)

[mysql@cjcos02 binlog2sql]$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'1' -dcjcdb01 --start-file='on.000007' --start-position=725 --stop-position=896 -B &gt; /home/mysql/0002.sql
[mysql@cjcos02 binlog2sql]$ cat /home/mysql/0002.sql 
INSERT INTO `cjcdb01`.`test1`(`id`, `name`) VALUES (2, 'a'); #start 725 end 896 time 2021-09-21 16:48:54

[mysql@cjcos02 binlog2sql]$ mysql -h127.0.0.1 -P3306 -uroot -p'1'  &lt; /home/mysql/0002.sql

MySQL [cjcdb01]&gt; select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 3 | a |
| 2 | a |
+------+------+
3 rows in set (0.00 sec)</code></pre><pre><code>关于DDL的flashback
本文所述的flashback仅针对DML语句的快速回滚。
但如果误操作是DDL的话，是无法利用binlog做快速回滚的，因为即使在row模式下，binlog对于DDL操作也不会记录每行数据的变化。
要实现DDL快速回滚，必须修改MySQL源码，使得在执行DDL前先备份老数据。
目前有多个mysql定制版本实现了DDL闪回特性，阿里林晓斌团队提交了patch给MySQL官方，MariaDB预计在不久后加入包含DDL的flashback特性。
DDL闪回的副作用是会增加额外存储。考虑到其应用频次实在过低，本文不做详述，有兴趣的同学可以自己去了解，重要的几篇文章我在参考资料中做了引用。</code></pre><img src="https://gcore.jsdelivr.net/gh/546426894/typecho@master/usr/uploads/2021/10/3430680571.png" alt="" title="" style=""><h3>方式十一: 快照备份</h3><pre><code>使用的比较少，需要存储或系统工程师配合完成。
LVM使用写时复制（copy-on-write）技术来创建快照
锁定所有表，或停库
FLUSH TABLES WITH READ LOCK;
创建快照
解锁</code></pre><img src="https://gcore.jsdelivr.net/gh/546426894/typecho@master/usr/uploads/2021/10/2650700982.png" alt="" title="" style=""><h3>方式十二：mysqlhotcopy</h3><pre><code>mysql5.7已经没有这个命令了，多用于mysql5.5之前。
mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表，属于裸文件备份（物理备份），只能用于MyISAM引擎的数据库。
本质是使用锁表语句，然后cp或scp。
常用命令如下：

备份一个数据库到目标目录
mysqlhotcopy -u [用户名] -p [密码] [数据库名] [目标目录]

备份多个数据库到目标目录
mysqlhotcopy -u [用户名] -p [密码] [数据库名1 数据库名2...] [目标目录]

备份数据库中的某个表到指定目录
mysqlhotcopy -u [用户名] -p [密码] [数据库名]./[表名]/ [目标目录]

还原
在Linux中Mysql数据库都是以物理文件存在的，数据库对应的是目录。如果想要还原只要将原来的文件拷贝回原目录即可。
单个数据库备份还原方式：如果文件所有这和所属组不是mysql，请使用chown修改。
cp -ra [目标目录] /var/lib/mysql/[数据库名]/</code></pre>

MySQL备份和恢复方法汇总

Mysql备份类型

方式一：CTAS

craete table xx as ...用来创建相同表结构并复制源表数据,不包含索引等。

create table xx like ...用来创建完整表结构和全部索引。

方式二：create table table_name_bak like table_name;

方式三：select into outfile

方式四：Mysqldump

方式五：mysqlbinlog恢复数据

方式六：mysqldumper/myloader

方式七：xtrbackup

方式八：MEB

方式九：冷备

方式十：闪回

方式十一: 快照备份

方式十二：mysqlhotcopy

相关文章

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

handsome 换字体

Oracle 数据库常用安装包合集（包含基础版和最新补丁集202107）

超全 Linux 安装包合集(Centos Redhat Oracle Linux)

本站typecho 主题版本修改的东西(8.40版本和typecho开发版1.2)

影视资源站接口

PLSQL查询语句中日期格式使用不当会导致性能和效率很低

计算机网络原理

禧玛诺SHIMANO套件介绍

Oracle精读44_表空间数据文件自动扩展

Oracle数据库巡检，详细关键点

MySQL备份和恢复方法汇总

Mysql备份类型

方式一：CTAS

craete table xx as ...用来创建相同表结构并复制源表数据,不包含索引等。

create table xx like ...用来创建完整表结构和全部索引。

方式二：create table table_name_bak like table_name;

方式三：select into outfile

方式四：Mysqldump

方式五：mysqlbinlog恢复数据

方式六：mysqldumper/myloader

方式七：xtrbackup

方式八：MEB

方式九：冷备

方式十：闪回

方式十一: 快照备份

方式十二：mysqlhotcopy

相关文章

发表评论 取消回复 # 请输入真实的邮箱地址便于接收回复

MySQL备份和恢复方法汇总

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复