MySQL mysqlhotcopy 使用詳解：安裝、備份、還原、增量備份與對比

mysqlhotcopy 是 Perl 腳本，利用 LOCK TABLES + FLUSH TABLES + cp/scp 實現 MyISAM 表的極速物理熱備份。本文涵蓋依賴安裝、參數詳解、正則過濾、binlog 位點記錄、增量備份腳本、與 mysqldump 深度對比。

限制前提

• 僅支援 MyISAM 引擎 (InnoDB 不支援)
• 需 Perl 環境：perl-DBI, perl-DBD-MySQL
• 必須在資料庫伺服器本機執行
• MySQL 5.7+ 已棄用，8.0 移除，建議遷移至 xtrabackup 或 mysqldump --single-transaction

依賴安裝

# CentOS/RHEL
yum -y install perl perl-DBI perl-DBD-MySQL

# Ubuntu/Debian
apt-get install libdbi-perl libdbd-mysql-perl

# 手動編譯 DBD::mysql (舊版)
wget https://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.050.tar.gz
tar xzf DBD-mysql-4.050.tar.gz
cd DBD-mysql-4.050
perl Makefile.PL --mysql_config=/usr/local/mysql/bin/mysql_config
make && make test && make install

常用參數速查表

備份實戰範例

# 單庫備份
mkdir -p /backup/mysql
/usr/local/mysql/bin/mysqlhotcopy --user=root --password=Pass123 --flushlog dbname /backup/mysql

# 多庫備份
/usr/local/mysql/bin/mysqlhotcopy -u root -p Pass123 --flushlog db1 db2 db3 /backup/mysql

# 正則匹配 (以 ttlsa 開頭)
/usr/local/mysql/bin/mysqlhotcopy -u root -p Pass123 --flushlog --regexp='^ttlsa' /backup/mysql

# 正則匹配 (a-f 開頭)
/usr/local/mysql/bin/mysqlhotcopy -u root -p Pass123 --flushlog --regexp='^[a-f]' /backup/mysql

# 備份指定表 (ttlsa_com 庫中 user_ 開頭)
/usr/local/mysql/bin/mysqlhotcopy -u root -p Pass123 --flushlog ttlsa_com./^user_/ /backup/mysql

# 排除表 (排除 user_log 開頭)
/usr/local/mysql/bin/mysqlhotcopy -u root -p Pass123 --flushlog ttlsa_com./~^user_log/ /backup/mysql

記錄 Binlog 位點：增量備份基石

1. 建立位點記錄表

CREATE TABLE `mysqlhotcopy_log_pos` (
    `host` VARCHAR(60) NOT NULL PRIMARY KEY,
    `time_stamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `log_file` VARCHAR(32),
    `log_pos` INT(11),
    `master_host` VARCHAR(60),
    `master_log_file` VARCHAR(32),
    `master_log_pos` INT(11),
    `relay_log_file` VARCHAR(32),
    `relay_log_pos` INT(11)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. 專用備份用戶權限

GRANT SELECT, RELOAD, LOCK TABLES ON *.* TO 'mysqlbackup'@'localhost' IDENTIFIED BY 'Pass123';
GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.mysqlhotcopy_log_pos TO 'mysqlbackup'@'localhost' IDENTIFIED BY 'Pass123';
FLUSH PRIVILEGES;

3. 執行備份並記錄位點

/usr/local/mysql/bin/mysqlhotcopy \
  -u mysqlbackup -p Pass123 \
  --flushlog \
  --record_log_pos=mysql.mysqlhotcopy_log_pos \
  --keepold \
  --regexp='[a-zA-Z0-9_-]' \
  /backup/mysql/mysqlhotcopy_$(date +%Y%m%d_%H%M%S)

增量備份還原流程

1. 還原最近一次全量 mysqlhotcopy 備份 (複製目錄到 datadir、修權限、啟動)
2. 查詢位點表獲取 master_log_file, master_log_pos
3. 重放 binlog：mysqlbinlog --start-position=POS LOG_FILE | mysql -u root -p

自動化備份腳本 (含從庫狀態檢查)

#!/bin/bash
mysqlhotcopy="/usr/local/mysql/bin/mysqlhotcopy"
user="mysqlbackup"
password="Pass123"
socket="/tmp/mysql.sock"
backupdir="/backup/mysql"
datadir="mysqlhotcopy_$(date +%Y%m%d_%H%M%S)"
target="$backupdir/$datadir"
retention=5

# 檢查從庫狀態 (僅主從架構需求)
status=($(mysql -u$user -p$password -S $socket -e "SHOW SLAVE STATUS\G" --skip-column-names \
  | egrep "Slave_IO_Running|Slave_SQL_Running" | awk '{print $2}'))

if [[ "${status[0]}" == "Yes" && "${status[1]}" == "Yes" ]]; then
    mkdir -p "$target"
    $mysqlhotcopy -u $user -p $password -S $socket \
      --record_log_pos=mysql.mysqlhotcopy_log_pos \
      --keepold --flushlog \
      --regexp="[a-zA-Z0-9_-]" \
      "$target"
    find "$backupdir" -name "mysqlhotcopy_*" -type d -mtime +$retention | xargs rm -rf
else
    echo "$(date) Slave error, backup skipped" >> /var/log/mysqlhotcopy.log
fi

mysqlhotcopy vs mysqldump 深度對比

遷移建議

MySQL 5.7+ 已棄用，8.0 移除。MyISAM 庫建議：

1. 轉換至 InnoDB：ALTER TABLE tbl ENGINE=InnoDB;
2. 改用 xtrabackup (Percona) 實現 InnoDB 物理熱備
3. 或標準化 mysqldump --single-transaction 流程

mysqlhotcopy 是 MyISAM 時代的極速利器，但隨引擎演進已完成歷史使命。理解其原理有助於掌握物理備份核心邏輯：鎖定 → 同步 → 複製 → 記錄位點 → 釋放。