MySQL replication basics

    I became acquainted with the replication of MySQL servers relatively recently, and as I was doing various experiments with tuning, I wrote down what I did. When a lot of material was gathered, the idea came up to write this article. I tried to collect advice and solutions on some of the most basic issues that I encountered. In the process, I will give links to documentation and other sources. I can not claim to be a 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 more dependent servers. We will call the main server the master , and the dependent ones as replicas .
    Data changes that occur on the wizard are repeated on the replicas (but not vice versa). Therefore, data modification requests (INSERT, UPDATE, DELETE, etc.) are executed only on the wizard, and data read requests (in other words, SELECT) can be executed both on replicas and on the wizard. The replication process on one of the replicas does not affect the work of other replicas, and practically does not affect the work of the wizard.
    Replication is done using binary logsconducted by the master. They store all the queries that lead (or potentially lead to) changes to the database (queries are not saved explicitly, so if you want to see them, you will have to use the mysqlbinlog utility ). Binlogs are sent to replicas (the binlog downloaded from the wizard is called " relay binlog ") and saved requests are executed starting from a certain position. It is important to understand that during replication, not the changed data itself is transmitted, but only the requests that cause the changes.
    During replication, the contents of the database are duplicated on several servers. Why do you need to resort to duplication? There are several reasons:
    • performance and scalability . One server may not cope with the load caused by simultaneous read and write operations in the database. The benefits of creating replicas will be greater, the more read operations fall on one write operation on your system.
    • fault tolerance . In the event 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 a replica).
    • data backup . The replica can be “slowed down” for a while to execute mysqldump, but the master cannot.
    • 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 the data itself is not transferred to the replicas, but the queries causing their changes, we can use a different table structure on the master and replicas. In particular, the type of table (engine) or set of indices may differ. For example, to perform a full-text search, we can use the MyISAM table type on the replica, despite the fact that 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 wizard is 192.168.1.101, the replicas are 192.168.1.102.
    • MySQL installed and configured
    • testdb database replication required
    • we can pause the wizard for a while
    • we, of course, have root on both machines

    Wizard Settings

    Be sure to specify a unique server ID, path for binary logs and the name of the database for replication in the [mysqld] section: Make sure that you have enough disk space for binary logs. Add the user replication, under whose rights replication will be performed. The " replication slave " privilege will suffice : Restart MySQL so that the changes in the config take effect: If everything went well, the " show master status " command should show something like this: The position value should increase as the database changes on the wizard .
    server-id = 1
    log-bin = /var/lib/mysql/mysql-bin
    replicate-do-db = testdb




    mysql@master> GRANT replication slave ON "testdb".* TO "replication"@"192.168.1.102" IDENTIFIED BY "password";


    root@master# service mysqld restart


    mysql@master> SHOW MASTER STATUS\G
    File: mysql-bin.000003
    Position: 98
    Binlog_Do_DB:
    Binlog_Ignore_DB:



    Replica settings

    Specify the server ID, the name of the database for replication, and the path to the relay binlogs 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


    Transfer data

    Here we have to lock the database for recording. To do this, you can either stop the applications or use the read_only flag on the wizard (note: this flag does not work for users with the SUPER privilege). If we have MyISAM tables, we will also make " flush tables ": Let's look at the state of the wizard with the command "show master status" and remember the values ​​of File and Position (after the wizard is successfully locked, they should not change): Finally, we start replication with the commands " change master to " and " start slave " and see if everything went well: The values ​​of MASTER_LOG_FILE and MASTER_LOG_POS we take from the wizard. Let's see how the replication with the "
    mysql@master> FLUSH TABLES WITH READ LOCK;
    mysql@master> SET GLOBAL read_only = ON;



    File: mysql-bin.000003
    Position: 98

    Делаем дамп БД, и после завершения операции снимаем блокировку мастера:

    mysql@master> SET GLOBAL read_only = OFF;

    Переносим дамп на реплику и восстанавливаем из него данные.


    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;



    show slave status ": I highlighted the most interesting values ​​now. When replication starts successfully, 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 if the replica will follow the master (the log number in Master_Log_File and the position Exec_Master_Log_Pos will increase), the lag time of the replica from the master (Seconds_Behind_Master) should ideally be zero. If it does not decrease or grow, it is possible that the load on the replica too tall - she just does not have time to repeat the changes that occur on the wizard.
    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




    If Slave_IO_State is empty and Seconds_Behind_Master is NULL, replication has not started. See the MySQL log for a reason, fix it, and restart replication:
    mysql @ replica> start slave;

    Through these simple actions we get a replica whose data is identical to the data on the master.
    By the way, the wizard lock time is the time to create the dump. If it is created unacceptably long, you can try to do this:
    • block the write to the master with the read_only flag, remember the position and stop MySQL.
    • after that, copy the database files to the replica and turn on 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 a replica, and we need to add one more to them. This is even easier than adding the first replica to the wizard. And much nicer is that there is no need to stop the master for this.
    First, configure MySQL on the second replica and make sure that we have made the necessary parameters in the config: Now we will stop replication on the first replica: 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 wizard to which the replica reached before stopping replication: We need the values ​​Master_Log_File and Exec_Master_Log_Pos: Create a database dump and continue replication on the first replica: Restore the data from the dump on the second replica. Then enable replication:
    server-id = 3
    replicate-do-db = testdb



    mysql@replica-1> stop slave;


    mysql@replica-1> SHOW SLAVE STATUS\G


    Master_Log_File: mysql-bin.000004
    Exec_Master_Log_Pos: 155



    mysql@replica-1> START SLAVE;


    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 values ​​MASTER_LOG_FILE and MASTER_LOG_POS 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 at which the first replica was stopped (and, accordingly, a dump was created). Thus, we will have two replicas with identical data.

    Merging Replicas


    Sometimes this situation arises: 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 without shutting it down? Simple enough using the " start slave until " command .
    So, we have master with the testdb1 and testdb2 databases, which are replicated on replica-1 and replica-2 replicas, respectively . Set up replication of both databases on replica-1 without stopping the wizard.
    We stop replication on replica-2 with the command and remember the position of the wizard: Create a dump of the testdb2 database and resume replication (the manipulations with replica-2 ended here). Restore the dump to replica-1.
    mysql@replica-2> STOP SLAVE;
    mysql@replica-2> SHOW SLAVE STATUS\G
    Master_Log_File: mysql-bin.000015
    Exec_Master_Log_Pos: 231




    The situation on replica-1 is this: the testdb1 database is in one position of the master and continues to replicate, the testdb2 database is restored from the dump from another position. We will synchronize them.

    Stop replication and remember the position of the master: Exec_Master_Log_Pos: 501 Ensure that the config on replica-1 in section [mysqld] Specified name second database: Restart MySQL, to change in configuration to take effect. By the way, you could just restart MySQL without stopping replication - from the log we would find out what position of the wizard replication stopped at. Now let's replicate from the position where replica-2 was suspended to the position where we just stopped replication:
    mysql@replica-1> STOP SLAVE;
    mysql@replica-1> SHOW SLAVE STATUS\G
    Master_Log_File: mysql-bin.000016




    replicate-do-db = testdb2




    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 of our databases will correspond to the same position of the wizard (where we stopped replication on replica-1). Make sure of this: Restart MySQL and continue replication: After replica-1 catches up with the master, the contents of their database will be identical. You can merge the database on replica-2 either in a similar way or by making a complete dump of replica-1.
    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

    Важно: каждая БД должна быть указана на отдельной строке.


    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;


    Castling master and replica


    It may be necessary to switch the replica to the wizard mode, for example, in the event of a wizard failure or during technical work on it. To enable such a switch, you must configure the replica like a master, or make it a passive master .

    We enable binary logging (in addition to relay binlogs) in the config in the [mysqld] section:
    log-bin = /var/lib/mysql/mysql-bin

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

    passive wizard replicates like a regular replica, but in addition it creates binary logics - that is, we can start replication with him. We will verify this with the command " show master status ":
    mysql@replica> SHOW MASTER STATUS\G
    File: mysql-bin.000001
    Position: 61
    Binlog_Do_DB:
    Binlog_Ignore_DB:


    Now, to put the passive master in active mode, you need to stop replication on it and enable replication on the former active master. To prevent data from being lost at the time of switching, the active master must be locked for writing. That's all, so we changed the active master. You can remove the lock from the former master.
    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;



    Conclusion



    We figured out a bit how to configure replication in MySQL and perform some basic operations. Unfortunately, the following important questions remained outside the scope of the article:
    • elimination of single points of failure (SPF, Single Points of Failure). When using a single MySQL server, its failure led to the failure of the entire system. When using multiple servers, the failure of any of them will lead to the failure of the system, unless we specifically take care of this. We need to provide for handling the situation with the failure of the master and the replica. One of the existing tools - MMM , however, needs to be finalized with a file.
    • load balancing. When using multiple replicas, it would be convenient for us to use a transparent balancing mechanism, especially if the replica performance is not the same. Under Linux, it is possible to use the standard solution - LVS .
    • changing the logic of the application. In an ideal situation, requests to read data should be directed to replicas, and to change - to the master. However, due to the possible lag of replicas, such a scheme is often inoperative and it is necessary to identify such read requests that still need to be executed on the wizard.

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

    Also popular now: