一、優化操作

在 MySQL 優化中，通常從 索引、SQL 語句、配置參數、架構 等維度入手，以下是一些常見優化方向及簡要説明：

1. 索引優化

• 創建合適的索引：為查詢頻繁的字段（如 WHERE、JOIN、ORDER BY 後的字段）建立索引（普通索引、聯合索引），避免全表掃描。
• 優化聯合索引順序：遵循 “最左前綴原則”，將過濾性強的字段放在前面（如 WHERE a=1 AND b>2，聯合索引 (a,b) 比 (b,a) 更高效）。
• 刪除冗餘 / 無效索引：定期清理未使用或重複的索引（通過 sys.schema_unused_indexes 查看），減少寫入時的索引維護開銷。

2. SQL 語句優化

• 避免 “SELECT *”：只查詢需要的字段，減少數據傳輸和內存消耗。
• 優化子查詢：將子查詢改為 JOIN（子查詢可能導致臨時表創建，JOIN 效率更高）。
• 控制 JOIN 表數量：避免多表關聯（尤其是大表），必要時拆分查詢或用中間表緩存結果。
• 使用 EXPLAIN 分析執行計劃：查看是否走索引、是否全表掃描、是否有臨時表 / 文件排序等，針對性優化。

3. 配置參數優化

• 調整緩存相關參數：

• innodb_buffer_pool_size：設置為物理內存的 50%-70%（緩存表數據和索引，減少磁盤 IO）。
• query_cache_size：MySQL 8.0 已移除，低版本按需開啓（適合讀多寫少場景，避免緩存失效頻繁）。

• 優化連接數：max_connections 設為合理值（避免連接數不足或過多導致資源耗盡），配合 wait_timeout 回收閒置連接。
• 調整 InnoDB 日誌參數：innodb_log_file_size 適當調大（如 1-2G），減少日誌切換頻率；innodb_flush_log_at_trx_commit=1 保證事務安全（或按需設為 2 平衡性能）。

4. 架構與存儲優化

• 分庫分表：大表（千萬級以上）按業務拆分（水平分表：按時間 / ID 分片；垂直分表：拆分冷熱數據），避免單表過大導致查詢緩慢。
• 讀寫分離：主庫寫入，從庫讀取（通過 MySQL 主從複製實現），分擔主庫壓力。
• 選擇合適的存儲引擎：InnoDB 適合事務和高併發（默認），MyISAM 適合讀多寫少且無事務需求的場景（已逐漸淘汰）。
• 定期維護表：通過 OPTIMIZE TABLE 優化表空間（減少碎片），ANALYZE TABLE 更新統計信息（幫助優化器生成更好的執行計劃）。

5. 其他細節

• 避免使用 SELECT COUNT(*) 全表統計：用近似值（如 InnoDB 的 EXPLAIN 估算）或緩存結果。
• 控制事務範圍：避免長事務（會佔用鎖資源，導致其他操作阻塞）。
• 使用慢查詢日誌：開啓 slow_query_log，記錄執行時間超過 long_query_time 的 SQL，針對性優化。

優化核心原則：先定位瓶頸（通過監控工具如 Prometheus、Percona Toolkit），再針對性優化，避免盲目調參。

二、導出命令（備份）

1. mysqldump 邏輯備份（常用）

2. mysqlbinlog 導出二進制日誌（增量備份）

三、導入命令（恢復）

1. 從 SQL 文件導入

注意區別

2. 二進制日誌增量導入

3. source 命令（MySQL 客户端內執行）

四、關鍵結論

1. 導出操作：所有備份命令僅讀取數據生成文件，不會修改原數據庫，無覆蓋風險。
2. 導入操作：

• 若備份含表結構（CREATE TABLE）：同名表會被徹底覆蓋（結構和數據全替換），新表會新增。
• 若僅導入數據（無結構，如 -t 導出）：不會刪除原有數據，但可能因主鍵衝突報錯（需手動處理重複數據）。
• 若導入前手動刪除原表 / 庫：則會完全重建，等同於 “全量覆蓋”。

3. 避免誤覆蓋的建議：

• 導入前備份原數據庫；
• 對僅需新增數據的場景，用 -t 導出數據，導入時先處理重複鍵（如 INSERT IGNORE 或 REPLACE）；
• 生產環境建議先在測試庫驗證導入效果。

xtrabackup 是 Percona 推出的一款高性能 MySQL 物理備份工具（支持 MySQL、MariaDB、Percona Server），相比 mysqldump 邏輯備份，它更適合大數據庫，支持熱備份（不鎖表），且備份 / 恢復速度更快。以下是其備份、恢復命令及對數據的影響總結：

五、xtrabackup 核心特點

• 物理備份：直接複製數據文件（如 .ibd、frm 等），而非 SQL 語句，備份與恢復效率更高。
• 支持熱備：備份時不阻塞讀寫操作（InnoDB 表），MyISAM 表仍需鎖表。
• 支持全量 / 增量備份：增量備份僅備份變化的數據頁，節省空間和時間。

六、備份命令（全量 / 增量）

1. 全量備份

# 基本語法
xtrabackup --user=用户名 --password=密碼 --backup --target-dir=備份目錄

# 示例：備份所有數據庫到 /backup/full_20251026
xtrabackup --user=root --password=123456 --backup --target-dir=/backup/full_20251026

# 注意：如果mysql是自定義安裝的情況下
xtrabackup --defaults-file=/usr/local/mysql/my.cnf --user=root --password='admin123' --backup --target-dir=/backup/full_20251026

# 遠程備份
xtrabackup --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password='root' --host=127.0.0.1 --compress-threads=8 --backup --stream=xbstream --parallel=4 | ssh 10.0.0.73 "gzip > /test/mysqlback.xb.gz"

• 作用：備份 MySQL 所有數據文件（含表結構、數據、索引等），生成完整的物理副本。
• 對原數據影響：僅讀取數據，不修改原庫，無覆蓋風險。

2. 增量備份（基於全量備份）

# 第一次增量備份（基於全量備份）
xtrabackup --user=root --password=123456 --backup \
--target-dir=/backup/inc_20251027 \
--incremental-basedir=/backup/full_20251026  # 指定全量備份目錄

# 第二次增量備份（基於上一次增量備份）
xtrabackup --user=root --password=123456 --backup \
--target-dir=/backup/inc_20251028 \
--incremental-basedir=/backup/inc_20251027  # 指定上一次增量備份目錄

• 作用：僅備份自 --incremental-basedir 對應備份後變化的數據頁。
• 對原數據影響：無影響，僅讀取變化數據。

七、恢復命令（全量 / 增量）

恢復前需先 “準備” 備份文件（合併增量備份、同步日誌等），再複製到數據庫目錄。

1. 全量恢復步驟

步驟 1：準備全量備份（確保數據一致性）

xtrabackup --prepare --target-dir=/backup/full_20251026

• 作用：將備份中的未提交事務回滾，已提交事務應用到數據文件，生成可恢復的一致性備份。

步驟 2：停止 MySQL 並清空原數據目錄

systemctl stop mysqld  # 停止服務
rm -rf /var/lib/mysql/*  # 清空原數據（謹慎！確保備份有效）

步驟 3：複製備份文件到數據目錄

xtrabackup --copy-back --target-dir=/backup/full_20251026

• 作用：將準備好的全量備份文件複製到 MySQL 數據目錄（默認 /var/lib/mysql）。

步驟 4：修復權限並啓動服務

chown -R mysql:mysql /var/lib/mysql  # 恢復數據目錄權限
systemctl start mysqld

2. 增量恢復步驟（基於全量 + 增量備份）

步驟 1：準備全量備份（--apply-log-only 避免回滾未提交事務，留待增量合併）

xtrabackup --prepare --apply-log-only --target-dir=/backup/full_20251026

步驟 2：合併第一次增量備份到全量備份

xtrabackup --prepare --apply-log-only --target-dir=/backup/full_20251026 \
--incremental-dir=/backup/inc_20251027

步驟 3：合併第二次增量備份（最後一次增量無需 --apply-log-only）

xtrabackup --prepare --target-dir=/backup/full_20251026 \
--incremental-dir=/backup/inc_20251028

步驟 4：後續步驟同全量恢復

停止 MySQL → 清空原數據目錄 → 複製合併後的全量備份 → 修復權限 → 啓動服務。

八、恢復時對原有數據的影響

1. 全量恢復：

• 若按上述步驟執行（清空原數據目錄後複製備份），原有數據庫會被完全覆蓋（所有表結構和數據替換為備份內容）。
• 若未清空原目錄直接複製，可能因文件衝突導致恢復失敗（建議嚴格按步驟操作）。

2. 增量恢復：

• 最終恢復結果是 “全量備份 + 所有增量備份” 的合併狀態，原有數據會被完全覆蓋（等同於恢復到最後一次增量備份的時刻）。

3. 注意：xtrabackup 恢復是物理替換數據文件，一旦執行，原數據無法保留（除非提前備份），務必在恢復前確認備份有效性，並停止數據庫服務。

總結

• 備份：全量 / 增量備份均不影響原數據，僅生成物理副本。
• 恢復：會完全覆蓋原有數據庫（基於備份內容重建），適合需要徹底恢復到某一時刻的場景。
• 優勢：速度快、支持熱備，適合 TB 級大庫；缺點：恢復操作較複雜，且必須停止數據庫服務才能完成恢復。

在數據庫複製技術中，異步複製是指主庫執行事務並提交後，無需等待從庫確認接收或執行該事務，即可繼續處理後續請求的複製模式。以下是對異步複製及其他複製類型的詳細説明：

九、複製方式

一、異步複製（Asynchronous Replication）

• 核心特點：主庫事務提交後立即返回，不等待從庫響應；從庫異步拉取主庫的二進制日誌並執行。
• 優勢：主庫性能損耗小，響應速度快。
• 劣勢：主庫故障時，可能存在部分事務未同步到從庫，導致數據不一致。
• 典型場景：MySQL 傳統主從複製默認採用異步複製。

二、其他複製類型

三、補充説明

• 實際應用中，半同步複製是異步和同步的折中方案，被廣泛用於對一致性和性能平衡要求較高的場景。
• MySQL 的 組複製（Group Replication） 屬於同步複製的一種，基於 Paxos 協議實現多節點間的強一致性，常用於高可用集羣（如 MGR）。

四、MHA高可用

MHA（Master High Availability）是 MySQL 主從架構下的高可用解決方案，可實現主庫故障時自動切換到從庫，核心由 管理節點（Manager） 和 數據節點（Master/Slave） 組成。以下以 3 台機器（10.0.0.100 主庫、10.0.0.101 從庫 1、10.0.0.102 從庫 2，10.0.0.103 作為管理節點）為例，簡要説明配置步驟：

前提條件

1. 所有節點已安裝 MySQL（主從複製已搭建，主庫 10.0.0.100，從庫 101/102 同步主庫數據）。
2. 所有節點間 免密 SSH 登錄（管理節點需免密登錄所有數據節點，數據節點間也需免密，用於複製 binlog）。
3. 管理節點安裝 MHA 管理工具（mha4mysql-manager），所有數據節點安裝 MHA 節點工具（mha4mysql-node）。

配置步驟

1. 主從複製基礎配置（已完成可跳過）

• 主庫（100）開啓 binlog 和 log_slave_updates，從庫（101/102）配置 change master to 同步主庫，確保從庫能正常複製主庫數據。
• 所有節點 MySQL 用户授權：創建 MHA 專用用户（如 mha_user），授予 REPLICATION SLAVE、REPLICATION CLIENT 等權限。

2. 管理節點配置（10.0.0.103）

• 創建 MHA 工作目錄：

mkdir -p /etc/mha/app1  # 存放配置文件
mkdir -p /var/log/mha/app1  # 存放日誌

• 編寫配置文件 app1.cnf：

[server default]
# MySQL 登錄信息（所有節點統一）
user=mha_user
password=123456
ssh_user=root  # SSH 登錄用户
repl_user=repl  # 主從複製用户（從庫連接主庫用）
repl_password=repl123
# 工作目錄
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
remote_workdir=/var/log/mha/app1  # 數據節點上的工作目錄

# 數據節點配置（主庫和從庫）
[server1]
hostname=10.0.0.100
port=3306

[server2]
hostname=10.0.0.101
port=3306
candidate_master=1  # 允許成為候選主庫

[server3]
hostname=10.0.0.102
port=3306
candidate_master=1

3. 檢查 MHA 環境（管理節點執行）

• 檢查 SSH 免密：

masterha_check_ssh --conf=/etc/mha/app1/app1.cnf

• 檢查主從複製狀態：

masterha_check_repl --conf=/etc/mha/app1/app1.cnf

4. 啓動 MHA 管理進程

• 前台啓動（測試用，日誌實時輸出）：

masterha_manager --conf=/etc/mha/app1/app1.cnf

• 後台啓動（生產用）：

nohup masterha_manager --conf=/etc/mha/app1/app1.cnf &

5. 驗證故障自動切換

• 手動模擬主庫（100）故障：systemctl stop mysqld。
• 觀察管理節點日誌（/var/log/mha/app1/manager.log），會自動檢測主庫故障，從 101/102 中選一個作為新主庫，並將其他從庫指向新主庫。

6.關鍵説明

• 候選主庫選擇：通過 candidate_master=1 指定優先成為主庫的節點（通常選數據最新、性能較好的從庫）。
• 自動恢復：故障切換後，原主庫修復後需手動配置為新主庫的從庫，再重新加入 MHA 集羣。
• 監控：可配合 masterha_check_status 查看 MHA 狀態，確保管理進程正常運行。

五、MGR高可用

MySQL Group Replication（MGR）是官方高可用方案，基於分佈式一致性協議實現節點間數據同步和故障自動恢復，支持單主 / 多主模式。以下以 3 節點（10.0.0.100、10.0.0.101、10.0.0.102）單主模式 為例，簡要説明配置步驟：

前提條件

1. 所有節點 MySQL 版本≥8.0（推薦，5.7 需滿足特定版本），且已安裝 group_replication 插件。
2. 節點間網絡互通（3306 端口用於 MySQL 服務，33061 端口用於組複製通信）。
3. 所有節點初始數據一致（可通過備份恢復或 GTID 同步初始化）。

配置步驟

1. 所有節點修改 MySQL 配置（my.cnf）

核心配置（每個節點需修改 server-id 和 local_address 為自身信息）：

[mysqld]
# 基礎配置（每個節點唯一）
server-id=100  # 100/101/102 分別對應三個節點
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# 組複製依賴（必須開啓）
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE  # 禁用binlog校驗
log_bin=binlog
log_slave_updates=ON
relay_log_info_repository=TABLE
master_info_repository=TABLE
transaction_write_set_extraction=XXHASH64  # 用於檢測事務衝突

# 組複製配置（所有節點統一）
plugin_load_add='group_replication.so'  # 加載插件
group_replication_group_name="ce32b9e1-xxxx-xxxx-xxxx-xxxxxxxxxxxx"  # 自定義UUID（所有節點相同）
group_replication_start_on_boot=OFF  # 首次配置不自動啓動
group_replication_local_address="10.0.0.100:33061"  # 本節點通信地址（IP:33061）
group_replication_group_seeds="10.0.0.100:33061,10.0.0.101:33061,10.0.0.102:33061"  # 所有節點通信地址
group_replication_bootstrap_group=OFF  # 僅初始化節點設為ON

2. 創建組複製專用用户（所有節點執行）

用於節點間通信認證：

-- 登錄MySQL
mysql -u root -p

-- 創建用户（所有節點用户名/密碼一致）
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 配置複製通道（使用上述用户）
CHANGE MASTER TO 
  MASTER_USER='repl', 
  MASTER_PASSWORD='repl_password' 
  FOR CHANNEL 'group_replication_recovery';

3. 初始化集羣（僅在第一個節點 10.0.0.100 執行）

-- 啓動組複製（首次初始化需開啓bootstrap）
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 查看狀態（確認加入成功，角色為PRIMARY）
SELECT * FROM performance_schema.replication_group_members;

4. 其他節點加入集羣（10.0.0.101、10.0.0.102 執行）

-- 直接啓動組複製，自動加入集羣
START GROUP_REPLICATION;

-- 查看狀態（確認兩個節點角色為SECONDARY，成員數為3）
SELECT * FROM performance_schema.replication_group_members;

5. 驗證集羣狀態

• 所有節點執行 SELECT * FROM performance_schema.replication_group_members;，確認 3 個節點均為 ONLINE 狀態。
• 單主模式下，100 為 PRIMARY（可讀寫），101/102 為 SECONDARY（只讀）。

6. 故障自動切換測試

• 手動停止主節點 100 的 MySQL 服務：systemctl stop mysqld。
• 等待約 10 秒，在 101 或 102 上執行 SELECT * FROM performance_schema.replication_group_members;，會發現其中一個節點自動變為 PRIMARY（新主庫）。

7.關鍵説明

• 單主 vs 多主：默認單主（推薦，避免寫入衝突），如需多主，需設置 group_replication_single_primary_mode=OFF。
• 節點恢復：故障節點修復後，重啓 MySQL 並執行 START GROUP_REPLICATION; 可自動重新加入集羣（作為 SECONDARY）。
• 客户端連接：需配合 VIP（虛擬 IP）或負載均衡工具（如 ProxySQL），讓客户端自動連接到當前主庫。

以上為核心步驟，生產環境需額外配置防火牆（開放 33061 端口）、監控（如 MGR 健康狀態）等。