mysql主主同步配置
服务器名 IP 系统 MySQL主机名地址系统MYSQL-SERVER
blog.sjf.com11.1.0.200CentOS-6.85.1.73-7
image.sjf.com11.1.0.19CentOS-6.85.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 replicationslave 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上设置
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上的配置
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 > 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 > 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 > 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 > 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 >start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB > 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 >start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB > 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 > use test;
Database changed
MariaDB > show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb1 |
| tb3 |
| tb4 |
+----------------+
3 rows in set (0.00 sec)
MariaDB > create table tb5(id int);
Query OK, 0 rows affected (0.02 sec)
MariaDB > 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 > show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb2 |
| tb5 |
+----------------+
2 rows in set (0.00 sec)
我们这里创建表6，看下效果
MariaDB > create table tb6(id int);
Query OK, 0 rows affected (0.01 sec)
blog上。
MariaDB > show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb1 |
| tb3 |
| tb5 |
| tb6 |
+----------------+
4 rows in set (0.00 sec)
从上面的示例可以看出我们这个双主模型就这么成功了，这里的设定主服务器的那个配置是关键，记得每次打开服务两边都要start slave这样才能称为双主模型。当然双主模型也不是没有弊端，下次给大家介绍吧。
页:
[1]