MySQL深入03-锁-事务-GTID

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，如需转载请自行联系原作者