MySQL数据备份

数据备份

1.逻辑备份

备份的是建表，建库，插入，更新等操作所执行SQL语句，适用于中小型数据库，效率相对较低。

本质：导出的是SQL语句文件

优点：不论是什么存储引擎，都可以用mysqldump备成SQL语句

缺点：速度较慢，导入时可能会出现格式不兼容的突发情况，无法做增量备份和累计增量备份。

提供三种级别的备份，表级和全库级

数据一致，服务可用。如何保证数据一致，在备份的时候会进行锁表会自动锁表。锁住之后在备份。

1.本身为客服端工具
2.远程备份语法：#MySQLdump -h 服务器 -u用户名 -p 密码 数据库名 > 备份文件.sql
3.本地备份语法：#mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql

常用备份选项

-A ,--all-databases \ 	备份所有库
-B ,--databases bbs test mysql 	备份多个数据库
msyqldump -p密码 -A > 备份文件.sql
数据库没了备份了，直接输出文件导入备份文件
msyqldump -p密码 -A < 备份文件.sql
msyqldump -p密码 -B 库名 > 备份文件. 备份多个数据库自己指定的
把库删了有备份导入：
#1.mysql> source /path/to/lhg.sql;#输入文件路径
#2.mysql -p密码 < db.sql
#3.cat db.sql | mysql -p密码
mysqldump -u用户名 -p密码 -h主机地址 -P端口 -S套接字文件
mysqldump -p密码 dbname tablename -d > tablename_jiegou.sql 	备份表结构
mysqldump -p密码 库名 tablename1 tablename2  > db_t1_t2.sql	          表级备份
# 恢复表 
#1.mysql -p123 库名 < lhg.sql
#2.cat db.sql | mysql -p密码 库名
#3.mysql> source 文件的路径
导出数据：
#更改数据库导出安全目录
#mysql> show variables like "secure%";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_auth      | ON                    |
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
#修改方法
mkdir /data
chown mysql.mysql /data
vim /etc/my.cnf
secure_file_priv=/data/
重启数据库
查看安全路径：
show variables like '%secure%';
show variables like "securce%";/var/lib/mysql-files
mysql>select * from mysql（库名）.user（表名） into outfile '/var/lib/mysql-files/user.xlsx'（路径选择安全目录里） 
导入数据
mysql>load data infile '/var/lib/mysql-files/user.xlsx'into table user;
#flush privileges;刷新

2.日志恢复数据Mysql bin-log

根据二进制日志文件恢复数据

vim /etc/my.cnf
1、开启binlog日志功能
server-id = 1
log-bin = /data/mysql-bin
重启数据库
mysql>flush logs;	
查看二进制日志：
mysqlbinlog /data/mysql-bin.00001 --base64-output=decode-rows -vv
找到要恢复的sql语句的起始位置、结束位置

```
[root@mysql-1 mysql]# mysqlbinlog mysql-bin.000002			#查看binlog日志内容


BEGIN
	position 123
	……
COMMIT
	position 789
恢复：
mysqlbinlog --start-position 123 --stop-position 789 /data/mysql-bin.00001 | mysql -p密码 school
 mysqlbinlog --start-position 219 --stop-position  321 mysql-bin.000002 |mysql -uroot -p123		#恢复语句
	

3.物理备份

热备份

数据库启动同时给客户端提供服务的情况下 

冷备份 数据库要关掉或者不能给客户端提供服务

官网：www.percona.com

安装xtrabackup

# wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
# rpm -ivh percona-release-0.1-4.noarch.rpm
[root@mysql-server yum.repos.d]# vim percona-release.repo

全量备份

创建备份目录：
[root@mysql-server ~]# mkdir /xtrabackup/full -p
备份：
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup/full
    innobackupex --user --password密码 /opt/full\
    			完全备份恢复流程
1. 停止数据库   systemctl stop mysqld&&systemctl stop firewalld&&setenforce 0
2. 清理环境
3. 重演回滚－－> 恢复数据
4. 修改权限
5. 启动数据库
1.关闭数据库：
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
[root@mysql-server ~]# rm -rf /var/log/mysqld.log			#可选操作
[root@mysql-server ~]# rm -rf /var/log/mysql-slow/slow.log  #可选操作
2.恢复之前的验证恢复:
[root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2019-08-20_11-47-49
3.确认数据库目录：
恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里
# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
4.恢复数据：
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_11-47-49
5.修改权限：
[root@mysql-server ~]# chown mysql.mysql  /var/lib/mysql -R
启动数据库:
[root@mysql-server ~]# systemctl start mysqld

					恢复流程
1. 停止数据库   systemctl stop mysqld&&systemctl stop firewalld&&setenforce 0
2. 清理环境
3. 重演回滚－－> 恢复数据
4. 修改权限
5. 启动数据库
1.关闭数据库：
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
[root@mysql-server ~]# rm -rf /var/log/mysqld.log			#可选操作
[root@mysql-server ~]# rm -rf /var/log/mysql-slow/slow.log  #可选操作
2.恢复之前的验证恢复:
[root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2019-08-20_11-47-49
3.确认数据库目录：
恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里
# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
4.恢复数据：
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_11-47-49
5.修改权限：
[root@mysql-server ~]# chown mysql.mysql  /var/lib/mysql -R
启动数据库:
[root@mysql-server ~]# systemctl start mysqld

增量备份

备份

完整备份:周一

```
[root@mysql-server ~]# rm -rf /xtrabackup/*
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2019-08-20_14-51-35
[root@mysql-server xtrabackup]# cd 2019-08-20_14-51-35/
[root@mysql-server 2019-08-20_14-51-35]# ls
backup-my.cnf  ib_buffer_pool  mysql               sys   testdb                  xtrabackup_info
company        ibdata1         performance_schema  test  xtrabackup_checkpoints  xtrabackup_logfile
```

2、增量备份：周二　——　周三

```
在数据库中插入周二的数据:
mysql> insert into testdb.t1 values(2);    #模拟周二
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_14-51-35/
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2019-08-20_14-51-35  2019-08-20_15-04-29    ---相当于周二的增量备份
```

```
在数据库中插入周三的数据:
mysql> insert into testdb.t1 values(3);   #模拟周三
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_15-04-29/      #基于前一天的备份为目录
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2019-08-20_14-51-35  2019-08-20_15-04-29  2019-08-20_15-10-56   ---相当于周三的增量备份
```

```
查看一下备份目录:
[root@mysql-server ~]# ls /xtrabackup/
2019-08-20_14-51-35  2019-08-20_15-04-29  2019-08-20_15-10-56
    全备周一             增量周二               增量周三
```
查看一下备份目录:
[root@mysql-server ~]# ls /xtrabackup/
2019-08-20_14-51-35  2019-08-20_15-04-29  2019-08-20_15-10-56
    全备周一             增量周二               增量周三

					恢复
增量备份恢复流程
1. 停止数据库
2. 清理环境
3. 依次重演回滚redo log－－> 恢复数据
4. 修改权限
5. 启动数据库
6. binlog恢复
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
依次重演回滚redo log:
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35
周二 ---  周三
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-04-29
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-10-56/
恢复数据:
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_14-51-35/
修改权限
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld
登陆上去看一下: 

恢复

1. 停止数据库
2. 清理环境
3. 依次重演回滚redo log－－> 恢复数据
4. 修改权限
5. 启动数据库
6. binlog恢复

innobackupex --user --password --incremental /opt/intr
--incremental-basedir=/opt/full/timedate

[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
依次重演回滚redo log:
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35
周二 ---  周三
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-04-29
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-10-56/
恢复数据:
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_14-51-35/
修改权限
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld
登陆上去看一下: 

差异备份流程

清理备份环境

[root@mysql-server ~]# rm -rf /xtrabackup/*
登陆数据库，准备环境
mysql> create database testdb;
mysql> delete from testdb.t1;
mysql> insert into testdb.t1 values(1);    #插入数据1，模拟周一
mysql> select * from testdb.t1;
+------+
| id   |
+------+
|    1 |
+------+
mysql> \q
查看时间:
[root@mysql-server ~]# date
Tue Aug 20 15:39:59 CST 2019
1、完整备份：周一
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup
2、差异备份：周二　——　周三
语法: # innobackupex --user=root --password=123 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录（周一）
3.修改时间：
[root@mysql-server ~]# date 08211543
Wed Aug 21 15:43:00 CST 2019
4.在登陆mysql：
mysql> insert into testdb.t1 values(2);  #插入数据2，模拟周二
差异备份周二的
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/  #备份目录基于周一的备份
5.再次登陆mysql
mysql> insert into testdb.t1 values(3);  #插入数据，模拟周三
6.在次修改时间
[root@mysql-server ~]# date 08221550
Thu Aug 22 15:50:00 CST 2019
7.再次差异备份
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/  #还是基于周一的备份
8.延伸到周四
mysql> insert into testdb.t1 values(4);
9.修改时间
[root@mysql-server ~]# date 08231553
Fri Aug 23 15:53:00 CST 2019
10.差异备份周四
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/   #还是基于周一的备份
11.查看一下备份目录
[root@mysql-server ~]# ls /xtrabackup/
2019-08-20_15-42-02  2019-08-21_15-46-53  2019-08-22_15-51-15  2019-08-23_15-53-28
   周一                   周二                   周三               周四

恢复流程

差异备份恢复流程
1. 停止数据库
2. 清理环境
3. 重演回滚redo log（周一，某次差异）－－> 恢复数据
4. 修改权限
5. 启动数据库
6. binlog恢复
停止数据库
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
1.恢复全量的redo log
语法: # innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/
2.恢复差异的redo log
语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）--incremental-dir=/xtrabacku/某个差异备份
这里我们恢复周三的差异备份
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/ --incremental-dir=/xtrabackup/2019-08-22_15-51-15/   #我们恢复周三的差异备份
3.恢复数据
语法:# innobackupex --copy-back /xtrabackup/完全备份目录（周一）
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_15-42-02/
修改权限：
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld