One may say that there are a lot of MySQL replication manuals, but latest versions of MySQL server have changed the way how configuration should be
applied. Most of the manuals do not reflect these changes. I want to describe some other aspects of configurations also. As far as there are a lot of good manuals about replication, I think there is no need to dove into details what is the replication. Just
want to mention that this technique is usually used for load balancing on database servers. If you have a lot of read requests (most common for web applications) master-slave replication should suit your needs well. In this case you will do write transactions
on master host and read requests on slave hosts, because data is populated from master to slave much faster than from slaves to master and to other slaves.
But sometimes you might have more write requests or may have other (application
related) reasons to start another type of replication. You can see it on the next fugure and that is so called master-master replication.
In this article I will describe simple master-slave architecture with 2 hosts and
simple master-master replication with the same 2 hosts. Our final goal is to configure master-master replication, what includes several sub-steps, so lets start. Sure you should configure network services on both systems. For example:
Master 1/Slave 2 ip: 192.168.1.201
Master 2/Slave 1 ip: 192.168.1.202
create user and authorization
master1
[root@db01 mysql]# /service/mysql/bin/mysql -uroot -pidontcare Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.25-log db01 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.1.202' IDENTIFIED BY 'rep'; Query OK, 0 rows affected (0.00 sec)
master2
[root@db02 ~]# /service/mysql/bin/mysql -uroot -pidontcare Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.25-log db02 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.1.201' IDENTIFIED BY 'rep'; Query OK, 0 rows affected (0.00 sec)
configuration files to add
auto-increment-increment value should be set to the whole structure of the total number of servers.
master1
log-bin=binlog-master01 binlog_format=mixed server-id = 1 auto-increment-increment = 2 auto-increment-offset = 1
master2
log-bin=binlog-master02 binlog_format=mixed server-id = 3 auto-increment-increment = 2 auto-increment-offset = 2
set master to master relationship
check master2 status
mysql> show master status; +------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ | binlog-master02.000002 | 262 | | | +------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
master1
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.202',MASTER_USER='rep',MASTER_PASSWORD='rep',MASTER_PORT=30307,MASTER_LOG_FILE='binlog-master02.000002', MASTER_LOG_POS=262,MASTER_CONNECT_RETRY=10; Query OK, 0 rows affected (0.03 sec)
check master1 status
mysql> show master status; +------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ | binlog-master01.000002 | 262 | | | +------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
master2
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.201',MASTER_USER='rep',MASTER_PASSWORD='rep',MASTER_PORT=30307,MASTER_LOG_FILE='binlog-master01.000002', MASTER_LOG_POS=262,MASTER_CONNECT_RETRY=10; Query OK, 0 rows affected (0.03 sec)
startup master1,master2 slave process
master2
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.1.201
Master_User: rep
Master_Port: 30307
Connect_Retry: 10
Master_Log_File: binlog-master01.000002
Read_Master_Log_Pos: 262
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 259
Relay_Master_Log_File: binlog-master01.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 262
Relay_Log_Space: 414
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)
master1
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.202
Master_User: rep
Master_Port: 30307
Connect_Retry: 10
Master_Log_File: binlog-master02.000002
Read_Master_Log_Pos: 262
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 259
Relay_Master_Log_File: binlog-master02.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 262
Relay_Log_Space: 414
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: 3
1 row in set (0.00 sec)
Slave_IO_Running andSlave_SQL_Running are all YES that successful configuration
Validation data synchronization
master1
[root@db01 mysql]# /service/mysql/bin/mysql -uroot -ppwd Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.25-log db01 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database reptest; Query OK, 1 row affected (0.02 sec) mysql> use reptest Database changed mysql> create table t1 (id int primary key,name varchar(50)); Query OK, 0 rows affected (0.03 sec) mysql> insert into t1 values(1,'the first'); Query OK, 1 row affected (0.03 sec) mysql> insert into t1 values(2,'the second'); Query OK, 1 row affected (0.00 sec)
master2
[root@db02 ~]# /service/mysql/bin/mysql -uroot -ppwd
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.25-log db02
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use reptest
Database changed
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from t1;
+----+------------+
| id | name |
+----+------------+
| 1 | the first |
| 2 | the second |
+----+------------+
2 rows in set (0.00 sec)