日志
事务的日志
redo log
作用
在事务ACID过程中，实现的是“D”持久化的作用。
REDO：记录的是，内存数据页的变化过程
特性:WAL(Write Ahead Log)日志优先写
文字描述流程
#修改
1）首先将表中id=1的行所在数据页加载到内存中data buffer page
2）MySQL实例在内存中将id=1的数据页改成id=2
3）id=1变成id=2的变化过程会记录到，redo内存区域，也就是redo buffer page中
4）当敲下commit命令的瞬间，MySQL会将redo buffer page写入磁盘区域redo log
5）当写入成功之后，commit返回ok
#查询
1.首先将表中id=1的行所在数据页加载到内存中data buffer page
2.将redo log中id=1变成id=2的变化过程取加载到redo buffer page
3.通过data buffer page和redo buffer page得到一个结果
undo log
作用
在事务ACID过程中，实现的是“A”原子性的作用。当然CI的特性也和undo有关
#修改
1）首先将表中id=1的行所在数据页加载到内存中data buffer page
2）MySQL实例在内存中将id=1的数据页改成id=2
3）id=1变成id=2的变化过程会记录到，redo内存区域，也就是redo buffer page中
4）当敲下commit命令的瞬间，MySQL会将redo buffer page写入磁盘区域redo log
5）同时commit命令执行记录会记录到undo buffer page，并实时存入undo log
6）当写入成功之后，commit返回ok
#查询
1.首先将表中id=1的行所在数据页加载到内存中data buffer page
2.将redo log中id=1变成id=2的变化过程取加载到redo buffer page
3.将undo log日志中的commit执行记录加载到undo buffer page
4.通过data buffer page和redo buffer page得到一个结果
5.得到结果后向undo buffer page查询commit是否执行
6.是--> 输出结果 否-->回滚至原始数据输出
redo和undo的存储位置
#redo位置
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 50331648 Aug 15 06:34 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 6 2017 ib_logfile1
#undo位置
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata1
-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata2
事务中的锁
什么是锁
“锁”顾名思义就是锁定的意思。
作用
在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。
锁的类别
排他锁：保证在多事务操作时，数据的一致性。（在我修改数据时，其他人不得修改）
共享锁：保证在多事务工作期间，数据查询时不会被阻塞。
乐观锁：多实务操作时，数据可以同时修改，谁先提交，以谁为准
悲观锁：多实务操作时，数据只有一个人可以修改
多版本并发控制
1.只阻塞修改类操作（排他锁），不阻塞查询类操作（共享锁）
2.乐观锁的机制（谁先提交谁为准
锁的粒度
1.MyIsam：表级锁
2.Innodb：行级锁
事务中的隔离级别
四种隔离级别
1.RU级别：READ UNCOMMITTED（独立提交）：未提交读，允许事务查看其他事务所进行的未提交更改
2.RC级别：READ COMMITTED：允许事务查看其他事务所进行的已提交更改，查看不需要重新记入数据库
3.RR级别：REPEATABLE READ：允许事务查看其他事务所进行的已提交更改，查看数据需要重新进入数据库（InnoDB 的默认级别）
4.串行化：SERIALIZABLE：将一个事务的结果与其他事务完全隔离
查看隔离级别
#查看隔离级别
mysql> show variables like '%iso%';
设置隔离级别
设置RU级别
[root@db03 ~]# vim /etc/my.cnf
transaction_isolation=read-uncommit
设置RC级别
[root@db03 ~]# vim /etc/my.cnf
transaction_isolation=read-commit
名词
1.脏读：RU级别，执行事务修改数据，被读取，但是数据最终回滚了，查询到的数据就是脏读
2.幻读：删除所有表数据，删除的同时有人插入数据，查看数据时以为是没删干净
3.不可重复读：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据并修改数据。
mysql日志
mysql错误日志
1.错误日志默认是关闭的
2.默认路径是 $datadir/，默认的名字是'主机名.err'
3.配置方式（一般场景所有配置）
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/tmp/mysql.sock
skip-name-resolve
log_err=/usr/local/mysql/data/mysql.err
[mysql]
socket=/tmp/mysql.sock
4.查看方式
[root@db02 ~]# mysql -e "show variables like '%log_err%'"
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| log_error | /usr/local/mysql/data/mysql.err |
+---------------------+---------------------------------+
一般查询日志
1.查看方式
mysql> show variables like '%general%';
+------------------+--------------------------------+
| Variable_name | Value |
+------------------+--------------------------------+
| general_log | OFF |
| general_log_file | /usr/local/mysql/data/db02.log |
+------------------+--------------------------------+
2 rows in set (0.00 sec)
2.默认是关闭的
3.默认路径是 $datadir/，默认的名字是'主机名.log'
4.配置方式
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log_err=/usr/local/mysql/data/mysql.err
general_log=on
general_log_file=/usr/local/mysql/data/db02.log
[mysql]
socket=/tmp/mysql.sock
二进制日志
#注意：
1）binlog生成默认大小是120
2）binlog的大小也是binlog的当前位置点
1.查看方式
mysql> show variables like '%log_bin%';
2.二进制日志默认是关闭的
3.配置binlog
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id #mysql5.7必须配置server_id
log_bin=/usr/local/mysql/data/mysql-bin #mysql5.7只支持下划线
#log-bin=/usr/local/mysql/data/mysql-bin #mysql5.6都支持
4.二进制配置路径和名字由配置文件决定，一般保存在$datadir/ 以'mysql-bin.000001'命令
二进制日志管理操作
开启二进制日志
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id
log_bin=/usr/local/mysql/data/mysql-bin
查看二进制日志
#物理查看
[root@db02 ~]# ll /usr/local/mysql/data/
-rw-rw---- 1 mysql mysql 120 Jul 21 19:24 mysql-bin.000001
-rw-rw---- 1 mysql mysql 39 Jul 21 19:24 mysql-bin.index
#数据库查看
mysql> show variables like '%log_bin%';
事件
1.什么是事件
1）在binlog中最小的记录单元为event
2）一个事务会被拆分成多个事件（event）
2.事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。
刷新binlog
1）flush logs;
2）重启数据库时会刷新
3）二进制日志上限，默认1G（max_binlog_size）
删除binlog
1.根据存在时间删除日志
#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
2.使用purge命令删除
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
3.根据文件名删除
PURGE BINARY LOGS TO 'mysql-bin.000010';
4.使用reset master
mysql> reset master;
二进制日志作用
1.记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
2.记录所有DDL、DCL等语句，总之，二进制日志会记录所有对数据库发生修改的操作
3.如果我拥有数据库搭建开始所有的二进制日志，那么我可以把数据恢复到任意时刻
4.数据的备份与恢复
5.数据的复制
二进制日志介绍
1.二进制日志默认是关闭的
2.查看二进制
mysql> show variables like '%log_bin%';
3.配置
[root@db03 ~]# vim /etc/my.cnf
server_id=1
log_bin=/service/mysql/data/mysql-bin
4.开启二进制一般存储子 $datadir/ 下面，以'mysql-bin.00000N'命名
二进制日志工作模式
1）工作模式种类
1.statement 语句模式
2.row 行级模式
3.mixed 混合模式
2）查看工作模式
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
3）STATEMENT语句模式
#语句模式，mysql5.6默认的模式
记录数据库中操作过得所有sql语句
#查看
[root@db03 data]# mysqlbinlog mysql-bin.000014
#优缺点：
1.易读
2.不安全
3.相对于行级模式占用磁盘空间小
4）row行级模式
#行级模式，mysql5.7默认的模式
记录的是数据的变化过程
#配置行级模式
[root@db03 data]# vim /etc/my.cnf
binlog_format=row
#查看方式
[root@db03 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000016
#优缺点：
1.安全
2.不易读
3.相对于语句模式占用磁盘大
5）mixed混合模式（没人用）
数据库的备份与恢复
添加数据
mysql> create database binlog;
Query OK, 1 row affected (0.00 sec)
mysql> use binlog
Database changed
mysql> create table binlog(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert binlog values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert binlog values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert binlog values(5);
Query OK, 1 row affected (0.00 sec)
删除数据
#误删除两条数据
mysql> delete from binlog where id=5;
Query OK, 1 row affected (0.00 sec)
mysql> delete from binlog where id=4;
Query OK, 1 row affected (0.00 sec)
通过binlog恢复数据
#查看二进制日志找到位置点
[root@db02 data]# mysqlbinlog mysql-bin.000002
#将位置点之间的数据取出
[root@db02 data]# mysqlbinlog --start-position=631 --stop-position=978 mysql-bin.000002 > /tmp/hf.sql
#将数据导入回去
[root@db02 data]# mysql < /tmp/45.sql
使用binlog配合数据库升级
1.准备一台新的数据库，版本为5.6.38
2.旧数据库备份数据
[root@db03 ~]# mysqldump -uroot -p123 --triggers -R --master-data=2 -B ku linux9 myisam qiudao qiudaodsb tmp world xiangqing >/tmp/full.sql
3.将备份的数据库传到新数据库
[root@db03 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/
4.修改sql中的存储引擎
[root@db02 data]# sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql
5.将修改后的sql文件导入新数据
[root@db02 data]# mysql < /tmp/full.sql
6.将代码中的数据库地址修改为新的数据库地址
7.通过binlog将数据迁移过程中新生成的数据取出
[root@db03 data]# mysqlbinlog -uroot -p123 --start-position=120 --stop-position=465 mysql-bin.000014 > /tmp/bu.sql
[root@db03 data]# scp /tmp/bu.sql 172.16.1.52:/tmp/
8.将新数据导入新库
[root@db02 data]# mysql < /tmp/bu.sql
慢日志
作用
1.是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
2.通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的
配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = 1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/service/mysql/data/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=100（鸡肋）
slow_query_log = 1
slow_query_log_file=/service/mysql/data/slow.log
long_query_time=3
log_queries_not_using_indexes
慢日志测试
#建表
mysql> create table solwlog2 select * from city;
Query OK, 4079 rows affected (0.07 sec)
Records: 4079 Duplicates: 0 Warnings: 0
#反复插入
mysql> insert solwlog select * from solwlog;
Query OK, 2088448 rows affected (9.00 sec)
Records: 2088448 Duplicates: 0 Warnings: 0
#查看慢日志
[root@db03 data]# less slow.log
使用mysqldumpslow命令来分析慢查询日志
#输出记录次数最多的10条SQL语句
mysqldumpslow -s c -t 10 /database/mysql/slow-log
-s:
是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t:
是top n的意思，即为返回前面多少条的数据；
-g:
后边可以写一个正则匹配模式，大小写不敏感的；
#例子：
#得到返回记录集最多的10个查询
mysqldumpslow -s r -t 10 /database/mysql/slow-log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /database/mysql/slow-log
扩展
第三方推荐（扩展）：
yum install -y percona-toolkit-3.0.11-1.el6.x86_64.rpm
使用percona公司提供的pt-query-digest工具分析慢查询日志
[root@mysql-db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log