mysql之主主复制

主主复制

主服务器一配置

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=/data/logbin/mysql-bin
auto_increment_offset=1  
#开始点
auto_increment_increment=2  
#增长幅度


systemctl start mariadb

mysqldump -A --single-transaction --master-data=1 > /data/all.sql
scp /data/all.sql 192.168.160.129:/data/

主服务器二配置

vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=/data/logbin/mysql-bin
auto_increment_offset=2
#开始点
auto_increment_increment=2  
#增长幅度


systemctl start mariadb

vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.160.136',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=245;


mysql </data/all.sql

start slave;
flush privileges;  --刷新用户权限，不执行这个可能会出现错误1130

半同步

主服务器配置

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=/data/logbin/mysql-bin

systemctl start mariadb

mysql
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  --安装主服务器插件来实现半同步
SHOW GLOBAL VARIABLES LIKE '%semi%';  --查看半同步的状态
SET GLOBAL rpl_semi_sync_master_enabled=1;  --开启半同步状态

从服务器一

vim /etc/my.cnf
[mysqld]
server-id=2
read-only

systemctl start mariadb

CHANGE MASTER TO
MASTER_HOST='192.168.160.136',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=245;

start slave;

 INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  --安装从服务器的插件
 SET GLOBAL rpl_semi_sync_slave_enabled=1;  --开启插件
 stop slave;
 start slave;  --重启线程

从服务器二

vim /etc/my.cnf
[mysqld]
server-id=2
read-only

systemctl start mariadb

CHANGE MASTER TO
MASTER_HOST='192.168.160.136',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=245;

start slave;

 INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  --安装从服务器的插件
 SET GLOBAL rpl_semi_sync_slave_enabled=1;  --开启插件
 stop slave;
 start slave;