|
1
2
|
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
|
1
2
3
4
5
6
7
8
9
|
我们这里先创建同步账号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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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 上查看做为主服务器状态
|
1
2
3
4
5
6
7
8
9
10
11
|
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)
|
|
1
2
3
4
5
6
7
8
9
10
11
|
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语句指定同步位置 :
|
1
2
3
|
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)
|
|
1
2
|
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
四、分别先取消读锁，启用从服务器模式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
|
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
...
...
五、测试
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
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这样才能称为双主模型。当然双主模型也不是没有弊端，下次给大家介绍吧。
本文转自 sjfbjs 51CTO博客，原文链接:http://blog.51cto.com/11886896/1873997
