mysql主主半同步配置

主主半同步配置：
备份导出数据库：
mysql> FLUSH TABLE WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000020 | 3010 | | | |
+——————+———-+————–+——————+——————-+

# mysqldump -usystem -ppass -x -B -A –events –master-data=1 -F -S /mysql/3306/mysql.sock | gzip > mysql_rep_bak.sql.gz
master 1:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
Query OK, 0 rows affected (0.04 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+————————————+——-+
4 rows in set (0.01 sec)

# vim /mysql/3306/my.cnf
server_id = 1
log-bin = /mysql/3306/mysql-bin
log-slave-updates = on
binlog_format=mixed
#binlog_format=Statement #binlog格式
#binlog-ignore-db = mysql
#binlog-ignore-db = performance_schema
#binlog-ignore-db = information_schema

auto_increment_increment = 2
auto_increment_offset =1
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 1000
rpl_semi_sync_master_trace_level = 32
rpl_semi_sync_master_wait_no_slave = on

master 2:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
Query OK, 0 rows affected (0.04 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+————————————+——-+
4 rows in set (0.01 sec)

# vim /mysql/3307/my.cnf
server_id = 2
log-bin = /mysql/3307/mysql-bin
log-slave-updates = on

auto_increment_increment = 2
auto_increment_offset =2
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 1000
rpl_semi_sync_master_trace_level = 32
rpl_semi_sync_master_wait_no_slave = on

删除：
# rm -rf /mysql/3307/data/master.info /mysql/3307/data/relay-log.info
# rm -rf /mysql/3306/data/master.info /mysql/3306/data/relay-log.info
导入备份的数据库：
# gunzip < mysql_rep_bak.sql.gz | mysql -usystem -ppass -S /mysql/3307/mysql.sock

现在双主全部锁表保证数据一致性：
mysql> flush table with read lock;

启动主从复制：

master1：
mysql> CHANGE MASTER TO
-> MASTER_HOST=’10.0.0.208′,
-> MASTER_PORT=3307,
-> MASTER_USER=’rep’,
-> MASTER_PASSWORD=’pass’;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
master2：
mysql> CHANGE MASTER TO
-> MASTER_HOST=’10.0.0.208′,
-> MASTER_PORT=3306,
-> MASTER_USER=’rep’,
-> MASTER_PASSWORD=’pass’;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

测试：

master1:
mysql> CREATE TABLE test(
-> id INT(10) NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
-> name char(10) NOT NULL COMMENT ‘姓名’,
-> PRIMARY KEY(ID)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test(name) VALUE(“zhong1”);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test(name) VALUE(“zhong2”);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+—-+——–+
| id | name |
+—-+——–+
| 1 | zhong1 |
| 3 | zhong2 |
+—-+——–+
2 rows in set (0.00 sec)
master2:
mysql> SELECT * FROM test;
+—-+——–+
| id | name |
+—-+——–+
| 1 | zhong1 |
| 3 | zhong2 |
+—-+——–+
2 rows in set (0.00 sec)

mysql> INSERT INTO test(name) VALUE(“zhong3”);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test(name) VALUE(“zhong4”);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+—-+——–+
| id | name |
+—-+——–+
| 1 | zhong1 |
| 3 | zhong2 |
| 4 | zhong3 |
| 6 | zhong4 |
+—-+——–+
4 rows in set (0.01 sec)