Msyql逻辑备份与恢复

数据库备份与恢复

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

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

xtrabackup、inbackup、cp、tar、lvm snapshot

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

本地备份、完整备份

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

数据的恢复

在备份好的数据库文件不写入binlog日志增量的恢复，在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)