MySQL Master-Slave Synchronization-Principle-Practice

What is mysql's master-slave replication?

MySQL master-slave replication refers to the replication of data from one MySQL database server master node to one or more slave nodes.MySQL replicates asynchronously by default, so that the master server is not always accessed by the slave node to update its own data, which can be updated over a remote connection, and the slave node can replicate all databases in the master database or specific databases, or specific tables.

Mysql Replication Principle

Principle:

(1) The Master server records changes to the data in a binary Binlog log, and writes the changes to the binary log when the data on the Master changes;

(2) The Slave server detects whether the Master binary log has changed over a certain time interval and, if so, begins an I/OThread request for Master binary events

(3) At the same time, the primary node starts a Dump thread for each I/O thread, which sends binary events to it and saves them to the relay log local to the node. The SQL thread from the node will start reading the binary log from the relay log, replaying it locally so that its data is consistent with the primary node. Finally, I/OThread and SQLThread will go to sleep and wait for the next timeWake up.

Principle analysis of Undo log and Redo log

Undo log principle

Undo log rolls back all transactions without COMMIT to the state before the transaction started. When the system crashes, some transactions may not have COMMIT. When the system recovers, these transactions without COMMIT need to be rolled back with Undo log.

Require when using Undo log:

1. When recording the change log (Redo log), (T, x, v) V is the value before the change of x, so that you can roll back with this log;
2. After the transaction is committed, all changes to the transaction, including the recorded change log, must be persisted before the COMMIT log can be written. This ensures that all changes to the transaction that has COMMIT are persisted and do not need to be rolled back when the downtime resumes. 

Transaction execution order when using Undo log

1. Record START T 
2. Record the old value of the record that needs to be modified (requires persistence)
3. Update the database as required by the transaction (requires persistence)
4. Record COMMIT 

Downtime rollback using Undo log

1. Scan the logs to find all transactions that have START but not COMMIT.
2. Rollback based on Redo log for all non-COMMIT logs. 

If the database is accessed a lot, the log volume will be large, and rollback will be heavy when the downtime is restored. To speed up rollback, you can use the Checkpoint mechanism to speed up rollback.

Scan Undo log from back to front
 1. If checkpoint_start is encountered first, all uncommitted transactions after checkpoint_start are rolled back;
2. If checkpoint_end is encountered first, all uncommitted transactions after the previous checkpoint_start are rolled back; (There may be many new transaction STARTs or COMMITs during the checkpoint process). 

With Undo log, requiring Redo log and all modifications to transactions to be persisted when writing COMMIT logs can often affect performance.

Redo log principle

Redo log refers to the process of replaying a COMMIT transaction while playback of the log, and doing nothing for a transaction without COMMIT as abort.

When using Redo log, the following are required:

1. When recording a Redo log, the V in (T,x, v) must be the modified value of x, otherwise the COMMIT transaction cannot be restored through Redo log.
2. Transaction modifications cannot be persisted until COMMIT T T logs are written. Otherwise, when recovery occurs, data may have been modified for operations that do not have COMMIT, but replaying Redo log s does not handle the transaction at all, thus the atomicity of the transaction cannot be guaranteed. 

Order of transaction execution when using Redo log

1. Record START T
 2. Recording transactions requires modifying new values of records (requires persistence)
3. Record COMMIT (requires persistence)
4. Write transaction-related modifications to the database 

Redo log Redo

1. Scan the logs to find all COMMIT transactions;
2. For transactions that already have COMMIT, redo them according to Redo log;

Accelerate recovery based on Checkpoint

Scan Redo log from back to front
 1. If checkpoint_start is encountered first, all COMMIT transactions after T1~Tn and checkpoint_start are redone;
2. If checkpoint_end is encountered first, all COMMIT transactions since T1~Tn and the previous checkpoint_start are redone; 

Similar to Undo log, both persistence and transaction order are required in use, and they can be used together. Redo log is used to redo COMMIT-enabled transactions in recovery, and Undo log is used to rollback transactions without COMMIT.When Redo/Undo log is used in combination, it is required to record both the pre-modified and modified values of the operation, such as (T, x, v, w), V as the pre-modified value of x, and w as the modified value of x, in the order of operation:

1. Record START T
 2. Record change logs (T, x, v, w) (requires persistence, where V is for undo and W is for redo)
3. Update the database
 4. Record COMMIT 

Actual Operations

Previous article has done some initial practice on Binlog setup: http://www.cyblogs.com/mysql-binlogshe-zhi/ Or start two containers locally using Docker.

➜  ~  docker ps -a
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS                     NAMES
662e8531eb70        centos:7            "/bin/bash"         2 hours ago         Up 2 hours          0.0.0.0:33062->3306/tcp   docker-mysql-slave
c738746e9623        centos:7            "/bin/bash"         4 hours ago         Up 4 hours          0.0.0.0:33061->3306/tcp   docker-mysql-master

One is docker-mysql-master as the master node, docker-mysql-slave as the slave node, and finally a master-slave synchronization function is implemented.

Master Node

Set up slave_account account
[root@c738746e9623 bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.45-log MySQL Community Server (GPL)
mysql> grant replication slave on *.* to 'slave_account'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
my.cnf of Master Node
[root@c738746e9623 bin]# cat /etc/my.cnf
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
socket = /tmp/mysql.sock

server-id = 1
log-bin=mysql-bin

binlog-ignore-db = mysql
binlog-ignore-db = information_schema

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
View master node status
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000002 |      120 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

Slave Node

Slave node my.cnf
[root@662e8531eb70 mysql]#cat /etc/my.cnf
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
socket = /tmp/mysql.sock

server-id = 2

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Configure the configuration synchronized with the primary node
mysql> change master to master_host='172.17.0.2',master_user='slave_account',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
Start Synchronization
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
View the status of a master-slave synchronization
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: slave_account
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: 662e8531eb70-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000002
             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: 120
              Relay_Log_Space: 463
              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: 7323857e-254b-11ea-9b62-0242ac110002
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

Master node writes data

mysql> CREATE TABLE `person_01` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `first_name` varchar(20) DEFAULT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   `gender` char(1) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| person         |
| person_01      |
+----------------+
2 rows in set (0.01 sec)

mysql> INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (1, 'Bob', 25, 'M');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (2, 'Jane', 20, 'F');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (3, 'Jack', 30, 'M');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (4, 'Bill', 32, 'M');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (5, 'Nick', 22, 'M');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (6, 'Kathy', 18, 'F');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (7, 'Steve', 36, 'M');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (8, 'Anne', 25, 'F');
Query OK, 1 row affected (0.00 sec)

mysql> select * from person_01;
+------+------------+------+--------+
| id   | first_name | age  | gender |
+------+------------+------+--------+
|    1 | Bob        |   25 | M      |
|    2 | Jane       |   20 | F      |
|    3 | Jack       |   30 | M      |
|    4 | Bill       |   32 | M      |
|    5 | Nick       |   22 | M      |
|    6 | Kathy      |   18 | F      |
|    7 | Steve      |   36 | M      |
|    8 | Anne       |   25 | F      |
+------+------------+------+--------+
8 rows in set (0.01 sec)
mysql> exit
Bye
[root@c738746e9623 bin]# Primary Node

Slave Node Lookup Data

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| person_01      |
+----------------+
1 row in set (0.00 sec)

mysql> select * from person_01;
+------+------------+------+--------+
| id   | first_name | age  | gender |
+------+------------+------+--------+
|    1 | Bob        |   25 | M      |
|    2 | Jane       |   20 | F      |
|    3 | Jack       |   30 | M      |
|    4 | Bill       |   32 | M      |
|    5 | Nick       |   22 | M      |
|    6 | Kathy      |   18 | F      |
|    7 | Steve      |   36 | M      |
|    8 | Anne       |   25 | F      |
+------+------------+------+--------+
8 rows in set (0.00 sec)
mysql> exit
Bye
[root@662e8531eb70 mysql]# From Node

This makes the simplest master-slave synchronization possible.Master-slave synchronization is only the most basic highly available architecture.

Reference Address

If you like my article, you can focus on your personal subscription number.Welcome to leave a message and exchange at any time.

Tags: Programming MySQL Database Docker CentOS

Posted on Wed, 15 Jan 2020 20:55:58 -0500 by Cinquecento