MySQL二进制日志

作者: 吃可爱长大鸭 | 来源:发表于2019-12-09 15:57 被阅读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全部清除

MySQL二进制日志

MySQL-day10

MySQL存储引擎-锁

MVCC多版本并发控制

隔离级别

提交读

未提交读

阻塞所有操作，查询的时候，不能修改，修改的时候，不能查询

二进制日志

记录所有非查询语句（DDL，DML，DCL，DTL）

使用binlog恢复数据

恢复数据

binlog的刷新和删除

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读