mysql日志详解

mysql备份方式：

按业务分：

完全备份：就是对整个数据库的数据和数据结构进行备份
优点：直观，恢复简单，可靠
缺点：大量重复数据，占用空间，存储成本高
增量备份：每次备份的数据只是相当于上次备份后增加和修改过的数据
优点：节省空间
缺点：恢复麻烦，任何一个备份文件有问题都会导致恢复失败
差异备份：每次备份的数据相当于上一次全备份之后新增加和修改过的数据。
举例说明：就是假如星期一，管理员对数据库进行了一次完全备份，星期二，增加了一张“资产管理表”，则管理员只需把这张表备份下来即可；星期三，增加了一张“工资表”，则管理员需要把“资产管理表”+“工资表”备份下来；星期四增加了一张“人员表”，则备份的数据是“资产管理表”+“工资表”+“人员表”；
备份组合：
一、完全备份+差异备份
       假如在星期一完全备份，星期二至五差异备份，如果在星期五数据被破坏，只需要恢复星期一的完整备份和星期四让差异备份即可。备份时间多，还原时间端。
二、完全备份+增量备份
       假如在星期一完全备份，星期二至星期五增量备份，在星期五数据被破坏，则需还原星期一的完整备份和星期二到星期四所有的增量备份。

按方式分：

热备：数据库运行中备份，不影响业务
冷备：数据库停止，业务中断
温备：数据库运行中备份，需要加锁，对业务有部分影响
物理备份：直接copy数据文件，跨平台较差（直接复制备份的数据文件是二进制格式的）
逻辑备份：从数据库中"导出"数据另存而进行的备份（将sql语句给导出到一个文本中，比二进制格式的文件大），备份文件较大，备份较慢，无法保证浮点数的精度，使用逻辑备份数据恢复后，还需手动重建索引，十分消耗CPU资源
 

mysql日志

错误日志：
mysql> show variables like 'log_error%';
+---------------------+-----------+
| Variable_name | Value |
+---------------------+-----------+
| log_error | ./web.err |
| log_error_verbosity | 3 |
+---------------------+-----------+
2 rows in set (0.00 sec)
mysql> show variables like '%log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 2     |
+---------------+-------+
1 row in set (0.00 sec)

#log_error定义日志路径
#log_warnings定义是否把警告日志写入错误日志
查询日志：默认关闭，记录所有的操作，占用大量的I/0资源，影响性能
mysql> show variables like 'general_log%';
+------------------+---------------+
| Variable_name | Value |
+------------------+---------------+
| general_log | OFF |
| general_log_file | /data/web.log |
+------------------+---------------+
2 rows in set (0.01 sec)

慢查日志：用于定位问题，建议开启
mysql> show variables like 'slow%';
+---------------------+--------------------+
| Variable_name | Value |
+---------------------+--------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /data/web-slow.log |
+---------------------+--------------------+
3 rows in set (0.00 sec)

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
#long_query_time定义超过多少时间记录为慢查询，单位为秒

事务日志：事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。
innodb_flush_log_at_trx_commit | 1
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./

二进制日志：主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等。
mysql> show variables like 'log_bin%';
+---------------------------------+---------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------+
| log_bin | ON |
| log_bin_basename | /mybinlog/mysql-bin |
| log_bin_index | /mybinlog/mysql-bin.index |
mysql> show variables like 'binlog%';
+--------------------------------------------+--------------+
| Variable_name | Value |
+--------------------------------------------+--------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
+--------------------------------------------+--------------+
15 rows in set (0.00 sec)
#binlog-format= {ROW|STATEMENT|MIXED} #指定二进制日志的类型，建议为MIXED。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。

查看二进制日志：
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 186587 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 186587 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 186587 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Stop | 1 | 177 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)
导出二进制日志到文本
[root@web wordpress]# mysqlbinlog /mybinlog/mysql-bin.000002 > /tmp/a.sql
删除二进制日志：
mysql>reset master；

中继日志：
从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。
mysql> show variables like '%relay%';
+---------------------------+---------------------------+
| Variable_name             | Value                     |
+---------------------------+---------------------------+
| max_relay_log_size        | 0                         |
| relay_log                 |                           |
| relay_log_basename        | /data/web-relay-bin       |
| relay_log_index           | /data/web-relay-bin.index |
| relay_log_info_file       | relay-log.info            |
| relay_log_info_repository | FILE                      |
| relay_log_purge           | ON                        |
| relay_log_recovery        | OFF                       |
| relay_log_space_limit     | 0                         |
| sync_relay_log            | 10000                     |
| sync_relay_log_info       | 10000                     |
+---------------------------+---------------------------+
11 rows in set (0.03 sec)

设置mysql日志的方法：
全局变量和会话变量：global，session
临时生效：mysql>set global slow_query_log=1;
永久生效 ：编辑my.cnf 在[mysqld]下增加 slow_query_log=1