MySQL的备份策略_数据库备份策略

MySQL的备份策略

1.简介

           备份有很多种，按存储方式分为物理备份（备份具体数据）和逻辑备份（备份SQL语句，这个可以通过查看导出的SQL文件看到），按备份过程分为冷备（需要服务停机，这个时候MySQL实例是停止的，再进行数据导出）和热备（服务器不停机）。

2.备份策略

1.全备

2.增备

3.差异备份

3.备份的类型

1.冷备

停库,停服务,备份

​ 这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

2.热备

不停库,不停服务,备份,也不会(锁表)阻止用户的写入

​ 这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。

3.温备

不停库,不停服务,备份,会(锁表)阻止用户的写入

​ 这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

备份工具:

​ 1.mysqldump: mysql原生自带很好用的逻辑备份工具
​ 2.mysqlbinlog: 实现binlog备份的原生态命令

​ 3. Xtrabackup: precona公司开发的性能很高的物理备份工具

4.其他常用的备份方式:

4.1全量备份:

​ 全量数据就是数据库中所有的数据（或某一个库的全部数据）；

　 全量备份就是把数据库中所有的数据进行备份。

　 mysqldump会取得一个时刻的一致性数据.

4.2增量备份 (刷新二进制日志)

​ 增量数据就是指上一次全量备份数据之后到下一次全备之前数据库所更新的数据

　　对于mysqldump,binlog就是增量数据.

4.3差异备份

​ 备份自从上一次完全备份后的全部改动和新文件
​ 备份速度较快,恢复速度较快,空间要求较多

​ 能够更快且简单的恢复(相比较增量)

​ 需要最近一次完全备份和最后一次差异备份就能快速恢复

4.4建议的恢复策略

完全+增量+二进制日志
完全+差异+二进制日志

5.设计场景

1）增量备份在周一到周日凌晨3点，复制mysql-bin.00000*到指定目录；
2）全量备份则使用mysqldump将整个数据库导出，每日凌晨3点执行，并会删除昨天留下的mysq-bin.00000*，然后对mysql的备份操作会保留在bak.log文件中。

6.服务器环境

主机：centos7；数据库：mysql5.7

7.技术点

mysqldump、mysqlbinlog、crontab

8.全量备份一(mysqldump)

我们最常用的就是MySQL自带的工具mysqldump。

mysqldump 常用来做温备

8.1．备份整个数据库

语法：mysqldump -uroot -p123456 --all-databases >/opt/amc.sql;

#备份全库

-A:--all-databases   全库备份

mysqldump -A

#备份多个库

-B:--databases        指定库备份 只能指定数据库

mysqldump -B zls world db1

--triggers   备份触发器数据

8.2．备份单个数据库的整个表

语法：mysqldump -uroot -p123456 --databases test>/opt/ amc.sql;

8.3．备份单个数据库的单张表

8.3.1．单个表结构：

语法：mysqldump -uroot -p123456 -d amc sv_content >/opt/sv_content_jiegou.sql;

8.3.2．单个表数据：

语法：mysqldump -uroot -p123456 test sv_content>/opt/sv_content_neirong.sql;

8.4．增量备份后对应的数据恢复操作

8.4.1．数据恢复可以使用mysql工具操作

mysql -f -h localhost -u root -p [database_name] > database_name.sql;

8.4.2．使用source

use [database_name];

source /opt/database_name.sql;

exit;

9.全量备份二(mydumper适用大数据库)

9.1.说明

mysqldump最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合，所以可以用mydumper，mydumper是一个针对MySQL和drizzle的高性能多线程的备份和恢复工具。

9.2mydumper 备份原理

备份过程信息

先把general_log 打开

show variables like 'general_log';#如果general_log 为OFF

set global general_log=1;  #将general_log 为ON

show variables like '%general%';

 

删除原来备份数据，进行重新备份

# 删除备份数据

[root@mysql-150 mysql]# cd /data/backup/mysql/

[root@mysql-150 mysql]# pwd

/data/backup/mysql

[root@mysql-150 mysql]# rm -rf *

# mydumper备份

[root@mysql-150 mysql]# mydumper -u root -p 123456  -P 3306 -h 127.0.0.1 -B beta  -o /data/backup/mysql/

备份原理

1、主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，保证数据的一致性 
2、读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用 
3、N个（线程数可以指定，默认是4）dump线程把事务隔离级别改为可重复读 并开启读一致的事物 
4、dump non-InnoDB tables, 首先导出非事物引擎的表 
5、主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁 
6、dump InnoDB tables, 基于事物导出InnoDB表 
7、事物结束

9.2 mydumper的主要特性

1.采用轻量级C语言写的代码；

2.相比于mysqldump，其速度快了近10倍；

3.具有事务性和非事务性表一致的快照（适用于V0.2.2+）；

4.可快速进行文件压缩（File compression on-the-fly）；

5.可多线程恢复（适用于0.2.1+）；

6.支持导出binlog；

7.支持定时备份

8.可以用守护进程的工作方式，定时扫描和输出连续的二进制日志。

9.3 mydumper 参数解释

-B, --database                      要备份的数据库，不指定则备份所有库

-T, --tables-list                        需要备份的表，名字用逗号隔开

-o, --outputdir              备份文件输出的目录

-s, --statement-size       生成的insert语句的字节数，默认1000000

-r, --rows                 将表按行分块时，指定的块行数，指定这个选项会关闭 --chunk-filesize

-F, --chunk-filesize         将表按大小分块时，指定的块大小，单位是 MB

-c, --compress            压缩输出文件

-e, --build-empty-files     如果表数据是空，还是产生一个空文件（默认无数据则只有表结构文件）

-x, --regex                是同正则表达式匹配 'db.table'

-i, --ignore-engines       忽略的存储引擎，用都厚分割

-m, --no-schemas         不备份表结构

-k, --no-locks             不使用临时共享只读锁，使用这个选项会造成数据不一致

--less-locking             减少对InnoDB表的锁施加时间（这种模式的机制下文详解）

-l, --long-query-guard     设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）

--kill-long-queries         杀掉长查询 (不退出)

-b, --binlogs              导出binlog

-D, --daemon             启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份

-I, --snapshot-interval     dump快照间隔时间，默认60s，需要在daemon模式下

-L, --logfile               使用的日志文件名(mydumper所产生的日志), 默认使用标准输出

--tz-utc                    跨时区是使用的选项，不解释了

--skip-tz-utc              同上

--use-savepoints          使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限

--success-on-1146          Not increment error count and Warning instead of Critical in case of table doesn't exist

-h, --host                 连接的主机名

-u, --user                 备份所使用的用户

-p, --password            密码

-P, --port                 端口

-S, --socket               使用socket通信时的socket文件

-t, --threads                开启的备份线程数，默认是4

-C, --compress-protocol   压缩与mysql通信的数据

-V, --version              显示版本号

-v, --verbose              输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为 2

9.4 myloader 参数解释

-d, --directory                              备份文件的文件夹

-q, --queries-per-transaction        每次事物执行的查询数量，默认是1000

-o, --overwrite-tables                   如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构

-B, --database                              需要还原的数据库

-e, --enable-binlog                       启用还原数据的二进制日志

-h, --host                                      主机

-u, --user                                      还原的用户

-p, --password                              密码

-P, --port                                      端口

-S, --socket                                  socket文件

-t, --threads                                 还原所使用的线程数，默认是4

-C, --compress-protocol               压缩协议

-V, --version                                 显示版本

-v, --verbose                                输出模式, 0=silent, 1=errors, 2=warnings, 3 = info, 默认为2

9.5下载与安装

mydumper使用c语言编写，使用glibc库 
mydumper安装所依赖的软件包，glibc, zlib, pcre, pcre-devel, gcc, gcc-c++, cmake, make, mysql客户端库文件

安装步骤

yum -y  install glib2-devel mysql-devel zlib-devel pcre-devel zlib gcc-c++ gcc cmake

wget https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz

tar zxf mydumper-0.9.1.tar.gz

cd mydumper-0.9.1/

cmake .

make

make install

#安装完成后生成两个二进制文件mydumper和myloader位于/usr/local/bin目录下

ls /usr/local/bin/

9.5备份

备份amc库到data/backup/mysql中

mydumper -u root -p 123456  -P 3306 -h 127.0.0.1 -B amc -o /data/backup/mysql/

 

从上面可以可以看出

备份所生成的文件 
目录中包含一个metadata文件

记录了备份数据库在备份时间点的二进制日志文件名，日志的写入位置，

如果是在从库进行备份，还会记录备份时同步至主库的二进制日志文件及写入位置 
每个表有两个备份文件：

1。。。database.table-schema.sql 表结构文件

2。。。database.table.sql 表数据文件

9.6恢复

恢复 beta 库

# 删除 beta 库

root@localhost [(none)]>drop database beta;

# myloader 恢复

[root@mysql-150 mysql]# myloader -u root -p 123456 -h 127.0.0.1 -B beta -d /data/backup/mysql/

# 验证

root@localhost [(none)]>show databases;

 

9.7全量备份脚本

进入/home/mysql目录

新建目录：mkdir backup

进入backup目录，新建daily目录：mkdir backup

切换到/home/mysql目录，执行：

#vim Mysql-FullyBak.sh

 

参数说明：
–lock-tables
锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于MySQL数据库引擎为MyISAM 表，如果是 Innodb 表可以用 –single-transaction 选项。
–flush-logs
结束当前日志，生成新日志文件。
–delete-master-logs
清除以前的日志，以释放空间。但是如果服务器配置为镜像的复制主服务器，用–delete-master-logs删掉MySQL二进制日志很危险，因为从服务器可能还没有完全处理该二进制日志的内容。在这种情况下，使用 PURGE MASTER LOGS更为安全。
–quick
该选项在导出大表时很有用，它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
–single-transaction
该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。本选项和 –lock-tables 选项是互斥的，因为lock-tables会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 –quick 选项。
–events
导出事件
–master-data=2
其中参数–master-data=[0|1|2]
0: 不记录
1：记录为CHANGE MASTER语句
2：记录为注释的CHANGE MASTER语句
–master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称，
用于日后恢复时参考，例如输出的备份SQL文件中含有：
CHANGE MASTER TO MASTER_LOG_FILE=’MySQL-bin.000002′, MASTER_LOG_POS=106;

10.增量备份

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

10.1步骤

1. 修改mysql配置文件

#vim /etc/my.cnf

添加

server_id=1

Log_bin=/home/mysql/mysql-bin/mysql-bin

说明：其中server-id表示单个结点的id，这里由于只有一个结点，所以可以把id随机指定为一个数，这里将id设置成1。若集群中有多个结点，则id不能相同(对于5.7以下版本不需要指定server-id)；

log_bin指定binlog日志文件的存储路径，日志文件以mysql-bin开头

1. 重启mysql

Service mysqld restart

1. 查看日志文件

#cd /home/mysql/mysql-bin

ll

目录下就有mysql-bin.00001   index等文件

1. 进入数据库查看启动效果

#show variables like '%log_bin%';

 

结合linux定时任务crontab使用，此处略

10.2增量备份脚本

切换到/home/mysql目录，执行

#vim binlogbak_zl.sh

#!/bin/bash

export LANG=en_US.UTF-8

BakDir=/data/mysqlbak/data/zlbak

BinDir=/var/lib/mysql

LogFile=/data/mysqlbak/log/binlog.log

BinFile=/var/lib/mysql/logindex.index

mysqladmin -h 127.0.0.1 -uroot -p123456 flush-logs

#这个是用于产生新的mysql-bin.00000*文件

Counter=`wc -l $BinFile |awk '{print $1}'`

NextNum=0

#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的。

for file in `cat $BinFile`

do

    base=`basename $file`

    #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./

    NextNum=`expr $NextNum + 1`

    if [ $NextNum -eq $Counter ]

    then

        echo $base skip! >> $LogFile

    else

        dest=$BakDir/$base

        if(test -e $dest)

        #test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去。

        then

            echo $base exist! >> $LogFile

        else

            cp $BinDir/$base $BakDir

            echo $base copying >> $LogFile

        fi

    fi

done

echo `date +"%Y年%m月%d日 %H:%M:%S"` Backup success! >> $LogFile

11.差异备份

11.1原理

1. 差异备份所基于的是最近一次的完整备份。这称为差异“基准**”。差异备份仅包括自建立差异基准后更改的数据。

差异备份的大小取决于自建立差异基准后更改的数据量。通常，差异基准越旧，新的差异备份就越大。特定的差异备份将在创建备份时捕获已更改的区的状态。如果创建一系列差异备份，则频繁更新的区可能在每个差异中包含不同的数据。当差异备份的大小增大时，还原差异备份会显著延长还原数据库所需的时间。因此，建议按设定的间隔执行新的完整备份，以便为数据建立新的差异基准。例如，您可以每周执行一次整个数据库的完整备份（即完整数据库备份），然后在该周内执行一系列常规的差异数据库备份。

1. “差异数据库备份”，只记录自上次完整数据库备份后更改的数据。此完整备份称为“差异基准”。差异数据库备份比完整数据库备份更小、更快。这会缩短备份时间，但将增加复杂程度。对于大型数据库，差异备份的间隔可以比完整数据库备份的间隔更短。这将降低工作丢失风险。

11.2差量备份用到的工具

• mysqladmin ----用来备份数据库
• mysqlbinlog ----用来恢复数据库

11.3一次差异备份的操作

    步骤1：开启二进制日志功能

   

    [root@xaii-59 ~]# vim /etc/my.cnf

    [root@xaii-59 ~]# tail  /etc/my.cnf

    basedir = /usr/local/mysql

    datadir = /opt/data

    socket = /tmp/mysql.sock

    port = 3306

    pid-file = /opt/data/mysql.pid

    user = mysql

    skip-name-resolve

   

    server-id = 1         //服务器的标志符

    log-bin = mysql_bin        //开启二进制日志功能

    [root@xaii-59 ~]# service mysqld restart

    Shutting down MySQL.... SUCCESS!

    Starting MySQL.. SUCCESS!

    步骤二：

    对数据进行整体备份

    mysqldump -uroot -pming123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902221646

    步骤三：新增数据库表，删除数据库操作

    步骤四：查询二进制文件  ll/opt/data

    步骤五：恢复完整备份

mysql -uroot -p  < all-201902221646

    步骤六：查看binlog 日志,发现1180为删库前，所以恢复到1180

        show binlog events in 'mysql_bin.000002'\G

查询误删记录show master status;

找到误删记录对于的Pos

    步骤七：进行差异恢复

mysqlbinlog --stop-position=480 /opt/data/mysql_bin.000002 |mysql -uroot -pyong123!

12.物理备份：Xtrabackup

1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。

​2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。

3）备份时读取配置文件/etc/my.cnf

[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

不加时间戳,全备

[root@db01 full]# innobackupex --user=root --no-timestamp /backup/full

13.Linux下的定时任务

13.1. 安装crontab（centos7默认已经安装）

#yum install crontabs

13.2. 服务操作说明

systemctl start crond.service //启动服务

systemctl stop crond.service //关闭服务

systemctl restart crond.service //重启服务

systemctl reload crond.service //重新载入

#查看服务状态

systemctl status crond.service //服务状态

13.3.加入开机自动启动

#chkconfig –level 35 crond on

13.3.1.设置定时任务：

在命令行输入

#crontab -e

添加相应的任务，wq存盘退出

#每个星期日凌晨3:00执行完全备份脚本

0 3 * * 0 /bin/bash -x /home/mysql/Mysql-FullyBak.sh >/dev/null 2>&1

#周一到周六凌晨3:00做增量备份

0 3 * * 1-6 /bin/bash -x /home/mysql/Mysql-DailyBak.sh >/dev/null 2>&1

说明：默认情况下，crontab执行一次任务后，会通过email通知用户，为避免每次发信息，加入/dev/null 2>&1

13.3.2.查看定时任务：

查看定时任务：#crontab -l

参数与说明:
crontab -u //设定某个用户的cron服务，一般root用户在执行这个命令的时候需要此参数 ;
　　crontab -l //列出某个用户cron服务的详细内容;
　　crontab -r //删除所有用户的cron服务;
　　crontab -e //编辑某个用户的cron服务;
　　例如：root查看自己的cron设置:crontab -u root -l
　　例如：root删除用户fred的cron设置:crontab -u fred -r
补充：
（1）可直接编辑/etc/crontab 文件，即vi /etc/crontab，添加相应的任务（针对整个系统的crontab文件）;
（2）crontab执行定时任务的记录会写入到/var/log/cron这个文件中，该记录以帐号为区分。

时间格式

{minute} {hour} {day-of-month} {month} {day-of-week} {full-path-to-shell-script} 
o minute: 区间为 0 – 59 
o hour: 区间为0 – 23 
o day-of-month: 区间为0 – 31 
o month: 区间为1 – 12. 1 是1月. 12是12月. 
o Day-of-week: 区间为0 – 7. 周日可以是0或7.

每分钟执行一次备份脚本：

* * * * * sh /usr/your/path/mysqlBackup.sh

每五分钟执行 ：

*/5 * * * * sh /usr/your/path/mysqlBackup.sh

每小时执行：

0 * * * * sh /usr/your/path/mysqlBackup.sh

每天执行：

0 0 * * * sh /usr/your/path/mysqlBackup.sh

每周执行：

0 0 * * 0 sh /usr/your/path/mysqlBackup.sh

每月执行：

0 0 1 * * sh /usr/your/path/mysqlBackup.sh

每年执行:

0 0 1 1 * sh /usr/your/path/mysqlBackup.sh

 

13.4mysql定时任务备份（正在使用的例子）

13.4.1湖北日报线上案例（全备）

先写脚本

mysqlbak.sh

mysqldump -h localhost -uhbrb -phbrb@163.com amc_hbrb > /data/mysqlbak/amc_hbrb_bak_$(date +%F).sql

在crontab里面加入

10 0 * * * sh /usr/local/src/mysqlbak.sh

0 1 * * * find /data/mysqlbak -mtime +30 -type f -name "*.sql" | xargs rm -f

说明：每日凌晨10分的时候全量备份mysql

       删除近30天以外的文件（定期检查删除）

13.4.2备份脚本

13.4.2.1增量脚本（示例）

#!/bin/bash

#在使用之前，请提前创建以下各个目录

backupDir=/usr/local/work/backup/daily

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

mysqlDir=/var/lib/mysql

#mysql的数据目录

logFile=/usr/local/work/backup/bak.log

BinFile=/var/lib/mysql/mysql-bin.index

#mysql的index文件路径，放在数据目录下的

mysqladmin -uroot -p123456 flush-logs

#这个是用于产生新的mysql-bin.00000*文件

# 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

13.4.2.2全量备份（示例）

#!/bin/bash

export LANG=en_US.UTF-8

BakDir=/data/mysqlbak/data/allbak

ZlbakDir=/data/mysqlbak/data/zlbak

LogFile=/data/mysqlbak/log/bak.log

Date=`date +%Y-%m-%d-%H-%M-%S`

Begin=`date +"%Y年%m月%d日 %H:%M:%S"`

Database=amc

#BackName=`$Database-$Date`

cd $BakDir

DumpFile=$Database-$Date.sql

GZDumpFile=$Database-$Date.tar.gz

mysqldump -h 127.0.0.1 -uroot -p123456 $Database --flush-logs --delete-master-logs --single-transaction > $BakDir/$DumpFile

#mysqldump -h 127.0.0.1 -uroot -p123456 $Database  > $BakDir/$DumpFile

tar -czvf $GZDumpFile $DumpFile

rm $DumpFile

#count=$(ls -l *.tar.gz |wc -l)

if [ $count -ge 30 ]

#then

#    file=$(ls -l *.tar.gz |awk '{print $9}'|awk 'NR==1')

#    rm -f $file

#fi

#只保留过去四周的数据库内容

Last=`date +"%Y年%m月%d日 %H:%M:%S"`

echo 开始:$Begin 结束:$Last $GZDumpFile success >> $LogFile

# 进入到增量备份目录，删除binlog

#cd $ZlbakDir

#rm -f *

对应文件夹

 

例子：测试服务器（备份操作案例）

准备：测试服务器连接配置

测试服务器配置：

连接地址：121.36.65.132   端口：22

连接密码：stf@123.

Mysql账号密码： root / 123456

 

本次操作案例为amc_sxwb库

 

例子一：数据库的全量备份（一）

对amc_sxwb库的全量备份

执行语句：mysqldump -uroot -p123456 -B amc_sxwb >/data/mysql_databases_bak/amc.sql;

注意：执行时间根据数据库大小决定

 

执行结果：

 

单张表数据备份：

执行语句：mysqldump -uroot -p123456 amc_sxwb sv_member>/data/mysql_databases_bak/amc_sv_member.sql;  

 

执行结果：

 

例子二：数据库的全量备份（二）

使用mydumper

执行语句:mydumper -u root -p 123456 -P 3306 -h 127.0.0.1 -B amc_sxwb -o /data/mysql_databases_bak/amc_$(date +%Y%m%d) -v 3 -t 2

 

执行结果：

 

例子三： 增量备份

先确保 mysql.conf中是否添加

 

my.cnf内容：
log-bin = mysqlbin # 默认配置

一般放在/var/lib/mysql

 

 

查看当前使用的 mysql_bin.000*** 日志文件

show master status;

模拟一次增量备份：

Use amc_sxwb;

select * from sv_activity;

update sv_activity set activity_name='回家行囊里你给家人准备了啥？湖北日报邀你晒行李箱 ,11111' WHERE id =9;

mysqladmin -uroot -p123456 flush-logs;

show master status;

 

 

update sv_activity set activity_name='回家行囊里你给家人准备了啥？湖北日报邀你晒行李箱 ,8888888' WHERE id =9;

现在id为9的name为 8888888

现在还原

mysqladmin -uroot -p123456 flush-logs;

show master status;

执行：

mysqlbinlog /var/lib/mysql/mysql-bin.000171 | mysql -uroot -p123456 amc_sxwb;

发现name变为11111

编写定时任务

由于测试需要 暂设置为1分钟备份一次

*/1 * * * * /bin/bash -x /home/mysql/binlogbak_zl.sh >/dev/null 2>&1

 

 

例子四：差异备份

1. 开启mysql服务器的二进制日志功能

server-id = 1   // 设置服务器标识

log-bin = mysql_bin  //开启二进制功能

差异备份前提是要对mysql做完整备份

mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all.Sql;

操作amc_sxwb; 对表进行增删改

刷新二进制文件

mysqladmin -uroot -p123456 flush-logs;

对数据库完全恢复

mysql -uroot -p  < all.sql;

发现数据库是增删改之前的。

查看bin-log日志

show binlog events in 'mysql_bin.000002';

查询对应的End_log_pos

mysqlbinlog --stop-position=505 /var/lib/mysql/mysql_bin.000002 |mysql -uroot -p123456 -B amc_sxwb;

例子：测试服务器（恢复操作）

例子一：全量备份的恢复

语句：mysql -h 127.0.0.1 -uroot -p123456 -B amc_sxwb < /data/mysqlbak/data/allbak/amc_sxwb-2020-09-10-22-37-01.sql

或者：登陆到mysql命令中用 use databases;

    source  …../xxx.sql;文件

例子二：mydumper的全量备份恢复

删除数据库后恢复：

语句：myloader -u root -p 123456 -B amc_sxwb -d /data/mysql_databases_bak/amc_20200910

例子三：增量恢复

参考例子备份操作，例子三增量备份一起写了

例子四：差异恢复

参考例子备份操作，例子四增量备份一起写了