1200字范文 > MySQL数据备份与恢复----------mysqldump-----多种恢复方法

MySQL数据备份与恢复----------mysqldump-----多种恢复方法

时间：2023-06-01 07:06:57

一、数据备份的重要性二、数据库备份的分类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;

±------±--------------±-----±----±--------±---------------+

±------±--------------±-----±----±--------±---------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

±------±--------------±-----±----±--------±---------------+

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设置大小后自动产生分割日志文件

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。

MySQL数据备份与恢复----------mysqldump-----多种恢复方法

目录

一、 数据备份的重要性

二、 数据库备份的分类

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 基于位置恢复

一、数据备份的重要性

二、数据库备份的分类