01 八 11 mysql-master-master-replication_1

khan.chan {at} virest.org

需要赘言吗？     不需要； 应该看的懂.

更多性能和问题请自己动手，mysql自增奇偶还是会有些问题的，所以正式生产环境需多努力了解，本文只是让其run起来而已.

[关键词:mysql auto_increment_increment auto_increment_offset log-bin ]

master1:

[mysqld]
log-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
sync_binlog    = 1
auto_increment_increment    = 2
auto_increment_offset        = 1

mysql> grant replication slave on *.* to ‘repl’@'%’ identified by ‘access’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+——————-+———-+————–+——————+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| mysqld-bin.000001 |       98 | test         | mysql            |
+——————-+———-+————–+——————+
1 row in set (0.01 sec)

master2:

[mysqld]
log-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
sync_binlog    = 1
auto_increment_increment    = 2
auto_increment_offset        = 2

mysql> change master to master_host=’192.168.3.209′,master_user=’repl’,master_password=’access’,master_log_file=’mysqld-bin.000001′,master_log_pos=98;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.209
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 236
Relay_Master_Log_File: mysqld-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: 98
Relay_Log_Space: 236
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
1 row in set (0.00 sec)

master2:

mysql> grant replication slave on *.* to ‘repl’@'%’ identified by ‘access’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+——————-+———-+————–+——————+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| mysqld-bin.000001 |       98 | test         | mysql            |
+——————-+———-+————–+——————+
1 row in set (0.00 sec)

master1:

mysql> change master to master_host=’192.168.3.210′,master_user=’repl’,master_password=’access’,master_log_file=’mysqld-bin.000001′,master_log_pos=98;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.210
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 236
Relay_Master_Log_File: mysqld-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: 98
Relay_Log_Space: 236
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
1 row in set (0.00 sec)

ERROR:
No query specified

master1:

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table who_repl
-> (id int not null auto_increment primary key,
-> str varchar(255) not null) engine myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into who_repl(str) values
-> (‘master wugui test table’);
Query OK, 1 row affected (0.01 sec)

mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| who_repl       |
+—————-+
1 row in set (0.00 sec)

mysql> select * from who_repl;
+—-+————————-+
| id | str                     |
+—-+————————-+
|  1 | master wugui test table |
+—-+————————-+
1 row in set (0.00 sec)

master2:

查看下Master2主机，who_repl Table表已经过来了.

mysql> use test;
Database changed
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| who_repl       |
+—————-+
1 row in set (0.00 sec)

然后我们在master2主机上新插入条数据：

mysql> insert into who_repl(str) values(‘Master2 say hello’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from who_repl;
+—-+————————-+
| id | str                     |
+—-+————————-+
|  1 | master wugui test table |
|  2 | Master2 say hello       |
+—-+————————-+
2 rows in set (0.00 sec)

回master1看下:

mysql> select * from who_repl;
+—-+————————-+
| id | str                     |
+—-+————————-+
|  1 | master wugui test table |
|  2 | Master2 say hello       |
+—-+————————-+
2 rows in set (0.00 sec)

People who looked at this item also looked at…

• Munin on CentOS/SL Linux and Ubuntu

Related items

• 密码保护：MySQL HP
• MySQL学习