Mysql数据库备份与恢复

1 数据备份与恢复

2 使用第三方软件进行备份

1 数据备份与恢复

1.1 数据备份方式

物理备份：直接拷贝库或者表对应的文件。cp,tar, ...

具有局限性，前提是表的存储引擎为myisam，跨平台性差，数据备份恢复浪费时间。

逻辑备份：执行备份时，根据已有的数据，生成对应的sql命令，把sql保存到指定的文件里。恢复时执行备份文件里的sql命令。

1.2 数据备份策略

完全备份：备份所有数据。

增量备份：备份自上一次备份之后有变化的数据

差异备份：备份自完全备份之后有变化的数据

完全备份+增量备份

完全备份+差异备份

1.3 完全备份

备份命令：

[root@ser51 ~]#mysqldump -hlocalhost -uroot -p密码 源库名 >文件名

源库名的表示：

--all-databases  所有库

库名             指定单个库

库名 表名        指定库的表（注意库名和表名之间是空格）

-B 数据库1 数据库2  备份多个库

恢复命令：

两种方式：

(1)[root@ser51 ~]#mysql 连接库操作  目标库名 <文件名.sql

(2)mysql>source 备份文件路径;

1.4 增量备份

(1)启用binlog日志 实现实时增量备份

binlog日志介绍：二进制日志，是mysql数据库服务日志文件中的一种，记录执行的除查询之外的sql命令。默认没有启用。

vim  /etc/my.cnf

[mysqld]

server_id=数值  #数值范围1-255

log_bin          #启用默认存放路径/var/lib/mysql/

binlog_format="mixed"

重启服务之后生成的文件为：

/var/lib/mysql/主机名-bin.000001  

#超过500M生成新的（000001-999999）

/var/lib/mysql/localhost-bin.index    #索引文件

#systemctl   restart  mysqld       #重启服务

(2)查看日志当前记录格式

mysql > show variables like "binlog_format";

三种记录格式：

statement:每一条修改数据的sql命令都会记录在binlog日志中；

row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。；

mixed:是以上两种格式的混合使用。

(3)查看binlog日志文件内容

#mysqlbinlog  /var/lib/mysql/localhost-bin.000001

# mysqlbinlog localhost-bin.000001  | grep -i insert

(4)自定义binlog日志文件存储的目录和文件名

# mkdir /logdir

# chown  mysql  /logdir;  setenforce  0

#vim /etc/my.cnf

server_id=数值  #数值范围1-255

log_bin=/logdir/mysql   

#自定义存放路径(mysql相当于主机名，必须写，无需mkdir)

binlog_format="mixed"

#systemctl  restart  mysqld

#ls  /logdir/

(5)分析binlog日志

binlog日志文件记录sql命令的方式？

时间点

--start-datetime="yyyy-mm-dd  hh:mm:ss"

--stop-datetime="yyyy-mm-dd  hh:mm:ss"

pos点（偏移量）

--start-position=数字     

--stop-position=数字

[root@ser51 logdir]# mysqlbinlog

--start-position=336 --stop-position=1494  mysql.000001

(6)执行binlog日志里的sql命令恢复数据

#mysqlbinlog  [选项]  日志文件名  |  mysql  -uroot  -p123qqq

#mysqlbinlog --start-position=300 --stop-position=1006   /var/lib/mysql/localhost-bin.000001  | mysql  -uroot  -p123456

(7)手动生成新的binlog日志

第1种：mysql> flush  logs;     #刷新一次生成一次

第2种：mysql -uroot -p123qqq -e "flush logs"

#-e就是以mysql登录方式执行sql命令

第3种：# systemctl  restart mysqld

第4种：#mysqldump -uroot -p123qqq --flush-logs  teadb t7  > /databak/t7.sql  #备份的时候重新生成

(8)删除已有的binlog日志文件

第1种：mysql> reset  master;   #重置所有

第2种：mysql> purge  master  logs  to  "binlog文件名";

#删除指定binlog之前的日志

第3种：#rm  -rf   binlog日志文件

2 使用第三方软件进行备份

2.1 安装Percona

一款强大的在线热备份工具，备份过程中不锁库表，适合生产环境。

主要含两个组件：

xtrabackup:

innobackupex:

[root@ser51 ~]# yum -y install perl-Digest-MD5 perl-DBD-MySQL

#环境不一样安装的依赖包不一样

[root@ser51 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

[root@ser51 ~]# rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

2.2 innobackupex完全备份

(1) 基本选项：

--host  --user  --port   --password  --databases=  

--no-timestamp #不用日期命名备份文件存储的子目录名

--redo-only #日志回滚合并，最后一次增量备份日志回滚不需要此项

--apply-log #准备还原（回滚日志）

--copy-back #恢复数据

--incremental 目录名 #增量备份存放的路径

--incremental--basedir=目录名  

#增量备份时，指定上一次备份数据存储的目录名

--incremental--dir=目录名 #恢复数据时，指定增量备份数据存储的目录名

--export #导出表信息

import  #导入表空间

恢复数据的时候要求数据库目录必须是空的（/var/lib/mysql）

(2) 使用innobackupex做完全备份和完全恢复

案例：完全备份到/allback中

[root@ser51 ~]#innobackupex --user root  --password 123456 --databases="mysql performance_schema sys db66" /allback

#databases：要备份的数据库名  /allback创建的时候自动生成，无需创建

[root@ser51 ~]#ls /allback/2018-01-22_21-19-33/

#查看生成的备份文件

[root@ser51 ~]#innobackupex --user root  --password 123456 --databases="mysql performance_schema sys db66" --apply-log  /allback/2018-01-22_21-19-33     

#同步日志

[root@ser51 ~]#rm -rf /var/lib/mysql/  #恢复时要求清空

[root@ser51 ~]#mkdir /var/lib/mysql  #创建新的

[root@ser51 ~]#innobackupex --user root  --password 123456 --databases="mysql performance_schema sys db66" --copy-back  /allback/2018-01-22_21-19-33   

#恢复数据

[root@ser51 ~]#ls -ld /var/lib/mysql  #查看目录的属性

[root@ser51 ~]#chown -R mysql:mysql /var/lib/mysql

#更改为所有者，属组为mysql

[root@ser51 ~]#systemctl restart mysqld   #重启服务

[root@ser51 ~]#mysql -uroot -p123456     #进入查看是否恢复成功

如果服务没有成功？

mv /etc/my.cnf  /etc/my.cnf.back

mysql_install_db --user=mysql --datadir=/var/lib/mysql

2.3 innobackupex增量备份

(1) 条件

必须有一次完全备份，本例以备份到/onedir为例

第一次增量备份到/new1dir中

第二次增量备份到/new2dir中

(2)增量备份

第一步：完全备份

[root@ser51 ~]#innobackupex --user root --password 123456 --databases="mysql performance_schema sys db66" /onedir  --no-timestamp

第二步：改变db66数据库表中的数据

进行第一次的增量备份到/new1dir

[root@ser51 ~]#innobackupex --user root --password 123456 --databases="mysql performance_schema sys db66" --incremental  /new1dir  --incremental-basedir=/onedir --no-timestamp

#增量备份时，指定上一次备份数据存储的目录名

第三步：再次改变db66数据库表中的数据

进行第二次的增量备份到/new2dir

[root@ser51 ~]#innobackupex --user root --password 123456 --databases="mysql performance_schema sys db66" --incremental  /new2dir  --incremental-basedir=/new1dir --no-timestamp

第四步：数据恢复

[root@ser51 ~]# innobackupex --user root --password 123456 --databases="mysql performance_schema sys db66"

--apply-log  --redo-only  /onedir   #恢复完全备份日志

[root@ser51 ~]# innobackupex --user root --password 123456 --databases="mysql performance_schema sys db66"

--apply-log  --redo-only  /onedir/  --incremental-dir=/new1dir

#恢复增量日志

[root@ser51 ~]# innobackupex --user root --password 123456 --databases="mysql performance_schema sys db66"

--apply-log  --redo-only  /onedir/  --incremental-dir=/new2dir

#恢复增量日志

[root@ser51 ~]# innobackupex --user root --password 123456 --databases="mysql performance_schema sys db66" --copy-back /onedir  #恢复文件

[root@ser51 ~]# chown -R mysql:mysql /var/lib/mysql/

[root@ser51 ~]# systemctl restart mysqld

登录数据库查看数据是否恢复

2.4 增量备份原理：

在/var/lib/mysql/下存在事务日志ib_logfile0 和ib_logfile1  ibdata1

备份之后同步日志会在新的备份的文件内存在以下两个文件：

xtrabackup_checkpoints #存在lsn：日志序列号

xtrabackup_logfile  #日志文件

每一次的增量恢复日志时，会和完全备份的日志进行合并，并且日志序列号会发生变化，这个变化记录了合并的序列号位置，同时会把增量备份的数据合并到完全备份中。

[root@ser51 ~]# cat /onedir/xtrabackup_checkpoints

[root@ser51 ~]# cat /new1dir/xtrabackup_checkpoints

[root@ser51 ~]# cat /new2dir/xtrabackup_checkpoints

2.5恢复完全备份数据中的某张表

(1)删除表空间命令

mysql> alter  table 库.表  discard  tablespace;

(2)前提完全备份的数据库：

#innobackupex --user root --password 123456  --databases="db66"   /db66bak   --no-timestamp

#ls /db66bak

(3)mysql> use db66;

mysql> drop  table  a;  #删除表a，对表a操作恢复的过程

mysql> create  table a(id int); # 建空表，表字段信息必须和原来一致

#ls /var/lib/mysql/db66/

a.frm  a.ibd

(4)导出表信息

#innobackupex --user root --password 123456

--databases="db66" --apply-log --export /db66bak  

#ls /db66bak/db66/ #查看发生了什么变化

(5)删除表空间

mysql> alter  table db66.a  discard  tablespace;  

#ls /var/lib/mysql/db66/

(6)把备份目录下导出的表信息文件拷贝到数据库目录下

# cp /db66bak/db66/a.{cfg,exp,ibd}  /var/lib/mysql/db66/

(7)修改文件所有者

# chown  mysql /var/lib/mysql/db66/a.*

(8)导入表空间

mysql> alter  table  db66.a  import  tablespace;

mysql> select  * from  a;

mysql> select  * from  b;