Linux下MySQL数据库的备份与恢复
作者：
原⽂地址：
概览
备份⽅式说明备注
mysqldump默认情况下仅备份：表，视图和触发器默认不备份存储过程和事件，需要加配置参数，如果需要备份存储过程和事件，需要增加-ER参数，且dump
⽤户需要增加show events的Global Privilege
binlog有时间期限，可以配置，⼀般保留5天记录即可
mysqlpump默认备份表，视图，存储过程，存储函数，触发
MySQL 5.7及以上版本才⽀持
器，事件，
xtrabackup需要重启数据库，恢复时需要清空数据库的数据⽬
只⽀持Linux
录，可以⽀持增量备份
基于版本
MySQL5.7
Deepin Linux 15.11
xtrabackup-2.4.18
定时备份脚本（利⽤MySQL dump）
前置⼯作
准备⼀个需要备份的数据库，假设这个数据库名称为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的相关⽬录是否存在
db_bk.sh
轮播图代码详解#!/bin/bash
DIRNAME=$0
if [ "${DIRNAME:0:1}" = "/" ];then
CURDIR=`dirname $DIRNAME`
else
CURDIR="`pwd`"/"`dirname $DIRNAME`"
fi
echo $CURDIR
# 定义全局变量
. $f
# 检查相关⽬录
. $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
# 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+feclipse离线安装包
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中的操作记录，逐个执⾏，⼀直到操作有误的那个操作停⽌，这样就可以撤销上⼀次有误的操作，这种⽅式的缺点是恢复的时间⽐较长，优点是可以控制到每⼀次执⾏的操作记录
关于mysqldump和mysqlpump的区别，可以参考MySQL官⽹对两个⼯具的说明：
注：mysqlpump uses MySQL features introduced in MySQL 5.7, and thus assumes use with MySQL 5.7 or higher.
即：MySQL 5.7及以上版本才能使⽤MySQL pump
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 |
mysql查看所有存储过程+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
binlog的配置在/etc/myf中配置, 若要配置，需要先停数据库服务
service mysql stop
然后在myf配置这两个参数
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 | |
xcopy使用详解| 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.
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/
全量备份数据库
# 查看myf位置：mysql --help | grep 'Default options' -A 1
innobackupex --defaults-file=/etc/myf --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/myf --copy-back --rsync /data/xtrabackup/2020-02-24_14-42-16/
配置mysql⽤户的数据⽬录权限
chmod -sql /data
启动数据库
service mysql start
增量备份
我们基于之前的全量备份，做增量备份操作。
⾸先，模拟增量数据，⽰例SQL
INSERT INTO `tt` (`t`) VALUES
(5434),
(2),
(3);
innobackupex --defaults-file=/etc/myf --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/myf --copy-back --rsync /data/xtrabackup/2020-02-24_14-42-16/
配置mysql⽤户的数据⽬录权限
chown -sql /data
启动数据库
service mysql start
使⽤xtrabackup备份mysql8相关命令
注：来⾃说法：
Due to changes in MySQL 8.0.20 released by Oracle at the end of April 2020, Percona XtraBackup 8.0, up to version 8.0.11, is not compatible with MySQL version 8.0.20 or higher, or Percona
products that are based on it: Percona Server for MySQL and Percona XtraDB Cluster.
简⾔之，就是xtrabackup 8 只⽀持到 MySQL 8.0.20以及以下的版本，对于更⾼版本，不⽀持！
mkdir -p /data/backup/mysql/20210520/fulldb
/usr/local/xtrabackup/bin/xtrabackup --defaults-file=/etc/myf --user=young --password=0320 --port=3306 --backup --target-dir=/data/backup/mysql/20210520/fulldb
mkdir -p /data/backup/mysql/20210520/incrdb
/usr/local/xtrabackup/bin/xtrabackup --defaults-file=/etc/myf --user=young --password=0320 --port=3306 --backup --target-dir=/data/backup/mysql/20210520/incrdb --incremental-basedir=/data/backup/mysql/20210520/full
service mysqld stop
mv /data/mysql /data/mysql_bak/
/usr/local/xtrabackup/bin/xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/20210520/fulldb
/usr/local/xtrabackup/bin/xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/20210520/fulldb --incremental-dir=/data/backup/mysql/20210520/incrdb
/usr/local/xtrabackup/bin/xtrabackup --prepare --target-dir=/data/backup/mysql/20210520/fulldb
/usr/local/xtrabackup/bin/xtrabackup -user=young --password=0320 --port=3306 --datadir=/data/mysql --copy-back --target-dir=/data/backup/mysql/20210520/fulldb
chown -sql /data/mysql/
chmod -R 755 /data/mysql/
service mysqld restart
参考资料
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论