一、数据库备份的分类
1.1 数据备份的重要性
■ 再生产环境中,数据的安全性至关重要
■ 任何数据的丢失都可能产生严重的后果
■ 造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难(如火灾、地震)和盗窃
1.2 数据库备份的分类-1
■ 从物理与逻辑的角度,备份可分为
物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
◆物理备份方法
冷备份(脱机备份) :是在关闭数据库的时候进行的
热备份(联机备份) :数据库处于运行状态,依赖于数据库的日志文件
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
逻辑备份:对数据库逻辑组件(如: 表等数据库对象)的
备份
1.3 数据库备份的分类-2
■ 从数据库的备份策略角度,备份可分为
完全备份:每次对数据进行完整的备份
差异备份:备份那些自从上次完全备份之后被修改过的文件
增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
1.4 常见的备份方法
■ 物理冷备
备份时数据库处于关闭状态,直接打包数据库文件
备份速度快,恢复时也是最简单的
■ 专用备份工具mydump或mysqlhotcopy
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
■ 启用二进制日志进行增量备份
■ 第三方工具备份
免费的MySQl热备份软件Percona XtraBackup
二、 MySQL完全备份与恢复
2.1 MySQL完全备份-1
■ 完全备份是对整个数据库的备份、数据库结构和文件结构的备份
■ 完全备份保存的是备份完成时刻的数据库
■ 完全备份是增量备份的基础
2.2 MySQL完全备份-2
■ 优点
■ 缺点
数据存在大量的重复
占用大量的备份空间,空间利用率低
备份与恢复时间长
2.3 数据库完全备份分类
■ 物理冷备份与恢复
关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可
■ mysqldump备份与恢复
MySQL自带的备份工具,可方便实现对MySQL的备份
可以将指定的库、表导出为SQL脚本
使用命令mysql导入备份的数据
2.4 MySQL物理冷备份及恢复
■ 物理冷备份
[ root@localhost ~]
[ root@localhost ~]
[ root@localhost ~]
■ 恢复数据库
[ root@localhost ~]
[ root@localhost ~]
[
root@localhost ~]
[ root@localhost ~]
[ root@localhost ~]
[ root@localhost ~]
2.5 mysqldump备份数据库-1
■ mysqldump命令对单个库进行完全备份
musqldump -u 用户名 -p【密码】【选项】【数据库名】 > 备份路径/备份文件名
■ 单裤备份的示例
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shuai |
| sys |
+--------------------+
5 rows in set ( 0.00 sec)
[ root@localhost ~]
[ root@localhost opt]
mysql-5.7.20 mysql-boost-5.7.20.tar.gz shuai.sql
mysql_all_2020-08-19.tar.gz rh
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
2.6 mysqldump备份数据库-2
■ mysqldump命令对多个库进行完全备份
mysqldump -u 用户名 -p 【密码】【选项】 --databases 库名1 【库名2】.. > /备份路径/备份文件名
■ 多库备份的示例
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mei |
| mysql |
| performance_schema |
| shuai |
| sys |
+--------------------+
6 rows in set ( 0.01 sec)
[ root@localhost opt]
mysqldump: [ Warning] Using a password on the command line interface can be insecure.
■ 对所有库进行完全备份
musqldump -u 用户名 -p 【密码】【选项】 -add-databases > /备份路径/备份文件名
■ 所有库备份的示例
[
root@localhost opt]
mysqldump: [ Warning] Using a password on the command line interface can be insecure.
2.7 恢复表的操作
■ 恢复表时同样可以使用source或者mysql命令
■ source恢复表的操作与恢复库的操作相同
■ 当备份文件中包含表的备份,而不包括创建库的语句时,必须指定库名,且目标库必须存在
mysql -u 用户名 -p [ 密码] < 表备份脚本的路径
mysql -u root -p mysql < /backup/mysql-user.sql
■ 在生产环境中,可以使用shell脚本自动实现定时备份
三、企业备份案例-完全备份
3.1 物理冷备份与恢复
备份数据库:
[ root@localhost ~]
[ root@localhost ~]
[ root@localhost ~]
[ root@localhost ~]
[ root@localhost backup]
总用量 1328
-rw-r--r-- 1 root root 1359664 8月 12 22:39 mysql_all-2018-08-12.tar.gz
◆模拟故障
[ root@localhost backup]
[ root@localhost backup]
[ root@localhost backup]
[ root@localhost mysql]
总用量 64
drwxr-xr-x 2 mysql mysql 4096 8月 4 19:19 bin
-rw-r--r-- 1 mysql mysql 17987 9月 13 2017 COPYING
-rw-r--r-- 1 mysql mysql 17987 9月 13 2017 COPYING-test
drwxr-xr-x 2 mysql mysql 55 8月 4 19:19 docs
drwxr-xr-x 3 mysql mysql 4096 8月 4 19:19 include
drwxr-xr-x 4 mysql mysql 191 8月 4 19:19 lib
drwxr-xr-x 4 mysql mysql 30 8月 4 19:19 man
drwxr-xr-x 10 mysql mysql 4096 8月 4 19:20 mysql-test
-rw-r--r-- 1 mysql mysql 2478 9月 13 2017 README
-rw-r--r-- 1 mysql mysql 2478 9月 13 2017 README-test
drwxr-xr-x 28 mysql mysql 4096 8月 4 19:20 share
drwxr-xr-x 2 mysql mysql 90 8月 4 19:20 support-files
drwxr-xr-x 3 mysql mysql 17 8月 4 19:19 usr
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
◆数据库恢复
[ root@localhost mysql]
[ root@localhost mysql]
usr/local/mysql/data/
usr/local/mysql/data/ibdata1
usr/local/mysql/data/ib_logfile1
usr/local/mysql/data/ib_logfile0
。。。。。。省略。。。。。。。
[ root@localhost mysql]
[ root@localhost mysql]
[ root@localhost mysql]
● mysqld.service - MySQL Server
Loaded: loaded ( /usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active ( running) since 日 2018-08-12 22:48:20 CST; 7s ago
Docs: man:mysqld( 8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 2036 ExecStart= /usr/local/mysql/bin/mysqld --daemonize --pid-file= /usr/local/mysql/mysqld.pid $MYSQLD_OPTS (
code= exited, status= 0/SUCCESS)
Process: 2016 ExecStartPre= /usr/local/mysql/bin/mysqld_pre_systemd ( code= exited, status= 0/SUCCESS)
Main PID: 2039 ( mysqld)
CGroup: /system.slice/mysqld.service
└─2039 /usr/local/mysql/bin/mysqld --daemonize --pid-file= /usr/local/mys.. .
8月 12 22:48:20 localhost.localdomain mysqld[ 2036] : 2018-08-12T14:48:20.795875Z 0.. ..
8月 12 22:48:20 localhost.localdomain mysqld[ 2036] : 2018-08-12T14:48:20.795889Z 0.. .;
8月 12 22:48:20 localhost.localdomain mysqld[ 2036] : 2018-08-12T14:48:20.795911Z 0.. ..
8月 12 22:48:20 localhost.localdomain mysqld[ 2036] : 2018-08-12T14:48:20.812257Z 0.. .s
8月 12 22:48:20 localhost.localdomain mysqld[ 2036] : 2018-08-12T14:48:20.812564Z 0.. ..
8月 12 22:48:20 localhost.localdomain mysqld[ 2036] : Version: '5.7.20' socket: '/.. .n
8月 12 22:48:20 localhost.localdomain mysqld[ 2036] : 2018-08-12T14:48:20.812584Z 0.. ..
8月 12 22:48:20 localhost.localdomain mysqld[ 2036] : 2018-08-12T14:48:20.812591Z 0.. .s
8月 12 22:48:20 localhost.localdomain mysqld[ 2036] : 2018-08-12T14:48:20.826263Z 0.. .s
8月 12 22:48:20 localhost.localdomain systemd[ 1] : Started MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
3.2 mysqldump备份与恢复
mysql> create database auth;
mysql> use auth;
mysql> create table users ( user_name CHAR( 16) NOT NULL, user_passwd CHAR( 48) DEFAULT '' , PRIMARY KEY ( user_name)) ;
Database changed
mysql> insert into users( user_name,user_passwd) values( 'zhangsan' , password( '123456' )) ;
Query OK, 1 row affected, 1 warning ( 0.01 sec)
mysql> insert into users values( 'lisi' , password( '123456' )) ;
Query OK, 1 row affected, 1 warning ( 0.01 sec)
[ root@localhost /]
Enter password:
[ root@localhost /]
Enter password:
查看备份文件
[ root@localhost /]
CREATE DATABASE /*! 32312 IF NOT EXISTS*/ ` auth` /*! 40100 DEFAULT CHARACTER SET utf8 */;
USE ` auth` ;
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 ( 'zhangsan' ,'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' ) ;
UNLOCK TABLES;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
mysql [ 选项] [ 库名] [ 表名] < /备份路径/备份文件名
[ root@localhost /]
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| myadm |
| mysql |
| performance_schema |
| sys |
mysql> create database text ;
Query OK, 1 row affected ( 0.00 sec)
mysql> exit
[ root@localhost opt]
Enter password:
[ root@localhost /]
Enter password:
+----------------+
| Tables_in_text |
+----------------+
| user |
+----------------+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
[ root@localhost /]
Enter password:
[ root@localhost /]
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| myadm |
| mysql |
| performance_schema |
| sys |
| text |
[ root@localhost /]
Enter password:
[ root@localhost /]
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| bdqn |
| myadm |
| mysql |
| performance_schema |
| sys |
| text |
+--------------------+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
四、MySQL增量备份与恢复
4.1 MySQL增量备份-1
■ 使用mysqldump命令进行完全备份存在的问题
■ 是自上一次备份后增加/变化的文件或者内容
■ 特点
没有重复数据,备份量不大,时间短
恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复
4.2 MySQL增量备份-2
■ MySQL没有提供直接的增量备份方法
■ 可以通过 MySQL提供的二进制日志间接实现增量备份
■ MySQL二进制日志对备份的意义
二进制日志保存了所有更新或者可能更新数据库的操作
二进制日志在启动MySQL服务器后开始记录,并在文件达到
max_binlog_size所设置的大小或者接收到flush logs命令后重新
创建新的日志文件
只需定时执行flush logs方法重新创建新的日志,生成二进制文
件序列,并及时把这些日志保存到安全的地方就完成了一个时间
段的增量备份
4.3 MySQL数据库增量恢复-1
■ 一般恢复
■ 基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
■ 基于时间点恢复
4.4 MySQL数据库增量恢复-2
■ 增量恢复的方法
mysqlbinlog [ --no-defaults] 增量备份文件 |
mysql -u 用户名 -p
mysqlbinlog --stop-position= ’操作 id’ 二进制日志 | mysql -u 用户名 -p 密码
从指定的位置开始恢复数据
mysqlbinlog --start-position= ’操作 id’ 二进制日志 | mysql -u 用户名 -p 密码
4.5 MySQL数据库增量恢复-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 密码
五、企业备份案例-增量备份
1、一般恢复(丢什么数据,找什么数据)
[ root@localhost opt]
Enter password:
mysql> create database client;
Query OK, 1 row affected ( 0.01 sec)
mysql> use client;
mysql> create table user_info( 身份证 char( 20) not null,姓名 char( 20) not null,姓别 char( 4) ,用户ID号 char( 10) not null,资费 int( 10)) ;
mysql> insert into user_info values( '000006' ,'张三' ,'男' ,'016' ,'10' ) ;
mysql> insert into user_info values( '000007' ,'李四' ,'女' ,'017' ,'91' ) ;
mysql> insert into user_info values( '000008' ,'王五' ,'女' ,'018' ,'23' ) ;
mysql> select * from user_info;
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1492 |
+------------------+-----------+
1 row in set ( 0.00 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
2、进行一次完全备份
mysql> flush tables with read lock;
Query OK, 0 rows affected ( 0.01 sec)
mysql> exit
Bye
[ root@localhost ~]
[ root@localhost opt]
Enter password:
[ root@localhost ~]
总用量 4
-rw-r--r-- 1 root root 2055 8月 15 05:55 client_userinfo-2018-08-15.sql
[ root@localhost mysql_bak]
[ root@localhost ~]
总用量 122928
-rw-r----- 1 mysql mysql 56 8月 9 16:16 auto.cnf
drwxr-x--- 2 mysql mysql 62 8月 15 06:04 client
-rw-r----- 1 mysql mysql 308 8月 15 06:03 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月 15 06:04 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月 15 06:04 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月 9 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月 15 06:05 ibtmp1
drwxr-x--- 2 mysql mysql 4096 8月 9 16:16 mysql
-rw-r----- 1 mysql mysql 1539 8月 15 06:05 mysql-bin.000001
-rw-r----- 1 mysql mysql 154 8月 15 06:05 mysql-bin.000002
-rw-r----- 1 mysql mysql 78 8月 15 06:05 mysql-bin.index
drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 performance_schema
drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 sys
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
3、继续录入新的内容并进行增量备份
[ root@localhost mysql_bak]
Enter password:
mysql> unlock tables;
Query OK, 0 rows affected ( 0.00 sec)
mysql> use client;
mysql> insert into user_info values( '000009' ,'赵六' ,'男' ,'019' ,'37' ) ;
mysql> insert into user_info values( '000010' ,'孙七' ,'男' ,'020' ,'36' ) ;
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> exit
Bye
[ root@localhost mysql_bak]
Enter password:
[ root@localhost ~]
总用量 122932
-rw-r----- 1 mysql mysql 56 8月 9 16:16 auto.cnf
drwxr-x--- 2 mysql mysql 62 8月 15 06:04 client
-rw-r----- 1 mysql mysql 308 8月 15 06:03 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月 15 06:07 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月 15 06:07 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月 9 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月 15 06:05 ibtmp1
drwxr-x--- 2 mysql mysql 4096 8月 9 16:16 mysql
-rw-r----- 1 mysql mysql 1539 8月 15 06:05 mysql-bin.000001
-rw-r----- 1 mysql mysql 797 8月 15 06:07 mysql-bin.000002
-rw-r----- 1 mysql mysql 154 8月 15 06:07 mysql-bin.000003
-rw-r----- 1 mysql mysql 117 8月 15 06:07 mysql-bin.index
drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 performance_schema
drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 sys
[ root@localhost ~]
1 2 3 4 5 6 7 8 9
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
4、模拟误操作删除 user_info表
[ root@localhost mysql_bak]
Enter password:
[ root@localhost mysql_bak]
Enter password:
ERROR 1146 ( 42S02) at line 1: Table 'client.user_info' doesn't exist
5、恢复操作
[ root@localhost mysql_bak]
Enter password:
[ root@localhost mysql_bak]
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-----------+--------+--------+-------------+--------+
[ root@localhost ~]
Enter password:
[ root@localhost ~]
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
18 19 20 21 22 23 24 25 26 27 28 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
[ root@localhost mysql_bak]
Enter password:
[ root@localhost mysql_bak]
Enter password:
ERROR 1146 ( 42S02) at line 1: Table 'client.user_info' doesn't exist
[root@localhost mysql_bak]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-14.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 换行显示
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180815 6:05:58 server id 1 end_log_pos 123 CRC32 0xba2781ac Start: binlog v 4, server v 5.7.20-log created 180815 6:05:58
# at 123
#180815 6:05:58 server id 1 end_log_pos 154 CRC32 0xa0182e52 Previous-GTIDs
# [empty]
# at 154
#180815 6:07:34 server id 1 end_log_pos 219 CRC32 0x3d67048b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= ' ANONYMOUS'/*!*/;
# at 219
#180815 6:07:34 server id 1 end_log_pos 293 CRC32 0xae858d22 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1534284454/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#180815 6:07:34 server id 1 end_log_pos 359 CRC32 0x3ccdc62e Table_map: ` client` .` user_info` mapped to number 221
# at 359
#180815 6:07:34 server id 1 end_log_pos 421 CRC32 0x55f72453 Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO ` client` .` user_info`
### SET
### @1=' 000009'
### @2=' 赵六'
### @3=' 男'
### @4=' 019'
### @5=37
# at 421
#180815 6:07:34 server id 1 end_log_pos 452 CRC32 0x45fd2af6 Xid = 50
COMMIT/*!*/;
# at 452
#180815 6:07:39 server id 1 end_log_pos 517 CRC32 0xa553503b Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= ' ANONYMOUS'/*!*/;
# at 517
#180815 6:07:39 server id 1 end_log_pos 591 CRC32 0x02ad5ee9 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1534284459/*!*/;
BEGIN
/*!*/;
# at 591
#180815 6:07:39 server id 1 end_log_pos 657 CRC32 0x3f478909 Table_map: ` client` .` user_info` mapped to number 221
# at 657
#180815 6:07:39 server id 1 end_log_pos 719 CRC32 0xb478e1c5 Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO ` client` .` user_info`
### SET
### @1=' 000010'
### @2=' 孙七'
### @3=' 男'
### @4=' 020'
### @5=36
# at 719
#180815 6:07:39 server id 1 end_log_pos 750 CRC32 0xe681426b Xid = 51
COMMIT/*!*/;
# at 750
#180815 6:07:59 server id 1 end_log_pos 797 CRC32 0xbc922301 Rotate to mysql-bin.000003 pos: 4
SET @@SESSION.GTID_NEXT= ' AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#######
[root@localhost ~]# mysqlbinlog --no-defaults --stop-position=' 517' /mysql_bak/mysql-bin.000002 |mysql -u root -p ####操作的节点附近即可
[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-2018-08-15.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no-defaults --start-position=' 657' /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 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
基于位置恢复的操作,节点不要选择太靠近操作的节点,否则容易恢复失败。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131
mysqlbinlog --no-defaults --stop-datetime= '2018-8-15 6:07:39' /mysql_bak/mysql-bin.000002 | mysql -u root -p
mysqlbinlog --no-defaults --start-datetime= '2018-8-15 6:07:39' /mysql_bak/mysql-bin.000002 | mysql -u root -p
mysqlbinlog --no-defaults --stop-datetime= '2020-9-13 0:01:10' /mysql_bak/mysql-bin.000002 | mysql -u root -p
mysqlbinlog --no-defaults --start-datetime= '2020-9-13 0:01:10' /mysql_bak/mysql-bin.000002 | mysql -u root -p
mysqlbinlog --no-defaults --start-datetime= '2019-3-13 18:16:30' /mysql_bak/mysql-bin.000003 | mysql -u root -p
18:16:30
[ root@localhost ~]
Enter password:
[ root@localhost ~]
Enter password:
[ root@localhost ~]
Enter password:
[ root@localhost ~]
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
+-----------+--------+--------+-------------+--------+
[ root@localhost ~]
Enter password:
[ root@localhost ~]
Enter password:
[ root@localhost ~]
Enter password:
[ root@localhost ~]
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
mysqladmin -u root -p flush-logs
[ root@localhost ~]
Enter password:
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1539 |
| mysql-bin.000002 | 797 |
| mysql-bin.000003 | 9438 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
mysql> use client;
mysql> insert into user_info values( '000009' ,'老八' ,'男' ,'025' ,'23' ) ;
mysql> select * from user_info;
mysql> LOCK TABLES tbl_name READ;
mysql> insert into user_info values( '000010' ,'赵9' ,'男' ,'030' ,'23' ) ;
[ root@localhost ~]
Enter password:
[ root@localhost ~]
Enter password:
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1539 |
| mysql-bin.000002 | 797 |
| mysql-bin.000003 | 9438 |
| mysql-bin.000004 | 499 |
| mysql-bin.000005 | 154 |
+------------------+-----------+
[ root@localhost ~]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 1 2 3 4 5 6 7 8 9
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37