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

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

MySQL存储引擎

一、MySQL存储引擎

1.什么是存储引擎

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

2.innodb与myisam的区别

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

3.表空间

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

4.事务的日志

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

5.锁的种类

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

6.隔离级别

模式全称功能
RUread uncommitted未提交读,又称独立提交,允许事务查看其他事务所进行的未提交更改
RRrepeatable read可重复读 InnoDB 的默认级别(commit提交以后可能看不到数据变化,必须重新连接)
RCread 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
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页