MariaDB日志文件、备份与恢复

1. 数据库的6种日志

       数据库有6种日志，分别是：查询日志、慢查询日志、错误日志、二进制日志、中继日志以及事务日志。

1> 查询日志

　　查询日志记录每一条sql语句，建议不开启，因为如果访问量较大，会占用相当大的资源，影响性能。

查询日志的开启：

       编辑配置文件：/etc/my.cnf.d/server.cnf

vim /etc/my.cnf.d/server.cnf
general_log = ON| OFF                 #查询日志开关，开为1，关为0
general_log_file localhost.log        #查询日志的文件名字（/var/lib/mysql），可以是绝对路径，也可以是相对路径，如果是绝对路径则生成绝对路径下的日志文件文件；如果是相对路径，则默认生成/var/lib/mysql下的路径
log_output TABLE | FILE | NONE        #查询日志的存储形式，有三种形式，table是以表的方式存储，file是以文件的方式，none是使用两种方式存储

示例：

[root@alph ~]# vim /etc/my.cnf.d/server.cnf
[server]
general_log=1                     #开启查询日志
general_log_file=localhost.log    #日志文件名，自定义
# this is only for the mysqld standalone daemon

保存退出，重启数据库，进入数据库查看日志查询方式
MariaDB [(none)]> show variables like '%general%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | ON            |
| general_log_file | localhost.log |
+------------------+---------------+
2 rows in set (0.001 sec)
[root@alph ~]# cd /var/lib/mysql/
[root@alph mysql]# ls
............ localhost.log
[root@alph mysql]# tail -f localhost.log     #已经开始监控
/usr/sbin/mysqld, Version: 10.3.16-MariaDB-log (MariaDB Server). started with:
Tcp port: 0  Unix socket: (null)
Time                Id Command  Argument
/usr/sbin/mysqld, Version: 10.3.16-MariaDB-log (MariaDB Server). started with:
Tcp port: 0  Unix socket: (null)
Time                Id Command  Argument
190803  2:53:53      8 Connect  root@localhost as anonymous on
                     8 Query    select @@version_comment limit 1
190803  2:53:58      8 Query    show variables like '%general%'
190803  2:59:43      8 Quit
#监控到的sql语句
可以看到，查询日志会记录每一条操作过得SQL语句

2> 慢查询日志

       执行时长超出指定时间的查询操作（测试命令:select sleep(4);）（超出指定的时长后才记录下来）。一般企业都将慢查询日志打开，用来优化SQL语句

 慢查询日志的开启：

       同样的，编辑配置文件/etc/my.cnf.d/server.cnf

slow_query_log = OFF|ON                      #开启慢查询日志
slow_query_log_file = LOCALHOST-SLOW.log     #慢查询日志的文件路径，默认在/var/lib/mysql，文件名默认为LOCALHOST-SLOW.log，localhost为主机名。
long_query_time                              #慢查询时长；默认是10s，超出10s将会被记录
log_slow_rate_limit                          #如果要记录的慢查询日志非常多的话，会按照速率来记录，默认1秒记录一个
log_slow_verbosity=1|0                       #记录的详细级别，1代表详细，0代表不太详细；

例如： 

[root@alph ~]# vim /etc/my.cnf.d/server.cnf
[serve
#general_log=1
#general_log_file=localhost.log
slow_query_log=1                   #开启详细的慢查询
long_query_time=4                  #超出时长为4秒，四秒后开始记录

没有指定路径和文件，默认为/var/lib/mysql/ LOCALHOST-SLOW.log
保存退出，重启生效
[root@alph ~]# systemctl restart mariadb
[root@alph mysql]# ls -ltr
-rw-rw---- 1 mysql mysql      142 Aug  3 16:53 alph-slow.log

进入数据库查看，同时打开交互界面进行监控日志
执行一个模拟5秒的SQL语句，等待tail -f监控文件内容
MariaDB [mysql]> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
[root@alph mysql]# tail -f alph-slow.log
/usr/sbin/mysqld, Version: 10.3.16-MariaDB-log (MariaDB Server). started with:
Tcp port: 0  Unix socket: (null)
Time                Id Command  Argument
# Time: 190803 17:00:31
# User@Host: root[root] @ localhost []
# Thread_id: 8  Schema: mysql  QC_hit: No
# Query_time: 5.000527  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 63
use mysql;
SET timestamp=1564822831;
select sleep(5);

3> 错误日志

　　错误日志记录mysqld启动和关闭过程中输出的事件信息，mysqld运行中产生的错误信息。同样的，配置文件为/etc/my.cnf.d/server.cnf

event scheduler                       #运行一个event时产生的日志信息，在主从复制架构中的从服务器上启动从服务器线程时产生的信息
log_error = /var/log/mysql_error.log  #指定错误日志的输出位置，该路径必须具有mysql权限（不指定路径默认放在/var/lib/mysql/）
log_warnings                          #为0， 表示不记录告警信息。为1， 表示告警信息写入错误日志。大于1， 表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志。(默认为2)

[root@alph ~]# vim /etc/my.cnf.d/server.cnf
[server]
#general_log=1
#general_log_file=localhost.log
#slow_query_log=1
#long_query_time=4
log_error = /var/lib/mysql/mysql_error.log
保存退出，重启生效
[root@alph ~]# systemctl restart mariadb
[root@alph ~]# cd /var/lib/mysql/
[root@alph mysql]# ls -ltr
-rw-rw---- 1 mysql mysql     1883 Aug  3 17:22 mysql_error.lo
[root@alph mysql]# tail -f mysql_error.log

在交互界面重新启动一下数据库，这些信息都会被记录到该日志当中
MariaDB [(none)]> exit
Bye
[root@alph ~]# mysql -uroot -p123
root@alph mysql]# tail -f mysql_error.log
2019-08-03 17:22:12 0 [Note] InnoDB: Waiting for purge to start
2019-08-03 17:22:12 0 [Note] InnoDB: 10.3.16 started; log sequence number 1630860; transaction id 21
2019-08-03 17:22:12 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-08-03 17:22:12 0 [Note] InnoDB: Buffer pool(s) load completed at 190803 17:22:12
2019-08-03 17:22:12 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-08-03 17:22:12 0 [Note] Server socket created on IP: '::'.
2019-08-03 17:22:12 0 [Note] Reading of all Master_info entries succeeded
2019-08-03 17:22:12 0 [Note] Added new Master_info '' to hash table
2019-08-03 17:22:12 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.16-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
 
查看当前错误日志的报警级别
MariaDB [(none)]> show variables like '%warning%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 2     |               #默认为2
| sql_warnings  | OFF   |
| warning_count | 0     |
+---------------+-------+
3 rows in set (0.001 sec)

4> 二进制日志

　　二进制日志是MariaDB最重要的日志，针对时间点还原起着至关重要的作用，可以根据时间点还原数据。所有的增删改操作都会被记录到二进制日志当中。

log_bin = OFF | ON                      　　   #二进制日志开关
log_bin_basename = /var/lib/mysql/mysql-bin   #日志路径，默认在/var/lib/mysql下
binlog_format=STATEMENT|ROW|MIXED  　　　　　　 #二进制记录格式
　　STATEMENT：基于“语句”记录，记录SQL语句，占用空间小，但是当数据库有该日志记录的语句（如数据库）的时候，回复数据时是会报错、失败
　　ROW：基于“行”记录，记录的是每一行的内容，对于大量的记录来说占用空间较大，但记录较精准
　　MIXED：混合模式，让系统自行判定该基于哪种方式进行，现在采用的基本都是这种混合模式
sql_log_bin=1|0        　　　　　　　　　　　　　　#是否启用二进制日志，或者指定文件名如mysql-bin
log_bin_index=PATH   　　　　　　　　　　　　　　　#二进制日志索引位置，默认在/var/lib/mysql下,myssql-bin.index，记录二进制日志文件的路径，该index不属于二进制文件，可以用cat或者vim打开，如：
        [root@alph mysql]# cat mysql-bin.index
　　　　　./mysql-bin.000001
　　　　　sync_binlog=1|0                       #设定是否启动二进制日志同步功能。由于磁盘写入文件较慢，开启同步则每记录一条既写入磁盘，对数据库的性能以及磁盘io来说是有影响的
　　　　　max_binlog_size=SIZE                  #单个二进制文件最大体积，默认为1G，超过设定大小就会滚动到另一个增加的二进制文件
　　　　　expire_logs_days=0                    #消除日志参数，超过多少天就清除二进制日志，默认为0,代表不启用此功能，二进制日志不清除会一直生成并保存

[root@alph mysql]# vim /etc/my.cnf.d/server.cnf
[server]
#general_log=1
#general_log_file=localhost.log
#slow_query_log=1
#long_query_time=4
#log_error = /var/lib/mysql/mysql_error.log
log_bin=mysql-bin                      　　   #开启二进制日志
保存退出，重启生效
[root@alph mysql]# ls -ltr
-rw-rw---- 1 mysql mysql      328 Aug  3 17:41 mysql-bin.000001

查看二进制日志：

MariaDB [(none)]> show master status;        #当前所使用的二进制文件
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> show master logs;         #所有的二进制文件
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.000 sec)

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name']     #二进制文件记录的内容
show master status;

二进制日志记录的是增删改操作，先创建一个表，在查看状态

MariaDB [(none)]> create database testdb;
MariaDB [(none)]> ues testdb;
MariaDB [testdb]> create table classes (id tinyint unsigned primary key,name varchar(20));
MariaDB [testdb]> show master status;  #position数据开始变化，二进制日志mysql-bin.000001开始记录数据
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      643 |              |                  |
+------------------+----------+--------------+------------------+

查看二进制日志文件内容：

       由于该日志是二进制形式，因此无法用cat或者vim命令来查看，可以用mysql提供的mysqlbinlog来查看指定的二进制日志文件

[root@alph mysql]# mysqlbinlog mysql-bin.000001
..........                                        #增删改均会被记录
create database testdb
/*!*/;
# at 461
#190803 18:07:55 server id 1  end_log_pos 503 CRC32 0x8ca3d982      GTID 0-1-2 ddl
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
# at 503
#190803 18:07:55 server id 1  end_log_pos 643 CRC32 0x3490771f      Query       thread_id=9  exec_time=0 error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1564826875/*!*/;
create table classes (id tinyint unsigned primary key,name varchar(20))
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog *//*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

二进制文件的滚动的三种情况：

　　1）flush logs：手动滚动

　　2)  文件超出指定大小

　　3)  service mariadb restart

　　每滚动一次，日志查询show master log 中的mysql-bin会增1

MariaDB [testdb]> flush logs;
Query OK, 0 rows affected (0.003 sec)

MariaDB [testdb]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       690 |
| mysql-bin.000002 |       385 |
+------------------+-----------+
MariaDB [testdb]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      385 |              |                  |
+------------------+----------+--------------+------------------+
[root@alph mysql]# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002

二进制日志格式:

190613 14:17:32 server id 1  end_log_pos 666 CRC32 0xeb1cde6b  Query   thread_id=9     exec_time=0       error_code=0
use `testdb`/*!*/;

　　事件发生的日期和时间：190613 14:17:32，19-06-13 14:17:32；

　　事件发生的服务器标识：server id 1，主从架构同步时将按照server-id来同步；

　　事件的结束位置：end_log_pos 666；

　　事件的类型：Query；

　　事件发生时所在服务器执行此事件的线程ID：thread_id=9；

　　语句的时间戳与将其写入二进制文件中的时间差：exec_time=0，记录SQL语句所用的时间，一般不为0；

　　错误代码：error_code=0；

　　事件内容：GTID:Global Transaction ID;

       专属属性：GTID

 客户端命令工具mysqlbinlog：

　　mysqlbinlog [options] log_file ...

--start-datetime=      
       --stop-datetime=       #基于时间点恢复。从start到stop时间点之间的内容将会被恢复
--start-position=
       --stop-position=       #基于position点恢复。从start到stop之间的内容将会被恢复

5> 中继日志

　　复制架构中，备服务器用于保存主服务器的二进制日志中读取到的事件。

6> 事务日志

       事务日志transaction log，有两个文件：ib_logfile0，ib_logfile1。事务日志一般不需要手动的管理，由事务型存储引擎自行管理和使用(Innodb,myisam引擎是不支持事务，外键，行级锁)；

　　数据库执行的所有事务都会写入到事务日志当中，事务里进行的增删改等操作先记录到事务日志当中，再写入到数据库。由此带来的问题是由于先将数据写入事务日志当中，当查询数据库时有可能查询不到。同样的，在读取事务日志的内容的同时又读不到原磁盘的内容。事务日志的数据加上磁盘里的数据才是完整的数据，数据库的解决办法是划分内存的一块区域buffer_pool（缓冲池），将事务日志数据可磁盘数据放入内存，直接调用内存。buffer_pool的大小影响数据库的性能。

[root@alph mysql]# ll
-rw-rw---- 1 mysql mysql 50331648 Aug  3 18:07 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Aug  2 22:54 ib_logfile1

innodb_buffer_pool_size                 #一般在没有其他业务的情况下设置成为物理内存的3/4,或者4/5
innodb_log_files_in_group = 2           #事务日志文件的个数，默认为2个事务日志文件，数据交替写入
innodb_log_file_size = 50331648（48M）   #事务日志文件的单个大小48M
innodb_log_group_home_dir = ./          #事务日志文件的所在路径，默认就在mariadb的数据目录/var/lib/mysql

事务型存储引擎自行管理和使用事务日志，

　　redo log：重做日志； undo log：撤销日志。

2. 数据库的备份     

1> 为什么要备份：

       灾难恢复：硬件故障，软件故障，自然灾害，黑客攻击，误操作；测试

2> 备份要注意的点：

• 备份需要多少时间：需要估算出来；
• 能容忍最多丢失多少数据；
• 恢复数据需要在多长时间内完成；
• 需要恢复哪些数据。

      数据库备份完成后要做还原测试，定期的去做还原演练，用于测试备份的可用性。

3> 备份类型：

　　a)   完全备份和部分备份：

　　　　i. 完全备份：整个数据集（整个库的整个表）；

　　　　ii.部分备份：只备份数据子集；

　　b)   完全备份、增量备份、差异备份：

              i. 增量备份：仅备份最近一次完全备份或增量备份（如果有增量备份）以来变化的数据；

              ii. 差异备份：仅备份最近一次完全备份以来变化的数据。

　　c)  热备、温备、冷备

               i.  热备：读写操作均可执行（不影响业务的操作）；

               ii. 温备：读操作可以、写不行（需要将整个库的整个表锁住）；

               iii.冷备：读写操作均不能执行（停掉业务）；

•                iv. MyISAM：温备，不能热备；

 　　d) 物理备份、逻辑备份

　　　　 i.  物理备份：直接复制数据文件进行备份，与存储引擎无关（如cp，备份的时候要注意不能有数据插入）；

　　　　ii.   逻辑备份：从数据库中“导出”数据库另存而进行备份（如二进制日志、Mysqldump备份的数据，结构不变）。

4> 备份时需要考虑的因素：

　　1) 持续多久；

　　2) 备份过程的时长；

　　3) 备份负载；

　　4) 恢复过程的时长。

 5> 备份什么

　　1) 数据；

　　2)  二进制日志（基于时间点还原），innodb的事务日志；

　　3) 代码（存储过程，存储函数，触发器，时间调度器）；

　　4) 服务器的配置文件。

6> 设计备份方案：

　　1) 数据集：完全+增量+二进制日志 | 完全+差异+二进制日志（最大量的恢复，接近100%）；

　　2) 备份手段：物理，逻辑；

　　3) 对于备份较大的数据建议物理备份，对于较小的数据建议用逻辑备份。

7> 备份工具的选择：

       1）mysqldump+复制binlog：

              mysqldump：完全备份

              复制binlog中指定时间范围内的event：增量备份

　　2）lvm2快照+复制binlog：

              lvm2快照：适用cp或者tar等做物理备份：完全备份；

              复制binlog中指定时间范围内的event：增量备份；

              注意：lvm2属于温备，要先锁表。 

　　3）xtrabackup | mariabackup：

　　xtrabackup作用于mysql数据库而不是mariadb，由Percona公司提供的支持对InnoDB做热备（物理备份）工具。xtrabackup在mariadb 10版本之前是可以用的。

mariabackup专门为mariadb设计的，可以做：完全备份，增量工具

3. 数据库的恢复

1> 基于二进制文件的恢复

1）基于时间点：

       a. 算好要恢复数据的时间段，重定向输入到bin.sql文件中，如：

mysqlbinlog --start-datetime="2019-06-08 22:55:13" --stop-datetime="2019-06-08 22:55:13" binlog.0000011 > bin.sql

       b. 执行bin.sql文件还原

source bin.sql

2）基于position，如：

mysqlbinlog /var/lib/mysql/mysql-bin.000005 --stop-position=1389 | mysql -uroot -p123

　　如果不提供start则从头开始恢复；position从二进制日志中查询。

2> 基于lvm2的备份

　　因为mariadb的默认数据文件位置是/var/lib/mysql目录里，并不是文件系统，因此这里用新加磁盘来创建文件系统测试

1.添加磁盘
2.重启服务器识别硬盘
3.[root@localhost ~]#fdisk /dev/sdb                           #分区
4.[root@localhost ~]#pvcreate /dev/sdb3                       #创建pv
5.[root@localhost ~]#pvdisplay                                #查看pv
"/dev/sdb3" is a new physical volume of "100.00 GiB"
--- NEW Physical volume ---
PV Name               /dev/sdb3
VG Name               
PV Size               100.00 GiB
Allocatable           NO
PE Size               0  
Total PE              0
Free PE               0
Allocated PE          0
PV UUID             zYUBTH-Eqfa-ZYf7-q8pZ-gWBQ-UtUL-qeZzyd
6.[root@localhost ~]# vgcreate test3vg /dev/sdb3               #创建vg
Volume group "test3vg" successfully created
7.[root@localhost ~]# lvcreate -L 99G -n testlv3 test3vg       #创建lv
Logical volume "testlv3" created.
8.[root@localhost ~]# mkfs.ext4 /dev/test3vg/testlv3           #格式化lv
9.[root@localhost ~]# mkdir /test3                             #根目录下创建一个test3目录
10.[root@localhost ~]# mount /dev/test3vg/testlv3 /test3/      #挂在刚才所创建的目录
11.如果是yum安装默认数据目录在/var/lib/mysql,如果需要更改
vim /etc/my.cnf.d/server.cnf
[mysqld]
datadir=/test3/
pid_file=/test3/localhost.pid
socket=/test3/mysql.sock
wsrep_data_home_dir=/test3/
log-bin=mysql-bin
[client]
socket=/test3/mysql.sock
12.[root@localhost ~]#chown -R mysql.mysql /test3
#修改目录下面所有文件的属主属组，必须为mysql不然数据库起不来
13.[root@localhost ~]#service mariadb restart                  #重启数据库
14. MariaDB [(none)]> flush tables with read lock;             #将所有表锁住(只能读，不能写)
15.[root@localhost ~]# lvcreate -L 100G -s -p r -n snap_test3 /dev/test3vg/testlv3  #创建快照lv(这里要注意，test3vg中必须有足够的空间，不然创建lv会失败)
备注:
       -L: --size
       -s: --snap
       -p: --permission rw|r
       -n: --name
16. MariaDB [(none)]> unlock tables;                            #释放锁
17.[root@localhost testvg]# mkdir /snap_test3                   #创建快照目录
18.[root@localhost ~]# mount /dev/testvg/snap_test3 /snap_test3/#挂载快照lv
19.这时候你就可以在快照目录下看到跟源文件系统一模一样的内容了，此时就可以用cp拷贝了！

3> 基于mysqldump工具进行备份(逻辑备份工具)

mysqldump -uroot -proot -l --databases testdb > testdb.sql    #备份testdb数据库(-l代表备份单个数据库时锁定该库的所有表；-x当对所有数据库备份时锁定所有数据库的所有表）

mysqldump -uroot -proot --all-databases > all_databases.sql   #备份所有数据库
mysqldump -uroot -proot testdb students > students.sql        #备份testdb数据库下的students表
source testdb.sql                                             #还原数据（sql命令行下）

4. 备份工具mariabackup

　　Mariabackup是MariaDB提供的一个开源工具，用于对InnoDB，Aria和MyISAM表进行物理在线备份。这个工具是基于Percona的XtraBackup（版本2.3.8）的解决方案。

　　在MariaDB10.3.x及以上的版本用Percona XtraBackup工具会有问题。原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。

　　Percona的官方文档：https://www.percona.com/doc/percona-xtrabackup/2.4/index.html

　　Mariabackup的官方文档：https://mariadb.com/kb/en/library/mariabackup-overview/

 安装方法

yum install MariaDB-backup                  #需要有MariaDB数据库的源

备份与恢复

　　 全备+恢复

　　　　1）全量备份

mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root

　　　　2）准备全备数据

mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root

3）还原数据(请确保数据目录下是空的)

mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=root

4）修改属组和属主

chown -R mysql.mysql /var/lib/mysql

　　全备+增量+二进制->恢复

　　　　1)   全量备份数据库

　　　　2)   创建一个数据库

　　　　3)   增量备份

　　　　4)   删除一个创建的数据库

　　　　5)   恢复数据

　　　　　　1>> 全量备份

mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root

　　　　　　2>> 增量备份

mariabackup --backup --target-dir=/root/inc1 --incremental-basedir=/root/fullbackup/ --user=root --password=root

　　　　　　3>> 准备全备数据

mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root

　　　　　　4>> 增量和全备数据合并

mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root --incremental-dir=/root/inc1/ --apply-log-only

　　　　　　5>> 恢复数据(请确保数据目录下是空的)

mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=root

　　　　　　6>> 修改属组和属主

chown -R mysql.mysql /var/lib/mysql

　　还原的时候确保属组和属主是mysql:mysql