mysql主从搭建双主多源主从复制

一资源

node1    10.22.18.100

node2    10.22.18.101

node3    10.22.18.103

node4    10.22.18.104

二 安装Mysql【4台vm】

1安装mysql

• wget dev.mysql.com/get/mysql57…
• yum localinstall mysql57-community-release-el7-8.noarch.rpm
• yum module disable mysql
• yum install mysql-community-server

2 开机启动mysql

• systemctl enable mysqld
• systemctl start mysqld
• systemctl status mysqld

3 查看临时密码

• grep password /var/log/mysqld.log

4 登录mysql，修改密码。

• ALTER USER 'root'@'localhost' IDENTIFIED BY '****'

5 创建账号

• grant all privileges on . to root@'%' identified by '****';

6 配置my.cnf

1 指定datafile/log 等物理路径
2 扩充磁盘

[client]
loose-default-character-set=utf8
socket=/data/mysql/mysql.sock

[mysqld]
#########################common setting#########################
#datadir=/var/lib/mysql
datadir=/data/mysql/datafile
tmpdir=/data/mysql/tmp
#user=mysql
#symbolic-links=0
socket=/data/mysql/mysql.sock
default_storage_engine = InnoDB
port = 3306
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8mb4
collation_server =utf8mb4_bin
log-bin-trust-function-creators=1
#innodb_large_prefix = on
autocommit=1
explicit_defaults_for_timestamp=0
lower_case_table_names =1
event_scheduler =ON
innodb_data_file_path=ibdata1:76M;ibdata2:2G:autoextend
#########################security setting#########################
local_infile = 0
secure_auth = 1
max_allowed_packet = 128M
max_connect_errors=10
wait_timeout =600
interactive_timeout =600
sql_mode ='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
skip-name-resolve
sysdate_is_now = 1
default_time_zone = '+08:00'
local-infile=0
#########################cache setting#########################
max_prepared_stmt_count = 1048576
thread_cache_size = 500
table_definition_cache = 4096
table_open_cache = 2000
table_open_cache_instances = 16
#open_file_limit=10240
back_log =3000
query_cache_type = 0
query_cache_size = 32M
max_connections = 2000
max_user_connections = 2000
key_buffer_size=2M
thread_stack=256k
sort_buffer_size=4M
read_buffer_size=4M
join_buffer_size=4M
read_rnd_buffer_size=4M
net_buffer_length=16k
#########################innodb setting#########################
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1
innodb_lock_wait_timeout = 60
innodb_io_capacity_max = 2000
innodb_io_capacity = 1000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
join_buffer_size=2M
read_buffer_size=1M
sort_buffer_size=4M
read_rnd_buffer_size=4M
net_buffer_length=16k
tmp_table_size = 128M
max_heap_table_size = 256M
innodb_buffer_pool_chunk_size = 256M
innodb_buffer_pool_instances = 8
innodb_thread_concurrency = 4
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
#skip-grant-tables
transaction_isolation=READ-COMMITTED
innodb_adaptive_flushing=1
innodb_open_files=65536
#########################replication setting#########################
server_id=1 #确保server_id 不相同，一般使用IP后两段
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
binlog_cache_size=16M
log-bin=/data/mysql/binlog/mysql-bin
log-bin-index=/data/mysql/binlog/mysql-bin.index
relay-log=/data/mysql/relay/mysql-relay
relay-log-index=/data/mysql/relay/mysql-relay.index
skip-slave-start
slave_net_timeout=30
max_binlog_size=512M
#slave-skip-errors
relay_log_info_repository=TABLE
master_info_repository=TABLE
relay_log_recovery=on
log_bin_trust_function_creators=1
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
#max_binlog_cache_size=102400
######################### master + master #########################
auto_increment_offset=1 ##第几台机器就写几
auto_increment_increment=4 ##一共几台机器就写几
#innodb_support_xa=FALSE

#######################log setting##################################
slow_query_log=ON
slow_query_log_file=/data/mysql/slowlog/slow.log
log_queries_not_using_indexes=1
log_slow_admin_statements=1
log_slow_slave_statements=1
log_throttle_queries_not_using_indexes=50
expire_logs_days=30
long_query_time=10
binlog_format=ROW
log-error=/data/mysqllog/mysqld.log
general_log=on
general_log_file=/data/mysqllog/mysqld.log
innodb_log_buffer_size=256M
innodb_log_file_size=1G
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=75

7 修改my.cnf和mysql物理路径的权限

• chown -R mysql:mysql my.cnf
• chown -R mysql:mysql /data

8 重启数据库

• systemctl stop mysqld
• systemctl start mysqld
• systemctl status mysqld

三 导入业务数据

1 node1恢复业务数据

• xftp或SCP 导入dump文件
• source /tmp/first.sql

2 node1：node2 恢复业务原始数据

四双主双从主从复制

1 双主主从复制 master1↔master2

现象，可以通过pos，gtid查找。这里只简单check

2 master1 和 master2 双主库 全局锁

3 从库多源复制

① master1备份数据文件dump

• mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs --all-databases >mysql.sql

②slave1 和slave2 还原

• scp 2020-06-06-23-mysql.sql root@'IP':/tmp
• source /tmp/mysql.sql

③ slave1和slave2 修改my.cnf，注意server-id一定不同

server_id=4 #slave1和slave2不同
gtid_mode=on
enforce_gtid_consistency=1
auto_increment_offset=4 #slave1和slave2不同
auto_increment_increment=4 #大于机器数量
relay_log_info_repository=TABLE
master_info_repository=TABLE

④部署slave1和slave2 ———— CHANGE MASTER TO

salve1	slave2
change master to master_host='10.22.18.100', master_user='repl', master_password='******', MASTER_LOG_FILE='mysql-bin.000022', MASTER_LOG_POS=234 FOR CHANNEL 'Master_1'	change master to master_host='10.22.18.100', master_user='repl', master_password='******', MASTER_LOG_FILE='mysql-bin.000022', MASTER_LOG_POS=234 FOR CHANNEL 'Master_1'
change master to master_host='10.22.18.101', master_user='repl', master_password='******', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=234 FOR CHANNEL 'Master_2'	change master to master_host='10.22.18.101', master_user='repl', master_password='******', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=234 FOR CHANNEL 'Master_2'

⑤master1写，同步master2 ，同步slave1，slave2

⑥master2写，同步master1，同步slave1，slave2