MySQL的锁
执行操作时施加的锁模式
读锁:又称共享锁,多个读操作可以同时施加,非阻塞
写锁:又称独占锁或排它锁,阻塞
根据锁粒度分类
表锁:table lock,锁定了整张表,开销小
行锁:row lock,锁定了需要的行,开销大
注:锁的粒度越小,开销越大,但并发性越好
根据锁的实现位置分类
MySQL锁:可以手动施加锁,又称显示锁(表级锁)
1
|
lock tables Tb_Name {
read
| write} [,Tb_Name2 Lock_Type2]…
|
存储引擎锁:自动进行的,又称隐式锁
行级锁:只锁定挑选出的部分行,是InnoDB存储引擎中支持的另外一种显示锁
1
2
|
select
… lock
in
share mode;
# 对挑选出的行施加读锁
select
…
for
update;
# 对挑选出的行施加写锁
|
MySQL的事务
事务就是一组原子性的查询语句
ACID测试:能满足ACID测试就表示其支持事务,或兼容事务
1
2
3
4
|
A:Atomicity
# 原子性
C:Consistency
# 一致性
I:Isolation
# 隔离性,一个事务的所有操作在提交之前对其它事务是不可见的
D:Durability
# 持久性,一旦事务得以提交,其所作的修改会永久有效
|
隔离级别
1
2
3
4
5
6
7
|
read
uncommited (读未提交)
# 容易产生脏读,不可重复读,幻读
# 脏读:能够读取未提交的事务内容
read
commited(读提交)
# 只能看到已提交事务的修改内容,但仍然存在不可重复读,幻读
# 不可重复读:在同一次事务中,多次同一读取操作结果不一致,因为读取了其他事务的操作结果
repeatable
read
(可重读)
# 容易产生幻读
# 幻读:对于自己未修改的内容,事务提交前后出现的结果不一致
serializable(可串行化)
# 强制事务的串行执行,避免了幻读,但效率极低
|
查看MySQL的事务隔离级别
1
2
3
|
show global variables like ‘tx_isolation’;
select
@@global.tx_isolation;
# 建议:对事务要求不严格的场景下,隔离级别可以使用“读提交”
|
MySQL实现事务的原理:MVCC(多版本并发控制)
1
2
3
4
|
每个事务启动时,InnoDB会为每个启动的事务提供一个当下时刻的快照;
为了实现此功能,InnoDB会为每个表提供2个隐藏字段,一个用于保存行的创建时间,一个用于保存行的失效时间(里面存储的是系统版本号:system version number)
在某一事务中,使用比当前事务相等或更旧的版本号数据,从而保证其所读取的数据都是过去的数据
注:只在2个隔离级别下有效:
read
commited和repeatable
read
|
手动执行事务操作
1
2
3
4
5
6
7
8
9
|
start transaction;
# 手动开始执行事务操作
rollback;
# 在事务未提交前回滚,放弃全部修改
savepoint a;
# 定义当前位置为保存点a
rollback to a;
# 回滚至保存点a
commit;
# 提交事务
# 如果没有显示启动事务,每个语句都会当做一个独立事务,其执行完成后会被自动提交
select
@@global.autocommit;
# 查看当前自动提交选项
set
global autocommit = 0;
# 关闭自动提交功能
# 关闭自动提交后,请手动启动事务,并手动进行提交
|
事务的GTID
简介:Global Transaction ID,在MySQL5.6中被引入的,为了快速提升salve为master,同时保证事务安全的一种机制;使得其复制功能的配置、监控及管理变得更加易于实现,且更加健壮;
1
2
3
4
5
|
# 特性:
GTID=UUID(Server ID:128位)+事务号,可唯一标识事务
GTID在事务开始前写入二进制日志;
GTID和事务记录同时被传输至slave;
# 注:即使salve被提升为了master,对于复制到的事务也不会生成新的GTID的
|
配置使用GTID的简单主从模型
说明:使用MariaDB 10 版本作为MySQL 5.6的替代程序;区别是mariadb中默认开启了GTID功能,故不支持如下2个参数:gtid-mode,enforce-gtid-consistency
配置主从节点的服务配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
# 配置master节点:
[mysqld]
server-
id
=1
# 主从复制架构中server-id需唯一
port=3306
datadir=
/mydata/data
socket=
/tmp/mysql
.sock
log-bin=master-bin
# 启用二进制日志,这是保证复制功能的基本前提
binlog-
format
=ROW
# 二进制日志的格式,必须为row
log-slave-updates=
true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync
-master-info=1
slave-parallel-threads=2
binlog-checksum=CRC32
# 启用与复制有关的所有校验功能
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-host=master.lamp.com
# slave注册过程中向master报告的slave的主机名或IP地址,只用于master上执行“show slave hosts”以显示slave的主机名
report-port=3306
# slave注册过程中向master报告的slave使用的mysqld端口,只在slave使用非3306端口时需要
# 配置slave节点:
[mysqld]
server-
id
=11
port=3306
datadir=
/mydata/data
socket=
/tmp/mysql
.sock
log-bin=slave-bin
binlog-
format
=ROW
log-slave-updates=
true
# 告诉slave在二进制日志中记录从主库同步到的数据更新操作
master-info-repository=TABLE
# slave利用repository中的信息跟踪master的二进制日志和本地的中继日志,在此设置为table;可提高崩溃时从服务器的数据安全
relay-log-info-repository=TABLE
sync
-master-info=1
# 对于master-info的信息每写1次就刷新至磁盘,可确保slave无信息丢失
slave-parallel-threads=2
# 设定slave的SQL线程数;0表示关闭多线程复制功能
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
# 启用可在二进制日志中记录事件相关信息的功能,可降低故障排除的复杂度
report-host=slave.lamp.com
report-port=3306
|
主服务器上创建复制用户
1
|
mysql> grant replication slave on *.* to repluser@
'172.16.25.%'
identified by
'replpass'
;
|
为备节点提供初始数据集
1
2
3
4
5
6
|
# 锁定主表,备份主节点上的数据,将其导入slave节点;
# master节点上
musqldump --single-transaction --all-databases --flush-logs --master-
date
=2 > all.sql
# slave节点上
mysql < all.sql
# 如果没有启用GTID,在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置,以便后面启动slave节点时使用;
|
启动从节点的复制线程
1
2
3
|
# 如果启用了GTID功能,则使用如下命令:
mysql> change master to master_host=
'master.lamp.com'
,master_user=
'repluser'
,master_password=
'replpass'
,master_user_gtid=current_pos;
mysql> start slave;
|
验证查看
在master和slave上分别执行“show master status;”,查看Executed_Gtid_Set项是否一致即可
本文转自 xxrenzhe11 51CTO博客,原文链接:http://blog.51cto.com/xxrenzhe/1399149,如需转载请自行联系原作者