MySQL 进阶03

-----转自 【王者-YCZ】的笔记

五、存储引擎

1.种类

show engines；			查看所有存储引擎

自带的存储引擎：
  CSV                
  MRG_MYISAM         
  MyISAM             
  BLACKHOLE          
  PERFORMANCE_SCHEMA 
  MEMORY             
  ARCHIVE            
  InnoDB             
  FEDERATED   

生产中第三方的存储引擎：
  MariaDB :  默认是InnoDB
  percona :  默认是XtraDB
  还有：TokuDB,MyRocks,RocksDB ： 这三种存储引擎的共同点:压缩比较高,数据插入性能极高。

2.innodb引擎的优点

1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL) 

3.事务（ACID）

Atomic（原子性）：所有语句作为一个单元全部成功执行或全部取消，不能出现中间状态。
Consistent（一致性）：如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
Isolated（隔离性）：事务之间不相互影响。
Durable（持久性）：事务成功完成后，所做的所有更改都会准确地记录在数据库中，所做的更改不会丢失。

4.事务的生命周期

1.标准的事务控制语句：
  begin：开启事务
  commit：提交事务
  rollback：回滚事务
  
2.自动提交机制：（select @@autocommit；可以查看自动提交机制的开启与否）
	关闭自动提交机制：
		vim /etc/my.cnf
    autocommit = 0
    
3.隐式提交：
  隐式提交的非事务语句：
    DDL语句： （ALTER、CREATE 和 DROP）
    DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
    锁定语句： （LOCK TABLES 和 UNLOCK TABLES）
  
  隐式回滚：
  	会话窗口关闭
  	关数据库
  	死锁

5.隔离级别

1.RU(Read UnCommitted) : 读未提交
解释：最低的隔离级别，一个事务可以读取另一个事务并未提交的更新结果。
存在问题：会出现脏读、不可重复读、幻读。

2.RC(Read Committed) : 读已提交。
解释：一个事务的更新操作结果只有在该事务提交之后，另一个事务才可以读取到同一笔数据更新后的结果。
存在问题：防止了脏读，会出现不可重复度、幻读。

3.RR(Repeatable Read) : 可重复读。
解释：mysql的默认隔离级别，整个事务过程中，对同一笔数据的读取结果是相同的，不管其他事务是否在对该笔数据进行更新，也不管更新提交与否。
存在问题：防止了脏读、不可重复读，会出现幻读。
			
4.SR(Serializable) : 可串行化。
解释：最高隔离级别，所有事务操作依次顺序执行。
存在问题：防止了脏读、不可重复读、幻读，但是性能较差。


(1).脏读：事务A在修改操作且并未提交，事务B读了事务A修改后的内容，事务A回滚了，此时，事务B读到的就是脏数据。
(2).不可重复读：事务A在先后两次读取数据后，发现数据不一样，因为事务B在事务A的第一次读取和第二次读取之间修改并且提交了数据，此时，就是不可重复读。
(3).幻读：事务A修改数据，并且未提交，事务B也修改了数据，且已提交，事务A修改完数据提交后，读取数据，发现数据和自己修改后的该有的数据不一样，此时，事务A以为自己的数据没有修改成功，这就是幻读。



a.select @@tx_isolation;	查看当前隔离级别，默认为RR模式。
b.修改隔离级别：
	vim /etc/my.cnf
	transaction_isolation   隔离级别参数

6.mysql如何防止不可重复读（RR模式下）

mysql防止不可重复读是因为MVCC（Multi-Version Concurrency Control多版本并发控制）：
	每次打开一个事务窗口，都会生成一个一致性的快照（undo）；
	有了这个快照，在该事务中的每次查询，都是快照读取数据；
	即使其他事务修改了数据，该事务中读取的还是原来的数据。

7.mysql如何防止幻读（RR模式下）

通过GAP Lock 和 Next Lock来防止幻读

六、日志和备份恢复

1.日志

log_error：错误日志（排错使用）
log_bin：二进制日志（备份恢复及主从复制使用）
slowlog：慢日志（记录mysql工作中执行较慢的SQL语句）

2.备份恢复

1.备份导出：
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=128M >/backup/full_$(date +%F).sql

参数解析：
	-A												全备份
	--master-data=2						innodb引擎独有，记录二进制日志位置点
	--single-transaction			innodb引擎独有，开启热备功能
	-R												导出存储过程以及自定义函数
  -E 												导出事件
  --triggers								导出触发器
	--max_allowed_packet=128M	最大数据包大小
	
2.恢复导入：
source /backup/xxx.sql;
或者
mysql -uroot -p <  /backup/xxx.sql 

七、mysql主从

1.搭建

1.准备两个mysql服务器（假设：主库3306、从库3307）

2.主库3306开启binlog
	server_id = 10
	log_bin = /data/mysql-bin
	
3.从库3307设置server_id
	server_id = 20

4.备份主库3306数据
  mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=128M >/backup/full_$(date +%F).sql

5.将备份数据恢复到从库3307
  source /backup/xxx.sql;
  或者
  mysql -uroot -p <  /backup/xxx.sql 

6.配置自动同步，在主库3306中授权用户
	grant replication slave on *.* to repl@'%' identified by '123';

7.从库3307开启自动同步
	(1)执行命令：help change master to	查看帮助信息
	(2)在帮助信息中找到以下命令，并修改配置：
    CHANGE MASTER TO
    MASTER_HOST='192.168.195.129',
    MASTER_USER='repl',
    MASTER_PASSWORD='123',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;
	(3)其中同步位置点MASTER_LOG_FILE和MASTER_LOG_POS参数需要在数据库备份文件中获取
		-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
		了解：同步位置点事在备份导出数据时--master-data=2产生的结果。
	(4)修改完配置后执行命令
	(5)start slave；	开启自动同步
	(6)show slave status\G	查看主从配置成功与否

2.原理(简单版本)

主从复制步骤解析:
1. 主数据库写入数据之后， 会有data changes(数据变化)记录
2. 有变化记录之后，将增删改的一些sql语句记录到本地的BinLog(二进制日志)中
3. 从库会一直开启着一个io线程
4. 通过线程去读取主库这个二进制日志的内容
5. 从库会将数据写入到自己的Relay log(中继日志)中
6. 从库会将中继日志中的操作转化为SQL thread(SQL语句)
7. 通过转化的SQL语句写入到自己的数据库, 两边的数据就一致了

3.原理(复杂版本)

主从复制中涉及到的文件和线程如图所示（框框->文件 圈圈->线程）：

主库：
	DUMP THREAD
	mysql-bin.000001		二进制日志
从库：
	IO THREAD
	SQL THREAD
	relay-log.00000x		中继日志
	master.info					主库信息记录日志
	relay-log.info			中继应用情况信息记录日志

步骤原理解析：

1.主从配置从库执行change master to命令时，
ip、port、user、password、binlog、position写入到master.info进行记录；

2.主从配置从库执行start slave命令时，从库会启动IO线程和SQL线程；

3.IO线程读取master.info信息，获取主库信息连接主库；

4.主库会生成一个DUMP线程，来响应从库；

5.IO线程根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志；

6.DUMP线程检查主库的binlog日志，如果有新的内容，传送给从库的IO线程；

7.IO线程将收到的日志内容存储到了TCP/IP缓存中，立即返回ACK给主库(ACK是确认字符的意思，确认接收无误)，
此时，主库工作完成；

8.从库中，IO线程将TCP/IP缓存中的数据，存储到relay-log中继日志文件中,并更新master.info文件中的binlog文件名和postion号。此时，IO线程工作完成；

9.SQL线程读取relay-log.info文件，获取到上次执行到的relay-log中继日志的位置，作为起点，回放relay-log，同步主库中的数据；

10.SQL线程回放完成之后，会更新relay-log.info文件中的信息，表明中继日志已经应用完成。

注意点：
1.主库一旦有新的日志生成，二进制日志会发送“信号”给dump线程，dump线程再通知从库的IO线程来请求同步数据。
2.relay-log中继日志会有自动清理的功能。

八、数据库架构

1.最开始的主从

解析：app应用连接主库，所有的读和写都在主库，从库只是作备用。
存在问题：主库压力太大。

2.读写分离架构的主从

解析：app应用请求发给中间件，中间件(诸如ProxySQL、Maxscale)做读写分离和负载均衡处理，再将请求转发给主库和从库，此时，主库只做写的操作，从库只做读的操作。
适用范围：中小型企业
注意：该架构可以继续升级为多个app引用，和多个主从数据库。
存在问题：只有一主一从，主库挂了，需要人为将从库切换为主库。

3.高可用架构

解析：利用MHA实现高可用，当主库宕机的时候，MHA会在15~60秒之间自动选择一个从库来当新的主库，全年故障率可以达到99.9%到99.99%的标准。(除了MHA还有pxc也可以实现高可用)
适用范围：电商网站，中大型企业。

4.分布式架构

解析：底层为多个高可用主从，app请求通过中间件TDDL做分布式架构处理。
适用范围：巨型企业，淘宝。