|NO.Z.00091|——————————|^^ 操作 ^^|——|Linux&MySQL.V06|——|日志备份|

一、mysql-binlog 日志备份：

### --- 所选时间段一定要完整包含所有动作（可以在原来基础上稍微增加点时间）

~~~     按文件大小还原：还原到 bb 库被删除的数据状态
~~~     查看 bb 库被删除前后的文件大小

### --- mysql-binlog 日志备份：

~~~     #二进制日志（log-bin 日志）：
~~~     所有对数据库状态更改的操作（create、drop、update 等）
~~~     修改 my.cnf 配置文件开启 binlog 日志记录功能

### --- 按文件大小还原：

--start-position
--stop-position

### --- 按时间还原：
--start-datetime
--stop-datetime

~~~     格式：mysqlbinlog --start-datetime ‘YY-MM-DD HH:MM:SS’ --stop-datetime ‘YY-MM-DD
~~~     HH:MM:SS’ 二进制日志 | mysql -uroot -p

### --- 还没有日志记录

[root@server21 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'mysql-bin.000001' not found (Errcode: 2)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

 

[root@server21 ~]# mysql -uroot -p123123
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)
mysql> use abc;
Database changed
mysql> create table xxx (id int,name char(10));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into xxx values (1,'aaaa'),(2,'bbbb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from xxx
    -> Ctrl-C -- exit!
Aborted

[root@server21 ~]# mysql -uroot -p123123
mysql> use abc;
Database changed
mysql> select * from xxx;
+------+------+
| id   | name |
+------+------+
|    1 | aaaa |
|    2 | bbbb |
+------+------+
2 rows in set (0.00 sec)

[root@server21 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
create database abc
/*!*/;
# at 187
#210204 16:19:05 server id 1  end_log_pos 288   Query   thread_id=2 exec_time=0 error_code=0
use `abc`/*!*/;
SET TIMESTAMP=1612426745/*!*/;
create table xxx (id int,name char(10))
/*!*/;
# at 288
#210204 16:20:11 server id 1  end_log_pos 394   Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1612426811/*!*/;
insert into xxx values (1,'aaaa'),(2,'bbbb')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

---

二、mysql-binlog 日志备份示例：

### --- 开启二进制日志

[root@server21 ~]# service mysqld stop
[root@server21 ~]# vim /etc/my.cnf
log-bin=mysql-bin                                           // 启动二进制日志
[root@server21 ~]# service mysqld start

### --- 查看二进制日志文件
~~~     会生成一个这样的文件，该日志只记录数据的增删改，不记录查询的数据

[root@server21 ~]# ls /var/lib/mysql/
mysql-bin.000001                                            

### --- 按时间还原：
~~~     如果数据库中的 bb 库被删，需要还原
### --- 模拟数据丢失

[root@server21 ~]# mv /var/lib/mysql/abc/ /tmp/
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

 

### --- 按照二进制日志的方式恢复；找到创建数据库的时间点，再确定数据库删除的最后时间点

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

 

mysql> drop database abc;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

### --- 查看二进制日志：    #找到两个at之间的时间节点。

[root@server21 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
#210204 16:19:05 server id 1  end_log_pos 288   Query   thread_id=2 exec_time=0 error_code=0
use `abc`/*!*/;
SET TIMESTAMP=1612426745/*!*/;
create table xxx (id int,name char(10))
/*!*/;
# at 288
#210204 16:20:11 server id 1  end_log_pos 394   Query   thread_id=2 exec_time=0 error_code=0

~~~     数据库写入的时间点

SET TIMESTAMP=1612426811/*!*/;
insert into xxx values (1,'aaaa'),(2,'bbbb')                
/*!*/;
# at 394
#210204 16:18:27 server id 1  end_log_pos 475   Query   thread_id=2 exec_time=951   error_code=0
SET TIMESTAMP=1612426707/*!*/;
create database abc
/*!*/;
# at 475
#210204 16:18:27 server id 1  end_log_pos 556   Query   thread_id=2 exec_time=1132  error_code=0
SET TIMESTAMP=1612426707/*!*/;
create database abc
/*!*/;
# at 556
#210204 16:42:36 server id 1  end_log_pos 635   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1612428156/*!*/;

~~~     数据库删除的时间点

mysql> drop database abc    

### --- 按时间点查看数据库

[root@server21 ~]# mysqlbinlog --start-datetime "2021-02-04 16:15:46" --stop-datetime "2021-02-04 16:43:36" /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use abc;
Database changed
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| xxx           |
+---------------+
1 row in set (0.00 sec)

mysql> select * from xxx;
+------+------+
| id   | name |
+------+------+
|    1 | aaaa |
|    2 | bbbb |
+------+------+
2 rows in set (0.00 sec)

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---