目录
一、 数据备份的重要性二、 数据库备份的分类2.1 从物理与逻辑的角度2.1.1物理备份2.1.2 逻辑备份2.2 从数据库的备份策略角度2.2.1 完全备份2.2.2 差异备份2.2.3 增量备份2.3 常见的备份方法三、MySQL完全备份3.1 完全备份概述3.2 完全备份的优缺点3.3 数据库完全备份操作3.3.1 物理冷备份与恢复(tar命令直接打包数据库文件夹)3.3.2 mysqldump备份与恢复四、数据库增量备份恢复4.1 什么是MySQL增量备份?4.2 使用mysqldump命令进行完全备份存在的问题4.3 增量备份有什么优缺点?4.4 如何实现MySQL增量备份?五、数据库增量恢复5.1 一般恢复5.2 基于位置的恢复5.2.1 恢复数据到指定位置5.2.2 从指定的位置开始恢复数据5.3 基于时间点的恢复六、MySQL企业备份案例6.1 一般恢复(丢什么数据,找什么数据)6.2 进行一次完全备份6.3 继续录入新的内容并进行增量备份6.4 模拟误操作删除 user_info表6.5 恢复操作6.6 基于位置恢复一、 数据备份的重要性
■在生产环境中,数据的安全性是至关重要的
■任何数据的丢失都可能产生严重的后果
■造成数据丢失的原因
●程序错误
●人为操作错误
●运算错误
●磁盘故障
●灾难(如火灾、地震)和偷窃
二、 数据库备份的分类
2.1 从物理与逻辑的角度
2.1.1物理备份
■对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
■物理备份又可以分为脱机备份(冷备份)和联机备份(热备份)
●冷备份:是在关闭数据库的时候进行的
●热备份:数据库处于运行状态,这种备份方法依赖于数据库的日志文件,无需关机,无需关闭业务,就可直接识别
2.1.2 逻辑备份
■对数据库逻辑组件(如表等数据库对象)的备份
■逻辑备份是对某个表、数据库备份
■逻辑上的一张表映射出物理层面的三个文件:
■表的结构文件frm,表的数据文件MYD,表的索引文件MYI
2.2 从数据库的备份策略角度
2.2.1 完全备份
■每次对数据进行完整的备份
■完全备份:会把服务器内的所有数据全部备份,每次都这么执行
●优点:安全
●缺点:数据备份冗余,占用磁盘空间
2.2.2 差异备份
■备份那些自从上次完全备份之后被修改过的文件
■前提是必须要备份一次完全备份,接下来每次只备份基于完全备份的基础上被修改过的文件
2.2.3 增量备份
■只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
●相同点:基础都是完全备份
●不同点:差异备份只参考基础的完全备份,
■增量备份是参考上一次的数据备份与当前状态进行对比,备份被修改的文件
■增量备份效率更高,空间利用率很高,但是在安全性能不高
2.3 常见的备份方法
■物理冷备份
●tar命令
■专用备份工具
●mysqldump
●Mysqlhotcopy
■二进制日志
■第三方工具备份
●Percona XtraBackup
●Xtrabackup、innobackupex、xbstream
三、MySQL完全备份
3.1 完全备份概述
■完全备份是对整个数据库的备份、数据库结构和文件结构的备份
■完全备份保存的是备份完成时刻的数据库
■完全备份是增量备份的基础
3.2 完全备份的优缺点
■优点
●备份与恢复操作简单方便
■缺点
●数据存在大量的重复
●占用大量的备份空间
●备份与恢复空间长
3.3 数据库完全备份操作
3.3.1 物理冷备份与恢复(tar命令直接打包数据库文件夹)
■备份数据库
[root@localhost ~]# systemctl stop mysqld'//关闭数据库'[root@localhost ~]# mkdir /backup '//在根下新建个backup目录'[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ '///usr/local/mysql/data备份压缩'[root@localhost ~]# cd /backup/'//进入/backup/ '[root@localhost backup]# ll '//查看备份'total 1276-rw-r--r-- 1 root root 1304034 Sep 13 15:12 mysql_all--09-13.tar.gz
■模拟故障
[root@localhost backup]# mkdir /bak '//在根下新建bak目录'[root@localhost backup]# mv /usr/local/mysql/data/ /bak/'//将目录data数据移动到bak 里面去'[root@localhost backup]# cd /usr/local/mysql/'//仅mysql目录查看data'[root@localhost mysql]# ll'//查看后data目录被移走 ,模拟故障成功'total 64drwxr-xr-x 2 mysql mysql 4096 Sep 8 15:23 bin-rw-r--r-- 1 mysql mysql 17987 Sep 13 COPYING-rw-r--r-- 1 mysql mysql 17987 Sep 13 COPYING-testdrwxr-xr-x 2 mysql mysql 55 Sep 8 15:23 docsdrwxr-xr-x 3 mysql mysql 4096 Sep 8 15:23 includedrwxr-xr-x 4 mysql mysql 191 Sep 8 15:23 libdrwxr-xr-x 4 mysql mysql 30 Sep 8 15:23 mandrwxr-xr-x 10 mysql mysql 4096 Sep 8 15:23 mysql-test-rw-r--r-- 1 mysql mysql 2478 Sep 13 README-rw-r--r-- 1 mysql mysql 2478 Sep 13 README-testdrwxr-xr-x 28 mysql mysql 4096 Sep 8 15:23 sharedrwxr-xr-x 2 mysql mysql 90 Sep 8 15:23 support-filesdrwxr-xr-x 3 mysql mysql 17 Sep 8 15:23 usr
■数据库恢复
[root@localhost mysql]# mkdir /restore'//根目录新建restore 目录'[root@localhost mysql]# tar xzvf /backup/mysql_all--09-13.tar.gz -C /restore/ '//将备份数据库解压到 /restore目录下'usr/local/mysql/data/. . . . . . . . . '//省略部分内容'[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/'//将备份数据移动到到/usr/local/mysql中'[root@localhost mysql]# systemctl start mysqld [root@localhost mysql]# systemctl status mysqld '//mysql数据库启动状态,日志文件显示正常'
3.3.2 mysqldump备份与恢复
■备份操作
●mysqldump备份需要和mysql进行数据交互,如果关闭mysql 则无法备份和恢复
●mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
●mysqldump [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名
●mysqldump [选项] --all-databases > /备份路径/备份文件名
■创建环境
[root@localhost mysql]# mysql -uroot -pEnter password: mysql> create database auth1;'//创建auth1这个数据库'Query OK, 1 row affected (0.00 sec)mysql> use auth1;'//进入auth 这个数据库'Database changedmysql> create table users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));'//创建表结构'Query OK, 0 rows affected (0.01 sec)mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456'));'//导入数据'Query OK, 1 row affected, 1 warning (0.03 sec)mysql> insert into users values('lisi', password('123456'));'//新增数据'Query OK, 1 row affected, 1 warning (0.00 sec)mysql> exit[root@localhost ~]# mysqldump -u root -p mysql user >mysql-user.sql'//备份数据路mysql中的user表 ,文件保存在当前操作目录下面'Enter password: '//输入密码'
■查看备份文件
[root@localhost ~]# mysqldump -u root -p --databases auth1 >auth1.sql'//备份auth数据库文件保存在当前操作目录下面'Enter password: [root@localhost ~]# grep -v "^--" auth1.sql | grep -v "^/" | grep -v "^$"CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth1` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `auth1`;DROP TABLE IF EXISTS `users`;CREATE TABLE `users` (`user_name` char(16) NOT NULL,`user_passwd` char(48) DEFAULT '',PRIMARY KEY (`user_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `users` WRITE;INSERT INTO `users` VALUES ('lisi','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'),('zhangsan','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');UNLOCK TABLES;
■恢复操作
●mysql [选项] [库名] [表名] < /备份路径/备份文件名
[root@localhost ~]# mysql -u root -p Enter password: mysql> create database text;'//新建数据库text ,等下导表用'Query OK, 1 row affected (0.00 sec)mysql> exitBye[root@localhost ~]# mysql -u root -p text < mysql-user.sql Enter password: [root@localhost ~]# mysql -u root -p -e 'show tables from text''//验证输出结果'Enter password: +----------------+| Tables_in_text |+----------------+| user |+----------------+'//导进去的表'
■数据库恢复
[root@localhost ~]# mysql -u root -p -e 'drop database auth1''//模拟故障删除auth数据库'Enter password: [root@localhost ~]# mysql -u root -p -e 'show databases' '//查看databases所有数据库有没有auth,发现没有了'Enter password: +--------------------+| Database |+--------------------+| information_schema || auth|| bbs|| mysql || performance_schema || school || sys|| text|+--------------------+[root@localhost ~]# mysql -u root -p < auth1.sql'//导入数据库'Enter password: [root@localhost ~]# mysql -u root -p -e 'show databases' '//查看databases所有数据库有没有auth1,发现有了'Enter password: +--------------------+| Database |+--------------------+| information_schema || auth|| auth1 || bbs|| mysql || performance_schema || school || sys|| text|+--------------------+
四、数据库增量备份恢复
4.1 什么是MySQL增量备份?
■只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
■增量备份就是备份自上一次备份之后增加或变化的文件或者内容
4.2 使用mysqldump命令进行完全备份存在的问题
■备份数据中有重复数据 ■备份时间与恢复时间长
4.3 增量备份有什么优缺点?
■优点:
●没有重复数据,效率高,空间利用率最大化
●备份量不大,时间短
■缺点:
●恢复麻烦:需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复
●安全性较低
4.4 如何实现MySQL增量备份?
■MySQL没有提供直接的增量备份方法
■可以通过 MySQL提供的二进制日志( binary logs)间接实现增量备份
■数据库二进制日志对备份的意义
●二进制日志保存了所有更新或者可能更新数据的操作。
●二进制日志在启动mysql服务器后开始记录,并在文件达到二进制日志所设置的最大值
●或者接受到flush logs命令后重新创建新的日志文件,生成二进制的文件序列,并及时把这些日志文件保存到安全的存储位置,即可完成一个时间段的增量备份
[root@localhost ~]# vim /etc/f . . . . . . . server-id = 1log-bin=/usr/local/mysql/data/mysql-bin. . . . .. . .[root@localhost ~]# systemctl restart mysqld [root@localhost ~]# ll /usr/local/mysql/data/total 122924drwxr-x--- 2 mysql mysql 54 Sep 10 16:27 authdrwxr-x--- 2 mysql mysql 54 Sep 13 16:03 auth1-rw-r----- 1 mysql mysql 56 Sep 8 15:24 fdrwxr-x--- 2 mysql mysql 20 Sep 8 15:32 bbs-rw-r----- 1 mysql mysql434 Sep 13 16:19 ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 Sep 13 16:19 ibdata1-rw-r----- 1 mysql mysql 50331648 Sep 13 16:19 ib_logfile0-rw-r----- 1 mysql mysql 50331648 Sep 8 15:24 ib_logfile1-rw-r----- 1 mysql mysql 12582912 Sep 13 16:19 ibtmp1drwxr-x--- 2 mysql mysql4096 Sep 8 15:24 mysql-rw-r----- 1 mysql mysql154 Sep 13 16:19 mysql-bin.000001'//二进制日志'-rw-r----- 1 mysql mysql 39 Sep 13 16:19 mysql-bin.indexdrwxr-x--- 2 mysql mysql8192 Sep 8 15:24 performance_schemadrwxr-x--- 2 mysql mysql 68 Sep 12 04:41 schooldrwxr-x--- 2 mysql mysql8192 Sep 8 15:24 sysdrwxr-x--- 2 mysql mysql 68 Sep 13 15:59 text
五、数据库增量恢复
5.1 一般恢复
■mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p
5.2 基于位置的恢复
5.2.1 恢复数据到指定位置
■mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
5.2.2 从指定的位置开始恢复数据
■mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
5.3 基于时间点的恢复
■从日志开头截止到某个时间点的恢复
●mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
■从某个时间点到日志结尾的恢复
●mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
■从某个时间点到某个时间点的恢复
●mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
六、MySQL企业备份案例
6.1 一般恢复(丢什么数据,找什么数据)
[root@localhost ~]# mysql -u root -pEnter password: mysql> create database client;Query OK, 1 row affected (0.00 sec)mysql> use client;Database changedmysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,姓别 char(4),用户ID号 char(10) not null, 资费 int(10));Query OK, 0 rows affected (0.01 sec)mysql> insert into user_info values('000006','张三','男','016','10');Query OK, 1 row affected (0.00 sec)mysql> insert into user_info values('000007','李四','女','017','91');Query OK, 1 row affected (0.00 sec)mysql> insert into user_info values('000008','王五','女','018','23');Query OK, 1 row affected (0.01 sec)mysql> select * from user_info;+-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 |+-----------+--------+--------+-------------+--------+3 rows in set (0.00 sec)mysql> show master logs;'//查看当前数据库binlog文件'+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 |1492 |+------------------+-----------+1 row in set (0.00 sec)
6.2 进行一次完全备份
mysql> flush tables with read lock;'//备份前需要将数据库加读锁,防止数据在备份时写入。'Query OK, 0 rows affected (0.00 sec)mysql> exitBye[root@localhost ~]# mkdir /mysql_bak'//创建/mysql_bak 目录'[root@localhost ~]# mysqldump -u root -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql'//括号中%F 的F必须是大写的'Enter password: [root@localhost ~]# ll /mysql_bak/total 4-rw-r--r-- 1 root root 2055 Sep 13 16:26 client_userinfo--09-13.sql[root@localhost ~]# mysqladmin -u root -p flush-logsEnter password: [root@localhost ~]# ll /usr/local/mysql/data/total 122928drwxr-x--- 2 mysql mysql 54 Sep 10 16:27 authdrwxr-x--- 2 mysql mysql 54 Sep 13 16:03 auth1-rw-r----- 1 mysql mysql 56 Sep 8 15:24 fdrwxr-x--- 2 mysql mysql 20 Sep 8 15:32 bbsdrwxr-x--- 2 mysql mysql 62 Sep 13 16:23 client-rw-r----- 1 mysql mysql434 Sep 13 16:19 ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 Sep 13 16:23 ibdata1-rw-r----- 1 mysql mysql 50331648 Sep 13 16:23 ib_logfile0-rw-r----- 1 mysql mysql 50331648 Sep 8 15:24 ib_logfile1-rw-r----- 1 mysql mysql 12582912 Sep 13 16:26 ibtmp1drwxr-x--- 2 mysql mysql4096 Sep 8 15:24 mysql-rw-r----- 1 mysql mysql1539 Sep 13 16:28 mysql-bin.000001-rw-r----- 1 mysql mysql154 Sep 13 16:28 mysql-bin.000002-rw-r----- 1 mysql mysql 78 Sep 13 16:28 mysql-bin.indexdrwxr-x--- 2 mysql mysql8192 Sep 8 15:24 performance_schemadrwxr-x--- 2 mysql mysql 68 Sep 12 04:41 schooldrwxr-x--- 2 mysql mysql8192 Sep 8 15:24 sysdrwxr-x--- 2 mysql mysql 68 Sep 13 15:59 text
6.3 继续录入新的内容并进行增量备份
[root@localhost ~]# mysql -u root -pEnter password: mysql> unlock tables; '//解除表锁'Query OK, 0 rows affected (0.00 sec)mysql> use client;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> insert into user_info values('000009','赵六','男','019','37');Query OK, 1 row affected (0.00 sec)mysql> insert into user_info values('000010','孙七','男','020','36');Query OK, 1 row affected (0.01 sec)mysql> select * from user_info;+-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 || 000009 | 赵六 | 男| 019 |37 || 000010 | 孙七 | 男| 020 |36 |+-----------+--------+--------+-------------+--------+5 rows in set (0.00 sec)mysql> exitBye[root@localhost ~]# ll /usr/local/mysql/datatotal 122928drwxr-x--- 2 mysql mysql 54 Sep 10 16:27 authdrwxr-x--- 2 mysql mysql 54 Sep 13 16:03 auth1-rw-r----- 1 mysql mysql 56 Sep 8 15:24 fdrwxr-x--- 2 mysql mysql 20 Sep 8 15:32 bbsdrwxr-x--- 2 mysql mysql 62 Sep 13 16:23 client-rw-r----- 1 mysql mysql434 Sep 13 16:19 ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 Sep 13 16:30 ibdata1-rw-r----- 1 mysql mysql 50331648 Sep 13 16:30 ib_logfile0-rw-r----- 1 mysql mysql 50331648 Sep 8 15:24 ib_logfile1-rw-r----- 1 mysql mysql 12582912 Sep 13 16:26 ibtmp1drwxr-x--- 2 mysql mysql4096 Sep 8 15:24 mysql-rw-r----- 1 mysql mysql1539 Sep 13 16:28 mysql-bin.000001-rw-r----- 1 mysql mysql750 Sep 13 16:30 mysql-bin.000002-rw-r----- 1 mysql mysql 78 Sep 13 16:28 mysql-bin.indexdrwxr-x--- 2 mysql mysql8192 Sep 8 15:24 performance_schemadrwxr-x--- 2 mysql mysql 68 Sep 12 04:41 schooldrwxr-x--- 2 mysql mysql8192 Sep 8 15:24 sysdrwxr-x--- 2 mysql mysql 68 Sep 13 15:59 text[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/
6.4 模拟误操作删除 user_info表
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info'Enter password: [root@localhost ~]# mysql -u root -p -e 'select * from client.user_info'Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
6.5 恢复操作
mysql> source /mysql_bak/client_userinfo--09-13.sql '//需要创建新的数据库并进入'[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo--09-13.sql Enter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 |+-----------+--------+--------+-------------+--------+[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 |mysql -u root -pEnter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 || 000009 | 赵六 | 男| 019 |37 || 000010 | 孙七 | 男| 020 |36 |+-----------+--------+--------+-------------+--------+
6.6 基于位置恢复
[root@localhost ~]# mysql -uroot -p -e 'drop table client.user_info''//删除表'Enter password: [root@localhost ~]# mysql -uroot -p -e 'select * from client.user_info'Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo--09-13.sql Enter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 |+-----------+--------+--------+-------------+--------+[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002''//--base64-output=decode-rows 64解码 -v换行显示'[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='517' /mysql_bak/mysql-bin.000002 |mysql -u root -p'//操作的节点附近即可'Enter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 || 000009 | 赵六 | 男| 019 |37 |+-----------+--------+--------+-------------+--------+[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info'Enter password: [root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo--09-13.sql Enter password: [root@localhost ~]# mysqlbinlog --no-defaults --start-position='657' /mysql_bak/mysql-bin.000002 |mysql -u root -pEnter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 |+-----------+--------+--------+-------------+--------+'//基于位置恢复的操作,节点不要选择太靠近操作的节点,否则容易恢复失败。'
补充:数据库恢复操作(基于位置与时间点的恢复)
[root@lamp ~]# vi /etc/f
log-bin = school-bin
[root@lamp ~]# systemctl restart mysqld
mysql> create database school;
mysql> use school;
mysql> desc info;
±------±--------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±--------------±-----±----±--------±---------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(10,0) | NO | | NULL | |
±------±--------------±-----±----±--------±---------------+
3 rows in set (0.02 sec)
mysql> create table info(id int(4) not null primary key auto_increment,name varchar(10) not null,score decimal not null);
mysql> insert into info(name,score) values (‘zhangsan’,90),(‘lisi’,80);
mysql> select * from info;
±—±---------±------+
| id | name | score |
±—±---------±------+
| 1 | zhangsan | 90 |
| 2 | lisi | 80 |
±—±---------±------+
2 rows in set (0.00 sec)
mysql> insert into info(name,score) values (‘wangwu’,70);
mysql> delete from info where name=‘zhangsan’; ##误操作##
mysql> insert into info(name,score) values (‘zhaoliu’,88);
mysql> select * from info;
±—±--------±------+
| id | name | score |
±—±--------±------+
| 2 | lisi | 80 |
| 3 | wangwu | 70 |
| 4 | zhaoliu | 88 |
±—±--------±------+
3 rows in set (0.00 sec)
mysql> quit
##现在若想恢复成zhangsan用户也存在的情况,则需要以下步骤,基于位置进行恢复和基于时间点进行恢复##
[root@lamp ~]# cd /usr/local/mysql/data
[root@lamp data]# mysqlbinlog --no-defaults school-bin.000001 ##看不懂的文件,需要转码##
[root@lamp data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v school-bin.000001 >/opt/share.txt ##恢复 64位解码 输出 形式按正常编码每一行正常输出 -v自动换行机制输出到/opt目录下,命名为share.txt##
[root@lamp data]# cd /opt
[root@lamp opt]# vim share.txt
##通过查看脚本文件,需要首先将数据恢复到完全备份后才能进行增量恢复。
##at 1167 -10-13 21:54:53##
zhangsan lisi wangwu at 790 -10-13 21:40:14
##基于位置恢复时,需要是commit后的位置,at 1167 -10-13 21:54:53##
zhangsan at 1306 -10-13 21:54:53
##误删除,基于位置恢复时,此处是误操作,不需要恢复,需要跳过,需要是begin前的位置进行恢复,at 1167
-10-13 21:54:53 ##
zhaoliu at 1640 -10-13 21:55:55 at 1446 -10-13 21:55:55
##以下是基于位置进行恢复##
[root@lamp data]# mysql -uroot -pabc123
mysql> use school
mysql> delete from info; ##清空
[root@lamp ~]# cd /usr/local/mysql/data
[root@lamp data]# mysqlbinlog --no-defaults --stop-position=‘1167’ school-bin.000001 | mysql -uroot -pabc123
[root@lamp data]# mysql -uroot -pabc123
mysql> use school
mysql> select * from info;
±—±---------±------+
| id | name | score |
±—±---------±------+
| 1 | zhangsan | 90 |
| 2 | lisi | 80 |
| 3 | wangwu | 70 |
±—±---------±------+
3 rows in set (0.00 sec) ##恢复到了完全备份位置##
[root@lamp data]# mysqlbinlog --no-defaults --start-position=‘1446’ school-bin.000001 | mysql -uroot -pabc123
[root@lamp data]# mysql -uroot -pabc123
mysql> use school;
mysql> select * from info;
±—±---------±------+
| id | name | score |
±—±---------±------+
| 1 | zhangsan | 90 |
| 2 | lisi | 80 |
| 3 | wangwu | 70 |
| 4 | zhaoliu | 88 |
±—±---------±------+
4 rows in set (0.00 sec) ##发现这个时候数据已经恢复且跳过了误删除zhangsan用户的位置##
注:服务启动才会产生日志文件,服务关闭日志文件还存在。 产生或刷新日志信息方法: ①进数据库操作:flush logs; mysqladmin -uroot -pabc123 flush-logs ②达到max_binlog_size设置大小后自动产生分割日志文件