java mysql 双向同步_二十三 mysql 双向同步配置

环境：

两台主机地址分别为：

ServA：172.16.42.33

ServB：172.16.42.34

1.配置同步账号

A端：mysql>GRANT all privileges ON *.* TO tongbu@'172.16.42.34' IDENTIFIED BY '123456';

mysql>flush privileges;

mysql>change master to master_host='172.16.42.34',master_user='tongbu',master_password='123456';

B端：mysql>GRANT all privileges ON *.* TO tongbu@'172.16.42.33' IDENTIFIED BY '123456';

mysql>flush privileges;

mysql>change master to master_host='172.16.42.33',master_user='tongbu',master_password='123456';

2 配置数据库参数

1)、以root用户登录ServA，修改ServA的my.cnf文件

vi /etc/my.cnf

7 [mysqld]

#add by gaojx for mysql tongbu 2013-11-1

33 explicit_defaults_for_timestamp = true

34 character_set_server=utf8

35 log_bin = MySQL-bin

36 relay_log = relay-bin

37 relay_log_index = relay-bin-index

38 server_id = 1

44 binlog_do_db = svn      #需要同步的库

45 replicate_do_db = svn #需要同步的库

49 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

2)、以root用户登录ServB，修改ServB的my.cnf文件

vi /etc/my.cnf

7 [mysqld]

#add by gaojx for mysql tongbu 2013-11-1

31 explicit_defaults_for_timestamp = true

32 character_set_server=utf8

33 log_bin = MySQL-bin

34 relay_log = relay-bin

35 relay_log_index = relay-bin-index

36 server_id = 2

37 binlog_do_db = svn      #需要同步的库

38 replicate_do_db = svn #需要同步的库

40 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

注意：如果是复制的mysql，请注意修改mysql的UUID。

3 状态查看

mysql> show master status;

mysql> show slave status\G;

下列2个参数都为YES时，同步生效：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes