mysql主主同步方案

2019/10/17 10:49
阅读数 0

第一台机器

vim /etc/my.cnf

 

 插入内容:

server-id=1
log-bin=mysql-binlog
log-slave-updates=true
max_binlog_size=1024M
auto_increment_offset = 1
auto_increment_increment = 2

replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = mys

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake
init-connect='SET NAMES utf8'
character-set-server=utf8
wait_timeout=1800
interactive_timeout=1800
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

 

systemctl start mariadb    //重启mariadb

mysql -uroot -p000000      //连接mysql

 grant replication slave on *.* to 'repl'@'192.168.96.4' identified by '123456';

flush privileges;    //重新声明

show master status;

 

 第二台机器:

vim /etc/my.cnf

 

 

插入内容;

server-id = 2
log-bin=mysql-binlog
log-slave-updates=true
max_binlog_size=1024M
auto_increment_offset = 2
auto_increment_increment = 2
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = mysql

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake
init-connect='SET NAMES utf8'
character-set-server=utf8
wait_timeout=1800
interactive_timeout=1800
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

 

systemctl start mariadb    //重启mariadb

mysql -uroot -p000000      //连接mysql

 grant replication slave on *.* to 'repl'@'192.168.96.5' identified by '123456';

flush privileges;    //重新声明

show master status;

 

 

特殊参数说明:

log-slave-updates = true     #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启

masterA自增长ID

auto_increment_offset = 1
auto_increment_increment = 2 #奇数ID
masterB自增加ID
auto_increment_offset = 2
auto_increment_increment = 2 #偶数ID

 

 

第一台机器:

stop slave;

change master to master_host='192.168.96.4',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-binlog.000001',master_log_pos=595;

 start slave;

show slave status\G;

 

 

 

第二台机器;

stop slave;

change master to master_host='192.168.96.5',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-binlog.000001',master_log_pos=483;

 start slave;

show slave status\G;

 

 

第一台机器;

create database test01;

第二台机器:

show databases;

第二台机器:

create database test02;

第一台机器:

show databases;

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部