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略差一些, 但操作系统或停电 可能导致最后一秒的交易丢失
事务提交了,意味着操作存储在了磁盘上。
系统比程序稳定
级别 | 0 | 1 | 2 |
---|---|---|---|
安全性 | 较高 | 最高 | 最高 |
性能 | 最高 | 最差 | 较高 |
安全选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种格式
statement 语句
:基于语句记录,记录语句(增删改查等语句)。默认模式( MariaDB 10.2.3 版本以下 ),日志量较少row 行
:基于行记录,记录数据,日志量较大,更加安全,建议使用的格式是MySQL8.0默认格式。所有改动的数据都会被记录。mixed 混合模式
:系统自行判定该基于哪种方式进行,默认模式( MariaDB 10.2.4及版本以上),但尽量不要用mixed3.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 离线查看日志
二进制日志的客户端命令工具,支持离线查看二进制日志
选项 | 含义 |
---|---|
-v | 显示详细信息 |
--start-position | 指定开始的位置 |
--stop-position | 指定结束的位置 |
--start-datetime | 开始时间 |
--stop-datetime | 结束时间 |
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 选项 | 含义 |
---|---|
-A | 备份所有数据库, -A里包含-E和-R |
-B | 指定备份的数据库 |
-E | 备份相关的所有event scheduler |
-R | 备份所有存储过程和自定义函数 |
--master data ='0或1或2' |
从哪一个节点开始备份的,2是注释,1是开启,0是关闭。非常重要(此选项需启用二进制日志) |
-F | 生成新的二进制日志 |
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