【Mysql】Mysql5.7的多源復(fù)制搭建

    一.基本環(huán)境
主庫1: 10.24.65.30  3306 MySQL 5.7.14
主庫2: 10.24.65.30  3307 mysql 5.7.14
從庫：10.24.65.33  3306 mysql 5.7.14
基于gtid復(fù)制

    二.搭建注意事項(xiàng)
1.每個實(shí)例上DB名字不能一樣。
2.創(chuàng)建或是復(fù)制用戶時(shí)名字不能一樣，如果經(jīng)常遇到一樣的帳號若是為了規(guī)范創(chuàng)建的帳
號是一樣的，考慮在slave的過濾規(guī)則把mysql復(fù)制忽略掉。
[stop slave sql_thread;change replication filter replicate_ignore_db=mysql;]

    3307啟停：
mysqld_safe --defaults-file=/data/mysql/mysql3307/data/my3307.cnf &
mysqladmin shutdown -S /tmp/mysql3307.sock
mysql  -S /tmp/mysql3307.sock

    三.主庫配置
1.master1參數(shù)設(shè)置
gtid-mode=on
enforce-gtid-consistency=1
skip_slave_start=1
2.master2參數(shù)設(shè)置
gtid-mode=on
enforce-gtid-consistency=1
skip_slave_start=1
3.復(fù)制帳號建立
grant replication slave on *.* to repl@'10.24%' identified by 'replsafe';
flush privileges;
4.slave參數(shù)設(shè)置
gtid-mode=on
enforce-gtid-consistency=1
skip_slave_start=1
master-info-repository=table
relay-log-info-repository=table
5.slave配置連接主庫
change master to
master_host='10.24.65.30',
master_port=3306,
master_user='repl',
master_password='replsafe',
master_auto_position=1 for channel 'master-65303306';

    change master to
master_host='10.24.65.30',
master_port=3307,
master_user='repl',
master_password='replsafe',
master_auto_position=1 for channel 'master-65303307';

    四.常用命令：
1.開關(guān)復(fù)制
start slave io_thread|sql_thread for channel 'master-65303306';
stop slave io_thread|sql_thread for channel 'master-65303306'

    2.監(jiān)控復(fù)制
show slave status for channel 'master-65303306';

    五.校驗(yàn)
master1：
create database mdb1;
use mdb1;
create table t1(id int);

    master2:
create database mdb2;
use mdb2;
create table t2(id int);

    slave:
(product)root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mdb1               |
| mdb2               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

    (product)root@localhost [(none)]> use mdb1;
Database changed
(product)root@localhost [mdb1]> show tables;
+----------------+
| Tables_in_mdb1 |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

    (product)root@localhost [mdb1]> use mdb2;
Database changed
(product)root@localhost [mdb2]> show tables;
+----------------+
| Tables_in_mdb2 |
+----------------+
| t2             |
+----------------+
1 row in set (0.00 sec)

    六.相關(guān)問題
1.多源復(fù)制處理之一：
binlog+position:
stop slave sql_thread for channel 'master-65303306';
set sql_slave_skip_counter=n;
start slave sql_thread for channel 'master-65303306';

    gtid:
stop slave sql_thread for channel 'master-65303306';
set gtid_next='uuid-N';
begin;commit;
set gtid_next='automatic';
start slave sql_thread for channel 'master-65303306';
2.從多源復(fù)制處理之二：
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; 
the first event 'mysql-bin.000181' at 154, the last event read from '/data/mysql/mysql3306/logs/mysql-bin.000181' at 123, the last byte read from '/data/mysql/mysql3306/logs/mysql-bin.000181' at 154.'

    解決方法：
a.由于兩主庫的server-uuid相同，更改其中一個auto.cnf中的server-uuid號
b.并在從庫上執(zhí)行reset slave all清除slave信息
c.重新change master

    補(bǔ)充：