目錄
一、環境搭建、部署
?1. RHEL7.9、9.3的搭建
二、主從復制
?1. 環境說明
?2. 環境準備
? 1）克隆RHEL79_mysql_master
? 2）改名為 “RHEL79_mysql_slave” 并修改IP
? 3）修改主機名
?3. 部署MySQL主從同步
? 1）主庫(mysql-master)
? 2）從庫(mysql-slave)
? 3）MySQL主從復制的狀況檢測
? 4）生產環境其他常用設置
三、主從復制進階
?1. 延時同步
?2. GID復制
? 1）配置GID復制(主、從)
? 2）檢測主從是否能夠正常通過GTID同步
四、半同步復制
?1. 配置半同步復制
? 1）主服務
? 2）從服務
?2. 測試
一、環境搭建、部署
?1. RHEL7.9、9.3的搭建
- 具體步驟請看煮波前兩篇發文
二、主從復制
?1. 環境說明
| 主機名 | ip地址 | 操作系統 | mysql版本 |
|---|---|---|---|
| mysql_master | 192.168.248.136 | rhel7.9 | 源碼安裝mysql8.0.40 |
| mysql_slave | 192.168.248.137 | rhel7.9 | 源碼安裝mysql8.0.40 |
?2. 環境準備
? 1）克隆RHEL79_mysql_master
? 2）改名為 “RHEL79_mysql_slave” 并修改IP
? 3）修改主機名
[root@mysql-master ~]# hostnamectl set-hostname mysql-salve
[root@mysql-master ~]# reboot?3. 部署MySQL主從同步
? 1）主庫(mysql-master)
-
IP：192.168.248.136
-
port：3306
#1）設置server-id值并開啟binlog參數---------------------------------------
[root@mysql-master ~]# cat /etc/my.cnf
修改如下內容：
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=136
log-bin=binlog[root@mysql-master ~]# ll /data/mysql/binlog.* # 查看日志#重啟數據庫
[root@mysql-master ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!#查看數據庫的編號
[root@mysql-master ~]# mysql -uroot -p123 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 136 |
+-------------+#2）建立同步賬號---------------------------------------------------------
[root@mysql-master ~]# mysql -uroot -p123mysql> create user 'rep'@'%' identified by 'rep123';
mysql> grant replication slave on *.* to 'rep'@'%';
#查看權限
mysql> show grants for 'rep'@'%';
+---------------------------------------------+
| Grants for rep@% |
+---------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `rep`@`%` |
+---------------------------------------------+#3）鎖表設置只讀---------------------------------------------------------
#為后面備份準備，注意生產環境要提前申請停機時間；
mysql> flush tables with read lock;#提示：如果超過設置時間不操作會自動解鎖。
mysql> show variables like '%timeout%';#測試鎖表后是否可以創建數據庫：
mysql> create database test;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock#4）查看主庫狀態--------------------------------------------------------
#查看主庫狀態，即當前日志文件名和二進制日志偏移量
mysql> show master status;
+------------+--------+------------+----------------+-----------------+
| File |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+------------+-----+--------------+------------------+----------------+
|binlog.000005| 688 | | | |
+-------------+-------+------------+----------------+-----------------+
#注意：如果想要重置主庫，可以使用reset master;生產環境慎用#5）備份主數據庫中數據----------------------------------------------------
# mysqldump -uroot -p -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz # 有額外的表，需要了再弄#6）解鎖---------------------------------------------------------------
mysql> unlock tables;#7）主庫備份數據上傳到從庫-------------------------------------------------
# scp /server/backup/mysql_bak.2025-08-07.sql.gz 192.168.168.129:/server/backup # 有額外的表，需要了再弄? 2）從庫(mysql-slave)
-
IP：192.168.248.137
-
port：3306
[root@mysql-salve ~]# rm -rf /data/mysql/* # 因為是克隆，所以先刪MySQL#1）設置server-id值-----------------------------------------------------
[root@rep1 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=137 # 自己的主機號[root@mysql-salve ~]# mysqld --initialize --user=mysql # 初始化
默認密碼：/9r7?1n9kw+J#重啟數據庫
[root@mysql-salve ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/master.err'.SUCCESS! #查看數據庫的編號
[root@mysql-salve ~]# mysql -uroot -p123 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 137 |
+-------------+[root@mysql-salve ~]# mysqladmin -uroot -p password '123'
Enter password: /9r7?1n9kw+J[root@mysql-salve ~]# mysql -uroot -p123
mysql> #2）還原從主庫備份數據----------------------------------------------------
# cd /server/backup/
# gzip -d mysql_bak.2025-08-07.sql.gz
# mysql -uroot -p < mysql_bak.2025-08-07.sql.gz
#檢查還原：
# mysql -uroot -p -e 'show databases;'#3）設定從庫向主庫同步----------------------------------------------------
mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.248.136', # 主服務器IP
SOURCE_USER='rep',
SOURCE_PASSWORD='rep123',
SOURCE_LOG_FILE='binlog.000005', # 在主服務器的show master status;中看
SOURCE_LOG_POS=688,
SOURCE_SSL=1; #4）啟動從庫同步開關------------------------------------------------------
mysql> start replica;#5）檢查狀態-------------------------------------------------------------
mysql> show replica status\G
Slave_IO_Running: Yes #IO線程是否打開
Slave_SQL_Running: Yes #SQL線程是否打開#注意：停止從庫可以使用命令：mysql> stop replica;? 3）MySQL主從復制的狀況檢測
-
在主庫上查看該主庫有多少從庫
# 在主庫上查看該主庫有多少從庫
mysql> show replicas;
+---------+----+-----+---------+--------------------------------------+
|Server_Id|Host| Port|Source_Id| Replica_UUID |
+---------+----+-----+---------+--------------------------------------+
| 137 | | 3306| 136 | 991a13b4-888f-11f0-9cd0-000c29926dc4 |
+--------+-----+------+------+----------------------------------------+# 主庫創建一個數據庫
[root@mysql-master ~]# mysql -uroot -p -e 'create database test1;'
Enter password: # 密碼：123-
在從庫可以看到主庫同步過來的數據庫
# 在從庫可以看到主庫同步過來的數據庫
[root@mysql-salve ~]# mysql -uroot -p -e 'show databases;'
Enter password: # 密碼：123
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+? 4）生產環境其他常用設置
1、配置不記錄information_schema mysql test等數據庫的操作（增、刪、改）的二進制日志文件------------------------------------------------------------------
# 在主配置文件中添加配置
binlog_ignore_db="information_schema"
binlog_ignore_db="mysql"
binlog_ignore_db="test"
#重啟數據庫
[root@mysql-master ~]# /etc/init.d/mysqld restart
[root@mysql-salve ~]# mysql -uroot -p -e 'show master status\G'
Enter password: # 密碼：123
*************************** 1. row ***************************File: binlog.000002Position: 688Binlog_Do_DB: Binlog_Ignore_DB:
Executed_Gtid_Set: 2、從庫備份開啟binlog-----------------------------------------------------
log-slave-updates #當從庫為其它從庫的主庫時必須要添加該參數
log_bin = mysql-bin #設置二進制日志文件的文件前綴為mysql-bin
expire_logs_days = 7 #設置二進制日志文件的保留時間
應用場景：級聯復制或從庫做數據備份。3、從庫只讀-------------------------------------------------------------
read-only
innodb_read_only = ON或1
注：當用戶權限中沒有SUPER權限(ALL權限是包括SUPER的)時，從庫的read-only生效！三、主從復制進階
?1. 延時同步
- 從庫
# 配置從庫延時同步，設置sql線程延遲300秒后讀取relay log
mysql> stop replica; # 停止同步mysql> CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 300; #延遲300s，5分鐘mysql>start replica;mysql> show replica status\G
SQL_Delay: 300
SQL_Remaining_Delay: NULLmysql> stop slave sql_thread; # 停掉slave的線程- 主庫
#主庫上創建庫和表
mysql> create database relay;
mysql> use relaymysql> create table t1(id int);
mysql> insert into t1 values (1);
mysql> drop database relay;- 從庫
mysql> show relaylog events in 'mysql-salve-relay-bin.000002';mysql> start slave sql_thread;#將relay log進行備份
[root@mysql-salve ~]# mysqlbinlog --start-position=584 --stop-position=1237 /data/mysql/slave1-relay-bin.000002 > /tmp/mysql.backup#恢復誤刪除的數據庫
[root@mysql-salve ~]# mysql -uroot -p -e 'source /tmp/relay.sql'
#查看數據是否恢復
mysql> select * from relay.t1;
+------+
| id |
+------+
| 1 |
+------+#解除從庫身份
mysql> stop replica;
mysql> reset replica all;?2. GID復制
? 1）配置GID復制(主、從)
- 在主master和從slave上執行
mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| OFF |
+----------------------------+#確定GTID模式復制的兼容性：查看日志中是否有警告，如果有警告說明應用和GTID復制不兼容
mysql> set global enforce_gtid_consistency=warn;#啟用GTID強一致性檢查，防止GTID不兼容的語句導致復制失敗
mysql> set global enforce_gtid_consistency=on;#將主和從的gtid_mode一步步從OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF |
+-------------+mysql> set global gtid_mode=OFF_PERMISSIVE;
mysql> set global gtid_mode=ON_PERMISSIVE;#在主庫和從庫上等待所有匿名事務復制完成（該變量為0）
mysql> show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0 |
+-------------------------------------+-------+#啟用GTID模式
mysql> set global gtid_mode=ON;#建議將enforce_gtid_consistency=on和gtid_mode=ON寫入配置文件，防止配置丟失；- 在從slave上重啟復制線程
mysql> stop replica;
mysql> change master to master_auto_position=1;
mysql> start replica;
mysql> show replica status \GReplica_IO_Running: YesReplica_SQL_Running: Yes? 2）檢測主從是否能夠正常通過GTID同步
- 主服務
# 在主上添加表并插入數據
mysql> create database test2;
mysql> use test2;
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> show master status \G
*************************** 1. row ***************************File: binlog.000009Position: 811Binlog_Do_DB: Binlog_Ignore_DB:
Executed_Gtid_Set: a3b49872-87d4-11f0-80e9-000c294b1068:1-3#查看日志信息
mysql> show binlog events in "binlog.000009"\G#查看已經執行過的gtid
mysql> select @@gtid_executed;
+------------------------------------------+
| @@gtid_executed |
+------------------------------------------+
| a3b49872-87d4-11f0-80e9-000c294b1068:1-3 |
+------------------------------------------+- 從服務
# 在從上進行查看
mysql> show replica status \G
*************************** 1. row ***************************Replica_IO_State: Waiting for source to send eventSource_Host: 192.168.248.136Source_User: repSource_Port: 3306Connect_Retry: 60Source_Log_File: binlog.000009Read_Source_Log_Pos: 811Relay_Log_File: mysql-salve-relay-bin.000002Relay_Log_Pos: 367Relay_Source_Log_File: binlog.000009Replica_IO_Running: YesReplica_SQL_Running: Yes#查看從上的relaylog日志
mysql> show relaylog events in "mysql-salve-relay-bin.000002"\G#在從庫上查看數據是否同步
mysql> select * from test2.t1;
+------+
| id |
+------+
| 1 |
+------+四、半同步復制
?1. 配置半同步復制
? 1）主服務
#主的配置，加載插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';#查看插件是否加載成功
mysql> show plugins;#開啟此開關，可寫入配置文件
mysql> set global rpl_semi_sync_master_enabled=1;#設置主等待從回復ACK的超時時間為3秒，默認是10秒，可寫入配置文件
mysql> set global rpl_semi_sync_master_timeout=3000;mysql> show status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+mysql> show variables like 'rpl_semi_sync_master%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+? 2）從服務
#從的配置
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show status like 'rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+#查看插件是否加載成功
mysql> show plugins;
#重啟從的IO線程
mysql> STOP REPLICA IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> START REPLICA IO_THREAD;?2. 測試
# 主上創建一張表
mysql> use test2
mysql> create table t2(id int);
mysql> show status like 'Rpl_semi_sync_master_yes_tx';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_yes_tx | 1 |#master成功接收到slave的回復的次數
+-----------------------------+-------+# 模擬從故障
mysql> stop slave io_thread;
mysql> set global rpl_semi_sync_slave_enabled=0;
mysql> show status like 'rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+# 在主上創建表，可以看到會等待一個超時時間
mysql> insert into t2 values(2);
Query OK, 1 row affected (10.00 sec)# 發現主關閉了半同步復制
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
+-----------------------------+-------+
![[Android]RecycleView的item用法](http://pic.xiahunao.cn/[Android]RecycleView的item用法)
))
