欢迎品鉴~MySQL数据库的备份与恢复

最新推荐文章于 2024-03-25 22:53:33 发布
最新推荐文章于 2024-03-25 22:53:33 发布
阅读量604 收藏
点赞数 8
分类专栏： Web服务器群集
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48190899/article/details/108563628
版权

一、数据库的备份分类

1-1数据库备份重要性

■在生产环境中，数据的安全性至关重要
■任何数据的丢失都可能产生严重的后果
■造成数据丢失的原因
●程序错误
●人为操作错误
●运算错误
●磁盘故障
●灾难和盗窃

1-2数据库备份分类1

■从物理与逻辑的角度，备份可分为
●物理备份∶对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
◆物理备份方法
●冷备份（脱机备份）∶是在关闭数据库的时候进行的
●热备份（联机备份）∶数据库处于运行状态，依赖于数据库的日志文件
●温备份∶数据库锁定表格（不可写入但可读）的状态下进行备份操作
●逻辑备份∶对数据库逻辑组件（如∶表等数据库对象）的备份

1-3数据库备份分类2

■从数据库的备份策略角度，备份可分为
● 完全备份∶每次对数据库进行完整的备份
●差异备份∶备份自从上次完全备份之后被修改过的文件
●增量备份∶只有在上次完全备份或者增量备份后被修改的文件才会被备份

1-4常见的备份方法

■物理冷备
● 备份时数据库处于关闭状态，直接打包数据库文件
● 备份速度快，恢复时也是最简单的
■专用备份工具mydump或mysqlhotcopy
● mysqldump常用的逻辑备份工具
●mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
■启用二进制日志进行增量备份
●进行增量备份，需要刷新二进制日志
■第三方工具备份
●免费的MySQL热备份软件Percona XtraBackup

二、MySQL完全备份与恢复

2-1MySQL完全备份简介

■是对整个数据库、数据库结构和文件结构的备份
■ 保存的是备份完成时刻的数据库
■是差异备份与增量备份的基础

2-2MySQL完全备份优缺点

■优点
●备份与恢复操作简单方便
■缺点
●数据存在大量的重复
●占用大量的备份空间
● 备份与恢复时间长

2-3数据库完全备份分类

■物理冷备份与恢复
●关闭MySQL数据库
● 使用tar命令直接打包数据库文件夹
●直接替换现有MySQL目录即可
■mysqldump备份与恢复
●MySQL自带的备份工具，可方便实现对MySQL的备份
●可以将指定的库、表导出为SQL脚本
●使用命令mysq导入备份的数据

2-4MySQL物理冷备份及恢复

■备份数据库

[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                                                        ##查看备份
总用量 1328
-rw-r--r-- 1 root root 1359664 8月  12 22:39 mysql_all-2018-08-12.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目录被移走 ，模拟故障成功
总用量 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

■数据库恢复

[root@localhost mysql]# mkdir /restore                                                                                        ####根目录新建restore 目录
[root@localhost mysql]# tar xzvf /backup/mysql_all-2018-08-12.tar.gz -C /restore/              ####将备份数据库解压到 /restore目录下  不要无脑刷，时间节点不一样
usr/local/mysql/data/
usr/local/mysql/data/ibdata1
usr/local/mysql/data/ib_logfile1
usr/local/mysql/data/ib_logfile0
。。。。。省略。。。。。。

2-5mysqldump备份与恢复

■mysqldump命令对单个库进行完全备份

musqldump  -u 用户名 -p【密码】【选项】【数据库名】 > 备份路径/备份文件名

■单裤备份的示例

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shuai              |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

[root@localhost ~]# mysqldump -u root -p shuai > /opt/shuai.sql
#shuai.sql就是导出的备份文件
[root@localhost opt]# ls
mysql-5.7.20                 mysql-boost-5.7.20.tar.gz  shuai.sql
mysql_all_2020-08-19.tar.gz  rh

■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 -uroot -pabc123 --databases shuai mei > /opt/shuai.mei
mysqldump: [Warning] Using a password on the command line interface can be insecure.

■ 对所有库进行完全备份

musqldump -u 用户名 -p 【密码】【选项】 -add-databases > /备份路径/备份文件名

■ 所有库备份的示例

[root@localhost opt]# mysqldump -uroot -pabc123 --all-databases > /opt/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

2-6 恢复表的操作

■ 恢复表时同样可以使用source或者mysql命令
■ source恢复表的操作与恢复库的操作相同
■ 当备份文件中包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在

mysql -u 用户名 -p [密码] <表备份脚本的路径
mysql -u root -p mysql </backup/mysql-user.sql

■ 在生产环境中，可以使用shell脚本自动实现定时备份

三、MySQL增量备份与恢复

3-1 MySQL增量备份-1

■ 使用mysqldump命令进行完全备份存在的问题

  • 备份数据中有重复数据
  • 备份时间与恢复时间过长

■ 是自上一次备份后增加/变化的文件或者内容
■ 特点

  • 没有重复数据，备份量不大，时间短
  • 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

3-2 MySQL增量备份-2

■ MySQL没有提供直接的增量备份方法
■ 可以通过 MySQL提供的二进制日志间接实现增量备份
■ MySQL二进制日志对备份的意义

  • 二进制日志保存了所有更新或者可能更新数据库的操作
  • 二进制日志在启动MySQL服务器后开始记录，并在文件达到
    max_binlog_size所设置的大小或者接收到flush logs命令后重新
    创建新的日志文件
  • 只需定时执行flush logs方法重新创建新的日志，生成二进制文
    件序列，并及时把这些日志保存到安全的地方就完成了一个时间
    段的增量备份

3-3 MySQL数据库增量恢复-1

■ 一般恢复

  • 将所有备份的二进制日志内容全部恢复

■ 基于位置恢复

  • 数据库在某一时间点可能既有错误的操作也有正确的操作
  • 可以基于精准的位置跳过错误的操作

■ 基于时间点恢复

  • 跳过某个发生错误的时间点实现数据恢复

3-4 MySQL数据库增量恢复-2

■ 增量恢复的方法

  • 一般恢复
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p
  • 基于位置的恢复
    恢复数据到指定位置
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

从指定的位置开始恢复数据

mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

3-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 密码

四、MySQL备份恢复案例–完全备份

4-1 物理冷备份与恢复

备份数据库：
[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                                                        ##查看备份
总用量 1328
-rw-r--r-- 1 root root 1359664 8月  12 22:39 mysql_all-2018-08-12.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目录被移走 ，模拟故障成功
总用量 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

◆数据库恢复
[root@localhost mysql]# mkdir /restore                                                                                        ####根目录新建restore 目录
[root@localhost mysql]# tar xzvf /backup/mysql_all-2018-08-12.tar.gz -C /restore/              ####将备份数据库解压到 /restore目录下  不要无脑刷，时间节点不一样
usr/local/mysql/data/
usr/local/mysql/data/ibdata1
usr/local/mysql/data/ib_logfile1
usr/local/mysql/data/ib_logfile0
。。。。。。省略。。。。。。。


[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/          ####将备份数据移动到到/usr/local/mysql中
[root@localhost mysql]# systemctl start mysqld                            ####启动mysql数据库
[root@localhost mysql]# systemctl status mysqld                           ####查看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.

4-2 mysqldump备份与恢复

#######创建环境#####
mysql> create database auth;                              	####创建auth 这个数据库
mysql> use auth;                              		####进入auth 这个数据库
mysql> create table users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));  ###创建users表
Database changed
mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456')); ###向users表插入‘zhangsan’信息
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into users values('lisi', password('123456'));   ###向users表插入‘lisi’信息
Query OK, 1 row affected, 1 warning (0.01 sec)

######列子####
[root@localhost /]# mysqldump -u root -p mysql user >mysql-user.sql     ###备份数据库mysql中的user表  ，文件保存在当前操作目录下面
Enter password:                                                                                                  ###输入密码abc123            

######列子#####
[root@localhost /]# mysqldump -u root -p --databases auth >auth.sql      ####备份auth数据库     文件保存在当前操作目录下面
Enter password:                                                                                             ####输入密码abc123 

#####列子######
查看备份文件
[root@localhost /]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"
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;

#####恢复操作#####
mysql [选项] [库名] [表名] < /备份路径/备份文件名

#####列子###恢复表
[root@localhost /]# mysql -u root -p               ###进入数据库###
Enter password:                                      ###输入密码####
mysql> show databases;                                      ###查看数据库####
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |            
| myadm              |
| mysql              |
| performance_schema |
| sys                |

mysql> create database text ;                           ####新建数据库text ,等下导表用###
Query OK, 1 row affected (0.00 sec)
mysql> exit                                                      ####退出#####

[root@localhost opt]# 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 auth'            ######模拟故障删除auth数据库
Enter password: 
[root@localhost /]# mysql -u root -p -e 'show databases'        ######查看databases所有数据库有没有auth，发现没有了
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |              
| myadm              |
| mysql              |
| performance_schema |
| sys                |
| text               |

[root@localhost /]# mysql -u root -p < ./auth.sql                        ####导入数据库auth
Enter password:                                                        ####输入密码
[root@localhost /]# mysql -u root -p -e 'show databases'       ######查看databases所有数据库有没有auth，发现有了
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |         #### 导进去的auth数据库        
| bdqn               |
| myadm              |
| mysql              |
| performance_schema |
| sys                |
| text               |
+--------------------+

五、MySQL备份恢复案例–增量备份

5-1一般恢复（丢什么数据，找什么数据）

[root@localhost opt]# mysql -u root -p
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;                                    ####查看当前数据库binlog文件
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1492 |
+------------------+-----------+
1 row in set (0.00 sec)

5-2进行一次完全备份

mysql> flush tables with read lock;                        #####备份前需要将数据库加读锁，防止数据在备份时写入。
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye

[root@localhost ~]# mkdir /mysql_bak                       ###创建/mysql_bak 目录

[root@localhost opt]# mysqldump -u root -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql      ####这边括号中%F 的F必须是大写的
Enter password: 
[root@localhost ~]# ll /mysql_bak/
总用量 4
-rw-r--r-- 1 root root 2055 8月  15 05:55 client_userinfo-2018-08-15.sql

[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
[root@localhost ~]#  ll /usr/local/mysql/data/
总用量 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

5-3继续录入新的内容并进行增量备份

[root@localhost mysql_bak]# mysql -u root -p
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]# mysqladmin -u root -p flush-logs
Enter password: 

[root@localhost ~]# ll /usr/local/mysql/data
总用量 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 ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/

5-4模拟误操作删除 user_info表

[root@localhost mysql_bak]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost mysql_bak]# 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

5-5恢复操作

[root@localhost mysql_bak]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-14.sql 
Enter password: 
[root@localhost mysql_bak]# 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 -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 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+

5-6基于位置恢复

[root@localhost mysql_bak]# mysql -uroot -p -e 'drop table client.user_info'    ### 删除表user_info
Enter password: 

[root@localhost mysql_bak]# 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_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 |
+-----------+--------+--------+-------------+--------+
#######总结##
基于位置恢复的操作，节点不要选择太靠近操作的节点，否则容易恢复失败。

5-7基于时间恢复##### 20200913 0:01:10


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 ~]# 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 --stop-datetime='2018-8-15 6:07:39' /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-2018-08-15.sql 
Enter password: 
[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime='2018-8-15 15:33:48' /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 |
+-----------+--------+--------+-------------+--------+

5-8验证表的锁定是否产生二进制文件

mysqladmin -u root -p flush-logs
[root@localhost ~]# mysql -uroot -p -e 'show master logs'
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 ~]# mysqladmin -u root -p flush-logs
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show master logs'
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 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000004              ####--base64-output=decode-rows    64解码    ###  -v   换行显示
吉吉大国王
关注
专栏目录
基于java代驾管理系统计算机毕业设计源码+系统+lw文档+mysql数据库+调试部署
m0_73296695的博客
09-09 608
基于java代驾管理系统计算机毕业设计源码+系统+lw文档+mysql数据库+调试部署。springcloud基于Springcloud的在线教育平台。springboot基于springboot技术的游戏交易平台。jsp基于JavaWeb的社区商超系统的设计与实现。ssm基于Web的医学院校大学生就业信息管理系统。ssm基于JAVA的OA办公系统的设计与实现。ssm基于JavaEE的疆域特色农家乐系统。ssm基于javaweb的家庭财务管理系统。ssm基于html的健身房信息管理系统。
django mysql 开发实例_Win10+Python+Django+Nginx+MySQL开发教程及实例（1）——开发环境搭建...
weixin_36286464的博客
01-19 186
Win10+Python+Django+Nginx+MySQL开发教程及实例PaulTsao本教程共有三篇内容：第四篇*：创建个人博客第五篇*：个人博客网站上云部署并运行第一篇：Win10+Python+Django+Nginx+MySQL 开发环境搭建环境及版本：【2018-01】Windows：Win10 专业版 32位提醒：强烈建议大家基于64位搭建开发环境，原因以后会懂的。(本人Win...
MySQL数据库备份和恢复
04-10
MySQL 数据库备份 和 恢复
理论+实验：MySQL备份与恢复（完整备份、增量备份）
CSDN著名博主
09-13 2766
目录一、数据库备份的分类1.1 数据备份的重要性 一、数据库备份的分类 1.1 数据备份的重要性 ■ 再生产环境中，数据的安全性至关重要 ■ 任何数据的丢失都可能产生严重的后果 ■ 造成数据丢失的原因 程序错误 人为操作错误 运算错误 磁盘故障 灾难（如火灾、地震）和盗窃 ...
MySQL数据的备份和恢复
weixin_42099301的博客
08-22 2295
MySQL数据的备份和恢复 文章目录MySQL数据的备份和恢复1. 数据备份的重要性2. 数据库备份的分类3. 常见的备份方法4. MySQL完全备份(1) 物理冷备份与恢复(2) mysqldump备份与恢复(3) MySQL物理冷备份及恢复5. mysqldump备份数据库(1) mysqldump命令对单个库进行完全备份(2) 单库备份的示例(3) mysqldump命令对多个库进行完全备份(4) 对所有库进行完全备份(5) mysqldump可针对库内特定的表进行备份6. 恢复数据库(1) 使用so
MySQL数据库备份
热门推荐
L2111533547的博客
06-22 1万+
日志文件在数据库进行备份和恢复时起到了很重要的作用 常用的日志文件默认保存在 /usr/local/mysql/data 目录下 可在 /etc/my.cnf 配置文件中的 [mysqld] 中进行日志的路径修改、开启、关闭等操作用于记录 mysql 启动、停止或运行时产生的错误信息 可通过一下字段进行更新 1.3 二进制文件 二进制日志，用来记录所有更新的数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复 开启方式： 1.4 中继日志 一般情况下，它在 mysql 主从同步（复制）、读写分
数据库第四次作业--备份与还原
cheng_wei_qiang的博客
03-16 908
备份所有数据库 输入 mysqldump -u root -p123456 --all-databases>D:\MYSQL\mysql-8.0.28-winx64\suoyou.sql 查询所有库 输入 show databases 备份A库 输入 >mysqldump -u root -p123456 a>D:\MYSQL\mysql-8.0.28-winx64\a.sql 备份B、C库 输入 mysqldump -u root -p1..
Win10+Python+Django+Nginx+MySQL开发教程及实例
u013943522的博客
01-11 1502
Win10+Python+Django+Nginx+MySQL开发教程及实例  PaulTsao 说明：本文由作者原创，仅供内部参考学习与交流，转载引用请注明出处，用于商业目的请联系作者本人。 本教程共有三篇内容： 第一篇：Win10+Python+Django+Nginx+MySQL 开发环境搭建 第二篇：用Python连通操作MySQL 第三篇：用Nginx
计算机毕业设计ssm医院取药系统
gfsa854的博客
09-28 1530
最新计算机专业原创毕业设计参考选题都有源码+数据库是近期作品 你的选题刚好在下面有,有时间看到机会给您发 1 ssm毕业生实习管理系统 2 jsp安全生产监管系统 3 jsp同学录网站 4 ssm实验室耗材管理系统 5 ssm国漫论坛网站 6 ssm基于SSM的英语学习网站的设计与实现 7 jsp基于Java的高速公路服务区管理系统 8 ssm翻转课堂微视频管理系统 9 ssm基于WEB的儿童运动馆业务信息系统 .
计算机毕业设计JAVA旅游管理系统mybatis+源码+调试部署+系统+数据库+lw
m0_71493226的博客
07-23 494
springboot基于Bootstrap框架的读书网站设计与实现。springboot基于springboot和vue的酒店管理系统。springboot基于springboot技术的游戏交易平台。springboot基于B_S模式的后勤管理系统-在线报修系统。jsp基于JavaEE技术的“日进斗金”理财大师系统设计与实现。springcloud基于微服务架构的乐居租房网的设计与实现。springboot基于JSP的企业办公管理系统设计与实现。springboot基于springBoot仓库管理系统。..
mysql数据库备份
07-17
mysql数据库字自动备份；将脚本添加到系统“定时任务”中
MySQL数据备份与恢复
彭淦淦的博客
04-07 297
3.1 问题 具体要求如下： 练习mysqldump命令的使用 使用 mysql 命令恢复删除的数据 3.2 步骤 实现此案例需要按照如下步骤进行。 步骤一：练习mysqldump命令的使用 1）备份MySQL服务器上的所有库 将所有的库备份为mysql-all.sql文件： [root@dbsvr1 ~]# mysqldump -u root -p --all-databases > /...
MySQL 数据备份和恢复
wtnu200的专栏
05-23 548
接触Mysql到今天，还没有一个星期，感觉相比较mssql，mysql很多地方很不方便。今天学习备份和恢复，遇到最麻烦的一个问题，就是编码，到现在还不明白为什么，反正用mysql这几天，让我很吐血，但还是不得不用，下面正题。 数据备份 cmd >cd c:\Program Files\MySQL\MySQL Server 5.0\bin  --有的server 2003 系统这命令没
MySQL数据库备份及恢复
最新发布
mikechen1的博客
03-25 4654
mysqld]#可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）#可加可不加该命令。
Mysql数据库之备份和恢复
cxin1225的博客
06-23 4020
完全备份和不完全备份都各有各的优点，公司对数据库备份大部分会采用的方式为。
MySQL的数据备份与恢复
陈玉博的技术博客
07-02 345
MySQL数据备份分为热备和冷备。 导出数据 mysql> select * from table_name into outfile "/tmp/filename"; 若提示：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。则是因为MySQL的权限问题。可通过下面命令查看MySQL的权限设置： mys
MySQL数据库备份与恢复策略
"MySQL数据库的备份与恢复是一个关键的系统维护任务，确保数据的安全性和可恢复性。本文主要探讨MySQL中的备份策略，包括各种工具和技术，以及针对不同表类型的备份方法。" 在MySQL中，备份通常涉及两种主要的存储...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值