Basics of replication in MySQL

I got acquainted with the replication of MySQL servers relatively recently, and as I went through various experiments with tuning, I wrote down what I did. When there was a lot of material, an idea to write this article appeared. I tried to collect advice and solutions on some of the most basic issues that I faced. Along the way, I will provide links to documentation and other sources. I can not claim to be complete description, but I hope that the article will be useful.

Small introduction

Replication (from lat. Replico-repeat) is the replication of data changes from the main database server on one or several dependent servers. The main server will be called the master , and dependent servers - replicas .
Data changes that occur on the wizard are repeated on the replicas (but not vice versa). Therefore, data change requests (INSERT, UPDATE, DELETE, etc.) are executed only on the master, and data read requests (in other words, SELECT) can be executed on both replicas and the master. The replication process on one of the replicas does not affect the operation of the other replicas, and practically does not affect the operation of the wizard.
Replication is performed using binary logs conducted on the master. They contain all the queries that lead (or potentially lead) to changes in the database (the queries are not saved explicitly, so if you want to see them, you will have to use the mysqlbinlog utility). Binlogs are transferred to replicas (binlog downloaded from the wizard is called " relay binlog ") and saved queries are executed starting from a specific position. It is important to understand that during replication, it is not the changed data that is transmitted, but only the requests that cause changes.
When replicating the contents of the database is duplicated on multiple servers. Why use duplication? There are several reasons:

• performance and scalability . One server can not cope with the load caused by simultaneous reads and writes to the database. The advantage of creating replicas will be the greater, the more reads per one write operation on your system.
• fault tolerance . In case of a replica failure, all read requests can be safely transferred to the wizard. If the master fails, write requests can be transferred to the replica (after the master is restored, it can assume the role of replica).
• data backup . The replica can be “slowed down” temporarily to perform mysqldump, but the master can not.
• deferred calculations . Heavy and slow SQL queries can be performed on a separate replica, without fear of interfering with the normal operation of the entire system.

In addition, there are some other interesting features. Since not the data itself is sent to the replicas, but the queries that cause their changes, we can use a different table structure on the master and the replicas. In particular, the type of the table (engine) or a set of indexes may differ. For example, to perform full-text search, we can use the MyISAM table type on a replica, even though the wizard will use InnoDB.

Replication setup

Suppose we have a working MySQL database, already filled with data and included in the work. And for one of the reasons described above, we are going to enable replication of our server. Our raw data:

• The IP address of the master is 192.168.1.101, the replicas are 192.168.1.102.
• MySQL is installed and configured
• need to set up testdb database replication
• we can pause the wizard for a while
• we, of course, have root on both machines

Wizard settings

Be sure to specify the unique server ID, the path for binary logs and the name of the database for replication in the [mysqld] section:
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
replicate-do-db = testdb
Make sure you have enough disk space for binary logs.

Let's add the user replication under which rights replication will be made. The " replication slave " privilege will suffice:
mysql@master> GRANT replication slave ON "testdb".* TO "replication"@"192.168.1.102" IDENTIFIED BY "password";

Restart MySQL so that the changes in the config take effect:
root@master# service mysqld restart

If everything went well, the show master status command should show something like the following:
mysql@master> SHOW MASTER STATUS\G
File: mysql-bin.000003
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
The position value should increase as changes are made to the database on the master.
')

Replica settings

Specify the server ID, the database name for replication and the path to the relay-binlog in the [mysqld] section of the config, then restart MySQL:
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
replicate-do-db = testdb

root@replica# service mysqld restart

We transfer data

Here we have to block the database for writing. To do this, you can either stop the application, or use the read_only checkbox on the wizard (note: this flag does not apply to users with the SUPER privilege). If we have MyISAM tables, let's also do the " flush tables ":
mysql@master> FLUSH TABLES WITH READ LOCK;
mysql@master> SET GLOBAL read_only = ON;

Let's see the status of the wizard with the command “show master status” and remember the values ​​of File and Position (after successful blocking of the wizard they should not change)
File: mysql-bin.000003
Position: 98

, :
mysql@master> SET GLOBAL read_only = OFF;

.
Finally, start the replication with the " change master to " and " start slave " commands and see if everything went well:
mysql@replica> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000003 ", MASTER_LOG_POS = 98;
mysql@replica> start slave;
We take the values ​​MASTER_LOG_FILE and MASTER_LOG_POS from the wizard.

Let's see how replication goes with the " show slave status " command:
mysql@replica> SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 98
Relay_Log_File: mysql-relay-bin.001152
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb,testdb
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: 98
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: 5


I selected the most interesting values ​​now. When replication is successfully started, their values ​​should be approximately the same as in the listing (see the description of the " show slave status " command in the documentation). The value of Seconds_Behind_Master can be any integer.
If replication is normal, the replica will follow the master (the log number in Master_Log_File and the position Exec_Master_Log_Pos will increase). The replica lag time from the master (Seconds_Behind_Master) should ideally be zero. If it does not contract or grows, it is possible that the load on the replica is too high — it simply does not have time to repeat the changes that occur on the master.
If the value of Slave_IO_State is empty, and Seconds_Behind_Master is NULL, replication has not started. See the MySQL log for the cause, fix it and rerun the replication:
mysql @ replica> start slave;

By these simple actions we get a replica, the data of which is identical to the data on the master.
By the way, the master lock time is the time to create a dump. If it is created unacceptably long, you can try to do this:

• Block the master record with the read_only flag, remember the position and stop MySQL.
• after that, copy the database files to the replica and enable the wizard.
• start replication in the usual way.
  

There are several ways to create a replica without stopping the wizard at all, but they do not always work.

Add replicas

Suppose we already have a working master and replica, and we need to add one more to them. Make it even easier than adding the first cue to the master. And much nicer is that there is no need to stop for this master.
First, let's configure MySQL on the second replica and make sure that we have entered the necessary parameters into the config:
server-id = 3
replicate-do-db = testdb

Now stop replication on the first replica:
mysql@replica-1> stop slave;

The replica will continue to work normally, but the data on it will no longer be relevant. Let's look at the status and remember the position of the master, to which the replica reached before stopping replication:
mysql@replica-1> SHOW SLAVE STATUS\G

We will need the values ​​Master_Log_File and Exec_Master_Log_Pos:
Master_Log_File: mysql-bin.000004
Exec_Master_Log_Pos: 155

Create a database dump and continue replication on the first replica:
mysql@replica-1> START SLAVE;

Recover data from dump on second replica. Then turn on replication:
mysql@replica-2> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000004 ", MASTER_LOG_POS = 155;
mysql@replica-2> START SLAVE;

The MASTER_LOG_FILE and MASTER_LOG_POS values ​​are respectively the values ​​of Master_Log_File and Exec_Master_Log_Pos from the result of the show slave status command on the first replica.
Replication should start from the position where the first replica was stopped (and accordingly, a dump was created). Thus, we will have two replicas with identical data.

Merge replicas

Sometimes this situation occurs: on the master there are two databases, one of which is replicated on one replica, and the second on the other. How to configure replication of two databases on both replicas without dumping them on the master and turning it off from work? Simple enough, using the " start slave until " command.
So, we have a master with the testdb1 and testdb2 databases, which are replicated on replica-1 and replica-2 replicas respectively . Set up the replication of both databases to replica-1 without stopping the wizard.
Stop replication on the replica-2 command and remember the position of the master:
mysql@replica-2> STOP SLAVE;
mysql@replica-2> SHOW SLAVE STATUS\G
Master_Log_File: mysql-bin.000015
Exec_Master_Log_Pos: 231

Create a testdb2 database dump and resume replication (this is the end of the replica-2 manipulations). Dump restore to replica-1.

The situation on replica-1 is as follows: the testdb1 database is in one master position and continues to replicate, the testdb2 database is restored from the dump from a different position. Sync them.

Stop replication and remember the position of the wizard:
mysql@replica-1> STOP SLAVE;
mysql@replica-1> SHOW SLAVE STATUS\G
Master_Log_File: mysql-bin.000016
Exec_Master_Log_Pos: 501

Make sure that in the config on replica-1 in the [mysqld] section the name of the second database is indicated:
replicate-do-db = testdb2

Restart MySQL for changes to take effect. By the way, it was possible to simply restart MySQL without stopping replication - from the log we would know at what position the wizard stopped replication.

Now we will replicate from the position at which replica-2 was suspended to the position at which we have just suspended replication:
mysql@replica-1> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000015 ", MASTER_LOG_POS = 231;
mysql@replica-1> start slave until MASTER_LOG_FILE = "mysql-bin.000016 ", MASTER_LOG_POS = 501;

Replication will end as soon as the replica reaches the specified position in the until section, after which both our databases will correspond to the same master position (at which we stopped replication on replica-1). Make sure of this:
mysql@replica-1> SHOW SLAVE STATUS\G
mysql@replica-1> START SLAVE;
Master_Log_File: mysql-bin.000016
Exec_Master_Log_Pos: 501

replica-1 [mysqld] :
replicate-do-db = testdb1
replicate-do-db = testdb2

: .
Restart MySQL and continue replication:
mysql@replica-1> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000016 ", MASTER_LOG_POS = 501;
After replica-1 will catch up with the master, the contents of their database will be identical. You can merge the database into replica-2 either in a similar way, or by making a complete dump of replica-1.

Castling Wizard and Replicas

It is sometimes necessary to switch a replica to master mode, for example, in the event of a master failure or when carrying out technical work on it. To enable such a switch, you need to configure a replica like a master, or make it a passive master .

Let's enable binary logging (in addition to relay binlog) in the [mysqld] section of the config:
log-bin = /var/lib/mysql/mysql-bin

And add a user to maintain replication:
mysql@master> GRANT replication slave ON 'testdb'.* TO 'replication'@'192.168.1.101′ IDENTIFIED BY "password ";

The passive master replicates just like a regular replica, but other than that it creates binary logies - that is, we can begin replication from it. Verify this with the " show master status " command:
mysql@replica> SHOW MASTER STATUS\G
File: mysql-bin.000001
Position: 61
Binlog_Do_DB:
Binlog_Ignore_DB:

Now, to put the passive master into active mode, you need to stop replication on it and enable replication on the former active master. To prevent data loss at the moment of switching, the active master must be blocked for writing.
mysql@master> FLUSH TABLES WITH READ LOCK
mysql@master> SET GLOBAL read_only = ON;
mysql@replica> STOP SLAVE;
mysql@replica> SHOW MASTER STATUS;
File: mysql-bin.000001
Position: 61
mysql@master> CHANGE MASTER TO MASTER_HOST = "192.168.1.102 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000001 ", MASTER_LOG_POS = 61;
mysql@master> start slave;
Everything, so we changed the active master. You can remove the lock from the former master.

Conclusion

We learned a little about how to set up replication in MySQL and perform some basic operations. Unfortunately, the following important questions were left out of the article:

• elimination of single points of failure (SPF, Single Points of Failure). When using a single MySQL server, its failure caused the entire system to fail. When using multiple servers, the failure of any of them will lead to a system failure, unless we specifically take care of it. We need to consider handling the situation with a master and replica failure. One of the existing tools - MMM , however, requires modification with a file.
• load balancing. When using multiple replicas, it would be convenient for us to use a transparent balancing mechanism, especially if the performance of replicas is not the same. Under Linux, it is possible to use a standard solution - LVS .
• change the logic of the application. In an ideal situation, requests for reading data should be sent to the replicas, and to change - to the master. However, due to the possible backlog of replicas, such a scheme is often inoperative and it is necessary to identify such requests for reading, which must still be executed on the master.

I hope to highlight these issues in future articles.
Thanks for attention!