mysqldump全量/增量进行备份/恢复

最新推荐文章于 2024-06-14 07:30:38 发布

勤天

最新推荐文章于 2024-06-14 07:30:38 发布

阅读量8.3k

点赞数 5

分类专栏： MySQL 文章标签： MySQL 备份、恢复增量备份

本文链接：https://blog.csdn.net/demored/article/details/123522132

版权

MySQL 专栏收录该内容

41 篇文章 6 订阅

订阅专栏

4、使用mysqlbinlog 恢复中错误处理

一、mysql备份说明

mysql的备份可以分为冷备份和热备份两种。

冷备份：停止数据库服务进行备份

热备份：不停止数据库服务进行备份

1、myisam备份介绍

mysql的存储引擎为MyIsam时，只支持冷备份，可以直接复制mysql的data目录下的数据库文件。这种方式需要注意mysql版本兼容性问题，同时，为了保证一致性，必须停机或者锁表进行备份。

在恢复时，首先关闭mysql服务，将备份的数据库文件复制到mysql的data目录下，然后启动mysql服务。

2、innodb备份介绍

mysql的存储引擎为InnoDB时，支持热备份，因为InnoDB引擎是事务性存储引擎，可以根据日志来进行redo和undo，即将备份的时候没有提交的事务进行回滚，已经提交了的事务进行重做。

mysql提供了mysqldump命令用于存储引擎为InnoDB时的备份。

说明：使用mysqldump备份，是在mysql终端外，不用输入账号密码进入到终端中。

3、全量备份和增量备份

1、全量备份：可以使用mysqldump直接备份整个库或者是备份其中某一个库或者一个库中的某个表。

2、增量备份：增量备份是针对于数据库的bin-log日志进行备份的，需要开始数据库的bin-log日志。增量备份是在全量的基础上进行操作的。增量备份主要是靠mysql记录的bin-log日志。（可以把二进制日志保存成每天的一个文件）

4、一些备份工具

1）mysql官方提供了mysqldump （简单，备份时间长，如果是简单的备份 / 恢复，使用它就足够了）

2）xtrabackup

3）mydumper

二、mysqldump全量备份与恢复

基本格式：

#1.登录选项：
 　　-u user：指定用户
    -h host：指定主机
    -p：表示要使用密码

#2.备份选项：
    --all-databases：备份所有数据库
    --databases db1 db2：备份指定的数据库
    --single-transaction：对事务引擎执行热备
    --flush-logs：更新二进制日志文件
    --master-data=2
        1：每备份一个库就生成一个新的二进制文件(默认)
        2：只生成一个新的二进制文件
    --quick：在备份大表时指定该选项

1、mysqldump全量备份

备份需要进入到mysql的安装目录中去，默认备份是同时备份表结构和数据；使用-p可以密码直接写在后面，不然不填密码的话，回车还需要输入密码

1）备份指定数据库。（全量备份mysql某个数据库）

mysqldump -uroot -proot --databases testdb --single-transaction --flush-logs --master-data=2 > databases.sql

2）备份指定数据库中的指定表。

mysqldump --databases testdb.demo --single-transaction --flush-logs --master-data=2 > demo.sql

3）令备份多个数据库。

mysqldump --databases testdb testdemo --single-transaction --flush-logs --master-data=2 > databases.sql

4）备份所有数据库。

mysqldump --all-databases --single-transaction --flush-logs --master-data=2 > all_databases.sql

5）只导出表结构不导出数据

mysqldump --opt -d demo -u root -p > xxx.sql

6）导出数据不导出结构

mysqldump　-t　demo　-uroot　-p　>　xxx.sql

7）mysqldump导出表的部分数据

mysqldump -uroot -proot -h172.16.18.2 库名 表名 --where=" id <10" > test.sql

8）导出一个库中多个表，表名中间用空格隔开就行，如导出dba库下的表t1和 t2

mysqldump -u root -p dba t1 t2 > /download/t1_t2.sql

备份时注意事项：

-- 表示当mysql的版本为4.01.03以上时，这里面的sql语句是会执行的
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

-- 在大批量导入时，先禁用索引，然后导入数据完成之后再开启索引，一次性重建索引的效率更高

-- 禁用表(student)的索引
alter table student disable keys;
-- 启用表(student)的索引
alter table student enable keys;

-- 为指定表设置读锁定，那么该线程和其它线程只能从该表中读数据，不能进行任何写操作
lock tables demo.student read;
-- 释放锁定
unlock tables;

-- 为指定表设置写锁定，那么该表只有拥有这个锁的线程才能读写，其它的线程被阻塞
lock tables demo.student write;
-- 释放锁定
unlock tables;

mysqldump全量备份shell

#!/bin/bash
#在使用之前，请提前创建以下各个目录
#获取当前时间
date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/home/db/backup/mysql
username="root"
password="123456"
db_name="zone"
#定义备份文件名
fileName="${db_name}_${date_now}.sql"

#定义备份文件目录
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."
/usr/bin/mysqldump -u${username} -p${password}  --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}

#进入到备份文件目录
cd ${backUpFolder}
#压缩备份文件
tar zcvf ${fileName}.tar.gz ${fileName}

date_end=$(date "+%Y%m%d-%H%M%S")
echo "finish backup mysql database ${db_name} at ${date_end}."

2、mysql恢复数据指令

1) mysql命令恢复至指定数据库

-- 恢复至指定数据库(Test)
mysql -u root -h 127.0.0.1 -p Test < c:\demo.sql

如果已经登录mysql，则可以使用这种方式恢复至指定数据库，但只能在cmd界面下执行source命令，不能在mysql工具里面执行source命令。

-- 如果已经登录mysql，则可以使用这种方式恢复至指定数据库
-- 只能在cmd界面下执行source命令，不能在mysql工具里面执行source命令
use Test;
source c:\demo.sql

2）从所有备份的数据库文件中恢复某一个数据库

mysql -u root -p song --one-database< /demo.sql 

其中 201503311428.sql 是所有数据库备份的文件，从其中只恢复song 这个数据库

3、注意事项

1）通过mysqldump备份的数据库文件，如果用了(--all-databases)或(--databases)选项，则在备份文件中包含create database和use语句，故并不需要指定一个数据库名去恢复备份文件。

2）如果通过mysqldump备份的是单个数据库，且没有使用(--databases)选项，则备份文件中不包含create database和use语句，那么在恢复的时候必须先创建数据库并指定该数据库。

3）调用mysqldump带有--tab=dir_name（指定备份的目录）选项备份数据库，则dir_name表示输出文件的目录，在这个指定的目录中，需备份的每个表将会产生两个文件（

结构sql文件，数据txt文件）

例如：对于一个名为t1的表，备份会生成两个文件，即t1.sql和t1.txt

t1.sql文件中包含create table语句，t1.txt文件中一行对应为数据表中的一条记录，列值与列值之间以‘tab’分隔。

-- 带有--tab=dir_name选项的备份
mysqldump -u root -h 127.0.0.1 -p test --tab= /data/backup

带有--tab=dir_name选项的备份在恢复时，需要先恢复表结构，然后恢复表中的数据。

#恢复表结构
mysql -u root -h 127.0.0.1 -p test <c:\Users\liufa\Desktop\student.sql

# 恢复表中的数据
mysqlimport -u root -h 127.0.0.1 -p test c:\Users\liufa\Desktop\student.txt

4、使用mysqldump错误汇总

MysqL5.7在使用mysqldump命令备份数据库报错：

mysqldump: [Warning] Using a password on the command line interface can be insecure.

问题描述：

在阿里云服务器增加一个shell脚本定时备份数据库脚本执行任务时，测试性的执行了备份命令，如下

/usr/local/mysql/bin/mysqldump -uroot -pmyServerPwd# dateabase > /data/mysql_bak/bak_test.sql

在执行完了命令本该在指定的目录下出现bak_test.sql文件，然而并没有生成，报了一行错误。这个问题应该是在MySQL5.6+版本的时候就有出现，可能是为了确保数据库的安全性采用的保护机制。

mysqldump: [Warning] Using a password on the command line interface can be insecure.

解决方案：

网上大部分给出的解决方案都是修改mysql的配置文件，给[client]选项增加三行命令

vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

host = localhost　　　　　　　
user = root　　　　　　　　　　 
password = 'myServerPwd'

重启

service mysqld restart

同样执行备份命令，发现还是出现错误，但是目录下已经有了该备份文件

彻底解决

在搜索各种方案的过程中也无意中发现关于此方法在安全性上的一些官方性不足，mysql的官方给出的说明。

https://dev.mysql.com/doc/refman/5.6/en/password-security-user.html?spm=5176.7737807.2.3.D6p7hh

然后针对高版本Mysql备份数据库的解决方案来了，前方高能~~~

命令和以前常用的快速导入和导入命令有所不同了，需要加载我们配置的MYSQL配置文件!

/usr/local/mysql/bin/mysqldump --defaults-extra-file=/etc/my.cnf database > /data/mysql_bak/bak_test.sql    //备份导出数据库

/usr/local/mysql/bin/mysql --defaults-extra-file=/etc/my.cnf database < test_db.sql          //导入数据库

查看/data/mysql_bak下，发现想要的数据库备份文件出现了~

三、增量备份、恢复

MySQL通过在全备份基础上保证完整的二进制日志文件来达到增量备份的目的，所以要确保mysql开启bin-log（步骤略）

1、增量备份步骤

增量备份一般都是备份binlog文件 + 使用mysqldump全备，具体原因如下总结中有说明

1）全备（可以利用mysqldump)

2）开启二进制日志

定时增量备份shell脚本如下：（每天备份所有的bin-log)

#!/bin/bash

#在使用之前，请提前创建以下各个目录
backupDir=/usr/local/work/backup/daily

#增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/var/lib/mysql

#mysql的数据目录
logFile=/usr/local/work/backup/bak.log


#mysql的index文件路径，放在数据目录下的
BinFile=/var/lib/mysql/mysql-bin.index



#这个是用于产生新的mysql-bin.00000*文件
mysqladmin -uroot -p123456 flush-logs

# wc -l 统计行数
# awk 简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0

#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
   base=`basename $file`
   echo $base
   
   #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
   NextNum=`expr $NextNum + 1`
   if [ $NextNum -eq $Counter ]
   then
       echo $base skip! >> $logFile
       
   else
       dest=$backupDir/$base
       if(test -e $dest)
       
       #test -e用于检测目标文件是否存在，存在就写exist!到$logFile去
       then
           echo $base exist! >> $logFile
       else
           cp $mysqlDir/$base $backupDir
           echo $base copying >> $logFile
        fi
    fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $logFile

#执行上传备份文件到七牛云
#NODE_ENV=$backUpFolder@$backUpFileName /root/node/v8.11.3/bin/node /usr/local/upload.js

2、增量恢复步骤

1）利用全备进行恢复（只有在整个库/表被删除后，需要用全备份，否则只需要从日志中恢复就好）

2）利用全备以来的所有二进制日志进行完全恢复

3、mysqlbinlog介绍

mysqlbinlog为mysql自带的恢复工具，可以将binlog日志恢复到mysql中，前提是需要开启bin-log

（1）mysqlbinlog 命令的语法格式

#作用：将二进制日志文件内容恢复到 指定的数据库中
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

--------------------------------------------------------
常用参数选项解释：
--start-position=875                    #起始pos点
--stop-position=954                     #结束pos点
--start-datetime="2016-9-25 22:01:08"   #起始时间点
--stop-datetime="2019-9-25 22:09:46"    #结束时间点
--database=zyyshop                     #指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
-------------------------------------------------------- 

不常用选项： 
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个MySQL服务器上读取binlog日志

（2）mysqlbinlog 具体使用步骤

1）进入到mysqlbinlog目录，默认位置在mysql安装目录**/mysql/bin/下

2）使用mysqlbinlog进行查看bin日志

查询2018-11-12 09:00:00到2018-11-13 20:00:00 数据库为 youxi 的操作日志

mysqlbinlog --no-defaults --database=youxi --start-datetime="2018-11-12 09:00:00" --stop-datetime="2018-11-13 20:00:00" /data/mysql/mysql-bin.000015 > a.txt

查询2018-11-12 09:00:00到2018-11-13 20:00:00 数据库为 youxi 的操作日志，并输出到屏幕上

mysqlbinlog --no-defaults --database=youxi --start-datetime="2018-11-12 09:00:00" --stop-datetime="2018-11-13 20:00:00" /data/mysql/mysql-bin.000015   |more

查询2018-11-12 09:00:00到2018-11-13 20:00:00 数据库为 youxi 的操作日志，并且过滤出只包括user 表数据的操作记录

mysqlbinlog --no-defaults --database=youxi --start-datetime="2018-11-12 09:00:00" --stop-datetime="2018-11-13 20:00:00" /data/mysql/mysql-bin.000015   | grep user

看到对应的日志，可以从中找出删除的数据，但是这样肉眼找的工作量太大，所以上述只是根据mysqlbinlog工具根据查询条件，查出相关的数据，具体恢复还得继续

4、使用mysqlbinlog 恢复中错误处理

#报错 
unknown variable 'default-character-set=utf8

解决办法有2个：

1）一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。

2）二是用mysqlbinlog --no-defaults mysql-bin.000004 命令打开（加上--no-defaults参数）

一般建议第二种方案

勤天

关注

5
点赞
踩
40

收藏

觉得还不错? 一键收藏
0
评论
mysqldump全量/增量进行备份/恢复

目录一、mysql备份说明1、myisam备份介绍2、innodb备份介绍3、全量备份和增量备份4、一些备份工具二、mysqldump全量备份与恢复1、mysqldump全量备份2、mysql恢复数据指令3、注意事项4、使用mysqldump错误汇总三、增量备份、恢复1、增量备份步骤2、增量恢复步骤3、mysqlbinlog介绍4、使用mysqlbinlog 恢复中错误处理一、mysql备份说明mysql的备份可以分为冷备份和热备
复制链接

扫一扫