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也会受到影响
连接数过多,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
表(三范式)和字段(保小不保大)
三范式(单一属性 主键依赖 没有传递依赖)的问题是需要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 用来建立新的slavemysqldumper + 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