基于MySQL的存储引擎与日志说明(全面讲解)

InnoDB、MyISAM (最常用的两种)
MEMORY、ARCHIVE、FEDERATED、EXAMPLE
BLACKHOLE、MERGE、NDBCLUSTER、CSV

a) 事务安全（遵从ACID）
b) MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
c) InnoDB行级锁
d) 支持外键引用完整性约束
e) 出现故障后快速自动恢复（crash safe recovery）
f) 用于在内存中缓存数据和索引的缓冲区池（buffer pool（data buffer page log buffer page） 、undo buffer page）
g) 大型数据卷上的最大性能
h) 将对表的查询与不同存储引擎混合
i) Oracle样式一致非锁定读取(共享锁)
j) 表数据进行整理来优化基于主键的查询（聚集索引）

SELECT @@default_storage_engine;
或
show variables like '%engine%';

SHOW CREATE TABLE City\G
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

SELECT TABLE_NAME, ENGINE FROM 
INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'City'
AND TABLE_SCHEMA = 'world_innodb'\G 

[mysqld]
default-storage-engine=<Storage Engine>

SET @@storage_engine=<Storage Engine>;

CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
row in set (0.00 sec)

[root@db02 world]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:100M:autoextend

mysql> show variables like 'innodb_data_file_path';
+-----------------------+-------------------------------------+
| Variable_name | Value  |
+-----------------------+-------------------------------------+
| innodb_data_file_path | ibdata1:76M;ibdata2:100M:autoextend |
+-----------------------+-------------------------------------+
row in set (0.00 sec)

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
row in set (0.00 sec)

[root@db02 clsn]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table=0

mysql> show variables like '%per_table%' ;
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
row in set (0.00 sec)

innodb_file_per_table=0 关闭独立表空间
innodb_file_per_table=1 开启独立表空间，单表单存储

所有语句作为一个单元全部成功执行或全部取消。
updata t1 set money=10000-17 where id=wxid1
updata t1 set money=10000+17 where id=wxid2

如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
 updata t1 set money=10000-17 where id=wxid1
 updata t1 set money=10000+17 where id=wxid2
 在以上操作过程中，去查自己账户还是10000

隔离级别会影响到一致性。
 read-uncommit X
 read-commit 可能会用的一种级别
 repeatable-read 默认的级别，和oracle一样的
 SERIALIZABLE 严格的默认，一般不会用

保证数据落地，才算事务真正安全

START TRANSACTION（或 BEGIN）：显式开始一个新事务
 COMMIT：永久记录当前事务所做的更改(事务成功结束)
 ROLLBACK：取消当前事务所做的更改(事务失败结束)

 SAVEPOINT：分配事务过程中的一个位置，以供将来引用
 ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改
 RELEASE SAVEPOINT：删除 savepoint 标识符
 SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit模式

mysql> show variables like '%autoc%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)

[root@db02 world]# vim /etc/my.cnf
[mysqld]
autocommit=0

mysql> show variables like '%autoc%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
row in set (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
row in set (0.00 sec)

DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

START TRANSACTION
SET AUTOCOMMIT = 1

假设有A、B两个数据，值分别为1,2。
 A.事务开始.
 B.记录A=1到undo log.
 C.修改A=3.
 D.记录B=2到undo log.
 E.修改B=4.
 F.将undo log写到磁盘。
 G.将数据写到磁盘。
 H.事务提交

A. 更新数据前记录Undo log。
B. 为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。
C. Undo log必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undo log是完整的，可以用来回滚事务。
D. 如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

假设有A、B两个数据，值分别为1,2.
 A.事务开始.
 B.记录A=1到undo log.
 C.修改A=3.
 D.记录A=3到redo log.
 E.记录B=2到undo log.
 F.修改B=4.
 G.记录B=4到redo log.
 H.将redo log写入磁盘。
 I.事务提交

 A. 为了保证持久性，必须在事务提交前将Redo Log持久化。
 B. 数据不需要在事务提交前写入磁盘，而是缓存在内存中。
 C. Redo Log 保证事务的持久性。
 D. Undo Log 保证事务的原子性。
 E. 有一个隐含的特点，数据必须要晚于redo log写入持久存储。

innodb_flush_log_at_trx_commit=1/0/2

READ UNCOMMITTED 许事务查看其他事务所进行的未提交更改
READ COMMITTED 允许事务查看其他事务所进行的已提交更改
REPEATABLE READ****** 确保每个事务的 SELECT 输出一致； InnoDB 的默认级别
SERIALIZABLE 将一个事务的结果与其他事务完全隔离

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

[mysqld]
log-error=/data/mysql/mysql.log

mysql> show variables like '%log%error%';

mysql> show variables like '%log%err%';
+---------------------+----------------------------------+
| Variable_name | Value  |
+---------------------+----------------------------------+
| binlog_error_action | IGNORE_ERROR  |
| log_error | /application/mysql/data/db02.err |
+---------------------+----------------------------------+
rows in set (0.00 sec)

[mysqld]
general_log=on
general_log_file=/data/mysql/server2.log

show variables like '%gen%';

mysql> show variables like '%gen%';
+------------------+----------------------------------+
| Variable_name | Value  |
+------------------+----------------------------------+
| general_log | OFF  |
| general_log_file | /application/mysql/data/db02.log |
+------------------+----------------------------------+
rows in set (0.00 sec)

1、是否开启
2、二进制日志路径/data/mysql/
3、二进制日志文件名前缀mysql-bin 
4、文件名以"前缀".000001~N
log-bin=/data/mysql/mysql-bin 

只有总闸开启才有意义，默认是开启状态。
我们在有些时候会临时关闭掉。
只影响当前会话。
sql_log_bin=1/0

记录信息简洁，记录的是SQL语句本身。但是在语句中出现函数操作的话，有可能记录的数据不准确。

5.6中默认模式，但生产环境中慎用，建议改成row。

表中行数据的变化过程。
记录数据详细，对IO性能要求比较高
记录数据在任何情况下都是准确的。
生产中一般是这种模式。
5.7以后默认的模式。

经过判断，选择row+statement混合的一种记录模式。（一般不用）

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name  | Value |
+---------------------------------+-------+
| log_bin  | OFF |
| log_bin_basename | |
| log_bin_index  | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin  | ON |
+---------------------------------+-------+
rows in set (0.00 sec)

[root@db02 tmp]# vim /etc/my.cnf
[mysqld]
log-bin=/application/mysql/data/mysql-bin

mysql> SET GLOBAL binlog_format = 'STATEMENT'
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

[root@db02 data]# file mysql-bin.*
mysql-bin.000001: MySQL replication log
mysql-bin.index: ASCII text

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name  | Value   |
+---------------------------------+-----------------------------------------+
| log_bin  | ON   |
| log_bin_basename | /application/mysql/data/mysql-bin |
| log_bin_index  | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events | OFF   |
| sql_log_bin  | ON   |
+---------------------------------+-----------------------------------------+
rows in set (0.00 sec)

mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| binlog_format | STATEMENT |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0  |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| time_format | %H:%i:%s |
+--------------------------+-------------------+
rows in set (0.00 sec)

[root@db02 data]# vim /etc/my.cnf
[mysqld]
binlog_format=row

mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| binlog_format | ROW |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0  |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| time_format | %H:%i:%s |
+--------------------------+-------------------+
rows in set (0.00 sec)

[root@db02 data]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql 143 Dec 20 20:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql 120 Dec 20 20:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql 82 Dec 20 20:17 mysql-bin.index

mysql> flush logs;

[root@db02 data]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql 143 Dec 20 20:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql 167 Dec 20 20:24 mysql-bin.000002
-rw-rw---- 1 mysql mysql 120 Dec 20 20:24 mysql-bin.000003
-rw-rw---- 1 mysql mysql 123 Dec 20 20:24 mysql-bin.index
[root@db02 data]#

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | |  |  |
+------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 167 |
| mysql-bin.000003 | 120 |
+------------------+-----------+
rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | |  |  |
+------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec)
[root@db02 data]# mysqlbinlog mysql-bin.000003 >/tmp/aa.ttt

[root@db02 data]# mysqlbinlog mysql-bin.000003 >/tmp/aa.ttt

mysqbin mysql-bin.000002 

mysqlbinlog --base64-output='decode-rows' -v mysql-bin.000002

show binary logs; 所有在使用的binlog信息
show binlog events in '日志文件'

SET GLOBAL expire_logs_days = 7;
或
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

PURGE BINARY LOGS TO 'mysql-bin.000010';

reset master 

long_query_time ： 设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log ： 指定是否开启慢查询日志
slow_query_log_file ： 指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引

[root@db02 htdocs]# vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/tmp/slow.log
long_query_time=0.5 # 控制慢日志记录的阈值
log_queries_not_using_indexes

mysql> show variables like '%slow%'
 -> ;
+---------------------------+---------------+
| Variable_name | Value |
+---------------------------+---------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /tmp/slow.log |
+---------------------------+---------------+
rows in set (0.00 sec)

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-log

mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 | #控制binlog commit 阶段
+---------------+-------+
row in set (0.00 sec)

innodb_flush_log_at_trx_commit=1