Mysql数据备份和还原

Mysql数据备份和还原
一、Mysql完整备份和恢复
1、备份的作用和mysql产生故障的原因
1）备份的作用
防止发生故障损失降到最低
故障后能够快速恢复丢失数据保证数据库系统或者数据稳定运行
2）mysql故障的类型
程序错误：MySQL程序故障导致数据无法使用
人为误操作：数据库管理员误删除数据
运算错误：数据库程序逻辑故障
磁盘故障：硬盘坏道导致数据丢失
灾难不可抗力的自然灾害：地震海啸使用异地备份缓解
2、数据库备份的类型
1）冷备份
关闭数据库服务
直接复制mysql数据目录进行数据备份
2）热备份
不停止数据库服务实时动态备份数据
3）温备份
备份数据设置数据库允许读取不允许写入数据备份成为温和备份
3、备份策略
1）完整备份
一般第一次备份使用完整备份
完整备份对数据库和数据库逻辑文件以及日志统一备份
备份时间长占用磁盘空间大
2）差异备份
完整备份后执行差异备份
差异备份只备份完整备份后修改的数据
3）增量备份
做完完整备份和差异备份使用后使用
增量备份备份差异备份修改的数据
备份速度快
占用磁盘空间小
方便数据恢复
4、常见的备份工具
1）冷备份工具
tar
cp
2）专用备份工具备份
mysqldump：MySQL系统自带备份工具
mysqlhotcopy：对数据库引擎进行备份，工具使用perl语言
3）二进制日志
通过二进制日志备份工具备份数据
数据故障从二进制日志中恢复
一般增量备份使用
4）第三工具
xtrbackup：是一个二进制文件工具，只能备份innodb/xtradb数据文件
innodbackupex：使用perl语言编写的备份脚本，可以备份innodb/xtradb以及mysiam引擎
xbstream：允许将文件转换为xbstream格式进行数据备份
二、数据库的完全备份和恢复
1、冷备份
1）停止mysql服务
[root@centos01 ~]# systemctl stop mysql

2）备份mysql数据
[root@centos01 ~]# mkdir /backup
[root@centos01 ~]# tar zcf /backup/mysql_backup-$(date +%F).tar.gz /usr/local/mysql/data/

3）模拟数据丢失
[root@centos01 ~]# mv /usr/local/mysql/data/ /opt/

4）恢复丢失数据
[root@centos01 ~]# mkdir restore
[root@centos01 ~]# tar zxf /backup/mysql_backup-2020-06-22.tar.gz -C ./restore/
[root@centos01 ~]# mv ./restore/usr/local/mysql/data/ /usr/local/mysql/



5）启动mysql服务
[root@centos01 ~]# systemctl start mysqld

2、使用mysqldump备份数据
1）备份benet数据库中的student表
[root@centos01 ~]# mysqldump -uroot -p benet student > benet_student_table.sql
mysql> mysql> create table benet.student (姓名 char(6),性别 char(3),电话 char(11),primary key(电话));
[root@centos01 ~]# cat benet_student_table.sql

2）备份数据库中的数据，备份benet数据库
[root@centos01 ~]# mysqldump -uroot -p --database benet > benet_database.sql

3）备份所有的数据库
[root@centos01 ~]# mysqldump -uroot -p --opt --all-databases > all_databases

3、恢复备份的数据
1）恢复benet数据库中的student表到accp数据库中
[root@centos01 ~]# mysql -uroot -ppwd@123
mysql> create database accp;
[root@centos01 ~]# mysql -uroot -p accp < ./benet_student_table.sql
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘select * from accp.student;’
2）恢复备份的数据库
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘create database benet;’
[root@centos01 ~]# mysql -uroot -p benet < ./benet_database.sql
三、配置增量备份恢复
1、增量备份的作用和优势
1）增量备份的作用
方便进行数据备份和恢复
2）增量备份的优势
备份和恢复数据速度快
恢复数据可靠性强
恢复数据不容易旧数据覆盖新数据
2、配置二进制日志
1）开启mysql二进制日志功能
[root@centos01 ~]# vim /etc/my.cnf
49 log-bin=mysql-bin
2）二进制日志保存的默认位置
/usr/local/mysql/data/
3）查看二进制日志
[root@centos01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000005
4）二进制日志切割，生成新的二进制日志
[root@centos01 ~]# mysqladmin -uroot -ppwd@123 flush-logs
3、使用二进制日志恢复误删除数据
1）备份二进制日志
[root@centos01 ~]# cp /usr/local/mysql/data/mysql-bin.000005 ./
2）模拟删除benet数据库所有数据丢失
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘drop database benet;’
3）从备份的二进制日志中恢复误删除的数据
[root@centos01 ~]# mysqlbinlog --no-defaults mysql-bin.000004 | mysql -uroot -ppwd@123
4）查看恢复的数据
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘select * from benet.student;’
4、使用二进制日志基于位置的恢复
1）模拟数据丢失
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘drop database benet;’
2）恢复到备份二benet数据库状态
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘create database benet;’
[root@centos01 ~]# mysql -uroot -p benet < ./benet_database.sql
3）通过备份的二进制日志恢复最新的数据
[root@centos01 ~]# mysqlbinlog --stop-position=‘1289’ ./mysql-bin.000004 | mysql -uroot -ppwd@123
4）查看恢复的数据
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘select * from benet.student;’
5）从特定的二进制日志开始恢复
[root@centos01 ~]# mysqlbinlog --start-position=‘1176’ ./mysql-bin.000004 | mysql -uroot -ppwd@123
首次插入数据
创建benet数据库student表

录入数据

首次录入数据进行完全备份

录入新的数据并进行增量备份

切割日志，生成新的二进制日志


备份二进制日志到root

模拟删除benet数据库所有数据丢失

先恢复完全备份


恢复增量备份

查看恢复的数据

使用二进制日志基于位置的恢复
模拟数据丢失

恢复到备份二benet数据库状态


通过备份的二进制日志恢复最新的数据


查看恢复的数据

从特定的二进制日志开始恢复
[root@centos01 ~]# mysqlbinlog --start-position=‘1176’ ./mysql-bin.000004 | mysql -uroot -ppwd@123

5、基于时间点恢复数据
1）基于开始恢复到日志结束时间
[root@centos01 ~]# mysqlbinlog --start-datetime=‘2020-06-23 15:38:21’ ./mysql-bin.000004 | mysql -uroot -ppwd@123

2）基于结束时间点恢复
[root@centos01 ~]# mysqlbinlog --stop-datetime=‘2020-06-23 15:38:35’ ./mysql-bin.000004 | mysql -uroot -ppwd@123

3）基于开始时间和结束时间之间的数据进行恢复
[root@centos01 ~]# mysqlbinlog --start-datetime=‘2020-06-23 15:38:21’ --stop-datetime=‘2020-06-23 15:38:35’ ./mysql-bin.000004 | mysql -uroot -ppwd@123