Msyql 逻辑备份与恢复

数据库备份与恢复

数据库的备份是重要的，但是数据的恢复更为重要

1. 逻辑备份：备份DDL DML DCL语句，适用于小型数据库，效率相对低下
   常用命令 ： mysqldump、mydumer
   使用方法：mysqldump -h 服务器 -u 用户名 -p 密码 数据库名 > 备份数据库名称
2. 物理备份：直接复制数据库文件，使用于大型数据库环境，效率相对高
   percona-xtrabackup 物理备份+binlog
   开源免费并支持mysql数据库热备份的软件，它能对InnoDB和xtraDB存储引擎的数据库非阻塞地备份，无需暂停服务备份mysql

xtrabackup、inbackup、cp、tar、lvm snapshot

3. 数据库的备份模式

• 完全备份：在特定的点备份完整的数据
• 增量备份：基于前一天的数据进行增量备份，增量备份需要注意恢复的顺序
• 差异备份：备份上一次的完全备份后发生变化的所有文件、差异备份指在一次全备份后到进行差异备份的这段时间内对哪些增加或修改文件的备份，在恢复时只需要对第一次全量备份和最后一次差异备份进行恢复

本地备份、完整备份

mysqldump  -uroot   -p'xxx'
--all-detabases
-- single-transaction 
--master-data=1
--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

数据的恢复

1. 在备份好的数据库文件不写入binlog日志
2. 增量的恢复，在binlog 恢复

数据库逻辑备份案例-误删除表
环境介绍：安装mysql，这里我安装的是mysql 5.7版本

[root@localhost ~]# mysql -uroot -pchen
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22-log MySQL Community Server (GPL)

注意：mysql5.7默认是不开启binlog日志，现在开启binglog日志
修改/etc/my.cnf

[root@mysql-server ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data/
socket = /tmp/mysql.sock
port = 3306
pid-file = /tmp/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=/var/lib/mysql/mysql-bin       //添加此行
server-id=12			 //添加此行

创建目录，修改属主和属组,重新启动服务

[root@mysql-server ~]# mkdir  /var/lib/mysql/
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql/
[root@mysql-server ~]# service mysqld restart
[root@mysql-server ~]# ls /var/lib/mysql/
mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index

创建一个库，库里面创建一张表,插入数据

mysql> create database chens;
mysql> use chens;
mysql> create  table chens (id int,name varchar(100));
mysql> insert into chens(id,name)values (1,'tom'),(2,'jerry');
mysql> select * from chens;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
+------+-------+
2 rows in set (0.00 sec)

创建存放备份目录

[root@mysql-server ~]# mkdir /backup/mysql/  -p

进行一次全备份

[root@mysql-server ~]# mysqldump -uroot -p'chen' \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql
[root@mysql-server mysql]# 
[root@mysql-server mysql]# ls
2018-09-2411-mysql-all.sql

进入mysql再次插入数据

mysql> insert into chens values (3,'laowang'),(4,'zhangshan'),(5,'csf');
mysql> select * from chens;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | tom       |
|    2 | jerry     |
|    3 | laowang   |
|    4 | zhangshan |
|    5 | csf       |
+------+-----------+
5 rows in set (0.00 sec)

模拟故障，先删除一点数据，在删除这个库

mysql> delete from chens where id=2;
mysql> drop database chens;

恢复操作
在导入全备份时有很多的执行语句，会记录到binlog里面，以下操作就是临时关闭 binlog

[root@mysql-server ~]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2018-09-2411-mysql-all.sql 

恢复全备份数据

[root@mysql-server ~]# mysql -uroot -p'chen' < /backup/mysql/2018-09-2411-mysql-all.sql 
mysql> select * from chens;           //数据已恢复，但是不全
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
+------+-------+
2 rows in set (0.01 sec)                       

通过mysqlbinlog 命令导出关于数据库 chens 的sql语句

[root@mysql-server ~]# sed -n '22p' /backup/mysql/2018-09-2411-mysql-all.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;
//表示在备份完之后记录在mysql-bin.000004 里面，从154这个位置开始

先进入binlog的位置，在执行操作

[root@mysql-server ~]# cd /var/lib/mysql/
[root@mysql-server mysql]# mysqlbinlog mysql-bin.000004

//针对全量备份后仅产生1个binlog文件的方式

[root@mysql-server mysql]# mysqlbinlog --start-position=154  mysql-bin.000004
//起始的位置154 
# at 4
#180924 11:38:53 server id 12  end_log_pos 123 CRC32 0x88f2e617 	Start: binlog v 4, server v 5.7.22-log created 180924 11:38:53
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
TVyoWw8MAAAAdwAAAHsAAAABAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ARfm8og=
'/*!*/;
# at 154
#180924 12:05:17 server id 12  end_log_pos 219 CRC32 0xfd89c3bf 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
//从文件中可以看出 位置从4  直接到 154

做转码，在后面接参数

[root@mysql-server mysql]# mysqlbinlog --start-position=154 --base64-output="decode-rows" -v mysql-bin.000004|grep INSERT    //可以过滤出insert 相关的语句
### INSERT INTO `chens`.`chens`
### INSERT INTO `chens`.`chens`
### INSERT INTO `chens`.`chens`

生成一个sql文件，导入到db.sql

[root@mysql-server mysql]# mysqlbinlog --start-position=154 --base64-output="decode-rows" -v mysql-bin.000004 > db.sql
//这只是一个文件

编辑这个文件，在里面搜索drop 、 DELETE，建议先打开行号，根据行号大小记录at 的先后位置，位置是顺序由小到大

[root@mysql-server mysql]# vim db.sql
 51 # at 637            //记录这个位置
 52 #180924 12:10:07 server id 12  end_log_pos 683 CRC32 0x05b85d0a         Delete_rows: table id     111 flags: STMT_END_F
 53 ### DELETE FROM `chens`.`chens`
 54 ### WHERE
 55 ###   @1=2
 56 ###   @2='jerry'
 57 # at 683          //记录这个位置
 58 #180924 12:10:07 server id 12  end_log_pos 714 CRC32 0x420255ab         Xid = 502
 59 COMMIT/*!*/;

 60 # at 714         //记录drop前一个位置
 61 #180924 12:10:33 server id 12  end_log_pos 779 CRC32 0x3d52653f         Anonymous_GTID  last_    committed=2        sequence_number=3       rbr_only=no
 62 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 63 # at 779         //注意这个是drop的位置
 64 #180924 12:10:33 server id 12  end_log_pos 874 CRC32 0x7b99f68c         Query   thread_id=4         exec_time=0     error_code=0
 65 SET TIMESTAMP=1537762233/*!*/;
 66 drop database chens
 67 /*!*/;
 68 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added 
.....此处省略

恢复数据

[root@mysql-server mysql]# mysqlbinlog --start-position=154 --stop-position=637 mysql-bin.000004|mysql -uroot -p'chen'
//注意起始位置，和结束位置，都是直接跳过了DELETE 和drop 语句  
[root@mysql-server mysql]# mysqlbinlog --start-position=683 --stop-position=714  mysql-bin.000004|mysql -uroot -p'chen'
mysql> select * from chens;       //数据已恢复
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | tom       |
|    2 | jerry     |
|    3 | laowang   |
|    4 | zhangshan |
|    5 | csf       |
+------+-----------+
5 rows in set (0.00 sec)