MySQL5.7 基于GTID的多源复制

电玩女神 2022-06-14 02:11 212阅读 0赞

基于GTID的MySQL5.7的多源复制

主库1：192.168.1.246:3306
    主库2：192.168.1.246:3307
    从库1：192.168.1.246:3308

安装5.7数据库

初始化5.7数据库，与之前不一样，命令如下(--defaults-file 一定为第一个参数)
    ./bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize --user=mysql
    新登录进去后改密码，新建用户
    set password for 'root'@'localhost'=password('root');
    
    CREATE USER 'root'@'%' IDENTIFIED BY 'root';
    grant all privileges on *.* to 'root'@'%';
    
    CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl';
    grant replication slave  on *.* to 'repl'@'192.168.1.%';
    flush privileges;
    
    3306端口上新建数据库db3306-新建表db_status
    3307端口上新建数据库db3307-新建表db_status3307
    
    
    CREATE TABLE `db_status3307` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `host` varchar(30) NOT NULL DEFAULT '',
      `port` varchar(10) NOT NULL DEFAULT '',
      `uptime_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=317 DEFAULT CHARSET=utf8;
    
    insert into db_status3307(host,port) values('123456','3307');

搭建多源复制

把3306数据dump出，导入3308端口
    /usr/local/mysql/bin/mysqldump  -uroot -proot -h127.0.0.1 -P3306 --master-data=2 --single-transaction --databases  --add-drop-database  db3306  >3306.sql
    /usr/local/mysql/bin/mysql -uroot -proot -h127.0.0.1 -P3308 < 3306.sql 
    
    配置CHANNEL 3306
    CHANGE MASTER TO
        MASTER_HOST = '192.168.1.246',
        MASTER_PORT = 3306,
        MASTER_USER = 'repl',
        MASTER_PASSWORD = 'repl',
        MASTER_AUTO_POSITION = 1 FOR CHANNEL '3306';
    	
    start slave;
    show slave status\G
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
               Retrieved_Gtid_Set: d023f67f-4a90-11e7-9eff-0800271d97b5:3
                Executed_Gtid_Set: d023f67f-4a90-11e7-9eff-0800271d97b5:1-3			
    
    同样操作3307，数据dump出，导入3308端口
    /usr/local/mysql/bin/mysqldump  -uroot -proot -h127.0.0.1 -P3307 --master-data=2 --single-transaction --databases  --add-drop-database  db3307  >3307.sql
    /usr/local/mysql/bin/mysql -uroot -proot -h127.0.0.1 -P3308 < 3307.sql 
    导入报错
    ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
    此时3308  reset master;在执行导入
    配置CHANNEL 3307
    CHANGE MASTER TO
        MASTER_HOST = '192.168.1.246',
        MASTER_PORT = 3307,
        MASTER_USER = 'repl',
        MASTER_PASSWORD = 'repl',
        MASTER_AUTO_POSITION = 1 FOR CHANNEL '3307';
    	
    start slave for channel '3307';
    show slave status\G
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
               Retrieved_Gtid_Set: 6e71bd59-4a97-11e7-ad35-0800271d97b5:9
                Executed_Gtid_Set: 6e71bd59-4a97-11e7-ad35-0800271d97b5:1-9

多源复制处理报错与单机类似

命令 都加上  for channel '3307';
    跳过错误
    stop slave;  
    set gtid_next='6e71bd59-4a97-11e7-ad35-0800271d97b5:1';  
    begin;commit;  
    set gtid_next='AUTOMATIC';  
    start slave; 
    show slave status\G