MySQL Series 13 MySQL Replication

MySQL Series 13 MySQL Replication

1. MySQL replication related concepts

  • Master-slave replication: The master node synchronizes data to multiple slave nodes
  • Cascading replication: The master node synchronizes data to a slave node, and other slave nodes replicate data to the slave node
  • Synchronous replication: The replication strategy that returns data to the user only after all data is synchronized from the master node to the slave node is called synchronous replication.
  • Asynchronous replication: As soon as the data is written to the primary node, it is immediately returned to the user to complete the synchronization
  • Read-write separation: Add a scheduler at the front end to schedule statements that change data and statements that query data separately, schedule write operations to the master node, and schedule read operations to the slave node

Master Node:

  • Dump Thread: Start a dump thread for each Slave I/O Thread to send binary log events to it

From the node:

  • I/O Thread: Requests binary log events from the Master and saves them in the relay log
  • SQL Thread: Reads log events from the relay log and replays them locally

Files related to the copy function:

  • master.info: used to save relevant information when the slave connects to the master, such as account, password, server address, etc.
  • relay-log.info: stores the correspondence between the current binary log that has been copied on the current slave node and the local replay log

Copy the schema:

  • One master and one slave
  • One master, many slaves
  • Master-Master Replication
  • Circular replication
  • Cascading replication
  • Multiple masters and one slave

Common architectures include master-slave architecture or cascade architecture

2. Simple one-master-one-slave architecture implementation

1. New database builds master-slave architecture

​1) Main server configuration

~]# vim /etc/my.cnf
    [mysqld]
    log_bin
    binlog_format=ROW
    log-basename=master1
    server_id=1
~]# systemctl restart mariadb
~]#mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass'; #Authorize synchronization account MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| master1-bin.000001 | 26756 |
| master1-bin.000002 | 921736 |
| master1-bin.000003 | 401 | #Record this location and start syncing from the server +--------------------+-----------+

2) From the server configuration

~]# vim /etc/my.cnf
    [mysqld]
    server_id=2 #server ID unique relay_log=relay-log
    relay_log_index=relay-log.index
    read_only=ON
~]# systemctl restart mariadb
~]#mysql
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.7', #Specify the master node IP
    -> MASTER_USER='testuser', #Username of the synchronization user-> MASTER_PASSWORD='testpass', #Password-> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master1-bin.000003', #File recorded above-> MASTER_LOG_POS=401, #Position-> MASTER_CONNECT_RETRY=10; #Retry time 10 secondsMariaDB [(none)]> START SLAVE; #Start master-slave replication

3) Testing

Generate some data on the master node:
MariaDB [(none)]> CREATE DATABASE testdb;
MariaDB [(none)]> use testdb
MariaDB [testdb]> create table testlog (id int auto_increment primary key,name char(30),age int default 20);
MariaDB [testdb]> delimiter $$
MariaDB [testdb]> create procedure pro_testlog()
    -> begin
    -> declare i int;
    -> set i = 1;
    -> while i < 100000
    -> do insert into testlog(name,age) values ​​(concat('testuser',i),i);
    -> set i = i +1; 
    -> end while;
    -> end$$
MariaDB [testdb]> delimiter;
MariaDB [testdb]> START TRANSACTION;
MariaDB [testdb]> CALL pro_testlog;
MariaDB [testdb]> COMMIT;
Check the synchronization status on the slave node:
MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
+----------+
| COUNT(*) |
+----------+
| 99999 | #Synchronization successful+----------+
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ****************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.7
                  Master_User: testuser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master1-bin.000003
          Read_Master_Log_Pos: 10389814
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 10389944
        Relay_Master_Log_File: master1-bin.000003
             Slave_IO_Running: Yes #IO thread has been started Slave_SQL_Running: Yes #SQL thread has been started Seconds_Behind_Master: 0 #Time difference between master and slave replication Master_Server_Id: 1

2. Add a new slave server to the old database

​1) Main server configuration

~]# vim /etc/my.cnf
    [mysqld]
    log_bin
    binlog_format=ROW
    log-basename=master1
    server_id=1
~]# systemctl restart mariadb
~]# mysqldump -A -F --single-transaction --master-data=1 > full.sql
~]# scp full.sql [email protected]:/root/
~]# mysql -e 'GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass';'

2) From the server configuration

~]# vim /etc/my.cnf
    [mysqld]
    server_id=2
    relay_log=relay-log   
    relay_log_index=relay-log.index
    read_only=ON
~]# systemctl restart mariadb
~]# vim full.sql #Add the following information to the backup SQL file CHANGE MASTER TO
    MASTER_HOST='192.168.0.7',
    MASTER_USER='testuser',
    MASTER_PASSWORD='testpass',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='master1-bin.000005',
    MASTER_LOG_POS=245,
    MASTER_CONNECT_RETRY=10;
~]# mysql < full.sql #Configuration has been completed while importing SQL MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
+----------+
| COUNT(*) |
+----------+
| 99999 |
+----------+
MariaDB [(none)]> START SLAVE; #Start replication

3. Cascading replication architecture implementation

1) Master Node

[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_format=ROW
log-basename=master
server_id=1
[root@master ~]# systemctl restart mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+----------+
| Log_name | File_size |
+-------------------+----------+
| master-bin.000001 | 26753 |
| master-bin.000002 | 921736 |
| master-bin.000003 | 401 |
+-------------------+----------+

2) Slave Node

[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
log_bin #Note that in the cascade architecture, the relay slave node must have the binary log function binlog_format=ROW
read_only=ON
server_id=2
log_slave_updates #This is the key, its function is to record the data changes from the service into the binary log file relay_log=relay-log
relay_log_index=relay-log.index
[root@slave1 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000003',
    -> MASTER_LOG_POS=401,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';

3) Slave nodes of slave nodes

[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=3
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave2 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.8',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;

4) Slave node 2 of the slave node

[root@slave3 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=4
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave3 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.8',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
Now that cascade replication has been built, let's test it.

4. Master-Master Replication Architecture

Problems that may occur: Data inconsistency, so use with caution; Key points to consider: Automatically increase id
Configure a node to use an odd id
auto_increment_offset=1 starting point
auto_increment_increment=2 increment rate Another node uses an even id
auto_increment_offset=2
auto_increment_increment=2

1) Main 1

[mysqld]
log_bin
binlog_format=ROW
log-basename=master1
server_id=1
relay_log=relay-log
relay_log_index=relay-log.index
auto_increment_offset=1 #The self-increment field starts from 1 auto_increment_increment=2 #Increase by 2 each time, that is, the id field of the data written by the master1 node is all odd numbers [root@master ~]# systemctl start mariadb
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| master1-bin.000001 | 27033 |
| master1-bin.000002 | 942126 |
| master1-bin.000003 | 245 |
+--------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.8',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master2-bin.000003',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;

2) Main 2

[mysqld]
log_bin
binlog_format=ROW
log-basename=master2
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
auto_increment_offset=2 #The self-increment field starts from 1 auto_increment_increment=2 #Increase by 2 each time, that is, the id fields of the data written by the master1 node are all even numbers [root@master2 ~]# systemctl start mariadb
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| master2-bin.000001 | 27036 |
| master2-bin.000002 | 942126 |
| master2-bin.000003 | 245 |
+--------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master1-bin.000003',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;

3) Testing

Create a table on master1 and add data MariaDB [(none)]> CREATE DATABASE db1;
MariaDB [(none)]> use db1
MariaDB [db1]> CREATE TABLE t1(id INT(2) AUTO_INCREMENT PRIMARY KEY,name CHAR(30));
MariaDB [db1]> INSERT t1(name) VALUES ('tom');
MariaDB [db1]> INSERT t1(name) VALUES ('maria'); 
MariaDB [db1]> SELECT * FROM t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 3 | maria |
+----+-------+
Add data on master2MariaDB [db1]> INSERT t1(name) VALUES ('jerry');
MariaDB [db1]> INSERT t1(name) VALUES ('tony'); 
MariaDB [db1]> SELECT * FROM t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 3 | maria |
| 4 | jerry |
| 6 | tony |

5. Implementation of semi-synchronous replication

By default, MySQL's replication function is asynchronous, which can provide the best performance. The master database ends when it sends the binlog log to the slave database, and does not verify whether the slave database has received it. This means that when a failure occurs on the master or slave server, the slave server may not receive the binlog log sent by the master server, which will cause data inconsistency between the master and slave servers, and even cause data loss during recovery; the mechanism of semi-synchronous replication is that only when the master node and the slave node are synchronized, only one of them will complete the synchronization and return the write completion. This mechanism ensures data security.

1) Master Node

[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_format=ROW
log-basename=master
server_id=1
relay_log=relay-log
relay_log_index=relay-log.index
[root@master ~]# systemctl restart mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+----------+
| Log_name | File_size |
+-------------------+----------+
| master-bin.000001 | 26753 |
| master-bin.000002 | 921736 |
| master-bin.000003 | 401 |
+-------------------+----------+
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #Install module MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1; #Enable semi-synchronous function MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON | #Enabled | rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+

2) From Node 1

[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
log_bin
binlog_format=ROW
log-basename=slave
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave1 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000003',
    -> MASTER_LOG_POS=401,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| slave-bin.000001 | 26753 |
| slave-bin.000002 | 921736 |
| slave-bin.000003 | 245 |
+------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';

3) From Node 2

[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=3
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave2 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.8',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='slave-bin.000003',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;

4) From Node 3

[root@slave3 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=4
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave3 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.8',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='slave-bin.000003',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;

6. Implementation of Encrypted Transmission and Replication

In the default master-slave replication process or when connecting to MySQL/MariaDB remotely, all data in the link communication is in plain text. Accessing data or replicating data in the external network poses a security risk. The method of copying through SSL/TLS encryption can further improve the security of data.

Enable SSL on the main server: [mysqld] Add a line of ssl
Configure the master server with a certificate and private key; create a replication account that requires an SSL connection. Specify SSL-related options when using the CHANGER MASTER TO command from the slave server. 

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+----------+

Special note: Before configuration, check whether the MySQL service supports the SSL function. If the value of have_ssl is 'DISABLED', it supports it; if it is 'NO', it does not support it and needs to be recompiled and installed or a version with SSL function is installed.

1) CA

[root@CA ~]# mkdir /etc/my.cnf.d/ssl/
[root@CA ~]# cd /etc/my.cnf.d/ssl/
[root@CA ssl]# openssl genrsa 2048 > cakey.pem
[root@CA ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 #Self-signed certificate Country Name (2 letter code) [XX]:CN
    State or Province Name (full name) []:beijing
    Locality Name (eg, city) [Default City]:beijing
    Organization Name (eg, company) [Default Company Ltd]:testmysqlca 
    Organizational Unit Name (eg, section) []:opt
    Common Name (eg, your name or your server's hostname) []:ca.testmysqlca.com

[root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr
    Country Name (2 letter code) [XX]:CN
    State or Province Name (full name) []:beijing
    Locality Name (eg, city) [Default City]:beijing
    Organization Name (eg, company) [Default Company Ltd]:testmysqlca
    Organizational Unit Name (eg, section) []:opt
    Common Name (eg, your name or your server's hostname) []:master.testmysqlca.com
[root@CA ssl]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt #Sign master certificate [root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr
    Country Name (2 letter code) [XX]:CN
    State or Province Name (full name) []:beijing
    Locality Name (eg, city) [Default City]:beijing
    Organization Name (eg, company) [Default Company Ltd]:testmysqlca 
    Organizational Unit Name (eg, section) []:opt
    Common Name (eg, your name or your server's hostname) []:slave.testmysqlca.com
[root@CA ssl]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt #Sign slave certificate [root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave2.key > slave2.csr
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:testmysqlca
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server's hostname) []:slave2.testmysqlca.com
[root@CA ssl]# openssl x509 -req -in slave2.csr -CA cacert.pem -CAkey cakey.pem -set_serial 03 > slave2.crt #Sign slave2 certificate [root@CA ssl]# openssl verify -CAfile cacert.pem master.crt slave.crt slave2.crt #Check if the certificate is available master.crt: OK
slave.crt: OK
slave2.crt: OK
First, create the /etc/my.cnf.d/ssl/ folder on each node and copy the respective certificates, CA certificates and respective key files to it. [root@CA ssl]# scp cacert.pem master.crt master.key [email protected]:/etc/my.cnf.d/ssl/
[root@CA ssl]# scp cacert.pem slave.crt slave.key [email protected]:/etc/my.cnf.d/ssl/ 
[root@CA ssl]# scp cacert.pem slave2.crt slave2.key [email protected]:/etc/my.cnf.d/ssl/

2) master

[root@master ~]# mkdir /etc/my.cnf.d/ssl/
[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_format=ROW
log-basename=master
server_id=1
ssl #Enable SSL function ssl-ca=/etc/my.cnf.d/ssl/cacert.pem #Specify the path of CA certificate ssl-cert=/etc/my.cnf.d/ssl/master.crt #Specify the path of your own certificate ssl-key=/etc/my.cnf.d/ssl/master.key #Specify your own key file path [root@master ~]# systemctl restart mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass' REQUIRE SSL; #Authorize users and force users to enable SSL login MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+----------+
| Log_name | File_size |
+-------------------+----------+
| master-bin.000001 | 26753 |
| master-bin.000002 | 921736 |
| master-bin.000003 | 413 |
+-------------------+----------+

3) slave1

[root@slave1 ~]# mkdir /etc/my.cnf.d/ssl/
[root@slave1 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave.crt
ssl-key=/etc/my.cnf.d/ssl/slave.key
[root@slave1 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000003',
    -> MASTER_LOG_POS=413,
    -> MASTER_CONNECT_RETRY=10,
    -> MASTER_SSL=1; #Note that you need to specify to enable SSL connection MariaDB [(none)]> START SLAVE;

4) slave2

[root@slave2 ~]# mkdir /etc/my.cnf.d/ssl/
[root@slave2 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave2.crt --ssl-key=/etc/my.cnf.d/ssl/slave2.key
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=3
relay_log=relay-log
relay_log_index=relay-log.index
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave2.crt
ssl-key=/etc/my.cnf.d/ssl/slave2.key
[root@slave2 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000003',
    -> MASTER_LOG_POS=413,
    -> MASTER_CONNECT_RETRY=10,
    -> MASTER_SSL=1;
MariaDB [(none)]> START SLAVE;

7. Summary of relevant instructions and variables for MySQL replication

Options:

  • log_bin enables binary logging, which must be enabled on the master node or the slave node in the cascading replication
  • binlog_format=ROW Binary log recording is row-based, it is strongly recommended to enable
  • log-basename=master | slave ... The prefix name of the binary log, not required, but recommended
  • server_id = # Server ID. Each node's ID must be unique.
  • relay_log = relay-log turns on the relay log and starts the file name with relay-log.
  • relay_log_index = relay-log.index relay log index file
  • log_slave_updates is used to record the operation of changing data as a binary log when the SQL thread rereads the relay log. It is used in cascading replication.
  • ssl Enable ssl function
    • ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
    • ssl-cert=/etc/my.cnf.d/ssl/master.crt
    • ssl-key=/etc/my.cnf.d/ssl/master.key
  • sync_binlog=1 Synchronize binary log to disk immediately after each write
    • innodb_flush_log_at_trx_commit=1 Each transaction commit immediately synchronizes the log to disk
    • sync_master_info=# #After the event, master.info is synchronized to disk
  • skip_slave_start=ON Do not automatically start slave
  • sync_relay_log=# #Synchronize relay log to disk after writing
  • sync_relay_log_info=# #Synchronize relay-log.info to disk after the transaction
  • auto_increment_offset=1 Automatic growth start point, used in master-master replication

variable:

  • replicate_do_db= specifies the whitelist of replication databases
  • replicate_ignore_db=Specify replication database blacklist
  • replicate_do_table= specifies a whitelist of replication tables
  • replicate_ignore_table= Specifies the blacklist of replication tables
  • replicate_wild_do_table = foo%.bar% supports wildcards
  • replicate_wild_ignore_table= Specify the replicated table, blacklist
  • rpl_semi_sync_slave_enabled=1 Enable semi-synchronous replication, which requires the installation of modules

instruction:

  • START SLAVE; Start master-slave replication
  • STOP SLAVE; Stop replication
  • SHOW SLAVE STATUS; View the replication status
    • Seconds_Behind_Master: 0 Whether the slave server lags behind the master server
  • RESET SLAVE ALL; Reset the configuration of the slave server
  • MASTER_SSL=1, used with CHANGE MASTER TO to enable SSL encrypted replication
    • MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
    • MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
    • MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
  • PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } Delete binary logs. Be careful.
  • SHOW MASTER STATUS View the binary log status
  • SHOW BINLOG EVENTS View binary logs
  • SHOW BINARY LOGS View binary logs

This is the end of this article about MySQL series 13 MySQL replication. For more information about MySQL replication, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL5.7 parallel replication principle and implementation
  • Detailed explanation of MySQL master-slave replication and read-write separation
  • Common repair methods for MySQL master-slave replication disconnection
  • Analysis of three parameters of MySQL replication problem
  • Comprehensive analysis of MySql master-slave replication mechanism

<<:  Implementation of Nginx+ModSecurity security module deployment

>>:  What does it mean to add a question mark (?) after the CSS link address?

Recommend

MySQL advanced learning index advantages and disadvantages and rules of use

1. Advantages and Disadvantages of Indexes Advant...

Chrome plugin (extension) development guide (complete demo)

Table of contents Written in front Preface What i...

Use javascript to create dynamic QQ registration page

Table of contents 1. Introduction 1. Basic layout...

A few front-end practice summaries of Alipay's new homepage

Of course, it also includes some personal experien...

Detailed explanation of the case of dynamically generating tables using JavaScript

Table of contents Preface Implementation ideas Im...

Summary of knowledge points about events module in Node.js

Through the study and application of Node, we kno...

The magic of tr command in counting the frequency of English words

We are all familiar with the tr command, which ca...

Detailed explanation of how to introduce custom fonts (font-face) in CSS

Why did I use this? It all started with the makin...

VMware12 installs Ubuntu19.04 desktop version (installation tutorial)

1. Experimental description In the virtual machin...

Introduction to Linux system swap space

Swap space is a common aspect of computing today,...

12 Laws of Web Design for Clean Code [Graphic]

Beautiful code is the foundation of a beautiful we...

Basic understanding and use of HTML select option

Detailed explanation of HTML (select option) in ja...

Zabbix configures DingTalk's alarm function with pictures

Implementation ideas: First of all, the alarm inf...

JavaScript adds prototype method implementation for built-in objects

The order in which objects call methods: If the m...

Native js to realize the upload picture control

This article example shares the specific code of ...