mysql5.5

mysql review:

1.性能：并发性+异步IO ==> 数据读写能力

充分利用cpu多核处理能力，提高了合并插入数量，改善了磁盘IO，同时支持多个bufferpool实例，提高线程并发数，事务提交采用组的方式，并发处理事务能力大幅提升，
增加了自适应刷新，缓冲池中的数据存活更久，数据恢复时间加快，可选择内存分配程序，预读算法只支持线性顺序的，

使用异步IO，清除数据和缓冲的能力增强，加入自旋锁，更快的curd索引操作，支持创建压缩数据页，
可关闭自适应哈希，可关闭元数据更新功能


2.binlog+change master+ignore + MMM　　

binlog设置：binlog_row_image=minimal
+性能 事务执行写日志innodb_log_buffer，事务提交（innodb_flush_log_at_trx_commit）写磁盘,也就是刷入binlog,然后是redo log（innodb_support_xa=1）
  隔离（脏读（提交后读到了提交前的） 不可重复读（没提交就读到了提交后的） 可重复读(提交后读到了提交后的)）

数据一致性:slave中继日志relay-log损坏， stop slave dump —> change master to —>  skip+source  start slave | 5.6增加 relay_log_recovery=1 就行

数据库的忽略:binlog_ignore_db 主库忽略，需要用use database，不然不写log | replicate-ignore-db  从库忽略，直接写log

主从设计：主低从高（字符集）

Replication+HA:扩展性好，是因为主从的从库能提供业务

keepalive （一主一从）
MMM        （一主多从）
Cobar       （多主多从） 主键取模，大表拆分小表，对开发人员来说透明

heartbeat+drbd：扩展性不好，一台宕机后另一台才能接管，由于使用共享存储，不存在主从延迟，安全上最少数据丢失，
RHCS

mysql5.5：特性  故障调试  性能调优  备库安全  集群架构   监控工具   库表操作

1.特性

性能：引擎  并发性 数据读写能力
默认innodb引擎，mysql5.5的效率是mysql5.1的3倍

充分利用cpu多核处理能力，提高了合并插入数量，改善了磁盘IO，同时支持多个bufferpool实例，提高线程并发数，事务提交采用组的方式，并发处理事务能力大幅提升，
增加了自适应刷新，缓冲池中的数据存活更久，数据恢复时间加快，可选择内存分配程序，预读算法只支持线性顺序的，使用异步骤IO，清除数据和缓冲的能力增强，加入自旋锁，更快的curd索引操作，支持创建压缩数据页，
可关闭自适应哈希，可关闭元数据更新功能


安全：主从的同步和异步和切换
innodb严格自检，
动态修改独立表空间和锁超时时间
半同步复制：通知主机，rpl_semi_sync_slave_status ON 半同步复制开启
relay-log可自我修复，
如果超时未接收，那么就转成异步，但是主从性能会降低，而且如果网络波动，异步和同步反复切换，主库的curd也会受到影响 




2.故障：磁盘I/O ==>  加内存条（连接数 独立表空间 热数据 大查询  内存swap交互）


连接数过多，curd比较频繁，或是sql大查询，wait_timeout设的过长，

独立表空间回收磁盘和数据空间，共享表空间只回收数据空间
数据库重启后，如何将热数据快速写入内存中去，innodb_buffer_pool_load or  dump
磁盘IO过于频繁，直接加内存条是就行了
5.5中子查询要改成连接查询才会提升效率，5.6中子查询自动改进了，比5.5快了10倍
监控mysql内存，超过90%重启mysql释放内存，同时增加swap分区，避免内存耗尽时机器死机


二进制：恢复数据  + 主从复制
方法：找到错误binlog（binlog_format=row ）,修改为sql语句，然后source导入


binlog_format=row 才是比较严谨的做法，不然的话，如果从库没数据就不会报错的,但是它会增加问题，5.6中启用了binlog_row_image=minimal，大大减少了记录量，也就是减少了IO频率
主从的时候，主从的调度器可能一个能用，一个不能用，这样主从切换的时候会有功能上的问题，需要手动修改为enable才行
update和delete不带条件的误操作，方法是，找到错误binlog,修改为sql语句，然后source导入


主从问题：数据一致性（主从的检测和修复 忽略  跳过  版本 | slave 清除、恢复和修复日志|master的curd）
master删除，slave上没有，可直接跳过
主键重复，slave上有了，主键又插入了一条同样的，需要删除slave上重复的主键，
master上更新，slave上没有，需要在slave上更新
slave中继日志relay-log损坏， stop slave  —> change master to —>    start slave | 5.6增加 relay_log_recovery=1 就行
serverid相同，需要手动改为不同的
避免master上执行大事务，改写成存储过程
同步复制错误，slave_exec_mode 与 slave_skip_errors相同，只不过不用写进my.cnf，可动态使用了
主从一致性，mk-table-checksum 检测表结构一致性    mk-table-sync修复主从不一致 mk-checksum-filter 过滤出不相等的表
数据库的忽略
binlog_ignore_db 主库忽略，需要用use database，不然不写log
replicate-ignore-db  从库忽略，直接写log
mysql低版本向高版本同步复制会出问题，而主低从高就不会出问题，尤其是字符集
恢复slave   stop slave  mysqldump 主库    从库changemasterto主库   skip问题sql   source从库   start slave
清除 slave   reset slave只是把master.info 和 relay-log.info删了，但同步信息还有， reset slave all 就 ok





3.性能
表（三范式）和字段（保小不保大）


三范式(单一属性  主键依赖  没有传递依赖)的问题是需要join多张表，适度冗余来进行改善
字段类型，保小不保大
int（手机号 bigint    IP unsigned  年龄 tinyint）
varchar (latin1 1   gbk 2  utf8 3)
timestamp datetime在5.6中趋同，5.5不支持多字段timestamp，5.6是支持的
altertable之后的curd不锁表，但是之前的curd会锁表，5.5之前和之后都会锁表
5.5增删索引不锁表，但是修改字段还是会锁表；5.6增加字段时,不会锁表
pt-online-change 在线修改表结构 ，确保curd不丢失




存储引擎：mysiam(读写锁) + innodb(行级锁和事务（隔离级别）)


mysiam读锁，能读不能写；写锁，读写都不能的
innodb，支持行锁和事务，原理是把数据捞到内存中使用，内存数据库是趋势
innodb，只有通过索引条件检索数据，才会使用行级锁，其他的使用表锁；行锁有死锁，会释放资源，让一方事务完成，另一方再完成；表锁没有死锁
innodb的事务实现是写数据之前先写日志，执行事务时，会往innodb_log_buffer日志缓冲区插入日志，提交时，插入磁盘，这个是由innodb_flush_log_at_trx_commit（0 1次 2 1次  1 10次）
事务提交后，首先刷新到binlog，然后刷到 redo log里，，如果刷新到binlog里后宕机， redo log里就没记录，从而回滚，但二进制里已经有了就不能回滚，主从就会出现不一致，innodb_support_xa=1保证了binlog 和 redo log的一致性，但是会影响性能


事务隔离级别
                    读数据一致性       脏读               不可重复读          幻读     
未提交读      不读取坏数据      是                     是                         是
已提交读      语句级                 否                     是                         是
可重复读      事务级                 否                     否                         是
可序列化      事务级                 否                     否                         否


session2提交了，session1还能读到session2修改前的数据，
如果session1提交后，才能读到session2修改后的数据，这是可重复读，
如果session1没提交就读到了session2修改后的数据，那么这就是不可重复读


间隙锁用来防止幻读，用于可重复读，限制对某个区间的数据进行操作，对于没有的数据，禁止操作；
其他三和都加  第一个加排他写锁 和 第二三个加共享读锁，第三个加间隙锁




sql优化: 慢查询 sql  索引 my.cnf
开启慢查询和用mysqldumpslow取出耗时最长的10条语句来进行分析
mysqldumpslow  -s -t -t 10 slow.log
sql 优化: in  like  limit  count or  duplicate  order   group having   ...
索引优化：单列和多列索引  引号  数据量过大 
mysql5.6支持全文索引和update +索引，同时还支持 must-range read索引 和 合并索引 以及多个单列索引同时起作用


my.cnf  （每个连接内存  内存中数据块 ）
每个连接到mysql的用户进程分配内存:read sort join  +buffer size   max_connections
内存中缓存数据块：innodb_buffer_pool_size query_cache_size  key_buffer_size  indoor_log_buffer_size
查询缓存(innodb_page_size
mysql5.6 同步复制 不需要找binlog里的pos，同时支持多线程复制基于库（多个库多个请求才会有的）



4.备份和恢复：完全+增量  |  冷备份 热备份 逻辑备份 | 日志(全量+增量)+数据+权限

mysqldump —dump-slave 用来建立新的slave
mysqldumper + myloader效率更高的
全量或增量 的 恢复和备份，主要是 日志(全量+增量)+数据+权限 




5.高可用4架构：
1.性能：mysql5.5的宕机恢复速度比5.1快200多倍，

2.切换原则：
主宕机，切换到从，从提升为主，主修复后，不会切换，避免频繁切换
从宕机，切换到主或从，从修复后，切换为从
3.四中架构，分成两大类，主从（扩展性）
3.1 Replication+HA:扩展性好，是因为主从的从库能提供业务

keepalive （一主一从）
MMM        （一主多从）
Cobar       （多主多从） 主键取模，大表拆分小表，对开发人员来说透明
3.2
heartbeat+drbd
RHCS
相同：扩展性不好，一台宕机后另一台才能接管，
不同：RHCS 比 ReplicationHA来说，由于使用共享存储，不存在主从延迟，安全上最少数据丢失，


6.监控 zabbix（分布式） = cacti（性能监控） + nagios(服务监控)




7.库表操作

数据碎片：alter talbe 表名  engine = innodb
查看物理文件大小和数据文件大小，如果物理文件大于数据文件，就是碎片过多了，
数据库级联：主-->从-->备（库）
步骤如下：
1.备库指向主库，同步数据（主从同步检测）
2.同步完成时，备库数据替换给从库（替换后两份数据不开同步，是静止数数据），然后指向从库
3.从库指向新的主库，开始追数据（追完后，主库进行切换）
4.第二次，再利用bak重建slave ... ...


拆分表（数据迁移）：主拆分表，映射到从的拆分表，从的拆分表映射到不同机器上，然后应用直接读取不同的机器上的数据，就行了
主库数据按照规则，复制到不同的表内
主从停止同步
从库按照规则，把主库的数据导入进不同的表内
主从开启同步
从库中的分表数据，写入不同的机器，比如拆分成3张表，就写入3台机器里
最高告知开发，按照用户ID取模，拆分成几张表，拆分到哪台机器的ip