一、基本介紹

在開發(fā)領(lǐng)域,MySQL 是一種流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。隨著技術(shù)的不斷發(fā)展,很多開發(fā)者希望能夠?qū)?MySQL 數(shù)據(jù)庫部署到線上環(huán)境,以支持真實的應(yīng)用需求

二、線上使用經(jīng)驗

1、MySQL慢查詢?nèi)罩?/h3>

連接數(shù)據(jù)庫，使用SHOW VARIABLES LIKE '%slow_query_log%';命令查詢慢查詢?nèi)罩鹃_關(guān)，輸出如下：

slow_query_log的值OFF表示慢查詢?nèi)罩居涗浌δ苁顷P(guān)閉的，需要設(shè)置成ON才能打開；
slow_query_log_file的值為慢查詢?nèi)罩镜奈募窂剑?/p>

執(zhí)行SET GLOBAL slow_query_log = ON;打開慢查詢?nèi)罩鹃_關(guān)，線上慎用。

執(zhí)行命令SHOW VARIABLES LIKE '%long_query_time%';輸出慢查詢的攔截時間，默認時間是10s：

可通過SET long_query_time = 1;命令修改慢查詢的攔截時間，比如說設(shè)置成1s。

測試一下：

另起一個窗口，執(zhí)行tail -f /usr/local/mysql-8/data/localhost-slow.log監(jiān)控慢查詢?nèi)罩緝?nèi)容，然后這邊執(zhí)行一個超過1秒的查詢，如下圖所示：

END

2、生產(chǎn)環(huán)境大數(shù)據(jù)表如何添加索引

生產(chǎn)環(huán)境單個數(shù)據(jù)庫的前提下，如果單表數(shù)據(jù)量很大達上千萬數(shù)據(jù)了，如何不影響主業(yè)務(wù)運行的前提下給大表某個字段添加索引呢，答案有很多種，下面介紹以下兩種方式：

（1）使用MySQL自帶的Oline DDL功能： MySQL提供了ALGORITHM=INPLACE選項，允許在不鎖定表的情況下添加索引，這就意味著即使在添加索引時，應(yīng)用程序仍然可以訪問該表的其他數(shù)據(jù)，其中INPLACE算法可以有效減少對業(yè)務(wù)的影響，創(chuàng)建索引的SQL語句如下所示：

ALTER TABLE your_table
ALGORITHM = INPLACE,
LOCK = NONE,
ADD INDEX index_name (column1, column2);

• ALGORITHM=INPLACE:指定使用就地算法進行表的修改，這樣MySQL就不會復(fù)制表數(shù)據(jù)，而是直接修改原始表的數(shù)據(jù)結(jié)構(gòu)；
• LOCK=NONE:在索引創(chuàng)建過程中不會對表進行鎖定，其他操作可以繼續(xù)進行，這樣可以最大程度減少對業(yè)務(wù)的影響；

此法最適用InnoDB存儲引擎，需要MySQL版本再5.6及以上。

（2）使用 pt-online-schema-change 工具： 如果表非常大，或者不支持在線索引創(chuàng)建，另一種常用的方案是使用 Percona Toolkit 中的 pt-online-schema-change 工具。該工具的工作原理是創(chuàng)建一個新的表，然后逐漸將數(shù)據(jù)從原表遷移到新表中，完成后將表切換過來，整個過程不會對業(yè)務(wù)造成大的影響。

# 以下是使用 pt-online-schema-change 工具添加索引的示例：
pt-online-schema-change --alter "ADD INDEX index_name (column1, column2)" D=your_database,t=your_table --execute

3、MySQL在線修改表結(jié)構(gòu)

其實和第二點是一樣的，使用Online DDL或者pt-online-schema-change 工具，這里不做過多介紹，直接上操作：

（1）使用MySQL自帶的Oline DDL

ALTER TABLE your_table
ALGORITHM = INPLACE,
LOCK = NONE,
ADD COLUMN 字段名 varchar(20)  default  ''  comment '這是字段描述';

此法最適用InnoDB存儲引擎，需要MySQL版本再5.6及以上。

（2）使用 pt-online-schema-change 工具：

執(zhí)行命令：pt-online-schema-change --socket=/tmp/mysql.sock --user=用戶名 --password=密碼 --alter "add column 字段名 varchar(20) default '' comment '這是字段描述' " D=數(shù)據(jù)庫名, t=表名 --print --execute；

關(guān)于socket的路徑，大家可根據(jù)/etc/my.cnf配置文件中配置的路徑選擇填寫。

這種方式是要錄入數(shù)據(jù)庫名、表名、賬號、密碼，然后還有字段名、類型、字段描述，這里我默認設(shè)置的varchar(20)，簡單易懂，可以自行按照業(yè)務(wù)來設(shè)置，執(zhí)行前請在測試環(huán)境先嘗試一遍。

演示一下：

執(zhí)行命令：

/usr/bin/pt-online-schema-change \
--socket=/tmp/mysql.sock \
--user=root \
--password='dinglian123@' \
--alter="ADD COLUMN description varchar(20) DEFAULT '' COMMENT '這是字段描述'" \
D=web_app,t=user \
--print \
--execute

再去查詢表結(jié)構(gòu)：

4、MySQL備份/恢復(fù)工具mysqldump

MySQL常用的備份/恢復(fù)工具有mysqldump、mydumper、XtraBackup。

• mysqldump工具： 這是官方自帶的工具；
• mydumper工具： 邏輯備份工具；
• XtraBackup工具：物理熱備份工具；

本案例講述的是使用mysqldump備份數(shù)據(jù)庫，實現(xiàn)原理為通過協(xié)議鏈接到MySQL數(shù)據(jù)庫，將需要備份的數(shù)據(jù)查詢出來，然后再將查詢出來的數(shù)據(jù)轉(zhuǎn)化為insert語句，當(dāng)我們需要還原數(shù)據(jù)時只需要執(zhí)行這些insert語句就可以恢復(fù)數(shù)據(jù)。下面進行實戰(zhàn)

mysqldump 是 MySQL 自帶的邏輯備份工具。它的備份原理是通過協(xié)議連接到MySQL 數(shù)據(jù)庫，將需要備份的數(shù)據(jù)查詢出來，將查詢出的數(shù)據(jù)轉(zhuǎn)換成對應(yīng)的insert語句，當(dāng)我們需要還原這些數(shù)據(jù)時，只要執(zhí)行這些insert 語句，即可將對應(yīng)的數(shù)據(jù)還原。

案例一：mysqldump備份單個數(shù)據(jù)庫

（1）備份一個數(shù)據(jù)庫

目前我們有web_app數(shù)據(jù)庫下有一個user表，如下圖所示：

備份該數(shù)據(jù)庫，執(zhí)行命令：mysqldump --user=root -p --databases web_app > web_app20251217.sql
將本機的web_app數(shù)據(jù)庫備份到當(dāng)前目錄下的web_app20251217.sql中，其他參數(shù)如下所示：

-p			 :  數(shù)據(jù)庫密碼
--host=name  :  數(shù)據(jù)庫地址,本機可以不寫
--user=name  :  數(shù)據(jù)庫賬號
--port=端口  :  數(shù)據(jù)庫端口,如果默認是3306可以不寫
--databases  :  導(dǎo)出的數(shù)據(jù)庫名
--tables     :  導(dǎo)出指定表的數(shù)據(jù)和結(jié)構(gòu)

執(zhí)行結(jié)果：

（2）刪除web_app數(shù)據(jù)庫，然后使用備份文件恢復(fù)該數(shù)據(jù)庫：

先刪除數(shù)據(jù)庫，然后在創(chuàng)建數(shù)據(jù)庫web_app，此時的庫下沒有任何表或者數(shù)據(jù)，然后再使用source 文件路徑命令進行恢復(fù)，如圖所示：

END…

案例二：備份所有數(shù)據(jù)庫

mysqldump -u root -p -A > all_$(date + '+%F').sql

# 終端提示輸入密碼
Enter password: 

# 執(zhí)行完成后，當(dāng)前目錄生成文件：all_2025-12-17.sql

以 root 用戶身份登錄 MySQL，輸入密碼后，導(dǎo)出服務(wù)器上所有數(shù)據(jù)庫的結(jié)構(gòu)和數(shù)據(jù)，并將導(dǎo)出的 SQL 內(nèi)容保存到當(dāng)前目錄下，以「all_年月日.sql」命名的文件中。

案例三：備份指定數(shù)據(jù)

備份單庫或者多庫

# 格式：mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 表名 > 備份文件名.sql
mysqldump -u root -p -B 數(shù)據(jù)庫名 > 數(shù)據(jù)目名_$(date + '+%F').sql

# 備份db1和db2兩個數(shù)據(jù)庫，生成合并的備份文件
mysqldump -u root -p -B db1 db2 > db1_db2_$(date + '+%F').sql

以 root 用戶身份登錄 MySQL，輸入密碼后，備份指定的單個數(shù)據(jù)庫（含該庫下所有表、視圖、存儲過程等），并將備份的 SQL 文件以「數(shù)據(jù)庫名_年月日.sql」的格式保存到當(dāng)前目錄。

恢復(fù)命令：

# 帶 -B 備份的恢復(fù)（無需提前建庫）
mysql -u root -p < 數(shù)據(jù)庫名_2025-12-17.sql

# 單表備份的恢復(fù)（需先建庫并切換）
mysql -u root -p
mysql> CREATE DATABASE IF NOT EXISTS test_db; # 建庫
mysql> USE test_db; # 切換到目標庫
mysql> SOURCE user_2025-12-17.sql; # 恢復(fù)表

案例四：只導(dǎo)出數(shù)據(jù)庫表結(jié)構(gòu)，不包含數(shù)據(jù)

mysqldump -u root -p -B -d db_name > db_name_not_data_$(date + '+%F').sql

案例五：只導(dǎo)出數(shù)據(jù)，不添加建表語句

mysqldump -u root -p -B -t db_name > db_name_not_create_info_$(date + '+%F').sql

案例六：導(dǎo)出指定數(shù)據(jù)庫的指定表

mysqldump -u root -p  庫名  --tables 表名 > db_name_表名_$(date + '+%F').sql

案例七：導(dǎo)出指定數(shù)據(jù)庫的多個表

mysqldump -u root -p  庫名  --tables 表名1  表名2  表3... > db_name_tables_$(date + '+%F').sql

案例八：壓縮備份

# 壓縮備份
mysqldump -u root -p -B 庫名 --tables 表名 | gzip > db_name_table_$(date + '+%F').sql.gz


## 解壓恢復(fù)的兩種方法
# 方法1
gzip < db_name_table_2025-12-17.sql.gz  | mysql -u root -p mydatabase
# 方法2
zcat db_name_table_2025-12-17.sql.gz | mysql -u root -p mydatabase

5、MySQL主從數(shù)據(jù)不一致的數(shù)據(jù)修復(fù)

pt-table-sync 是 Percona 工具集里用于校驗 / 同步 MySQL 主從數(shù)據(jù)不一致的工具

# 目標端(從庫)在前，源端(主庫)在參數(shù)中，--sync-to-source 表示以源端為基準同步目標端
# 驗證
pt-table-sync --sync-to-source \
-u從庫用戶名 -p從庫密碼 \
h=主庫ip,D=數(shù)據(jù)庫名,t=表名,u=主庫用戶名,p=主庫密碼 \
h=從庫ip \
--print

# 執(zhí)行
pt-table-sync --sync-to-source \
-u從庫用戶名 -p從庫密碼 \
h=主庫ip,D=數(shù)據(jù)庫名,t=表名,u=主庫用戶名,p=主庫密碼 \
h=從庫ip \
--execute

修復(fù)的時間比較長，如果是上千萬的數(shù)據(jù)，大概需要半小時至一小時左右。

需要注意的點：–print會把差異數(shù)據(jù)打印出來，如果數(shù)據(jù)比較多的話，建議不要使用這個命令，直接使用pt-table-checksum 工具來校驗。

6、pt-table-checksum 工具校驗主從不一致的差異

pt-table-checksum 是 Percona 工具集里用于校驗 MySQL 主從數(shù)據(jù)一致性的工具（僅檢測不一致，不修復(fù)），會通過在主庫執(zhí)行校驗和計算，對比從庫的結(jié)果，輸出具體哪些表 / 哪些行數(shù)據(jù)不一致；

pt-table-sync 則基于 pt-table-checksum 的校驗結(jié)果，精準修復(fù)主從數(shù)據(jù)差異（以主庫為基準同步從庫）。

文末安裝了 pt-online-schema-change 時會同步包含 pt-table-checksum 工具—— 因為這兩個工具都屬于 Percona Toolkit 核心組件，官方打包時會將整個工具集作為一個整體發(fā)布，而非單獨拆分。

第一步：用 pt-table-checksum 校驗數(shù)據(jù)不一致范圍

在從庫上執(zhí)行以下命令：

pt-table-checksum \
--nocheck-replication-filters \  # 不檢查復(fù)制過濾規(guī)則（避免漏檢）
--no-check-binlog-format \       # 不檢查binlog格式（兼容ROW/STATEMENT格式）
--replicate=test.checksum_results \  # 校驗結(jié)果存入主庫 test 庫的 checksum_results 表（自動創(chuàng)建）
h=主庫IP,u=校驗賬號,p=密碼,P=3306 \  # 主庫信息（IP/賬號/密碼/端口）
-d 數(shù)據(jù)庫名 \  # 指定要校驗的數(shù)據(jù)庫（只校驗該庫，不寫則校驗所有庫）
-t 表名       # 指定要校驗的表（只校驗該表，不寫則校驗庫下所有表）

執(zhí)行完命令后，終端會輸出校驗結(jié)果，核心字段解讀：

DATABASE：數(shù)據(jù)庫名
TABLE：表名
CHUNKS：表被拆分的校驗塊數(shù) -
DIFFS：不一致的塊數(shù)，大于 0 表示該表有數(shù)據(jù)不一致
ROWS：校驗的總行數(shù) 對比主庫 3000w、從庫 2800w，看是否匹配
ERRORS： 校驗錯誤數(shù) 大于 0 需排查（如權(quán)限、網(wǎng)絡(luò)問題）

也可直接查詢主庫的校驗結(jié)果表：

-- 主庫執(zhí)行，查詢不一致的表
SELECT db, tbl, diffs FROM test.checksum_results WHERE diffs > 0;

第二步：用 pt-table-sync 修復(fù)數(shù)據(jù)不一致

# 先打印修復(fù) SQL（不執(zhí)行，驗證準確性）
pt-table-sync \
--sync-to-source \  # 以主庫（source）為基準，修復(fù)從庫數(shù)據(jù)
--print \           # 僅打印修復(fù)SQL（不實際執(zhí)行，先核對）
--charset=utf8mb4 \ # 指定字符集（避免亂碼）
h=主庫ip,D=數(shù)據(jù)庫名,t=表名,u=主從同步的用戶,p=你的密碼 \  # 主庫（源端）信息
h=從庫ip,u=主從同步的用戶,p=你的密碼 \                     # 從庫（目標端）信息

#  執(zhí)行修復(fù)（確認 SQL 無誤后）
pt-table-sync \
--sync-to-source \  
--print \           
--charset=utf8mb4 \ 
h=主庫ip,D=數(shù)據(jù)庫名,t=表名,u=主從同步的用戶,p=你的密碼 \ 
h=從庫ip,u=主從同步的用戶,p=你的密碼 \  

–sync-to-source：核心：以 h=192.168.0.1（主庫）為基準，同步 h=192.168.0.2（從庫）數(shù)據(jù)
–print：僅輸出需要執(zhí)行的 INSERT/UPDATE/DELETE 語句，用于驗證修復(fù)邏輯
–execute：執(zhí)行修復(fù) SQL（務(wù)必先通過 --print 驗證，再執(zhí)行）

注意事項：

• 數(shù)據(jù)量較大（3000w）的優(yōu)化：校驗 / 修復(fù)時指定單表（-t 表名），避免全庫操作耗時過久，同時業(yè)務(wù)低峰期再去執(zhí)行；
• 修復(fù)前確認從庫的 IO/SQL 線程是 Running 狀態(tài)（show slave status\G）；
• 校驗 / 修復(fù)賬號必須有主從庫的 SELECT、UPDATE、DELETE、INSERT、SUPER 權(quán)限；
• 修復(fù)完成后，重新執(zhí)行 pt-table-checksum，確認 DIFFS=0（無不一致）；

三、如何安裝pt-online-schema-change

方式一：通過percona-toolkit.tar.gz安裝包解壓安裝

# 下載
wget percona.com/get/percona-toolkit.tar.gz
# 解壓
tar -zvxf percona-toolkit.tar.gz
cd percona-toolkit-3.7.0-2
# 安裝perl依賴
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
perl Makefile.PL
# 編譯安裝
make
make install
# 驗證
pt-online-schema-change

方式二：RPM 包方式安裝

# 1.  從 Percona 官方鏡像下載（推薦）
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.5/binary/redhat/7/x86_64/percona-toolkit-3.5.5-1.el7.x86_64.rpm

# 備用鏈接（阿里云鏡像）
wget https://mirrors.aliyun.com/percona/yum/release/7/RPMS/x86_64/percona-toolkit-3.5.5-1.el7.x86_64.rpm

# 2. 安裝（加 --force --nodeps 忽略依賴警告，前提是已裝完 Perl 依賴）
rpm -ivh percona-toolkit-3.5.5-1.el7.x86_64.rpm --force --nodeps

# 3. 查看版本，輸出正常則安裝完成
pt-online-schema-change --version

注意：若 RPM 安裝仍報錯，可直接解壓 RPM 包，將工具二進制文件放到系統(tǒng)路徑（這是在上面步驟安裝失敗的前提下）：

# 1. 解壓 RPM 包
rpm2cpio ../percona-toolkit-3.5.5-1.el7.x86_64.rpm | cpio -idmv

# 2. 將工具拷貝到 /usr/bin（系統(tǒng)可執(zhí)行路徑）
cp -r usr/bin/* /usr/bin/

# 3. 賦予執(zhí)行權(quán)限
chmod +x /usr/bin/pt-online-schema-change

# 4. 驗證
pt-online-schema-change --version

離線安裝包下載地址

如果是離線的系統(tǒng)說缺少依賴，可以點擊這個網(wǎng)址去下載：https://mirrors.163.com/centos-vault/7.9.2009/os/x86_64/Packages/

下載完成后，通過執(zhí)行：

rpm -ivh perl-Digest-1.17-245.el7.noarch.rpm
rpm -ivh perl-Digest-MD5-2.52-3.el7.x86_64.rpm

報錯缺少perl-Digest-MD5包

如果報錯說缺少perl-Digest-MD5包，那么就安裝一下yum -y install perl-Digest-MD5

四、其他

其他的線上經(jīng)驗，后續(xù)再分享了，目前只想得起這么多，哈哈…

到此這篇關(guān)于MySQL線上使用經(jīng)驗分享的文章就介紹到這了,更多相關(guān)MySQL線上使用經(jīng)驗內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論