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.
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.
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:
| 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.