一、 日志

MySQL 支持丰富的日志类型，如下：

• 事务日志：transaction log

  事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead logging事务日志文件： ib_logfile0， ib_logfile1

• 错误日志 error log

• 通用日志 general log

• 慢查询日志 slow query log

• 二进制日志 binary log

• 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

1. 事务日志

• redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log
• undo log：保存与执行的操作相反的操作,用于实现rollback

事务型存储引擎自行管理和使用，建议和数据文件分开存放

Innodb事务日志相关配置：

show variables like '%innodb_log%';    //查看事务日志
  innodb_log_file_size     50331648    //每个日志文件大小  字节
  innodb_log_files_in_group   2        //日志组成员个数
  innodb_log_group_home_dir   ./       //事务文件路径

ll -h /var/lib/mysql

事务日志性能优化

select @@innodb_flush_log_at_trx_commit;
//查看默认值

innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

级别	0	1	2
安全性	较高	最高	最高
性能	最高	最差	较高

2. 错误日志

错误日志

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

错误文件路径： /var/log/mysqld.log

3. 通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句

通用日志可以保存在：file（默认值）或 table（mysql.general_log表）

通用日志相关设置：

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

示例： 启用通用日志并记录至文件中

select @@general_log;      //默认没开启
set global general_log=1;  //开启

show global variables like 'log_output';  //默认通用日志存放在文件中
select @@general_log_file;               //通用日志存放的文件路径，文件名跟主机名有关

4. 慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作

慢查询相关变量：

slow_query_log=ON|OFF //开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N //慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log  //慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
//上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON  //不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语
句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 //多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain //记录内容
log_slow_queries = OFF    //同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除


//开启
set  global slow_query_log=1;
set long_query_time=1;

//测试
select sleep(10)

5. 二进制日志

• 记录导致数据改变或潜在导致数据改变的SQL语句
• 记录已提交的日志
• 不依赖于存储引擎类型

功能：通过"重放"日志文件中的事件来生成数据副本

注意：建议二进制日志和数据文件分开存放

5.1 二进制日志记录三种格式

1. 基于"语句"记录：statement记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
2. 基于"行"记录：row 记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
3. 混合模式：mixed 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

show variables like 'binlog_format';   //查看二进制日志记录格式

5.2 二进制日志相关的服务器变量

• sql_log_bin

sql_log_bin=ON|OFF：
//是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项，一般默认开启

select @@sql_log_bin;

• log_bin

log_bin=mysql-bin      //默认是关闭
//指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
//此选项作用是指明二进制日志的位置所在
//需要加 server-id=1 数据库编号

select @@log_bin;
//注意！变量log_bin 和 服务器选项 是两个意思

• binlog_format：日志格式

binlog_format=STATEMENT|ROW|MIXED：
//二进制日志记录的格式，mariadb5.5默认STATEMENT

• max_binlog_size：最大日志大小

max_binlog_size=1073741824：
//单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
//说明：文件达到上限时的大小未必为指定的精确值

• 其他

binlog_cache_size=4m 
//此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m 
//限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：
//设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：
//二进制日志可以自动删除的天数。 默认为0，即不自动删除

5.3 示例

• 开启二进制日志

[root@node5 ~]#  vim /etc/my.cnf
  log_bin=/data/mysql-bin  //指明二进制日志所存在的目录
  server-id=1              //指明数据库的编号
[root@node5 ~]#  chown mysql.mysql /data/
[root@node5 ~]#  systemctl restart mysqld

• 离线查看二进制日志

mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志

insert teachers values(null,'hkj','60','M')

mysqlbinlog -v mysql-bin.000001   //-v查看详细
  ### INSERT INTO `hellodb`.`teachers`
  ### SET
  ###   @1=5
  ###   @2='hkj'
  ###   @3=60
  ###   @4=2
  # at 400     //节点

• 查看使用中的二进制文件

show master status;

• 查看二进制文件的大小

show master logs;

• 在线查看二进制日志

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
例子：
show binlog events in '日志名称';
show binlog events in 'mysql-bin.000001';
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3;

• 删除二进制日志

purge binary logs to '日志名称';
purge binary logs to 'mysql-bin.000002';  //代表删除002之前的日志

reset master；  //彻底清空二进制日志

flush logs；    //刷新日志

二、 备份

1. 备份类型

• 完全备份，部分备份
  • 完全备份：整个数据集
  • 部分备份：只备份数据子集，如部分库或表
• 完全备份、增量备份、差异备份
  • 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂 （还原规则:先备先还）
  • 差异备份：仅备份最近一次完全备份以来变化的数据或者增长的数据，备份较慢，还原简单， 直接还原最新的备份

注意：二进制日志文件不应该与数据文件放在同一磁盘冷、温、热备份

• 冷备：读、写操作均不可进行，数据库停止服务
• 温备：读操作可执行；但写操作不可执行
• 热备：读、写操作均可执行

MyISAM：温备，不支持热备； InnoDB：都支持。

备份数据：

• cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
• LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
• mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
• xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
• MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
• mysqlbackup：热备份， MySQL Enterprise Edition 组件
• mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

2. 完全备份

2.1 物理冷备份

通过打包备份 数据库文件夹的方式备份（一般用于数据库迁移）

• 备份方式： 冷备份，一定要先停数据库
• 备份工具： cp 、 tar 、 scp 、 rsync （同步软件）等

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

示例：

//将数据库文件整个打包
[root@node5 ~]#  systemctl stop mysqld
[root@node5 ~]#  scp -r /var/lib/mysql 192.168.204.70:/opt

//模拟破坏数据
[root@node5 ~]#  rm -rf /var/lib/mysql
[root@node5 ~]#  ls /var/lib/mysql
ls: 无法访问/var/lib/mysql: 没有那个文件或目录

//将备份的数据库文件再拷贝回来
[root@node7 ~]# cd /opt
[root@node7 opt]# ls
mysql  rh
[root@node7 opt]# scp -r /opt/mysql/ 192.168.204.50:/var/lib/

//修改权限
[root@node5 ~]#  chown mysql.mysql /var/lib/mysql -R

//验证
[root@node5 ~]#  systemctl start mysqld

2.2 mysqldump 备份与恢复 （热备）

语法

• 备份表 （不推荐）

mysqldump [OPTIONS] database [tables]   
//只能备份表  不推荐使用

例：mysqldump -uroot -pabc123 hellodb students > /opt/h-s.sql  (备份)
    mysql -uroot -pabc123 hellodb < /opt/h-s.sql  (恢复)

• 备份指定数据库

mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
//可以备份数据库  -B 可以选择多个数据库

例：mysqldump -uroot -pabc123 hellodb > /data/hellodb.sql  (备份)
    mysql -uroot -pabc123 < /data/hellodb.sql  (恢复)

• 备份所有数据库

mysqldump [OPTIONS] -A [OPTIONS]       
//备份所有数据库

例：mysqldump -uroot -pabc123 -A > /backup/all.sql  (备份)
    mysql -uroot -pabc123 < /backup/all.sql  (恢复)

mysqldump 常见通用选项

选项	含义
-A, --all-databases	备份所有数据库，含create database
-B, --databases db_name…	指定备份的数据库，包括create database语句
-E, --events：	备份相关的所有event scheduler
-R, --routines：	备份所有存储过程和自定义函数
--triggers：	备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8	指定字符集
--master-data[=#]：	①此选项须启用二进制日志 ②：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复 制多机使用 ③：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原 ④此选项会自动关闭--lock-tables功能，自动打开-x 【 --lock-all-tables功能（除非开启-- single-transaction）】
-F, --flush-logs	备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件， 配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact	去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data	只备份表结构,不备份数据,即只备份create table
-t, --no-create-info	只备份数据,不备份表结构,即不备份create table
-n,--no-create-db	不备份create database，可被-A或-B覆盖
--flush-privileges	备份mysql或相关时需要使用
-f, --force	忽略SQL错误，继续执行
--hex-blob	使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY， BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick	不缓存查询，直接输出，加快备份速度

• mysqldump的MyISAM存储引擎相关的备份选项：

MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

-x,--lock-all-tables //加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables //对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
//注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

• mysqldump的InnoDB存储引擎相关的备份选项：

InnoDB 存储引擎支持事务，可以利用事务的相应的隔离级别，实现热备，也可以实现温备但不建议用

--single-transaction
//此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
//此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 
//在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,
  //此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用。

3. 实战案例

每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表。

//开启二进制日志
[root@node5 ~]#  vim /etc/my.cnf
log_bin=/data/mysql-bin      
server-id = 1
[root@node5 ~]#  mkdir /data
[root@node5 ~]#  chown  mysql.mysql /data/ -R
[root@node5 ~]#  systemctl restart mysqld

//2:30 执行全备
[root@node5 ~]#  mysqldump -uroot -pabc123 -A -F --single-transaction --master-data=2 > /data/all.sql

//完全备份后数据更新
[root@node5 ~]#  mysql -uroot -pabc123 -e "insert hellodb.teachers  values(null,'test',20,'M')"
[root@node5 ~]#  mysql -uroot -pabc123 -e "insert hellodb.teachers  values(null,'test1',22,'M')"
[root@node5 ~]#  mysql -uroot -pabc123 -e "insert hellodb.teachers  values(null,'test2',24,'M')"

//10:00  误删除了hellodb库
mysql> drop database hellodb;
//10点10分发现进行还原，停止数据库访问
[root@node5 ~]#  systemctl stop mysqld

//从完全备份中，找到二进制位置
[root@node5 ~]#  grep '\-\- CHANGE MASTER TO'  /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;

//备份  完全备份后更新数据的二进制日志
[root@node5 ~]#  mysqlbinlog --start-position=154 /data/mysql-bin.000002 > /data/inc.sql

//找到  删库的语句
[root@node5 ~]#  grep -in "drop"  /data/inc.sql
92:drop database hellodb
//删除 删库的那一行
[root@node5 ~]#  sed -i.bak '92d'  /data/inc.sql

//开启数据库
[root@node5 data]#  systemctl start mysqld
//临时关闭二进制日志
mysql> set sql_log_bin=0;

//还原数据 按顺序还原！！！
mysql> source     /data/all.sql
mysql> source     /data/inc.sql

//还原后开启二进制日志
mysql> set sql_log_bin=1;