主从同步

master端配置

1. 修改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. 新建授权复制用户

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

2. 重启mysql

   1	service mysqld restart

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

   1	flush tables with read lock;

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

   1	show master status;

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

   1	ublock tables;

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

slave端配置

1. 修改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

2. 重启mysql

   1	service mysqld restart

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

4. 连接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端配置

1. 安装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;

2. 修改my.cnf文件

   1 2 3 4

   vim /etc/my.cnf [mysqld] rpl_semi_sync_master_enabled=1 #启用半同步 rpl_semi_sync_master_timeout=1000 #超时时间为1s

3. 重启mysql

   1	service mysqld restart

slave端配置

1. 安装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;

2. 修改my.cnf文件

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

3. 重启mysql

   1	service mysqld restart

检查配置

1. 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)

2. 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端配置

1. 修改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/, 感谢原作者分享。