MySQL二进制日志
2019-12-09 本文已影响0人
吃可爱长大鸭
MySQL-day10
MySQL存储引擎-锁
img
1）什么是“锁”？
“锁”顾名思义就是锁定的意思。
2）“锁”的作用是什么？
在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。
3）MySQL中的锁
`排他锁`：保证在多事务操作时，数据的一致性。(修改操作会被阻塞)
`共享锁`：保证在多事务工作期间，数据查询时不会被阻塞。
`乐观锁`：谁先提交，以谁为准
`悲观锁`：谁先执行事务，并阻塞其他事务查询
4）锁的粒度
innodb：行级锁
myisam：表级锁
MVCC多版本并发控制
1）只阻塞修改类操作（排他锁），不阻塞查询类操作（共享锁）
2）乐观锁的机制（谁先提交谁为准）
隔离级别
mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1.read-commit（RC）
提交读
[root@db01 ~]# vim /etc/my.cnf
transaction_isolation=read-commit
mysql> select * from test.suo_new;
+-----+--------+
| id | name |
+-----+--------+
| 20 | zhang3 |
| 30 | li4 |
| 200 | qls |
+-----+--------+
3 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test.suo_new set name='wang5' where id=200;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
2.read-uncommit（RU）
未提交读
vim /etc/my.cnf
transaction_isolation=read-uncommit
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test.suo_new;
+-----+--------+
| id | name |
+-----+--------+
| 20 | zhang3 |
| 30 | li4 |
| 200 | qls |
+-----+--------+
3 rows in set (0.00 sec)
mysql> update test.suo_new set name='wang5' where id=200;
mysql> select * from test.suo_new;
+-----+--------+
| id | name |
+-----+--------+
| 20 | zhang3 |
| 30 | li4 |
| 200 | wang5 |
+-----+--------+
3.REPEATABLE-READ（RR）
[root@db01 ~]# vim /etc/my.cnf
transaction_isolation=REPEATABLE-READ
mysql> select * from test.suo_new;
+-----+--------+
| id | name |
+-----+--------+
| 20 | zhang3 |
| 30 | li4 |
| 200 | wang5 |
+-----+--------+
3 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test.suo_new set name='zhao4' where id=30;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
4.串行化
[root@db01 ~]# vim /etc/my.cnf
transaction_isolation=SERIALIZABLE
阻塞所有操作，查询的时候，不能修改，修改的时候，不能查询
二进制日志
作用：
记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录 记录所有DDL、DCL等语句 总之，二进制日志会记录所有对数据库发生修改的操作
begin；
sql1
sql2
sql3
...
commit;
记录所有非查询语句（DDL，DML，DCL，DTL）
**默认路径:**
默认是否开启：关闭
log-bin=mysql-bin
$datadir
### MySQL binlog工作模式
1.statement（默认）语句模式，记录所有非DQL语句(mysql 5.6)
优点：通俗易懂，占用磁盘空间小
缺点：不严谨
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
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.01 sec)
mysql> insert into binlog values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
[root@db01 data]# mysqlbinlog mysql-bin.000029
2.row 行级模式，记录所有非DQL语句，并且记录他们的变化过程
优点：严谨
缺点：不通俗易懂，占用磁盘空间大
log-bin=mysql-bin
binlog_format=row
3.mixed 混合模式，以上两种模式的混合
### 二进制日志实战操作
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000030 | 864 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1638 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 143 |
| mysql-bin.000004 | 143 |
| mysql-bin.000005 | 143 |
| mysql-bin.000006 | 143 |
| mysql-bin.000007 | 143 |
| mysql-bin.000008 | 143 |
| mysql-bin.000009 | 143 |
| mysql-bin.000010 | 143 |
| mysql-bin.000011 | 143 |
| mysql-bin.000012 | 143 |
| mysql-bin.000013 | 143 |
| mysql-bin.000014 | 143 |
| mysql-bin.000015 | 90301 |
| mysql-bin.000016 | 507 |
| mysql-bin.000017 | 167 |
| mysql-bin.000018 | 167 |
| mysql-bin.000019 | 222 |
| mysql-bin.000020 | 634 |
| mysql-bin.000021 | 795994 |
| mysql-bin.000022 | 28834 |
| mysql-bin.000023 | 143 |
| mysql-bin.000024 | 2181 |
| mysql-bin.000025 | 1125 |
| mysql-bin.000026 | 3923 |
| mysql-bin.000027 | 143 |
| mysql-bin.000028 | 143 |
| mysql-bin.000029 | 1125 |
| mysql-bin.000030 | 864 |
+------------------+-----------+
mysql> show binlog events in 'mysql-bin.000029';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------+
| mysql-bin.000029 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.40-log, Binlog ver: 4 |
| mysql-bin.000029 | 120 | Query | 1 | 220 | create database binlog |
| mysql-bin.000029 | 220 | Query | 1 | 325 | use `binlog`; create table binlog(id int) |
| mysql-bin.000029 | 325 | Query | 1 | 408 | BEGIN |
| mysql-bin.000029 | 408 | Query | 1 | 522 | use `binlog`; insert into binlog values(1),(2),(3) |
| mysql-bin.000029 | 522 | Xid | 1 | 553 | COMMIT /* xid=10 */ |
| mysql-bin.000029 | 553 | Query | 1 | 651 | drop database binlog |
| mysql-bin.000029 | 651 | Query | 1 | 757 | create database binlog
事件介绍
1）在binlog中最小的记录单元为event 2）一个事务会被拆分成多个事件（event）
事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。
使用binlog恢复数据
mysql> reset master;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> create database binlog1;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 223 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> use binlog1
mysql> create table binlog_table(id int);
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 336 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> insert into binlog_table values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 550 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> update binlog_table set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 760 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> delete from binlog_table where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 964 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> drop table binlog_table;
mysql> drop database binlog1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
+------+
| id |
+------+
| 10|
| 2 |
| 3 |
+------+
+------+
| id |
+------+
| 10|
| 2 |
+------+
恢复数据
mysql> show binary logs;
mysql> show binlog events in 'mysql-bin.000001';
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000001
起始：120
结束：760
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=760 /application/mysql/data/mysql-bin.000001 > /tmp/binlog_table.sql
#临时关闭binlog
mysql> set sql_log_bin=0;
#导入
[root@db01 data]# mysql < /tmp/binlog_table.sql
mysql> use binlog1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_binlog1 |
+-------------------+
| binlog_table |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from binlog_table;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
### 使用binlog恢复指定库数据
<pmysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2664 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2761 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> use test1
Database changed
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2861 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> insert into test1.tb1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> use test2
Database changed
mysql> create table tb2(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables from test1;
+-----------------+
| Tables_in_test1 |
+-----------------+
| tb1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from test1.tb1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
mysql> show tables from test2;
+-----------------+
| Tables_in_test2 |
+-----------------+
| tb2 |
+-----------------+
1 row in set (0.00 sec)
mysql> insert into test2.tb2 values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test2.tb2;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> update test1.tb1 set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> update test2.tb2 set id=20 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test1.tb1;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from test2.tb2;
+------+
| id |
+------+
| 1 |
| 20 |
+------+
2 rows in set (0.00 sec)
mysql> drop database test1;
Query OK, 1 row affected (0.00 sec)
起始：2567
结束：3752
[root@db01 data]# mysqlbinlog -d test1 --start-position=2567 --stop-position=3752 mysql-bin.000001 >/tmp/3.sql
binlog的刷新和删除
刷新binlog
1.MySQL重启会刷新binlog
[root@db01 data]# /etc/init.d/mysqld restart
2.执行，flush logs
mysql> flush logs
3.mysqladmin
[root@db01 data]# mysqladmin flush-log
4.binlog到1G会自动刷新
5.mysqldump -F
删除binlog
1.根据binlog的生存时间
#临时生效
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 7 day;
3.根据binlog 名字删除
PURGE BINARY LOGS TO 'mysql-bin.000010';
4.reset master全部清除
