mysql 5.5 同步_MySQL5.5主主同步配置

一、准备工作：

两台服务器：A: 10.21.239.240

B: 10.21.239.222

MySQL数据库5.5

二、在两台MySQL上创建用户，设置权限

A：mysql> grant replication slave, replication client, reload, super on *.* to 'sync_user'@'10.21.239.222' identified by '123456' with grant option;//用户B访问

B：mysql> grant replication slave, replication client, reload, super on *.* to 'sync_user'@'10.21.239.240' identified by '123456' with grant option;//用户A访问

三、在my.ini上进行相关配置

在[mysqld]下方增加：

A：

server-id=1

replicate-do-db=testsync

replicate-ignore-db=mysql

log-bin=mysql-bin

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

B：

server-id=2

replicate-do-db=testsync

replicate-ignore-db=mysql

log-bin=mysql-bin

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

保存后，重启MySQL

四、在MySQL Shell上进行相关配置

1、在A上mysql shel 中执行 show master status;

mysql> show master status;

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

| File                           | Position   | Binlog_Do_DB  | Binlog_Ignore_DB |

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

| mysql-bin.000002 |      107      |                           |                                |

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

1 row in set (0.00 sec)

记录下mysql-bin.000002和107

2、在B上执行：stop slave;//停止同步

然后执行：

mysql> change master to master_host='10.21.239.240', master_port=3306, master_user='sync_user', master_password='123456', master_log_file='mysql-bin.000002',master_log_pos=107;

然后执行：

start slave;//开始同步

最后执行：show slave status\G;如果显示以下信息，表示同步设置成功。

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.21.239.240

Master_User: sync_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 107

Relay_Log_File: 7d44cba7defa42f-relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: mydb

Replicate_Ignore_DB: mysql

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: 107

Relay_Log_Space: 419

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: 1

1 row in set (0.02 sec)

ERROR:

No query specified

3、上一步没有问题，则在B上继续执行show master status;

mysql> show master status;

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

| File                           | Position   | Binlog_Do_DB  | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      107      |                           |                                |

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

1 row in set (0.00 sec)

记录下mysql-bin.000001和107

4、在A上执行：stop slave;//停止同步

然后执行：

mysql> change master to master_host='10.21.239.222', master_port=3306, master_user='sync_user', master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=107;

然后执行：

start slave;//开始同步

最后执行：show slave status\G;如果显示以下信息，表示同步设置成功。

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.21.239.222

Master_User: sync_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 590

Relay_Log_File: Dean-relay-bin.000003

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: testsync

Replicate_Ignore_DB: mysql

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: 590

Relay_Log_Space: 1037

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: 2

1 row in set (0.00 sec)

ERROR:

No query specified