精华内容
下载资源
问答
  • 2022-04-19 15:05:34

    概念

    众所周知,传统的主从备份主要依赖于二进制日志文件位置的复制,也就是基于binlog+pos(binlog filename + position),pos每个主节点和从节点都不一样,这样会导致一个问题,在主节点down掉之后,其他从节点升为主节点,此时再进行主从复制,由于pos不一致的原因,导致很难保持数据一致性。从mysql5.6之后,支持基于GTID的方式进行主从复制(binlog+gtid),与传统复制最大的不同就是GTID和pos的不同,pos是基于每个主从节点的,而GTID是基于全局的。

    工作原理

    • 当一个事务在主库提交时,会产生一个全局的GTID,并且记录到binlog中;
    • binlog传输到slave,并且写入到本地的relaylog中,即告诉Slave,下一个要执行的GTID值;
    • sql线程从relaylog中获取GTID,然后对比slave端的binlog中是否存在这个GTID,如果存在则忽略,如果不存在,则执行此GTID事务,并且保存GTID到自身的binlog中;

    前提条件

    • mysql版本5.6++;
    • 不支持非事务数据库;
    • 不支持CREATE TABLE … SELECT语句(原因:会生成两个sql, 一个是DDL创建表SQL, 一个是insert into 插入数据的 sql; 由于DDL会导致自动提交, 所以这个sql至少需要两个GTID, 但是GTID模式下, 只能给这个sql生成一个GTID);
    • 在传统的主从复制中,slave端不用开启binlog;但是在GTID主从复制中,必须开启binlog;

    主从配置

    主机配置

    # 主库配置
    # 设置server_id,一般设置为IP,保证唯一就好
    server_id=1
    
    #开启gtid模式
    gtid_mode=on
    
    #强制gtid一致性,开启后对于特定create table不被支持
    enforce_gtid_consistency=on
    
    #通过同步产生的变化将记录在自己的binlog中,即可以当从也可以当主
    log-slave-updates=1
    
    #skip_slave_start=1
    
    # 开启binlog,名字可以随意取
    log-bin=mysql-master-binlog
    
    ## binlog日志格式,(mixed,statement,row,默认格式是statement)
    #binlog日志格式,强烈建议,其他格式可能造成数据不一致
    binlog_format=row
    
    ## 日志自动删除日期,默认值为0,表示不自动删除。
    expire_logs_days=7
    
    #要同步的数据库,无法指定多个库,逗号和复制多行都不行,5.7版本测试,在从库指定即可,从库可指定多个
    #binlog-do-db=ride_share
    
    ## 复制过滤:也就是指定哪个数据库不用同步(mysql库一般不同步)
    binlog-ignore-db=mysql,information_schema,sys,performance_schema
    
    #控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大,但可减小MySQL崩溃造成的损失)
    sync_binlog = 1
    
    #这个参数一般用在主主同步中,用来错开自增值, 防止键值冲突
    #auto_increment_offset = 1
    
    #这个参数一般用在主主同步中,用来错开自增值, 防止键值冲突
    #auto_increment_increment = 2
    

    从机配置

    # 从库配置
    # 设置server_id,一般设置为IP,保证唯一就好
    server_id=3
    
    #开启gtid模式
    gtid_mode=on
    
    #强制gtid一致性,开启后对于特定create table不被支持
    enforce_gtid_consistency=on
    
    #通过同步产生的变化将记录在自己的binlog中,即可以当从也可以当主
    log-slave-updates=1
    
    #跳过启动复制
    skip_slave_start=1
    
    # 开启binlog,名字可以随意取
    log-bin=mysql-slave-binlog
    
    ## binlog日志格式,(mixed,statement,row,默认格式是statement)
    #binlog日志格式,强烈建议,其他格式可能造成数据不一致
    binlog_format=row
    
    ## 日志自动删除日期,默认值为0,表示不自动删除。
    expire_logs_days=7
    
    # 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
    # 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
    slave_skip_errors=all
    #需要同步的数据库名。如果不指明同步哪些库,就去掉这行,表示所有库的同步(除了ignore忽略的库)。
    #多个库不能使用,号连接,必须分行写,5.7版本测试是这样
    replicate-do-db=ride_share
    #replicate-do-db=test_data
    #当只针对某些库的某张表进行同步时,多个不同库表,可以配置将上下两行多配置几个
    #replicate-wild-do-table = ride_share.user
    
    ## 复制过滤:也就是指定哪个数据库不用同步(mysql库一般不同步)
    replicate-ignore-db=mysql,information_schema,sys,performance_schema
    
    

    多主多从

    slave配置不变,加上这两行即可;

    master_info_repository = table
    relay_log_info_repository = table
    

    每个主节点都需要授权用户给从节点;

    grant replication slave on *.* to 'repl'@'%' identified by '123456';
    

    从节点连接主节点;

    stop slave;
    #channel对应每个主节点的server_id
    change master to master_host='192.168.2.189',master_user='repl',master_password='123456',master_auto_position=1 for channel '1';
    
    change master to master_host='192.168.150.129',master_user='repl',master_password='123456',master_auto_position=1 for channel '2';
    
    start slave;
    
    show slave status
    

    提示:

    #通过同步产生的变化将记录在自己的binlog中,即可以当从也可以当主(主从一体必备配置项)
    log-slave-updates=1
    
    更多相关内容
  • MySQL基于gtid主从复制(一主一从、一主多从、双主一从) MySQL基于gtid主从复制 什么是GTID? 全局唯一,一个事务对应一个GTID 替代传统的binlog+pos复制;使用master_auto_position=1自动匹配GTID断点进行复制 ...

    MySQL基于gtid主从复制(一主一从、一主多从、双主一从)

    MySQL基于gtid主从复制

    什么是GTID?

    • 全局唯一,一个事务对应一个GTID
    • 替代传统的binlog+pos复制;使用master_auto_position=1自动匹配GTID断点进行复制
    • MySQL5.6开始支持
    • 在传统的主从复制中,slave端不用开启binlog;但是在GTID主从复制中,必须开启binlog
    • slave端在接受master的binlog时,会校验GTID值
    • 为了保证主从数据的一致性,多线程同时执行一个GTID

    工作原理

    • master更新数据时,会在事务前产生GTID,一同记录到binlog日志中
    • slave端的i/o 线程将变更的binlog,写入到本地的relay log中
    • sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录
    • 如果有记录,说明该GTID的事务已经执行,slave会忽略
    • 如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog
    • 在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描

    配置Gtid

    一主一从

    环境:

    数据库角色IP
    主数据库192.168.72.131
    从数据库192.168.72.132

    授权一个用户,用于主从复制

    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 'xk'@'192.168.72.132' identified by "123";
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> grant  replication slave on *.* to  'xk'@'192.168.72.132'  ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    

    修改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.000014 |     1951 |              |                  | b736875b-097e-11ec-b557-000c29810dc2:1-9 |
    +------------------+----------+--------------+------------------+------------------------------------------+
    1 row in set (0.00 sec)
    
    
    

    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! 
    
    

    配置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)
    
    mysql> change master to
        -> master_host='192.168.72.131' ,
        -> master_user='xk' ,
        -> master_password='123' ,
        -> 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.72.131
                      Master_User: chenshunli
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000014
              Read_Master_Log_Pos: 154
                   Relay_Log_File: localhost-relay-bin.000003
                    Relay_Log_Pos: 367
            Relay_Master_Log_File: mysql-bin.000014
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    
    
    
    
    

    验证主从复制

    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | chenshunli         |
    | 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.000006 |     1389 |              |                  | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
    +------------------+----------+--------------+------------------+-------------------------------------------+
    1 row in set (0.00 sec)
    
    发现master主机插入的数据,已经在slave主机进行了同步,
    
    

    一主多从

    数据库角色IP
    主数据库192.168.72.131
    从数据库1192.168.72.132
    从数据库2192.168.72.133
    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
    
    

    配置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.72.131' ,
        -> master_user='xk' ,
        -> master_password='123' ,
        -> 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.72.131
                      Master_User: chenshunli
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000014
              Read_Master_Log_Pos: 1776
                   Relay_Log_File: localhost-relay-bin.000004
                    Relay_Log_Pos: 454
            Relay_Master_Log_File: mysql-bin.000014
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | chenshunli         |
    | 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 |      750 |              |                  | b736875b-097e-11ec-b557-000c29810dc2:1-10,
    ba11a7ff-097f-11ec-9272-000c29959565:1,
    ddd6f885-0a26-11ec-81cc-000c29f2278e:1 |
    +------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
    
    
    

    双主多从

    主服务器配置与以上相同

    查看主库信息

    //主库1
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------------------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
    +------------------+----------+--------------+------------------+-------------------------------------------+
    | mysql-bin.000014 |     2128 |              |                  | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
    +------------------+----------+--------------+------------------+-------------------------------------------+
    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.000002 |      154 |              |                  | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
    +------------------+----------+--------------+------------------+-------------------------------------------+
    1 row in set (0.00 sec)
    
    

    从库配置也与上面相同
    配置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.72.131' ,
        -> master_user='xk' ,
        -> master_password='123' ,
        -> master_log_file='mysql-bin.000014'
        -> master_log_pos=2128
        -> for channel 'master-1';  //创建隧道
        
    
    配置主库2
    
    mysql> change master to
        -> master_host='192.168.72.132' ,
        -> master_user='xk' ,
        -> master_password='123' ,
        -> master_log_file='mysql-bin.000002'
        -> master_log_pos=154
        -> 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.72.131
                      Master_User: xk
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000014
              Read_Master_Log_Pos: 2128
                   Relay_Log_File: localhost-relay-bin-master@002d1.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000014
                 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: 2128
                  Relay_Log_Space: 544
                  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: b736875b-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.72.132
                      Master_User: xk
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000002
              Read_Master_Log_Pos: 154
                   Relay_Log_File: localhost-relay-bin-master@002d2.000002
                    Relay_Log_Pos: 320
            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: 154
                  Relay_Log_Space: 544
                  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: ba11a7ff-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上创建数据库xk
    mysql> create database xk;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | xk         |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    
    主库2上创建数据库xiongke
    mysql> create database xiongke;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | xiongke             |
    +--------------------+
    5 rows in set (0.00 sec)
    
    
    
    从库上开启从服务后查看
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | xk         |
    | mysql              |
    | performance_schema |
    | sys                |
    | xiongke             |
    +--------------------+
    6 rows in set (0.00 sec)
    
    
    展开全文
  • Mysql基于GTID主从复制

    2018-07-05 12:57:24
    MySQL主从复制是一个异步的复制过程(虽然一般情况下感觉是实时的),数据将从一个Mysql数据库(我们称之为Master)复制到另一个Mysql数据库(我们称之为Slave),在Master与Slave之间实现整个主从复制的过程是由...

    一、Mysql主从复制

    1、工作原理

    MySQL的主从复制是一个异步的复制过程(虽然一般情况下感觉是实时的),数据将从一个Mysql数据库(我们称之为Master)复制到另一个Mysql数据库(我们称之为Slave),在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程(SQL线程和IO线程)在Slave端,另一个线程(I/O线程)在Master端。
    要实现MySQL的主从复制,首先必须打开Master端的binlog记录功能,否则就无法实现。因为整个复制过程实际上就是Slave从Master端获取binlog日志,然后再在Slave上以相同顺序执行获取的binlog日志中的记录的各种SQL操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用 “—log-bin” 参数选项,或者在 my.cnf 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加 “log-bin” 参数项。
    这里写图片描述

    2、工作过程

    1)在Slave 服务器上执行start slave命令开启主从复制开关,开始进行主从复制。

    2)此时,Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器,并请求从执行binlog日志文件的指定位置(日志文件名和位置是在配置主从复制服务时执行change master命令指定的)之后开始发送binlog日志内容。

    3)Master服务器接收到来自Slave服务器的IO线程的请求后,其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息,然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外,还有在Master服务器端记录的IO线程。返回的信息中除了binlog中的下一个指定更新位置。

    4)当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后,会将binlog日志内容依次写到Slave端自身的Relay Log(即中继日志)文件(Mysql-relay-bin.xxx)的最末端,并将新的binlog文件名和位置记录到master-info文件中,以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容。

    5)Slave服务器端的SQL线程会实时检测本地Relay Log中IO线程新增的日志内容,然后及时把Relay LOG 文件中的内容解析成sql语句,并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句,并在relay-log.info中记录当前应用中继日志的文件名和位置点。
    这里写图片描述

    3、主从复制条件

    • 开启Binlog功能
    • 主库要建立账号
    • 从库要配置master.info(CHANGE MASTER to…相当于配置密码文件和Master的相关信息)
    • start slave 开启复制功能

    4、知识点

    • 3个线程,主库IO,从库IO和SQL及作用
    • master.info(从库)作用
    • relay-log 作用
    • 异步复制
    • binlog作用(如果需要级联需要开启Binlog)

    5、小结

    • 主从复制是异步的逻辑的SQL语句级的复制;
    • 复制时,主库有一个I/O线程,从库有两个线程,I/O和SQL线程;
    • 实现主从复制的必要条件是主库要开启记录binlog功能;
    • 作为复制的所有Mysql节点的server-id都不能相同;
    • binlog文件只记录对数据库有更改的SQL语句(来自主库内容的变更),不记录任何查询(select,show)语句。

    6、mysql主从复制配置

    环境配置:

    Linux系统版本:Redhat 6.5版本
    系统防火墙关闭:selinux=disabled
    server5:172.25.51.5(Master
    server6:172.25.51.6(Slave

    系统部署:

    1、Master数据库的搭建
    <1>下载并解压mysql安装包mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
    [root@server5 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
    

    示图:解压后的安装包
    这里写图片描述

    <2>安装mysql的5个模块插件并开启服务
    [root@server5 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm  mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y
    [root@server5 ~]# /etc/init.d/mysqld start
    
    <3>查找mysql数据库原始密码
    [root@server5 ~]# grep password /var/log/mysqld.log
    

    示图:系统安全初始化原密码
    这里写图片描述

    <4>mysql数据库安全初始化
    [root@server5 ~]# mysql_secure_installation
    

    注意:

    mysql数据库安全初始化过程中,首先输入原密码,再设定新密码,新密码设定要求:字母大小写+特殊字符+数字,此处密码设定为WESTOS.com123。


    <5>修改配置文件并重新开启数据库服务
    [root@server5 ~]# vim /etc/my.cnf
     29 server-id=1                ##服务器标识,主从id取值不能相同
     30 log-bin=mysql-bin          ##二进制日志
     [root@server5 ~]# /etc/init.d/mysqld restart
    

    这里写图片描述

    <6>登陆数据库
    [root@server5 ~]# mysql -p
    

    示图:查看master,此时id为154
    这里写图片描述

    <7>授权

    授权slave以repl身份从172.25.51.0/24网段登陆,对任何数据库的任何表进行操作,密码‘WESTOS.com123‘

    mysql> grant replication slave on *.* to repl@'172.25.51.%' identified by 'WESTOS.com123';
    

    示图:授权后,master状态pos值为447
    这里写图片描述

    2.Slave数据库的搭建
    <1>下载并解压mysql安装包mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
    <2>安装mysql的5个模块插件并开启mysql服务
    <3>查找mysql数据库原始密码
    <4>mysql数据库安全初始化
    <5>修改配置文件并重新开启数据库服务
    [root@server6 ~]# vim /etc/my.cnf
     29 server-id=2             ##服务器默认id,不能与mater相同
    [root@server5 ~]# /etc/init.d/mysqld restart
    
    <6>登陆数据库并与master建立认证
    mysql> change master to master_host='172.25.51.5',master_user='repl',master_password='WESTOS.com123',master_log_file='mysql-bin.000001',master_log_pos=447; 
    

    注意: file和pos为Master中授权后的数值
    这里写图片描述


    <7>开启slave,并查看slave状态
    mysql> start slave;
    mysql> show slave status\G;
    

    示图:查看IO线程和sql线程开启即可
    Slave的IO线程以repl身份拷贝(多线程)Master的log-bin到自己的relay-log,Slave的sql线程(单线程)读取relay-log,并在自身Slave服务器上按解析SQL语句的位置顺序执行sql语句,完成与Master同样的操作从而达到复制。
    这里写图片描述

    3.测试:
    <1>Master建立westos数据库并创建usertb表格,插入表格内容
    mysql> create database westos;
    mysql> use westos
    mysql> create table usertb ( 
        -> username varchar(20) not null,
        -> password varchar(25) not null);
    mysql> insert into usertb values('user1',123);
    mysql> insert into usertb values('user2',456);
    

    示图:查看Master的westos.usertb表格信息
    这里写图片描述

    <2>Slave登陆数据库,查看westos.usertb表格内容,Slave自动同步

    示图:主从复制
    这里写图片描述

    二、GTID主从复制

    1、Master

    <1>修改配置文件并重新开启服务
    [root@server5 ~]# vim /etc/my.cnf
     29 server-id=1
     30 log-bin=mysql-bin
     31 
     32 gtid_mode=ON           ##开启gtid模块
     33 enforce-gtid-consistency=true
     [root@server5 ~]# /etc/init.d/mysqld restart
    

    这里写图片描述

    <2>进入数据库,查看file和POS

    这里写图片描述

    2、Slave

    <1>修改配置文件并重新开启服务
    [root@server6 ~]# vim /etc/my.cnf
     29 server-id=2
     31 gtid_mode=ON
     32 enforce-gtid-consistency=true
    
    [root@server5 ~]# /etc/init.d/mysqld restart
    

    这里写图片描述

    <2>基于GTID的复制
    mysql> stop slave;    ##停止slave
    mysql> change master to master_host='172.25.51.5',master_user='repl',master_password='WESTOS.com123',MASTER_AUTO_POSITION = 1;
    
    <3>开启Slave,并查看其进程状态
    mysql> start slave;
    mysql> show slave status\G;
    

    这里写图片描述

    测试:Master数据库表中插入信息,Slave数据库表格内容复制

    示图:Master端查看数据库信息
    这里写图片描述
    示图:Slave端同步信息
    这里写图片描述

    展开全文
  • 本文介绍MySQL基于GTID主从复制安装过程。GTID核心参数介绍GTID(Global Transaction ID)是对于一个已提交事务的唯一编号,并且是一个全局(主从复制)唯一的编号它的官方定义如下:GTID= source id:transaction_id什么...

    大家好,我是anyux。本文介绍MySQL基于GTID主从复制安装过程。

    c6c728fbea0a946479dc11ff213a3661.png

    GTID核心参数

    介绍

    GTID(Global Transaction ID)是对于一个已提交事务的唯一编号,并且是一个全局(主从复制)唯一的编号

    它的官方定义如下:

    GTID= source id:transaction_id

    什么是server_uuid,server-id的区别

    核心特性:全局唯一,具备幂等性

    GTID核心参数

    重要参数:

    启用gtid类型,否则就是普通的复制架构

    gtid-mode=on

    强制GTID的致性

    enforce-gtid-consistency=true

    slave更新是否记录日志,读取relaylog最后一个事务的GTID

    log-slave-updates=1

    GTID主从复制配置过程

    清理环境

    pkill mysqld\rm -rf /data/mysql/data/*\rm -rf /data/binlog/*

    准备多个目录

    \rm -rf /data/33*mkdir -p /data/330{7,8,9}/data

    0cb3672d94f44c197763c0e5edda5635.png

    准备配置文件

    主库server_id=6

    cat >/etc/my.cnf<

    从库server_id=7

    cat >/data/3307/my.cnf<

    从库server_id=8

    cat >/data/3308/my.cnf<

    使用单元管理

    cat > /etc/systemd/system/mysqld.service<

    提示,向/etc/systemd/system/目录下添加新的单元文件后,需要重新加载,执行下面命令

    systemctl daemon-reload

    systemd管理多实例

    cd /etc/systemd/systemcp mysqld.service mysqld3307.serivcecp mysqld.service mysqld3308.serivcecp mysqld.service mysqld3309.serivcesed -i 's#/etc/my.cnf#/data/3307/my.cnf#g' mysqld3307.servicesed -i 's#/etc/my.cnf#/data/3308/my.cnf#g' mysqld3308.servicesed -i 's#/etc/my.cnf#/data/3309/my.cnf#g' mysqld3309.service

    授权

    chown -R mysql.mysql /data/*

    初始化多实例数据

    初始化主库server_id=6

    mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data

    初始化从库server_id=7

    mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3307/data

    初始化从库server_id=8

    mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3308/data

    aedada93912a063d96372ccd44fa1758.png

    启动数据库

    启动主库server_id=6

    systemctl restart mysqld

    启动从库server_id=7

    systemctl restart mysqld3307

    启动从库server_id=8

    systemctl restart mysqld3308

    多实例验证

    mysql -e "select @@server_id;grant all on *.* to root@'localhost' identified by 'root' with grant option;flush privileges;"mysql -S /data/3307/mysql.sock -e "select @@server_id;grant all on *.* to root@'localhost' identified by 'root' with grant option;flush privileges;"mysql -S /data/3308/mysql.sock -e "select @@server_id;grant all on *.* to root@'localhost' identified by 'root' with grant option;flush privileges;"

    构建主从

    主库server_id=6下创建用户

    mysql -uroot -proot -e "grant replication slave on *.* to repl@'%' identified by 'repl'";

    检查主库状态,根据主库状态来查找binlog日志和pos号

    mysql -uroot -proot -e "show master binary";

    从库server_id=7下开启slave,查看从库状态信息

    mysql -uroot -proot -S /data/3307/mysql.sock -e "change master tomaster_host='192.168.255.113',master_user='repl',master_password='repl',master_auto_position=1;start slave;show slave status\G"

    从库server_id=8下开slave,查看从库状态信息

    mysql -uroot -proot -S /data/3308/mysql.sock -e "change master tomaster_host='192.168.255.113',master_user='repl',master_password='repl',master_auto_position=1;start slave;show slave status\G"

    验证主从状态

    检查主库sever-id=6

    mysql -uroot -proot -e "show master status";

    检查从库sever-id=7

    mysql -uroot -proot -S /data/3307/mysql.sock -e "show slave status\G"

    检查从库sever-id=8

    mysql -uroot -proot -S /data/3308/mysql.sock -e "show slave status\G";

    检验主从复制功能

    主库插入数据sever-id=6

    mysql -uroot -proot -e "create database tmp_gtid charset utf8mb4;use tmp_gtid;create table tmp(id int,name varchar(255));insert into tmp values(1,'zs'),(2,'ls'),(3,'w5')";

    从库检查数据sever-id=7

    mysql -uroot -proot -S /data/3307/mysql.sock -e "show databases"| grep "tmp_gtid" && mysql -uroot -proot -e "use tmp_gtid; select * from tmp;"

    从库检查数据sever-id=8

    mysql -uroot -proot -S /data/3308/mysql.sock -e "show databases"| grep "tmp_gtid" && mysql -uroot -proot -e "use tmp_gtid; select * from tmp;"

    70a0b58fa2dc53adb47cba29aedd660d.png

    GTID复制和普通复制和区别

    非GTID

    master_log_file='log-bin:000001',master_log_pos='523'

    GTID

    master_auto_postion=1;

    1.在主从复制环境中,主库发生过的事务,在全局都是由唯一GTID记录的,更方便Failover

    2.额外功能参数3个

    3.change master to 的时候不再需要binlog文件名和position号

    master_auto_position=1

    4.在复制过程中,从库不再依赖master.info文件,而是直接读取最后一个relaylog的GTID号

    5.mysqldump备份时,默认会将备份中包含的事务操作,使用以下方式,告诉从库,备份中已经存在以上事务,不需要运行了,直接从下一个GTID开始请求binlog就行

    set @@global.gtid_pured='xxx:1'

    展开全文
  • 一、MySQL8.0基于GTID主从复制 注意: MySQL8.0之前版本的mysql密码加密方式为mysql_native_password,而MySQL8.0默认新添加的用户密码默认使用的caching_sha2_password,因此进行使用主从复制时可能会遇到错误:...
  • MySQL主从复制入门 确定主库与从库的关系(尽量保持主从库版本一致) 在主库创建备份所需的user create user ‘repl’ @’%’ identified by ‘123456’ (创建一个用户,要给予你备库可以访问的地址权限) 在主库...
  • GTID的概念全局事务标识:global transaction identifiersGTID是一个事务一一对应,并且全局唯一IDGTID在一个服务器上只执行一次,避免重复执行导致数据混乱或主从不一致GTID用来代替传统复制方法,不再使用MASTER_...
  • 准备两台安装好MySQL的机器:192.168.1.113和192.168.1.108 master:192.168.1.113 slave:192.168.1.108 1.2 防火墙关闭,互相可以访问通 2、开始配置 2.1 master机器配置 [mysql] # 设置mysql客户端默认字符集 ...
  • 解决mysql使用GTID主从复制错误问题做MySQL主从的话肯定会遇到很多同步上的问题, 大多数都是由于机器宕机,重启,或者是主键冲突等引起的从服务器停止工作, 这里专门收集类似问题并提供整理解决方案,仅供参考!...
  • 一、添加Maria源 vi /etc/yum.repos.d/MariaDB.repo 粘贴阿里云的最新mariadb镜像: [mariadb] name = MariaDB ...gpgcheck=1 安装新版本的MariaDB ...yum install mariadb mariadb-server ...开启binlog日志
  • 文章目录MySQL基于Gtid主从复制概念:什么是GtidGtid的作用Gtid的工作原理配置一主一从Gtid在主数据库里创建一个同步账户授权给从数据库使用配置一主多从Gtid MySQL基于Gtid主从复制 概念: ​ 从 MySQL 5.6.5 ...
  • 但是在GTID主从复制中,必须开启binlog 5、slave端在接受master的binlog时,会校验GTID值,则slave需要存储硬盘比之前更大! 6、为了保证主从数据的一致性,多线程同时执行一个GTID Master_UUID:
  • 1.主从复制1.1原理主库开启binlog功能并授权从库连接主库,从库通过change master得到主库的相关同步信息,然后连接主库进行验证,主库IO线程根据从库slave线程的请求,从master.info开始记录的位置点向下开始取信息...
  • - gtid_mode=ON(必选) - log_bin=ON(必选) - log-slave-updates=ON(必选) - enforce-gtid-consistency(必选)
  • 参考http://imysql.com/tag/gtidhttp://mysqllover.com/?p=594Mysql 基于GTID主从复制及切换一、主从复制配置两个mysql服务的my.cnf 中相关内容配置[mysqld]#从复制数据库表设置replicate-wild-ignore-table = ...
  • mysql主从复制&&基于GTID主从复制

    千次阅读 2018-08-13 15:37:55
    在一主多从的数据库体系中,多个从服务器采用异步的方式更新主数据库的变化,业务服务器在执行写或者相关修改数据库的操作是在主服务器上...Mysql主从复制的实现原理图大致如下(来源网络): MySQL之间数据复制...
  • 前言: Mysql是现在普遍使用的数据库,但是如果宕机了必然会造成数据丢失。为了保证mysql数据库的可靠性。...本文我们将会详细给大家介绍在Redhat7环境下Mysql主从复制以及基于gtid主从复制、半同...
  • mysql5.7基于GTID实现主从复制一.GTID概述1.1 GTID的概念GTID,全局事务ID globaltransaction identifiersGTID是一个事务对应一个全局唯一ID,随事务记录到binlog中,用来标识事务。GTID 对应事务在一个服务器上只...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 19,374
精华内容 7,749
关键字:

mysql基于gtid的主从复制

mysql 订阅