MySQL9.1.0實現(xiàn)GTID模式的項目實踐

更新時間：2025年02月24日 09:27:29 作者：妍妍的寶貝

本文主要介紹了在MySQL 9.1.0中實現(xiàn)GTID模式的主從復(fù)制,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

主機	IP地址	端口
mysql-master	192.168.239.100	3306
mysql-slave1	192.168.239.110	3306
mysql-slave2	192.168.239.120	3306

1 實驗準備工作

1.1 下載并安裝官方MySQL的rpm包

[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 設(shè)置DNS解析

 [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 修改密碼

MASTER SLAVE1 SLAVE2 均是以下這樣設(shè)置

 # 改變密碼策略
 [root@master ~]# cat >> /etc/my.cnf <<EOF
 validate_password.policy=LOW
 validate_password.length=0
 EOF
 ?
 # 啟動MySQL服務(wù)
 [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'
 ?
 # 修改數(shù)據(jù)庫密碼
 mysql> alter user 'root'@'localhost' identified by '123456';
 mysql> flush privileges;

2 GTID模式實現(xiàn)主從復(fù)制

2.1 增加配置文件內(nèi)容

 [root@master ~]# cat >> /etc/my.cnf <<EOF
 log_bin=mysql-bin
 symbolic-links=0    # 禁用符號鏈接
 server_id=10 # 設(shè)置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 # 設(shè)置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 # 設(shè)置serverID 為機器的標識符
 ?
 # 開啟GTID的前提
 gtid_mode=ON
 enforce-gtid-consistency=ON
 EOF
 ?
 # 重啟 MySQL 服務(wù)重新加載
 [root@master ~]# systemctl restart mysqld
 [root@slave1 ~]# systemctl restart mysqld
 [root@slave2 ~]# systemctl restart mysqld

2.2 創(chuàng)建主從復(fù)制賬號

MASTER

 mysql> set sql_log_bin=0;    # 關(guān)閉二進制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 的權(quán)限
 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 實現(xiàn)GTID的自動定位

SLAVE1 && SLAVE2

 mysql> SET SQL_LOG_BIN=0;   # 關(guān)閉語句記錄
 ?
 # 指定主服務(wù)器的IP端口以及授權(quán)過的用戶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;
 ?
 # 假如說失敗需要執(zhí)行     STOP REPLICA;   停止復(fù)制
 # 之后再執(zhí)行     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表示鏈接網(wǎng)絡(luò)沒有問題
           Replica_SQL_Running: Yes      # SQL表示本地同步?jīng)]有問題，如有問題基本上就是配置文件的問題
 ?
 ?
 mysql> SET SQL_LOG_BIN=0; # 開啟語句記錄

3 導(dǎo)入數(shù)據(jù)查看是否成功

3.1 主服務(wù)器導(dǎo)入SQL腳本

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

3.2 兩個從服務(wù)器查看是否復(fù)制同步

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

到此這篇關(guān)于MySQL9.1.0實現(xiàn)GTID模式的項目實踐的文章就介紹到這了,更多相關(guān)MySQL9.1.0 GTID模式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: