shell腳本鏈接、讀寫、操做mysql數據庫實例詳情 - 數據庫,mysql,mysql,MySQL,服務器,Python,後端開發 mob64ca13fba42b 博客

在日常運維或開發工作中，我們經常需要對MySQL數據庫進行重複性操作（如批量查詢、數據統計、定時監控等）。手動執行SQL命令不僅效率低下，還容易出錯。Shell腳本作為Linux系統下的自動化工具，能與MySQL完美結合，實現操作的自動化與批量化。本文將從實驗環境準備出發，通過多個實戰案例，詳細講解如何用Shell腳本高效操作MySQL數據庫，所有案例均可直接複用或根據需求修改。

一、實驗環境準備

在開始Shell腳本實戰前，需先完成MySQL環境的基礎配置，包括創建測試用户、數據表，以及熟悉Linux命令行與MySQL的直接交互方式。

1.1 創建MySQL測試用户與數據表

為避免使用root用户操作（降低安全風險），我們先創建一個專用測試用户shell_rw，並授權必要權限；同時創建測試數據庫和數據表，用於後續腳本測試。

-- 1. 創建測試用户shell_rw（允許遠程訪問，密碼為Idyq8_a1，生產環境需替換為強密碼）
CREATE USER 'shell_rw'@'%' IDENTIFIED BY 'Idyq8_a1';

-- 2. 授權用户權限（create/alter用於表管理，insert/delete/select/update用於數據操作）
GRANT create,alter,insert,delete,select,update,show view ON *.* TO 'shell_rw'@'%';

-- 3. 切換到測試數據庫（若martin庫不存在，需先執行CREATE DATABASE martin;）
USE martin;

-- 4. 創建測試表products（存儲商品名稱與價格，id自增為主鍵）
CREATE TABLE products (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  price decimal(10, 2) NOT NULL,
  PRIMARY KEY (id)
);

執行方式：可通過MySQL客户端直接執行，或在Linux命令行用mysql -uroot -p -e "SQL語句"批量執行。

1.2 Linux命令行直接連接MySQL

在編寫Shell腳本前，先熟悉Linux命令行與MySQL的直接交互方式，核心參數如下：

-u：指定MySQL用户名
-p：指定密碼（密碼緊跟-p，無空格；或僅寫-p，執行後手動輸入密碼，更安全）
-h：指定MySQL服務器IP（本地可省略，默認localhost）
-P：指定MySQL端口（默認3306，可省略）
-e：直接執行SQL語句（無需進入MySQL交互界面）
數據庫名：指定默認操作的數據庫（可選）

示例1：連接MySQL並查看所有數據庫

# 方式1：密碼明文（僅測試環境使用，生產環境禁用）
mysql -ushell_rw -p'Idyq8_a1' -e "show databases;"

# 方式2：密碼交互輸入（推薦，避免明文泄露）
mysql -ushell_rw -p -e "show databases;"

示例2：直接連接指定數據庫並查看錶

# 連接martin庫，查看所有表
mysql -ushell_rw -p'Idyq8_a1' martin -e "show tables;"

1.3 Linux命令行查詢MySQL數據

直接在命令行執行查詢語句，無需進入MySQL交互模式，適合快速驗證數據或集成到腳本中。

示例：查詢martin庫中t1表的前1條數據

mysql -ushell_rw -p'Idyq8_a1' martin -e "select * from t1 limit 1;"

説明：若查詢結果需格式化（如對齊），可搭配awk或column命令，例如：

mysql -ushell_rw -p'Idyq8_a1' martin -e "select * from t1 limit 5;" | column -t

二、Shell腳本操作MySQL實戰案例

掌握基礎交互後，我們通過5個高頻實戰案例，實現MySQL操作的自動化。所有腳本均包含錯誤檢查（如MySQL客户端是否安裝、文件是否存在）和清晰註釋，確保可複用性。

2.1 基礎實戰：Shell腳本實現MySQL增刪查改（CRUD）

需求：通過Shell腳本批量執行MySQL的插入、查詢、更新、刪除操作，適用於重複性數據操作場景（如測試數據生成）。

步驟1：創建腳本文件

# 1. 創建腳本存放目錄（統一管理）
mkdir -p /data/script/mysql_shell && cd /data/script/mysql_shell

# 2. 創建腳本文件mysql_crud.sh
vim mysql_crud.sh

步驟2：編寫腳本內容

#!/bin/bash
# 腳本功能：MySQL增刪查改（CRUD）自動化操作
# 依賴：Linux環境已安裝MySQL客户端（mysql命令）

# -------------------------- 1. 配置MySQL連接信息 --------------------------
mysql_host='localhost'  # MySQL服務器IP（遠程需改為實際IP，如192.168.1.100）
mysql_user='shell_rw'   # 連接用户
mysql_pass='Idyq8_a1'   # 密碼（生產環境建議用配置文件，見下文注意事項）
mysql_db='martin'       # 目標數據庫

# -------------------------- 2. 執行CRUD操作 --------------------------
echo "=== 1. 插入數據到products表 ==="
# 插入一條商品數據（apple，價格10.50）
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" "$mysql_db" -e "
insert into products (name,price) values ('apple',10.50);
"

echo -e "\n=== 2. 查詢插入的數據 ==="
# 查詢name為apple的商品
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" "$mysql_db" -e "
select * from products where name='apple';
"

echo -e "\n=== 3. 更新數據 ==="
# 將apple的價格修改為12.50
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" "$mysql_db" -e "
update products set price=12.5 where name='apple';
"
# 再次查詢驗證更新結果
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" "$mysql_db" -e "
select * from products where name='apple';
"

echo -e "\n=== 4. 刪除數據 ==="
# 刪除name為apple的商品
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" "$mysql_db" -e "
delete from products where name='apple';
"
# 再次查詢驗證刪除結果（應無數據）
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" "$mysql_db" -e "
select * from products where name='apple';
"

echo -e "\n=== CRUD操作完成 ==="

步驟3：執行腳本

# 1. 給腳本添加執行權限
chmod +x mysql_crud.sh

# 2. 執行腳本
./mysql_crud.sh

預期結果：腳本會依次輸出“插入→查詢→更新→查詢→刪除→查詢”的結果，驗證每一步操作是否成功。

2.2 運維必備：查詢MySQL活躍連接並導出日誌

需求：查詢MySQL當前活躍連接（SHOW FULL PROCESSLIST），並將結果按“時間戳”命名保存到日誌文件，用於運維排查連接異常（如慢查詢、連接泄露）。

腳本文件：mysql_processlist.sh

#!/bin/bash
# 腳本功能：查詢MySQL活躍連接並導出到日誌文件
# 輸出文件：processlist_YYYYMMDDHHMMSS.log（當前路徑）

# -------------------------- 1. 配置MySQL連接信息 --------------------------
USER="shell_rw"
PASSWORD="Idyq8_a1"
HOST="localhost"
PORT="3306"

# -------------------------- 2. 基礎檢查 --------------------------
# 檢查MySQL客户端是否安裝
if ! command -v mysql &> /dev/null; then
    echo "錯誤：未找到mysql客户端，請先安裝（如yum install mysql-community-client）。"
    exit 1  # 退出腳本，狀態碼1表示錯誤
fi

# -------------------------- 3. 生成日誌文件名 --------------------------
# 格式化當前時間（YYYYMMDDHHMMSS，如20241014153000）
CURRENT_TIME=$(date +"%Y%m%d%H%M%S")
OUTPUT_FILE="processlist_${CURRENT_TIME}.log"

# -------------------------- 4. 查詢並導出活躍連接 --------------------------
echo "正在查詢MySQL活躍連接，結果將保存到：${OUTPUT_FILE}"
# 執行SHOW FULL PROCESSLIST，並將結果寫入日誌（2>/dev/null屏蔽錯誤輸出）
mysql -h "$HOST" -P "$PORT" -u "$USER" -p"$PASSWORD" -e "SHOW FULL PROCESSLIST;" > "$OUTPUT_FILE" 2>/dev/null

# 檢查命令是否執行成功（$?為上一條命令的狀態碼，0表示成功）
if [ $? -ne 0 ]; then
    echo "錯誤：無法連接MySQL或查詢失敗，請檢查IP、用户名、密碼是否正確。"
    rm -f "$OUTPUT_FILE"  # 刪除空日誌文件
    exit 1
fi

echo "查詢完成！日誌文件路徑：$(pwd)/${OUTPUT_FILE}"

執行與驗證

# 1. 執行腳本
chmod +x mysql_processlist.sh && ./mysql_processlist.sh

# 2. 查看日誌內容
cat processlist_20241014153000.log  # 替換為實際生成的文件名

2.3 批量管理：多MySQL服務器變量查詢腳本

需求：現有多個MySQL服務器（IP、用户名、密碼存於ip.txt），需批量查詢某一全局變量（如read_only、max_connections）的值，適用於集羣環境的配置一致性檢查。

步驟1：準備IP配置文件ip.txt

在腳本同一目錄下創建ip.txt，格式為“IP,用户名,密碼”，每行對應一台MySQL服務器：

192.168.152.70,shell_rw,xxUagc8_6
192.168.152.71,shell_rw,dagdacag6
192.168.12.161,shell_rw,Idyq8_al  # 新增測試服務器

步驟2：編寫腳本query_mysql_variable.sh

#!/bin/bash
# 腳本功能：批量查詢多台MySQL服務器的指定全局變量值
# 依賴文件：ip.txt（需與腳本同目錄，格式：IP,用户名,密碼）

# -------------------------- 1. 基礎檢查 --------------------------
# 檢查ip.txt是否存在
if [ ! -f "ip.txt" ]; then
    echo "錯誤：ip.txt文件不存在，請將其放在當前目錄（$(pwd)）。"
    exit 1
fi

# 檢查MySQL客户端是否安裝
if ! command -v mysql &> /dev/null; then
    echo "錯誤：未找到mysql客户端，請先安裝。"
    exit 1
fi

# -------------------------- 2. 接收用户輸入（要查詢的變量名） --------------------------
read -p "請輸入要查詢的MySQL全局變量名（如read_only、max_connections）：" VARIABLE_NAME

# -------------------------- 3. 批量查詢多台服務器 --------------------------
echo -e "\n=== 開始查詢所有MySQL服務器的變量：${VARIABLE_NAME} ==="
echo "=========================================================="

# 逐行讀取ip.txt（IFS=','指定分隔符為逗號）
while IFS=',' read -r IP USERNAME PASSWORD; do
    echo "正在查詢服務器：$IP"
    
    # 執行變量查詢（grep -v "Variable_name"過濾表頭）
    RESULT=$(mysql -h "$IP" -u "$USERNAME" -p"$PASSWORD" -e "SHOW GLOBAL VARIABLES LIKE '$VARIABLE_NAME';" 2>/dev/null | grep -v "Variable_name")
    
    # 判斷結果是否為空（空表示連接失敗或變量不存在）
    if [ -z "$RESULT" ]; then
        echo "  狀態：無法連接服務器，或變量'$VARIABLE_NAME'不存在"
    else
        echo "  結果：$RESULT"
    fi
    
    echo "----------------------------------------------------------"
done < ip.txt  # 將ip.txt作為輸入源，傳遞給while循環

echo "=== 所有服務器查詢完成 ==="

執行與示例

# 1. 執行腳本
chmod +x query_mysql_variable.sh && ./query_mysql_variable.sh

# 2. 輸入變量名（如read_only）
請輸入要查詢的MySQL全局變量名（如read_only、max_connections）：read_only

# 3. 預期輸出
=== 開始查詢所有MySQL服務器的變量：read_only ===
==========================================================
正在查詢服務器：192.168.152.70
  結果：read_only	OFF
----------------------------------------------------------
正在查詢服務器：192.168.152.71
  結果：read_only	ON
----------------------------------------------------------
正在查詢服務器：192.168.12.161
  狀態：無法連接服務器，或變量'read_only'不存在
----------------------------------------------------------
=== 所有服務器查詢完成 ===

2.4 性能監控：統計MySQL查詢與慢查詢數量

需求：腳本運行10秒，統計這10秒內MySQL的總查詢次數（Questions變量）和慢查詢次數（Slow_queries變量），用於簡單的性能監控。

腳本文件：mysql_stats_collector.sh

#!/bin/bash
# 腳本功能：統計10秒內MySQL的查詢次數與慢查詢次數
# 依賴：MySQL慢查詢日誌已開啓（否則Slow_queries統計為0）

# -------------------------- 1. 配置MySQL連接信息 --------------------------
MYSQL_USER="shell_rw"
MYSQL_PASSWORD="Idyq8_a1"
MYSQL_HOST="localhost"

# -------------------------- 2. 基礎檢查 --------------------------
if ! command -v mysql &> /dev/null; then
    echo "錯誤：未找到mysql客户端，請先安裝。"
    exit 1
fi

# -------------------------- 3. 記錄10秒前的初始值 --------------------------
echo "=== 開始統計（將持續10秒）==="
# 獲取初始查詢次數（Questions：MySQL啓動後總查詢數）
QUERY_COUNT_START=$(mysql -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW GLOBAL STATUS LIKE 'Questions'" 2>/dev/null | awk '{print $2}' | tail -n 1)

# 獲取初始慢查詢次數（Slow_queries：MySQL啓動後總慢查詢數）
SLOW_QUERY_COUNT_START=$(mysql -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW GLOBAL STATUS LIKE 'Slow_queries'" 2>/dev/null | awk '{print $2}' | tail -n 1)

# 檢查初始值是否獲取成功
if [ -z "$QUERY_COUNT_START" ] || [ -z "$SLOW_QUERY_COUNT_START" ]; then
    echo "錯誤：無法獲取MySQL狀態變量，請檢查連接配置。"
    exit 1
fi

# -------------------------- 4. 等待10秒 --------------------------
sleep 10

# -------------------------- 5. 記錄10秒後的最終值 --------------------------
QUERY_COUNT_END=$(mysql -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW GLOBAL STATUS LIKE 'Questions'" 2>/dev/null | awk '{print $2}' | tail -n 1)
SLOW_QUERY_COUNT_END=$(mysql -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW GLOBAL STATUS LIKE 'Slow_queries'" 2>/dev/null | awk '{print $2}' | tail -n 1)

# -------------------------- 6. 計算10秒內的增量 --------------------------
QUERY_COUNT=$((QUERY_COUNT_END - QUERY_COUNT_START))
SLOW_QUERY_COUNT=$((SLOW_QUERY_COUNT_END - SLOW_QUERY_COUNT_START))

# -------------------------- 7. 輸出統計結果 --------------------------
echo -e "\n=== 10秒內MySQL性能統計 ==="
echo "總查詢次數：$QUERY_COUNT"
echo "慢查詢次數：$SLOW_QUERY_COUNT"
echo "=========================="

執行與説明

# 1. 執行腳本
chmod +x mysql_stats_collector.sh && ./mysql_stats_collector.sh

# 2. 預期輸出
=== 開始統計（將持續10秒）===

=== 10秒內MySQL性能統計 ===
總查詢次數：28
慢查詢次數：1
==========================

關鍵變量説明：

Questions：MySQL服務啓動後接收的所有SQL查詢次數（包括SELECT、INSERT等）。
Slow_queries：執行時間超過long_query_time（默認10秒）的查詢次數，需確保慢查詢日誌已開啓（slow_query_log=ON）。

2.5 數據盤點：統計數據庫表信息（數據量、自增值、引擎）

需求：查詢指定數據庫中所有表的預估數據量（MB）、自增值（若有自增主鍵）、存儲引擎（如InnoDB、MyISAM），用於數據盤點或容量規劃。

腳本文件：mysql_tables_stats.sh

#!/bin/bash
# 腳本功能：統計指定MySQL數據庫中所有表的元信息（數據量、自增值、引擎）
# 用法：./mysql_tables_stats.sh <數據庫名>（如./mysql_tables_stats.sh martin）

# -------------------------- 1. 配置MySQL連接信息 --------------------------
USER="shell_rw"
PASSWORD="Idyq8_a1"
HOST="localhost"
PORT="3306"

# -------------------------- 2. 基礎檢查 --------------------------
# 檢查是否傳入數據庫名參數
if [ -z "$1" ]; then
    echo "用法錯誤：請指定數據庫名！"
    echo "示例：./mysql_tables_stats.sh martin"
    exit 1
fi
DATABASE="$1"  # $1表示腳本的第一個參數（數據庫名）

# 檢查MySQL客户端是否安裝
if ! command -v mysql &> /dev/null; then
    echo "錯誤：未找到mysql客户端，請先安裝。"
    exit 1
fi

# 檢查指定數據庫是否存在
DB_EXISTS=$(mysql -h"$HOST" -P"$PORT" -u"$USER" -p"$PASSWORD" -sN -e "SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name = '$DATABASE'" 2>/dev/null)
if [ "$DB_EXISTS" -eq 0 ]; then
    echo "錯誤：數據庫'$DATABASE'不存在！"
    exit 1
fi

# -------------------------- 3. 查詢表信息並格式化輸出 --------------------------
echo "=== 正在查詢數據庫'$DATABASE'的表信息 ==="
echo "表名                預估數據量(MB)  自增值    存儲引擎"
echo "--------------------------------------------------------"

# 查詢information_schema.TABLES（MySQL系統表，存儲所有表的元數據）
mysql -h"$HOST" -P"$PORT" -u"$USER" -p"$PASSWORD" --database="$DATABASE" -e "
SELECT 
    TABLE_NAME,  # 表名
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS DATA_SIZE_MB,  # 數據+索引大小（MB，保留2位小數）
    AUTO_INCREMENT,  # 自增值（無自增主鍵則為NULL）
    ENGINE  # 存儲引擎
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA = '$DATABASE'  # 過濾指定數據庫
ORDER BY 
    DATA_LENGTH DESC;  # 按數據量降序排列
" 2>/dev/null | awk 'NR>1 {  # NR>1：跳過表頭行
    # 格式化輸出（%-20s：左對齊，佔20字符；確保列對齊）
    printf "%-20s %-16s %-9s %-10s\n", $1, $2, ($3==""?"NULL":$3), $4
}'

echo "--------------------------------------------------------"
echo "=== 數據庫'$DATABASE'表信息查詢完成 ==="

執行與示例

# 1. 執行腳本（指定數據庫為martin）
chmod +x mysql_tables_stats.sh && ./mysql_tables_stats.sh martin

# 2. 預期輸出
=== 正在查詢數據庫'martin'的表信息 ===
表名                預估數據量(MB)  自增值    存儲引擎
--------------------------------------------------------
products            0.02           101      InnoDB
t1                  0.01           NULL     InnoDB
--------------------------------------------------------
=== 數據庫'martin'表信息查詢完成 ===

字段説明：

DATA_SIZE_MB：DATA_LENGTH（表數據大小）+ INDEX_LENGTH（索引大小），單位為MB，是預估數據量（非精確值，精確值需用COUNT(*)）。
AUTO_INCREMENT：自增主鍵的下一個值（若表無自增主鍵，顯示為NULL）。

三、注意事項與優化建議

上述腳本均經過測試可直接使用，但在生產環境中需注意以下優化點，確保安全性與穩定性：

1. 密碼安全：避免明文存儲

腳本中明文寫密碼存在泄露風險，生產環境建議用以下方式替代：

方式1：使用MySQL配置文件
在~/.my.cnf（當前用户）或/etc/my.cnf（全局）中添加：

[client]
user=shell_rw
password=Idyq8_a1
host=localhost

然後修改文件權限（僅當前用户可讀）：

chmod 600 ~/.my.cnf

腳本中可省略-u、-p、-h參數：

mysql martin -e "SELECT * FROM products;"

方式2：使用環境變量
在腳本開頭定義環境變量（或在系統環境變量中配置）：

export MYSQL_PWD="Idyq8_a1"  # MySQL會自動讀取該環境變量作為密碼
mysql -ushell_rw martin -e "SELECT * FROM products;"

2. 錯誤處理：增強腳本健壯性

現有腳本已包含基礎錯誤檢查，可進一步擴展：

添加日誌記錄：將腳本執行結果（成功/失敗）寫入日誌文件，便於後續排查。
重試機制：若MySQL連接失敗，可添加重試邏輯（如重試3次，每次間隔2秒）。
郵件告警：若關鍵操作（如批量查詢失敗），通過mail命令發送告警郵件給運維人員。

3. 性能優化：避免頻繁連接MySQL

若腳本需執行多個SQL語句，建議單次連接執行所有SQL，而非多次連接（減少連接開銷）。例如：

# 優化前：3次連接MySQL
mysql -ushell_rw martin -e "INSERT ...;"
mysql -ushell_rw martin -e "UPDATE ...;"
mysql -ushell_rw martin -e "SELECT ...;"

# 優化後：1次連接執行3條SQL
mysql -ushell_rw martin -e "
INSERT ...;
UPDATE ...;
SELECT ...;
"

四、結語

本文從環境準備到實戰案例，詳細講解了如何用Shell腳本自動化操作MySQL，覆蓋了日常運維中“增刪查改、批量管理、性能監控、數據盤點”等高頻場景。這些腳本不僅能減少重複操作、提高效率，還能作為運維自動化體系的基礎模塊（如結合crontab實現定時執行）。

實際工作中，可根據具體需求擴展腳本功能（如添加參數校驗、日誌分析、告警機制），讓Shell與MySQL的結合更貼合業務場景。希望本文能為你的數據庫運維工作提供實用參考！

本文章為轉載內容，我們尊重原作者對文章享有的著作權。如有內容錯誤或侵權問題，歡迎原作者聯繫我們進行內容更正或刪除文章。

mob64ca13fba42b 博客

mob64ca13fba42b 博客

博客 / 詳情