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文件中，如：
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
