mysql主主同步配置_mysql主主同步配置

MySQL 主主同步配置

服务器名    IP    系统    MySQL

主机名

地址

系统

MYSQL-SERVER

blog.sjf.com

11.1.0.200

CentOS-6.8

5.1.73-7

image.sjf.com

11.1.0.19

CentOS-6.8

5.1.73-7

一. 假设要同步的库是 test

我们这里先创建同步账号sjf

交叉授权

blog.sjf.com主机上

grant replication   slave on *.* to 'sjf'@'11.1.0.204'  identified by 'sjf';

flush privileges;

image.sjf.com主机上

grant replication  slave on *.* to 'sjf'@'11.1.0.203'   identified by 'sjf';

flush privileges;

二. 修改配置文件/etc/my.cnf

blog上设置

[mysqld]

innodb_file_per_table=ON

skip_name_resolve=ON

user = mysql

log-bin=mysql-bin

server-id = 1

binlog-do-db=test

binlog-ignore-db=mysql

replicate-do-db=test

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

skip-name-resolve

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

image.sjf.com上的配置

[mysqld]

innodb_file_per_table=ON

skip_name_resolve =ON

user = mysql

log-bin=mysql-bin

server-id = 2

binlog-do-db=test

binlog-ignore-db=mysql

replicate-do-db=test

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

skip-name-resolve

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

三、分别重启服务器blog image 两台主机上的mysql服务

(1) 分别在服务器blog、image 上查看做为主服务器状态

blog：

MariaDB [(none)]> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show master status\G;

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

File: mysql-bin.000001

Position: 435

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

image：

MariaDB [(none)]> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show master status\G;

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

File: mysql-bin.000001

Position: 340

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

(2)分别在服务器blog、image上用change master语句指定同步位置 :

blog

MariaDB [test]> change master to master_host='11.1.0.19', master_user='sjf', master_password='sjf',master_log_file='mysql-bin.000001',master_log_pos=340;

Query OK, 0 rows affected (0.01 sec)

image

MariaDB [test]> change master to master_host='11.1.0.200', master_user='sjf', master_password='sjf',master_log_file='mysql-bin.000001',master_log_pos=435;

注：master_log_file，master_log_pos由上面主服务器查出的状态值中确定 master_log_file对    应  File，master_log_pos对应Position

四、分别先取消读锁，启用从服务器模式

blog上：

MariaDB [test]>  start slave;

Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 11.1.0.19

Master_User: sjf

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 340

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

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

Relay_Log_Space: 825

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)

image上：

MariaDB [test]>  start slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 11.1.0.200

Master_User: sjf

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 435

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

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

Relay_Log_Space: 825

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.00 sec)

主要关注以下 2 个参数：

...

...

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

...

五、测试

blog上：

MariaDB [test]> use test;

Database changed

MariaDB [test]> show tables;

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

| Tables_in_test |

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

| tb1            |

| tb3            |

| tb4            |

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

3 rows in set (0.00 sec)

MariaDB [test]> create table tb5(id int);

Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> show tables;

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

| Tables_in_test |

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

| tb1            |

| tb3            |

| tb4            |

| tb5            |

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

images上：

MariaDB [(none)]> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

当blog生成表5后

MariaDB [test]> show tables;

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

| Tables_in_test |

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

| tb2            |

| tb5            |

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

2 rows in set (0.00 sec)

我们这里创建表6，看下效果

MariaDB [test]> create table tb6(id int);

Query OK, 0 rows affected (0.01 sec)

blog上。

MariaDB [test]> show tables;

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

| Tables_in_test |

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

| tb1            |

| tb3            |

| tb5            |

| tb6            |

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

4 rows in set (0.00 sec)

从上面的示例可以看出我们这个双主模型就这么成功了，这里的设定主服务器的那个配置是关键，记得每次打开服务两边都要start slave这样才能称为双主模型。当然双主模型也不是没有弊端，下次给大家介绍吧。