热门标签
当前位置: article > 正文
MySQL基于gtid主从复制_mysql gtid从库重做
作者：黑客灵魂 | 2024-08-10 16:28:17
赞
踩
mysql gtid从库重做
一、Gtid简介
从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
二、Gtid的工作原理
GTID是MySQL 5.6的新特性，其全称是Global Transaction Identifier，可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识一个事务。当事务提交时，MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID，然后再写事务的Binlog。主从同步时GTID_Event和事务的Binlog都会传递到从库，从库在执行的时候也是用同样的GTID写binlog，这样主从同步以后，就可通过GTID确定从库同步到的位置了。
三、Gtid的作用
Gtid采用了新的复制协议，旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。
新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。
四、配置
主机名 ip
master 192.168.58.120
slave 192.168.58.121
1.1 授权用户，用于主从
mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | OFF | | gtid_executed_compression_period | 1000 | | gtid_mode | OFF | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.01 sec) mysql> create user 'xu'@'192.168.58.121' identified by "xu1"; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave on *.* to 'xu'@'192.168.58.121' ; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
- 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
1.2开启GTID的必备条件
MySQL 5.6
- gtid_mode=ON(必选)
- log_bin=ON(必选)
- log-slave-updates=ON(必选)
- enforce-gtid-consistency(必选)
- 1
- 2
- 3
- 4
- 5
MySQL 5.7
- gtid_mode=ON(必选)
- enforce-gtid-consistency（必选）
- log_bin=ON（可选）
- log-slave-updates=ON（可选）
- 1
- 2
- 3
- 4
- 5
1.3 修改master主配文件
[root@localhost ~]# vim /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve #replication config log-bin = mysql-bin #开启二进制日志 server-id = 1 gtid-mode = on #开启gtid模式 enforce-gtid-consistency = on #强制gtid一致性，开启后对特定的create table不被支持 binlog-format = row #默认为mixed混合模式，更改成row复制，为了数据一致性 log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志 skip-slave-start = 1 #跳过slave复制线程 [root@localhost ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000011 | 1924 | | | c736875b-097e-11ec-b557-000c29810dc2:1-8 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
- 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
slave主配文件
[root@localhost ~]# vim /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve # replication config server-id = 2 log-bin = mysql-bin binlog-format = row skip-slave-start = 1 log-slave-updates = 1 gtid-mode = on enforce-gtid-consistency = on [root@localhost ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
检查gtid状态
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> reset slave; Query OK, 0 rows affected (0.01 sec) mysql> change master to -> master_host='192.168.153.133' , -> master_user='xu' , -> master_password='xu1' , -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave ; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.58.120 Master_User: xu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 133 Relay_Log_File: localhost-relay-bin.000003 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000011 Slave_IO_Running: Yes Slave_SQL_Running: Yes
- 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
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
验证
mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | xu | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql_bin.000005 | 1423 | | | c736875b-097e-11ec-b557-000c29810dc2:1-9 | +------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec) 已经在slave主机进行了同步，
- 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
一主多从
主机名 ip
master 192.168.58.120
slave 192.168.58.121
slave2 192.168.58.122
配置一主多从，只需在主库master再上添加从库就行
第一台主库配置与之前配置一致
第一台从库与上一致
第二台从库配置
slave主配文件 [root@localhost ~]# vim /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve # replication config server-id = 3 //第三台从服务器id 不重复 log-bin = mysql-bin binlog-format = row skip-slave-start = 1 log-slave-updates = 1 gtid-mode = on enforce-gtid-consistency = on [root@localhost ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS
- 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
配置slave主机
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> reset slave; Query OK, 0 rows affected (0.01 sec) mysql> change master to -> master_host='192.168.58.120' , -> master_user='xu' , -> master_password='xu1' , -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave ; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.58.120 Master_User: xu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 1542 Relay_Log_File: localhost-relay-bin.000005 Relay_Log_Pos: 511 Relay_Master_Log_File: mysql-bin.000011 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | xu | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+ | mysql_bin.000008 | 694 | | | b736875b-097e-16ec-b557-000c29810dc2:1-10, ba15a7ff-097f-11ec-9272-000c29859565:1, dca6f885-0a26-11ec-81cc-000c29g2278e:1 | +------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
- 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
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
双主多从
主库配置如之前配置的方法一致详细步骤省略
(1)查看主库
//主库1 +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000011 | 2064 | | | c736875b-097e-11ec-b557-000c26782dc2:1-8 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) 主库2 mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000004 | 176 | | | c736875b-097e-11ec-b557-000c26782c2:1-10 | +------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
(2)从库配置
修改配置文件省略，具体步骤和前面一致
配置slave主机
//检查gtid模式状态 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> reset slave; Query OK, 0 rows affected (0.01 sec) 在从库上配置主库信息 //配置主库1 //下面两条命令不执行，后续会报错 mysql> set global master_info_repository='table'; Query OK, 0 rows affected (0.00 sec) mysql> set global relay_log_info_repository='table'; Query OK, 0 rows affected (0.00 sec) mysql> change master to -> master_host='192.168.58.120' , -> master_user='xu' , -> master_password='xu1' , -> master_log_file='mysql-bin.000011' -> master_log_pos=2064 -> for channel 'master-1'; //创建隧道 配置主库2 mysql> change master to -> master_host='192.168.58.121' , -> master_user='xu' , -> master_password='xu1' , -> master_log_file='mysql-bin.000004' -> master_log_pos=176 -> for channel 'master-2'; //创建隧道 mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec) //查看发现都已经开启 mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.58.120 Master_User: xu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 2064 Relay_Log_File: localhost-relay-bin-master@002d1.000002 Relay_Log_Pos: 312 Relay_Master_Log_File: mysql-bin.000011 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2064 Relay_Log_Space: 523 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: c736875b-097e-11ec-b557-000c29810dc2 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: baf6e1ae-0a32-11ec-bf3d-000c29f2278e:1 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: master-1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.58.121 Master_User: xu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 176 Relay_Log_File: localhost-relay-bin-master@002d2.000002 Relay_Log_Pos: 312 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 176 Relay_Log_Space: 523 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: ca11a7ff-097f-11ec-9272-000c29959565 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: baf6e1ae-0a32-11ec-bf3d-000c29f2278e:1 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: master-2 Master_TLS_Version: 2 rows in set (0.00 sec) //测试 主库1上创建数据库xu mysql> create database xu; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | xu | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) 主库2上创建数据库student mysql> create database student; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | student | +--------------------+ 5 rows in set (0.00 sec) 从库上开启从服务后查看 mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | xu | | mysql | | performance_schema | | sys | | student | +--------------------+ 6 rows in set (0.00 sec)
- 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
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：【wpsshop博客】
推荐阅读
相关标签
