错误查询二进制日志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.
