日志和备份介绍

查询日志:general
show variables like '%general%';
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| general_log | OFF |
| general_log_file | zxw8.log |
+------------------+----------+
• 记录每一条sql语句，建议不开启，因为如果访问量较大，会占用相当大的资源，影响性能;
• vim /etc/my.cnf.d/server.cnf
• general_log = ON| OFF#查询日志开关
• general_log =1
• general_log_file localhost.log #查询日志的文件名字（/var/lib/mysql）默认在
• log_output TABLE | FILE | NONE #查询日志的存储形式
慢查询日志:
show variables like '%slow_query%';
+---------------------+---------------+
| slow_query_log | OFF |
| slow_query_log_file | zxw8-slow.log |
+---------------------+---------------+
show variables like '%long_query%';
long_query_time | 10.000000 |
执行时长超出指定时间的查询操作（测试命令:select sleep(4);）
slow_query_log = OFF|ON #开启慢查询日志
slow_query_log =1
slow_query_log_file = LOCALHOST-SLOW.log #慢查询日志的文件路径
long_query_time #慢查询时长；默认是10s
log_slow_rate_limit#如果要记录的慢查询日志非常多的话，会按照速率来记录，默认1秒记录一个
log_slow_verbosity=full | query_plan #记录的详细级别

错误日志
mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler 运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息

log_error = /var/log/mysql_error.log#指定错误日志的输出位置
log_warnings 为0，表示不记录告警信息。
log_warnings 为1，表示告警信息写入错误日志。
log_warnings 大于1，表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志。
| log_warnings | 2 (默认为2)
/var/log/messages
show variables like '%log_warnings%';

二进制日志
针对时间点还原起着至关重要的作用(增，删，改）
查看二进制日志：
show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
mysqlbinlog mysql-bin.000001
记录的增删改
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name']
show master status;
记录导致数据改变或者可能导致数据改变的SQL语句
show variables like '%log_bin%'
log_bin = OFF | ON
log_bin =mysql-bin
log_bin_basename = /var/lib/mysql/mysql-bin 默认路径
记录格式
binlog_format=STATEMENT|ROW|MIXED:二进制记录格式
• ROW：基于“行”记录（精准，占用空间大）
• STATEMENT：基于“语句”记录（易错，占用空间小）
• MIXED：让系统自行判定该基于哪种方式进行（混合）
sql_log_bin=1|0:是否启用二进制日志
log_bin_index=PATH:二进制日志索引位置
sync_binlog=1|0:设定是否启动二进制日志同步功能
max_binlog_size=SIZE:单个二进制文件最大体积，默认为1G
expire_logs_days=0#超过多少天就清除二进制日志，默认为0,代表不启用此功能
二进制日志滚动：
1.flush logs;
show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 954 |
| mysql-bin.000002 | 385 |
+------------------+-----------+
show master status
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 385 | | |
+------------------+----------+--------------+------------------+

2.文件超出指定大小
3.service mariadb restart
二进制日志文件的构成：
两类文件：
日志文件：mysql-bin.xxxxx，二进制格式
索引文件：mysql-bin.index,索引文件

二进制日志格式:
#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
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 666
事件的类型：Query
事件发生时所在服务器执行此事件的线程ID：thread_id=9
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：

GTID:Global Transaction ID;
专属属性：GTID
mysqlbinlog：客户端命令工具
mysqlbinlog [options] log_file ...
--start-datetime=
--stop-datetime=

--start-position=
--stop-position=

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

事务日志:transaction log（ib_logfile0，ib_logfile1）
show variables like '%buffer_pool%';
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
事务型存储引擎自行管理和使用(Innodb,myisam引擎是不支持事务，外键，行级锁)
redo log : 重做日志
undo log ：撤销日志

数据库的备份与恢复
mysqldump备份工具

#备份testdb数据库(-l代表备份单个数据库时锁定该库的所有表；-x当对所有数据库备份时锁定所有数据库的所有表）
mysqldump -uroot -proot -l --databases testdb > testdb.sql
#备份所有数据库
mysqldump -uroot -proot --all-databases > all_databases.sql
#备份testdb数据库下的students表
mysqldump -uroot -proot testdb students > students.sql
#还原数据（sql命令行下）
source testdb.sql

利用二进制binlog恢复数据

#第一步
vim /etc/my.cnf.d/server.cnf
[server]
log-bin=mysql-bin
#第二步，查看二进制文件
mysqlbinlog /var/lib/mysql/mysql-bin.000001
#第三步，删除一些数据
#第四步，恢复
mysqlbinlog mysql-bin.000001 | mysql -uroot -proot

备份和恢复：
为什么要备份？
灾难恢复：硬件故障，软件故障，自然灾害，黑客攻击，误操作
测试
要注意的要点：
备份需要多少时间
能容忍最多丢失多少数据
恢复数据需要在多场时间内完成
需要恢复哪些数据
（1）做还原测试，用于测试备份的可用性
（2）还原演练

备份类型:
完全备份，部分备份
完全备份:整个数据集
部分备份:只备份数据子集

完全备份、增量备份、差异备份
增量备份:仅备份最近一次完全备份或增量备份（如果有增量备份）以来变化的数据
差异备份:仅备份最近一次完全备份以来变化的数据;

热备、温备、冷备
热备:读写操作均可执行
温备:读操作可以、写不行
冷备:读写操作均不能执行

MyISAM:温备，不能热备；
Innodb：热备

物理备份、逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎无关
逻辑备份：从数据库中“导出”数据库另存而进行备份
备份时需要考虑的因素：
持续多久
备份过程的时长
备份负载
恢复过程的时长
备份什么？
数据
二进制日志，innodb的事务日志
代码（存储过程，存储函数，触发器，时间调度器）
服务器的配置文件
设计备份方案：
数据集：完全+增量
备份手段：物理，逻辑
对于备份较大的数据建议物理备份，对于较小的数据建议用逻辑备份;

备份工具的选择：
mysqldump+复制binlog：
mysqldump：完全备份
复制binlog中指定时间范围内的event：增量备份
lvm2快照+复制binlog：
lvm2快照：适用cp或者tar等做物理备份：完全备份
复制binlog中指定时间范围内的event：增量备份
xtrabackup|mariabackup：
由Percona提供的支持对InnoDB做热备（物理备份）工具
完全备份，增量工具

基于二进制文件的恢复
1.算好要恢复数据的时间段，重定向输入到bin.sql文件中
mysqlbinlog --start-datetime="2019-06-08 22:55:13" --stop-datetime="2019-06-08 22:55:13" binlog.0000011 > bin.sql
mysqlbinlog /var/lib/mysql/mysqlbin.000001 --stop-pos=1093 | mysql -uroot -proot
2.执行bin.sql文件还原
source bin.sql

基于lvm2的备份
(1)请求锁定所有表
flush tables with read lock;
(2)记录二进制日志文件及事件位置或时间点
flush logs;
mysql -uroot -proot -e 'show master status;' > /path/to/somefile
(3)创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4)释放锁
unlock tables;
(5)挂在快照卷，执行数据备份
(6)备份完成后，删除快照卷
(7)指定好策略，通过原卷备份二进制日志
(8)还原的时候确保属组和属主是mysql:mysql
如果是yum安装默认数据目录在/var/lib/mysql,如果需要更改
[mysqld]
datadir=/mysql_data/
pid_file=/mysql_data/localhost.pid
socket=/mysql_data/mysql.sock
wsrep_data_home_dir=/mysql_data/
log-bin=mysql-bin
[client]
socket=/mysql_data/mysql.sock

备份工具mariabackup
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

备份与恢复
全备+恢复
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.全量备份
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