MySQL数据库备份与恢复

一、MySQL数据库完全备份与恢复

（一）数据库备份概述
1.数据备份的重要性
2.造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难（如火灾、地震）和盗窃
3.数据库备份类型

一、按照数据库的运行状态分类：
(1)热备：在数据库运行时，直接进行备份，对运行的数据库没有影响
(2)冷备：在数据库停止运行的时候进行备份，这种备份方式最为简单，只需要拷贝数据库物理文件即可。
(3)温备：同样是在数据库运行的时候进行备份的，但对当前数据库的操作会产生影响。（只可以读操作，不可以写操作）
数据库热备与冷备的优缺点：
热备份的优点：
1.可在表空间或数据文件级备份，备份时间短。
2.备份时数据库依然可以使用。
3.可达到秒级恢复，能够恢复到某一个时间点上。
4.恢复的速度很快，在大多数情况下在数据库工作时就可恢复。
5.几乎所有的数据库实体都可以进行恢复。
热备份的缺点:
1.尽量不要出错，否则后果会很严重。
2.如果热备份不成功，所得结果不可用于时间点的数据恢复。
3.维护的工作比较困难。
冷备份的优点：
1.是非常快速的备份方法，因为只需要拷贝文件即可
2.容易归档，容易恢复到某个时间点上(只需将文件再拷贝回去即可)
3.能与归档方法相结合，作数据库（最新状态）的恢复。
4.容易维护，且比较安全。
冷备份的缺点：
1.单独使用时，只能提供到"某一时间点的上"的恢复。
2.再实施备份的全过程中，数据库必须是关闭状态。
3.不能按表或按用户恢复。

注意：
冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的。而且在恢复后一定要把数据库文件的属组和属主改为mysql

二、按照备份方式分类：
物理备份：
直接复制数据文件进行的备份
优点：不需要其他的工具，直接复制就好，恢复直接复制备份文件即可
缺点：与存储引擎有关，跨平台能力较弱
逻辑备份：
从数据库中导出数据另存而进行的备份
优点：能使用编辑器处理，恢复简单，能基于网络恢复，有助于避免数据损坏
缺点：备份文件较大，备份较慢，无法保证浮点数的精度，使用逻辑备份数据恢复后，还需要手动重建索引，十分消耗cpu资源。

三、按照业务划分
(1)完全备份
即对整个数据库的数据和数据结构进行备份。
这种备份方式的好处：
就是很直观容易被人理解。而且当发生数据丢失的灾难时，只要用灾难之前的备份文件，就可以恢复丢失的数据。
不足之处：
首先由于每天都对系统进行完全备份,因此在 备份数据中有大量是重复的。这些重复的数据占用了大量的空间， 这对用户来说就意味着增加成本；其次，由于需要备份的数据量相当大，因此备份所需时间较长。对于那些业务繁忙，备份窗口时间有限的单位来说，选择这种备份策略无疑是不明智的。
(2)增量备份
每次备份的数据只是相当于上一次备份后增加的和修改过的数据。
这种备份的优点很明显：没有重复的备份数据，即节省空间，又缩短了备份时间。
但它的缺点在于当发生灾难时，恢复数据比较麻烦。
举例来说，如果系统在星期四的早晨发生故障，丢失大批数据，
那么现在就需要将系统恢复到星期三晚上的状态。
这时管理员需要首先找出星期一的那份完全备份数据进行系统恢复，
然后，再找出星期二的数据来恢复星期二的数据，然后在找出星期三的数据来恢复星期三的数据。
很明显这比第一种策略要麻烦得多。
另外这种备份可靠 性也差。
在这种备份下，各备份数据间的关系就象链子一样，一环套一环
其中任何一个备份数据出了问题都会导致整条链子脱节。
(3)差异备份
每次备份的数据是相对于上一次全备份之后新增加的和修改过的数据。管理员先在星期一进行一次系统完全备份；然后在接下来的几天里，管理员再将当天所有与星期一不同的数据（新的或经改动的）备份到磁带上。
举例来说，在星期一，网络管理员按惯例进行系统完全备份；
在星期二，假设系统内只多了一个资产清单，于是管理员只需将 这份资产清单一并备份下来即可；
在星期三，系统内又多了一份产品目录，于是管理员 不仅要将这份目录
还要连同星期二的那份资产清单一 并备份下来。
如果在星期四系统内有多了一张工资表
那么星期四需要备份的内容就是：工资表+产 品目录+资产清单。

4.常见的备份方法
4.1 物理冷备份
tar命令
4.2 专用备份工具
mysqldump
Mysqlhotcopy
4.3 二进制日志
4.4 第三方工具备份
Percona XtraBackup
Xtrabackup、innobackupex、xbstream

（二）MySQL数据库完全备份操作
1.物理冷备份与恢复
tar命令直接打包数据库文件夹
关闭mysql服务—创建备份数据存储路径—使用tar创建备份文件–模拟故障–恢复数据
关闭mysql服务—创建备份数据存储路径—使用tar创建备份文件
[root@www ~]# systemctl stop mysqld
[root@www ~]# netstat -anput | grep mysqld
[root@www ~]# mkdir /backup
归档时最好切换到目标目录路径
[root@www ~]# cd /usr/local/mysql/
z — gzip格式
j ---- bzip2格式
KaTeX parse error: Expected 'EOF', got '#' at position 37: …功能 [root@www ~]#̲ tar zcf /backu…(date +%F).tar.gz data/
查看备份前后的文件大小
[root@www ~]# du -sh data/
[root@www ~]# du -sh /backup/mysql_all-2019-06-21.tar.gz
模拟故障—将数据全部迁移，查看是否能够启动
[root@www ~]# mkdir bak
[root@www ~]# mv /usr/local/mysql/data/ bak/
[root@www ~]# systemctl start mysqld
恢复数据
[root@www ~]# mkdir restore
[root@www ~]# tar xf /backup/mysql_all-2019-03-25.tar.gz -C restore/
[root@www ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
[root@www ~]# systemctl start mysqld
[root@www ~]# netstat -anput | grep mysqld
tcp6 0 0 :::3306 ::😗 LISTEN 1569/mysqld
2.mysqldump备份与恢复—常用于温备份
将指定的库、表、或全部的库导出为SQL脚本
备份mysql库的user表
[root@www ~]# mysqldump -u root -p123 mysql user >mysql-user.sql
备份数据库auth
[root@www ~]# mysqldump -u root -p123 --databases auth >auth.sql
备份所有数据库
[root@www ~]# mysqldump -u root -p123 --opt --all-databases > all-data.sql
2）恢复操作
从mysql-user.sql中导入表user到test数据库
[root@www ~]# mysql -u root -p test < mysql-user.sql
Enter password:
-e 用于指定连接mysql后执行的命令
登录数据库执行命令：查看库test的表user是否存在
[root@www ~]# mysql -u root -p -e ‘show tables from test;’
Enter password:
±---------------+
| Tables_in_test |
±---------------+
| user |
±---------------+
模拟故障：删除数据库auth
[root@www ~]# mysql -u root -p123 -e ‘drop database auth;’
查看数据库是否删除
[root@www ~]# mysql -u root -p123 -e ‘show databases;’
恢复数据库auth
[root@www ~]# mysql -u root -p123 < auth.sql
查看是否已恢复
[root@www ~]# mysql -u root -p123 -e ‘show databases;’
开启数据库二进制日志功能
编辑主配置文件
[root@www ~]# vim /usr/local/mysql/my.cnf
[mysqld]
log_bin=/usr/local/mysql/mysql-bin
重启服务
[root@www ~]# systemctl restart mysqld
查看生成的日志文件
[root@www ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 120 3月 25 14:12 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 34 3月 25 14:12 /usr/local/mysql/mysql-bin.index
刷新日志文件
[root@localhost ~]# mysql -uroot -p123 -e ‘flush logs;’
[root@www ~]# ls -l /usr/local/mysql/mysql-bin.*
一般恢复
添加数据–进行完全备份—录入新的数据—进行增量备份–模拟故障–恢复操作
mysql> create database client;
mysql> use client;
创建表user_info
mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null, 性别 char(4), 用户ID号 char(10) not null, 资费 int(10));
写入数据到表user_info
mysql> insert into user_info values(‘000006’,‘张三’,‘男’,‘016’,‘10’);
mysql> insert into user_info values(‘000007’,‘李四’,‘女’,‘017’,‘91’);
mysql> insert into user_info values(‘000008’,‘王五’,‘女’,‘018’,‘23’);
mysql> select * from client.user_info;
备份表user_info
[root@www ~]# mkdir /mysql_bak
[root@www ~]# mysqldump -uroot -p123 client user_info > /mysql_bak/client_userinfo-$(date +%F).sql
[root@www ~]# ll /mysql_bak/
总用量 4
-rw-r–r-- 1 root root 2055 3月 25 14:26 client_userinfo-2019-03-25.sql
刷新二进制日志，生成新的日志文件
[root@www ~]# mysqladmin -uroot -p123 flush-logs
[root@www ~]# ls -l /usr/local/mysql/mysql-bin.*
继续写入测试数据
mysql> use client;
mysql> insert into user_info values(‘000009’,‘赵六’,‘男’,‘019’,‘37’);
mysql> insert into user_info values(‘000009’,‘孙七’,‘男’,‘020’,‘36’);
mysql> select * from client.user_info;
刷新生成新的日志文件
[root@www ~]# mysqladmin -uroot -p123 flush-logs
[root@www ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 1252 3月 25 14:27 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 679 3月 25 14:32 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql 120 3月 25 14:32 /usr/local/mysql/mysql-bin.000003
-rw-rw---- 1 mysql mysql 102 3月 25 14:32 /usr/local/mysql/mysql-bin.index
模拟故障：删除表user_info
[root@www ~]# mysql -uroot -p -e’drop table client.user_info;’
[root@www ~]# mysql -uroot -p123 -e’select * from client.user_info;’
恢复导入完全备份
[root@www ~]# mysql -uroot -p123 client < /mysql_bak/client_userinfo-2019-03-25.sql
[root@www ~]# mysql -uroot -p123 -e’select * from client.user_info;’
±----------±-------±-------±------------±-------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
±----------±-------±-------±------------±-------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000006 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
±----------±-------±-------±------------±-------+
进行增量备份的恢复（一般恢复）
[root@www ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000003 | mysql -u root -p123
[root@www ~]# mysql -uroot -p123 -e’select * from client.user_info;’
±----------±-------±-------±------------±-------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
±----------±-------±-------±------------±-------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000006 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
| 000009 | 孙七 | 男 | 020 | 36 |
±----------±-------±-------±------------±-------+
基于位置恢复
模拟删除
指定‘停止位置’，即不恢复‘孙七’的用户数据
[root@www ~]# mysqlbinlog --no-defaults --stop-position=‘459’ /mysql_bak/mysql-bin.000002 | mysql -u root -p123
[root@www ~]# mysql -uroot -p123 -e’select * from client.user_info;’
Warning: Using a password on the command line interface can be insecure.

±----------±-------±-------±------------±-------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
±----------±-------±-------±------------±-------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000006 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
±----------±-------±-------±------------±-------+
指定起始时间进行恢复孙七
[root@www ~]# mysqlbinlog --no-defaults --start-datetime=‘2019-03-25 14:29:44’ /mysql_bak/mysql-bin.000002 | mysql -uroot -p123
[root@www ~]# mysql -uroot -p123 -e’select * from client.user_info;’
±----------±-------±-------±------------±-------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
±----------±-------±-------±------------±-------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000006 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 孙七 | 男 | 020 | 36 |
±----------±-------±-------±------------±-------+
扩展内容：基于时间点恢复，如何只恢复王五、赵六的记录
[root@www ~]# mysql -uroot -p -e’drop table client.user_info;’
[root@www ~]# mysql -uroot -p -e’create table client.user_info(身份证 char(20) not null,姓名 char(20) not null, 性别 char(4), 用户ID号 char(10) not null, 资费 int(10));’
[root@www ~]# mysqlbinlog --no-defaults --start-datetime=‘2019-06-24 11:17:28’ /usr/local/mysql/mysql-bin.000002 --stop-datetime=‘2019-06-24 11:46:05’ /usr/local/mysql/mysql-bin.000003 |mysql -uroot -p123
[root@www ~]# mysql -uroot -p123 -e’select * from client.user_info;’