mysql資料庫-備份與還原實操

AlexG發表於2021-05-29
MySql資料庫

目錄

備份工具

  1. cp, tar等複製歸檔工具：物理備份工具，適用所有儲存引擎；只支援冷備；完全和部分備份
  2. LVM的快照：先加讀鎖，做快照後解鎖，幾乎熱備；藉助檔案系統工具進行備份
  3. mysqldump：邏輯備份工具，適用所有儲存引擎，對MyISAM儲存引擎進行溫備；支援完全或部分備份；對InnoDB儲存引擎支援熱備，結合binlog的增量備份
  4. xtrabackup：由Percona提供支援對InnoDB做熱備(物理備份)的工具，支援完全備份、增量備份
  5. MariaDB Backup： 從MariaDB 10.1.26開始整合，基於Percona XtraBackup 2.3.8實現
  6. mysqlbackup：熱備份， MySQL Enterprise Edition元件
  7. mysqlhotcopy：PERL 語言實現，幾乎冷備，僅適用於MyISAM儲存引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp來快速備份資料庫

1 基於 LVM 的快照備份(幾乎熱備)

# 1. 鎖定所有表
mysql> FLUSH TABLES WITH READ LOCK;
# 2. 記錄二進位制日誌檔案及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/FILE
# 3. 建立快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
# 4. 釋放鎖
mysql> UNLOCK TABLES;
# 5. 掛載快照卷，執行資料備份
# 6. 備份完成後，刪除快照卷
# 7. 制定好策略，通過原卷備份二進位制日誌

2 資料庫冷備份和還原

# 1. 關停要備份的DB
 systemctl stop mariadb
# 2. 複製相關資料檔案（配置檔案、資料檔案、binlog）到遠端主機
 scp /etc/my.cnf.d/mariadb-server.cnf ${Restore_Machine}:/etc/my.cnf.d/
 rsync -avz /var/lib/mysql/ ${Restore_Machine}:/var/lib/mysql/  #保留屬性複製資料檔案
 scp -r /data/logbin/ ${Restore_Machine}:/data/  #${Restore_Machine}事先存在/data/目錄
# 3. 在目標主機（${Restore_Machine}）上修改檔案屬主資訊
 chown -R mysql.mysql /var/lib/mysql/
 chown -R mysql.mysql /data/logbin/
# 4. 啟動還原完資料的DB
 systemctl start mariadb

3 mysqldump備份工具

mysqldump是MySQL的客戶端命令，通過mysql協議連線至mysql伺服器進行備份
Schema和資料儲存在一起、巨大的SQL語句、單個巨大的備份檔案
官方mysqldump介紹頁面 https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

3.1 實戰備份策略

3.1.1 全備份

  • InnoDB建議備份策略
# 直接全量備份
mysqldump –uroot -p –A –F –E –R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

# 全量分庫備份，不備份MySQL自帶的資訊資料庫
mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -B $db | gzip > /data/$db.sql.gz;done

# for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /data/$db.sql.gz;done

# mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$' | sed -rn 's#(.*)#mysqldump -B \1 | gzip > /data/\1.sql.gz#p' |bash

# mysql -uroot -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 | gzip > /data/\1.sql.gz#p' |bash
  • MyISAM建議備份策略
mysqldump –uroot -p –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql

3.1.2 分庫分表備份

# cat backup_hellodb.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=123456
[ -d "$DIR" ] || mkdir $DIR

mysqldump -uroot -p "$PASS" -F –E –R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip >  ${DIR}/${DB}_${TIME}.sql.gz

3.2 mysqldump 備份常見選項

-A, --all-databases #備份所有資料庫，含create database
-B, --databases db_name…  #指定備份的資料庫，包括create database語句
-E, --events：#備份相關的所有event scheduler
-R, --routines：#備份所有儲存過程和自定義函式
--triggers：#備份表相關觸發器，預設啟用,用--skip-triggers，不備份觸發器
--default-character-set=utf8 #指定字符集
--master-data[=#]： #此選項須啟用二進位制日誌
#1：所備份的資料之前加一條記錄為CHANGE MASTER TO語句，非註釋，不指定#，預設為1，適合於主從複製多機使用
#2：記錄為被註釋的#CHANGE MASTER TO語句，適合於單機使用
#此選項會自動關閉--lock-tables功能，自動開啟-x | --lock-all-tables功能（除非開啟--single-transaction）
-F, --flush-logs #備份前滾動日誌，鎖定表完成後，執行flush logs命令,生成新的二進位制日誌檔案，配合-A 或 -B 選項時，會導致重新整理多次資料庫。建議在同一時刻執行轉儲和日誌重新整理，可通過和--single-transaction或-x，--master-data 一起使用實現，此時只重新整理一次二進位制日誌
--compact #去掉註釋，適合除錯，生產不使用
-d, --no-data #只備份表結構,不備份資料
-t, --no-create-info #只備份資料,不備份表結構,即create table
-n,--no-create-db #不備份create database，可被-A或-B覆蓋
--flush-privileges #備份mysql或相關時需要使用
-f, --force    #忽略SQL錯誤，繼續執行
--hex-blob     #使用十六進位制符號轉儲二進位制列，當有包括BINARY， VARBINARY，BLOB，BIT的資料型別的列時使用，避免亂碼
-q, --quick   #不快取查詢，直接輸出，加快備份速度

3.3 mysqldump的MyISAM儲存引擎相關的備份選項

MyISAM不支援事務，只能支援溫備；不支援熱備，所以必須先鎖定要備份的庫，而後啟動備份操作

-x,--lock-all-tables #加全域性讀鎖，鎖定所有庫的所有表，同時加--single-transaction或--lock-tables選項會關閉此選項功能，注意：資料量大時，可能會導致長時間無法併發訪問資料庫
-l,--lock-tables #對於需要備份的每個資料庫，在啟動備份之前分別鎖定其所有表，預設為on,--skip-lock-tables選項可禁用,對備份MyISAM的多個庫,可能會造成資料不一致
#注：以上選項對InnoDB表一樣生效，實現溫備，但不推薦使用

3.4 mysqldump的InnoDB儲存引擎相關的備份選項

InnoDB 儲存引擎支援事務,可以利用事務的相應的隔離級別,實現熱備，也可以實現溫備但不建議用

--single-transaction
#此選項Innodb中推薦使用，不適用MyISAM，此選項會開始備份前，先執行START TRANSACTION指令開啟事務
#此選項通過在單個事務中轉儲所有表來建立一致的快照。 僅適用於儲存在支援多版本控制的儲存引擎中的表（目前只有InnoDB可以）; 轉儲不保證與其他儲存引擎保持一致。 在進行單事務轉儲時，要確保有效的轉儲檔案（正確的表內容和二進位制日誌位置），沒有其他連線應該使用以下語句：ALTER TABLE，DROPTABLE，RENAME TABLE，TRUNCATE TABLE,此選項和--lock-tables（此選項隱含提交掛起的事務）選項是相互排斥,備份大型表時，建議將--single-transaction選項和--quick結合一起使用

4 mysql完全備份和還原

4.1 常規備份方法

# 1. 開啟源DB二進位制日誌
vim /etc/mariadb-server.cnf
[mysqld]
log-bin
# log-bin=/data/mysql/mysql-bin

# 2. 備份源DB
mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz

# 3. 還原目標DB準備
gzip -d all-2019-11-27.sql.gz

# 4. 開始目標DB的還原
mysql> set sql_log_bin=off;
mysql> source /backup/all-2000-01-01.sql
mysql> set sql_log_bin=on;

4.2 利用二進位制日誌，實現秒精度資料庫資料恢復

4.3 xtrabackup備份工具使用

相關文章