linux mysql 系统日志_linux系统mysql日志(1)

一、事务介绍

1.事物的周期

1)成功的周期

begin;

sql1;

sql2;

sql3;

...

commit;

2)失败的周期

begin;

sql1;

sql2;

sql3;

...

rollback;

2.事务的特性

A：原子性

C：一致性

I：隔离性

D：持久性

二、事务的日志

1.redo log

redo，顾名思义“重做日志”，是事务日志的一种

1)作用

在事务ACID过程中，实现的是“D”持久化的作用。

REDO：记录的是，内存数据页的变化过程

特性:WAL(Write Ahead Log)日志优先写

2)图解

3)文字描述流程

#修改

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得到一个结果

2.undo log

undo，顾名思义“回滚日志”，是事务日志的一种

1)作用

在事务ACID过程中，实现的是“A”原子性的作用。当然CI的特性也和undo有关

2)图解

3.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

三、事务中的锁

1.什么是锁

“锁”顾名思义就是锁定的意思。

2.作用

在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。

3.锁的类别

排他锁：保证在多事务操作时，数据的一致性。(在我修改数据时，其他人不得修改)

共享锁：保证在多事务工作期间，数据查询时不会被阻塞。

乐观锁：多实务操作时，数据可以同时修改，谁先提交，以谁为准

悲观锁：多实务操作时，数据只有一个人可以修改

4.多版本并发控制

1.只阻塞修改类操作(排他锁)，不阻塞查询类操作(共享锁)

2.乐观锁的机制(谁先提交谁为准)

5.锁的粒度

1.MyIsam：表级锁

2.Innodb：行级锁

四、事务中的隔离级别

1.四种隔离级别

1.RU级别：READ UNCOMMITTED(独立提交)：未提交读，允许事务查看其他事务所进行的未提交更改

2.RC级别：READ COMMITTED：允许事务查看其他事务所进行的已提交更改，查看不需要重新记入数据库

3.RR级别：REPEATABLE READ：允许事务查看其他事务所进行的已提交更改，查看数据需要重新进入数据库(InnoDB 的默认级别)

4.串行化：SERIALIZABLE：将一个事务的结果与其他事务完全隔离

2.查看隔离级别

#查看隔离级别

mysql> show variables like '%iso%';

3.设置隔离级别

1)设置RU级别

[root@db03 ~]# vim /etc/my.cnf

transaction_isolation=read-uncommit

2)设置RC级别

[root@db03 ~]# vim /etc/my.cnf

transaction_isolation=read-commit

4.名词

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'命令

1.二进制日志管理操作

1)开启二进制日志

[root@db02 ~]# vim /etc/my.cnf

[mysqld]

server_id

log_bin=/usr/local/mysql/data/mysql-bin

2)查看二进制日志

#物理查看

[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%';

3)事件

1.什么是事件

1)在binlog中最小的记录单元为event

2)一个事务会被拆分成多个事件(event)

2.事件(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.根据存在时间删除日志

#临时生效

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;

2.二进制日志作用

1.记录已提交的DML事务语句，并拆分为多个事件(event)来进行记录

2.记录所有DDL、DCL等语句，总之，二进制日志会记录所有对数据库发生修改的操作

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

4.数据的备份与恢复

5.数据的复制

1)数据库的备份与恢复

1>添加数据

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)

2>删除数据

#误删除两条数据

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)

3>通过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

2)使用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