网站设计学的科目,wordpress grace主题,网站简历模板,教育培训门户网站源码一、概述
数据库备份是指将数据库中的数据、表格、视图、存储过程、触发器等信息备份到另一个地方#xff0c;一遍在数据库丢失或损坏时进行恢复#xff0c;数据库备份是数据库管理中必不可少的一项工作#xff0c;通过备份可以保护数据库中的数据和业务。
二、数据备份的…一、概述
数据库备份是指将数据库中的数据、表格、视图、存储过程、触发器等信息备份到另一个地方一遍在数据库丢失或损坏时进行恢复数据库备份是数据库管理中必不可少的一项工作通过备份可以保护数据库中的数据和业务。
二、数据备份的重要性
备份的主要目的是灾难恢复备份还可以测试应用回滚数据修改查询历史数据审计等。
三、造成数据丢失的原因
程序错误
人为错误
运算失败
磁盘故障
灾难如火灾、地震和盗窃等等
四、备份类型
物理与逻辑角度物理备份对数据库操作系统的物理文件如数据文件、日志文件等的备份冷备份是在关闭数据库的时候进行的热备份数据库处于运行状态这种备份方法依赖于数据库的日志文件逻辑备份对数据库逻辑组件如表等数据库对象的备份表示为逻辑数据库结构create database、create table等语句和内容insert语句或分割文本文件的信息
数据库备份策略角度完整备份完整备份指的是备份整个数据库的所有信息包括数据库文件中所有的数据、表、视图、存储过程、触发器等。一般来说完整备份是备份最全面、可靠的方式但备份文件体积较大备份时间也相对较长增量备份增量备份指的是在完整备份的基础上每隔一定时间备份新增的数据或修改的数据。这中备份方式可以节省备份时间和备份文件的空间但需要配合特定软件或命令进行
五、常见的备份方法
物理备份物理冷备份时需要在数据库处于关闭状态下能够较好的保证数据库的完整性。物理冷备份以用于非核心业务这类业务都允许中断物理冷备份的特点就是速度快恢复时也是最为简单的通过直接打包数据库文件夹/usr/local/mysql/data来实现备份。
使用专用备份工具mysqldumpmysqlhotcopy 通过启用二进制日志增量备份 MySQL支持增量备份进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制。对执行备份点后进行的数据库更改所需的信息进行备份。如果进行增量备份包含上次完全备份或增量备份以来发生的数据修改需要刷新二进制日志。
第三方工具备份Percona XtraBackup是一个免费的MySQL热备份软件支持在线备份innodb和XtraDB也可以支持MySQL表备份
注意事项定期备份指定备份计划严格遵守除了完全备份之外开启binlog日志功能很重要使用统一的、容易理解的备份名称推荐使用库名或者表名加上时间的命名规则
六、完整备份
打包数据库文件备份位置源码包的位置/usr/local/mysql/data/rpm包的位置/var/lib/mysql在数据库中创建数据表并写入数据备份在数据库中创建数据表并写入数据停止数据库服务创建备份目录 按照特定格式对数据库目录进行备份 tar czf mysql_all-$(date %F).tar.gz /var/lib/mysql/* 还原创建还原目录模拟元数据丢失将解压之后的数据还原值原目录登录查看 备份 工具 备份 备份工具mysqldump对单个库进行完全备份mysqldump -u用户名 -p[密码] [选项] --databases [数据库名] /备份路径/备份文件名对多个库进行完全备份mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2]…… /备份路径/备份文件名对所有库进行完全备份mysqldump -u用户名 -p[密码] [选项] --opt --all-databases /备份路径/备份文件名对表进行完全备份mysqldump -u用户名 -p[密码] [选项] 数据库名 表名 /备份路径/备份文件名对表的结构进行备份mysqldump -u用户名 -p[密码] -d 数据库名 表名 /备份路径/备份文件名还原工具source登录mysql 数据库执行source 备份sql脚本路径mysqlmysql -u用户名 -p[密码] 库备份脚本的路径mysql -u用户名 -p[密码] 库名 表备份脚本的路径
七、增量备份
使用完全备份时备份与恢复的时间过长增量备份就是备份自上一次备份之后增加的或改变的文件内容特点没有重复数据备份量不大时间短恢复麻烦需要上次完全备份及完全备份之后所有的增量备份才能恢复而且要对所有增量备份进行逐个反推恢复MySQL没有提供直接的增量备份办法可以通过MySQL提供的二进制日志binary logsbinlog间接实现增量备份MySQL二进制日志对备份的意义二进制日志保存了所有更新或者可能更新数据库的操作。二进制日志在启动MySQL服务器后开始记录并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。开启二进制日志备份功能MySQL的配置文件的[mysqld]项中加入 log-bin文件存放路径/文件前缀如 log-binmysql-bin然后重启mysqld服务。默认此配置存在。使用mysqld --log-bin文件存放路径/文件前缀 重新启动mysqld服务每周选择服务器负载较轻的时间段或者用户访问较少的时间段进行备份
增量恢复 应用场景 人为的SQL语句破坏了数据库在进行下一次全备之前发生系统故障导致数据库丢失在主从架构中主库数据发生了故障保证从库数据一致性 方法 一般恢复备份的二进制日志内容全部恢复格式 mysqldbinlog [--no-defaults] 增量备份文件 | mysql -u用户名 -p密码基于时间点的恢复便于跳过某个发生错误的时间点实现数据恢复格式从日志开头截止到某个时间点的恢复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密码基于位置的恢复可能在同一时间点既有错误的操作也有正确的操作基于位置进行恢复更加准确mysqlbinlog --stop-position‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码mysqlbinlog --start-position‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码 MySQL完整/增量备份与恢复
MySQL安全备份与恢复
1数据备份的重要性
备份的主要目的是灾难恢复备份还可以测试应用回滚数据修改查询历史数据审计等。
在企业中数据的价值至关重要数据保障了企业业务的运行因此数据的安全性及可靠性是运维的重中之重任何数据的丢失都有可能会对企业产生严重的后果造成数据丢失的原因如下
● 程序错误
● 人为错误
● 运算失败
● 磁盘故障
● 灾难如火灾、地震和盗窃 2.1数据库备份的类型
从物理与逻辑的角度
备份可以分为物理备份和逻辑备份 物理备份对数据库操作系统的物理文件如数据文件、日志文件等的备份物理备份又可分为脱机备份冷备份和练级备份热备份这种类型的备份使用于出现问题时需要快速恢复的大型重要数据库。
1.冷备份是在关闭数据库的时候进行的
2.热备份数据库处于运行状态这种备份方法依赖于数据库的日志文件
3.温备份数据库锁定表格不可写入但可读的状态下进行的 逻辑备份对数据库逻辑组件如表等数据库对象的备份表时为逻辑数据库结构 create database、 create table等语句和内容insert语句或分割文本文件的信息。这种类型的备份使用于可以编辑数据值或表结构较小的数据量或者在不同机器体系结构上中心创建数据。
2.2从数据库的备份策略角度
备份可分为完全备份、差异备份和增量备份 完全备份每次对数据进行完整备份即对整个数据库的备份、数据库结构和文件结构的备份保证的是备份完成时刻的数据库状态是差异备份与增量备份的基础。
优点备份与恢复操作简单方便。
缺点数据存在大量的重复占用大量的空间备份与恢复时间长。 差异备份备份那些自从上次完全备份之后修改过的所有文件备份的时间起点是从上次完整备份起备份数据量会越来越大。恢复数据时只需恢复上次的完全备份与最近的一次差异备份。 增量备份只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点仅备份这之间的数据变化因而备份的数据量小占用空间小备份速度快。但恢复时需要从上一次的完整备份起到最后一次增量备份一次恢复如中间某次的备份数据损坏将导致数据的丢失。
2.3常见的备份方法 MySQL数据库的备份可以采用很多种方法如直接打包数据库文件物理冷备份专用备份工具mysqldump二进制日志增量备份第三方工具备份等
物理备份
物理冷备份时需要在数据库处于关闭状态下能够较好的保证数据库的完整性。物理冷备份以用于非核心业务这类业务都允许中断物理冷备份的特点就是速度快恢复时也是最为简单的通过直接打包数据库文件夹/usr/local/mysql/data来实现备份
专用备份工具 mysqldump或mysqlhotcopy
Mysqldump和mysqlhotcopy都可以做备份。Mysqldump时客户端常用逻辑备份程序能够产生一组被执行以再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个MySQL数据库其进行备份或传输到远程SQL服务器。MySQL更为通用因为它可以备份各种表。Mysqlhotcopy仅适用于某些存储引擎
Mysqlhotcopy是由Tim Bunce最初编写和贡献的Perl脚。Mysqlhotcopy仅用于备份myISAM和ARCHIVE表。只能运行在Unix或Linux操作系统上
通过启用二进制binary log binlog日志进行增量备份
MySQL支持增量备份进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制。对执行备份点后进行的数据库更改所需的信息进行备份。如果进行增量备份包含上次完全备份或增量备份以来发生的数据修改需要刷新二进制日志
通过第三方工具备份
Percona XtraBackup是一个免费的MySQL热备份软件支持在线备份innodb和XtraDB也可以支持MySQL表备份不过MyISAM表的备份要在表锁的情况进行
Percona XtraBackup主要的工具xtrabackup、innobackupex、xbstream
● Xtrabackup是一个编译了的二进制文件只能备份innodb/Xtradb数据文件
● Innobackupex是一个封装了Xtrabackup的Perl脚本除了可以备份innodb/xtradb之外还可以备份MyISAM。
● Xbstream是一个新的组件能够允许将文件格式转换成xbstream格式或从xbstream格式转到文件格式。
Xtrabackup工具可以单独使用但推荐使用Innobackupex来进行备份因为其本身已
经包含了Xtrabackup的所有功能
Xtrabackup是基于innodb的灾难恢复功能进行设计的备份工具复制innodb的数据文件但是由于不锁表这样复制出来的数据将不一致innodb维护了一个重要日志包含innodb数据的所有改动情况。在xtrabackup备份innodb的数据同时xtrabackup还有另外一个线程用来监控重做日志一旦日志发生变化就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了。
以上是备份过程如果我们需要恢复数据则在准备阶段Xtrabackup就需要使用之前复制的重做日志对备份出来的innodb数据文件进行灾难恢复此阶段完成之后数据库就可以进行重建还原了。
Percona Xtrabackup对MyISAM的复制是按顺序进行的先锁定表然后复制再解锁表。 3MySQL完全备份操作 MySQL数据库的完全备份可以采用多种方式物理冷备份一般用他人命令直接打包数据库文件数据目录而在备份前需要先停库
一、直接打包数据文件
进入mysql
创建实验数据库并指向
创建数据表
添加Rich数据和ID
查看user数据表
[rootlocalhost beifen]# mysql -uroot -p123456MariaDB [(none)] create database experiment;
Query OK, 1 row affected (0.00 sec)MariaDB [(none)] use experiment;
Database changedMariaDB [experiment] create table user(- name char(10)not null,- ID int(48)- );
Query OK, 0 rows affected (0.01 sec)MariaDB [experiment] insert into user values(Rich,123);
Query OK, 1 row affected (0.01 sec)MariaDB [experiment] select * from user;
------------
| name | ID |
------------
| Rich | 123 |
------------
1 row in set (0.02 sec)MariaDB [experiment] exit
Bye
[rootlocalhost beifen]# 2.模拟数据丢失
指定存储目录 格式bzip2归档
查看刚刚创建的根目录backup 压缩文件存在
创建存储数据文件夹移动mysql原有所有数据
报错进入不了
[rootlocalhost /]# tar jcf backup/mysql_all-$(date %F).tar.gz /var/lib/mysql/*
tar: 从成员名中删除开头的“/”[rootlocalhost /]# ls /backup
mysql_all-2023-07-28.tar.gz模拟数据丢失
[rootlocalhost /]# mkdir bak; mv /var/lib/mysql/* bak[rootlocalhost /]# mysql -uroot -p123456
ERROR 2002 (HY000): Cant connect to local MySQL server through socket /var/lib/mysql/mysql.sock (2)
3.数据恢复
创建 恢复文件夹
解压备份压缩包文件到restore
移动备份文件到 mysql原配置文件夹里
重启mysql服务
[rootlocalhost /]# mkdir restore[rootlocalhost /]# tar xf backup/mysql_all-2023-07-28.tar.gz -C restore/[rootlocalhost /]# ls restore/
var[rootlocalhost /]# mv restore/var/lib/mysql/* /var/lib/mysql/[rootlocalhost /]# systemctl restart mariadb[rootlocalhost /]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type help; or \h for help. Type \c to clear the current input statement.MariaDB [(none)] 二、使用专用备份工具mysqldump
MySQL自带的备份工具mysqldump可以很方便的对MySQL进行备份。通过该命令工具可以将数据库、数据表或全部的库导出为SQL脚本便于该命令在不同版本的MySQL服务器上使用。例如当需要升级MySQL服务器时可以先使用mysqldump命令将原有库信息导出然后直接在升级后的MySQL服务器中导入即可。
1.对experiment单个库进行完全备份
退出mysql
格式mysqldump -u用户名 -p[密码] [选项] --databases [数据库名] /备份路径/备份文件名
MariaDB [(none)] show databases;
--------------------
| Database |
--------------------
| information_schema |
| asd |
| experiment |
| mysql |
| performance_schema |
| students |
| sy |
| test |
| userBase |
--------------------
9 rows in set (0.01 sec)
MariaDB [experiment] exit
Bye
[rootlocalhost /]# mysqldump -uroot -p123456 --databases experiment backup/experiment
[rootlocalhost /]# ls backup/
experiment mysql_all-2023-07-28.tar.gz2.对多个库进行完全备份
格式mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2]…… /备份路径/备份文件名
[rootlocalhost ~]# mysqldump -uroot -p123456 --databases sy mysql /backup/mysql_sy
[rootlocalhost ~]# ls /backup/
experiment mysql_sy3.对所有库进行完全备份
格式mysqldump -u用户名 -p[密码] [选项] --opt --all-databases /备份路径/备份文件名
[rootlocalhost ~]# mysqldump -uroot -p123456 --opt --all-databases backup/mysql_all.$(date %Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.[rootlocalhost ~]# ls backup/
auth-20200730.sql mysql_all.20200730.sql mysql_all-2020-07-30.tar.gz mysqlauth-20200730.sql
4.对表进行完全备份
格式mysqldump -u用户名 -p[密码] [选项] 数据库名 表名 /备份路径/备份文件名
[rootlocalhost ~]# mysqldump -uroot -p123456 --databases auth user backup/auth_user-$(date %Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1049: Unknown database user when selecting the database[rootlocalhost ~]# ls backup/
auth-20200730.sql mysql_all.20200730.sql mysqlauth-20200730.sql
auth_user-20200730.sql mysql_all-2020-07-30.tar.gz
5.对表结构的备份
格式mysqldump -u用户名 -p[密码] -d 数据库名 表名 /备份路径/备份文件名
[rootlocalhost ~]# mysqldump -uroot -p123456 -d mysql user backup/desc_mysql_user-$(date %Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[rootlocalhost ~]# ls backup/
auth-20200730.sql desc_mysql_user-20200730.sql mysql_all-2020-07-30.tar.gz
auth_user-20200730.sql mysql_all.20200730.sql mysqlauth-20200730.sql 五、使用mysqldump备份后恢复数据库
1.source命令
登录到MySQL数据库执行source备份sql脚本路径
[rootlocalhost ~]# mysql -uroot -p123456
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| Rich |
| auth |
| mysql |
| performance_schema |
| sys |
--------------------
6 rows in set (0.00 sec)mysql drop database auth;
Query OK, 1 row affected (0.01 sec)mysql source backup/auth-20200730.sqlmysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| Rich |
| auth |
| mysql |
| performance_schema |
| sys |
--------------------
6 rows in set (0.00 sec)
2.mysql命令
格式mysql -u用户名 -p[密码] 库备份脚本的路径
mysql -u用户名 -p[密码] 库名 表备份脚本的路径
[rootlocalhost ~]# mysql -uroot -p123456 -e show databases;
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------------
| Database |
--------------------
| information_schema |
| Rich |
| auth |
| mysql |
| performance_schema |
| sys |
--------------------
[rootlocalhost ~]# mysql -uroot -p123456 -e drop database auth;
[rootlocalhost ~]# mysql -uroot -p123456 backup/auth-20200730.sql
[rootlocalhost ~]# mysql -uroot -p123456 -e show databases;
--------------------
| Database |
--------------------
| information_schema |
| Rich |
| auth |
| mysql |
| performance_schema |
| sys |
--------------------
六、MySQL备份思路
1、定期实施备份指定备份计划或策略并严格遵守
2、除了进行完全备份开启MySQL服务器的binlog日志功能是很重要的完全备份加上日志可以对MySQL进行最大化还原
3、使用统一和易理解的备份名称推荐使用库名或者表名加上时间的命名规则如mysql_user-20200430.sql不要使用backup1或者abc之类没有意义的名字。 MySQL增量备份与恢复
一、MySQL增量备份概念
使用mysqldump进行完全备份备份的数据中有重复数据备份时间与恢复时间过长。而增量备份就是备份自上一次备份之后增量或改变的文件或内容。
增量备份的特点
没有重复数据备份量不大时间短
恢复麻烦需要上次完全备份及完全备份之后所有的增量备份才能恢复而且要对所有增量备份进行逐个反推恢复。
MySQL没有提供直接的增量备份办法可以通过MySQL提供的二进制日志binary logsbinlog间接实现增量备份。
MySQL二进制日志对备份的意义
二进制日志保存了所有更新或者可能更新数据库的操作。
二进制日志在启动MySQL服务器后开始记录并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。
[rootlocalhost ~]# vim /etc/my.cnf
max_binlog_size 1024000 #二进制日志最大1M
只需定时执行flush logs方法重新创建新的日志生成二进制文件序列并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。
要进行MySQL的增量备份首先要开启二进制日志功能开启MySQL的二进制日志功能。
方法一MySQL的配置文件的[mysqld]项中加入 log-bin文件存放路径/文件前缀如 log-binmysql-bin然后重启mysqld服务。默认此配置存在。
[rootlocalhost ~]# awk /log-bin/{print NR,$0} /etc/my.cnf
[mysqld]
server-id1
log-binmysql-bin
方法二使用mysqld --log-bin文件存放路径/文件前缀
重新启动mysqld服务每周选择服务器负载较轻的时间段或者用户访问较少的时间段进行备份。
二、MySQL增量恢复
应用场景
1. 人为的SQL语句破坏了数据库
2. 在进行下一次全备之前发生系统故障导致数据库丢失
3. 在主从架构中主库数据发生了故障保证从库数据一致性
增量恢复的方法
1. 一般的恢复备份的二进制日志内容全部恢复
格式 mysqldbinlog [--no-defaults] 增量备份文件 | mysql -u用户名 -p密码
2. 基于时间点的恢复便于跳过某个发生错误的时间点实现数据恢复
格式从日志开头截止到某个时间点的恢复
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密码
3. 基于位置的恢复
可能在同一时间点既有错误的操作也有正确的操作基于位置进行恢复更加准确
格式
mysqlbinlog --stop-position‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码
mysqlbinlog --start-position‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码
三、制定企业备份策略的思路
1. 确定当前mysql是处于哪种表类型下工作的它们支持事物处理还是非事务的因为我们需要根据不同的特点来做一些设置。
2. 要选择备份的形式是完全备份还是增量备份它们各有优缺点
3. 为了保证恢复的完整性我们得开启binary log功能同时binlog给恢复工作也带来了很大的灵活性可以基于时间或是位置进行恢复。考虑到数据库性能我们可以将binlog文件保存到其他安全的硬盘中。
4. 正如最初所提到的备份操作和应用服务同时运行这样就十分消耗系统资源了会导致数据库服务性能下降这就要求我们选择一个合适的时间比如在应用负担很小的时候再来进行备份操作。
5. 不是备份完就完事大吉我们还得确认备份是否可用所以之后的恢复测试是完全有必要的。
根据数据更新频繁则应该较为频繁的备份数据重要则在有适当更新时进行备份在数据库压力小的时刻进行备份如一周一次完全备份然后每天进行增量备份中小公司全备一般可一天一次大公司可每周进行一次全备每天进行一次增量备份尽量为企业实现主从复制架构 四、MySQL企业备份案例
需求描述
北京移电通信公司的用户信息数据库为client用户资费数据表为user_info
请为该公司每周进行完全备份
每天为该公司进行增量备份
新增加的用户信息如表所示 安装mysqlyum 方式
[rootlocalhost ~]# vim /etc/my.cnf
[mysqld]
character-set-serverutf8对于中文支持
MariaDB [(none)] show variables like character_set_%;
[rootlocalhost ~]# /etc/init.d/mysqld start
添加数据库、表录入数据
[rootlocalhost ~]# mysql -uroot -p123456
mysql create database client;
Query OK, 1 row affected (0.00 sec)mysql use client;
Database changed
mysql create table user_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10)) DEFAULT CHARSETutf8;
Query OK, 0 rows affected (0.00 sec)mysql insert into user_info values(000000006,张三,男,016,10);
mysql insert into user_info values(000000007,李四,女,017,91);
mysql insert into user_info values(000000008,王五,女,018,23);mysql select * from user_info;
------------------------------------------------
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
------------------------------------------------
| 000000006 | 张三 | 男 | 016 | 10 |
| 000000007 | 李四 | 女 | 017 | 91 |
| 000000008 | 王五 | 女 | 018 | 23 |
------------------------------------------------
3 rows in set (0.00 sec)
先进行一次完全备份
[rootlocalhost ~]# mkdir /mysql_bak
[rootlocalhost ~]# mysqldump -uroot -p123456 client user_info /mysql_bak/client_userinfo-$(date %F).sql
[rootlocalhost ~]# mysqldump -uroot -p123456 --databases client /mysql_bak/client-$(date %F).sql
[rootlocalhost ~]# ls /mysql_bak/
client-2020-07-30.sql client_userinfo-2020-07-30.sql[rootlocalhost ~]# vim /etc/my.cnf
[mysqld]
server-id1
log-binmysql-bin[rootlocalhost ~]# /etc/init.d/mysqld restart
进行一次日志回滚生成新的二进制日志
[rootlocalhost ~]# ls /usr/local/mysql/data/
auto.cnf ibdata1 ibtmp1 mysql-bin.000001 performance_schema
client ib_logfile0 localhost.localdomain.pid mysql-bin.index sys
ib_buffer_pool ib_logfile1 mysql mysql.log
[rootlocalhost ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[rootlocalhost ~]# ls /usr/local/mysql/data/
auto.cnf ibdata1 ibtmp1 mysql-bin.000001 mysql.log
client ib_logfile0 localhost.localdomain.pid mysql-bin.000002 performance_schema
ib_buffer_pool ib_logfile1 mysql mysql-bin.index sys
继续录入新的数据
[rootlocalhost ~]# mysql -uroot -p123456
mysql use client;
mysql insert into user_info values(000000009,赵六,男,019,37);
mysql insert into user_info values(000000010,孙七,男,020,36);
mysql select * from user_info;
------------------------------------------------
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
------------------------------------------------
| 000000006 | 张三 | 男 | 016 | 10 |
| 000000007 | 李四 | 女 | 017 | 91 |
| 000000008 | 王五 | 女 | 018 | 23 |
| 000000009 | 赵六 | 男 | 019 | 37 |
| 000000010 | 孙七 | 男 | 020 | 36 |
------------------------------------------------
5 rows in set (0.00 sec)
进行增量备份
[rootlocalhost ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[rootlocalhost ~]# ls /usr/local/mysql/data/
auto.cnf ibdata1 ibtmp1 mysql-bin.000001 mysql-bin.index sys
client ib_logfile0 localhost.localdomain.pid mysql-bin.000002 mysql.log
ib_buffer_pool ib_logfile1 mysql mysql-bin.000003 performance_schema[rootlocalhost ~]# mv /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/
[rootlocalhost ~]# mysqlbinlog -v /mysql_bak/mysql-bin.000002#查看新操作的日志记录
模拟误操作删除user_info表
mysql drop table client.user_info;
Query OK, 0 rows affected (0.01 sec)mysql select * from client.user_info;
ERROR 1146 (42S02): Table client.user_info doesnt exist
恢复完全备份
[rootlocalhost ~]# mysql -uroot -p123456 client /mysql_bak/client_userinfo-2020-07-30.sqlmysql select * from client.user_info;
------------------------------------------------
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
------------------------------------------------
| 000000006 | 张三 | 男 | 016 | 10 |
| 000000007 | 李四 | 女 | 017 | 91 |
| 000000008 | 王五 | 女 | 018 | 23 |
------------------------------------------------
3 rows in set (0.00 sec)
恢复增量备份
[rootlocalhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql select * from client.user_info;
------------------------------------------------
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
------------------------------------------------
| 000000006 | 张三 | 男 | 016 | 10 |
| 000000007 | 李四 | 女 | 017 | 91 |
| 000000008 | 王五 | 女 | 018 | 23 |
| 000000009 | 赵六 | 男 | 019 | 37 |
| 000000010 | 孙七 | 男 | 020 | 36 |
------------------------------------------------
5 rows in set (0.00 sec)
基于时间点的增量备份恢复
mysql drop table client.user_info;
mysql select * from client.user_info;
ERROR 1146 (42S02): Table client.user_info doesnt exist[rootlocalhost ~]# mysql -uroot -p123456 client /mysql_bak/client_userinfo-2020-07-30.sql
mysql: [Warning] Using a password on the command line interface can be insecure.[rootlocalhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 -v
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#200730 16:10:02 server id 1 end_log_pos 123 CRC32 0x7d9089a5 Start: binlog v 4, server v 5.7.24-log created 200730 16:10:02
BINLOG
WoAiXw8BAAAAdwAAAHsAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AaWJkH0
/*!*/;
# at 123
#200730 16:10:02 server id 1 end_log_pos 154 CRC32 0x5b1f353d Previous-GTIDs
# [empty]
# at 154
#200730 16:14:26 server id 1 end_log_pos 219 CRC32 0x2814ab60 Anonymous_GTID last_committed0 sequence_number1 rbr_onlyyes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 219
#200730 16:14:26 server id 1 end_log_pos 293 CRC32 0x5db0af10 Query thread_id3 exec_time0 error_code0
SET TIMESTAMP1596096866/*!*/;
SET session.pseudo_thread_id3/*!*/;
SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1, session.autocommit1/*!*/;
SET session.sql_mode1436549152/*!*/;
SET session.auto_increment_increment1, session.auto_increment_offset1/*!*/;
/*!\C utf8 *//*!*/;
SET session.character_set_client33,session.collation_connection33,session.collation_server8/*!*/;
SET session.lc_time_names0/*!*/;
SET session.collation_databaseDEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#200730 16:14:26 server id 1 end_log_pos 359 CRC32 0x39d532e6 Table_map: client.user_info mapped to number 108
# at 359
#200730 16:14:26 server id 1 end_log_pos 424 CRC32 0xdb25adb2 Write_rows: table id 108 flags: STMT_END_FBINLOG
YoEiXxMBAAAAQgAAAGcBAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX/v7AwjPP48
/gzHhTmMtU5
YoEiXx4BAAAAQQAAAKgBAAAAAGwAAAAAAAEAAgAF/AJMDAwMDAwMDA5Bui1teWFrQPnlLcDMDE5
JQAAALKtJds
/*!*/;
### INSERT INTO client.user_info
### SET
### 1000000009
### 2赵六
### 3男
### 4019
### 537
# at 424
#200730 16:14:26 server id 1 end_log_pos 455 CRC32 0xd3cd7cbc Xid 10
COMMIT/*!*/;
# at 455
#200730 16:14:52 server id 1 end_log_pos 520 CRC32 0x7aefdd68 Anonymous_GTID last_committed1 sequence_number2 rbr_onlyyes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 520
#200730 16:14:52 server id 1 end_log_pos 594 CRC32 0x628d89c4 Query thread_id3 exec_time0 error_code0
SET TIMESTAMP1596096892/*!*/;
BEGIN
/*!*/;
# at 594
#200730 16:14:52 server id 1 end_log_pos 660 CRC32 0x5ec8e443 Table_map: client.user_info mapped to number 108
# at 660
#200730 16:14:52 server id 1 end_log_pos 725 CRC32 0x7301c8fd Write_rows: table id 108 flags: STMT_END_FBINLOG
fIEiXxMBAAAAQgAAAJQCAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX/v7AwjPP48
/gzHhRD5Mhe
fIEiXx4BAAAAQQAAANUCAAAAAGwAAAAAAAEAAgAF/AJMDAwMDAwMDEwBuWtmeS4gwPnlLcDMDIw
JAAAAP3IAXM
/*!*/;
### INSERT INTO client.user_info
### SET
### 1000000010
### 2孙七
### 3男
### 4020
### 536
# at 725
#200730 16:14:52 server id 1 end_log_pos 756 CRC32 0x55b5519b Xid 11
COMMIT/*!*/;
# at 756
#200730 16:16:35 server id 1 end_log_pos 803 CRC32 0x4dfaca3f Rotate to mysql-bin.000003 pos: 4
SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;只恢复赵六不恢复孙七
[rootlocalhost ~]# mysqlbinlog --no-defaults --stop-datetime2020-07-30 16:14:52 /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql select * from client.user_info;
------------------------------------------------
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
------------------------------------------------
| 000000006 | 张三 | 男 | 016 | 10 |
| 000000007 | 李四 | 女 | 017 | 91 |
| 000000008 | 王五 | 女 | 018 | 23 |
| 000000009 | 赵六 | 男 | 019 | 37 |
------------------------------------------------
4 rows in set (0.00 sec)只恢复孙七不恢复赵六
mysql drop table client.user_info;[rootlocalhost ~]# mysql -uroot -p123456 client /mysql_bak/client_userinfo-2020-07-30.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[rootlocalhost ~]# mysqlbinlog --no-defaults --start-datetime2020-07-30 16:14:52 /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql select * from client.user_info;
------------------------------------------------
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
------------------------------------------------
| 000000006 | 张三 | 男 | 016 | 10 |
| 000000007 | 李四 | 女 | 017 | 91 |
| 000000008 | 王五 | 女 | 018 | 23 |
| 000000010 | 孙七 | 男 | 020 | 36 |
------------------------------------------------
4 rows in set (0.00 sec)
基于位置的恢复
mysql drop table client.user_info;[rootlocalhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 -v
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#200730 16:10:02 server id 1 end_log_pos 123 CRC32 0x7d9089a5 Start: binlog v 4, server v 5.7.24-log created 200730 16:10:02
BINLOG
WoAiXw8BAAAAdwAAAHsAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AaWJkH0
/*!*/;
# at 123
#200730 16:10:02 server id 1 end_log_pos 154 CRC32 0x5b1f353d Previous-GTIDs
# [empty]
# at 154
#200730 16:14:26 server id 1 end_log_pos 219 CRC32 0x2814ab60 Anonymous_GTID last_committed0 sequence_number1 rbr_onlyyes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 219
#200730 16:14:26 server id 1 end_log_pos 293 CRC32 0x5db0af10 Query thread_id3 exec_time0 error_code0
SET TIMESTAMP1596096866/*!*/;
SET session.pseudo_thread_id3/*!*/;
SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1, session.autocommit1/*!*/;
SET session.sql_mode1436549152/*!*/;
SET session.auto_increment_increment1, session.auto_increment_offset1/*!*/;
/*!\C utf8 *//*!*/;
SET session.character_set_client33,session.collation_connection33,session.collation_server8/*!*/;
SET session.lc_time_names0/*!*/;
SET session.collation_databaseDEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#200730 16:14:26 server id 1 end_log_pos 359 CRC32 0x39d532e6 Table_map: client.user_info mapped to number 108
# at 359
#200730 16:14:26 server id 1 end_log_pos 424 CRC32 0xdb25adb2 Write_rows: table id 108 flags: STMT_END_FBINLOG
YoEiXxMBAAAAQgAAAGcBAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX/v7AwjPP48
/gzHhTmMtU5
YoEiXx4BAAAAQQAAAKgBAAAAAGwAAAAAAAEAAgAF/AJMDAwMDAwMDA5Bui1teWFrQPnlLcDMDE5
JQAAALKtJds
/*!*/;
### INSERT INTO client.user_info
### SET
### 1000000009
### 2赵六
### 3男
### 4019
### 537
# at 424
#200730 16:14:26 server id 1 end_log_pos 455 CRC32 0xd3cd7cbc Xid 10
COMMIT/*!*/;
# at 455
#200730 16:14:52 server id 1 end_log_pos 520 CRC32 0x7aefdd68 Anonymous_GTID last_committed1 sequence_number2 rbr_onlyyes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 520
#200730 16:14:52 server id 1 end_log_pos 594 CRC32 0x628d89c4 Query thread_id3 exec_time0 error_code0
SET TIMESTAMP1596096892/*!*/;
BEGIN
/*!*/;
# at 594
#200730 16:14:52 server id 1 end_log_pos 660 CRC32 0x5ec8e443 Table_map: client.user_info mapped to number 108
# at 660
#200730 16:14:52 server id 1 end_log_pos 725 CRC32 0x7301c8fd Write_rows: table id 108 flags: STMT_END_FBINLOG
fIEiXxMBAAAAQgAAAJQCAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX/v7AwjPP48
/gzHhRD5Mhe
fIEiXx4BAAAAQQAAANUCAAAAAGwAAAAAAAEAAgAF/AJMDAwMDAwMDEwBuWtmeS4gwPnlLcDMDIw
JAAAAP3IAXM
/*!*/;
### INSERT INTO client.user_info
### SET
### 1000000010
### 2孙七
### 3男
### 4020
### 536
# at 725
#200730 16:14:52 server id 1 end_log_pos 756 CRC32 0x55b5519b Xid 11
COMMIT/*!*/;
# at 756
#200730 16:16:35 server id 1 end_log_pos 803 CRC32 0x4dfaca3f Rotate to mysql-bin.000003 pos: 4
SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;只恢复赵六不恢复孙七
[rootlocalhost ~]# mysql -uroot -p123456 client /mysql_bak/client_userinfo-2020-07-30.sql
[rootlocalhost ~]# mysqlbinlog --no-defaults --stop-position520 /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql select * from client.user_info;
------------------------------------------------
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
------------------------------------------------
| 000000006 | 张三 | 男 | 016 | 10 |
| 000000007 | 李四 | 女 | 017 | 91 |
| 000000008 | 王五 | 女 | 018 | 23 |
| 000000009 | 赵六 | 男 | 019 | 37 |
------------------------------------------------
4 rows in set (0.00 sec)只恢复孙七不恢复赵六
mysql drop table client.user_info;[rootlocalhost ~]# mysql -uroot -p123456 client /mysql_bak/client_userinfo-2020-07-30.sql
[rootlocalhost ~]# mysqlbinlog --no-defaults --start-position520 /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql select * from client.user_info;
------------------------------------------------
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
------------------------------------------------
| 000000006 | 张三 | 男 | 016 | 10 |
| 000000007 | 李四 | 女 | 017 | 91 |
| 000000008 | 王五 | 女 | 018 | 23 |
| 000000010 | 孙七 | 男 | 020 | 36 |
------------------------------------------------
4 rows in set (0.00 sec)
