Mysql数据库的备份与恢复

数据备份的重要性

在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
程序错误
人为错误（常事）
计算机失败
磁盘失败
灾难

数据库备份的分类

物理备份
对数据库操作系统的物理文件（如数据文件，日志文件等）的本份
物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）
冷备份：实在关闭数据库的时候进行的
热备份：数据库处于运行状态，这种备份方法依赖于数据库的日志文件
逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份
从数据库的备份策略角度，备份可分为
完全备份：每次对数据进行完整的备份
差异备份：备份那些自从上次完全备份之后被修改的文件
增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
增量备份，第一点在完全备份基础之上，a,b,c增量修改之后会就b+,c+增量备份。在这两个文件增量备份之后，再备份修改，b+b+

完全备份

完全备份是对整个数据库的备份，数据库结构和文件结构的备份
完全备份保存的是备份完成时刻的数据库
完全备份是增量备份的基础
完全备份的优缺点
优点：备份与恢复操作简单方便
缺点：数据存在大量的重复，占用大量的备份空间，备份与恢复时间长

mysqldump备份库

Mysql数据库的备份可以采用多种方式
直接打包数据库文件夹，如/usr/local/mysal/data
使用专用备份工具mysqldump
mysqldump命令
mysql自带的备份工具，相当方便对mysql进行备份
通过命令工具可以将指定额库，表或全部的库导出为SQL脚本，在需要恢复时可进行数据恢复

mysql增量备份

特点
没有重复数据，备份量不大，时间短
恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
可以通过mysql提供的二进制日志（binary logs) 间接实现增量备份

mysql数据库增量恢复

一般恢复
添加数据--进行完全备份--录入新的数据--进行增量备份--模拟故障--恢复操作

基于位置恢复
就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复

基于时间点恢复
使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式

我们先创建一个数据库和表格来做实验

mysql> create database shcool; #创建shcool库Query OK, 1 row affected (0.51 sec)mysql> use shcool; #进入shcool库中Database changedmysql> create table info (  #创建info表    -> id int(4) not null primary key auto_increment,    -> name varchar(10) not null,    -> score decimal(4,1) not null);Query OK, 0 rows affected (0.21 sec)mysql> desc info; #查看表结构+-------+--------------+------+-----+---------+----------------+| Field | Type         | Null | Key | Default | Extra          |+-------+--------------+------+-----+---------+----------------+| id    | int(4)       | NO   | PRI | NULL    | auto_increment || name  | varchar(10)  | NO   |     | NULL    |                || score | decimal(4,1) | NO   |     | NULL    |                |+-------+--------------+------+-----+---------+----------------+3 rows in set (0.01 sec)mysql> insert into info (name,score) values ('stu01',88),('stu02',77); #插入数据Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from info;+----+-------+-------+| id | name  | score |+----+-------+-------+|  1 | stu01 |  88.0 ||  2 | stu02 |  77.0 |+----+-------+-------+2 rows in set (0.00 sec)mysql> select * from info limit 1; #查看数据表的开头的一行+----+-------+-------+| id | name  | score |+----+-------+-------+|  1 | stu01 |  88.0 |+----+-------+-------+1 row in set (0.00 sec)mysql> quit #退出来

完整备份

物理备份

[root@localhost ~]# cd /usr/local/mysql/[root@localhost mysql]# lsbin      COPYING-test  docs     lib  mysqld.pid  mysql.sock.lock  README       share          usrCOPYING  data          include  man  mysql.sock  mysql-test       README-test  support-files[root@localhost mysql]# cd data/[root@localhost data]# lsauto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  performance_schema  shcool  sys[root@localhost data]# cd shcool/[root@localhost shcool]# cd ../../[root@localhost mysql]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/tar: Removing leading `/' from member names/usr/local/mysql/data//usr/local/mysql/data/ibdata1/usr/local/mysql/data/ib_logfile1[root@localhost mysql]# ls /opt/mysql-2019-11-24.tar.xz  mysql-5.7.20  rh

逻辑备份

[root@localhost mysql]# cd data/[root@localhost data]# mysqldump -u root -p shcool > /opt/shcool.sqlEnter password: [root@localhost data]# ls /opt/mysql-2019-11-24.tar.xz  mysql-5.7.20  rh  shcool.sql

对多个数据库进行完整性备份

[root@localhost opt]# mysqldump -u root -p123123 --databases shcool mysql > /opt/db_shcool_mysql.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost opt]# lsdb_shcool_mysql.sql  mysql-2019-11-24.tar.xz  mysql-5.7.20  rh  shcool.sql

对所有数据库进行备份

[root@localhost opt]# mysqldump -u root -p123123 --opt --all-databases > /opt/all.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost opt]# lsall.sql  db_shcool_mysql.sql  mysql-2019-11-24.tar.xz  mysql-5.7.20  rh  shcool.sql

针对某一张表进行备份

[root@localhost opt]# mysqldump -u root -p123123 shcool info > /opt/shcool_info.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.

对某张表的结构进行备份

[root@localhost opt]# mysqldump -u root -p123123 -d shcool info > /opt/shcool_info_secret.sql

完整备份恢复

mysql> use shcool;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> drop table info;Query OK, 0 rows affected (0.03 sec)mysql> source /opt/shcool.sql;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> show tables;+------------------+| Tables_in_shcool |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> select * from info;+----+-------+-------+| id | name  | score |+----+-------+-------+|  1 | stu01 |  88.0 ||  2 | stu02 |  77.0 |+----+-------+-------+2 rows in set (0.00 sec)

在控制台下恢复数据库

mysql> drop table info;Query OK, 0 rows affected (0.01 sec)mysql> show tables;Empty set (0.00 sec)mysql> quit[root@localhost opt]# mysql -u root -p123123 shcool < /opt/shcool.sql mysql: [Warning] Using a password on the command line interface can be insecure.

进数据库去验证一下

[root@localhost opt]# mysql -u root -p123123mysql> use shcool;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+------------------+| Tables_in_shcool |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> select * from info;+----+-------+-------+| id | name  | score |+----+-------+-------+|  1 | stu01 |  88.0 ||  2 | stu02 |  77.0 |+----+-------+-------+2 rows in set (0.00 sec)

增量备份和恢复

我们先把原来的删掉

[root@localhost opt]# rm -rf *.sql[root@localhost opt]# lsmysql-2019-11-24.tar.xz  mysql-5.7.20  rh

先要开启二进制日志文件

[root@localhost opt]# vim /etc/my.cnf

我们在[mysqld]区域中加上这一行开启二进制日志文件

log-bin=mysql-bin[root@localhost opt]# systemctl restart mysqld.service [root@localhost opt]# cd /usr/local/mysql/data/[root@localhost data]# lsauto.cnf        ibdata1      ib_logfile1  mysql             mysql-bin.index     shcoolib_buffer_pool  ib_logfile0  ibtmp1       mysql-bin.000001  performance_schema  sys[root@localhost data]# mysqldump -u root -p123123 shcool > /opt/shcool.sql #增量备份是建立在完整备份的基础上mysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost data]# ls /opt/mysql-2019-11-24.tar.xz  mysql-5.7.20  rh  shcool.sql[root@localhost data]# mysqladmin -uroot -p123123 flush-logs  #产生增量备份的日志文件mysqladmin: [Warning] Using a password on the command line interface can be insecure.[root@localhost data]# ls #02就是我们的二进制日志文件，你接下来的操作会生成到02当中auto.cnf        ibdata1      ib_logfile1  mysql             mysql-bin.000002  performance_schema  sysib_buffer_pool  ib_logfile0  ibtmp1       mysql-bin.000001  mysql-bin.index   shcool[root@localhost data]# mysql -u root -p123123mysql> use shcool;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from info;+----+-------+-------+| id | name  | score |+----+-------+-------+|  1 | stu01 |  88.0 ||  2 | stu02 |  77.0 |+----+-------+-------+2 rows in set (0.00 sec)mysql> insert into info (name,score) values ('test01',66); #正常插入数据Query OK, 1 row affected (0.01 sec)mysql> delete from info where name='stu01'; #这是一个误操作，我们不小心删了一行数据Query OK, 1 row affected (0.01 sec)mysql> insert into info (name,score) values ('test02',99); #正常插入数据Query OK, 1 row affected (0.00 sec)mysql> select * from info;+----+--------+-------+| id | name   | score |+----+--------+-------+|  2 | stu02  |  77.0 ||  3 | test01 |  66.0 ||  4 | test02 |  99.0 |+----+--------+-------+3 rows in set (0.00 sec)

我们想恢复stu01数据，它正好在两条正常的操作语句中间

[root@localhost data]# mysqladmin -uroot -p123123 flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure.[root@localhost data]# ls #我们刚才所有的操作都在02中包括误操作auto.cnf        ibdata1      ib_logfile1  mysql             mysql-bin.000002  mysql-bin.index     shcoolib_buffer_pool  ib_logfile0  ibtmp1       mysql-bin.000001  mysql-bin.000003  performance_schema  sys[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt要用64解码器输出，按行进行读取，显示出来生成到bak.txt文件中[root@localhost data]# cd /opt/[root@localhost opt]# lsbak.txt  mysql-2019-11-24.tar.xz  mysql-5.7.20  rh  shcool.sql以时间点进行恢复

去日志文件中找到删除的那条语句，把时间写下来

191124 19:18:21    --stop-datetime 代表从日志文件头部操作开始到这个时间点，后面不在执行操作191124 19:18:33    --start-datatime 代表从这个时间点开始继续进行操作

我们进数据库先不进行时间点的恢复，我们尝试一下增量备份的恢复

[root@localhost opt]# mysql -u root -p123123mysql> drop table info;Query OK, 0 rows affected (0.02 sec)mysql> select * from info;ERROR 1146 (42S02): Table 'shcool.info' doesn't existmysql> source /opt/shcool.sql;mysql> show tables;+------------------+| Tables_in_shcool |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> select * from info;+----+-------+-------+| id | name  | score |+----+-------+-------+|  1 | stu01 |  88.0 ||  2 | stu02 |  77.0 |+----+-------+-------+2 rows in set (0.00 sec)mysql> quitBye

第一个时间点恢复，我们那个时间点有一个插入了一行数据

[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2019-11-24 19:18:21' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123123

我们去数据库验证一下

[root@localhost opt]# mysql -u root -p123123mysql> use shcool;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from info;+----+--------+-------+| id | name   | score |+----+--------+-------+|  1 | stu01  |  88.0 ||  2 | stu02  |  77.0 ||  3 | test01 |  66.0 |+----+--------+-------+3 rows in set (0.00 sec)mysql> quit

第二个时间点恢复，我们等于跳过我们之前的误操作

[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2019-11-24 19:18:33' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123123

我们再去数据库验证一下

[root@localhost opt]# mysql -u root -p123123mysql> use shcool;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from info;+----+--------+-------+| id | name   | score |+----+--------+-------+|  1 | stu01  |  88.0 ||  2 | stu02  |  77.0 ||  3 | test01 |  66.0 ||  4 | test02 |  99.0 |+----+--------+-------+4 rows in set (0.00 sec)

按照at位置恢复

先进行误操作把test的删掉

mysql> delete from info where name='test01';Query OK, 1 row affected (0.00 sec)mysql> delete from info where name='test02';Query OK, 1 row affected (0.00 sec)mysql> select * from info;+----+-------+-------+| id | name  | score |+----+-------+-------+|  1 | stu01 |  88.0 ||  2 | stu02 |  77.0 |+----+-------+-------+2 rows in set (0.00 sec)[root@localhost opt]# cd /opt/[root@localhost opt]# lsbak.txt  mysql-2019-11-24.tar.xz  mysql-5.7.20  rh  shcool.sql[root@localhost opt]# vim bak.txt at 568  --stop-position   #跟前面一样，568是从日志头部到568结束at 672  --start-position  #从672开始到结束

568位置恢复

[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='568' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123123mysql: [Warning] Using a password on the command line interface can be insecure.

我们去数据库验证一下

[root@localhost opt]# mysql -u root -p123123mysql> use shcool;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from info;+----+--------+-------+| id | name   | score |+----+--------+-------+|  1 | stu01  |  88.0 ||  2 | stu02  |  77.0 ||  3 | test01 |  66.0 |+----+--------+-------+3 rows in set (0.00 sec)mysql> quitBye

672位置恢复，就跳过了我们之前的误操作

[root@localhost opt]# mysqlbinlog --no-defaults --start-position='672' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123123

再去数据库验证一下

[root@localhost opt]# mysql -u root -p123123mysql> use shcool;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from info;+----+--------+-------+| id | name   | score |+----+--------+-------+|  1 | stu01  |  88.0 ||  2 | stu02  |  77.0 ||  3 | test01 |  66.0 ||  4 | test02 |  99.0 |+----+--------+-------+4 rows in set (0.00 sec)

以上就是我们全部的内容了，谢谢收看