ITPub博客

首页 > 数据库 > MySQL > MySQL备份和恢复方法汇总

MySQL备份和恢复方法汇总

原创 MySQL 作者:chenoracle 时间:2021-09-21 22:26:21 0 删除 编辑

一: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

参考链接:


mysqldump无法并行,这点与Oracle的expdp相比,存在一定的劣势,但是开源的mysqldumper是一个很好的补充

mydumper()是一个在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


软件地址:



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备份工具

网站域名:

[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)


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 
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 
  % 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/[数据库名]/

#####chenjuchao 20210921 22:25#####

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2792933/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE、OCMU 用户组成员、Oracle 11g OCM、微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    633
  • 访问量
    1547272