【mysql】の 锁机制 | 事务 | 二进制日志 | 备份还原（删库也不怕跑路了~）

1、并发控制

mysql 数据库 同一时间会有多个用户一起使用

1.1 锁机制

加锁是为了限制别人的操作，不会影响自己。

锁类型：

• 读锁：共享锁，也称为 S 锁，只读不可写（包括当前事务） ，多个读互不阻塞 只能读 不能写 别人也能看
• 写锁：独占锁，排它锁，也称为 X 锁，写锁会阻塞其它事务（不包括当前事务）的读和写 写锁 别人不可读也不可写
• S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，

锁粒度：

• 表级锁：MyISAM 存储引擎 mysql
• 行级锁：InnoDB。两个都能操作，但是只会对其中一个进行修改。

1.2 手动加锁

一般进行数据备份时，会手动加锁

 格式：
 lock tables 表名 type;
 //type类型分为：
     read
     write

整个数据库加锁：

 flush tables with read lock;

示例：

 lock tables students read;

1.3 解锁

 unlock tables;    //解锁，只能把所有表都解锁

1.4 测试

 drop table students;

2、事务（transactions）

• 事务是一次成功的、完整的操作过程。有了事务，可以保障数据的完整性和安全性。
• 事务可以是一条，可以是多条指令的集合。
• 事务可以减少i/o，也就是减少输入输出的过程。

2.1 A C I D 特性

A：atomicity 原子性：整个事务中的所有操作要么全部成功执行，要么失败后回滚。只要中间有一步失败，就是失败。

C：consistency 一致性：数据库总是从一个一致性状态转换为另一个一致性状态,类似于质量守恒定律(A1wB 0 A1w 给 B转1w 始终保持A+B=1w)

I： Isolation 隔离性 ：一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发.(不最后提交看不到，脏数

D：durability 持久性：一旦事务提交，其所做的修改会永久保存于数据库中

2.2 基础命令

 `人为开启事务：`
 begin
 begin work
 start transaction     //这三个命令都是人为开启事务
 ​
 commit     //保存、执行、提交。永久保存到硬盘上，如果没提交就是存到内存中。
 ​
 rollback   //回滚，也就是撤销，但是重大级的修改，比如drop、新建表等，没办法撤销

是否需要自动提交：

 set autocommit=0    //是否自动提交事务  退出后要再加，改成1，就是自动提交。

2.3 死锁

两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

 show processlist;
 kill 13    //13是进程列表

2.4 事务隔离级别

MySQL 支持4种隔离级别，事务隔离级别 从上至下更加严格

 select @@tx_isolation;    //系统隔离级别，是系统自带的变量
 +-----------------+
 | @@tx_isolation  |
 +-----------------+
 | REPEATABLE-READ |
 +-----------------+
 ​
 SET tx_isolation='READ-UNCOMMITTED未提交可读|READ-COMMITTED提交可读|REPEATABLE-READ可重复读|SERIALIZABLE'串读

 vim /etc/my.cnf
 [mysqld]
 transaction-isolation=READ-UNCOMMITTED
 systemctl restart mysqld

MVCC和事务的隔离级别：

MVCC（多版本并发控制机制）只在READ COMMITTED（提交可读）和REPEATABLE READ（可重复读） 两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

3、日志

MySQL 支持丰富的日志类型，如下：

• 事务日志：transaction log

   事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead logging事务日志文件：ib_logfile0、ib_logfile1
  

• 错误日志 error log

• 通用日志 general log

• 慢查询日志 slow query log

• 二进制日志 binary log，比较重要，备份时使用

• 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件 主做了什么修改，把修改的的内容放到中继日志里，发给备，备读取了之后，也同步进行修改。

3.1 事务日志

事务日志：transaction log

• redo log：实现 WAL（Write Ahead Log) ，数据更新前先记录redo log
• undo log：保存与执行的操作相反的操作，用于实现rollback

事务型存储引擎自行管理和使用，建议和数据文件分开存放

Innodb事务日志相关配置：

 show variables like '%innodb_log%';
 innodb_log_file_size   50331648 #每个日志文件大小  字节
 innodb_log_files_in_group 2     #日志组成员个数
 innodb_log_group_home_dir ./    #事务文件路径

 ll -h /var/lib/mysql

修改事务日志的大小：

 vim /etc/my.cnf
 [mysq1d]
 innodb_log_file_size=503316480    //500M
 innodb_1og_fi1es_in_group=3       //3个

事务日志性能优化：

 innodb_flush_log_at_trx_commit=0|1|2
 ​
 select @@innodb_flush_log_at_trx_commit;    //查看默认值

双1操作：

• 0：每秒写1次。 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
• 1：此为默认值， 立即写入磁盘。日志缓冲区将写入日志文件，并在每次事务完成后执行刷新到磁盘。 这是完全遵守ACID特性
• 2：每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些， 但操作系统或停电 可能导致最后一秒的交易丢失

事务提交了，意味着操作存储在了磁盘上。

系统比程序稳定

安全选1，性能选2，尽量不要选0

3.2 错误日志

错误日志

• mysqld启动和关闭过程中输出的事件信息
• mysqld运行中产生的错误信息
• event scheduler运行一个event时产生的日志信息
• 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误文件路径

 SHOW GLOBAL VARIABLES LIKE 'log_error' ;
 ​
 yum安装：
 cat /var/log/mysqld.log    //错误日志路径

过滤错误日志：

 grep -i error /var/log/mysqld.log

3.3 通用日志

通用日志：localhost.log ，名字和主机名有关。

通用日志：记录对数据库的通用操作，包括：错误的SQL语句。保存在：file（默认值）或 table（mysql.general_log表）

通用日志相关设置：

 general_log=ON|OFF
 general_log_file=HOSTNAME.log
 log_output=TABLE|FILE|NONE

示例：启用通用日志并记录至文件中

 select @@general_log;        //默认没开启
 set global general_log=1;   //1是开启，0是关闭
 ​
 SHOW GLOBAL VARIABLES LIKE 'log_output';   //默认通用日志存放在文件中
 select @@general_log_file;     //通用日志存放的文件路径
 ll -h /var/lib/mysql

3.4 慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作

慢查询相关变量：

 slow_query_log=ON|OFF   //开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
 long_query_time=N      //慢查询的阀值，单位秒,默认为10s
 slow_query_log_file=HOSTNAME-slow.log   //慢查询日志文件
 ​
 log_queries_not_using_indexes=ON    //不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
 log_slow_rate_limit = 1   //多少次查询才记录，mariadb特有
 log_slow_verbosity= Query_plan,explain    //记录内容,和explain配合
 log_slow_queries = OFF      //同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除
 ​
 //开启
 set  global slow_query_log=1;
 set long_query_time=1;
 ​
 //测试
 select sleep(10)

修改配置文件：

 vim /etc/my.cnf
 slow_query_log=ON
 systemctl restart mysqld
 cat /var/lib/mysql/localhost-slow.log

3.5 二进制日志⭐⭐⭐

• 记录导致数据改变或潜在导致数据改变的SQL语句
• 记录已提交的日志
• 不依赖于存储引擎类型

功能：通过"重放"日志文件中的事件来生成数据副本

注意：建议二进制日志和数据文件分开存放

3.5.1 二进制日志 的3种格式

3.5.2 二进制日志文件 的2种格式

 mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002

 mysql|mariadb-bin.index，文本格式，记录当前已有的二进制日志文件列表

3.5.3 查看二进制日志的格式

方法一：

 show variables like 'binlog_format';    //5.5之前的老版本默认的格式是statement语句，新版本默认的都是row行。

方法二：

 select @@binlog_format;

3.5.4 如何开启二进制日志⭐

有两个变量需要同时开启：

 log_bin=mysql-bin      //指定文件位置。默认是OFF关闭，也就是不启用二进制日志功能
 ​
 select @@log_bin;
 //注意:变量log_bin 和 上面的 log_bin(服务器选项) 是两个不同的意思

 sql_log_bin=ON   //是否记录二进制日志，默认ON开启。支持动态修改，系统变量，而非服务器选项
 ​
 select @@sql_log_bin;

示例：开启二进制日志

 [root@7-3 ~]# vim /etc/my.cnf
 log_bin=/data/mysql-bin   //指明二进制日志的位置，后面的路径只是前缀，正常后面还要有.000001、.000002等
 server-id=1              //指明数据库的编号，1可以自己定义
 ​
 [root@7-3 ~]# chown -R mysql.mysql /data/      //修改权限
 [root@7-3 ~]# systemctl restart mysqld
 [root@7-3 ~]# cd /data/
 [root@7-3 data]# ll
 总用量 12
 -rw-r----- 1 mysql mysql 454 7月   3 15:22 mysql-bin.000001
 -rw-r----- 1 mysql mysql 154 7月   3 15:22 mysql-bin.000002     //每重启一次，就会多出来一个编号
 -rw-r----- 1 mysql mysql  46 7月   3 15:22 mysql-bin.index     //索引
 [root@7-3 data]# cat mysql-bin.index    //查看索引，显示二进制文件的个数
 /data/mysql-bin.000001
 /data/mysql-bin.000002

3.5.5 基础命令

 show master logs;
 +------------------+-----------+
 | Log_name         | File_size |
 +------------------+-----------+
 | mysql-bin.000001 |       431 |
 +------------------+-----------+

 show master status;
 +------------------+----------+--------------+------------------+-------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.000001 |      431 |              |                  |                   |
 +------------------+----------+--------------+------------------+-------------------+

注意: 在mysql软件里面删

 格式：
 purge binary logs to '日志名称';
 ​
 示例：
 purge binary logs to 'mysql-bin.000002';  //删除2号之前的日志,也就是只删除1

 reset master;

 flush logs;

3.5.6 mysqlbinlog 离线查看日志

二进制日志的客户端命令工具，支持离线查看二进制日志

 insert into teachers values(null,'wxy',22,'M');     //先人为对数据库进行操作
 ​
 [root@7-3 data]# mysqlbinlog -v /data/mysql-bin.000001    //再查看日志
 ### INSERT INTO `hellodb`.`teachers`
 ### SET
 ###   @1=5
 ###   @2='wxy'
 ###   @3=22
 ###   @4=2
 # at 400     //操作节点、时间点，第400个位置。

备份还原的命令是: mysqldump

4、备份还原

mysql面试题：

• 主从复制
• 读写分离

4.1 备份类型

• 完全备份：备份整个数据集

• 部分备份：只备份数据子集，如部分库或表

  • 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

  增量备份的还原规则是：先备先还,后备后还

  • 差异备份：仅备份最近一次完全备份以来变化的数据或者增长的数据，备份较慢，还原简单，直接还原最新的备份

注意:二进制日志文件(对应增量备份),不应该与完全备份的文件,放在同一磁盘

4.2 冷|温|热备份

• 冷备：读、写操作 均不可进行，数据库停止服务
• 温备：可读不可写, 读操作可执行, 但写操作不可执行
• 热备：读、写操作均可执行 用的最多

MyISAM：温备，不支持热备 不支持 事务

InnoDB：都支持

备份数据

• cp, tar 等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
• LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
• mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
• xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
• MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
• mysqlbackup：热备份， MySQL Enterprise Edition 组件
• mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

4.3 完全备份

4.3.1 物理冷备份

通过打包备份 数据库文件夹的方式备份（一般用于数据库迁移）

备份方式： 冷备份，一定要先停数据库

备份工具： cp tar scp rsync（同步软件）等

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

示例: 冷备份还原数据库

 7-3:
 [root@7-3 ~]# systemctl stop mysqld   //首先关闭数据库⭐
 [root@7-3 ~]# scp -r /var/lib/mysql 192.168.125.120:/opt   //把数据库备份到远程主机⭐
 [root@7-3 ~]# rm -rf /var/lib/mysql     //模拟删库故障
 ​
 7-2:
 [root@7-2 ~]# scp -r /opt/mysql 192.168.125.130:/var/lib/
 ​
 7-3:
 [root@7-3 ~]# chown -R mysql:mysql /var/lib/mysql    //z注意权限⭐
 [root@7-3 ~]# systemctl start mysqld     //开启
 [root@7-3 ~]# mysql -uroot -pabc123     //登录

4.3.2 mysqldump 备份还原

热备份, 使用时 mysql软件需要保持开启

mysqldump的MyISAM存储引擎相关的备份选项：

MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

 -x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
 -l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
 #注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

mysqldump的InnoDB存储引擎相关的备份选项：

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用

 --single-transaction
 #此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
 #此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用

生产环境实战备份策略

4.3.2.1 备份表

 格式:
 mysqldump [选项] database [表名]   
 #支持指定数据库和指定多表的备份，但数据库本身定义不备份

示例:

 mysqldump -uroot -pabc123 hellodb students    //指定表

案例实操:

 `备份`
 [root@7-3 ~]# mysqldump -uroot -p hellodb > /data/hellodb.sql    //重定向,不能把密码暴露在命令行
 Enter password: abc123
 `模拟删库`
 (root@localhost) [(none)]> drop database hellodb;
 `创建新库`
 (root@localhost) [(none)]> create database hellodb2;     //只能备份还原表,所以需要手动创建数据库
 `输入重定向`
 [root@7-3 data]# mysql -uroot -pabc123 hellodb2 < /data/hellodb.sql
 `免交互查看是否还原成功`
 [root@7-3 ~]# mysql -uroot -pabc123 -e "select * from hellodb2.students;"

4.3.2.2 备份数据库

 格式:
 mysqldump [选项] -B DB1 [DB2 DB3...]
 #支持指定数据库备份，包含数据库本身定义也会备份

案例实操:

 [root@7-3 ~]# mkdir /backup
 [root@7-3 ~]# mysqldump -uroot -pabc123 -B hellodb > /backup/hellodb.sql
 (root@localhost) [(none)]> drop database hellodb;
 [root@7-3 ~]# mysql -uroot -pabc123 < /backup/hellodb.sql    //恢复备份
 [root@7-3 ~]# mysql -uroot -pabc123 -e"show databases;"
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | hellodb            |    //已还原
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+

4.3.2.3 备份数据库和表

 格式:
 mysqldump [选项] -A [OPTIONS]       
 #备份所有数据库，包含数据库本身定义也会备份

备份：

• performance_schema 内存中的数据没必要备份
• information_schema 同上
• sys 同上
• mysql 存放了用户信息 需要备份

案例实操:

 [root@7-3 ~]# mysqldump -uroot -pabc123 -A > /backup/all.sql
 [root@7-3 ~]# rm -rf /var/lib/mysql
 [root@7-3 ~]# systemctl stop mysqld
 [root@7-3 ~]# systemctl start mysqld
 [root@7-3 ~]# grep password /var/log/mysqld.log
 [root@7-3 ~]# mysql -uroot -p'-(PLoDiR:8e('
 *****设置密码策略,并修改简单密码*****
 [root@7-3 ~]# mysql -uroot -pabc123 < /backup/all.sql    //还原

4.4 增量备份⭐

实验目的: 采用完全备份+二进制日志, 将数据还原到删库前的状态

 [root@localhost ~]# vim /etc/my.cnf
 [mysqld]
 log_bin=/data/mysql-bin      
 server-id = 1
 [root@localhost ~]# mkdir /data
 [root@localhost ~]# chown  mysql.mysql /data/ -R
 [root@localhost ~]# systemctl restart mysqld

 [root@localhost ~]# mysqldump -uroot -pabc123  -A  --master-data=2 > /data/all.sql
 ​
 [root@localhost ~]# mysqldump -uroot -pabc123  -A  --master-data=2 |gzip > /data/all.sql.gz    //备份加压缩

 [root@localhost ~]#mysql -uroot -pabc123 -e "insert hellodb.teachers  values(null,'test',20,'M')"
 [root@localhost ~]#mysql -uroot -pabc123 -e "insert hellodb.teachers  values(null,'test1',21,'M')"
 [root@localhost ~]#mysql -uroot -pabc123 -e "insert hellodb.teachers  values(null,'test2',20,'M')"
 [root@localhost ~]#mysql -uroot -pabc123 -e "select * from hellodb.teachers;"
 +-----+---------------+-----+--------+
 | TID | Name          | Age | Gender |
 +-----+---------------+-----+--------+
 |   1 | Song Jiang    |  45 | M      |
 |   2 | Zhang Sanfeng |  94 | M      |
 |   3 | Miejue Shitai |  77 | F      |
 |   4 | Lin Chaoying  |  93 | F      |
 |   6 | wxy           |  22 | M      |
 |   7 | test          |  20 | M      |
 |   8 | test1         |  21 | M      |
 |   9 | test2         |  22 | F      |
 +-----+---------------+-----+--------+
 ​
 ​
 [root@localhost ~]#vim /data/all.sql
 ................................................................
 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
 .......................................................................

 (root@localhost) [(none)]> drop database hellodb;

 [root@localhost data]# mysqlbinlog  --start-position=154 /data/mysql-bin.000001 > /data/binlog.sql     //生成新的二进制日志文件

 [root@localhost data]#systemctl start mysqld
 #如果启动不了 把 /var/lib/mysql/  文件夹中的文件全部删除

 [root@localhost data]# mysql  -uroot -pabc123 < /data/all.sql 

 [root@localhost data]# grep -in drop /data/binlog.sql
 94:drop database hellodb      //第94行存在drop删库的信息    如果存在多行, 删除时注意先删除大数
 [root@localhost data]# sed -i.bak '/^drop/d' /data/binlog.sql    //把以drop开头的行删掉

 mysql> source /data/binlog.sql     //source用来执行脚本命令

 set sql_1og_bin=0;    //0是关闭,1是开启,都是临时性的

4.5 生产环境实战 备份策略

InnoDB建议备份策略

 mysqldump -uroot -pabc123 -A -F -E -R --triggers --single-transaction --master-data=1  >${BACKUP}/fullbak_${BACKUP_TIME}.sql

脚本例子：

 #!/bin/bash
 TIME=`date +%F_%H-%M-%S`
 DIR=/backup
 DB=hellodb
 PASS=test
 [ -d $DIR ] || mkdir $DIR
 mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz

生产例子分表分库

 for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done
 ​
 ​
 crontab  -e
 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin 
 0 1 * * *   /bin/bash    /data/backup
 ​
 ​
 vim     backup.sh
 for  db  in   `mysql -uroot -pabc123 -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`
 do mysqldump -B $db | gzip > /backup/$db.sql.gz
 done
 ​
 ​
 ​
 mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -
 B $db | gzip > /backup/$db.sql.gz;done
 ​
 ​
 ​
 mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$' | sed -rn 's#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash