mysql semi sync_mysql5.5主从同步配置(semi_sync)

好久不折腾mysql主从了，发现5.5交之前的版本，配置上有些变化，趁着夜深人静，重温一下，顺便简单记录一下配置过程。 主从同步 master端配置 修改 my.cnf 文件 12345678910 vim /etc/my.cnf[mysqld]server-id = 1log-bin = mysql-binbinlog_format = mixede

好久不折腾mysql主从了，发现5.5交之前的版本，配置上有些变化，趁着夜深人静，重温一下，顺便简单记录一下配置过程。

主从同步

master端配置

修改my.cnf文件

1

2

3

4

5

6

7

8

9

10vim /etc/my.cnf

[mysqld]

server-id = 1

log-bin = mysql-bin

binlog_format = mixed

expire-logs-days = 7

binlog-do-db = luxtarget

binlog-ignore-db = mysql

replicate-do-db = luxtarget

replicate-ignore-db = mysql

新建授权复制用户

1

2grant replication slave on *.* to 'slave'@'xxx.xxx.xxx.xxx' identified by 'slave';

flush privileges;

重启mysql

1service mysqld restart

锁定数据库，将数据打包并拷贝至slave

1flush tables with read lock;

查看master上二进制日志情况，记录File与Position

1show master status;

当数据打包拷贝完毕，解除锁定；

1ublock tables;

当数据较大时，将数据打包备份比使用mysqldump导入时效率更高

slave端配置

修改my.cnf文件

1

2

3

4

5

6

7

8

9

10vim /etc/my.cnf

[mysqld]

server-id = 2

log-bin = mysql-bin

binlog_format = mixed

expire-logs-days = 7

binlog-do-db = luxtarget

binlog-ignore-db = mysql

replicate-do-db = luxtarget

replicate-ignore-db = mysql

重启mysql

1service mysqld restart

将从master拷贝来的数据导入slave

连接master开始同步

1

2

3

4

5change master to master_host='xxx.xxx.xxx.xxx',master_user='slave',master_password='slave',master_log_file='master-bin.xxxxxx,master_log_pos=xxx;

start slave;

show slave status\G;

当Slave_IO_Running与Slave_SQL_Running的状态都为Running时即可。

半同步配置

master端配置

安装mysql半同步插件

1

2

3INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

SET GLOBAL rpl_semi_sync_master_enabled = 1;

SET GLOBAL rpl_semi_sync_master_timeout = 1000;

修改my.cnf文件

1

2

3

4vim /etc/my.cnf

[mysqld]

rpl_semi_sync_master_enabled=1 #启用半同步

rpl_semi_sync_master_timeout=1000 #超时时间为1s

重启mysql

1service mysqld restart

slave端配置

安装mysql半同步插件

1

2

3

4INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

STOP SLAVE IO_THREAD;

START SLAVE IO_THREAD;

修改my.cnf文件

1rpl_semi_sync_slave_enabled=1 #启用半同步复制

重启mysql

1service mysqld restart

检查配置

master

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20SHOW GLOBAL STATUS LIKE 'rpl_semi%';

+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 0 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_status | ON |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 0 |

| Rpl_semi_sync_master_tx_wait_time | 0 |

| Rpl_semi_sync_master_tx_waits | 0 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 0 |

+--------------------------------------------+-------+

14 rows in set (0.00 sec)

slave

1

2

3

4

5

6

7mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | ON |

+----------------------------+-------+

1 row in set (0.01 sec)

好久不折腾mysql主从了，发现5.5交之前的版本，配置上有些变化，趁着夜深人静，重温一下，顺便简单记录一下配置过程。

主从同步

master端配置

修改my.cnf文件

1

2

3

4

5

6

7

8

9

10vim /etc/my.cnf

[mysqld]

server-id = 1

log-bin = mysql-bin

binlog_format = mixed

expire-logs-days = 7

binlog-do-db = luxtarget

binlog-ignore-db = mysql

replicate-do-db = luxtarget

replicate-ignore-db = mysql