Mysql之多源复制

1.　　什么是多源复制

　　　　mysql5.7开始支持多主一从的复制方式也就是多源复制

2.　　多源复制的应用场景

　　　　1.　　各个主数据库的数据汇总到统一的数据库中

　　　　2.　　在从数据库对各个主数据库的数据进行集中备份

3.　　多源复制的必要条件

　　　　1.　　开启多源复制需要在从库配置文件设置两个参数　　　　　　　　

　　　　　　　　master_info_repository = TABLE
　　　　　　　　relay_log_info_repository = TABLE

　　　　　　　　也可以动态设置这两个参数

　　　　　　　　　　SET GLOBAL master_info_repository = 'TABLE';

　　　　　　　　　　SET GLOBAL relay_log_info_repository = 'TABLE';　　　　　　　　　　

4.　　配置多源复制

　　　　1.　　环境准备

　　　　　　　　master1　　192.168.1.200

　　　　　　　　master2　　192.168.1.221

　　　　　　　　slave1　　192.168.1.189

　　　　2.　　三台机器安装mysql

　　　　　　　　省略

　　　　3.　　配置文件

　　　　　　　　1.　　master1

[client]
port  = 3306
socket = /server/mysql/tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
datadir = /server/mysql/var/data
pid-file = /server/mysql/var/db.pid
log-error = /server/mysql/log/error.log
socket = /server/mysql/tmp/mysql.sock
user = mysql
tmpdir = /tmp
log_timestamps = SYSTEM
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
character-set-server = utf8

skip-external-locking
skip-name-resolve
key_buffer_size = 512M
max_allowed_packet = 512M
table_open_cache = 1024
sort_buffer_size = 512M
binlog_cache_size = 64M
net_buffer_length = 8K
read_buffer_size = 128M
read_rnd_buffer_size = 128M
tmp_table_size = 512M
join_buffer_size = 64M
max_heap_table_size = 128M
bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 512M
max_connections         = 3000
max_user_connections    = 800
max_connect_errors      = 10000

#SSD config
innodb_io_capacity      = 5000
#innodb_support_xa       = 1
innodb_read_io_threads  = 24
innodb_write_io_threads = 24
#innodb_rollback_on_timeout
innodb_buffer_pool_instances = 8

query_cache_size        = 0
"/etc/my.cnf" 115L, 3221C
#replicate-wild-ignore-table  = mysql.%
#replicate-wild-ignore-table  = performance_schema.%

#Innodb
innodb_data_home_dir = /server/mysql/var/data
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /server/mysql/log
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 50
innodb_buffer_pool_size = 64G
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT

#config semi_sync
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

myisam_max_sort_file_size    = 5G
myisam_repair_threads        = 1
default-storage-engine       = InnoDB
max_binlog_cache_size = 10G
expire_logs_days      = 90
group_concat_max_len    = 10240
transaction_isolation   = REPEATABLE-READ
skip-symbolic-links
skip-slave-start
back_log                = 200
open_files_limit        = 65535
thread_cache_size       = 128
#thread_concurrency      = 96
local_infile            = 1
innodb_max_dirty_pages_pct = 50

slave-parallel-type       = LOGICAL_CLOCK
slave-parallel-workers    = 16
#binlog-checksum          = CRC32
#binlog-checksum           = NONE
master_info_repository    = TABLE
relay_log_info_repository = TABLE
relay_log_recovery        = ON
log_slave_updates         = ON

　　　　　　　　2.　　master2

　　　　　　　　　　　　同master1一样，除了server-id

　　　　　　　　3.　　slave1　　

[client]
port = 3306
socket = /server/mysql/tmp/mysql.sock
default-character-set = utf8

[mysqld]
#basedir = /server/mysql/
datadir = /server/mysql/var/data/
port    = 3306
socket  = /server/mysql/tmp/mysql.sock
pid-file = /server/mysql/var/db.pid
log-error = /server/mysql/log/error.log
log_timestamps       = SYSTEM
character-set-server = utf8
default-storage-engine   = InnoDB
max_allowed_packet       = 512M
max_connections          = 3000
max_connect_errors       = 10000
connect-timeout          = 10
wait-timeout             = 28800
interactive-timeout      = 28800

# slow query log
slow_query_log           = 1
long-query-time          = 2
slow_query_log_file      = /server/mysql/log/slow.log

#不同步的数据库
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%

#添加以下同步数据库：
replicate_wild_do_table=product.%

server_id = 242
read_only = 1
log-bin                  = /server/mysql/log/mysql-bin
log-bin-index            = /server/mysql/log/mysql-bin.index
relay_log                = /server/mysql/log/relay-log
relay_log_index          = /server/mysql/log/relay-log.index
binlog_format = row
transaction_isolation    = REPEATABLE-READ
log_slave_updates = on
sql-mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
auto_increment_increment = 1


#Innodb
innodb_file_per_table    = 1
innodb_buffer_pool_size         = 8G
innodb_data_home_dir            = /server/mysql/var/data/
innodb_data_file_path           = ibdata1:512M:autoextend
innodb_thread_concurrency       = 8
innodb_flush_log_at_trx_commit  = 1

innodb_log_buffer_size          = 256M
innodb_log_file_size            = 512M
innodb_log_files_in_group       = 2
innodb_log_group_home_dir       = /server/mysql/var/data/
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 50
innodb_io_capacity   = 2000
innodb_support_xa    = 1

binlog_cache_size    = 64M
expire_logs_days     = 10
group_concat_max_len = 10240
tmp_table_size       = 128M
max_heap_table_size  = 128M
sort_buffer_size     = 128M
net_buffer_length    = 8K
read_buffer_size     = 128M
read_rnd_buffer_size = 128M

#Enhanced Multi-Threaded Slave
slave-parallel-type       = LOGICAL_CLOCK
slave-parallel-workers    = 16
binlog-checksum           = CRC32
master_info_repository    = TABLE
relay_log_info_repository = TABLE
relay_log_recovery        = ON

　　　　4.　　重启三台服务器的mysql服务

　　　　5.　　在两台主服务器上授权

　　　　　　　　grant replication slave on *.*  to 'repluser'@'192.168.1.%' identified by 'replpass';

　　　　　　　　flush privileges;

　　　　6.　　在从库执行sql　　　　　　　

CHANGE MASTER TO MASTER_HOST='192.168.1.200',
MASTER_USER='repluser',
MASTER_PORT=3306,
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1 FOR CHANNEL 'master1';
 
CHANGE MASTER TO MASTER_HOST='192.168.1.221',
MASTER_USER='repluser',
MASTER_PORT=3306,
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1 FOR CHANNEL 'master2';

　　　　　　　　start slave;

　　　　6.　　查看从库状态

　　　　　　　　show slave status\G;

　　　　　　　　show slave status for CHANNEL 'master1' \G;


　　　　7.　　注意事项

　　　　　　　　1.　　需要考虑各 master 数据增长频率，slave 的数据增长频率是这些数据的总和。如果太高，会导致大量的磁盘IO，造成数据更新延迟，最严重的是会影响正常的查询。

　　　　　　　　2.　　如果多个主数据库实例中存在同名的库，则同名库的表都会放到一个库中；

　　　　　　　　3.　　如果同名库中的表名相同且结构相同，则数据会到一起；如果结构不同，则先建的有效。