mysql（九）

作者: Freestyle_0f85 | 来源:发表于2019-12-09 14:08 被阅读0次

MySQL存储引擎-锁

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

使用binlog恢复注意事项
一般恢复的时候需要临时关闭binlog的写入
set sql_log_bin=0

image.png

错误日志

作用：解决MySQL故障，排错

默认是否开启：开启

路径/名字： $datadir/$ hostname.err

[root@db01 data]# mysqladmin variables|grep 'error'

#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/application/mysql/data/$hostname.err
#查看方式
mysql> show variables like 'log_error';

一般查询日志

作用：记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启。

默认是否开启：关闭

路径/名字： $datadir/$ hostname.log

[root@db01 data]# mysqladmin variables |grep 'gen'
general_log        OFF                         
general_log_file  /application/mysql-5.6.40/data/db01.log 

mysql> show variables like '%gen%';
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | OFF                                     |
| general_log_file | /application/mysql-5.6.40/data/db01.log |
+------------------+-----------------------------------------+

vim /etc/my.cnf
general_log=1

慢日志

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  2 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
|  2 | root | localhost | NULL | Query   |    0 | init  | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+

作用：
1）是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
2）通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

默认是否开启：关闭

路径/名字： $datadir/$ hostname-slow.log

[root@db01 ~]# mysqladmin variables |grep 'slow'
slow_query_log OFF
slow_query_log_file /application/mysql-5.6.40/data/db01-slow.log

如何开启：

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = 1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/application/mysql/data/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=100（鸡肋）

模拟慢查询

mysql> use test
mysql> create table city select * from world.city;
mysql> insert into city select * from city;

mysql> show processlist;
+----+------+-----------+------+---------+------+--------------+-------------------------------------+
| Id | User | Host      | db   | Command | Time | State        | Info                                |
+----+------+-----------+------+---------+------+--------------+-------------------------------------+
|  2 | root | localhost | NULL | Query   |    0 | init         | show processlist                    |
|  5 | root | localhost | test | Query   |    9 | Sending data | insert into city select * from city |
+----+------+-----------+------+---------+------+--------------+-------------------------------------+
2 rows in set (0.01 sec)

mysql> kill 5;
Query OK, 0 rows affected (0.00 sec)


[root@db01 ~]# yum  localinstall -y percona-toolkit-3.0.11-1.el6.x86_64.rpm

[root@db01 ~]# pt-query-digest /application/mysql/data/slow.log

mysql（九）

MySQL存储引擎-锁

MVCC多版本并发控制

隔离级别

二进制日志

MySQL binlog工作模式

二进制日志实战操作

使用binlog恢复数据

binlog的刷新和删除

错误日志

一般查询日志

慢日志

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读