mysql 多主一从

CHANGE MASTER TO 语句

CHANGE MASTER TO option [, option] ... [ channel_option ]

option: {
    MASTER_BIND = 'interface_name'
  | MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = interval
  | MASTER_RETRY_COUNT = count
  | MASTER_DELAY = interval
  | MASTER_HEARTBEAT_PERIOD = interval
  | MASTER_LOG_FILE = 'source_log_name'
  | MASTER_LOG_POS = source_log_pos
  | MASTER_AUTO_POSITION = {0|1}
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_CRL = 'crl_file_name'
  | MASTER_SSL_CRLPATH = 'crl_directory_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
  | MASTER_TLS_VERSION = 'protocol_list'
  | IGNORE_SERVER_IDS = (server_id_list)
}

channel_option:
    FOR CHANNEL channel

server_id_list:
    [server_id [, server_id] ... ]

可选子句使您能够命名该语句适用于哪个复制通道。提供子句将 语句应用于特定的复制通道，并用于添加新通道或修改现有通道。例如，要添加一个名为 channel2 的新频道： FOR CHANNEL channelFOR CHANNEL channelCHANGE MASTER TO

CHANGE MASTER TO MASTER_HOST=host1, MASTER_PORT=3002 FOR CHANNEL 'channel2'

如果没有指定子句并且不存在额外的通道，则该语句适用于默认通道。使用多个复制通道时，如果CHANGE MASTER TO语句未使用 子句命名通道，则会发生错误。

导出数据

master1节点

mysqldump -hxxx -uxxx --quick --routines --master-data=2 --single-transaction --databases db1  > db1.sql    

master2节点

mysqldump -hxxx -uxxx --quick --routines --master-data=2 --single-transaction --databases db2  > db2.sql 

master3节点

mysqldump -hxxx -uxxx --quick --routines --master-data=2 --single-transaction --databases db3  > db3.sql 

导入数据

mysql -hxxx -uxxx -p < db1.sql
mysql -hxxx -uxxx -p < db2.sql
mysql -hxxx -uxxx -p < db3.sql

导入报错

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解决办法

方法一

# 清空本地的gtid
mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

方法二

/usr/local/mysql/bin/mysqldump --set-gtid-purged=OFF
加上--set-gtid-purged=OFF参数再备份，这样就忽略。

设置同步

修改配置文件

从库配置文件修改（my.cnf）在[mysqld]下增加参数server-id=1（值与主库不一样即可）

添加以下参数

master_info_repository = table
relay_log_info_repository = table

主库中授权

grant replication slave on *.* to username@”x.x.x.x” identified by “xxxx”;

flush privileges;

找到change master to

~# grep "CHANGE MASTER " db1.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000070', MASTER_LOG_POS=998732944;

执行语句

master1节点通道

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx,MASTER_HOST='X.X.X.X',MASTER_PORT=XXX,MASTER_USER='XXX',MASTER_PASSWORD='xxx'FOR CHANNEL 'db1';

master2节点通道

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx,MASTER_HOST='X.X.X.X',MASTER_PORT=XXX,MASTER_USER='XXX',MASTER_PASSWORD='xxx'FOR CHANNEL 'db2';

master3节点通道

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx,MASTER_HOST='X.X.X.X',MASTER_PORT=XXX,MASTER_USER='XXX',MASTER_PASSWORD='xxx'FOR CHANNEL 'db3';

执行slave start

mysql>start slave;

主从是否建

 show slave status\G;

错误处理

错误The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF.

set global   ENFORCE_GTID_CONSISTENCY   = WARN;
set global ENFORCE_GTID_CONSISTENCY = ON;
set global GTID_MODE = OFF_PERMISSIVE;
set global GTID_MODE = ON_PERMISSIVE;
set global GTID_MODE = ON;