MySQL备份方案

mysql数据库的几种备份方案：

一、binlog二进制日志通常作为备份的重要资源，所以再说备份方案之前先总结一下binlog日志

1、binlog日志

1.1、引起mysql服务器改变的任何操作( 增 删 改 没有查)
1.2、复制功能依赖于此日志
1.3、slave服务器通过复制master服务器的二进制日志完成主从复制，在执行之前保存于中继日志(relay log)中
1.4、slave服务器通常可以关闭二进制日志以提升性能

2、binlog日志文件的文件表现形式

2.1、默认在安装目录下，存在mysql-bin.00001, mysql-bin.00002的二进制文件（binlog日志文件名依据my.cnf配置中的log-bin参数后面的设置为准）

[root@C7-15 ~]# vim /etc/my.cnf
..................
......
server-id=1            #开启二进制日志需要指定一个server-id
log-bin=mysql-bin      #开启二进制日志

保存

 二进制日志在什么时候会重新生成

[root@C7-15 ~]# systemctl restart mysql   #每次重启就会生成一个
[root@C7-15 ~]# systemctl restart mysql

[root@C7-15 ~]#  cd /usr/local/mysql/data/         
[root@C7-15 data]# ls           
auto.cnf            ib_logfile0         mysql-bin.000001    performance_schema/
gs/                 ib_logfile1         mysql-bin.000002    sys/
ib_buffer_pool      ibtmp1              mysql-bin.index     
ibdata1             mysql/              mysqld_safe.pid     

mysql> flush logs;                 #数据库中刷新日志 也会生成一个新日志
Query OK, 0 rows affected (0.00 sec)

2.2、还有mysql-bin.index用来记录被mysql管理的二进制文件列表
2.3、如果需要删除二进制日志时，切勿直接删除二进制文件，这样会使得mysql管理混乱

3、binlog日志文件查看相关mysql命令

3.1、查看正在使用的二进制文件

mysql> show master status;     
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.2、手动滚动二进制日志

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

滚动以后，mysql重新创建一个新的日志mysql-bin.000004

3.3、显示所有的二进制日志文件

mysql> show binary logs;            
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
4 rows in set (0.00 sec)

3.4、 以表的形式查看二进制文件

mysql> show binlog events in 'mysql-bin.000002'\G         
*************************** 1. row ***************************
   Log_name: mysql-bin.000002
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.12-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000002
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql-bin.000002
        Pos: 154
 Event_type: Rotate
  Server_id: 1
End_log_pos: 201
       Info: mysql-bin.000003;pos=4
3 rows in set (0.00 sec)

4、删除二进制日志文件

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

[root@C7-15 ~]# ls /usr/local/mysql/data/ |grep mysql-bin*
mysql-bin.000001
mysql-bin.index

5、MySQL二进制文件读取工具mysqlbinlog

MySQLbinlog日志结构

[root@C7-15 data]# mysqlbinlog mysql-bin.000001
mysqlbinlog: [Warning] option 'start-position': unsigned value 1 adjusted to 4
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211212 10:47:25 server id 1  end_log_pos 123 CRC32 0x43e3c4b5  Start: binlog v 4, server v 5.7.12-log created 211212 10:47:25 at startup
ROLLBACK/*!*/;
BINLOG '
vWK1YQ8BAAAAdwAAAHsAAAAAAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAC9YrVhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AbXE40M=
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0
ERROR: Could not read entry at offset 39: Error in log format or read error.
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*/;

 以上是没有进行操作查询出来的结果

注意： 需自己进行操作创建或删除

 以下是进行删除wwww库后查询结果

[root@C7-15 data]# mysqlbinlog --start-position='378' mysql-bin.000001
............
......
# at 378
#211216  1:38:23 server id 1  end_log_pos 463 CRC32 0xb125d9dd  Query   thread_id=2     
  exec_time=0     error_code=0
SET TIMESTAMP=1639589903/*!*/;
drop database wwww
/*!*/;
............
.....

事件位置	at 378
时间点	211216  1:38:23
服务器ID	server id 1
服务器ID主要用于标记日志产生的服务器，主要用于双主模型中，互为主从，确保二进制文件不会被相互循环复制
事件结束位置 end_log_pos也就是下一事件开始的位置	end_log_pos 463
记录类型	Query
线程号	thread_id = 2
语句的时间戳和写入二进制日志文件的时间差	exec_time=0
事件内容	drop database wwww /*!*/;
错误代码	error_code=0

如果出现乱码原因：可能是隔离级别的原因，默认隔离级别是READ-COMMITTED，所以将隔离修改为REPEATABLE-READ就好了

[root@C7-15 ~]# vim /etc/my.cnf
[mysqld]
.........
.......
...
transaction_isolation = REPEATABLE-READ
binlog_format=MIXED

保存退出

重启mysql    重启后创建或删除都可以查看到

5、二进制日志格式

由 bin_log_format={ statement | row | mixed } 定义

5.1、statement

基于语句，记录生成数据的语句 

缺点：在于如果当时插入信息为函数生成，有可能不同时间点执行结果不一样

5.2、row

 基于行数据

缺点：有时候数据量会过大

5.3、mixed

 混合模式

mysql自行决定何时使用statement, 何时使用row 模式

 5.4、查看当前二进制日志记录格式

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

  5.5、修改二进制日志记录格式

mysql> set session binlog_format=statement;          #修改二进制记录格式
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

 注意：5.7之前是statement，5.7之后是基于行的

6、二进制相关参数总结

log_bin = {ON|OFF}	可以是个文件路径，自定义binlog日志文件名，使用“log_bin=“或“log-bin=“都可以，主要用于控制全局binlog的存放位置和是否开启binlog日志功能 比如：log_bin=mysql-bin 或者 log-bin=mysql-bin，这样binlog日志默认会和mysql数据放在同一目录下
log_bin_trust_function_creators	是否记录在
sql_log_bin = {ON|OFF}	会话级别是否关闭binlog， 如果关闭当前会话内的操作将不会记录
sync_binlog	是否马上同步事务类操作到二进制日志中
binlog_format = {statement|row|mixed}	二进制日志的格式，上面单独提到了
max_binlog_cache_size =	二进制日志缓冲空间大小，仅用于缓冲事务类的语句
max_binlog_stmt_cache_size =	语句缓冲，非事务类和事务类共用的空间大小
max_binlog_size =	二进制日志文件上限，超过上限后则滚动

建议：将binlog日志实时备份到远程设备上，以防出现机器故障进行数据恢复；如果存放在一台机器可能数据将无法恢复

二、binlog二进制日志备份和恢复

1、备份数据的重要性：

①、灾难恢复；②、审计，数据库在过去某一个时间点是什么样的；③、测试

2、备份的目的：

①、用于恢复数据；②、备份结束后，需要周期性的做恢复测试

3、备份类型：

根据备份时，MySQL服务器是否在线
冷备(cold backup)	服务器离线，读写操作都不能进行