mysqldump备份一般方式详解

本文主要给大家介绍mysqldump备份一般方式详解，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续在亿速云行业资讯里面关注我的更新文章的。

 一、mysqldump备份结合binlog日志恢复

MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

1.binlog日志恢复介绍

·首先要开启binary log功能

通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如下：

log-bin [=DIR/[filename]]  （配置文件中只写log_bin不写后面的文件名和路径时，默认存放在/usr/local/mysql/data目录下，文件名为主机名-bin.000001…命名）

·下面用一个例子来说明binlog日志恢复数据的方法

先对测试数据库的表进行一下增删改的操作，否则log里边数据有点空。

重新开始一个新的日志文件，并删除测试表中的一行数据，模拟错误操作。

查看MySQL Server上的二进制日志

查看二进制日志中的事件，默认显示可找到的第一个二进制日志文件中的事件，包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容。

语法格式：SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

选项解析：

IN 'log_name'      指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos               指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
offset           偏移量(不指定就是0),例如为3就会从第3行开始
row_count          查询总条数(不指定就是所有行)

显示信息中的参数介绍：

Format_desc  此事件为格式描述事件
Query  为查询事件
BEGIN  为事务开始
Table_map  为表映射事件
Write_rows    为我们执行的insert事件
XidXid    时间是自动提交事务的动作
Rotate    为日志轮换事件，是我们执行flush logs开启新日志文件引起的。
COMMIT    为事务的提交

查看指定的二进制日志中的事件

为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v（--verbose）选项，该选项会将行事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容。

执行以下命令，我们可以看到上图中显示的信息：

mysqlbinlog  -v  /usr/local/mysql/data/mysql-bin.000002

另外mysqlbinlog和可以通过--read-from-remote-server选项从远程云服务器读取二进制日志文件，这时需要一些而外的连接参数，如-h，-P，-p，-u等，这些参数仅在指定了--read-from-remote-server后有效。

从上图中可以看出delete事件发生position是291，事件结束position是420

恢复流程：直接用bin-log日志将数据库恢复到删除位置291前,然后跳过故障点,再进行恢复。下面所有的操作,命令如下：

常见的选项有以下几个：

--start-datetime
从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。
--stop-datetime
从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。
--start-position        
从二进制日志中读取指定position 事件位置作为开始。
--stop-position
从二进制日志中读取指定position 事件位置作为事件截至。

删除test数据库，利用binlog恢复数据，完成后发现数据都恢复过来了。

注意：

（1）在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。

（2）以上方法中仅仅利用了binlog来恢复数据，并没有使用到mysqldump全库备份来恢复数据库，所以在恢复数据前要删除数据库。

2.利用脚本实现mysql的备份与恢复

（1）mysqldump介绍

mysqldump是mysql用于备份和数据转移的一个工具。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建你的数据库所需要的 SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。

mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。

mysqldump一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了。

·数据库的导出

导出对象说明：

mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作

# mysqldump [options] db_name [tbl_name ...]   //导出指定数据库或单个表
# mysqldump [options] --databases db_name ...   //导出多个数据库
#mysqldump [options] --all-databases           //导出所有

·数据库的导入

mysql  -uroot  -p  [options]  [db_name]  [tbl_name]  <  /路径/备份文件

生产环境中Mysql数据库的备份是周期性重复的操作，所以通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本。

mysqldump备份方案：

·周日凌晨1点全库备份

·周一到周六凌晨每隔4个小时增量备份一次

设置crontab任务，每天执行备份脚本

# crontab –e

#每个星期日凌晨1:00执行完全备份脚本
0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1
#周一到周六每隔4个小时增量备份一次
0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh脚本内容：

#!/bin/sh
#name:mysqlfullbackup.sh
mysqldir=/usr/local/mysql
user=root
userpwd=123456
dbname=test_db
#定义备份目录
databackupdir=/opt/mysqlbackup
[ ! -d $databackupdir ] && mkdir $databackdir
#定义邮件正文文件
emailfile=$databackupdir/email.txt
#定义邮件地址
email=root@localhost.localdomain
#定义备份日志文件
logfile=$databackupdir/mysqlbackup.log
DATE=`data -I`
 
echo "" > $emailfile
echo $(date +"%y-%m-%d%H:%M:%S") >> $emailfile
cd $databackupdir
#定义备份文件名
dumpfile=mysql_$DATE.sql
gzdumpfile=mysql_$DATE.sql.tar.gz
#使用mysqldump备份数据库
$mysqldir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile
#压缩备份文件
if [ $? -eq 0 ]; then
tar zcf $gzdumpfile $dumpfile >> $emailfile 2>&1
echo "backupfilename:$gzdumpfile" >> $emailfile
echo "database backup success!" >> $emailfile
rm -f $dumpfile
else
echo "database backup fail!" >> $emailfile
fi
#写日志文件
echo "------------------------------------------" >> $logfile
cat $emailfile >> $logfile
#发送邮件通知
cat $emailfile | mail -s "mysql backup" $email
 
mysqldailybackup.sh脚本内容：
#!/bin/sh
# Name:mysqlDailyBackup.sh
# 定义数据库目录和数据目录
mysqldir=/usr/local/mysql
datadir=$mysqldir/data
# 定义用于备份数据库的用户名和密码
user=root
userpwd=123456
# 定义备份目录，每日备份文件备份到$dataBackupDir/daily
databackupdir=/opt/mysqlbackup
dailybackupdir=$databackupdir/daily
[ ! -d $dailybackupdir ] && mkdir -p $dailybackupdir/daily
# 定义邮件正文文件
emailfile=$databackupdir/email.txt
# 定义邮件地址
email=root@localhost.localdomain
# 定义日志文件
logfile=$databackupdir/mysqlbackup.log
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
# 刷新日志，使数据库使用新的二进制日志文件
$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
cd $datadir
# 得到二进制日志列表
filelist=`cat mysql-bin.index`
icounter=0
for file in $filelist
do
icounter=`expr $icounter+1`
done
nextnum=0
ifile=0
for file in $filelist
do
binlogname=`basename $file`
nextnum=`expr $nextnum+1`
if [ $nextnum -eq $icounter ];then
echo "Skip laster!" > /dev/null
else
dest=$dailybackupdir/$binlogname
if [ -e $dest ];then
echo "Skip exist $binlogname" > /dev/null
else
cp $binlogname $dailybackupdir
if [$? -eq 0 ];then
ifile=`expr $ifile+1`
echo "$binlogname backup success!" >> $emailfile
fi
fi
fi
done
if [ $ifile -eq 0 ];then
echo "no binlog backup!" >> $emailfile
else
echo "backup $ifile files" >> $emailfile
fi
# 发送邮件通知
cat $emailfile | mail -s "MySQL Backup" $email
# 写日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile

看了以上关于mysqldump备份一般方式详解，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。