|
1.通过什么恢复mysql数据？ 由于MySQL二进制日志文件可能在灾难性的数据丢失时帮助恢复数据，它已经成为重要的管理领域。
2.mysql的binlog是什么？
binlog是MySQL的二进制日志文件，它可以用于记录MySQL服务器上的所有增删改（查询不会被记录），可以使用binlog进行数据恢复。默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看mysqlbinlog--base64-output=decode-rows -v/var/lib/mysql/mysql-bin.000001
3.启用mysql二进制日志文件的方式【数据库规划部署阶段】 方式1：修改mysql配置文件my.cnf[mysqld]log-bin：指定binlog文件名和路径，mysql每一个实例对应一个binlog日志路径，默认值为mysql-bin.log.server-id：服务器id，mysql服务器得唯一id，多个模式的的binlog日志需要不同的id；binlog_format：指定binlog的识别格式，有三种格式，row，statement和mixed；expire-logs-days：指定binlog文件的有效时限，默认为0，代表永不过期；binlog_cache_size：在不同的会话之间共享的binlog缓冲大小，此参数影响事务提交速度，默认值64K；binlog_do_db：此参数表示只记录指定数据库的二进制日志，默认全部记录。binlog_ignore_db：此参数表示不记录指定的数据库的二进制日志。{这两个参数为互斥关系，一般只选择其一设置，只能在启动命令行中或配置文件中加入。指定多个数据库要分行写入，举例如下：# 指定 db1 db2 记录binlog[mysqld]binlog_do_db = db1binlog_do_db = db2# 不让 db3 db4 记录binlog[mysqld]binlog_ignore_db = db3binlog_ignore_db = db4}max_binlog_cache_size：指定binlog缓存的最大值，多少条一次提交，越大，提交消耗资源越多；binlog_row_image：指定binlog日志行复制级别，默认为FULL，表示记录更新前和更新后的记录；binlog-gtid-simple-recovery：支持binlog的基于GTID的快速同步
配置结束以后需要重启mysql数据库如：systemctl restart mysqld
方式2：通过mysql命令行进行设置# 打开binlogset global log_bin=ON;# 设置保存日志的位置SET GLOBAL log_bin_basename = /var/log/mysql-bin;SET GLOBAL log_bin_index = /var/log/mysql-bin.index;# 设置识别格式set global binlog_format="ROW";# 设置保存期限set global expire_logs_days=90;# 设置binlog文件的最大大小 set global max_binlog_size=1G;# 设置binlog缓存的大小set global binlog_cache_size=4M;# 要忽略日志的数据库set global binlog_ignore_db="db1";# 要记录日志的库set global binlog_do_db="db2";
# 执行FLUSH LOGS命令：FLUSH LOGS；# 查看开启的日志文件SHOW MASTER LOGS；
4.通过binlog恢复数据的详细步骤 1.现有数据的备份【临时数据库部署在其他服务器上】（1）首先应对当前的binlog进行压缩备份，执行以下操作： mysqldump --all-databases --master-data=2 > databack.sql这个命令用来备份binlog文件，将数据库中的所有数据（包括binlog）备份到文件databack.sql.（2）恢复到临时数据库中，执行以下操作：mysql -u root -p < databack.sql这个命令用来恢复databack.sql中的数据到临时数据库。
2.丢失数据的恢复过程：（1）确定当前使用的日志文件及位置，使用 SHOW MASTER STATUS命令，该命令会返回文件名和对应的序号mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 3001| | |+------------------+----------+--------------+------------------+[注：也可以通过查看binlog文件中的sql语句确定要恢复数据的时间段][或者show binlog events in 'mysql-bin.000001';也可以判断要恢复发位置区间]
（2）使用mysqlbinlog命令查看binlog信息，执行以下操作：mysqlbinlog --database=mydatabase --start-position=1000 --stop-position=3000 mysql-bin.000001这个命令用来查看指定的binlog文件中从1000到3000位置的内容，即查看指定位置的binlog信息。
（3）使用mysqlbinlog命令恢复binlog信息，执行以下操作：mysqlbinlog --database=mydatabase --start-position=1000 --stop-position=3000 mysql-bin.000001 | mysql -u root -p这个命令用来恢复mysql-bin.000001指定位置1000到3000的binlog信息到数据库中。[注：假如mysql没做主从，使用--start-datetime会报错，可以使用--start-position ]
演示实验 [注意：在生产环境下会有数据库的多种容灾备份机制，可以通过多种方式恢复数据，本次演示为单机的数据库实验环境，只为演示通过binlog恢复数据的过程。] 环境：mysql数据库单机部署，无主从。 1.数据库规划搭建阶段 搭建数据库：（1）单机部署mysql：安装过程省略...其中binlog配置如下：[mysqld]log-bin = /var/lib/mysql/mysql_binlog_devbinlog_format = mixedexpire-logs-days = 0binlog_cache_size = 4mbinlog-do-db = km_db01binlog-do-db = km_db02max_binlog_cache_size = 1G
重启mysql服务
（2）创建数据库数据库：KM_db01，并在该数据库下创建表user_baseinfo;create database if not exists km_db01 character set utf8mb4 collate utf8mb4_bin;create table if not exists km_db01.user_baseinfo (id int(10) primary key auto_increment,username varchar(20),password varchar(50),age int(10),sex varchar(20),reg varchar(20));grant all privileges on km_db01.* to kmops@'%' identified by 'km123456';flush privileges;show master status;
数据库：KM_db02,并在该数据库下创建表user_moreinfo;create database if not exists km_db02 character set utf8mb4 collate utf8mb4_bin;create table if not exists km_db02.user_moreinfo (id int(10) primary key auto_increment,username varchar(20),phonenumber varchar(50),login_times int(10));grant all privileges on km_db02.* to kmops@'%' identified by 'km123456';flush privileges;show master status;
（3）模拟日常数据写入insert into km_db01.user_baseinfo (username,password,age,sex,reg) values ("小明","pashdsg",22,"男","昆明");...insert into km_db02.user_moreinfo (username,phonenumber,login_times) values ("小明","18727928798",100);...
数据现状：
MariaDB [(none)]> select * from km_db01.user_baseinfo;+----+----------+----------+------+------+--------+| id | username | password | age | sex | reg |+----+----------+----------+------+------+--------+| 1 | 小明 | pashdsg | 22 | 男 | 昆明 |+----+----------+----------+------+------+--------+1 row in set (0.000 sec)
MariaDB [(none)]> select * from km_db02.user_moreinfo;+----+----------+-------------+-------------+| id | username | phonenumber | login_times |+----+----------+-------------+-------------+| 1 | 小明 | 18727928798 | 100 |+----+----------+-------------+-------------+
2.数据丢失、恢复数据阶段 运维人员A离职走了，业务部门收到消息部分用户的数据都清空了，让另外一个运维人员B去排查问题，最终发现km_db01和km_db02两个数据库被删了,使用的命令为：drop database km_db01;drop database km_db03; drop database km_db01;drop database km_db02;
MariaDB [(none)]> select * from km_db01.user_baseinfo;ERROR 1146 (42S02): Table 'km_db01.user_baseinfo' doesn't existMariaDB [(none)]> select * from km_db02.user_moreinfo;ERROR 1146 (42S02): Table 'km_db02.user_moreinfo' doesn't exist
     
[开始干活] （1）先确定当前的二进制文件路径及位置positionroot用户登录mysql，使用show master status查看；
MariaDB [(none)]> show master status;+-------------------------+----------+-----------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------------+----------+-----------------+------------------+| mysql_binlog_dev.000001 | 2578 | km_db01,km_db02 | |+-------------------------+----------+-----------------+------------------+1 row in set (0.000 sec)
（2）先备份所有的binlog日志mysqlbinlog /var/log/mysql/mysql_binlog_dev.000001 >dbback.sql
（3）mysqlbinlog 查看日志文件，确定数据丢失的区间（position或者时间）（3.1）通过看日志查找：mysqlbinlog /var/log/mysql/mysql_binlog_dev.00000发现drop命令前最后一次修改数据在end=2312,数据库at 377开始创建，所以恢复的position区间为[377-2312]mysqlbinlog --start-position=377 --stop-position=2312 /var/log/mysql/mysql_binlog_dev.00000（3.2推荐）或者使用show binlog events in 'mysql_binlog_dev.000001'查看：MariaDB [(none)]> show binlog events in 'mysql_binlog_dev.000001';+-------------------------+------+-------------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+-------------------------+------+-------------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| mysql_binlog_dev.000001 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.38-MariaDB-0ubuntu0.20.04.1-log, Binlog ver: 4 || mysql_binlog_dev.000001 | 256 | Gtid_list | 1 | 285 | [] || mysql_binlog_dev.000001 | 285 | Binlog_checkpoint | 1 | 335 | mysql_binlog_dev.000001 || mysql_binlog_dev.000001 | 335 | Gtid | 1 | 377 | GTID 0-1-1 || mysql_binlog_dev.000001 | 377 | Query | 1 | 526 | create database if not exists km_db01 character set utf8mb4 collate utf8mb4_bin || mysql_binlog_dev.000001 | 526 | Gtid | 1 | 568 | GTID 0-1-2 || mysql_binlog_dev.000001 | 568 | Query | 1 | 806 | create table if not exists km_db01.user_baseinfo (id int(10) primary key auto_increment,username varchar(20),password varchar(50),age int(10),sex varchar(20),reg varchar(20)) || mysql_binlog_dev.000001 | 806 | Gtid | 1 | 848 | GTID 0-1-3 || mysql_binlog_dev.000001 | 848 | Query | 1 | 998 | grant all privileges on km_db01.* to kmops@'%' identified by 'km123456' || mysql_binlog_dev.000001 | 998 | Gtid | 1 | 1040 | GTID 0-1-4 || mysql_binlog_dev.000001 | 1040 | Query | 1 | 1189 | create database if not exists km_db02 character set utf8mb4 collate utf8mb4_bin || mysql_binlog_dev.000001 | 1189 | Gtid | 1 | 1231 | GTID 0-1-5 || mysql_binlog_dev.000001 | 1231 | Query | 1 | 1448 | create table if not exists km_db02.user_moreinfo (id int(10) primary key auto_increment,username varchar(20),phonenumber varchar(50),login_times int(10)) || mysql_binlog_dev.000001 | 1448 | Gtid | 1 | 1490 | GTID 0-1-6 || mysql_binlog_dev.000001 | 1490 | Query | 1 | 1640 | grant all privileges on km_db02.* to kmops@'%' identified by 'km123456' || mysql_binlog_dev.000001 | 1640 | Gtid | 1 | 1682 | GTID 0-1-7 || mysql_binlog_dev.000001 | 1682 | Query | 1 | 1761 | flush privileges || mysql_binlog_dev.000001 | 1761 | Gtid | 1 | 1803 | BEGIN GTID 0-1-8 || mysql_binlog_dev.000001 | 1803 | Intvar | 1 | 1835 | INSERT_ID=1 || mysql_binlog_dev.000001 | 1835 | Query | 1 | 2009 | insert into km_db01.user_baseinfo (username,password,age,sex,reg) values ("小明","pashdsg",22,"男","昆明") || mysql_binlog_dev.000001 | 2009 | Xid | 1 | 2040 | COMMIT /* xid=66 */ || mysql_binlog_dev.000001 | 2040 | Gtid | 1 | 2082 | BEGIN GTID 0-1-9 || mysql_binlog_dev.000001 | 2082 | Intvar | 1 | 2114 | INSERT_ID=1 || mysql_binlog_dev.000001 | 2114 | Query | 1 | 2281 | insert into km_db02.user_moreinfo (username,phonenumber,login_times) values ("小明","18727928798",100) || mysql_binlog_dev.000001 | 2281 | Xid | 1 | 2312 | COMMIT /* xid=67 */ || mysql_binlog_dev.000001 | 2312 | Gtid | 1 | 2354 | GTID 0-1-10 || mysql_binlog_dev.000001 | 2354 | Query | 1 | 2445 | drop database km_db01 || mysql_binlog_dev.000001 | 2445 | Gtid | 1 | 2487 | GTID 0-1-11 || mysql_binlog_dev.000001 | 2487 | Query | 1 | 2578 | drop database km_db02 |+-------------------------+------+-------------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+29 rows in set (0.000 sec)
（4）mysqlbinlog 恢复操作mysqlbinlog --no-defaults --start-position=377 --stop-position=2312 /var/lib/mysql/mysql_binlog_dev.000001 |mysql -uroot -p（5）检查数据完整性MariaDB [(none)]> select * from km_db01.user_baseinfo;+----+----------+----------+------+------+--------+| id | username | password | age | sex | reg |+----+----------+----------+------+------+--------+| 1 | 小明 | pashdsg | 22 | 男 | 昆明 |+----+----------+----------+------+------+--------+1 row in set (0.000 sec)
MariaDB [(none)]> select * from km_db02.user_moreinfo;+----+----------+-------------+-------------+| id | username | phonenumber | login_times |+----+----------+-------------+-------------+| 1 | 小明 | 18727928798 | 100 |+----+----------+-------------+-------------+1 row in set (0.000 sec)
数据已恢复
|
