基于MySQL5.7搭建GTID+Row格式的复制

基于MySQL5.7搭建GTID+Row格式的复制
   MySQL8.0

环境说明：
    主机列表：
    主库：   172.18.0.150
    从库：   172.18.0.151
    MySQL版本： MySQL-5.7.25
                MySQL-8.0.15
    配置文件：my3306.cnf 

搭建目标：
1. 异步复制
主库：
    1. 拿到原始密码 登录主库(error log)
    2. alter user user() identified by 'wubxwubx';
    3. create user 'repl'@'%' identified by 'repl4slave';
       grant replication slave on *.* to 'repl'@'%';
    4. reset master;

    配置上的要求：
        server-id
        log_bin
        binlog_format =row
        gtid_mode
        enforce_gtid_consistency
    建立复制用帐号：
        create user 'repl'@'%' identified by 'repl4slave';
        grant replication slave on *.* to 'repl'@'%';
从库：

    change master to  master_host='172.18.0.150', master_port=3306, master_user='repl', master_password='repl4slave', MASTER_AUTO_POSITION=1;

    root@localhost [(none)]>show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 |      154 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

    change master to  master_host='172.18.0.150', master_port=3306, master_user='repl', master_password='repl4slave', master_log_file='mysql-bin.000001', master_log_pos=154;



验证功能
    show slave status\G;

2. 增强半同步复制(5.7)

    复制结构是Ok。
    
    主库和从库都加载
    INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
    INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
    show plugins


    slave: 
1.    SET GLOBAL rpl_semi_sync_slave_enabled = 1;
4.    stop slave io_thread; start slave io_thread;show slave status;

    master :
2.    SET GLOBAL rpl_semi_sync_master_enabled = 1;
3.   SET GLOBAL rpl_semi_sync_master_timeout = 10000;    10秒

    [mysqld]
    rpl_semi_sync_master_timeout = 1000;    不是金融环境的话可以配置1秒

如果从库崩溃很长时间了, 直接配置半同步会拖垮主库,所以不建议在配置文件中设置半同步

mysql -S /tmp/mysql3306.sock -p
alter user user() identified by 'xxxxx';
set global super_read_only=0
set global read_only=0;
alter user user() identified by 'xxxxx';
show master status;

reset master;              重置binlog文件,从000001_154号开始
show master status;

show global variables like '%server%';       --确认server_id
show global variables like '%gtid%';          --确认gtid_mode


从库

主库执行
show global status like '%sync%';
rpl_semi_sync_master_clients                     有多少个客户端连过来
rpl_semi_sync_master_net_waits   

show global variables like '%sync%';
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout
rpl_semi_sync_master_wait_for_slave_count               最少有多少个从库相应
           

从库执行,查看从库有没有启用半同步
show global status like '%sync%';
rpl_semi_sync_slave_enabled 
start slave io_thread;
start slave sql_thread;
set global rpl_semi_sync_slave_enabled = 1;
stop slave io_thread;start slave io_thread;


主库执行
show global status like '%sync%';
rpl_semi_sync_master_no_tx                          没有确认的值