1.GTID是什么
|
1 2 3 4 5 6 7 8 9 10 11 12 |
1.GTID是一个事务标识符 2.这个标识符不仅仅在一台机器上是唯一的，在一个集群中都是唯一的 3.GTID实际上是由 UUID + TID 组成的 #UUID [root@db01 ~]# cat /usr/local/mysql/data/auto.cnf [auto] server-uuid=e92aaff7-1f07-11eb-b7de-000c296ca6bc #TID 提交事务的ID，次数 1 2 3 4 #GTID形式 e92aaff7-1f07-11eb-b7de-000c296ca6bc:1 e92aaff7-1f07-11eb-b7de-000c296ca6bc:2 |
2.GTID的优缺点
1）优点
|
1 2 3 4 |
1.GTID会把主从信息记录到数据库中 2.在做主从同步的时候不需要自己指定binlog名字和位置点 3.普通的主从复制，只有一个sql线程，GTID的主从一个库有一个sql线程 4.binlog的记录方式，如果是row模式，只记录修改的列，日志量相对较少 |
2）缺点
|
1 2 |
1.mysql数据备份的时候，必须要加一个参数 --set-gtid-purged=off 2.当数据库sql线程出错的时候，没有办法跳过 |
3.基于GTID主从复制的搭建
1）主库配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
#主库配置 [root@db01 ~]# vim /etc/my.cnf [mysqld] server_id=1 log_bin=mysql-bin #从库1配置 [root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=2 #从库2配置 [root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=3 |
2）查看gtid是否启动
|
1 |
mysql> show variables like '%gtid%'; |
3）配置开启gtid
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
#主库 [root@db01 ~]# vim /etc/my.cnf [mysqld] server_id=1 log_bin=mysql-bin gtid_mode=on enforce_gtid_consistency=on #从库1 [root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=2 gtid_mode=on enforce_gtid_consistency=on #从库2 [root@db03 ~]# vim /etc/my.cnf [mysqld] server_id=3 gtid_mode=on enforce_gtid_consistency=on |
4）重启数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
#重启出错 [root@db01 ~]# systemctl restart mysqld Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details. #查看日志 [root@db01 ~]# less /usr/local/mysql/data/db01.err 2020-11-06 12:17:52 73178 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires - -log-bin and --log-slave-updates #修改配置文件 #主库 [root@db01 ~]# vim /etc/my.cnf [mysqld] server_id=1 log_bin=mysql-bin log-slave-updates gtid_mode=on enforce_gtid_consistency=on #从库1 [root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=2 log_bin=mysql-bin log-slave-updates gtid_mode=on enforce_gtid_consistency=on #从库2 [root@db03 ~]# vim /etc/my.cnf [mysqld] server_id=3 log_bin=mysql-bin log-slave-updates gtid_mode=on enforce_gtid_consistency=on |
5）授权一个主从用户
|
1 |
mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123'; |
6）从库配置主从
|
1 2 3 4 5 |
change master to master_host='172.16.1.51', master_user='rep', master_password='123', master_auto_position=1; |
7）开启线程
|
1 2 |
mysql> start slave; Query OK, 0 rows affected (0.00 sec) |
8）查看主从状态
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.1.51 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 151 Relay_Log_File: db02-relay-bin.000002 Relay_Log_Pos: 361 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.1.51 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 151 Relay_Log_File: db03-relay-bin.000002 Relay_Log_Pos: 361 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes |