One of our expertise is to give our clients high-availability products. In many solutions we are implementing different types of replication.
Our favor replication is master-master or active-active, which give the product stability and long-term life-cycle. MySQL can implement it easily, and this blog post come to give simple quick-start guide to beginners and advanced MySQL users
Background
MySQL master-master implemented as Master-Slave in dual-way, means every master think the other is slave and opposite. We will demonstrate 2 servers of replication but you can do it with 3 or more, in star topology. In addition, each master can have one or more slaves to scale reads (very recommended on master-master).
We will implement the master-master in Amazon (AWS EC2) environment with AMI Linux distribution, but you can do it in your own environment with your favorite distribution.
Server A
Our server is fresh meet, so will install MySQL:
Let's edit the MySQL configuration file with our lovely editor - nano (you can use vi or what ever editor you like):
# sudo nano /etc/my.cnf
We will add the next config snippet to the pre-installed configuration (under [mysqld] section):
server-id = 1 log_bin = /var/log/mysql/mysql-bin.log auto-increment-increment = 2 auto-increment-offset = 1
The trick here is to avoid auto increment conflict, therefor each master have its own series of increment. Server A will start from 1 (auto-increment-offset variable) and increase by 2 (auto-increment-increment), means odd number (1, 3, 5,...). The second server. If you have more then two server, increase the auto-increment-increment variable accordingly.
Let's start the MySQL server or restart if you update running one (restart works for both cases):
# sudo service mysqld restart
Because this is fresh installation, we declare admin password:
# sudo mysqladmin -u root password 'verystrongpass'
Let's login to our MySQL server to declare the Master-Slave configuration:
# mysql -u root -p
In the MySQL console we should first configure the user that access to the replica:
> CREATE USER 'replicator'@'%' IDENTIFIED BY 'rep_pass';
After that we will give the replica user (replicator) permission to access from the master:
> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
Then will show details about Server A master status, for further use:
> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 495 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Pay Attention! mysql-bin.000008, 495 - will be used on Server B slave configuration.
Server B
After configure Server A, we will do almost the same for Server B.
Server B is also fresh meet and we will install MySQL:
# sudo yum install mysql-server
Let's add the MySQL configuration snippet to declare the replication settings (under [mysqld] section)
# sudo nano /etc/my.cnf
#replica settings
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
auto-increment-increment = 2
auto-increment-offset = 2
Now let's restart (or start) the server and declare MySQL admin (root) password:
# sudo service mysqld restart
# sudo mysqladmin -u root password 'verystrongpass'
Let's login to our MySQL server to declare the Master-Slave configuration:
# mysql -u root -p
In the MySQL console we should first configure the user that access to the replica:
> CREATE USER 'replicator'@'%' IDENTIFIED BY 'rep_pass';
After that we will give the replica user (replicator) permission to access from the master:
> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
Now we should add declare Server A as the Master of Server B.
We need to stop to receive any binary transactions from the master to be able to declare new one, so, we are stopping any slave operations:
> SLAVE STOP;
And now for the magic trick, let's declare our master (Server A):
> CHANGE MASTER TO MASTER_HOST = 'SERVER-A-IP-OR-KNOWN-HOST-NAME',
MASTER_USER = 'replicator',
MASTER_PASSWORD = ‘reprep’,
MASTER_LOG_FILE = ‘mysql-bin.000008’,
MASTER_LOG_POS = 495;
Final step - start the slave to receive transactions from our new master (Server A):
> SLAVE START;
Now we declare Server A to be the Master of Server B. We need to to the opposite. For that reason let's show the master status of Server B to declare it as Master on Server A,
From the MySQL console of Server B:
> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 507 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Pay Attention! mysql-bin.000003, 507 - will be used on Server A slave configuration.
(Back To) Server A
As we described in the last note of Server B we required to declare Server B as Master of Server A, therefor, let's login to MySQL console on Server A:
# mysql -u root -p
After that we will configure it the same way we did on Server B (3 steps):
> SLAVE STOP;
> CHANGE MASTER TO MASTER_HOST= ‘SERVER-A-IP-OR-KNOWN-HOST-NAME’,
MASTER_USER = ‘replicator’,
MASTER_PASSWORD = ‘reprep’,
MASTER_LOG_FILE = ‘mysql-bin.000003’;
MASTER_LOG_POS = 507;
> SLAVE START;
Now you should have 2 servers configured as Master-Master replication.
For verification just run the next commands from Server A MySQL console:
> CREATE DATABASE testrep;
> use testrep;
> CREATE TABLE reptable (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`));
> INSERT INTO reptable (name) VALUES ('BillRun.net');
> INSERT INTO reptable (name) VALUES ('MySQL Replication');
> SELECT * FROM reptable;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | BillRun.net |
| 3 | MySQL Replication |
+----+-------------------+
2 rows in set (0.00 sec)
Pay attention that Server A add odd numbers only. It take the last value and the next in its series.
Now let's go to Server B and add more lines:
> INSERT INTO reptable (name) VALUES ('MySQL another Master');
> INSERT INTO reptable (name) VALUES ('MySQL is very cool');
> SELECT * FROM reptable;
+----+----------------------+
| id | name |
+----+----------------------+
| 1 | BillRun.net |
| 3 | MySQL Replication |
| 4 | MySQL another Master |
| 6 | MySQL is very cool |
+----+----------------------+
4 rows in set (0.00 sec)
Now you see that Server B take the last even number that is available after the last number inserted.
In case you need to enforce clear auto increment from both servers, you can insert directly the id with specific value. Nevertheless, the app should verify that it succeed to insert and take care of conflicts.
That's all fox. Next will be deal with the load balancing and web server.