MySQL日志、引擎、事务
一、日志管理
mysql的日志一共分为六种不同的日志
配置文件位置（源码安装模板的存放位置）：/etc/my.cnf
1.错误日志
作用：记录启动、运行或停止时出现的问题，一般也会记录警告信息
[mysqld]下编辑开启
vim /etc/my.cnf
--------------------------
#指定日志的存放位置
log_error = /usr/local/mysql/data/mysql.err
#启用警告信息
log_warnings = 0/1
2.通用查询日志
作用：记录建立的客户端连接和执行的语句
开启之后对服务器的压力很大（一般不开启，默认不开启）
vim /etc/my.cnf
----------------------------
#启动或关闭查询日志，默认为关闭
general_log = ON/OFF
#指定查询日志存放位置
general_log_file = /usr/local/mysql/data/mysql.log
log_output=TABLE\FILE
补充：为了调试数据库可以开启查询日志，生产环境不建议开启查询日志
3.慢查询日志
作用：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询，可以帮我们定位服务器性能问题
vim /etc/my.cnf
--------------------------
#开启或关闭慢查询日志，默认是关闭的
slow_query_log = ON/OFF
#指定查询日志存放位置
slow_query_log_file = /usr/local/mysql/logs/mysql_slow.log
#定义慢查询日志的记录市场，默认为10秒
log_query_time = 10
4.二进制日志
作用：精确的记录了用户对数据库中的数据进行操作的命令和操作的数据对象，做数据增量备份，提供数据基于时间点的恢复，还可以主从复制，主服务器开启
查看二进制日志是否开启
show variables like 'log_%';
开启二进制日志
vim /etc/my.cnf
--------------------------
server_id = 2
#配置开启binlog日志， 日志的文件前缀为 mysqlbin
log_bin = /usr/local/mysql/logs/mysql-bin
#配置二进制日志的格式
binlog_format = ROW
#日志存活时间
expire_logs_days = 30
5.中继日志
作用：从主服务器的二进制文件中复制而来的时间，并保存为的日志文件
vim /etc/my.cnf
--------------------------
relay-log = master-relay-bin
reay-log-index = master-relay-bin.index
6.事务日志
作用：记录lnnoDB等支持事务的存储引擎执行事务时产生的日志，数据库需要REDO日志去保证事务的持久性！
事务性存储引擎用于保证（ACID）原子性、一致性、隔离性和持久性；其不会立即写到数据文件中，而是写到事务日志中
vim /et/my.cnf
--------------------------
#指定 redo log 文件组所在的路径，默认值为`./`
innodb_log_group_home_dir=/usr/local/mysql
#指明redo log file的个数
innodb_log_files_in_group
#控制 redo log 刷新到磁盘的策略，默认为1
innodb_flush_log_at_trx_commit=1
#单个 redo log 文件设置大小，默认值为 48M 。最大值为512G
innodb_log_file_size=200M
二、存储引擎
在数据保存到数据文件之前会先传输到存储引擎，再按照各个存储引擎的存储格式进行数据存储
最为知名的存储引擎：MyISAM和InnoDB存储引擎
MyISAM
特点：
- 读写操作速度快，内存占用小
- 读写过程相互阻塞
- 不支持事务，不支持外键，只支持全文索引
- 表级锁定，数据再更新时锁定整个表
- 在磁盘上存储成三个文件
1、.frm文件存储定义 2、数据文件的扩名为：.MYD 3、索引文件的扩展名为：.MYI
MyISAM适用的场景应用
不需要事务的支持，单方面读写数据比较多，因为读写相互阻塞的特点，数据读写频繁的环境不适用，并发访问较低，数据修改较少
InnoDB
特点：
- 支持事务，支持4个事务隔离级别
- 读写阻塞跟事务隔离级别有关
- 缓存特性非常高效，能缓存索引，也能缓存数据
- 行级锁定，但是全表扫描仍然会是表级锁定
- 表和主键以簇的方式存储
- 支持分区，表空间，类似Oracle数据库
- 支持外键约束，MySQL5.5以前不支持全文索引，5.5版本以后支持
InnoDB适用的场景应用
业务需要事务支持，高并发的适应能力，数据更新较频繁，数据一致性要求较高，硬件设备内存较大
比较
- 事务: InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
- 并发: MyISAM 只支持表级锁，而 InnoDB 还支持行级锁
- 外键: InnoDB 支持外键
- 备份: InnoDB 支持在线热备份
- 崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
- 其它特性: MyISAM 支持压缩表和空间数据索引
存储引擎管理
查看存储引擎
#查看数据库可配置的存储引擎
show engines;
#查看表正在使用的存储引擎
show table status from 库名 where name = '表名'
修改存储引擎
#设置修改存储引擎
alter table table_name engine=引擎名;
配置文件修改存储引擎
vim /etc/my.cnf
---------------------------------
default-storage-engine=引擎名
批量转换存储引擎
mysql_convert_table_format --user=root --password=密码 --socket=/temp/mysql.sock --engine=引擎名 库名 表名
命令选项
–force：碰到错误强制转换
–host：转换的主机名
–password：当前进行转换用户的密码
–port：如果不是本地连接，需要指定端口号
–socket：SOCKET文件存在的位置
–user：连接的用户名
三、事务管理
作用：MySQL事务主要用于处理操作量大，复杂度高的数据
比如：你删除一个学生的学籍数据，那么你要删除他的学号、学校账号、密码、家庭住址等基础信息，这一系列操作可以看作是一个事务
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
事务的特性：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）
- 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
查看事务详情
#查看事务是否为自动提交
SHOW VARIABLES like '%autocommit%'
#查看数据库当前进程
show processlist;
#查看当前运行的所有事务
select * from information_schema.INNODB_TRX;
#查看当前出现的锁
select * from information_schema.INNODB_LOCKs;
事务处理方法
事务开始：begin;
提交：commit;
回滚：rollback;
执行之后回滚到begin位置，之后的操作全部不作数
定义回滚点：savepoint si;
定义了之后不会直接回滚到begin
设置事务为手动提交（关闭之后需要手动提交）：set autocommit=0/1;
0为关闭自动提交，1为开启
事务测试
#创建测试的库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
#创建测试用的表，引擎为innodb
mysql> create table test(work_id int(5)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from test;
Empty set (0.00 sec)
#创建第一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test value(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test value(2);
Query OK, 1 row affected (0.00 sec)
#提交第一个事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
+---------+
| work_id |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.00 sec)
#创建第二个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test value(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test value(4);
Query OK, 1 row affected (0.00 sec)
#查看新添加的数据，添加成功
mysql> select * from test;
+---------+
| work_id |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
+---------+
4 rows in set (0.00 sec)
#数据回滚，会回滚到第二个事务begin位置
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
#因为回滚所以数据没有插入
mysql> select * from test;
+---------+
| work_id |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.00 sec)