Mysql 5.5 主主同步

机器A 10.211.55.5
机器B 10.211.55.19

一：主从同步

主结点机器A修改：

1 vi /etc/mysql/my.cnf

#在[mysqld]下加入以下内容：

server-id = 5 #不可重复
log-bin=mysql-bin

binlog-do-db=sync_test
expire_logs_days=7

#保存退出、并重启mysql服务、systemctl restart mysql

2.创建用户：sync,密码为：123,并授权。

grant replication slave, replication client on . to ‘sync’@‘10.211.55.19’ identified by ‘123’;

#刷新用户权限
flush privileges;

3.登陆mysql查看主节点(master)节点状态
mysql -uroot -p123456
mysql> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000004 | 1209 | sync_test | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.00 sec)

从节点机器B修改：

1 vi /etc/mysql/my.cnf
#在[mysqld]下加入以下内容：

server-id = 19 #不可重复
log-bin=mysql-bin

binlog-do-db=sync_test
expire_logs_days=7

2.更改从节点mater配置
change master to master_host=‘10.211.55.5’,master_user=‘sync’, master_password=‘123’, master_port=3306, master_log_file=‘mysql-bin.000004’, master_log_pos=1209, master_connect_retry=30;

3.启动slave同步进程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

4.查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.211.55.19
Master_User: sync
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 480
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 477
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 480
Relay_Log_Space: 634
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
1 row in set (0.00 sec)

二.主主同步(主从是单向，主主双向，即在上面操作的基础上在机器A上开启slave同步进程，同步机器B修改)

机器B修改：

1.查看机器B上master状态

mysql> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000004 | 480 | sync_test | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.00 sec)

2.创建用户：sync,密码为：123,并授权。
grant replication slave, replication client on . to ‘sync’@‘10.211.55.5’ identified by ‘123’;
#刷新用户权限
flush privileges;

机器A修改：

1.更改从节点mater配置
change master to master_host=‘10.211.55.19’,master_user=‘sync’, master_password=‘123’, master_port=3306, master_log_file=‘mysql-bin.000004’, master_log_pos=480, master_connect_retry=30;

2.启动slave同步进程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.211.55.19
Master_User: sync
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 480
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 477
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 480
Relay_Log_Space: 634
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
1 row in set (0.00 sec)