Linux下MySQL資料庫的備份與恢復
作者：Grey
原文地址：
基於版本
- MySQL5.7
- Deepin Linux 15.11
- xtrabackup-2.4.18
定時備份指令碼
前置工作
準備一個需要備份的資料庫，假設這個資料庫名稱為cargo，示例指令碼如下
CREATE DATABASE IF NOT EXISTS `cargo`;
USE `cargo`;
CREATE TABLE IF NOT EXISTS `b_gen` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `b_gen` (`id`, `name`) VALUES
(1, 'SJA1'),
(2, 'SJA2');
CREATE TABLE IF NOT EXISTS `b_org` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `b_org` (`id`, `name`) VALUES
(1, 'SJA'),
(2, 'ITC');
準備一個用於dump資料庫的使用者，授予以下許可權：
-- 建立使用者
create user db_user@'%' identified by 'db_pass';
-- 授權
grant select,show view,lock tables,trigger on cargo.* to db_user@'%' identified by 'db_pass';
flush privileges;
建立存放指令碼的目錄
mkdir /data/backup/mysql
指令碼目錄結構
- db_bk.sh →主程式
- check_dir.sh→驗證mysql的相關目錄是否存在
- options.conf→全域性變數和相關配置
db_bk.sh
#!/bin/bash
DIRNAME=$0
if [ "${DIRNAME:0:1}" = "/" ];then
CURDIR=`dirname $DIRNAME`
else
CURDIR="`pwd`"/"`dirname $DIRNAME`"
fi
echo $CURDIR
# 定義全域性變數
. $CURDIR/options.conf
# 檢查相關目錄
. $CURDIR/check_dir.sh
DBname=$1
LogFile=${backup_dir}/db.log
DumpFile=${backup_dir}/DB_${DBname}_$(date +%Y%m%d_%H).sql
NewFile=${backup_dir}/DB_${DBname}_$(date +%Y%m%d_%H).tgz
OldFile=${backup_dir}/DB_${DBname}_$(date +%Y%m%d --date="${expired_days} days ago")*.tgz
[ ! -e "${backup_dir}" ] && mkdir -p ${backup_dir}
DB_tmp=$(${db_install_dir}/bin/mysql -u${dbdumpuser} -p${dbdumppwd} -e "show databases\G" | grep ${DBname})
[ -z "${DB_tmp}" ] && {
echo "[${DBname}] not exist" >>${LogFile}
exit 1
}
if [ -n "$(ls ${OldFile} 2>/dev/null)" ]; then
rm -f ${OldFile}
echo "[${OldFile}] Delete Old File Success" >>${LogFile}
else
echo "[${OldFile}] Delete Old Backup File" >>${LogFile}
fi
if [ -e "${NewFile}" ]; then
echo "[${NewFile}] The Backup File is exists, Can't Backup" >>${LogFile}
else
${db_install_dir}/bin/mysqldump -u${dbdumpuser} -p${dbdumppwd} --databases ${DBname} >${DumpFile}
pushd ${backup_dir} >/dev/null
tar czf ${NewFile} ${DumpFile##*/} >>${LogFile} 2>&1
echo "[${NewFile}] Backup success " >>${LogFile}
rm -f ${DumpFile}
popd >/dev/null
fi
並賦予可執行許可權
chmod u+x db_bk.sh
建立備份後的資料庫檔案的存放目錄：
mkdir /data/backup/mysql/backup_files
options.conf
# mysql 的安裝路徑，你可以通過以下SQL檢視
# select @@basedir as basePath from dual ; show variables like '%basedir%';
mysql_install_dir=/usr/local/mysql
# mysql的資料儲存路徑，你可以通過以下SQL檢視
# select @@datadir as dataPath from dual ;show variables Like '%datadir%';
mysql_data_dir=/data/mysql
dbdumpuser=db_user
dbdumppwd=db_pass
# Backup Dest directory, change this if you have someother location
backup_dir=/data/backup/mysql/backup_files
# How many days before the backup directory will be removed
expired_days=5
並賦予可執行許可權
chmod u+x options.conf
check_dir.sh
#!/bin/bash
# check MySQL dir
# [ -d "${mysql_install_dir}/support-files" ] && { db_install_dir=${mysql_install_dir}; db_data_dir=${mysql_data_dir}; }
{
db_install_dir=${mysql_install_dir}
db_data_dir=${mysql_data_dir}
}
並賦予可執行許可權
chmod u+x check_dir.sh
將這個指令碼加到定時任務中：
crontab -e
編輯定時任務檔案，增加以下一行，cron表示式意思為：每小時執行一次：
*/60 * * * * /bin/bash /data/backup/mysql/db_bk.sh cargo
定時清理指令碼
在/data/backup/mysql/backup_files目錄下建立：deleteLegacy.sh
#!/bin/bash
for file in `find /data/backup/mysql/backup_files/ -type f -name "*"`
do
let expired_time=$[1*24*60*60] #此處定義檔案的過期時間1天
let currentDate=`date +%s` #獲取系統時間，所以時間格式為秒
let modifyDate=$(stat -c %Y $file) #獲取檔案修改時間
let existTime=$[$currentDate-$modifyDate] #對比時間，算出日誌存在時間
if [ $existTime -gt $expired_time ];
then
rm -rf $file #刪除檔案
fi
done
並賦予可執行許可權
chmod u+x deleteLegacy.sh
加入定時任務
crontab -e
編輯定時任務檔案，增加以下一行，cron表示式意思為：每天凌晨1點執行一次：
00 01 * * * /bin/sh /data/backup/mysql/backup_files/deleteLegacy.sh
定時同步指令碼
定時同步cargo資料庫到一個新的資料庫（需要提前先建好這個資料庫，假設名字為：cargo_backup）
CREATE DATABASE IF NOT EXISTS `cargo_backup`;
將之前新建的db_user使用者，賦予cargo_backup的所有許可權，同時需要設定db_user的Global privileges的SUPER許可權（否則匯入檢視的時候會有問題
grant all privileges on cargo_backup.* to db_user@'%' identified by 'db_pass';
grant SUPER on *.* to db_user@'%';
flush privileges;
建立同步SQL目錄
mkdir /data/backup/mysql/mysqlsync
指令碼參考
mysql_sync.sh
#!/bin/bash
# MySQL資料庫
# 建立一個同步專用使用者(賦予select,show view, trrigger, lock tables許可權)
# 對於備份的目標資料庫有所有許可權
# 要設定Global privileges的SUPER許可權
DB_USER="db_user"
DB_PASS="db_pass"
DB_HOST="localhost"
# 需要備份的資料庫名稱
DB_FROM="cargo"
DB_TO="cargo_backup"
BIN_DIR="/usr/local/mysql/bin"
SYNC_DIR="/data/backup/mysql/mysqlsync"
$BIN_DIR/mysqldump -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_FROM > $SYNC_DIR/sync.sql
$BIN_DIR/mysql -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_TO < $SYNC_DIR/sync.sql
賦予可執行許可權
chmod u+x mysql_sync.sh
加入定時任務
crontab -e
編輯定時任務檔案，增加以下一行，cron表示式意思為：每天凌晨1點執行一次：
00 01 * * * /bin/bash /data/backup/mysql/backup_files/mysql_sync.sh
採用mysqlpump備份資料庫操作
mysqlpump主要用於備份整個sql執行過程，針對誤操作的情況，我們可以拿mysqlpump中的操作記錄，逐個執行，一直到操作有誤的那個操作停止，這樣就可以撤銷上一次有誤的操作，這種方式的缺點是恢復的時間比較長，優點是可以控制到每一次執行的操作記錄
ln -s /usr/local/mysql/bin/mysqlpump /usr/bin
grant all privileges on *.* to mysqlpump@'%' identified by 'mysqlpump';
-- grant reload,lock tables,replication client,create tablespace,process,super on *.* to mysqlpump@'%' identified by 'mysqlpump';
FLUSH PRIVILEGES;
備份，假設要備份的資料庫為t，執行
mysqlpump -umysqlpump -pmysqlpump --databases t >t.sql
檢視備份後的檔案：
t.sql
-- Dump created by MySQL pump utility, version: 5.7.29, Linux (x86_64)
-- Dump start time: Tue Feb 25 19:44:18 2020
-- Server version: 5.7.29
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `t` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
CREATE TABLE `t`.`tt` (
`t` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
;
CREATE TABLE `t`.`x` (
`Column 1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
;
INSERT INTO `t`.`tt` VALUES (2),(2),(2);
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Tue Feb 25 19:44:18 2020
採用Binlog恢復資料庫
可以檢視整個資料庫的操作記錄，對於誤操作的記錄，可以先還原為最近的一次備份，然後將mysqlpump後的sql逐句執行，一直到執行錯誤的那條語句（忽略之）
控制檯用root登入
mysql -uroot -p
show variables like 'binlog_format'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
binlog的配置在/etc/my.cnf中配置, 若要配置，需要先停資料庫服務
service mysql stop
然後在my.cnf配置這兩個引數
log_bin = mysql-bin
binlog_format = mixed
重啟資料庫
service mysql start
接下來我們模擬幾次操作,我在demo資料庫下建了tsdtas這個表
-- 正常操作
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
-- 錯誤操作
DELETE FROM `demo`.`tsdtas`;
-- 正常操作
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
控制檯用root登入
mysql -uroot -p
執行
flush logs;
在mysql的資料目錄下（一般為：/data/mysql) 找到最新的binlog(格式為：mysql-bin.00000x)， 在mysql控制檯中
執行：
show binlog events in"mysql-bin.000008";
即可檢視所有操作記錄
MySQL [(none)]> show binlog events in"mysql-bin.000008";
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000008 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.29-log, Binlog ver: 4 |
| mysql-bin.000008 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000008 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 219 | Query | 1 | 298 | BEGIN |
| mysql-bin.000008 | 298 | Query | 1 | 429 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 429 | Xid | 1 | 460 | COMMIT /* xid=282 */ |
| mysql-bin.000008 | 460 | Anonymous_Gtid | 1 | 525 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 525 | Query | 1 | 604 | BEGIN |
| mysql-bin.000008 | 604 | Query | 1 | 735 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 735 | Xid | 1 | 766 | COMMIT /* xid=284 */ |
| mysql-bin.000008 | 766 | Anonymous_Gtid | 1 | 831 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 831 | Query | 1 | 910 | BEGIN |
| mysql-bin.000008 | 910 | Query | 1 | 1041 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 1041 | Xid | 1 | 1072 | COMMIT /* xid=286 */ |
| mysql-bin.000008 | 1072 | Anonymous_Gtid | 1 | 1137 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 1137 | Query | 1 | 1216 | BEGIN |
| mysql-bin.000008 | 1216 | Query | 1 | 1317 | use `demo`; DELETE FROM `demo`.`tsdtas` |
| mysql-bin.000008 | 1317 | Xid | 1 | 1348 | COMMIT /* xid=288 */ |
| mysql-bin.000008 | 1348 | Anonymous_Gtid | 1 | 1413 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 1413 | Query | 1 | 1492 | BEGIN |
| mysql-bin.000008 | 1492 | Query | 1 | 1623 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 1623 | Xid | 1 | 1654 | COMMIT /* xid=290 */ |
| mysql-bin.000008 | 1654 | Anonymous_Gtid | 1 | 1719 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 1719 | Query | 1 | 1798 | BEGIN |
| mysql-bin.000008 | 1798 | Query | 1 | 1929 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 1929 | Xid | 1 | 1960 | COMMIT /* xid=292 */ |
| mysql-bin.000008 | 1960 | Rotate | 1 | 2007 | mysql-bin.000009;pos=4 |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
在這個操作記錄中，把DELETE語句重新刪掉，其他語句重新執行即可恢復。
採用xtrabackup備份和恢復資料庫
Important: With the introduction of Percona XtraBackup 8.0, Percona XtraBackup 2.4 will continue to support MySQL and Percona Server 5.6 and 5.7 databases. Due to the new MySQL redo log and data dictionary formats the Percona XtraBackup 8.0.x versions will only be compatible with MySQL 8.0.x and the upcoming Percona Server for MySQL 8.0.x
安裝Xtrabackup
tar zxvf percona-xtrabackup-2.4.18-Linux-x86_64.libgcrypt20.tar.gz
cd percona-xtrabackup-2.4.18-Linux-x86_64/bin
cp xtrabackup /usr/bin/xtrabackup
cp innobackupex /usr/bin/innobackupex
建立使用者並授權
# 使用者授權（注：可以限制訪問ip，如本機訪問，可以將%改成localhost）
create user xtrabackup@'%' identified by 'xtrabackup';
grant reload,lock tables,replication client,create tablespace,process,super on *.* to xtrabackup@'%' identified by 'xtrabackup';
# 這裡可以指定具體資料庫的增刪改的許可權
grant all privileges on *.* to xtrabackup@'%' identified by 'xtrabackup' ;
FLUSH PRIVILEGES;
建立備份目錄
mkdir /data/xtrabackup/
全量備份資料庫
# 檢視my.cnf位置：mysql --help | grep 'Default options' -A 1
innobackupex --defaults-file=/etc/my.cnf --user=xtrabackup --password=xtrabackup --socket=/tmp/mysql.sock /data/xtrabackup/
執行完畢後，在/data/xtrabackup下會有一個以時間戳命名的資料夾：類似：2020-02-24_14-42-16
刪除資料庫
# 停止資料庫服務
service mysql stop
# 刪除mysql的data目錄：select @@datadir as dataPath from dual ;
# 假設data目錄為：/data/mysql
cd /data
mv mysql/ mysql_bak/
mkdir mysql
利用 --apply-log來回滾未提交的事務及同步已經提交的事務至資料檔案，使資料檔案處於一致性狀態。
innobackupex --apply-log /data/xtrabackup/2020-02-24_14-42-16/
恢復
innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/xtrabackup/2020-02-24_14-42-16/
配置mysql使用者的資料目錄許可權
chmod -R mysql.mysql /data
啟動資料庫
service mysql start
增量備份
我們基於之前的全量備份，做增量備份操作。
首先，模擬增量資料，示例SQL
INSERT INTO `tt` (`t`) VALUES
(5434),
(2),
(3);
innobackupex --defaults-file=/etc/my.cnf --user=xtrabackup --password=xtrabackup --socket=/tmp/mysql.sock --incremental-basedir=/data/xtrabackup/2020-02-24_14-42-16 --incremental /data/xtrabackup/
此時，/data/xtrabackup下會生成一個時間戳資料夾，例如：2020-02-25_14-55-31
這個資料夾是2020-02-24_14-42-16這個備份的增量備份
刪除資料庫
# 停止資料庫服務
service mysql stop
# 刪除mysql的data目錄：select @@datadir as dataPath from dual ;
# 假設data目錄為：/data/mysql
cd /data
mv mysql/ mysql_bak/
mkdir mysql
增量恢復
innobackupex --apply-log --redo-only /data/xtrabackup/2020-02-24_14-42-16
innobackupex --apply-log --redo-only /data/xtrabackup/2020-02-24_14-42-16 --incremental-dir=/data/xtrabackup/2020-02-25_14-55-31
innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/xtrabackup/2020-02-24_14-42-16/
配置mysql使用者的資料目錄許可權
chown -R mysql.mysql /data
啟動資料庫
service mysql start