数据库有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
[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文件中，如：
1 mysqlbinlog --start-datetime="2019-06-08 22:55:13" --stop-datetime="2019-06-08 22:55:13" binlog.0000011 > bin.sql
b. 执行bin.sql文件还原
1 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/
1 yum install MariaDB-backup #需要有MariaDB数据库的源
备份与恢复
全备+
恢复
1）全量备份
1 mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root
2）准备全备数据
1 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>> 全量备份
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
