MySQL示例DTID主从原理解析

时间:2022-08-28 09:01:29

1.GTID基本概念

MySQL 5.6.5开始支持的,全局事务标识符(GTID(Global Transaction ID))是创建的唯一标识符,并与在源(主)服务器上提交的每个事务相关联。
此标识符不但是唯一的,而且在给定复制设置中的所有服务器上都是唯一的。
所有交易和所有GTID之间都有一对一的映射关系 。
它由服务器ID以及事务ID组合而成。
这个全局事务ID不仅仅在原始服务器上唯一,在所有存在主从关系 的mysql服务器上也是唯一的。
正是因为这样一个特性使得mysql的主从复制变得更加简单,以及数据库一致性更可靠。
一个GTID在一个服务器上只执行一次,避免重复执行导致数据混乱或者主从不一致。

2.GTID优点

保证同一个事务在某slave上绝对只执行一次,没有执行过的gtid事务总是会被执行。
不用像传统复制那样保证binlog的坐标准确,因为根本不需要binlog以及坐标。
故障转移到新的master的时候很方便,简化了很多任务。
很容易判断master和slave的数据是否一致。只要master上提交的事务在slave上也提交了,那么一定是一致的。

3.GTID的工作原理

MySQL示例DTID主从原理解析

1.当一个事务在主库端执行并提交时,产生GTID,一同记录到binlog日志中。
2.binlog传输到slave,并存储到slave的relaylog后,读取这个GTID的这个值设置gtid_next变量,即告诉Slave,下一个要执行的GTID值。
3、sql线程从relay log中获取GTID,然后对比slave端的binlog是否有该GTID。
4、如果有记录,说明该GTID的事务已经执行,slave会忽略。
5、如果没有记录,slave就会执行该GTID事务,并记录该GTID到自身的binlog,在读取执行事务前会先检查其他session持有该GTID,确保不被重复执行。
6、在解析过程中会判断是否有主键,如果有就用二级索引,如果没有就用全部扫描。

4.GTID比传统复制的优势

1.更简单的实现故障转移(failover),不需要找log_file,log_pos

2.更简单的搭建主从复制

3.更加安全

4.GTID是连续没有空洞的,因此主数据库发生冲突时,可以添加空事件的方式进行跳过

5.启动的方法

  • 方法一:如果是新搭建的服务器,直接启动即可
  • 方法二:如果是以及跑的服务器,需要重启一下mysql server

启动前,先关闭master的写入,保证master端和slave端数据保持同步,所有slave需要加上skip_slave_start=1的配置参数,避免启动后还是使用之前的复制协议

6.GTID(一主一从)配置

 

6.1环境:

centos8.0 ip:192.168.136.239 有数据 hostname:mysql01

centos8.0 ip:192.168.136.219 无数据 hostname:mysql02

#二进制安装以及mysql自启动服务略

6.2在主库上给从库授权:

  1. mysql> grant replication slave on *.* to 'slave'@'192.168.136.219' identified by 'slave';
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3. mysql> flush privileges;
  4. Query OK, 0 rows affected (0.00 sec)
  5. #俩服务器均关闭防火墙
  6. [root@mysql01 ~]# systemctl stop firewalld
  7. [root@mysql01 ~]# setenforce 0
  8. [root@mysql02 ~]# systemctl stop firewalld
  9. [root@mysql02 ~]# setenforce 0
  10. 从库测试连接:
  11. [root@mysql02 ~]# mysql -u slave -p'slave' -h192.168.136.239
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  13. mysql>

6.3确保数据一致操作

  1. 1.对主库进行锁表
  2. mysql> flush tables with read lock;
  3. 2.对主库进行全备
  4. [root@mysql01 ~]# mysqldump -uroot -A > /clq/all-databases-20210519.sql
  5. 3.拷贝到从库主机上去
  6. [root@mysql01 ~]# scp /clq/all-databases-20210519.sql root@192.168.136.219:/backup/
  7. [root@mysql02 backup]# ll
  8. -rw-r--r--. 1 root root 873527 5 19 16:40 all-databases-20210519.sql
  9. 4.从库上进行主库的恢复
  10. [root@mysql02 backup]# mysql -uroot -pHuawei0917@ < all-databases-20210519.sql

6.4配置主库

  1. [mysqld]
  2. basedir = /usr/local/mysql
  3. datadir = /opt/data
  4. socket = /tmp/mysql.sock
  5. port = 3306
  6. user = mysql
  7. pid-file = /opt/data/mysql.pid
  8. skip-name-resolve
  9. #skip-grant-tables
  10. log-bin = master_bin #开启主库日志
  11. server-id = 10 #服务唯一标识id
  12. gtid-mode = on #GTID模式开启
  13. enforce_gtid_consistency = on #强制gtid模式一致性
  14. log-slave-updates = 1 #从库允许更新日志,同步操作日志
  15. binlog_format = row #binlog日志格式为行格式, 默认是mixed混合模式
  16. skip_slave_start = 1 #跳过从库开启,以主库开始开启
  17. #重启
  18. systemctl restart mysqld

6.5配置从库

  1. [root@mysql02 data]# cat /etc/my.cnf
  2. [mysqld]
  3. basedir = /usr/local/mysql
  4. datadir = /opt/data
  5. socket = /tmp/mysql.sock
  6. port = 3306
  7. user = mysql
  8. pid-file = /opt/data/mysql.pid
  9. skip-name-resolve
  10. #skip-grant-tables
  11. gtid_mode=on
  12. enforce_gtid_consistency=on
  13. server-id=20
  14. log-bin=slave_binlog #开启从库日志
  15. log_slave-updates=1 #从库允许更新
  16. binlog_format=row #格式为行
  17. skip-slave_start=1
  18. #重启
  19. systemctl restart mysqld

查看gtid状态情况

  1. mysql> show variables like '%gtid%';
  2. +----------------------------------+-----------+
  3. | Variable_name | Value |
  4. +----------------------------------+-----------+
  5. | binlog_gtid_simple_recovery | ON |
  6. | enforce_gtid_consistency | ON |
  7. | gtid_executed_compression_period | 1000 |
  8. | gtid_mode | ON |
  9. | gtid_next | AUTOMATIC |
  10. | gtid_owned | |
  11. | gtid_purged | |
  12. | session_track_gtids | OFF |
  13. +----------------------------------+-----------+
  14. 8 rows in set (0.00 sec)

6.6配置主从复制

  1. #从库上root登录配置 #help change master to 可以查看帮助文档实例
  2. mysql> change master to
  3. -> master_host='192.168.136.239',
  4. -> master_user='slave',
  5. -> master_password='slave',
  6. -> master_port=3306, #主库端口
  7. -> master_auto_position=1; #位置
  8. #master_use_gtid = current_pos
  9. Query OK, 0 rows affected, 2 warnings (0.01 sec)
  10. mysql> start slave;
  11. Query OK, 0 rows affected (0.00 sec)
  12. mysql> show slave status\G;
  13. Slave_IO_Running: Connecting
  14. Slave_IO_Running: Yes
  15. Slave_SQL_Running: Yes
  16. 保证系统一致性
  17. 授权一致性

(一主一从GTID)测试

主库创建一个数据库test,进行测试查看

从库创建一个数据库test02,进行测试查看

  1. #主库创建一个test数据库
  2. mysql> create database test;
  3. mysql> show databases;
  4. +--------------------+
  5. | Database |
  6. +--------------------+
  7. | information_schema |
  8. | mysql |
  9. | performance_schema |
  10. | sys |
  11. | test |
  12. +--------------------+
  13. #从库上查看同步情况
  14. mysql> show databases;
  15. +--------------------+
  16. | Database |
  17. +--------------------+
  18. | information_schema |
  19. | mysql |
  20. | performance_schema |
  21. | sys |
  22. | test |
  23. +--------------------+
  24. 6 rows in set (0.00 sec)
  25.  
  26. #从库创建test02库
  27. mysql> create database test02;
  28. Query OK, 1 row affected (0.00 sec)
  29. #主库上查看
  30. mysql> show databases;
  31. +--------------------+
  32. | Database |
  33. +--------------------+
  34. | information_schema |
  35. | mysql |
  36. | performance_schema |
  37. | sys | #是没有test02库的
  38. | test |
  39. +--------------------+
  40. 5 rows in set (0.00 sec)

小结:主库上的数据操作会同步到从库上面去,而从库上的数据操作与主库没联系

7.GTID(一主俩从)

第三台mysql连接的话,相应配置

第3台mysql ,版本:centos8 ip:192.168.136.230 主机名:mysql03

  1. [root@mysql03 ~]# cat /etc/my.cnf
  2. [mysqld]
  3. basedir = /usr/local/mysql
  4. datadir = /opt/data
  5. socket = /tmp/mysql.sock
  6. port = 3306
  7. user = mysql
  8. pid-file = /opt/data/mysql.pid
  9. skip-name-resolve
  10. #skip-grant-tables
  11. # replication config
  12. log-bin = master_bin
  13. server-id = 21 #id必须与之前不同
  14. gtid-mode = on
  15. enforce-gtid-consistency = on
  16. log-slave-updates = 1
  17. binlog-format = row
  18. skip-slave-start = 1
  19. #查看gtid情况
  20. mysql> show variables like '%gtid%';
  21. +----------------------------------+-----------+
  22. | Variable_name | Value |
  23. +----------------------------------+-----------+
  24. | binlog_gtid_simple_recovery | ON |
  25. | enforce_gtid_consistency | ON |
  26. | gtid_executed_compression_period | 1000 |
  27. | gtid_mode | ON |
  28. | gtid_next | AUTOMATIC |
  29. | gtid_owned | |
  30. | gtid_purged | |
  31. | session_track_gtids | OFF |
  32. +----------------------------------+-----------+
  33. #由于之前只权限了一个ip,此刻在mysql01主数据库上再授权一个ip
  34. mysql> grant replication slave on *.* to 'slave'@'192.168.136.230' identified by 'slave';
  35. Query OK, 0 rows affected, 1 warning (0.00 sec)
  36. mysql> flush privileges;
  37. Query OK, 0 rows affected (0.00 sec)
  38. #测试连接
  39. [root@mysql ~]# mysql -uslave -pslave -h192.168.136.239
  40. mysql: [Warning] Using a password on the command line interface can be insecure.
  41. Welcome to the MySQL monitor. Commands end with ; or \g.
  42. Your MySQL connection id is 17
  43. Server version: 5.7.33-log MySQL Community Server (GPL)
  44. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  45. Oracle is a registered trademark of Oracle Corporation and/or its
  46. affiliates. Other names may be trademarks of their respective
  47. owners.
  48. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  49. mysql>
  50. #mysql03从库上root用户连接进行相应配置
  51. [root@mysql03 ~]# mysql -uroot -p1
  52. mysql> change master to
  53. -> master_host='192.168.136.239', #主库ip
  54. -> master_user='slave', #主库授权的普通用户
  55. -> master_password='slave',
  56. -> master_port=3306, #主库端口
  57. -> master_auto_position=1; #位置从1开始同步
  58. #也可以查看帮助进行配置
  59. mysql> help change master to;
  60. CHANGE MASTER TO
  61. MASTER_HOST='source2.example.com',
  62. MASTER_USER='replication',
  63. MASTER_PASSWORD='password',
  64. MASTER_PORT=3306,
  65. MASTER_LOG_FILE='source2-bin.001',
  66. MASTER_LOG_POS=4,
  67. MASTER_CONNECT_RETRY=10;
  68. URL: https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
  69.  
  70. #开启
  71. mysql> start slave;
  72. mysql> show slave status\G;
  73. *************************** 1. row ***************************
  74. Slave_IO_State: Waiting for master to send event
  75. Master_Host: 192.168.136.239
  76. Master_User: slave
  77. Master_Port: 3306
  78. Connect_Retry: 60
  79. Master_Log_File: master_bin.000002
  80. Read_Master_Log_Pos: 2172
  81. Relay_Log_File: mysql-relay-bin.000002
  82. Relay_Log_Pos: 2387
  83. Relay_Master_Log_File: master_bin.000002
  84. Slave_IO_Running: Yes
  85. Slave_SQL_Running: Yes #显示俩个yes则运行成功!
  86. #mysql03查看数据库,数据库内容也同步成功
  87. mysql> show databases;
  88. +--------------------+
  89. | Database |
  90. +--------------------+
  91. | information_schema |
  92. | mysql |
  93. | performance_schema |
  94. | sys |
  95. | test |
  96. +--------------------+
  97. 5 rows in set (0.00 sec)

8.GTID(俩主一从)

 

1.最新环境

版本 ip 主机名 身份
centos8 192.168.136.239 master01 主库
centos8 192.168.136.219 master02 主库
centos8 192.168.136.230 slave 从库

2.所有服务器均关闭防火墙或者放行防火墙

  1. [root@master01 ~]# systemctl stop firewalld
  2. [root@master01 ~]# systemctl disable firewalld
  3. [root@master02 ~]# systemctl stop firewalld
  4. [root@master02 ~]# systemctl disable firewalld
  5. [root@slave ~]# systemctl stop firewalld
  6. [root@slave ~]# systemctl disable firewalld

3.授权连接

 

master01库授权普通用户

  1. mysql> grant replication slave on *.* to 'user'@'192.168.136.%' identified by 'user';

 

slave进行连接

  1. [root@slave ~]# mysql -uuser -p'user' -h192.168.136.239
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor. Commands end with ; or \g.
  4. Your MySQL connection id is 5
  5. Server version: 5.7.33 MySQL Community Server (GPL)
  6. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  7. Oracle is a registered trademark of Oracle Corporation and/or its
  8. affiliates. Other names may be trademarks of their respective
  9. owners.
  10. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master02授权普通用户

  1. mysql> grant replication slave on *.* to 'app'@'192.168.136.%' identified by 'app';
  2. Query OK, 0 rows affected, 1 warning (0.01 sec)
  3. mysql> flush privileges;
  4. Query OK, 0 rows affected (0.00 sec)

slave进行连接

  1. [root@slave ~]# mysql -uapp -papp -h192.168.136.219
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor. Commands end with ; or \g.
  4. Your MySQL connection id is 3
  5. Server version: 5.7.33 MySQL Community Server (GPL)
  6. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  7. Oracle is a registered trademark of Oracle Corporation and/or its
  8. affiliates. Other names may be trademarks of their respective
  9. owners.
  10. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  11. mysql>

4.分别进行配置文件修改

  1. #master01主机:
  2. [root@master01 ~]# cat /etc/my.cnf
  3. [mysqld]
  4. basedir = /usr/local/mysql
  5. datadir = /opt/data
  6. socket = /tmp/mysql.sock
  7. port = 3306
  8. user = mysql
  9. pid-file = /opt/data/mysql.pid
  10. skip-name-resolve
  11. skip-grant-tables
  12. log-bin = master_bin
  13. server-id = 10
  14. gtid-mode = on
  15. enforce-gtid-consistency = on
  16. log-slave-updates = 1
  17. binlog-format = row
  18. skip-slave-start = 1
  19. #master02主机
  20. [mysqld]
  21. basedir = /usr/local/mysql
  22. datadir = /opt/data
  23. socket = /tmp/mysql.sock
  24. port = 3306
  25. user = mysql
  26. pid-file = /opt/data/mysql.pid
  27. skip-name-resolve
  28. #replication config
  29. log-bin = master_bin
  30. server-id = 11
  31. gtid-mode = on
  32. enforce-gtid-consistency = on
  33. log-slave-updates = 1
  34. binlog-format = row
  35. skip-slave-start = 1
  36. #slave主机
  37. [mysqld]
  38. basedir = /usr/local/mysql
  39. datadir = /opt/data
  40. socket = /tmp/mysql.sock
  41. port = 3306
  42. user = mysql
  43. pid-file = /opt/data/mysql.pid
  44. skip-name-resolve
  45. log-bin = slave_bin
  46. server-id = 13
  47. gtid-mode = on
  48. enforce-gtid-consistency = on
  49. log-slave-updates = 1
  50. binlog-format = row
  51. skip-slave-start = 1

5.分别重启

  1. [root@master01 ~]# systemctl restart mysqld
  2. [root@master02 ~]# systemctl restart mysqld
  3. [root@slave ~]# systemctl restart mysqld

6.在进行GTID多主一从配置前,先引入一个概念

channel(频道):每一个channel都是一个独立的slave服务,都有一个IO_THREAD和SQL_THREAD,原理和普通复制一样,只是需要在change master to语句后面使用FOR Channel来进行区分slave

在使用channel时需要将从库的master-info-repositoryrelay-log-info-repository设置为table,否则会报错。

将信息存储库设置为table格式

  1. 方式一(mysql内设置):
  2. set global master_info_repository='table';
  3. set global relay_log_info_repository='table';
  4. 方式二(/etc/my.cnf内设置):
  5. 3.my.cnf中设置
  6. master_info_repository = TABLE
  7. relay_log_info_repository = TABLE
  8. #检查是否更改成功
  9. mysql> show variables where variable_name in ('relay_log_info_repository','master_info_repository');
  10. +---------------------------+-------+
  11. | Variable_name | Value |
  12. +---------------------------+-------+
  13. | master_info_repository | TABLE |
  14. | relay_log_info_repository | TABLE |
  15. +---------------------------+-------+

7.slave从库以root用户登录进行GTID配置

  1. #slave从库上配置俩个主库GTID复制
  2. mysql> change master to
  3. -> master_host='192.168.136.219', #mysql02主库ip
  4. -> master_user='app', #mysql02主库授权的普通用户
  5. -> master_password='app', #mysql02主库授权的普通用户密码
  6. -> master_port=3306, #主库端口
  7. -> master_auto_position=1 for channel 'master01'; #位置从1开始同步,并且第一个slave取名master01
  8. mysql> change master to
  9. -> master_host='192.168.136.239', #mysql01主库ip
  10. -> master_user='user',
  11. -> master_password='user',
  12. -> master_port=3306, #主库端口
  13. -> master_auto_position=1 for channel 'master02'; #位置从1开始同步,并且第一个slave取名master01
  14. #查看俩个slave状态
  15. mysql> show slave status\G;
  16. *************************** 1. row ***************************
  17. Slave_IO_State:
  18. Master_Host: 192.168.136.219
  19. Master_User: app
  20. Master_Port: 3306
  21. Connect_Retry: 60
  22. Master_Log_File:
  23. Read_Master_Log_Pos: 4
  24. Relay_Log_File: slave02-relay-bin-master1.000001
  25. Relay_Log_Pos: 4
  26. Relay_Master_Log_File:
  27. Slave_IO_Running: No
  28. Slave_SQL_Running: No #都是关闭的
  29. Replicate_Do_DB:
  30. Replicate_Ignore_DB:
  31. Replicate_Do_Table:
  32. Replicate_Ignore_Table:
  33. Replicate_Wild_Do_Table:
  34. Replicate_Wild_Ignore_Table:
  35. Last_Errno: 0
  36. Last_Error:
  37. Skip_Counter: 0
  38. Exec_Master_Log_Pos: 0
  39. Relay_Log_Space: 154
  40. Until_Condition: None
  41. Until_Log_File:
  42. Until_Log_Pos: 0
  43. Master_SSL_Allowed: No
  44. Master_SSL_CA_File:
  45. Master_SSL_CA_Path:
  46. Master_SSL_Cert:
  47. Master_SSL_Cipher:
  48. Master_SSL_Key:
  49. Seconds_Behind_Master: NULL
  50. Master_SSL_Verify_Server_Cert: No
  51. Last_IO_Errno: 0
  52. Last_IO_Error:
  53. Last_SQL_Errno: 0
  54. Last_SQL_Error:
  55. Replicate_Ignore_Server_Ids:
  56. Master_Server_Id: 0
  57. Master_UUID:
  58. Master_Info_File: mysql.slave_master_info
  59. SQL_Delay: 0
  60. SQL_Remaining_Delay: NULL
  61. Slave_SQL_Running_State:
  62. Master_Retry_Count: 86400
  63. Master_Bind:
  64. Last_IO_Error_Timestamp:
  65. Last_SQL_Error_Timestamp:
  66. Master_SSL_Crl:
  67. Master_SSL_Crlpath:
  68. Retrieved_Gtid_Set:
  69. Executed_Gtid_Set: b4326a77-0a31-11ec-a991-000c298d3571:1-2,
  70. d68b404d-0a35-11ec-9df1-000c29581959:1
  71. Auto_Position: 1
  72. Replicate_Rewrite_DB:
  73. Channel_Name: master1
  74. Master_TLS_Version:
  75. *************************** 2. row ***************************
  76. Slave_IO_State:
  77. Master_Host: 192.168.136.239
  78. Master_User: user
  79. Master_Port: 3306
  80. Connect_Retry: 60
  81. Master_Log_File:
  82. Read_Master_Log_Pos: 4
  83. Relay_Log_File: slave02-relay-bin-master2.000001
  84. Relay_Log_Pos: 4
  85. Relay_Master_Log_File:
  86. Slave_IO_Running: No
  87. Slave_SQL_Running: No
  88. Replicate_Do_DB:
  89. Replicate_Ignore_DB:
  90. Replicate_Do_Table:
  91. Replicate_Ignore_Table:
  92. Replicate_Wild_Do_Table:
  93. Replicate_Wild_Ignore_Table:
  94. Last_Errno: 0
  95. Last_Error:
  96. Skip_Counter: 0
  97. Exec_Master_Log_Pos: 0
  98. Relay_Log_Space: 154
  99. Until_Condition: None
  100. Until_Log_File:
  101. Until_Log_Pos: 0
  102. Master_SSL_Allowed: No
  103. Master_SSL_CA_File:
  104. Master_SSL_CA_Path:
  105. Master_SSL_Cert:
  106. Master_SSL_Cipher:
  107. Master_SSL_Key:
  108. Seconds_Behind_Master: NULL
  109. Master_SSL_Verify_Server_Cert: No
  110. Last_IO_Errno: 0
  111. Last_IO_Error:
  112. Last_SQL_Errno: 0
  113. Last_SQL_Error:
  114. Replicate_Ignore_Server_Ids:
  115. Master_Server_Id: 0
  116. Master_UUID:
  117. Master_Info_File: mysql.slave_master_info
  118. SQL_Delay: 0
  119. SQL_Remaining_Delay: NULL
  120. Slave_SQL_Running_State:
  121. Master_Retry_Count: 86400
  122. Master_Bind:
  123. Last_IO_Error_Timestamp:
  124. Last_SQL_Error_Timestamp:
  125. Master_SSL_Crl:
  126. Master_SSL_Crlpath:
  127. Retrieved_Gtid_Set:
  128. Executed_Gtid_Set: b4326a77-0a31-11ec-a991-000c298d3571:1-2,
  129. d68b404d-0a35-11ec-9df1-000c29581959:1
  130. Auto_Position: 1
  131. Replicate_Rewrite_DB:
  132. Channel_Name: master2
  133. Master_TLS_Version:
  134. 2 rows in set (0.00 sec)
  135. #开启俩个slave
  136. mysql> start slave;
  137. #再次查看状态

GTID(俩主一从)测试:

  1. #master01主库创建一个test数据库
  2. mysql> create database test;
  3. Query OK, 1 row affected (0.00 sec)
  4. mysql> show databases;
  5. +--------------------+
  6. | Database |
  7. +--------------------+
  8. | information_schema |
  9. | mysql |
  10. | performance_schema |
  11. | sys |
  12. | test |
  13. +--------------------+
  14. 5 rows in set (0.00 sec)
  15. #master02主库上查看
  16. mysql> show databases;
  17. +--------------------+
  18. | Database |
  19. +--------------------+
  20. | information_schema |
  21. | mysql |
  22. | performance_schema |
  23. | sys | #没有内容
  24. +--------------------+
  25. 4 rows in set (0.00 sec)
  26. #slave从库查看
  27. mysql> show databases;
  28. +--------------------+
  29. | Database |
  30. +--------------------+
  31. | information_schema |
  32. | mysql |
  33. | performance_schema |
  34. | sys |
  35. | test | #已经同步了test库
  36. +--------------------+
  37. 5 rows in set (0.00 sec)
  38. #mysql02主库创建一个RHCA数据库
  39. mysql> create database RHCA;
  40. Query OK, 1 row affected (0.01 sec)
  41. mysql> show databases;
  42. +--------------------+
  43. | Database |
  44. +--------------------+
  45. | information_schema |
  46. | RHCA |
  47. | mysql |
  48. | performance_schema |
  49. | sys |
  50. +--------------------+
  51. 5 rows in set (0.00 sec)
  52. #slave从库
  53. mysql> show databases;
  54. +--------------------+
  55. | Database |
  56. +--------------------+
  57. | information_schema |
  58. | RHCA |
  59. | mysql |
  60. | performance_schema |
  61. | sys | #有了mysql01主库的test库和mysql02的RHCA的库
  62. | test |
  63. +--------------------+
  64. 6 rows in set (0.00 sec)

slave相关命令:

show slave status; //查看全部slave状态

show slave status for channel ‘naem'; //查看单个slave状态

reset slave; #重置全部slave

reset slave for channel ‘master1'; #重置单个slave

stop slave for channel ‘master1'; #暂停单个slave

start slave for channel ‘master1'; #开启单个slave

虽然我在做的过程没有遇到错误,但是下面这个是最最容易出现的错误

配置完开启slave出现报错

  1. mysql> start slave;
  2. ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解决问题

由于mysql.slave_relay_log_info表中保留了以前的复制信息,导致新从库启动时无法找到对应文件,那么我们清理掉该表中的记录即可

  1. mysql> reset slave;
  2. Query OK, 0 rows affected (0.00 sec)

以上就是MySQL示例DTID主从原理解析的详细内容,更多关于MySQL示例DTID主从原理的资料请关注服务器之家其它相关文章!

原文链接:https://blog.csdn.net/qq_47945825/article/details/120023860