mysql多源复制 知乎_MySQL多主一从（多源复制）同步配置

>多主一从，也称为多源复制，数据流向。这是小编的服务器部署的一次小尝试，实际工作中还没遇到过

形式

主库1 -> 从库s

主库2 -> 从库s

主库n -> 从库s

应用场景

数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。

读写分离，从库只用于查询，提高数据库整体性能。

部署环境

注：使用docker部署mysql实例，方便快速搭建演示环境。但本文重点是讲解主从配置，因此简略描述docker环境构建mysql容器实例。(亦或者可以使用一键安装环境的插件，在这里不做详说)若不熟悉，可使用传统方式安装mysql，效果相同。

数据库：MySQL 5.7.x (相比5.5，5.6而言，5.7同步性能更好，支持多源复制，可实现多主一从，主从库版本应保证一致)

操作系统：CentOS 7.x

容器：Docker 17.09.0-ce

镜像：mysql:5.7

主库300：IP=192.168.1.218; PORT=3306; server-id=2; database=test3; table=user

主库200：IP=192.168.1.225; PORT=3306; server-id=1; database=test4; table=user

从库：IP=192.168.1.128; PORT=3306; server-id=3; database=test3,test4; table=user

配置约束

主从库必须保证网络畅通可访问

主库必须开启binlog日志

主从库的server-id必须不同

【主库300】操作及配置

配置my.cnf

[client]

#password = your_passwordport = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306 #默认端口socket = /tmp/mysql.sock

datadir = /usr/local/mysql/var

skip-external-locking

key_buffer_size = 32M

max_allowed_packet = 1M

table_open_cache = 128

sort_buffer_size = 768K

net_buffer_length = 8K

read_buffer_size = 768K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

thread_cache_size = 16

query_cache_size = 16M

tmp_table_size = 32M

performance_schema_max_table_instances = 1000

skip-grant-tables

explicit_defaults_for_timestamp = true

#skip-networkingmax_connections = 500

max_connect_errors = 100

open_files_limit = 65535

log-bin=mysql-bin #开启二进制日志记录binlog_format=mixed

server-id = 3 #配置唯一idexpire_logs_days = 10 #日志过期时间(天)early-plugin-load = ""

#不给从机同步的库binlog-ignore-db=sys

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

default_storage_engine = InnoDB

innodb_file_per_table = 1

innodb_data_home_dir = /usr/local/mysql/var

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /usr/local/mysql/var

innodb_buffer_pool_size = 128M

innodb_log_file_size = 32M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

创建授权用户

mysql重启，连接mysql主数据库，键入命令mysql -u root -p，输入密码后登录数据库。创建用户用于从库同步复制，授予复制、同步访问的权限；

mysql> CREATE USER '用户名'@'%' IDENTIFIED BY '密码';#创建用户mysql> GRANT REPLICATION SLAVE ON *.* TO '用户名'@'%';#分配权限mysql>flush privileges; #刷新权限

查看log_bin是否开启

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set (0.00 sec)

查看master状态，记录二进制文件名(mysql-bin.000006)和位置(916)

mysql> show master status;

+------------------+----------+--------------+-------------------------------------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+-------------------------------------------------+-------------------+

| mysql-bin.000006 | 916 | | sys,mysql,information_schema,performance_schema | |

+------------------+----------+--------------+-------------------------------------------------+-------------------+

1 row in set (0.00 sec)

【主库400】配置及操作

配置my.cnf

[client]

#password = your_passwordport = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

datadir = /usr/local/mysql/var

skip-external-locking

key_buffer_size = 32M

max_allowed_packet = 1M

table_open_cache = 128

sort_buffer_size = 768K

net_buffer_length = 8K

read_buffer_size = 768K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

thread_cache_size = 16

query_cache_size = 16M

tmp_table_size = 32M

performance_schema_max_table_instances = 1000

explicit_defaults_for_timestamp = true

#skip-networkingmax_connections = 500

max_connect_errors = 100

open_files_limit = 65535

log-bin=mysql-bin #开启二进制日志binlog_format=mixed

server-id = 1 #唯一idexpire_logs_days = 10 #过期日期(天)early-plugin-load = ""

#不给从机同步的库binlog-ignore-db=sys

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

default_storage_engine = InnoDB

innodb_file_per_table = 1

innodb_data_home_dir = /usr/local/mysql/var

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /usr/local/mysql/var

innodb_buffer_pool_size = 128M

innodb_log_file_size = 32M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump]

quick

"/etc/my.cnf" 69L, 1430C

创建授权用户

mysql重启，连接mysql主数据库，键入命令mysql -u root -p，输入密码后登录数据库。创建用户用于从库同步复制，授予复制、同步访问的权限；

mysql> CREATE USER '用户名'@'%' IDENTIFIED BY '密码';#创建用户mysql> GRANT REPLICATION SLAVE ON *.* TO '用户名'@'%';#分配权限mysql>flush privileges; #刷新权限

注：各个主机创建的用户可以相同的。

查看log_bin是否开启

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set (0.00 sec)

查看master状态，记录二进制文件名(mysql-bin.000008)和位置(892)

mysql> show master status;

+------------------+----------+--------------+-------------------------------------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+-------------------------------------------------+-------------------+

| mysql-bin.000008 | 892 | | sys,mysql,information_schema,performance_schema | |

+------------------+----------+--------------+-------------------------------------------------+-------------------+

1 row in set (0.00 sec)

【从库】配置及操作

配置my.cnf

[client]

#password = your_passwordport = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

datadir = /usr/local/mysql/var

skip-external-locking

key_buffer_size = 32M

max_allowed_packet = 1M

table_open_cache = 128

sort_buffer_size = 768K

net_buffer_length = 8K

read_buffer_size = 768K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

thread_cache_size = 16

query_cache_size = 16M

tmp_table_size = 32M

performance_schema_max_table_instances = 1000

explicit_defaults_for_timestamp = true

#skip-networkingmax_connections = 500

max_connect_errors = 100

open_files_limit = 65535

#log-bin=mysql-bin #关闭二进制日志binlog_format=mixed

server-id = 2 #配置唯一idexpire_logs_days = 10 #过期时间(天)early-plugin-load = ""

#加上以下参数避免更新不及时，slave 重启后导致的主从复制出错read_only = 1

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=1 #从机禁止写操作

default_storage_engine = InnoDB

innodb_file_per_table = 1

innodb_data_home_dir = /usr/local/mysql/var

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /usr/local/mysql/var

innodb_buffer_pool_size = 128M

innodb_log_file_size = 32M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump]

quick

"/etc/my.cnf" 69L, 1494C

重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

php mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.1.218',

-> MASTER_USER='用户名',

-> MASTER_PASSWORD='密码',

-> MASTER_LOG_FILE='mysql-bin.000006',

-> MASTER_LOG_POS=916

-> for channel '300';

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.1.225',

-> MASTER_USER='用户名',

-> MASTER_PASSWORD='密码',

-> MASTER_LOG_FILE='mysql-bin.000008',

-> MASTER_LOG_POS=892

-> for channel '200';

关键点解说

stop slave; //停止同步

start slave; //开始同步

//必须和【主库】的信息匹配。

CHANGE MASTER TO MASTER_HOST='192.168.10.212', //主库IP

MASTER_PORT=4300, //主库端口

MASTER_USER='slave', //访问主库且有同步复制权限的用户

MASTER_PASSWORD='123456', //登录密码//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果

MASTER_LOG_FILE='mysql-bin.000003',//【关键处】从文件中指定位置开始读取，主库show master status返回结果

MASTER_LOG_POS=438

for channel '300'; //定义通道名称

启动slave同步进程

mysql>start slave;

查看同步状态

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.225

Master_User: root2

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 892

Relay_Log_File: localhost-relay-bin-200.000061

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000008

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: 892

Relay_Log_Space: 701

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: 6c83a613-5cfe-11e9-92a0-000c29804965

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:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name: 200

Master_TLS_Version:

*************************** 2. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.218

Master_User: root3

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 916

Relay_Log_File: localhost-relay-bin-300.000065

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000006

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: 916

Relay_Log_Space: 701

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: 3

Master_UUID: 164794cb-7557-11e9-bb7c-000c29b01621

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:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name: 300

Master_TLS_Version:

2 rows in set (0.00 sec)

ERROR:

No query specified

可以看见设置两个的主从同步通道的所有状态信息。只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes，则同步是正常的。 如果是No或者Connecting都不行，一般出错都是【MASTER_LOG_FILE】和【MASTER_LOG_POS】不一致问题，需要细细核查才行。

配置完成，则【从库】开始自动同步。若需要单独启动或停止某个同步通道，可使用如下命令：

start slave for channel '300'; //启动名称为300的同步通道stop slave for channel '300'; //停止名称为300的同步通道

同步功能可自行验证(亲测有效)

参考

MySQL5.7多主一从(多源复制)同步配置

MySQL主从复制(Master-Slave)实践​​​​​​​