Master-Slave Replication
Treselle Engineering June 6, 2014
669
VIEWS
Twitter Facebook Google +r LinkedIN
Master-Slave Replication
Table of Content [show]
Introduction
This blog covers the basics of how replication really works on the high level, and the configuration of Master-Slave replication. With this replication we can share load between Master and Slave (only read operations), take backups from Slave server without effecting the Master server.
Use Case
This use case describes replication and configuration of a Master-Slave replication.
What we need to do:
Theoretical explanation of how replication works.
Configuration of Master Server.
Configuration of Slave Server.
Solution
Before solving our use case, let’s get some pre-requisites satisfied.
Pre-requisites:
Minimum two Linux servers along with MySQL software should be installed.
Master ip: 192.168.0.1
Slave ip: 192.168.0.2
Theoretical explanation of how replication works:
Types of mysql replication:
Replication is based on events written to the binary log, which are read from master and then processed on the slave.
Statement Based Replication:
Replication work is based on propagation of SQL statements from master to slave. This is called statement-based replication. Often it called SBR, Which corresponds to the standard statement-based binary logging format.
Row Based Replication:
Replication based on row-based logging which changes binary logging logs in individual table row. This is known as row-based replication. It is also called as RBR. In row-based replication, the master writes events to the binary log that indicates how individual table rows are changed.
Mixed Based Replication:
The server can change the binary logging format in real time according to the type of event using mixed-format logging. When the mixed format is in effect, statement-based logging is used by default, but automatically switches to row-based logging in particular cases. Replication using the mixed format is often referred to as mixed-based replication or mixed-format replication.
So now let’s start with what is happening on the master. For replication to work, first and foremost, master needs to write replication events to a special log called binary log. The binary log file stores data that replication slave will be reading later. Whenever a replication slave connects to a master, master creates a new thread for the connection.
Slaves that are up to date will mostly be reading events that are still cached in OS cache on the master, so there will not be any physical disk reads on the master in order to feed binary log events to slave(s). However, when you connect a replication slave that is few hours or even days behind, it will initially start reading binary logs that were written hours or days ago – master may no longer have these cached, so disk reads will occur. If master does not have free IO resources, you may feel a bump at that point.
Now let’s see what is happening on the slave. When you start replication, two threads are started on the slave:
IO thread:
This process called IO thread connects to a master, reads binary log events from the master as they come in and just copies them over to a local log file called relay log. That’s all.
Even though there are only one thread reading binary log from the master and one writing relay log on the slave, very rarely copying of replication events is a slower element of the replication. There could be a network delay, causing a steady delay of few hundred milliseconds, but that’s about it.
To see IO thread status, just type “show slave status\G” on slave.
Master_Log_File – last file copied from the master (most of the time it would be the same as last binary log written by a master)
Read_Master_Log_Pos – This shows the position where binary log copied over the relay log on the slave.
SQL thread:
This process reads the events from a relay log stored locally on the replication slave and then applies them as fast as possible and it is a single thread.
To see SQL thread status, just type “show slave status\G” on slave.
Relay_Master_Log_File – The name of the master binary log file containing the most recent event executed by the SQL thread.
Exec_Master_Log_Pos – The position in the current master binary log file through which the SQL thread has read and executed.
SQL thread
Configuration of Master Server:
Take backup of database from Master server. The command to take consistent backup is given below:
1
# mysqldump -u$username -p$passwd DBname –single-transaction -R –triggers –quick –master-data=2 –flush-logs>/opt/mysqlbackup/MasterBackup.sql
Edit my.cnf file on the Master server to enable binary logging and set the server’s id.
1
#vi /etc/my.cnf
Add these lines under [mysqld] section:
1
2
log-bin=mysql-bin
server-id=1
Restart MySQL for the changes to take effect.
1
#/etc/init.d/mysqld restart
Login into MySQL as root user and create the slave user and grant privileges for replication.
1
2
3
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’192.168.0.2’ IDENTIFIED BY ‘your_password’;
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
Now execute ‘SHOW MASTER STATUS’ command to get all the data we need.
Show master status
Note the current binary log and position. In our example, the Master server is currently on mysql- bin.00003 binary log and on position 239. Here Binlog_Do_DB means to capture the DB changes into binary file and Binlog_Ignore_DB means do not capture the DB changes into binary file and these are empty because we did not mentioned these parameters in my.cnf file.
Configuration of Slave Server:
Edit my.cnf file on the Slave server.
1
#vi /etc/my.cnf
Add these lines under the [mysqld] section:
1
2
3
server-id = 2
relay-log = mysql-relay-bin
log-bin = mysql-bin
Restart MySQL for the changes to take effect.
1
#/etc/init.d/mysqld restart
Now import the dump file that we exported from Master server.
1
# mysql -u root -p CHANGE MASTER TO
-> MASTER_HOST=’192.168.0.1′,
-> MASTER_USER=’slave_user’,
-> MASTER_PASSWORD=’your_password’,
-> MASTER_LOG_FILE=’mysql-bin.000003′,
-> MASTER_LOG_POS=239;
Note the values for each field. The MASTER_HOST is the private IP of the Master server, MASTER_USER is the user we created for replication, MASTER_PASSWORD is the password for the replication user, MASTER_LOG_FILE is the binary log that we recorded from the Master server status earlier, and MASTER_LOG_POS is the position the Master was in that we recorded.
Now start the slave thread on the Slave server.
1
mysql> START SLAVE;
Let’s make sure that replication is working with the ‘SHOW SLAVE STATUS’ statement:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 314
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000003
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: 314
Relay_Log_Space: 235
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
If Slave_IO_Running and Slave_SQL_Running is YES then your replication is working fine.
Conclusion
One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.
Do backups from slave. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If we have slave, we just take slave out of rotation and run backups off the slave.
References
http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html/
http://www.tecmint.com/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora/
balajiommudali 