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!