What is master-slave replication of mysql?
MySQL master-slave replication means that data can be copied from one MySQL database server master node to one or more slave nodes. MySQL adopts asynchronous replication by default, so that the slave node does not have to always access the master server to update its own data. The data can be updated on a remote connection. The slave node can copy all databases or specific databases or specific tables in the master database.
Mysql replication principle
Principle:
(1) The Master server records the changes of data in the binary Binlog log. When the data on the Master changes, the changes are written into the binary log;
(2) The Slave server will detect whether the Master binary log has changed within a certain time interval. If it has changed, it will start an I/OThread to request the Master binary event
(3) At the same time, the master node starts a Dump thread for each I/O thread to send binary events to it and save them to the local relay log of the slave node. The slave node will start the SQL thread to read the binary log from the relay log and replay it locally to make its data consistent with that of the master node. Finally, the I/OThread and SQLThread will enter the sleep state, Wait for the next wake-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 starts. When the system crashes, some transactions may not have committed. When the system recovers, these transactions without COMMIT need to be rolled back with undo log.
When using Undo log:
1,When recording the modification log( Redo log)，(T，x，v)in v by x The value before modification can be rolled back with the help of this log; 2,After a transaction is committed, it can only be written after all modifications of the transaction (including the recorded modification log) are persisted COMMIT T Log; in this way, it can be ensured that when the downtime is restored, it has been COMMIT All modifications of the transaction have been persisted and do not need to be rolled back.
Transaction execution order when using Undo log
1,record START T 2,Record the old value of the record to be modified (persistence required) 3,Update the database according to the needs of the transaction (persistence is required) 4,record COMMIT T
Using Undo log for downtime rollback
1,Scan the log to find out all the START,Not yet COMMIT Transaction. 2,For all unsettled COMMIT Log, according to Redo log To roll back.
If there are many database accesses, the log volume will be large, and the workload of rollback will be large during downtime recovery. In order to speed up rollback, Checkpoint mechanism can be used to speed up rollback.
Scan from back to front Undo log 1,If you encounter it first checkpoint_start, Will checkpoint_start Roll back all uncommitted transactions; 2,If you encounter it first checkpoint_end, The previous checkpoint_start After that, all uncommitted transactions are rolled back; (in the checkpoint There may be many new transactions in the process of START perhaps COMMIT).
Using Undo log, when writing the COMMIT log, it is required that the Redo log and all modifications of the transaction must be persistent, which usually affects the performance.
Redo log principle
Redo log refers to redo the committed transactions when playing back the log. The transactions without COMMIT are processed according to abort without any operation.
When using Redo log, the following requirements are required:
1,record Redo log When,(T,x，v)Medium v Must be x The modified value, otherwise it cannot be passed Redo log To restore what has been COMMIT Transaction. 2,write COMMIT T Before logging, the modification of transactions cannot be persisted. Otherwise, when recovering, the transaction is not persisted COMMIT Data may have been modified but replayed Redo log The transaction will not be processed, so the atomicity of the transaction cannot be guaranteed.
Transaction execution order when Redo log is used
1,record START T 2,Record transaction needs to modify the new value of the record (persistence is required) 3,record COMMIT T(Require persistence) 4,Writes transaction related modifications to the database
Redo transactions using Redo log
1,Scan logs to find all COMMIT Affairs of the; 2,For already COMMIT Transactions, according to Redo log Redo the transaction;
Accelerate recovery based on Checkpoint
Scan from back to front Redo log 1，If you encounter it first checkpoint_start, Then put T1~Tn as well as checkpoint_start After all has been COMMIT Redo the transaction; 2. If you encounter it first checkpoint_end, be T1~Tn And the previous one checkpoint_start Since then, all have COMMIT Redo the transaction;
Similar to Undo log, it requires high persistence and transaction operation sequence when used. It can be used in combination. During recovery, Redo log is used for redo of committed transactions, and Undo log is used for rollback of uncommitted transactions. When Redo/Undo log is used in combination, it is required to record the values before and after modification, For example (T, x, v, w), v is the value before x modification and W is the value after X modification. The specific operation sequence is as follows:
1. record START T 2. Record modification log( T，x，v，w)(Persistence is required, where v be used for undo，w be used for redo) 3. Update database 4. record COMMIT T
Actual operation
The previous article has done some preliminary practice on Binlog settings: http://www.cyblogs.com/mysql-binlogshe-zhi/ , two containers were started locally by 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 to use docker MySQL master as the master node and docker MySQL slave as the slave node. Finally, a master-slave synchronization function is realized.
Master node
Set slave_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 master 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 write 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]# Master nodeSlave node query 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]# Slave node
In this way, the simplest master-slave synchronization is done. Master-slave synchronization is only the most basic high availability architecture.
Reference address
- https://blog.csdn.net/xuanxuan_good/article/details/54427154
- https://zhuanlan.zhihu.com/p/96212530