MySQL Replication (MySQL Master-Slave) – solution to backup MySQL realtime and reduce load MySQL

MySql replication is the process of creating the availability of the same database on some different remote location. The databases are always syn with each other. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database. Replication can be Mater-slave or Master-Master.

In the Master-slave model, you can replicate from the master server to replicas. Replicas are updated asynchronously using the MySQL engine’s native binary log (binlog) file position-based replication technology. All writes happen on master only and replicas are read-only.

In Master-Master model both servers have the rights to read-write.

In the above figure, clients will read data from MySQL slaves, in this case, we have a lot of MySQL slaves, so we need a load balancer in front to share traffic between slaves. All write operations will be executed on MySQL master database, then data will be replicated to slaves.

Why use MySQL replication?

MySQL Replication can be very useful as:

• High availability, realtime database backups, improved reliability: If one system goes down due to faulty hardware, malware attack, or another problem, the data can be accessed from a different node.
• Reducing load with load-balancing mechanism: Having the same data in multiple locations can lower data access latency, since required data can be retrieved closer to where the transaction is executing.
• Increased data analytics support: Replicating data to a data warehouse empowers distributed analytics teams to work on common projects for business intelligence. You won’t worry about damaging database or interrupting service because data analyst only work on slave database.

How does MySQL replication work?

In Master-Slave MySQL replication you need at least 2 servers, one for master and another for slave.

The master is the primary database server from which data is copied, where all database updates occur, from adding, updating, deleting records to creating functions, stored procedures, etc.

The slave server is one or more servers that data is copied from the master server.

The replicate process will be executed as follows:

1. Connection to master db instance will open a Session_Thread. Session_Thread will write SQL statements to a binary log file. Binary log file is maintained by master database and saved in data_dir (configured by my.cnf) with name like this: mysql-bin.000001
2. This binary log file is a record of all the events that modified the database and the data in it. It records only the write events and not the read event like SELECT.
3. The replica when connected to the master instance, requests a copy of the binary log file via I/O_Thread
4. Master database open Dump_Thread and send binary log to I/O_Thread of replica.
5. The replica then apply the binary log to its relay-log.
6. The relay log is read by the SQL_Thread process and it applies all the operations/data to the replica database and its binary log.

All of the above steps will be illustrated by below diagram

MySQL Replication Events

There are two types replication events:

• Statement based: these are the write operations. They written to the binary log as SQL statements.
• Row based: these are the row changes and they are written to the binary log as complete copies of the rows.

On the master

As updates occur, the events are written sequentially to the binary log for the slave to read later. Writes on the master are actually buffered to improve performance for when the slave is reading them as well as being written to the binary log.

The binlog dump thread  when the slave connects to the master, the master opens up a thread for the connection from the slave. This thread is just like any other thread however the difference will be that it will be typically opened using a dedicated user set up for the replication process and it just handles notifying the slave of changes and sending the binary log contents to it as it connects.

On the slave

When replication is started, two threads are created on the slave:

The I/O thread:  When you call START SLAVE on a slave server, the slave creates this thread which connects to the master and requests a copy of the master’s binary log.

The SQL thread: this thread reads the relay log (written by the IO thread) and applies the changes to the database as quickly as possible.

If you want to check the status of the IO thread, run “show slave status\G” on the slave:

• Master_Log_File:  the last binary log file copied from the master.
• Read_Master_Log_Pos: the binary log from the master is copied over to the relay log on the slave up to the position listed.

If you want to see the status of the SQL thread on the slave, again using “show slave status\G”

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.100.101
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1061
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000004
             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: 1061
              Relay_Log_Space: 533
              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: 1
                  Master_UUID: cbaf3d1a-2261-11ec-b14c-0800274fb032
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

• Relay_Master_Log_File: this is the relay log (binary log from the master) that SQL thread is currently processing.
• Exec_Master_Log_Pos: the position in the binary log currently being executed.

Replication lag

Replication lag occurs when the slaves cannot keep up with the updates occurring on the master. Unapplied changes accumulate in the slaves’ relay logs and the version of the database on the slaves becomes increasingly different from that of the master.

Replication lag is caused when either the I/O Thread or SQL Thread cannot cope with the demands placed upon it. If the I/O Thread is suffering, this means that the network connection between the master and its slaves is slow. You might want to consider enabling the slave_compressed_protocol to compress network traffic or speaking to your network administrator.

The solution to detect Replication lag is use the command “SHOW MASTER STATUS\G” and “SHOW SLAVE STATUS\G”. Both two commands will give you the reason cause of the error.

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 1967
     Binlog_Do_DB: replicadb
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)