错误查询二进制日志mysqlbinlog1

事务的日志

1. 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,返回ok
#过程
ibdata1 --> data buffer page --> redobuffer page -->redo log
#数据库重启或者隔一段时间，redo log中的日志会写入到ibdata1（.ibd）中

#查询
1.首先将表中id=1的行所在数据页加载到内存中data buffer page
2.将redo log中id=1变成id=2的变化过程取加载到redo buffer page
3.通过data buffer page和redo buffer page得到一个结果
#过程
ibdata1 --> data buffer page '???' redo buffer page <-- redo log

---

2.undo log,回滚日志，也是事务日志的一种

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

#redo log 和undo log 都是事务的日志，不是数据库命令的日志

断电的三种情况(如何恢复内存中的数据)

1.执行了commit，启动数据库，数据恢复，id=2(CSR)
2.没有执行commit，但是数据保存到了redo log(过了一段时间),id=2，但是因为undo buffer page中没有记录commit，所以 id=1
3.没有执行commit，数据没有保存到redo log, id=1

#源数据+变化过程=修改后的内容

redo和undo的存储位置

#redo位置
[root@db02 ~]# ll /usr/local/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@db02 ~]# ll /usr/local/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'隔离性的作用。

#查看mysql存储引擎
mysql> show create table cs;
| cs    | CREATE TABLE `cs` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

#mysql innodb存储引擎支持锁，pyisam不支持锁

#创建指定innodb存储引擎的表
mysql> create table suo(id int) engine=innodb;

#锁
db03 tty1
mysql> create table suo1(id int);
mysql> insert suo1 values(1);
mysql> select * from suo1;
+------+
| id   |
+------+
|    1 |
+------+
mysql> update suo1 set id=2 where id=1;
mysql> select * from suo1;
+------+
| id   |
+------+
|    2 |
+------+

db03 tty2
mysql> update suo1 set id=3 where id=1;
mysql> select * from suo1;
+------+
| id   |
+------+
|    2 |
+------+


#mysql里面谁先提交谁为准，Redis里面谁后提交谁为准

innoDB中锁的类别

排他锁：在我'修改时'，别人不能修改（#行级锁）
共享锁：保证在多事务工作期间，'数据查询时'不会被阻塞。

乐观锁：多事务操作时，数据可以同时修改，谁先提交，以谁为准（#购票）
悲观锁：多事务操作时，数据只有一个人可以修改(#购物车+购票)

多版本并发控制（多锁一起用）

1.只阻塞修改类操作（排他锁），不阻塞查询类操作（共享锁）
2.乐观锁的机制（谁先提交谁为准）

锁的粒度

1.myisam		表级锁
2.innoDB		行级锁,效率更高

---

隔离

事务中的隔离级别

1.RU级别：READ UNCOMMITTED（独立提交）：'未提交读'，允许事务查看其他事务所进行的未提交更改,
问题1.不安全，2.会出现脏读

2.RC级别：READ COMMITTED：允许事务查看其他事务所进行的已提交更改，查看'不需要重新进入数据库'
3.RR级别：REPEATABLE READ：允许事务查看其他事务所进行的已提交更改，查看数据需要'有时重新进入数据库'（'InnoDB 的默认级别'）

4.串行化：SERIALIZABLE：将一个事务的结果与其他事务'完全隔离'(最大程度的隔离)

#查看隔离级别
mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |  #RR级别，系统默认
+---------------+-----------------+

#设置隔离级别(RU RC #RR)
[root@db03 ~]# vim /etc/my.cnf
#log_bin=mysql-bin
transaction_isolation=read-uncommit
transaction_isolation=read-commit
[root@db03 ~]# /etc/init.d/mysqld restart
mysql> show variables like '%iso%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |  #RU级别
+---------------+------------------+
mysql> update suo1 set id=1 where id=100;
mysql> select * from suo1;
+------+
| id   |
+------+
|    1 |
+------+
mysql> rollback;
mysql> select * from suo1;
+------+
| id   |
+------+
|  100 |	#如果id=1已经被使用，那么这种现象即为脏读	
+------+

名词：
1.RU级别会出现'脏读'：RU级别，执行事务修改数据，被读取，但是数据最终回滚了，查询到的数据就是脏
2.幻读：删除所有表数据，删除的同时有人插入数据，查看数据时'以为是'没删干净
3.不可重复读：修改数据后被读取，被读取之后再次修改数据，两次数据不一致(#访问次数)

查看隔离级别

#查看隔离级别
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

---

mysql日志

mysql 错误日志

1.错误日志'默认是关闭的'
2.错误日志文件默认路径是 $datadir/主机名.err

查看错误日志路径
0）初始化之后
rm -rf /usr/local/mysql/data/ibdata*
./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
/etc/init.d/mysqld start
1）命令行
[root@db02 ~]# mysql -e "show variables like '%log_err%'"
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| log_error           | 							 |
+---------------------+---------------------------------+
2）SQL语句
mysql> show variables like 'log_err';

#配置错误日志
    [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  （#相对路径的话，放到data目录）

    [mysql]
    socket=/tmp/mysql.sock

#查看错误日志
less /usr/local/mysql/data/mysql.err

#作用
mysql数据库启动不了的时候，查看报错

#购买了阿里云主机，安装mysql后，第一时间修改mysql错误日志，来指定mysql错误日志的格式

mysql查询日志

1.查询日志默认是'关闭的'
2.查询日志'文件默路径'是 $datadir/主机名.log

查看查询日志路径
    mysql> show variables like '%general%';
    +------------------+--------------------------------+
    | Variable_name    | Value                          |
    +------------------+--------------------------------+
    | general_log      | OFF                            |	#
    | general_log_file | /usr/local/mysql/data/db02.log |
    +------------------+--------------------------------+

配置查询日志
    [root@db02 ~]# vim /etc/my.cnf
    [mysqld]
    log_err=/usr/local/mysql/data/mysql.err
    general_log=on/1
    general_log_file=/usr/local/mysql/data/db02.log

    [mysql]
    socket=/tmp/mysql.sock
    
#查看查询日志
less /usr/local/mysql/data/db02.log

#作用
记录sql语句执行过的命令（#一般不用 Ctrl+R）

二进制日志

1.二进制文件'默认是关闭的'
2.二进制配置'路径和名字'由配置文件决定，一般保存在 $datadir/mysql-bin 命名(默认)

1.二进制日志管理操作

1）开启二进制日志

配置bin_log
3.配置binlog
    [root@db02 ~]# vim /etc/my.cnf
    [mysqld]
    server_id
    log_bin=/usr/local/mysql/data/mysql-bin
	#log-bin=/usr/local/mysql/data/mysql-bin

#mysql5.7(log_bin)只支持下划线_ ，5.6支持 - _
#mysql5.7必须指定server_id
#binlog生成默认大小是120（刷新后是143），也是binlog当前的位置点
#数据库重启会生成新的mysql-bin.00000x
#企业全备和增备一起用

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
[root@db03 ~]# mysql -uroot -p123 -e'show variables like "%log_bin%"'

#数据库查看
mysql> show variables like '%bin%'		#查看like命令和默认
| log_bin                                 | OFF                  |
show variables like 'log_bin'
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |		#默认关闭
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

3）事件

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

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

#查看bin_log

物理查看
[root@db03 ~]# ll /service/mysql/data/
-rw-rw---- 1 mysql mysql      120 Jul 20 21:40 mysql-bin.000001
-rw-rw---- 1 mysql mysql      143 Jul 20 21:52 mysql-bin.000002	#最新（143）

SQL语句查看
[root@db03 ~]# vim /etc/my.cnf
log_bin=mysql-bin
server_id=2
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000010 |      120 |

#重启数据库（修改模式）会生成新的bin_log，默认的bin_log大小变成143，写入了一点系统记录

4）刷新binlog

1）flush logs;		#会产生新的log-bin.00000x  #可以和全备一起用
2）重启数据库时会刷新
3）二进制日志上限，默认1G（可以使用 max_binlog_size=xx修改）

5）删除binlog

1.根据存在时间删除日志
#临时生效
mysql> set global expire_logs_days=7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

2.使用purge命令删除（删除从现在起 3天前）
mysql> purge binary logs before now() -interval 3 day;

3.根据文件名删除（只保留10）
PURGE BINARY LOGS TO 'mysql-bin.000010';

4.使用reset master（重置binlog，删除所有的binlog）（会导致主从复制卡住）
mysql> reset master; 

#不能使用rm删除binlog,因为索引里仍有该索引的记录
[root@db03 data]# cat mysql-bin.index 
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003

#没有备份的情况下，删除binlog的话无法找回

2.二进制日志作用

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

3.如果我拥有数据库搭建开始，'有所有的二进制日志'，那么我可以'把数据恢复到任意时刻'
但是一般使用'全备和增备'恢复数据库数据
4.利用bin_log进行数据的备份与恢复
5.数据的主从复制

#查看帮助
[root@db03 ~]# mysqldump --help

#使用mysqldump命令需要指定 -S /tmp/mysql.sock

1）数据库的备份与恢复

1>添加数据

mysql> create database binlog;
mysql> use binlog
mysql> create table binlog(id int);
mysql> insert binlog values(1),(2),(3);
mysql> insert binlog values(4);
mysql> insert binlog values(5);

mysql> select * from binlog;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

2>删除数据

#误删除两条数据
mysql> delete from binlog where id=5;
mysql> delete from binlog where id=4;
mysql> delete from bl where id=5 or id=4;

3>通过binlog恢复数据

#查看二进制日志找到位置点
[root@db02 data]# mysqlbinlog mysql-bin.000002
...
...
# at 4
...
...
# at 120 (位置点)
create database bl
/*!*/;
# at 208
use `bl`/*!*/;
SET TIMESTAMP=1595361536/*!*/;
create table bbl(id int)
/*!*/;

#将位置点之间的数据取出
[root@db02 data]# mysqlbinlog --start-position=631 --stop-position=978 mysql-bin.000002 > /tmp/hf.sql

#将数据导入回去
[root@db02 data]# mysql < /tmp/45.sql

#查看
select * from binlog

#一个事务可以包含多个位置点
#企业数据库不会随便重启，所以binlog可能会很大
#通过binlog恢复数据是不现实的，因为企业中的binlog很大（不好操作）
#企业中不会超过一个月的binlog，但是binlog数据量依然很大

2）使用binlog配合数据库升级（企业数据库的切换)

1.准备一台新的数据库，版本为5.6.38
2.旧数据库备份数据（同时导出多个库）（要想使用--master-data=2，数据库必须配置bin_log）
	[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
	select * from xx

#解决新数据写入的方法
1.先导入'旧数据'，将web中代码的地址修改为新库，再通过binlog将'新数据'导出再导入新库就可以了
2.先做旧库和新库做'主从复制'，再根据位置点导入'旧数据',再'切库'，再断开主从（切库会导致数据少量丢失）

报错

mysql> insert bbl values(1);
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.