+——————————+————————————————–+
| Variable_name | Value |
+——————————+————————————————–+
| wsrep_local_state_uuid | 550e8400-e29b-41d4-a716-446655440000 |
| wsrep_protocol_version | 10 |
| wsrep_last_committed | 10 |
| wsrep_replicated | 5 |
| wsrep_replicated_bytes | 1024 |
| wsrep_received | 10 |
| wsrep_received_bytes | 2048 |
| wsrep_local_commits | 5 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 1 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_incoming_addresses | 192.168.1.100:3306,192.168.1.101:3306,192.168.1.102:3306 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 550e8400-e29b-41d4-a716-446655440000 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy |
| wsrep_provider_version | 4.16(rxxx) |
| wsrep_ready | ON |
+——————————+————————————————–+

4.3 故障切换实战

# 模拟主库故障
# 在主库上执行
systemctl stop greatsql

# 检查从库状态
mysql -u root -pFGedu123456! -e “SHOW SLAVE STATUS\G”

*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 156
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
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: 156
Relay_Log_Space: 524
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master ‘repl@192.168.1.100:3306’ – retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 550e8400-e29b-41d4-a716-446655440000
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 260409 13:00:00
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 00000001-0000-0000-0000-000000000001:1-5, 00000002-0000-0000-0000-000000000002:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:

# 提升从库为主库
mysql -u root -pFGedu123456! -e “STOP SLAVE;” mysql -u root -pFGedu123456! -e “RESET SLAVE ALL;” mysql -u root -pFGedu123456! -e “SET GLOBAL read_only = OFF;”

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

# 配置其他从库指向新主库
mysql -u root -pFGedu123456! -e “STOP SLAVE;” mysql -u root -pFGedu123456! -e “CHANGE MASTER TO \ MASTER_HOST=’192.168.1.101′, \ MASTER_USER=’repl’, \ MASTER_PASSWORD=’Repl@123456′, \ MASTER_PORT=3306, \ MASTER_AUTO_POSITION=1;” mysql -u root -pFGedu123456! -e “START SLAVE;”

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

Part05-风哥经验总结与分享

5.1 常见复制问题与解决方案

问题	解决方案
复制延迟	检查网络连接，调整复制参数，使用并行复制
复制错误	检查错误日志，修复数据冲突，重新同步
主库故障	提升从库为主库，更新应用连接配置
集群分裂	检查网络连接，确保quorum，重启节点

5.2 集群管理最佳实践

• 定期监控复制状态
• 实施自动化故障切换
• 定期备份数据
• 使用GTID确保复制一致性
• 配置合理的网络拓扑
• 定期测试故障切换流程
• 保持集群节点配置一致

更多学习教程公众号风哥教程itpux_com

5.3 性能优化建议

# 优化复制性能
cat >> /etc/my.cnf << EOF
# 复制性能优化 slave-parallel-type = LOGICAL_CLOCK slave-parallel-workers = 4 slave_preserve_commit_order = 1 slave_transaction_retries = 10 EOF
# 重启服务
systemctl restart greatsql

复制监控脚本

# 创建复制监控脚本
cat > /greatsql/scripts/replication_monitor.sh << 'EOF'
#!/bin/bash # replication_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
echo “=== GreatSQL Replication Monitor ===” echo “Date: $(date)” echo “”
# 检查复制状态 SLAVE_STATUS=$(mysql -u root -pFGedu123456! -e “SHOW SLAVE STATUS\G” -s) if
echo “$SLAVE_STATUS” | grep -q “Slave_IO_Running: Yes” && echo “$SLAVE_STATUS” | grep -q “Slave_SQL_Running: Yes”; then echo “Replication is running normally”
# 检查复制延迟 SECONDS_BEHIND=$(echo “$SLAVE_STATUS” | grep “Seconds_Behind_Master” | awk ‘{print $2}’) if [ “$SECONDS_BEHIND” -gt 300 ]; then
echo “Warning: Replication delay is high ($SECONDS_BEHIND seconds)” else echo “Replication delay: $SECONDS_BEHIND seconds” fi else echo “Error: Replication is not running” echo “Slave IO running: $(echo “$SLAVE_STATUS” | grep “Slave_IO_Running” | awk ‘{print $2}’)” echo “Slave SQL running: $(echo “$SLAVE_STATUS” | grep “Slave_SQL_Running” | awk ‘{print $2}’)” echo “Last error: $(echo “$SLAVE_STATUS” | grep “Last_Error” | cut -d: -f2-)” fi echo “” echo “Replication monitoring completed!” EOF
# 设置脚本权限
chmod +x /greatsql/scripts/replication_monitor.sh

# 执行复制监控脚本 /greatsql/scripts/replication_monitor.sh

=== GreatSQL Replication Monitor ===
Date: Wed Apr 9 20:00:00 CST 2026
Replication is running normally
Replication delay: 0 seconds
Replication monitoring completed!

集群监控脚本

# 创建集群监控脚本
cat > /greatsql/scripts/cluster_monitor.sh << 'EOF'
#!/bin/bash # cluster_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
echo “=== GreatSQL Cluster Monitor ===” echo “Date: $(date)” echo “”
# 检查集群状态 CLUSTER_STATUS=$(mysql -u root -pFGedu123456! -e “SHOW STATUS LIKE ‘wsrep%’;” -s) CLUSTER_SIZE=$(echo “$CLUSTER_STATUS” | grep “wsrep_cluster_size” | awk ‘{print $2}’) CLUSTER_STATUS=$(echo “$CLUSTER_STATUS” | grep “wsrep_cluster_status” | awk ‘{print $2}’) LOCAL_STATE=$(echo “$CLUSTER_STATUS” | grep “wsrep_local_state_comment” | awk ‘{print $2}’) if [ “$CLUSTER_STATUS” = “Primary” ] && [ “$LOCAL_STATE” = “Synced” ]; then
echo “Cluster is healthy” echo “Cluster size: $CLUSTER_SIZE” echo “Local state: $LOCAL_STATE” else echo “Error: Cluster is not healthy” echo “Cluster status: $CLUSTER_STATUS” echo “Local state: $LOCAL_STATE” fi echo “” echo “Cluster monitoring completed!” EOF
# 设置脚本权限
chmod +x /greatsql/scripts/cluster_monitor.sh

# 执行集群监控脚本 /greatsql/scripts/cluster_monitor.sh

=== GreatSQL Cluster Monitor ===
Date: Wed Apr 9 20:00:00 CST 2026
Cluster is healthy
Cluster size: 3
Local state: Synced
Cluster monitoring completed!

from greatsql视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

MySQL优化 MySQL安装 MySQL监控 MySQL管理 MySQL配置

相关推荐

• greatsql教程FG009-GreatSQL分区与索引设计最佳实践
• greatsql教程FG026-GreatSQL运行平台实战
• greatsql教程FG010-GreatSQL备份与恢复策略实战
• greatsql教程FG027-GreatSQL学习前景与职业发展
• greatsql教程FG011-GreatSQL用户与权限管理实战
• greatsql教程FG028-GreatSQL日常维护管理实战
• greatsql教程FG012-GreatSQL性能优化策略实战
• greatsql教程FG029-GreatSQL项目实施与验收
• greatsql教程FG013-GreatSQL故障处理与常用报错分析
• greatsql教程FG030-GreatSQL总结与展望