MySQL database realizes master-slave synchronization

Install MySQL database 8.0.32

Foreword

Today, let’s learn the principle and process of database master-slave synchronization. The database is mainly used to store WEB data, which is extremely important in enterprises. Let’s take a look at it together.

1.1 The purpose of database master-slave

MySQL master-slave replication is widely used in small and medium-sized enterprises and large enterprises. The purpose of MySQL master-slave replication is to achieve database redundancy backup, and to synchronize the master database data to the slave database at regular intervals. Once the masker database is down, the WEB application database can be configured Quickly switch to the slave database to ensure high availability of WEB applications.

1.2 Master-slave principle architecture

MySQL master-slave synchronization requires at least two servers, one for the master library and the other for the slave library. To achieve replication, you first need to enable the bin-log log function on the master, and the bin-log log is used to record in the Master library The sql statements for adding, deleting, modifying, and updating operations executed in , need to open three threads in the whole process, namely, the master starts the IO thread, and the slave starts the IO thread and the SQL thread.

1. Prepare the experimental environment

This experiment requires two servers, one is the master and the other is the slave. The preparation before the experiment requires the operation of both servers.

1.1 Turn off selinux and firewall

If necessary, it is still recommended to shut down selinux. The firewall can depend on the situation. You can directly turn off the firewall if you do your own experiments.

#Close the firewall
systemctl stop firewalld
iptables -F
# close selinux
setenforce 0

1.2 View local IP

Check the local IP and test whether you can ping www.baidu.com

ifconfig
192.168.116.166 #The first server IP as the main master
192.168.116.128 #Second server IP as slave slave

1.3 server database version

Both server versions are CentOS7.9

[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

The database uses MySQL8.0 community version to do this experiment
rpm package download address
The rpm package used in this experiment-Mysql8.0 Community Edition-you can directly click on the previous one to jump, or you can directly copy this link: http://repo.mysql.com/mysql80-community-release-el7.rpm
Display in table format:

server version IP database version
CentOS7.9.2009 192.168.116.166 (main master) MySQL8.0.32 Community Edition
CentOS7.9.2009 192.168.116.128 (from slave) MySQL8.0.32 Community Edition

2. Install MySQL8.0

Here is also the need to install the database on both servers

2.1 Clear the package that comes with CentOS7

In CentOS7, mariadb files and packages are installed by default, and these need to be deleted first, otherwise it will affect subsequent installations.

#Empty existing mariadb and mysql related files
[root@localhost ~]# rpm -qa | grep mysql
[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@localhost ~]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps

Next, look for files about mysql and mariadb to delete.

[root@localhost ~]# find / -name mariadb
[root@localhost ~]# find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/usr/lib64/mysql
[root@localhost ~]# rm -rf /etc/selinux/targeted/active/modules/100/mysql /usr/lib64/mysql

2.2 Download and install database 8.0

Use wget to download the rpm source copied from the previous official website. If the wget command is not available, you need to download the wget command first.
– can be copied directly

yum install wget -y
wget http://repo.mysql.com/mysql80-community-release-el7.rpm
rpm -ivh mysql80-community-release-el7.rpm
yum install mysql-community-server

After installing these, you can start the service directly;

#Start service
systemctl restart mysqld

2.3 Find the initialization password

The initialization passwords of the two computers are different. You can find out the passwords first, and then modify the database passwords of the two computers to be the same.

#Find the initialization password
grep -i 'password' /var/log/mysqld.log
2023-03-23T06:39:43.463553Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: q_gkjqeR1iGA

The database password is after the colon: q_gkjqeR1iGA

2.4 Change database password

It is recommended to change the passwords of the two databases to be the same to facilitate subsequent testing.

#Use the password to log in to the database and change it to a new password
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified by '#Admin$5^!';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

Change password statement

alter user 'root'@'localhost' identified by '#Admin$5^!';

In MySQL8.0, the password needs to be modified as complicated as possible to pass, and you can use the statement to see the degree of complexity.

mysql> show variables like 'validate%';
 + -------------------------------------- + -------- +
| Variable_name | Value |
 + -------------------------------------- + -------- +
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password. length | 8 |
| validate_password. mixed_case_count | 1 |
| validate_password. number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password. special_char_count | 1 |
 + -------------------------------------- + -------- +
7 rows in set (0.00 sec)

Whether to enable user password verification
Where the password file is stored
Password length at least 8 characters
The number of uppercase and lowercase letters is at least 1
contain at least one number
Set the password level, the default level is medium
contain at least one character

The above content needs to be operated on both servers.

3. Operation on the main master

The above defines the main master as the server IP: 192.168.116.166
In order to be able to visually see which server it is running on, here is the modification of the host name, so that it is easy to distinguish.

3.1 Temporarily modify the host name

[root@localhost ~]# hostname master
[root@localhost ~]# bash
[root@master~]#

3.2 Setting up the main database

Create a database user slave21, make it remote, and set a password

[root@master ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'slave21'@'%' identified with mysql_native_password by '#slvae21!@3LL';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'slave21'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'slave21'@'%';
 + ------------------------------------------------- +
| Grants for slave21@% |
 + ------------------------------------------------- +
| GRANT REPLICATION SLAVE ON *.* TO `slave21`@`%` |
 + ------------------------------------------------- +
1 row in set (0.00 sec)

Looking at this picture, it seems that there is a lot of content, but in fact there are only a few sentences;

create user 'slave21'@'%' identified with mysql_native_password by '#slvae21!@3LL';

The meaning of this statement is to create a slave21 user, the user name can be defined by yourself; @’%’ means that you can use remote login database, identified with mysql_native_password by this statement is MySQL8 The fixed writing method of .0 means to set a password for him.

grant replication slave on *.* to 'slave21'@'%';

This statement means to authorize the database authority for this user, *.* basically means to grant all permissions, the first * means all databases, and the second means database tables.

flush privileges;

This statement means to refresh the database, so that the previously set content can be synchronized to the database.

show grants for 'slave21'@'%';

Check whether the previously set permissions have been written into the database, and the displayed content indicates that the write has been successful.

3.3 Modify the database configuration file

[root@master ~]# vim /etc/my.cnf
[root@master ~]# sed 4, + 4p -n /etc/my.cnf
[mysqld]
server-id=21
log-bin=mysql-bin
binlog_do_db=test
binlog_ignore_db=mysql
# restart service
[root@master ~]# systemctl restart mysqld

server-id=21 The unique ID of the database service
log-bin=mysql-bin Set the storage location of binlog logs
binlog_do_db=test set the database for synchronization, if this item is not written, it means that all databases are synchronized
binlog_ignore_db=mysql set the database not to be synchronized

Remember that after setting, there is another command that needs to restart the service.

3.4 Check the master status

After setting the configuration file, log in to the mysql database system to check the master information.

[root@master ~]# !m
mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
 + ------------------ + ---------- + -------------- + ---- -------------- + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 + ------------------ + ---------- + -------------- + ---- -------------- + ------------------- +
| mysql-bin.000001 | 157 | test | mysql | |
 + ------------------ + ---------- + -------------- + ---- -------------- + ------------------- +
1 row in set (0.00 sec)

Why do you need to check this information, because it needs to be used when configuring the slave database.

File is the storage file of the log-bin we just set up, which is used to record the log file of mysql operation.
Position represents the offset
Binlog_Do_DB The database that needs to be synchronized
Binlog_Ignore_DB does not synchronize the database

At this point, do not continue to operate the first master database to avoid changing the offset.

Four. Operate from slave

Here you need to go to the second server to operate, and here also modify the host name to increase the recognition.

4.1 Temporarily modify the hostname

[root@localhost ~]# hostname slave22
[root@localhost ~]# bash
[root@slave22 ~]#

4.2 Modify configuration file

The slave server only needs to write the unique ID, server-id=22, the content of this id does not have to be the same as the one above, as long as it is not the same as the id number of the previous one, how to set it all will do.

#mysql configuration file
[root@slave22 ~]# vim /etc/my.cnf
[root@slave22 ~]# sed 4, + 1p -n /etc/my.cnf
[mysqld]
server-id=22
[root@slave22 ~]# systemctl restart mysqld

Don’t forget to restart the service

4.3 Bind the main master server

[root@slave22 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to master_host='192.168.116.166', master_user='slave21', master_password='#slvae21!@3LL', master_log_file='mysql-bin.000001', master_log_pos=157 ;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status \G
*************************** 1. row ********************* *****
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.116.166
                  Master_User: slave21
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000001
             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: 157
              Relay_Log_Space: 540
              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: 21
                  Master_UUID: 7d57e7f4-c945-11ed-b2a1-000c29b5625a
             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)

The statement that actually needs to be executed;

change master to master_host='192.168.116.166', master_user='slave21', master_password='#slvae21!@3LL', master_log_file='mysql-bin.000001', master_log_pos= 157;
  • master_host= #The IP of the first main master should be filled in later
  • master_user= #write the user name created on the main master before
  • master_password= #write the password of the username created on the master master before
  • master_log_file= #Log file, use show master status in the master; the value displayed in that step.
  • master_log_pos= #offset
start slave;

Enable synchronous data replication

show slave status \G

After executing this statement, check whether the two values of Slave_IO_Running: Yes and Slave_SQL_Running: Yes are yes. If yes, it means that synchronization is in progress, which also means that our experiment this time is successful.

Of course, there are also cases where these two values are not yes after the small partners are finished, and there may be situations where one value is no, or both values are no.

How to solve the problem of no in 4.4

You can stop the slave first, and redefine it again until both are yes.

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show slave status \G
*************************** 1. row ********************* *****
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.116.166
                  Master_User: slave21
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 373
        Relay_Master_Log_File: mysql-bin.000001
             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: 157
              Relay_Log_Space: 767
              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: 21
                  Master_UUID: 7d57e7f4-c945-11ed-b2a1-000c29b5625a
             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)

You can also copy the content of the database to be synchronized to the slave server first, so that the content of the database to be synchronized by the two servers is exactly the same, and then use this method again to bind and start.

5. Test results

5.1 Create a database on the master

We have finished the above experiment, it is time to start creating a database to verify, the database we chose to synchronize is test, then we will create a test database and write in it Enter tables, data, etc.

# Created on the main master
mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> create database abc;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table test1(id int,name varchar(20) not null default 'username');
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(1,'zhangsan'), (2,'lisi'), (3,'wangwu');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> desc test1;
 + ------- + ------------- + ------ + ----- + ---------- + --- ----+
| Field | Type | Null | Key | Default |
 + ------- + ------------- + ------ + ----- + ---------- + --- ----+
| id | int | YES | | NULL | |
| name | varchar(20) | NO | | username | |
 + ------- + ------------- + ------ + ----- + ---------- + --- ----+
2 rows in set (0.01 sec)

mysql> select id,name from test1;
 + ------ + ---------- +
| id | name |
 + ------ + ---------- +
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
 + ------ + ---------- +
3 rows in set (0.00 sec)

5.2 View from the database on the slave

You can see that the test database has been synchronized. Enter the test database to view the data under it, and it is also synchronized.

mysql> show databases;
 + --------------------+
| Database |
 + --------------------+
| information_schema |
|mysql|
| performance_schema |
| sys |
| test |
 + --------------------+
5 rows in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

mysql> desc test1;
 + ------- + ------------- + ------ + ----- + ---------- + --- ---- +
| Field | Type | Null | Key | Default |
 + ------- + ------------- + ------ + ----- + ---------- + --- ---- +
| id | int | YES | | NULL | |
| name | varchar(20) | NO | | username | |
 + ------- + ------------- + ------ + ----- + ---------- + --- ----+
2 rows in set (0.00 sec)

mysql> select id,name from test1;
 + ------ + ---------- +
| id | name |
 + ------ + ---------- +
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
 + ------ + ---------- +
3 rows in set (0.00 sec)

Check the table information in the database exists, indicating that the test database selected for synchronization is no problem.

Summary

After doing the above experiment, you will find that the main database can only be synchronized from the database. So can both of them be synchronized with each other on this basis? The answer is yes. This method is the main and main synchronization. It is the master server, it is the slave when it synchronizes data, and it is the master when it writes data, which will be revealed in the next article. If you think the above content is okay, you can like it and support it!