MySQL9.1.0实现GTID模式的项目实践

[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

 [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

 # 改变密码策略
 [root@master ~]# cat >> /etc/my.cnf <<eof
 validate_password.policy=low
 validate_password.length=0
 eof
 ​
 # 启动mysql服务
 [root@master ~]# systemctl start mysqld
 [root@master ~]# grep password /var/log/mysqld.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'
 ​
 # 修改数据库密码
 mysql> alter user 'root'@'localhost' identified by '123456';
 mysql> flush privileges;

 [root@master ~]# cat >> /etc/my.cnf <<eof
 log_bin=mysql-bin
 symbolic-links=0    # 禁用符号链接
 server_id=10 # 设置serverid 为机器的标识符
 ​
 log_bin=mysql-bin # 开启 log_bin 日志
 # 开启gtid的前提
 gtid_mode=on
 enforce-gtid-consistency=on
 eof
 ​
 ​
 [root@slave1 ~]# cat >> /etc/my.cnf <<eof
 log_bin=mysql-bin
 symbolic-links=0    # 禁用符号链接
 server_id=20 # 设置serverid 为机器的标识符
 ​
 # 开启gtid的前提
 gtid_mode=on
 enforce-gtid-consistency=on
 eof
 ​
 ​
 [root@slave2 ~]# cat >> /etc/my.cnf <<eof
 log_bin=mysql-bin
 symbolic-links=0    # 禁用符号链接
 server_id=30 # 设置serverid 为机器的标识符
 ​
 # 开启gtid的前提
 gtid_mode=on
 enforce-gtid-consistency=on
 eof
 ​
 # 重启 mysql 服务重新加载
 [root@master ~]# systemctl restart mysqld
 [root@slave1 ~]# systemctl restart mysqld
 [root@slave2 ~]# systemctl restart mysqld

 mysql> set sql_log_bin=0;    # 关闭二进制sql日志写入
 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';
 ​
 # 赋予所有库所有表 repl 用户 replication slave 的权限
 mysql> grant replication slave on *.* to 'repl'@'%';
 mysql> flush privileges;
 mysql> set sql_log_bin=1;

 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;

 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;

 mysql> set sql_log_bin=0;   # 关闭语句记录
 ​
 # 指定主服务器的ip端口以及授权过的用户repl，并开启自动定位
 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,    # 信任证书
 source_auto_position=1;     # 开启自动定位功能
 ​
 mysql> start replica;
 ​
 # 假如说失败需要执行     stop replica;   停止复制
 # 之后再执行     reset replica;  删除配置的语句
 ​
 # 查看连接 master 是否正常
 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表示链接网络没有问题
           replica_sql_running: yes      # sql表示本地同步没有问题，如有问题基本上就是配置文件的问题
 ​
 ​
 mysql> set sql_log_bin=0; # 开启语句记录

 [root@master ~]# mysql -uroot -p123456 
 mysql> create database gtid;
 query ok, 1 row affected (0.00 sec)
 ​
 mysql> use gtid
 database changed
 mysql> source gtid.sql;
 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          |
 +----------------------------------------+

[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          |
+----------------------------------------+