1.开启GTID
1. 修改配置文件，在my.cnf
加入server_id=100;
[mysqld]
server_id=100
gtid-mode=ON
enforce-gtid-consistency=true
log-slave-updates=ON
log_bin=/mysql_log/log_bin_3306/mysql-log-bin
port=3306
user=mysql
basedir=/opt/mysql
datadir=/linux0224/mysql_3306/
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
2.重启mysql
[root@VM-4-8-centos etc]# systemctl restart mysqld
3.查看关于gtid的mysql内置变量,是否设置成功
[root@VM-4-8-centos etc]# mysql -uroot -psummer66
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%GTID%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+--------------------------
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000007 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> mysql> show binlog events in 'mysql-log-bin.000007';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-log-bin.000007 | 4 | Format_desc | 100 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-log-bin.000007 | 123 | Previous_gtids | 100 | 154 | |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
4.创建数据库,并查看当前的GTID
mysql> show master status;
+----------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+----------------------------------------+
| mysql-log-bin.000007 | 313 | | | fc8cecdf-65e8-11ef-ba5e-525400f2542b:1 |
+----------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-log-bin.000007';
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-log-bin.000007 | 4 | Format_desc | 100 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-log-bin.000007 | 123 | Previous_gtids | 100 | 154 | |
| mysql-log-bin.000007 | 154 | Gtid | 100 | 219 | SET @@SESSION.GTID_NEXT= 'fc8cecdf-65e8-11ef-ba5e-525400f2542b:1' |
| mysql-log-bin.000007 | 219 | Query | 100 | 313 | create database hehe |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)
5.创建数据表,并查看当前的GTID
mysql> use hehe;
Database changed
mysql> create table xixi;
mysql> create table xixi(name char(50));
Query OK, 0 rows affected (0.04 sec)
5.插入表
mysql> insert into xixi values('ceshi'),('ceshi2'),('ceshi3');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
6.删除表
mysql> drop table xixi;
Query OK, 0 rows affected (0.03 sec)
mysql>
7.基于GTID 恢复表数据
语法：mysqlbinlog --skip-gtids --include-gtids='fc8cecdf-65e8-11ef-ba5e-525400f2542b:2-3' mysql-log-bin.000007 > /opt/huifu_xixi_table.txt
注意参数的添加，--skip-gtids ，不加mysql会进行gtid记录的幂等性检查，导入sql会报错
8.操作恢复数据
mysql> exit
Bye
[root@VM-4-8-centos etc]# cd /mysql_log/
[root@VM-4-8-centos mysql_log]# ls
log_bin_3306
[root@VM-4-8-centos mysql_log]# cd log_bin_3306/
[root@VM-4-8-centos log_bin_3306]# ls
mysql-log-bin.000001 mysql-log-bin.000002 mysql-log-bin.000003 mysql-log-bin.000004 mysql-log-bin.000005 mysql-log-bin.000006 mysql-log-bin.000007 mysql-log-bin.index
[root@VM-4-8-centos log_bin_3306]# mysqlbinlog --skip-gtids --include-gtids='fc8cecdf-65e8-11ef-ba5e-525400f2542b:2-3' mysql-log-bin.000007 > /opt/huifu_xixi_table.txt
[root@VM-4-8-centos log_bin_3306]# ls /opt/
all-3306.sql elasticsearch-7.17.0-linux-x86_64.tar.gz huifu.txt mysql nginx-php V6.06.tar.gz
api elasticsearch-analysis-ik-7.17.0.zip huifu_xixi_table.txt mysql-5.7.26-linux-glibc2.12-x86_64 rh
conf elasticsearch-analysis-pinyin-7.17.0.zip kibana-7.17.0-linux-x86_64.tar.gz mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz test
conf.d fastdfs-nginx-module-1.22.tar.gz log nginx tomcat
containerd html logs nginx-1.19.2.tar.gz V1.0.43.tar.gz
db-4.7.25.tar.gz huifu2.txt mellanox nginx.conf V1.22.tar.gz
[root@VM-4-8-centos log_bin_3306]#
# 关闭binlog记录
mysql> set sql_log_bin=0;
#恢复
mysql> source /opt/huifu_xixi_table.txt
#开启binlog记录
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from hehe.xixi;
+--------+
| name |
+--------+
| ceshi |
| ceshi2 |
| ceshi3 |
+--------+
3 rows in set (0.00 sec)
mysql>
