MySQL示例DTID主从原理解析

2022-05-15 0 388
目录
  • 1.GTID基本概念
  • 2.GTID优点
  • 3.GTID的工作原理
  • 4.GTID比传统复制的优势
  • 5.启动的方法
  • 6.GTID(一主一从)配置
    • 6.1环境:
    • 6.2在主库上给从库授权:
    • 6.3确保数据一致操作
    • 6.4配置主库
    • 6.5配置从库
    • 6.6配置主从复制
  • 7.GTID(一主俩从)
    • 8.GTID(俩主一从)
      • 1.最新环境
      • 2.所有服务器均关闭防火墙或者放行防火墙
      • 3.授权连接
        • master01库授权普通用户
        • slave进行连接
        • master02授权普通用户
        • slave进行连接
      • 4.分别进行配置文件修改
        • 5.分别重启
          • 6.在进行GTID多主一从配置前,先引入一个概念
            • 7.slave从库以root用户登录进行GTID配置
              • GTID(俩主一从)测试:
              • slave相关命令:
              • 解决问题

          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在主库上给从库授权:

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

          6.3确保数据一致操作

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

          6.4配置主库

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

          6.5配置从库

          [root@mysql02 data]# cat /etc/my.cnf
          [mysqld]
          basedir = /usr/local/mysql 
          datadir = /opt/data 
          socket = /tmp/mysql.sock 
          port = 3306
          user = mysql
          pid-file = /opt/data/mysql.pid
          skip-name-resolve
          #skip-grant-tables 
          gtid_mode=on
          enforce_gtid_consistency=on
          server-id=20
          log-bin=slave_binlog       #开启从库日志
          log_slave-updates=1        #从库允许更新
          binlog_format=row          #格式为行
          skip-slave_start=1   
          #重启
          systemctl restart mysqld 
          

          查看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       |
          +----------------------------------+-----------+
          8 rows in set (0.00 sec)
          

          6.6配置主从复制

          #从库上root登录配置      #help change master to 可以查看帮助文档实例
          mysql> change master to
              -> master_host='192.168.136.239',
              -> master_user='slave',
              -> master_password='slave',
              -> master_port=3306,        #主库端口
              -> master_auto_position=1;  #位置
                                                 #master_use_gtid = current_pos
          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; 
          Slave_IO_Running: Connecting
                      Slave_IO_Running: Yes
                      Slave_SQL_Running: Yes
          保证系统一致性 
          授权一致性
          

          (一主一从GTID)测试

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

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

          #主库创建一个test数据库
          mysql> create database test;
          mysql> show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | mysql              |
          | performance_schema |
          | sys                |
          | test               |
          +--------------------+
          #从库上查看同步情况
          mysql> show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | mysql              |
          | performance_schema |
          | sys                |
          | test               |
          +--------------------+
          6 rows in set (0.00 sec)
          
          #从库创建test02库
          mysql> create database test02;
          Query OK, 1 row affected (0.00 sec)
          #主库上查看
          mysql> show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | mysql              |
          | performance_schema |
          | sys                |         #是没有test02库的
          | test               |
          +--------------------+
          5 rows in set (0.00 sec)
          

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

          7.GTID(一主俩从)

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

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

          [root@mysql03 ~]# cat /etc/my.cnf 
          [mysqld]
          basedir = /usr/local/mysql
          datadir = /opt/data
          socket = /tmp/mysql.sock
          port = 3306
          user = mysql
          pid-file = /opt/data/mysql.pid
          skip-name-resolve
          #skip-grant-tables
          # replication config
          log-bin = master_bin
          server-id = 21               #id必须与之前不同
          gtid-mode = on
          enforce-gtid-consistency = on
          log-slave-updates = 1
          binlog-format = row
          skip-slave-start = 1
          #查看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       |
          +----------------------------------+-----------+
          #由于之前只权限了一个ip,此刻在mysql01主数据库上再授权一个ip
          mysql> grant replication slave on *.* to 'slave'@'192.168.136.230' identified by 'slave';
          Query OK, 0 rows affected, 1 warning (0.00 sec)
          mysql> flush privileges;
          Query OK, 0 rows affected (0.00 sec)
          #测试连接
          [root@mysql ~]#  mysql -uslave -pslave -h192.168.136.239
          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 17
          Server version: 5.7.33-log MySQL Community Server (GPL)
          Copyright (c) 2000, 2021, Oracle and/or its affiliates.
          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> 
          #mysql03从库上root用户连接进行相应配置
          [root@mysql03 ~]#  mysql -uroot -p1
          mysql> change master to
              -> master_host='192.168.136.239',  #主库ip
              -> master_user='slave',              #主库授权的普通用户
              -> master_password='slave',
              -> master_port=3306,              #主库端口
              -> master_auto_position=1;   #位置从1开始同步
          #也可以查看帮助进行配置
          mysql> help change master to;
          CHANGE MASTER TO
            MASTER_HOST='source2.example.com',
            MASTER_USER='replication',
            MASTER_PASSWORD='password',
            MASTER_PORT=3306,
            MASTER_LOG_FILE='source2-bin.001',
            MASTER_LOG_POS=4,
            MASTER_CONNECT_RETRY=10;
          URL: https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
          
          #开启
          mysql> start slave;
          mysql> show slave status\G;
          *************************** 1. row ***************************
                         Slave_IO_State: Waiting for master to send event
                            Master_Host: 192.168.136.239
                            Master_User: slave
                            Master_Port: 3306
                          Connect_Retry: 60
                        Master_Log_File: master_bin.000002
                    Read_Master_Log_Pos: 2172
                         Relay_Log_File: mysql-relay-bin.000002
                          Relay_Log_Pos: 2387
                  Relay_Master_Log_File: master_bin.000002
                       Slave_IO_Running: Yes
                      Slave_SQL_Running: Yes    #显示俩个yes则运行成功!
          #mysql03查看数据库,数据库内容也同步成功
          mysql> show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | mysql              |
          | performance_schema |
          | sys                |
          | test               |
          +--------------------+
          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.所有服务器均关闭防火墙或者放行防火墙

          [root@master01 ~]# systemctl stop firewalld
          [root@master01 ~]# systemctl disable firewalld
          [root@master02 ~]# systemctl stop firewalld
          [root@master02 ~]# systemctl disable firewalld
          [root@slave ~]# systemctl stop firewalld
          [root@slave ~]# systemctl disable firewalld
          

          3.授权连接

          master01库授权普通用户

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

          slave进行连接

          [root@slave ~]# mysql -uuser -p'user' -h192.168.136.239
          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 5
          Server version: 5.7.33 MySQL Community Server (GPL)
          Copyright (c) 2000, 2021, Oracle and/or its affiliates.
          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.
          

          master02授权普通用户

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

          slave进行连接

          [root@slave ~]# mysql -uapp -papp -h192.168.136.219
          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 3
          Server version: 5.7.33 MySQL Community Server (GPL)
          Copyright (c) 2000, 2021, Oracle and/or its affiliates.
          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> 
          

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

          #master01主机:
          [root@master01 ~]# cat /etc/my.cnf 
          [mysqld]
          basedir = /usr/local/mysql
          datadir = /opt/data
          socket = /tmp/mysql.sock
          port = 3306
          user = mysql
          pid-file = /opt/data/mysql.pid
          skip-name-resolve
          skip-grant-tables
          log-bin = master_bin
          server-id = 10
          gtid-mode = on
          enforce-gtid-consistency = on
          log-slave-updates = 1
          binlog-format = row
          skip-slave-start = 1
          #master02主机
          [mysqld]
          basedir = /usr/local/mysql
          datadir = /opt/data
          socket = /tmp/mysql.sock
          port = 3306
          user = mysql
          pid-file = /opt/data/mysql.pid
          skip-name-resolve
                                #replication config
          log-bin = master_bin
          server-id = 11
          gtid-mode = on
          enforce-gtid-consistency = on
          log-slave-updates = 1
          binlog-format = row
          skip-slave-start = 1
          #slave主机
          [mysqld]
          basedir = /usr/local/mysql
          datadir = /opt/data
          socket = /tmp/mysql.sock
          port = 3306
          user = mysql
          pid-file = /opt/data/mysql.pid
          skip-name-resolve
          log-bin = slave_bin
          server-id = 13
          gtid-mode = on
          enforce-gtid-consistency = on
          log-slave-updates = 1
          binlog-format = row
          skip-slave-start = 1
          

          5.分别重启

          [root@master01 ~]# systemctl restart mysqld
          [root@master02 ~]# systemctl restart mysqld
          [root@slave ~]# systemctl restart mysqld
          

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

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

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

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

          方式一(mysql内设置):
          set global master_info_repository='table';
          set global relay_log_info_repository='table';
          方式二(/etc/my.cnf内设置):
          3.在my.cnf中设置
          master_info_repository    = TABLE 
          relay_log_info_repository = TABLE   
          #检查是否更改成功
          mysql> show variables where variable_name in  ('relay_log_info_repository','master_info_repository');
          +---------------------------+-------+
          | Variable_name             | Value |
          +---------------------------+-------+
          | master_info_repository    | TABLE |
          | relay_log_info_repository | TABLE |
          +---------------------------+-------+
          

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

          #slave从库上配置俩个主库GTID复制
          mysql> change master to
              -> master_host='192.168.136.219',  #mysql02主库ip
              -> master_user='app',              #mysql02主库授权的普通用户
              -> master_password='app',           #mysql02主库授权的普通用户密码
              -> master_port=3306,              #主库端口
              -> master_auto_position=1 for channel 'master01';   #位置从1开始同步,并且第一个slave取名master01
          mysql> change master to
              -> master_host='192.168.136.239',  #mysql01主库ip
              -> master_user='user',              
              -> master_password='user',          
              -> master_port=3306,              #主库端口
              -> master_auto_position=1 for channel 'master02';   #位置从1开始同步,并且第一个slave取名master01
          #查看俩个slave状态                   
          mysql> show slave status\G;
          *************************** 1. row ***************************
                         Slave_IO_State: 
                            Master_Host: 192.168.136.219
                            Master_User: app
                            Master_Port: 3306
                          Connect_Retry: 60
                        Master_Log_File: 
                    Read_Master_Log_Pos: 4
                         Relay_Log_File: slave02-relay-bin-master1.000001
                          Relay_Log_Pos: 4
                  Relay_Master_Log_File: 
                       Slave_IO_Running: No
                      Slave_SQL_Running: No         #都是关闭的
                        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: 0
                        Relay_Log_Space: 154
                        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: NULL
          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: 0
                            Master_UUID: 
                       Master_Info_File: mysql.slave_master_info
                              SQL_Delay: 0
                    SQL_Remaining_Delay: NULL
                Slave_SQL_Running_State: 
                     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: b4326a77-0a31-11ec-a991-000c298d3571:1-2,
          d68b404d-0a35-11ec-9df1-000c29581959:1
                          Auto_Position: 1
                   Replicate_Rewrite_DB: 
                           Channel_Name: master1
                     Master_TLS_Version: 
          *************************** 2. row ***************************
                         Slave_IO_State: 
                            Master_Host: 192.168.136.239
                            Master_User: user
                            Master_Port: 3306
                          Connect_Retry: 60
                        Master_Log_File: 
                    Read_Master_Log_Pos: 4
                         Relay_Log_File: slave02-relay-bin-master2.000001
                          Relay_Log_Pos: 4
                  Relay_Master_Log_File: 
                       Slave_IO_Running: No
                      Slave_SQL_Running: No
                        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: 0
                        Relay_Log_Space: 154
                        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: NULL
          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: 0
                            Master_UUID: 
                       Master_Info_File: mysql.slave_master_info
                              SQL_Delay: 0
                    SQL_Remaining_Delay: NULL
                Slave_SQL_Running_State: 
                     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: b4326a77-0a31-11ec-a991-000c298d3571:1-2,
          d68b404d-0a35-11ec-9df1-000c29581959:1
                          Auto_Position: 1
                   Replicate_Rewrite_DB: 
                           Channel_Name: master2
                     Master_TLS_Version: 
          2 rows in set (0.00 sec)
          #开启俩个slave
          mysql> start slave;
          #再次查看状态
          
          

          GTID(俩主一从)测试:

          #master01主库创建一个test数据库
          mysql> create database test;
          Query OK, 1 row affected (0.00 sec)
          mysql>  show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | mysql              |
          | performance_schema |
          | sys                |
          | test               |
          +--------------------+
          5 rows in set (0.00 sec)
          #master02主库上查看
          mysql>  show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | mysql              |
          | performance_schema |
          | sys                |                  #没有内容
          +--------------------+
          4 rows in set (0.00 sec)
          #slave从库查看
          mysql> show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | mysql              |
          | performance_schema |
          | sys                |
          | test               |          #已经同步了test库
          +--------------------+
          5 rows in set (0.00 sec)
          #mysql02主库创建一个RHCA数据库
          mysql> create database RHCA;
          Query OK, 1 row affected (0.01 sec)
          mysql>  show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | RHCA               |
          | mysql              |
          | performance_schema |
          | sys                |
          +--------------------+
          5 rows in set (0.00 sec)
          #slave从库
          mysql> show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | RHCA               |
          | mysql              |
          | performance_schema |
          | sys                |             #有了mysql01主库的test库和mysql02的RHCA的库
          | test               |
          +--------------------+
          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出现报错

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

          解决问题

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

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

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

          免责声明:
          1、本网站所有发布的源码、软件和资料均为收集各大资源网站整理而来;仅限用于学习和研究目的,您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。 不得使用于非法商业用途,不得违反国家法律。否则后果自负!

          2、本站信息来自网络,版权争议与本站无关。一切关于该资源商业行为与www.niceym.com无关。
          如果您喜欢该程序,请支持正版源码、软件,购买注册,得到更好的正版服务。
          如有侵犯你版权的,请邮件与我们联系处理(邮箱:skknet@qq.com),本站将立即改正。

          NICE源码网 MySql MySQL示例DTID主从原理解析 https://www.niceym.com/38877.html