MySQL – Master / Slave Replication and MySQL Multi – Part Two

MySQL Replication – Part Two: Master / Slave

In this two-part series we provide some food for thought on MySQL, and strategies for Master / Slave replication.  First off, the discussion and examples given are based on a CentOS Linux install of MySQL.

The particulars are as follows:

  • Operating System: CentOS 6.4
  • Database Engine: MySQL 5.5

In MySQL Replication – Part One: Multi, we showed how to setup multiple MySQL instances on a single Linux server.  In this part we will discuss how to assign MASTER and SLAVE roles to a MySQL engine.

Premise

To put this in perspective, here is the scenario.  We have three servers, let’s call them “A”, “B” and “C”.  They have IP addresses as follows: xxx.yyy.123.101, nnn.mmm.ppp.102 and iii.jjj.kkk.103.  These servers each have a single MySQL engine which supports multiple databases, and applications.  As an example, Box A and B could be hosts for PLESK controlled hosting sites.  A majority of these hosted sites are based on WordPress, and therefore have WP databases running on the MySQL engine.  Box “C” runs MySQL for proprietary applications.

MySQL Three Servers

Now, as per our first installment, we configure each of these three servers to host two additional MySQL engines.  The main, original engine, will run at port 3306 and will be configured as a MASTER.  The two new engines at ports 33602 an 33603 are configured as SLAVES.

MySQL MASTER & SLAVES

With that in place, what we want to do is make a full-mesh replication.  Let’s explain.

MySQL Master / Slave Replication Concepts

In this article we describe a standard Master / Slave replication setup.  As data is written to the Master, the Slave, running on a different MySQL instance, updates itself to reflect the content of the Master.  The primary reasons to implement replication are

  • Backups
  • Spread read access over multiple servers for scalability
  • High Availability / Automated fail-over

We  implement basic asynchronous replication.  A master writes a binary log file, and slaves can read this log file (possibly selectively) to replay the query statements. It is asynchronous, which means the master and slaves may have different states at a specific point of time; also this setup can survive a network disconnection.

A MySQL instance can be configured as Master for some databases, and as Slave for others.  In our implementation, the MySQL instances will be Master only and Slave only. Plus, we will implement full replication (in other words not selective databases only).  In our setup, the MySQL engine running at port 3306 will be configured as Master, and the instances on port 33062 and 33063 as slaves.

We implement a full-mesh replication between three servers.  As explained above, each server runs One Master and Two Slaves.  Each Master replicates on each of the other servers, so:

MySQL Full Mesh Replication

Master A -> Slave B1 Master B -> Slave C1 Master C -> Slave A1
Master A -> Slave C2 Master B -> Slave A2 Master C -> Slave B2


Comment
:  when going beyond three nodes, full-mesh replication quickly becomes counter productive.  The scenario discussed here illustrates the principle however of setting up multiple slaves per master.

Setting up Masters

Each of the servers will run as Master on the 3306 MySQL instance.  The setup will be nearly identical for each of the boxes, so let’s do this.  We start with Master C.

Create a user account for the Slaves

Master C will be replicated on Slave A1 and Slave B2.  We create an account for these Slaves at the Master C like so:

[root@serverC ~] # mysql -uroot
mysql> GRANT REPLICATION SLAVE ON *.* TO replicant@xxx.yyy.123.101
mysql> IDENTIFIED BY 'secretslave';
mysql> GRANT REPLICATION SLAVE ON *.* TO replicant@nnn.mmm.ppp.102
mysql> IDENTIFIED BY 'secretslave';

So Slaves at Server A (.101) and Server B (.102) can now connect to Server C’s MySQL engine running at port 3306 with username “replicant” and password “secretslave”.

We then do the identical (mutatis mutandis) thing at Server A and Server B.   At the end of this procedure, all Masters have user accounts for their replication slaves.

Configure the engine as a Master

The /etc/my.cnf file is edited to put options in for logging.  We set a unique ID for the Master, and log ALL transactions to a binary log file (binlog). By default, the master server logs updates for all databases, and the slave server replicates all updates that it receives from the master.

Remember from Part One that the Master section in the server is in the [mysqld1] section.

Unique ID:  Each of the instances need a unique id.  Since we have 9 instances, we could just use numbers 1 .. 9.  To keep things a little structured however, we will number the Masters as 1 .. 2 .. 3.  Since each server implements two slaves, we can name these {11 .. 12} {21 .. 22} {31 .. 32}.

As an example we list the [mysqld1] section of the /etc/my.cnf file at Server C.

[mysqld1]
port = 3306
datadir = /var/lib/mysql 
socket = /var/lib/mysql/mysql.sock 
user = mysql 
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

# master setup
server-id=3
log-bin=mysql-bin
binlog-format=mixed
max_binlog_size=1GB
xinnodb_flush_log_at_trx_commit=1
sync_binlog=1

The options are reasonably self explanatory.

  • server-id .. the unique id
  • log-bin .. the name prefix of the binary log file (binlog).
  • These log files are created in the data directory, and are called mysql-bin.000001, mysql-bin.000002 etc.
    • the binlog file contains all relevant transactions which change the state of the database.
    • These files are read by the Slaves.
  • binlog-format .. “mixed’ is recommended
  • max-binlog_size .. when this size is reached, a new binlog file is created
  • innodb_flush_log_at_trx_commit .. only completed transactions are logged
  • sync_binlog .. see note below

The sync_binlog option.  If you do not mind possible data loss (up to 1 second’s worth) then you can use either 0 or 2 at your own risk if the rewards (faster write speed) are worth it.  A value of 1 provides full ACID compliance. In computer science, ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

Restart the Master engine ..

# mysqld_multi stop 1
# mysqld_multi start 1

Repeat these steps for configuring the Master at Server A and Server B.  So in the end we have three configured Masters running.

Configuring the Slaves

Slaves connect to their Master, read data from the binlog file, and replicate, i.e. playback, all transactions.

The configuration is rather simple.  Two lines are added in the /etc/my.cnf file on each server.  One is to set the unique ID, the other makes our Slave instance read-only.  This is a safety / security precaution.

So, for the Slaves at Server A, the [mysqld2] and [mysqld3] sections look as follows:

[mysqdl2]
port = 33062
datadir = /var/lib/mysql-databases/mysqld2 
socket = /var/lib/mysql-databases/mysqld2/mysql.sock 
user = mysql 
log-error = /var/log/mysql2.log
pid-file = /var/lib/mysql-databases/mysqld2/mysqld.pid

# slave setup
server-id=11
read-only=1
[mysqdl3]
port = 33063
datadir = /var/lib/mysql-databases/mysqld3 
socket = /var/lib/mysql-databases/mysqld3/mysql.sock 
user = mysql 
log-error = /var/log/mysql3.log
pid-file = /var/lib/mysql-databases/mysqld3/mysqld.pid

# slave setup
server-id=12
read-only=1

We restart the Slave engines for this to take effect:

# mysqld_multi stop 2,3
# mysqld_multi start 2,3

We do this at each server.  So in the end we have six configured Slaves running.

Activating the Slaves

Although by now the Slave engines are configured and running, they are not yet behaving as Replication Slaves.  To start replication, there are two steps required at each Slave.

  • Import the Master Data into the Slave
  • Start the Slave service

Import Master Data into the Slave

To import the Master Data, we use the mysqldump utility, with an extra option.  At each Master we execute the following (the example is for Master A):

# mysqldump -uroot --all-databases --master-data=2 > master_a.sql

The option –master-data=2 causes the dump file to include a comment containing a CHANGE MASTER statement that indicates the replication coordinates as of the time of the backup.  Also, we use the –all-databases since we are a complete replication.  This includes all users etc.

When looking at the content of the .sql file, we will find lines like this:

--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=691;

Referring to the diagram higher, we want Master A to replicate to Slave B1 and to Slave C2. Let’s discuss B1. At Server B, after we copy the master_a.sql file, we import the data in Slave B1, which runs on port 33062.  We load the data like this:

# mysql -uroot -h127.0.0.1 -P33602 < master_a.sql

Start the Slave Service

We log on to the Slave, and tell it about the Master and the replication start coordinates.

# mysql-uroot -h127.0.0.1 -P33602
mysql> CHANGE MASTER TO
mysql> MASTER_HOST='xxx.yyy.123.101',
mysql> MASTER_USER='replicant',
mysql> MASTER_PASSWORD='secretslave',
mysql> MASTER_LOG_FILE='mysql-bin.000001',
mysql> MASTER_LOG_POS=691;

The host is the IP address of Server A.  The user and password were created when we prepared the Master.  The LOG_FILE and LOG_POS coordinates are obtained from the master_a.sql dump file.

Finally, still logged in to the mysql console, we start the slave:

mysql> START SLAVE;

And we check on its condition like this:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: xxx.yyy.123.101
 Master_User: replicant
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 2975
 Relay_Log_File: mysqld-relay-bin.000002
 Relay_Log_Pos: 2140
 Relay_Master_Log_File: mysql-bin.000001
 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: xxxxxx
 Relay_Log_Space: xxxxxx
 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: 2880
1 row in set (0.00 sec)

This is an example status of a healthy Slave.  Once we repeat this procedure for all six slaves, we have our full-mesh replication.  When you look at the data directory for the Slave, you will find a file like this: mysqld-relay-bin.000002.  This is the binary logfile which contains all the updates the slave has updated – or is about to update – itself with.

Sidenote: Killing a Slave

If we ever want to decommission a slave, this is quite simple.  At the Slave’s mysql console execute:

mysql> STOP SLAVE;
mysql> RESET SLAVE; // this removes all Master Info

After this, remove the replicating log files (mysqld-relay-bin.xxxxxx) in the datadir.

Conclusion

This article described how to implement full engine replication.  It is possible to replicate selected databases only.  Indeed, a single MySQL instance can be configured as a Master for some databases, and as a Slave for others.  To learn more, do check the official MySQL documentation.