Implement primary/secondary replication of Mysql

This is the 8th day of my participation in the August Text Challenge.More challenges in August

The preparatory work

Configure the network

Using VMware, copy the master, Slave1, and Slave2 VMS

Changing an IP Address

# master: 192.168.200.11
# slave1:192.168.200.12
# slave2:192.168.200.13
Open the configuration file
vim /etc/sysconfig/network-scripts/ifcfg-ens33

# IP configuration method [none | static | the bootp | DHCP] (boot is not applicable to static assign IP protocol | | | the bootp protocol DHCP protocol)
BOOTPROTO="static"
# the IP addressIPADDR = 192.168.200.11# gatewayGATEWAY = 192.168.200.2# Domain name resolverDNS1 = 192.168.200.2Copy the code

Restarting the network service or the system takes effect

# Restart the network
service network restart
Restart the system
shutdown -r now 
Restart the system
reboot
# to check the IP
ifconfig
Copy the code

Mysql installation

Create a directory

#Create a directory
mkdir /opt/software
#Go to the directory
cd /opt/software
Copy the code

Use XFTP to upload mysql5.7 to this directory
Unpack the installation

#Unpack theTar XVF mysql - 5.7.26-1. El7. X86_64. RPM - bundle. The tarCopy the code

The principle of

When the Master node performs insert, UPDATE, and delete operations, they are written to the binary log in sequence.
Salve connects to the master library and creates as many binlog dump threads as the master has.
When the binlog of the Master node changes, the binlog dump thread notifies all salve nodes and pushes the corresponding binlog content to slave nodes.
After receiving the binlog content, the I/O thread writes the content to the local relay-log.
The SQL thread reads the relay log written by the I/O thread and acts on the slave database based on the relay log.

Knowing the principle of master/slave replication, we can also clearly know that the write operation of read/write separation must be carried out on the master node, because the salve node realizes data unification according to the binlog of the master node. If we carry out the write operation on the slave node and the read operation on the master node, The two data will not be unified, master – slave replication will lose its significance.

Primary instance Setup

Example Modify the mysql configuration file /etc/my.cnf

[mysqld]
Set server_id to be unique on the same LAN
server_id=11
## Specifies the database name that does not need to be synchronized
binlog-ignore-db=mysql
Enable the binary log function
log-bin=mall-mysql-bin
Set the size of memory used by binary logs (transactions)
binlog_cache_size=1M
# change the binary log format to use (mixed,statement,row)
binlog_format=mixed
## Clearing time of binary log expiration. The default value is 0, indicating that automatic clearing is not performed.
expire_logs_days=7
Skip all errors or specified types of errors encountered in master/slave replication to avoid replication interruption on slave.
Error 1032 is caused by data inconsistency between primary and secondary databases
slave_skip_errors=1062
Copy the code

After modifying the configuration, restart the instance:
```
service mysqld restart
Copy the code
```
Create a data synchronization user:

#Connecting to a Database
mysql -uroot -proot

#Example Create a data synchronization user
#Create a synchronization account for Slave1
CREATE USER 'slave1'@'192.168.200.12' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave1'@'192.168.200.12';
#Create a synchronization account for Slave2
CREATE USER 'slave2'@'192.168.200.13' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave2'@'192.168.200.13';
Copy the code

If not, mysql will return an error:

This is because a simple password setting can cause errors. MySQL has a password setting specification that is related to the value of validate_password_policy, as shown in the following figure

We can look at mysql’s initial password rules

SHOW VARIABLES LIKE 'validate_password%';
Copy the code

Modify the password authentication policy:

set global validate_password_policy=0;
Copy the code

The length of the password is related to validate_password_length. Let’s reduce the length of the password

set global validate_password_length=4;
Copy the code

That’s fine

Build from instance

Slave instance configuration is basically the same, here is only a slave instance to explain

Example Modify the mysql configuration file /etc/my.cnf

[mysqld]
Set server_id to be unique on the same LAN
Another slave instance can be set to 103
server_id=102  
## Specifies the database name that does not need to be synchronized
binlog-ignore-db=mysql
Enable binary logging in case Slave acts as Master of other database instances
# Another slave instance can be configured as mall-mysql-slave2-bin
log-bin=mall-mysql-slave1-bin 
Set the size of memory used by binary logs (transactions)
binlog_cache_size=1M
# change the binary log format to use (mixed,statement,row)
binlog_format=mixed
## Clearing time of binary log expiration. The default value is 0, indicating that automatic clearing is not performed.
expire_logs_days=7
Skip all errors or specified types of errors encountered in master/slave replication to avoid replication interruption on slave.
Error 1032 is caused by data inconsistency between primary and secondary databases
slave_skip_errors=1062
## relay_log Configures relay logs
relay_log=mall-mysql-relay-bin
## log_slave_updates indicates that slave writes replication events to its binary log
log_slave_updates=1
Set slave to read-only (except for users with super privileges)
read_only=1
Copy the code

After modifying the configuration, restart the instance:

service mysqld restart
Copy the code

Connect the primary and secondary databases

Connect to the mysql client of the primary database and check the primary database status:

show master status;
Copy the code

The following information is displayed about the active database:

Mysql client that connects to the secondary database

mysql -uroot -proot
Copy the code

Configure master/slave replication in the slave database

Change master to master_host='192.168.200.11', master_user='slave1', master_password='123456', master_port=3306, master_log_file='mall-mysql-bin.000001', master_log_pos=1136, master_connect_retry=30;Copy the code

Slave2 You need to replace the master_user and master_password accounts with the second ones previously configured

Parameter description of the primary/secondary replication command:
- Master_host: IP address of the primary database.
- Master_port: running port of the primary database.
- Master_user: user account created on the primary database to synchronize data.
- Master_password: user password created on the primary database to synchronize data;
- Master_log_file: specifies the log File to copy data from the database. You can view the status of the primary data to obtain the File parameter.
- Master_log_pos: specify the Position from which the data is copied from the database. Obtain the Position parameter by viewing the status of the master data.
- Master_connect_retry: Indicates the retry interval for connection failures, in seconds.
Viewing the master/slave synchronization status:

show slave status \G;
Copy the code

* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * * Slave_IO_State: Master_Host: 192.168.200.11 Master_User: slave2 Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mall-mysql-bin.000001 Read_Master_Log_Pos: 1136 Relay_Log_File: mall-mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Mall-mysql-bin.000001 Slave_IO_Running: No # Indicates that Replicate_Do_DB is not synchronized. 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: 1136 Relay_Log_Space: 154 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: NULL 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: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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: 1 row in set (0.00 SEC)Copy the code

Enable primary/secondary synchronization:

start slave;
Copy the code

SQL > select * from database;

*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.200.11
                  Master_User: slave2
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000001
          Read_Master_Log_Pos: 1136
               Relay_Log_File: mall-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mall-mysql-bin.000001
             Slave_IO_Running: Connecting	
            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: 1136
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 30  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 210811 22:01:39
     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: 
1 row in set (0.01 sec)
Copy the code

Slave_IO_Running: No

Mysql > install mysql; mysql > install mysql;

Mysql has a uUID, but uUID is unique, so the cloned UUID is the same, just need to change the UUID, find auto. CNF file to change the UUID

# exit mysql
mysql> exit;
# query auto. CNF
find / -iname "auto.cnf"
Write the path you found here
vim /var/lib/mysql/auto.cnf

# the original content
[auto]
server-uuid=0661e980-f858-11eb-9073-000c2965b894

Change the last digit of 4 to 5
[auto]
server-uuid=0661e980-f858-11eb-9073-000c2965b895
# save exit
# restart mysql
systemctl restart mysql
# do not use the same command as above.
service mysqld restart

# login mysql
mysql -uroot -p

# restart slave
stop slave;
start slave;
# check status
show slave status \G;
Copy the code

Well, you could be in my situation, hahaha. (You can skip this part if you’re both Yes)

Slave_IO_Running: Connecting, Slave_SQL_Running: Yes

Check the master firewall status

systemctl status firewalld
Copy the code

Sure enough, firewall open, because I firewall did not release port 3306 (although other ports also did not release).

I use more violent method: close firewall, you don’t learn me, you still honest release port

Systemctl stop firewalld check the firewall status systemctl status firewalldCopy the code

The firewall is successfully disabled. Procedure

Let’s go back to one of the slave tests:

# restart slave
stop slave;
start slave;
# check status
show slave status \G;
Copy the code

*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.200.11 Master_User: slave1 Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mall-mysql-bin.000001 Read_Master_Log_Pos: 1136 Relay_Log_File: mall-mysql-relay-bin.000002 Relay_Log_Pos: 325 Relay_Master_Log_File: mall-mysql-bin.000001 Slave_IO_Running: 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: 1136 Relay_Log_Space: 537 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: 11 Master_UUID: f8b4cd3b-f857-11eb-9172-000c293b13fb Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave 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: 1 row in set (0.00 SEC)Copy the code

Success!!

Master/slave replication tests

Create a database in the primary instancemall;

When I look at the database from the instance, I find one as wellmallDatabase, you can determine that the primary/secondary replication has been set up successfully.

At this point, we have completed the master-slave replication of mysql.

Again, with master-slave replication configured, if we use the connection tool on Windows (navicat is used by the blogger) to connect to our virtual machine database, the following situations may occur.

192.168.200.11 (master) : 192.168.200.11 (master) : 192.168.200.11 (master) : 192.168.200.11 (master) : 192.168.200.11 (master) : 192.168.200.11

Grant all PRIVILEGES on *.* to @ LOCALhost IDENTIFIED by "123456"; Grant all PRIVILEGES on *.* to user @ "%" IDENTIFIED by "123456";Copy the code

Connect again:

Success.

The connection to 192.168.200.1 failed because we did not assign an account to 192.168.200.1

If you want to configure read/write separation, go to my other blog post, search shardingJDBC in the navigation bar, and drop down articles to read/write separation

Implement primary/secondary replication of Mysql

The preparatory work

Configure the network

Mysql installation

The principle of

Primary instance Setup

Build from instance

Connect the primary and secondary databases

Master/slave replication tests

Related Posts

Object Oriented Knowledge (3)

The number of threads in a SpringBoot project explodes when the thread pool is created in a service class method

Erasure Coding