This chapter implements the MySQL master-slave replication GTID mode
| Host | IP address | port |
|---|---|---|
| mysql-master | 192.168.239.100 | 3306 |
| mysql-slave1 | 192.168.239.110 | 3306 |
| mysql-slave2 | 192.168.239.120 | 3306 |
1 Experimental preparation work
1.1 Download and install the official MySQL rpm package
[root@master mysql_rpm]# yum localinstall mysql-community-server-9.1.0-1.el7.x86_64.rpm \ mysql-community-common-9.1.0-1.el7.x86_64.rpm \ mysql-community-client-9.1.0-1.el7.x86_64.rpm \ mysql-community-icu-data-files-9.1.0-1.el7.x86_64.rpm \ mysql-community-libs-9.1.0-1.el7.x86_64.rpm \ mysql-community-client-plugins-9.1.0-1.el7.x86_64.rpm
1.2 Setting up DNS resolution
[root@master ~]# cat >> /etc/hosts <<EOF > 192.168.239.100 master > 192.168.239.110 slave1 > 192.168.239.120 slave2 > EOF [root@slave1 ~]# cat >> /etc/hosts <<EOF > 192.168.239.100 master > 192.168.239.110 slave1 > 192.168.239.120 slave2 > EOF [root@slave2 ~]# cat >> /etc/hosts <<EOF > 192.168.239.100 master > 192.168.239.110 slave1 > 192.168.239.120 slave2 > EOF
1.3 Modify password
MASTER SLAVE1 SLAVE2 are all set as follows
# Change password policy [root@master ~]# cat >> /etc/ <<EOF validate_password.policy=LOW validate_password.length=0 EOF # Start MySQL service [root@master ~]# systemctl start mysqld [root@master ~]# grep password /var/log/ 2024-12-14T14:29:20.406601Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: (:lt/hLIW4da [root@master ~]# mysql -uroot -p'(:lt/hLIW4da' # Modify the database password mysql> alter user 'root'@'localhost' identified by '123456'; mysql> flush privileges;
2 GTID mode realizes master-slave replication
2.1 Add configuration file content
[root@master ~]# cat >> /etc/ <<EOF log_bin=mysql-bin symbolic-links=0 # Disable symbolic links server_id=10 # Set serverID to the machine's identifier log_bin=mysql-bin # Enable log_bin log # Prerequisites for enabling GTID gtid_mode=ON enforce-gtid-consistency=ON EOF [root@slave1 ~]# cat >> /etc/ <<EOF log_bin=mysql-bin symbolic-links=0 # Disable symbolic links server_id=20 # Set serverID to the machine's identifier # Prerequisites for enabling GTID gtid_mode=ON enforce-gtid-consistency=ON EOF [root@slave2 ~]# cat >> /etc/ <<EOF log_bin=mysql-bin symbolic-links=0 # Disable symbolic links server_id=30 # Set serverID to the machine's identifier # Prerequisites for enabling GTID gtid_mode=ON enforce-gtid-consistency=ON EOF # Restart MySQL service and reload [root@master ~]# systemctl restart mysqld [root@slave1 ~]# systemctl restart mysqld [root@slave2 ~]# systemctl restart mysqld
2.2 Create a master-slave copy account
MASTER
mysql> set sql_log_bin=0; # Close binary SQL log writing Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456'; # Grant all databases all tables repl user REPLICATION SLAVE permissions mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; mysql> FLUSH PRIVILEGES; mysql> set sql_log_bin=1;
SLAVE1
mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456'; mysql> FLUSH PRIVILEGES; mysql> set sql_log_bin=1;
SLAVE2
mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456'; mysql> FLUSH PRIVILEGES; mysql> set sql_log_bin=1;
2.3 Implement automatic positioning of GTID
SLAVE1 && SLAVE2
mysql> SET SQL_LOG_BIN=0; # Close statement record
# Specify the IP port of the main server and the authorized user repl, and enable automatic positioning mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.239.100',
SOURCE_PORT=3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='123456',
GET_SOURCE_PUBLIC_KEY=1, # Trust Certificate SOURCE_AUTO_POSITION=1; # Turn on the automatic positioning function
mysql> START REPLICA;
# If the failure requires STOP REPLICA; Stop copying # Then execute RESET REPLICA; delete the configuration statement
# Check whether the connection master is normal mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.239.100
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 158
Relay_Log_File: slave2-relay-bin.000002
Relay_Log_Pos: 375
Relay_Source_Log_File: mysql-bin.000001
Replica_IO_Running: Yes # IO means there is no problem connecting to the network Replica_SQL_Running: Yes # SQL means there is no problem with local synchronization. If there is any problem, it is basically a configuration file problem.
mysql> SET SQL_LOG_BIN=0; # Turn on statement record3 Import data to check whether it is successful
3.1 Import SQL scripts on the master server
[root@master ~]# mysql -uroot -p123456 mysql> create database gtid; Query OK, 1 row affected (0.00 sec) mysql> use gtid Database changed mysql> source ; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show tables; +----------------------------------------+ | Tables_in_gtid | +----------------------------------------+ | aaa | | abi_http_log | | act_app_appdef | | act_app_databasechangelog | | act_app_databasechangeloglock | +----------------------------------------+
3.2 Check whether to replicate synchronization from the server
[root@ slave1 && slave2]# mysql -uroot -p123456 mysql> show databases; +--------------------+ | Database | +--------------------+ | gtid | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) mysql> use gtid 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_gtid | +----------------------------------------+ | aaa | | abi_http_log | | act_app_appdef | | act_app_databasechangelog | | act_app_databasechangeloglock | +----------------------------------------+
This is the article about the project practice of implementing the GTID mode of MySQL9.1.0. This is the end of this article. For more related contents of MySQL9.1.0 GTID mode, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!