回顾存储引擎

1.什么是存储引擎

mysql的文件系统，主要就是与次盘进行交互

innodb和myisam

2.innodb和myisam的区别

1.InnoDB是行级锁，MyISAM是表级锁
2.InnoDB支持事务，MyISAM不支持事务
3.InnoDB支持CSR，MyISAM不支持CSR

3.表空间

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

4.事务的日志

redo log：重做的日志 记录的是数据的变化，帮助在内存里面完成数据的修改
undo log：不做日志    记录的是是否执行了commit，执行数据修改前的状态，为了帮我们回滚数据

5.事务的锁

共享锁：在有事务操作数据时该数据也可以被查询
排它锁：在一个事务修改数据时，该数据无法被其他事物执行
乐观锁：多事务同时操作时，谁先提交以谁修改的数据为准
悲观锁：多事务同时操作时，数据只有一个人可以修改

6.隔离级别

1.RU：未提交读，一个事务还没有提交，其他事务就可以查看已经修改的值
2.RR：可重复读
3.RC：允许查看其他事务已提交的数据
4.串行化：将事务完全隔离

mysql 日志

一、mysql错误日志

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

#一般数据库配置文件会写的配置
[root@db04 ~]# vim /etc/my.cnf

[mysqld]
basedir=/service/mysql
datadir=/service/mysql/data
port=3306
socket=/tmp/mysql.sock
skip_name_resolve
log_error=mysql.err

[mysql]
socket=/tmp/mysql.sock

二、一般查询日志

1.一般查询日志默认是关闭的
2.默认路径是 $datadir/ 下面，以 '主机名'.log 命名
3.配置开启一般查询日志
    [root@db04 ~]# vim /etc/my.cnf
    [mysqld]
    general_log=on
    general_log_file=/service/mysql/data/general.log
4.查看方式：
    [root@db04 ~]# mysqladmin -uroot -p123456 variables | grep 'general'
    mysql> show variables like '%general%';

#注意：一般不开启，因为他会记录每一步在数据库中的操作，生产环境中程序会跑很多的sql，文件会越来愈大，且不安全

三、二进制日志

#注意：
binlog 大小默认 生成时就是 120
binlog 查看主节点时，show master status
    文件名一定时最新的binlog，为支点一定时新的binlog的大小
    binlog的位置点是跟着大小改变的

#总结：
1.二进制日志默认是关闭的
2.查看二进制日志的方式
    [root@db04 data]# mysqladmin -uroot -p123456 variables | grep -w log_bin
    mysql> show variables like '%log_bin%';
3.配置开启binlog
    vim /etc/my.cnf
    server_id=1
    log_bin=mysql-bin
    log_bin=/server/mysql/data/mysql-bin        #两种方式只用一种，一直用不要改
4.默认路径是 $datadir/ 下面，以 mysql-bin.00000N 来命名

1.二进制日志管理操作

1）开启二进制日志

[root@db04 ~]# vim /etc/my.cnf
[mysqld]
server_id=1                #mysql5.6可以加可以不加，但是5.7中必须配置server_id
log_bin=mysql-bin

2）查看二进制文件

#物理查看
[root@db04 ~]# ll /service/mysql/data/
-rw-rw---- 1 mysql mysql      120 Apr 24 10:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql       19 Apr 24 10:17 mysql-bin.index

#mysql查看
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

3）事件的介绍

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

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

4）刷新binlog

1.flush logs
2.重启数据库
3.二进制日志大小达到1G时，会自动刷新；max_binlog_size（可以修改大小）

5）删除binlog

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

#删除方式：
1.删除七天前数据
    #临时生效
    SET GLOBAL expire_logs_days = 7;
    #永久生效
    [root@db01 data]# vim /etc/my.cnf
    [mysqld]
    expire_logs_days = 7

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

3.删除指定binglog名字之前的binlog(保留指定binlog以后的binlog)
PURGE BINARY LOGS TO 'mysql-bin.000010';

4.重置binlog
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

2.二进制日志作用

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

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

1）数据的备份和恢复

1> 添加数据

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

2> 恢复数据

#使用mysqlbinlog来查看应该恢复数据的部分
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000013
#截取二进制日志，根据位置点取出应该恢复数据的部分
查看二进制日志后，发现delete语句开始以上的位置点是858
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858 /application/mysql/data/mysql-bin.000013 > /tmp/binlog.sql

#执行sql文件
mysql> source /tmp/binlog.sql
#查看删除的库
mysql> show databases;
#进binlog库
mysql> use binlog
#查看删除的表
mysql> show tables;
#查看表中内容
mysql> select * from binlog_table;

3.二进制日志的工作模式

1）查看工作模式

[root@db04 data]# mysqladmin -uroot -p123456 variables | grep -w 'binlog_format'

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

#工作模式类型
1.语句模式
2.行级模式
3.混合模式

2）语句模式 STATEMENT

语句模式 STATEMENT （mysql 5.6 默认的模式）

#语句模式查看binlog
[root@db04 data]# mysqlbinlog mysql-bin.000002
...
#200424 10:38:35 server id 1  end_log_pos 410 CRC32 0x7b8723be     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1587695915/*!*/;
insert into binlog values(1),(2),(3)
/*!*/;
# at 410
#200424 10:38:56 server id 1  end_log_pos 518 CRC32 0x8fbb947e     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1587695936/*!*/;
update binlog set id=10 where id=2
/*!*/;
# at 518
...

#优缺点：
    1.不严谨，不安全
    2.语句容易理解
    3.日志文件不是很大，不占用磁盘

3）行级模式 row （在企业里，基本全都是用row模式）

行级模式 row (mysql 5.7 默认的模式)

#修改binlog模式为 row
[root@db04 data]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin
binlog_format=row

#行级模式查看binlog
[root@db04 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000003
...
#200424 11:03:29 server id 1  end_log_pos 336 CRC32 0xedd13d8b     Table_map: `test`.`row` mapped to number 70
# at 336
#200424 11:03:29 server id 1  end_log_pos 386 CRC32 0x4a6fdde7     Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `test`.`row`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`row`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`row`
### SET
###   @1=3 /* 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

#优缺点：
    1.严谨，安全
    2.语句不容易理解
    3.日志文件很大，比较占用磁盘

4）混合模式

结合上面两中模式使用

四、结合binlog升级数据库

#1.准备一台服务器，安装数据库5.6.48版本
#2.把旧数据库数据导出
[root@db04 data]# mysqldump -uroot -p123 -R -B binlog > /tmp/123.sql
#3.将数据传到新的机器
[root@db04 data]# scp /tmp/123.sql 172.16.1.51:/tmp
#4.旧数据库仍然有数据写入
mysql> insert into binlog_table values(4),(5),(6);
#5.新库导入数据
[root@db01 ~]# mysql -uroot -p123 < /tmp/123.sql
#6.把web服务程序连接数据库信息修改为新库的IP
#7.停止旧库，导出新写入的数据
[root@db04 data]# mysqlbinlog --start-position=120 --stop-position=332 mysql-bin.000001 > /tmp/xin.sql
    起始位置点，当你导出数据时使用 --master-data=2 sql文件里面会有提示
    结束位置点可以直接看最新的binlog的大小
#8.将新写入的数据传到新库
[root@db04 data]# scp /tmp/xin.sql 172.16.1.51:/tmp
#9.将新的数据导入数据库
[root@db01 ~]# mysql -uroot -p123 < /tmp/xin.sql
#10.查看数据，测试

五、慢查询日志（慢日志）

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不被记录到慢查询日志
min_examined_row_limit=100（鸡肋）

3.测试慢日志

#1.复制表数据到新的表
mysql> create table slow select * from city;
#2.多插入几次数据
mysql> insert into slow select * from slow;
mysql> insert into slow select * from slow;
mysql> insert into slow select * from slow;
mysql> insert into slow select * from slow;
mysql> insert into slow select * from slow;

#3.查看慢日志
[root@db04 data]# cat slow.log
[root@db04 data]# mysqldumpslow -s -r -t 10 slow.log
参数说明:
-s:
是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t:
是top n的意思，即为返回前面多少条的数据；
-g:
后边可以写一个正则匹配模式，大小写不敏感的；