#replication status

In our last article we covered basic MySQL server replication using the master-lave configuration, using it to create reserve database copies, and running resource intensive queries.

This configuration has a few relatively obvious limitations:

1. Database performance is limited entirely by a single server: all active queries must go through the master server first, before being sent off to be handled by the slave servers.

2. Replication of overloaded databases might lag behind, but we can still use it to get reliable results while it's in read-only.

3. If the master server fails for whatever reason, it will require administrative attention.

To deal with these issues, MySQL actually allows us to set up replication using a master-master configuration. This setup lets us use two servers to read and write to the database while syncing any differences that might come up. Master-master configuration is exactly what we're going to be covering in this article, so let's get into it!

To get this going you will need 2 servers running CentOS 6.7 with MySQL server installed and a database, that we will call "test" for this example, that we will configure for master-master replication.

The First Server

1. Input settings into '/etc/mysql/my.cnf' on the first server:

server-id=1 log-bin=/var/lib/mysql/mysql-bin replicate-do-db=test

2. Add a user for replication:

mysql –h localhost –uroot –p mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'somepass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Restart the MySQL process:

root@mysql-server1# service mysql restart

3. Check server status:

mysql> SHOW MASTER STATUS\G *************************** 1. row ***************************            File: mysql-bin.000002        Position: 121    Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

The number in the 'Position' field here will grow along with the size of the database.

The Second Server

1. Edit '/etc/mysql/my.cnf' on the second server

server-id = 2 log-bin=/var/lib/mysql/mysql-bin binlog_do_db=test

Restart the MySQL process

root@mysql-server2# service mysql restart

2. Let's add a user:

mysql –h localhost –uroot –p mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'somepass'; mysql> CREATE DATABASE test; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Restart MySQL again:

root@mysql-server2# service mysql restart

3. Launch replication

mysql@mysql-server2> slave stop; mysql@mysql-server2> CHANGE MASTER TO MASTER_HOST = "", MASTER_USER = "repl", MASTER_PASSWORD = "somepass", MASTER_LOG_FILE = "mysql-bin.000002 ", MASTER_LOG_POS = 121; mysql@mysql-slave> start slave;

4. Check replication status.

mysql> show master status\G *************************** 1. row ***************************            File: mysql-bin.000004        Position: 121    Binlog_Do_DB: test

Finally, finish setting up replication on the first server

mysql –h localhost –uroot –p mysql> slave stop; CHANGE MASTER TO MASTER_HOST = ‘', MASTER_USER = 'repl', MASTER_PASSWORD = 'somepass', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 121; mysql> slave start;

Done!

You should not that the database "test" isn't there on the first server yet. We're going to have to create it and make sure that it, and its tables, appear on the second server.

Let's run MySQL client and create 'test' database and an empty 'example' table:

mysql –h localhost –uroot –p mysql> CREATE DATABASE test; mysql> USE test; mysql> CREATE TABLE example (‘id’ int);

Now we can check to see if the same database, 'test,' exists on the second server, so let's get into MySQL:

mysql –h localhost –uroot –p mysql> SHOW TABLES IN test; +-------------------+ | Tables_in_test    | +-------------------+ | example           | +-------------------+ 1 row in set (0.00 sec)

Now is a good a time as any to make sure our replication setup works. You can do this by filling in some database fields, or deleting filled in fields.

At this point, any application can make requests on either of the servers, with replication synchronizing any changes made to the other.

You can also setup a simple failover scheme, using two DNS type A records that point to both servers, to address the server's database by name instead of IP. Using this setup, all requests should be distributed almost evenly. There are methods to manage the workload balance between servers, but we'll leave that for another article.

This kind of two-way replication works great for small scale operations where you need failover and replication, but don't need more than two servers.

That's pretty much it for today. As you can no doubt tell, there are of course some limitations to this kind of setup. We'll go over these in our next article where we'll be covering MySQL Galera cluster. Until next time!