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 10 |
vim /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 2
grant replication slave on *.* to 'slave'@'xxx.xxx.xxx.xxx' identified by 'slave'; flush privileges;
-
重启mysql
1
service mysqld restart
-
锁定数据库,将数据打包并拷贝至slave
1
flush tables with read lock;
-
查看master上二进制日志情况,记录
File
与Position
1
show master status;
-
当数据打包拷贝完毕,解除锁定;
1
ublock tables;
当数据较大时,将数据打包备份比使用mysqldump导入时效率更高
slave端配置
-
修改
my.cnf
文件1 2 3 4 5 6 7 8 9 10
vim /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
1
service mysqld restart
-
将从master拷贝来的数据导入slave
-
连接master开始同步
1 2 3 4 5
change 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 3
INSTALL 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 4
vim /etc/my.cnf [mysqld] rpl_semi_sync_master_enabled=1 #启用半同步 rpl_semi_sync_master_timeout=1000 #超时时间为1s
-
重启mysql
1
service mysqld restart
slave端配置
-
安装mysql半同步插件
1 2 3 4
INSTALL 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
文件1
rpl_semi_sync_slave_enabled=1 #启用半同步复制
-
重启mysql
1
service mysqld restart
检查配置
-
master
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
SHOW 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 7
mysql> 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 10 |
vim /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 |
本文出自:http://mayiwei.com/, 原文地址:http://mayiwei.com/2014/02/22/mysql55-master-slave/, 感谢原作者分享。
下一篇: SQLServer分布式事务问题