MySQL-day8-存储引擎与MySQL日志

今日内容：MySQL存引擎与MySQL日志

MySQL存储引擎

一、MySQL存储引擎

1.什么是存储引擎

• mysql的文件系统，主要是与磁盘交互

2.innodb与myisam的区别

功能	Myisam	InoDB
锁类型	表级	行级
事务	不支持	支持
CSR	不支持	支持

3.表空间

1.共享表空间：ibdata1
	系统数据
	undo log
	临时表
2.独立表空间：.ibd
	用户创建的数据

4.事务的日志

• redo log：重做日志 记录数据变化的过程，完成数据变化
• undo log： 不做日志 记录是否提交commit，回滚数据

5.锁的种类

锁种类	区别
共享锁	保证在多事务操作时，数据可以被查询，只不过看不到没有提交的数据（串行是关闭）
排它锁	保证在多事务操作时，数据的一致性。（我修改时，另一端无法修改）
乐观锁	多事务操作时，数据可以同时被修改，谁先提交，谁就修改成功。（都能看到票，谁先付钱是谁的）
悲观锁	多事务操作时，数据只有一个人可以修改。（我加入订单只有我看的见）

6.隔离级别

模式	全称	功能
RU	read uncommitted	未提交读，又称独立提交，允许事务查看其他事务所进行的未提交更改
RR	repeatable read	可重复读 InnoDB 的默认级别（commit提交以后可能看不到数据变化，必须重新连接）
RC	read committed	允许事务查看其他事务所进行的已提交更改
串行化	serializable	将一个事务的结果与其他事务完全隔离 #如果一个事务没有提交，查询也不能查了

• 串行化简意：我改微信头像时你不能看我的信息，我看你朋友圈时你不能发朋友圈，也不能看朋友圈

mysql日志

一、mysql错误日志

首次启动会提示错误日志位置
查询错误日志：
[root@db04 ~]# mysqladmin -uroot -p123456 variables | grep -w log_error

variables 其实就是查看mysql的配置文件，很多都是注释掉的，他就走默认的，其实我们可以把配置文件打开，一般都会配置
[root@db04 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
socket=/tmp/mysql.sock
basedir=/service/mysql
datadir=/service/mysql/data
port=3306
skip_name_resolve
#绝对路径
log_error=/service/mysql/data/mysql.err
#相对路径
log_error=mysql.err
[mysql]
socket=/tmp/mysql.sock

#结论：
1.默认是开启的
2.路径在 $datadir/data/ 下面，以主机名.err来命名
3.可以修改我们指定的地址
	[root@db04 ~]# vim /etc/my.cnf
	log_error=/service/mysql/data/mysql.err
4.查看错误日志方式
	mysqladmin -uroot -p123456 variables | grep -w log_error
	mysql> show variables like '%log_error%';

二、一般查询日志

1.默认是关闭的
2.路径在 $datadir/data/ 下面，以主机名.log来命名
3.查看一般查询日志
	[root@db04 ~]# mysqladmin -uroot -p123456 variables|grep general_log
	mysql> show variables like '%gen%';
4.可以修改我们指定的地址
	[root@db04 ~]# vim /etc/my.cnf
	general_log=on
	general_log_file=/service/mysql/data/mysql.log
	#可以使用set general_log=on;设置
	
mysql的好处就是编译完了不是一定得根据他编译的配置走，可以去配置自己想要的内容

#一般不会开启，因为哪怕你开启事务一顿操作，最后不提交也会记录，生产上程序跑sql很多，会非常非常占地方，从来都不启动，要看操作去binlog

三、二进制日志

1.默认是关闭的
2.查看binlog方式
	mysql> show variables like 'log_bin';
	[root@db04 ~]# mysqladmin -uroot -p123456 variables |grep -w log_bin
	[root@db04 ~]# mysqladmin -uroot -p123456 variables |grep -E  "\blog_bin\b"
3.配置开启binlog
	vim /etc/my.cnf
		[mysqld]
		server_id=1
		log-bin=/service/mysql/data/mysql-bin 		#如果使用绝对路径，那就一直使用绝对路径
		log-bin=mysql-bin		#MySQL5.6可以多种写法
		log_bin=mysql-bin		#MySQL5.7必须这么写 _ 下划线
4.路径在 $datadir/data/ 下面，以mysql-bin.00000N来命名

1.二进制日志的作用

1.记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
2.记录所有DDL、DCL等语句
3.总之，二进制日志会记录所有对数据库发生修改的操作
	
记录数据库,增,删,改的SQL语句.

1.如果我拥有数据库搭建开始所有的二进制日志，那么我可以把数据恢复到任意时刻
2.数据的备份恢复
3.数据的复制

2.二进制日志工作模式

1）查看工作模式

#查看工作模式
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

2）语句模式

#语句模式（MySQL5.6默认）STATEMENT：
	主要是记录增，删，改的SQL语句
	
#验证：
#建表、插入数据、修改数据
	use test;
	create table binlog(id int);
	begin;
	insert into binlog values(1),(2),(3);
	update binlog set id=10 where id=1;
	commit;

#查看binlog，看看有没有记录，二进制文件我们看不了，有专门的命令
	[root@db01 data]# mysqlbinlog mysql-bin.000001
	
#注意：
	binlog 默认多大？120	（系统占用的一部分）
	binlog起始位置点是哪里？show master status
	binlog位置点是跟着大小走的
	
#binlog可以用来恢复数据
	#修改数据
	mysql> begin;
	mysql> update binlog set id=200 where id=2;
	mysql> commit;
	#发现自己修改错了
	mysql> select * from binlog;
	#回滚，回滚不了，已经提交了
	mysql> rollback;
	mysql> select * from binlog;
	#一怒之下删表
	mysql> drop table binlog;
#恢复数据
	查看binlog到底应该恢复到哪个位置点，到处成SQL
	[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=549 /service/mysql/data/mysql-bin.000001  > /tmp/binlog.sql
	导入SQL
	[root@db01 data]# mysql -uroot -p123 < /tmp/binlog.sql


#优缺点：
	1.易读
	2.占用磁盘空间小
	3.不严谨

3）行级模式

#行级模式（MySQL5.7默认）ROW：
	主要是记录SQL语句,修改数据变化过程	

#修改binlog为行级模式
[root@db01 data]# vim /etc/my.cnf
[mysqld]
binlog_format=row
[root@db01 data]# /etc/init.d/mysqld restart

#验证：
	#建表、插入数据、修改数据
	use test;
	create table binlog(id int);
	begin;
	insert into binlog values(1),(2),(3);
	update binlog set id=10 where id=1;
	commit;

#再次查看binlog
	[root@db01 data]# mysqlbinlog mysql-bin.000002
	#这个时候我们是看不懂他写的内容的，因为行模式就是这样子的，需要加上参数
	[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
	
#优缺点：企业喜欢使用这个模式
	1.严谨,不容易丢失数据
	2.日志文件会越来越大,占用磁盘空间
	3.不易读
	
#修改错误数据
	mysql> update test22 set id=100;
	#修复数据前最好将表删除，否则会报已存在，或者把起始位置点修改成建表之后
	[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=518 /service/mysql/data/mysql-bin.000002  > /tmp/test22.sql
	[root@db01 data]# mysql -uroot -p123 < /tmp/test22.sql

4）混合模式

语句模式和行级模式混合使用，一般不适用，只使用行级模式

3.结合实例升级数据库

#1.提出方案升级数据库
#2.准备一台新机器，搭建mysql 5.6.48版本
#3.在旧数据库上备份所有数据（除了系统库）
	mysqldump -uroot -p123 -R --trigers -B world --master-data=2 > /tmp/123.sql
	--master-data=2 参数会在导出的数据加一行 位置点的记录
#4.将备份的数据传到新的数据库上
	scp  rsync  NFS  物理
#5.模拟旧的库有数据写入
	insert into test22 values(5),(6)
	update test22 set id=500 where id=3;
#6.将修改的数据导入数据库
	mysql -uroot -p123 < /tmp/123.sql
#7.将测试环境连接到新的数据库测试功能，业务
#8.测试没有问题切换生产环境数据库配置
#9.停止生产旧的数据库，使用binlog截取数据传输时新的数据
	通过sql里面的位置点截取binlog找到新增数据，导出sql语句
	[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
	[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=518 /service/mysql/data/mysql-bin.000002  > /tmp/test22.sql
#10.将新增数据导入新的数据库
#11.完成

4.二进制日志的管理操作

1）开启二进制日志

[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row

#5.7中必须配置server_id
[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
server_id=1

2）二进制日志的操作

#物理查看
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql      285 Mar  6  2017 mysql-bin.000001

#命令行查看
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1339 |
| mysql-bin.000002 |      1300 |
| mysql-bin.000003 |       120 |	默认初始大小是120
+------------------+-----------+

#刷新binlog
mysql> flush logs;
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1339 |
| mysql-bin.000002 |      1300 |
| mysql-bin.000003 |       167 |	row模式什么也没有写入，刷新binlog，大小变成167
| mysql-bin.000004 |       120 |	语句模式什么也没有写入，刷新binlog，大小变成143
+------------------+-----------+

mysql> show master status;

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000002';

3）事件

事件介绍
1）在binlog中最小的记录单元为event
2）一个事务会被拆分成多个事件（event）

事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。

4）刷新binlog

刷新binlog日志
1）flush logs;
2）重启数据库时会刷新
3）二进制日志上限（max_binlog_size）;当binlog达到1G,自动刷新

5）删除binlog

#删除二进制日志
#原则"
在存储能力范围内，能多保留则多保留
基于上一次全备前的可以选择删除

1.删除7天前的binlog
#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

show binary logs;

2.只保留3天的
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

3.删除指定binlog名之前的所有binlog(保留指定的binlog)
PURGE BINARY LOGS TO 'mysql-bin.00004';
		
4.删除所有binlog，相当于重置
mysql> reset master;

ansible做主从的时候有的人因为名字和位置点不好获取，他每次都会reset，如果先同步了所有数据那没问题，如果没同步问题就大了；

四、实战演练使用binlog恢复数据

1.对数据库一顿操作

#查看binlog信息
mysql> show master status;
#创建一个binlog库
mysql> create database binlog;
#使用binlog库
mysql> use binlog
#创建binglog_table表
mysql> create table binlog_table(id int);
#查看binlog信息
mysql> show master status;
#插入数据1
mysql> insert into binlog_table values(1);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#查看binlog信息
mysql> show master status;
#插入数据2
mysql> insert into binlog_table values(2);
#插入数据3
mysql> insert into binlog_table values(3);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#删除数据1
mysql> delete from binlog_table where id=1;
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#更改数据2为22
mysql> update binlog_table set id=22 where id=2;
#查看binlog
mysql> show master status;
#提交
mysql> commit;
#查看binlog信息
mysql> show master status;
#查看数据
mysql> select * from binlog_table;
#删表
mysql> drop table binlog_table;
#删库
mysql> drop database binlog;

2.binlog恢复数据

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000013';
#使用mysqlbinlog来查看
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013|grep -v SET
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000013
### UPDATE `binlog`.`binlog_table`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
#分析
update binlog.binlog_table
set
@1=22 --------->@1表示binlog_table中的第一列,集合表结构就是id=22
where
@1=2  --------->@1表示binlog_table中的第一列,集合表结构就是id=2
#结果
update binlog.binlog_table set id=22 where id=2;
#截取二进制日志
查看二进制日志后，发现delete语句开始位置是858
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858 /application/mysql/data/mysql-bin.000013
#临时关闭binlog
mysql> set sql_log_bin=0;
#执行sql文件
mysql> source /tmp/binlog.sql
#查看删除的库
mysql> show databases;
#进binlog库
mysql> use binlog
#查看删除的表
mysql> show tables;
#查看表中内容
mysql> select * from binlog_table;

3.问题：

数据库或表被误删除的是很久之前创建的（一年前，生成了上百个binlog）
如果基于binlog全量恢复，成本很高
1）可以用备份恢复+短时间内二进制日志，恢复到故障之前
2）非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback
3）延时从库 如果同一时间内和故障库无关的数据库都有操作，在截取binlog时都会被截取到

#都是废话，数据库出了有binlog可以恢复数据以外，还有全备，每天或者每周全被一次，全备之后，那个位置点之前的binlog全都可以删除，不可能一年有上百个binlog

生产中很多库，只有一个库的表被删除，我不可能把所有的库都导出来筛选，想一个办法过滤出来？
1）grep？
其他过滤方案？
1）-d 参数接库名
mysqlbinlog -d database --base64-output=decode-rows -vvv mysql-bin.000002
--base64-output  显示模式
-vvv			显示详细信息

五、慢查询日志

1.慢日志作用

1）是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件(不论是什么语句，增删改查)
2）通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

2.配置慢日志

#默认慢日志是不开启的

[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不被记录到慢查询日志，少于100行的sql语句查询慢的话不记录，一般不使用
min_examined_row_limit=100（鸡肋）

3.测试慢日志

insert city_new select * from city;
insert city_new select * from city_new;
insert city_new select * from city_new;
insert city_new select * from city_new;
... ...

#执行卡死，查看执行的sql执行时间，如果停不下来 可以 kill id
show processlist;

查看慢日志可以使用
cat /service/mysql/data/slow.log
#看起来太费劲，我们使用以下命令

mysqldumpslow -s r -t 10 /service/mysql/data/slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
	参数说明:
	-s:
	是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
	-t:
	是top n的意思，即为返回前面多少条的数据；
	-g:
	后边可以写一个正则匹配模式，大小写不敏感的；

第三方工具查看慢日志
yum provides pt-query-digest