devbox
你好赵伟
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

GTID复制

作者：你好赵伟 | 2024-08-10 16:20:20

赞

踩

gtid复制

GTID复制

  1. GTID（global transaction identifier，全局事务ID）是5.6新特性，为每个事务分配一个唯一的ID。事务ID的格式
  2. source_id:transaction_id
  3.  
  4. source_id通常用实例的server_uuid表示
  5. transaction_id是事务的序列号，从1开始

GTID搭建

  1. 1、参数配置
  2.  
  3. 主从节点都要配置
  4.  
  5. root@localhost [mysql]> select @@gtid_mode;
  6. +-------------+
  7. | @@gtid_mode |
  8. +-------------+
  9. | OFF         |
  10. +-------------+
  11. 1 row in set (0.01 sec)
  12.  
  13. root@localhost [mysql]> select @@enforce_gtid_consistency;
  14. +----------------------------+
  15. | @@enforce_gtid_consistency |
  16. +----------------------------+
  17. | OFF                        |
  18. +----------------------------+
  19. 1 row in set (0.00 sec)
  20.  
  21.  
  22. 主从节点都要修改配置文件
  23. gtid_mode=on
  24. enforce_gtid_consistency=1
  25.  
  26.  
  27. 添加完参数后，重启服务
  28. [root@node01 mysql]# systemctl restart mysqld
  29. [root@node02 mysql]# systemctl restart mysqld
  30. [root@node03 mysql]# systemctl restart mysqld
  31.  
  32.  
  33. root@localhost [hr]> select @@enforce_gtid_consistency;
  34. +----------------------------+
  35. | @@enforce_gtid_consistency |
  36. +----------------------------+
  37. | ON                         |
  38. +----------------------------+
  39. 1 row in set (0.00 sec)
  40.  
  41. root@localhost [hr]> select @@gtid_mode;
  42. +-------------+
  43. | @@gtid_mode |
  44. +-------------+
  45. | ON          |
  46. +-------------+
  47. 1 row in set (0.00 sec)
  48.  
  49. 2、change master to
  50. 从库执行
  51. gtid复制只需要设置master_auto_position=1，无需设置maser_log_file和master_log_pos参数
  52.  
  53. change master to
  54.     master_host='192.168.6.101',
  55.     master_port=3306,
  56.     master_user='repl',
  57.     master_password='oracle',
  58.     master_auto_position=1,
  59.     get_master_public_key=1;
  60.  
  61. 从库node02执行
  62.  
  63. 需要先关闭slave
  64.  
  65. root@localhost [(none)]> stop slave;
  66. Query OK, 0 rows affected, 1 warning (0.00 sec)
  67.  
  68. node03
  69. root@localhost [(none)]> stop slave;
  70. Query OK, 0 rows affected, 1 warning (0.02 sec)
  71.  
  72. root@localhost [(none)]> 
  73.  
  74.  
  75. root@localhost [(none)]> start slave;
  76. Query OK, 0 rows affected, 1 warning (0.00 sec)
  77.  
  78. root@localhost [(none)]> start slave;
  79. Query OK, 0 rows affected, 1 warning (0.00 sec)
  80.  
  81.  
  82. root@localhost [(none)]> show slave status\G
  83. *************************** 1. row ***************************
  84.                Slave_IO_State: Waiting for source to send event
  85.                   Master_Host: 192.168.6.101
  86.                   Master_User: repl
  87.                   Master_Port: 3306
  88.                 Connect_Retry: 60
  89.               Master_Log_File: binlog.000010
  90.           Read_Master_Log_Pos: 157
  91.                Relay_Log_File: node02-relay-bin.000003
  92.                 Relay_Log_Pos: 367
  93.         Relay_Master_Log_File: binlog.000010
  94.              Slave_IO_Running: Yes
  95.             Slave_SQL_Running: Yes
  96.               Replicate_Do_DB: 
  97.           Replicate_Ignore_DB: 
  98.            Replicate_Do_Table: 
  99.        Replicate_Ignore_Table: 
  100.       Replicate_Wild_Do_Table: 
  101.   Replicate_Wild_Ignore_Table: 
  102.                    Last_Errno: 0
  103.                    Last_Error: 
  104.                  Skip_Counter: 0
  105.           Exec_Master_Log_Pos: 157
  106.               Relay_Log_Space: 758
  107.               Until_Condition: None
  108.                Until_Log_File: 
  109.                 Until_Log_Pos: 0
  110.            Master_SSL_Allowed: No
  111.            Master_SSL_CA_File: 
  112.            Master_SSL_CA_Path: 
  113.               Master_SSL_Cert: 
  114.             Master_SSL_Cipher: 
  115.                Master_SSL_Key: 
  116.         Seconds_Behind_Master: 0
  117. Master_SSL_Verify_Server_Cert: No
  118.                 Last_IO_Errno: 0
  119.                 Last_IO_Error: 
  120.                Last_SQL_Errno: 0
  121.                Last_SQL_Error: 
  122.   Replicate_Ignore_Server_Ids: 
  123.              Master_Server_Id: 1001
  124.                   Master_UUID: 8ba057c3-2d10-11ee-a0fa-000c29004cdf
  125.              Master_Info_File: mysql.slave_master_info
  126.                     SQL_Delay: 0
  127.           SQL_Remaining_Delay: NULL
  128.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
  129.            Master_Retry_Count: 86400
  130.                   Master_Bind: 
  131.       Last_IO_Error_Timestamp: 
  132.      Last_SQL_Error_Timestamp: 
  133.                Master_SSL_Crl: 
  134.            Master_SSL_Crlpath: 
  135.            Retrieved_Gtid_Set: 
  136.             Executed_Gtid_Set: 
  137.                 Auto_Position: 1
  138.          Replicate_Rewrite_DB: 
  139.                  Channel_Name: 
  140.            Master_TLS_Version: 
  141.        Master_public_key_path: 
  142.         Get_master_public_key: 1
  143.             Network_Namespace: 
  144. 1 row in set, 1 warning (0.00 sec)

GTID复制切换成异步复制

  1. 1、关闭从库
  2.  
  3. root@node01 [(none)]> show master status;
  4. +---------------+----------+--------------+------------------+------------------------------------------+
  5. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
  6. +---------------+----------+--------------+------------------+------------------------------------------+
  7. | binlog.000010 |     2105 |              |                  | 8ba057c3-2d10-11ee-a0fa-000c29004cdf:1-8 |
  8. +---------------+----------+--------------+------------------+------------------------------------------+
  9. 1 row in set (0.00 sec)
  10.  
  11.  
  12. root@node02 [(none)]> stop slave;
  13. Query OK, 0 rows affected, 1 warning (0.60 sec)
  14.  
  15. 查看Relay_Master_Log_File和Relay_Master_Log_File的值
  16. root@node02 [(none)]> show slave status\G
  17. Relay_Master_Log_File: binlog.000010
  18. Relay_Master_Log_File: 2105
  19.  
  20. root@node03 [(none)]> stop slave;
  21. Query OK, 0 rows affected, 1 warning (0.52 sec)
  22.  
  23. root@node03 [(none)]> show slave status\G
  24. Relay_Master_Log_File: binlog.000010
  25. Exec_Master_Log_Pos: 2105
  26.  
  27.  
  28. 2、从库执行change master to命令
  29. change master to 
  30.     master_auto_position=0,
  31.     master_log_file='binlog.000010',
  32.     master_log_pos=2105,
  33.     get_master_public_key=1;
  34.  
  35.  
  36. root@node02 [(none)]> change master to 
  37.     ->     master_auto_position=0,
  38.     ->     master_log_file='binlog.000010',
  39.     ->     master_log_pos=2105,
  40.     ->     get_master_public_key=1;
  41. Query OK, 0 rows affected, 5 warnings (0.30 sec)
  42.  
  43.  
  44. root@node03 [(none)]> change master to 
  45.     ->     master_auto_position=0,
  46.     ->     master_log_file='binlog.000010',
  47.     ->     master_log_pos=2105,
  48.     ->     get_master_public_key=1;
  49. Query OK, 0 rows affected, 5 warnings (0.24 sec)
  50.  
  51. 3、开启复制
  52. root@node02 [(none)]> start slave;
  53. Query OK, 0 rows affected, 1 warning (1.49 sec)
  54.  
  55. root@node03 [(none)]> start slave;
  56. Query OK, 0 rows affected, 1 warning (1.66 sec)
  57.  
  58.  
  59. 从库状态正常
  60. root@node02 [(none)]> show slave status\G
  61. *************************** 1. row ***************************
  62.                Slave_IO_State: Waiting for source to send event
  63.                   Master_Host: 192.168.6.101
  64.                   Master_User: repl
  65.                   Master_Port: 3306
  66.                 Connect_Retry: 60
  67.               Master_Log_File: binlog.000010
  68.           Read_Master_Log_Pos: 2105
  69.                Relay_Log_File: node02-relay-bin.000002
  70.                 Relay_Log_Pos: 323
  71.         Relay_Master_Log_File: binlog.000010
  72.              Slave_IO_Running: Yes
  73.             Slave_SQL_Running: Yes
  74.               Replicate_Do_DB: 
  75.           Replicate_Ignore_DB: 
  76.            Replicate_Do_Table: 
  77.        Replicate_Ignore_Table: 
  78.       Replicate_Wild_Do_Table: 
  79.   Replicate_Wild_Ignore_Table: 
  80.                    Last_Errno: 0
  81.                    Last_Error: 
  82.                  Skip_Counter: 0
  83.           Exec_Master_Log_Pos: 2105
  84.               Relay_Log_Space: 534
  85.               Until_Condition: None
  86.                Until_Log_File: 
  87.                 Until_Log_Pos: 0
  88.            Master_SSL_Allowed: No
  89.            Master_SSL_CA_File: 
  90.            Master_SSL_CA_Path: 
  91.               Master_SSL_Cert: 
  92.             Master_SSL_Cipher: 
  93.                Master_SSL_Key: 
  94.         Seconds_Behind_Master: 0
  95. Master_SSL_Verify_Server_Cert: No
  96.                 Last_IO_Errno: 0
  97.                 Last_IO_Error: 
  98.                Last_SQL_Errno: 0
  99.                Last_SQL_Error: 
  100.   Replicate_Ignore_Server_Ids: 
  101.              Master_Server_Id: 1001
  102.                   Master_UUID: 8ba057c3-2d10-11ee-a0fa-000c29004cdf
  103.              Master_Info_File: mysql.slave_master_info
  104.                     SQL_Delay: 0
  105.           SQL_Remaining_Delay: NULL
  106.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
  107.            Master_Retry_Count: 86400
  108.                   Master_Bind: 
  109.       Last_IO_Error_Timestamp: 
  110.      Last_SQL_Error_Timestamp: 
  111.                Master_SSL_Crl: 
  112.            Master_SSL_Crlpath: 
  113.            Retrieved_Gtid_Set: 
  114.             Executed_Gtid_Set: 3ff16ad3-2d17-11ee-aab1-000c297a8cee:1-2,
  115. 8ba057c3-2d10-11ee-a0fa-000c29004cdf:1-8
  116.                 Auto_Position: 0
  117.          Replicate_Rewrite_DB: 
  118.                  Channel_Name: 
  119.            Master_TLS_Version: 
  120.        Master_public_key_path: 
  121.         Get_master_public_key: 1
  122.             Network_Namespace: 
  123. 1 row in set, 1 warning (0.00 sec)
  124.  
  125. root@node03 [(none)]> show slave status\G
  126. *************************** 1. row ***************************
  127.                Slave_IO_State: Waiting for source to send event
  128.                   Master_Host: 192.168.6.101
  129.                   Master_User: repl
  130.                   Master_Port: 3306
  131.                 Connect_Retry: 60
  132.               Master_Log_File: binlog.000010
  133.           Read_Master_Log_Pos: 2105
  134.                Relay_Log_File: node03-relay-bin.000002
  135.                 Relay_Log_Pos: 323
  136.         Relay_Master_Log_File: binlog.000010
  137.              Slave_IO_Running: Yes
  138.             Slave_SQL_Running: Yes
  139.               Replicate_Do_DB: 
  140.           Replicate_Ignore_DB: 
  141.            Replicate_Do_Table: 
  142.        Replicate_Ignore_Table: 
  143.       Replicate_Wild_Do_Table: 
  144.   Replicate_Wild_Ignore_Table: 
  145.                    Last_Errno: 0
  146.                    Last_Error: 
  147.                  Skip_Counter: 0
  148.           Exec_Master_Log_Pos: 2105
  149.               Relay_Log_Space: 534
  150.               Until_Condition: None
  151.                Until_Log_File: 
  152.                 Until_Log_Pos: 0
  153.            Master_SSL_Allowed: No
  154.            Master_SSL_CA_File: 
  155.            Master_SSL_CA_Path: 
  156.               Master_SSL_Cert: 
  157.             Master_SSL_Cipher: 
  158.                Master_SSL_Key: 
  159.         Seconds_Behind_Master: 0
  160. Master_SSL_Verify_Server_Cert: No
  161.                 Last_IO_Errno: 0
  162.                 Last_IO_Error: 
  163.                Last_SQL_Errno: 0
  164.                Last_SQL_Error: 
  165.   Replicate_Ignore_Server_Ids: 
  166.              Master_Server_Id: 1001
  167.                   Master_UUID: 8ba057c3-2d10-11ee-a0fa-000c29004cdf
  168.              Master_Info_File: mysql.slave_master_info
  169.                     SQL_Delay: 0
  170.           SQL_Remaining_Delay: NULL
  171.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
  172.            Master_Retry_Count: 86400
  173.                   Master_Bind: 
  174.       Last_IO_Error_Timestamp: 
  175.      Last_SQL_Error_Timestamp: 
  176.                Master_SSL_Crl: 
  177.            Master_SSL_Crlpath: 
  178.            Retrieved_Gtid_Set: 
  179.             Executed_Gtid_Set: 42cab633-2d17-11ee-a9e1-000c296e813f:1-2,
  180. 8ba057c3-2d10-11ee-a0fa-000c29004cdf:1-8
  181.                 Auto_Position: 0
  182.          Replicate_Rewrite_DB: 
  183.                  Channel_Name: 
  184.            Master_TLS_Version: 
  185.        Master_public_key_path: 
  186.         Get_master_public_key: 1
  187.             Network_Namespace: 
  188. 1 row in set, 1 warning (0.02 sec)
  189.  
  190.  
  191. 4、设置主从参数gtid_mode为ON_PERMISSIVE
  192.  
  193. set global gtid_mode=ON_PERMISSIVE;
  194. root@node01 [(none)]> set global gtid_mode=ON_PERMISSIVE;
  195. Query OK, 0 rows affected (0.30 sec)
  196.  
  197.  
  198. root@node02 [(none)]> set global gtid_mode=ON_PERMISSIVE;
  199. Query OK, 0 rows affected (0.15 sec)
  200.  
  201. root@node03 [(none)]> set global gtid_mode=ON_PERMISSIVE;
  202. Query OK, 0 rows affected (0.07 sec)
  203.  
  204. 5、设置主从参数gtid_mode为OFF_PERMISSIVE
  205. set global gtid_mode=OFF_PERMISSIVE;
  206.  
  207. root@node01 [(none)]> set global gtid_mode=OFF_PERMISSIVE;
  208. Query OK, 0 rows affected (0.00 sec)
  209.  
  210. root@node02 [(none)]> set global gtid_mode=OFF_PERMISSIVE;
  211. Query OK, 0 rows affected (0.00 sec)
  212.  
  213. root@node03 [(none)]> set global gtid_mode=OFF_PERMISSIVE;
  214. Query OK, 0 rows affected (0.00 sec)
  215.  
  216. 6、检查主从gtid_owned
  217. select @@global.gtid_owned;
  218.  
  219.  
  220. root@node01 [(none)]> select @@global.gtid_owned;
  221. +---------------------+
  222. | @@global.gtid_owned |
  223. +---------------------+
  224. |                     |
  225. +---------------------+
  226. 1 row in set (0.00 sec)
  227.  
  228.  
  229.  
  230. root@node02 [(none)]> select @@global.gtid_owned;
  231. +---------------------+
  232. | @@global.gtid_owned |
  233. +---------------------+
  234. |                     |
  235. +---------------------+
  236. 1 row in set (0.00 sec)
  237.  
  238.  
  239.  
  240. root@node03 [(none)]> select @@global.gtid_owned;
  241. +---------------------+
  242. | @@global.gtid_owned |
  243. +---------------------+
  244. |                     |
  245. +---------------------+
  246. 1 row in set (0.00 sec)
  247.  
  248.  
  249. 如果值为空，再继续下一步
  250.  
  251. 7、等待所有的GTID事务应用完毕
  252. 通过MASTER_POS_WAIT函数判断
  253.  
  254. root@node01 [(none)]> show master status;
  255. +---------------+----------+--------------+------------------+------------------------------------------+
  256. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
  257. +---------------+----------+--------------+------------------+------------------------------------------+
  258. | binlog.000012 |      197 |              |                  | 8ba057c3-2d10-11ee-a0fa-000c29004cdf:1-8 |
  259. +---------------+----------+--------------+------------------+------------------------------------------+
  260. 1 row in set (0.00 sec)
  261.  
  262. 从库执行
  263. select MASTER_POS_WAIT('binlog.000012',197);
  264.  
  265. root@node02 [(none)]> select MASTER_POS_WAIT('binlog.000012',197);
  266. +--------------------------------------+
  267. | MASTER_POS_WAIT('binlog.000012',197) |
  268. +--------------------------------------+
  269. |                                    0 |
  270. +--------------------------------------+
  271. 1 row in set, 1 warning (0.06 sec)
  272.  
  273. root@node03 [(none)]> select MASTER_POS_WAIT('binlog.000012',197);
  274. +--------------------------------------+
  275. | MASTER_POS_WAIT('binlog.000012',197) |
  276. +--------------------------------------+
  277. |                                    0 |
  278. +--------------------------------------+
  279. 1 row in set, 1 warning (0.04 sec)
  280.  
  281. 8、主从设置gtid_mode OFF
  282. set global gtid_mode=OFF;
  283.  
  284. root@node01 [(none)]> set global gtid_mode=OFF;
  285. Query OK, 0 rows affected (0.04 sec)
  286.  
  287. root@node02 [(none)]> set global gtid_mode=OFF;
  288. Query OK, 0 rows affected (0.04 sec)
  289.  
  290. root@node03 [(none)]> set global gtid_mode=OFF;
  291. Query OK, 0 rows affected (0.04 sec)
  292.  
  293. 9、修改主从参数，将GTID相关参数值设为OFF
  294.  
  295. [root@node01 ~]# vim /etc/my.cnf
  296. #gtid
  297. gtid_mode=off
  298. enforce_gtid_consistency=off
  299.  
  300.  
  301. [root@node02 ~]# vim /etc/my.cnf
  302. #gtid
  303. gtid_mode=off
  304. enforce_gtid_consistency=off
  305.  
  306. [root@node03 ~]# vim /etc/my.cnf
  307. #gtid
  308. gtid_mode=off
  309. enforce_gtid_consistency=off
  310.  
  311.  
  312. root@node01 [(none)]> show master status;
  313. +---------------+----------+--------------+------------------+------------------------------------------+
  314. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
  315. +---------------+----------+--------------+------------------+------------------------------------------+
  316. | binlog.000013 |      197 |              |                  | 8ba057c3-2d10-11ee-a0fa-000c29004cdf:1-8 |
  317. +---------------+----------+--------------+------------------+------------------------------------------+
  318. 1 row in set (0.00 sec)

异步复制切换成GTID复制

  1. root@node01 [(none)]> select @@gtid_mode;
  2. +-------------+
  3. | @@gtid_mode |
  4. +-------------+
  5. | OFF         |
  6. +-------------+
  7. 1 row in set (0.00 sec)
  8.  
  9.  
  10. root@node02 [(none)]> select @@gtid_mode;
  11. +-------------+
  12. | @@gtid_mode |
  13. +-------------+
  14. | OFF         |
  15. +-------------+
  16. 1 row in set (0.00 sec)
  17.  
  18.  
  19. root@node03 [(none)]> select @@gtid_mode;
  20. +-------------+
  21. | @@gtid_mode |
  22. +-------------+
  23. | OFF         |
  24. +-------------+
  25. 1 row in set (0.00 sec)
  26.  
  27.  
  28. 1、分别在主从将enforce_gtid_consistency设置为warn
  29.  
  30. root@node01 [(none)]> select @@enforce_gtid_consistency;
  31. +----------------------------+
  32. | @@enforce_gtid_consistency |
  33. +----------------------------+
  34. | OFF                        |
  35. +----------------------------+
  36. 1 row in set (0.01 sec)
  37.  
  38. root@node01 [(none)]> set @@global.enforce_gtid_consistency=warn;
  39. Query OK, 0 rows affected (0.13 sec)
  40.  
  41.  
  42. root@node02 [(none)]> set @@global.enforce_gtid_consistency=warn;
  43. Query OK, 0 rows affected (0.03 sec)
  44.  
  45. root@node03 [(none)]> set @@global.enforce_gtid_consistency=warn;
  46. Query OK, 0 rows affected (0.03 sec)
  47.  
  48.  
  49. 2、分别在主从上将enforce_gtid_consistency设为on
  50.  
  51. root@node01 [(none)]> set @@global.enforce_gtid_consistency=on;
  52. Query OK, 0 rows affected (0.00 sec)
  53.  
  54. root@node02 [(none)]> set @@global.enforce_gtid_consistency=on;
  55. Query OK, 0 rows affected (0.00 sec)
  56.  
  57. root@node03 [(none)]> set @@global.enforce_gtid_consistency=on;
  58. Query OK, 0 rows affected (0.00 sec)
  59.  
  60. 3、分别在主从将gtid_mode设置off_permissive
  61. root@node01 [(none)]> set @@global.gtid_mode=off_permissive;
  62. Query OK, 0 rows affected (0.22 sec)
  63.  
  64. root@node02 [(none)]> set @@global.gtid_mode=off_permissive;
  65. Query OK, 0 rows affected (0.00 sec)
  66.  
  67. root@node03 [(none)]> set @@global.gtid_mode=off_permissive;
  68. Query OK, 0 rows affected (0.03 sec)
  69.  
  70. 4、分别在主从将gtid_mode设置on_permissive
  71. root@node01 [(none)]> set @@global.gtid_mode=on_permissive;
  72. Query OK, 0 rows affected (0.02 sec)
  73.  
  74. root@node02 [(none)]> set @@global.gtid_mode=on_permissive;
  75. Query OK, 0 rows affected (0.02 sec)
  76.  
  77. root@node03 [(none)]> set @@global.gtid_mode=on_permissive;
  78. Query OK, 0 rows affected (0.01 sec)
  79.  
  80. 5、分别在主从上检查状态变量Ongoing_anonymous_transaction_count
  81.  
  82. root@node01 [(none)]> show status like '%ongoing%';
  83. +-------------------------------------+-------+
  84. | Variable_name                       | Value |
  85. +-------------------------------------+-------+
  86. | Ongoing_anonymous_transaction_count | 0     |
  87. +-------------------------------------+-------+
  88. 1 row in set (0.76 sec)
  89.  
  90. root@node02 [(none)]> show status like '%ongoing%';
  91. +-------------------------------------+-------+
  92. | Variable_name                       | Value |
  93. +-------------------------------------+-------+
  94. | Ongoing_anonymous_transaction_count | 0     |
  95. +-------------------------------------+-------+
  96. 1 row in set (0.63 sec)
  97.  
  98.  
  99. root@node03 [(none)]> show status like '%ongoing%';
  100. +-------------------------------------+-------+
  101. | Variable_name                       | Value |
  102. +-------------------------------------+-------+
  103. | Ongoing_anonymous_transaction_count | 0     |
  104. +-------------------------------------+-------+
  105. 1 row in set (0.00 sec)
  106.  
  107. 6、等待从库上匿名事务应用完毕
  108.  
  109. root@node01 [(none)]> show master status;
  110. +---------------+----------+--------------+------------------+-------------------+
  111. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  112. +---------------+----------+--------------+------------------+-------------------+
  113. | binlog.000016 |      157 |              |                  |                   |
  114. +---------------+----------+--------------+------------------+-------------------+
  115. 1 row in set (0.00 sec)
  116.  
  117. 在从上查看，返回0说明事务应用完毕
  118. root@node02 [(none)]> select master_pos_wait('binlog.000016',157);
  119. +--------------------------------------+
  120. | master_pos_wait('binlog.000016',157) |
  121. +--------------------------------------+
  122. |                                    0 |
  123. +--------------------------------------+
  124. 1 row in set, 1 warning (0.05 sec)
  125.  
  126. root@node03 [(none)]> select master_pos_wait('binlog.000016',157);
  127. +--------------------------------------+
  128. | master_pos_wait('binlog.000016',157) |
  129. +--------------------------------------+
  130. |                                    0 |
  131. +--------------------------------------+
  132. 1 row in set, 1 warning (0.05 sec)
  133.  
  134. 7、分别在从库上将gtid_mode设为on
  135. root@node01 [(none)]> set @@global.gtid_mode=on;
  136. Query OK, 0 rows affected (0.02 sec)
  137.  
  138. root@node02 [(none)]> set @@global.gtid_mode=on;
  139. Query OK, 0 rows affected (0.02 sec)
  140. root@node03 [(none)]> set @@global.gtid_mode=on;
  141. Query OK, 0 rows affected (0.02 sec)
  142.  
  143. 8、修改主从参数文件
  144. [root@node01 ~]# vim /etc/my.cnf
  145. #gtid
  146. gtid_mode=on
  147. enforce_gtid_consistency=on
  148.  
  149. [root@node02 ~]# vim /etc/my.cnf
  150. #gtid
  151. gtid_mode=on
  152. enforce_gtid_consistency=on
  153.  
  154. [root@node03 ~]# vim /etc/my.cnf
  155. #gtid
  156. gtid_mode=on
  157. enforce_gtid_consistency=on
  158.  
  159. 9、将基于binlog位置点的复制改为gtid复制
  160.  
  161. root@node02 [(none)]> stop slave;
  162. Query OK, 0 rows affected, 1 warning (0.12 sec)
  163.  
  164. root@node02 [(none)]> stop slave;
  165. Query OK, 0 rows affected, 1 warning (0.12 sec)
  166.  
  167. root@node02 [(none)]> change master to
  168.     ->     master_host='192.168.6.101',
  169.     ->     master_port=3306,
  170.     ->     master_user='repl',
  171.     ->     master_password='oracle',
  172.     ->     master_auto_position=1,
  173.     ->     get_master_public_key=1;
  174. Query OK, 0 rows affected, 8 warnings (0.02 sec)
  175.  
  176. root@node02 [(none)]> start slave;
  177. Query OK, 0 rows affected, 1 warning (0.06 sec)
  178.  
  179. root@node02 [(none)]> show slave status\G
  180. *************************** 1. row ***************************
  181.                Slave_IO_State: Waiting for source to send event
  182.                   Master_Host: 192.168.6.101
  183.                   Master_User: repl
  184.                   Master_Port: 3306
  185.                 Connect_Retry: 60
  186.               Master_Log_File: binlog.000017
  187.           Read_Master_Log_Pos: 157
  188.                Relay_Log_File: node02-relay-bin.000002
  189.                 Relay_Log_Pos: 367
  190.         Relay_Master_Log_File: binlog.000017
  191.              Slave_IO_Running: Yes
  192.             Slave_SQL_Running: Yes
  193.               Replicate_Do_DB: 
  194.           Replicate_Ignore_DB: 
  195.            Replicate_Do_Table: 
  196.        Replicate_Ignore_Table: 
  197.       Replicate_Wild_Do_Table: 
  198.   Replicate_Wild_Ignore_Table: 
  199.                    Last_Errno: 0
  200.                    Last_Error: 
  201.                  Skip_Counter: 0
  202.           Exec_Master_Log_Pos: 157
  203.               Relay_Log_Space: 578
  204.               Until_Condition: None
  205.                Until_Log_File: 
  206.                 Until_Log_Pos: 0
  207.            Master_SSL_Allowed: No
  208.            Master_SSL_CA_File: 
  209.            Master_SSL_CA_Path: 
  210.               Master_SSL_Cert: 
  211.             Master_SSL_Cipher: 
  212.                Master_SSL_Key: 
  213.         Seconds_Behind_Master: 0
  214. Master_SSL_Verify_Server_Cert: No
  215.                 Last_IO_Errno: 0
  216.                 Last_IO_Error: 
  217.                Last_SQL_Errno: 0
  218.                Last_SQL_Error: 
  219.   Replicate_Ignore_Server_Ids: 
  220.              Master_Server_Id: 1001
  221.                   Master_UUID: b0ae4cca-32af-11ee-8ff1-000c29004cdf
  222.              Master_Info_File: mysql.slave_master_info
  223.                     SQL_Delay: 0
  224.           SQL_Remaining_Delay: NULL
  225.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
  226.            Master_Retry_Count: 86400
  227.                   Master_Bind: 
  228.       Last_IO_Error_Timestamp: 
  229.      Last_SQL_Error_Timestamp: 
  230.                Master_SSL_Crl: 
  231.            Master_SSL_Crlpath: 
  232.            Retrieved_Gtid_Set: 
  233.             Executed_Gtid_Set: 
  234.                 Auto_Position: 1
  235.          Replicate_Rewrite_DB: 
  236.                  Channel_Name: 
  237.            Master_TLS_Version: 
  238.        Master_public_key_path: 
  239.         Get_master_public_key: 1
  240.             Network_Namespace: 
  241. 1 row in set, 1 warning (0.00 sec)
  242.  
  243.  
  244.  
  245. root@node03 [(none)]> stop slave;
  246. Query OK, 0 rows affected, 1 warning (0.00 sec)
  247.  
  248. root@node03 [(none)]> change master to
  249.     ->     master_host='192.168.6.101',
  250.     ->     master_port=3306,
  251.     ->     master_user='repl',
  252.     ->     master_password='oracle',
  253.     ->     master_auto_position=1,
  254.     ->     get_master_public_key=1;
  255. Query OK, 0 rows affected, 8 warnings (0.01 sec)
  256.  
  257. root@node03 [(none)]> start slave;
  258. Query OK, 0 rows affected, 1 warning (0.00 sec)
  259.  
  260. root@node03 [(none)]> show slave status\G
  261. *************************** 1. row ***************************
  262.                Slave_IO_State: Waiting for source to send event
  263.                   Master_Host: 192.168.6.101
  264.                   Master_User: repl
  265.                   Master_Port: 3306
  266.                 Connect_Retry: 60
  267.               Master_Log_File: binlog.000017
  268.           Read_Master_Log_Pos: 157
  269.                Relay_Log_File: node03-relay-bin.000002
  270.                 Relay_Log_Pos: 367
  271.         Relay_Master_Log_File: binlog.000017
  272.              Slave_IO_Running: Yes
  273.             Slave_SQL_Running: Yes
  274.               Replicate_Do_DB: 
  275.           Replicate_Ignore_DB: 
  276.            Replicate_Do_Table: 
  277.        Replicate_Ignore_Table: 
  278.       Replicate_Wild_Do_Table: 
  279.   Replicate_Wild_Ignore_Table: 
  280.                    Last_Errno: 0
  281.                    Last_Error: 
  282.                  Skip_Counter: 0
  283.           Exec_Master_Log_Pos: 157
  284.               Relay_Log_Space: 578
  285.               Until_Condition: None
  286.                Until_Log_File: 
  287.                 Until_Log_Pos: 0
  288.            Master_SSL_Allowed: No
  289.            Master_SSL_CA_File: 
  290.            Master_SSL_CA_Path: 
  291.               Master_SSL_Cert: 
  292.             Master_SSL_Cipher: 
  293.                Master_SSL_Key: 
  294.         Seconds_Behind_Master: 0
  295. Master_SSL_Verify_Server_Cert: No
  296.                 Last_IO_Errno: 0
  297.                 Last_IO_Error: 
  298.                Last_SQL_Errno: 0
  299.                Last_SQL_Error: 
  300.   Replicate_Ignore_Server_Ids: 
  301.              Master_Server_Id: 1001
  302.                   Master_UUID: b0ae4cca-32af-11ee-8ff1-000c29004cdf
  303.              Master_Info_File: mysql.slave_master_info
  304.                     SQL_Delay: 0
  305.           SQL_Remaining_Delay: NULL
  306.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
  307.            Master_Retry_Count: 86400
  308.                   Master_Bind: 
  309.       Last_IO_Error_Timestamp: 
  310.      Last_SQL_Error_Timestamp: 
  311.                Master_SSL_Crl: 
  312.            Master_SSL_Crlpath: 
  313.            Retrieved_Gtid_Set: 
  314.             Executed_Gtid_Set: 
  315.                 Auto_Position: 1
  316.          Replicate_Rewrite_DB: 
  317.                  Channel_Name: 
  318.            Master_TLS_Version: 
  319.        Master_public_key_path: 
  320.         Get_master_public_key: 1
  321.             Network_Namespace: 
  322. 1 row in set, 1 warning (0.00 sec)
  323.  
  324. root@node01 [(none)]> show master status;
  325. +---------------+----------+--------------+------------------+-------------------+
  326. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  327. +---------------+----------+--------------+------------------+-------------------+
  328. | binlog.000017 |      157 |              |                  |                   |
  329. +---------------+----------+--------------+------------------+-------------------+
  330. 1 row in set (0.00 sec)
  331.  
  332. root@node01 [(none)]> select * from sh.customer;
  333. +------+
  334. | id   |
  335. +------+
  336. | 1001 |
  337. | 1002 |
  338. | 1003 |
  339. | 1004 |
  340. | 1005 |
  341. +------+
  342. 5 rows in set (0.34 sec)
  343.  
  344. root@node01 [(none)]> insert into sh.customer values(1006);
  345. Query OK, 1 row affected (0.28 sec)
  346.  
  347. root@node01 [(none)]> show master status;
  348. +---------------+----------+--------------+------------------+----------------------------------------+
  349. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
  350. +---------------+----------+--------------+------------------+----------------------------------------+
  351. | binlog.000017 |      430 |              |                  | b0ae4cca-32af-11ee-8ff1-000c29004cdf:1 |
  352. +---------------+----------+--------------+------------------+----------------------------------------+
  353. 1 row in set (0.00 sec)
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/959298
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号