如何实现mysql主从读写分离
1.通过程序实现读写分离(性能，效率最佳，推荐)
php和java都可以通过设置多个连接文件轻松的实现对数据库的读写分离，即当select时，就去
连接读库的连接文件，当update,insert,delete时就连接写库的连接文件。
2.通过软件实现读写分离
Mysql-proxy,Amoeba等代理软件也可以实现读写分离功能，但最常用好用的还是程序实现读写分离
3.开发dbproxy
5.1 flush tables with read lock;
5.5 flush table with read lock;
mysql主从同步原理要点:
1.异步方式同步
2.逻辑同步模式，可采取多种模式进行同步，默认是通过SQL语句执行
3.主库通过记录binlog实现对从库的同步
4.主库一个线程，从库2个线程完成
5.从库关键文件master.info,relay-bin,relay-log
6.从库还想级联从库，要打开log_bin和log-slave-update参数
binlog的三种模式
1.statement Level模式
每一条修改数据的sql语句都会记录到bin-log日志中，slave复制时执行相同的sql语句进行复制
优点:减少bin-log日志量，节约IO，提高性能，延迟小。
缺点:它记录的事执行语句，为了让这些语句在slave端正确执行，必须记录每条语句的一些上下文信息;在某些
情况下，会导致函数和触发器同步出线问题
2.Row Level模式:日志中会以修改每一行数据的形式记录，然后再slave端再对相同的数据进行修改
优点:日志内容非常清楚地记录下每一行数据修改的细节，不会出现某些特定情况下存储过程，函数，以及
trigger的调用和触发无法被正确复制的问题。
缺点:记录的日志内容非常多，日志量非常大
3.Mixed模式
它是前两种模式的结合，在Mixed模式下，Mysql会根据执行的每一条具体sql语句区分对待记录的日志形式 解析row格式的binlog日志:mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000007 事务:事务就是指逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句要么全成功要么全失败
事务的四大特性
1.原子性(Atomicity)
事务是一个不可分割的单位，事务中的所有SQL操作要么全发生，要么全都不发生
2.一致性(Consistency)
事务发生前和发生后，数据的完整性必须保持一致
3.隔离性(Isolation)
当并发访问数据库时，一个正在执行的事务对于其他的对话是不可见的，多个并发事务之间的数据时相互隔离的
4.持久性(Durability)
一个事务一旦被提交，它对数据库的数据改变时永久性的。如果出了错误，事务也不允许撤销，只能通过"补偿性事务" mysql数据库默认是开启事务的:
start transaction 开启事务 mysql5.1
begin; 开启事务 mysql5.5
rollback 回滚事务
commit 提交事务 MyISAM引擎特点
1.不支持事务
2.表级别锁定，打打降低了并发性能
3.读写互相堵塞:不仅会在写入的时候堵塞读取，MyISAM会在读取的时候堵塞写入
4.只会缓存索引:MyISAM可以通过key_buffer_size缓存索引，打打提高访问性能，但不能缓存数据
5.读取速度较快，占用资源相对少
6.不支持外键约束，相对粗颗粒 MyISAM生产场景:
1.不需要事务支持的业务
2.一般是读数据比较多的应用，读写都频繁的场景不适合
3.读写并发访问相对较低的业务
4.数据修改相对较少的业务(堵塞问题)
5.中小型的网站部分业务会用 MyISAM引擎调优:
1.设置合适的索引
2.调整读写优先级
3.启用延迟插入改善大批量写入性能(降低写入频率，尽可能多条数据一次性写入)
4.尽量顺序操作让insert数据都写入到尾部，减少堵塞
5.分解大的时间长的操作，降低单个操作的堵塞时间
6.降低并发数，某些高并发场景通过应用进行排队队列机制
7.充分利用缓存
InnoDB引擎特点:
1. 支持事务:支持4个事务隔离级别
2.行级锁定:通过索引实现，全表扫描仍然会是表锁
3.读写堵塞与事务隔离级别相关
4.具有高效的缓存特性:缓存索引和数据
5.整个表和主键以cluster方式存储，组成一颗平衡树
6.所有Secondary Index都会保存主键信息
7.支持分区，表空间，类似Oracle数据库
8.支持外键约束 InnoDB引擎适用的生产业务场景
1.需要事务支持的业务
2.行级锁对高并发有很好的适应能力，但需要确保查询是通过索引完成
3.读写数据及更新数据较为频繁的场景，如BBS,SNS,微博，微信等
4.数据一致性要求较高的业务,如充值转账等金融业务 InnoDB引擎调优
1.主键尽可能小
2.避免全表扫描，因为会使用表锁
3.尽可能缓存所有索引和数据，提高响应速度，减少磁盘IO消耗
4.在大批量小插入的时候，尽量自己控制事务而不要使用自动提交
5.合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。
6.避免主键更新，这会带来大量的数据移动 数据库慢的排查方法:
1.show full processlist; #找出导致慢的SQL语句
2.开启慢查询日志
long_query_time = 1
log-slow-queries = /data/3306/slow.log
找到慢查询语句，创建联合索引解决问题 查看负载
[root@A1-lamp ~]# uptime
16:29:35 up 13:46, 4 users, load average: 0.00, 0.19, 0.34 数据库中像 LIKE '%杜冷丁%' 这样的语句特别多，导致数据库负载特别高，
%杜冷丁%这样的双%%的SQL语句创建索引是不起作用的，因此对于数据库本身优化
是不行的
优化方案思路:
1.从业务上实现用户登录后再搜索，这样减少搜索次数，从而减轻数据库服务的压力
2.如果有大量频繁的搜索，一般是由爬虫在爬你的网站，分析WEB日志封掉IP(AWSTATS)
3.配置多个主从同步，程序上实现读写分离(最好让LIKE‘%杜冷丁%’这样的查询去从库查，减轻主库读写压力)
4.在数据库前端加上memcached缓存服务器
5.LIKE'%杜冷丁%'的语句，可以通过搜索服务Sphinx实现搜索
索引的原理:索引时建立了针对数据内容的排序结果的指针。就像一本书的目录
索引的设计原则:
1.索引列一般为where子句中的列或者连接子句中的列
2.尽量不对重复值多的列做索引，如性别
3.尽可能使用短索引:尽量对字符列索引尽量指定最小长度
create index cityname on city(city(5));
4.符合索引前缀特性，索引的顺序很重要
key(a,b) ... where b=5 不会用索引
key(a,b,c)
可以做索引的组合:key(a),key(a,b),key(a,b,c)
下列组合无法走索引:key(b),key(b,c),key(a,c),key(b),key(c)
创建复合索引应将最常用做限制条件列放在最左边，依次递减
5.避免出现无用索引(很少使用或者不用)
6.InnoDB:尽量指定主键，最常用较短数据类型唯一列做主键
7.尽量使用定长字符类型做索引,如char,不用varchar
索引是一把双刃剑，避免过度使用索引:
1.索引的建立对提高检索能力很有用，但是数据库维护它很费资源
2.对性别列索引
只有两个值，建索引不仅没优势，还会影响插入、更新速度
3.索引会占用磁盘空间，降低写操作。执行计划要考虑各个索引
4.索引不是越多越好
5.行数比较少的表可以不建立索引(100行以内)
创建索引的方法
1.create index index_name on table_name(列名);
2.alter table table_name add index index_name(列名); 删除索引:
drop index index_name on table_name;
mysql索引优化步骤
1.查看负载很高uptime
2.找出慢查询语句
show full processlist;
3.看看查询语句的执行过程，是否有索引，
explain 慢查询语句 4.在查询列创建索引
create index index_name on table_name(列名); 5.查看优化后语句的执行效率
explain 慢查询语句 创建索引很消耗性能，因此不要在业务高峰期建立索引
利用explain查看sql语句的执行计划
防止误操作:alias mysql='mysql -U'
长期可追加到/etc/profile
mysqldump -uroot -phaha123 -d oldboy > oldboy.sql #备份表结构 -t 备份表数据
-A 备份所有数据库
备份所有数据库mysqldump -uroot -phaha123 -A -B --events|gzip >/opt/all.sql
-F 切割二进制日志
mysqldump -uroot -phaha123 -A -B -F --events |gzip > /opt/all.sql mysqldump 的关键参数说明
1.-B 指定多个库，增加建库语句和切换库语句
2.--compact 去掉注释，适合调试输出，生产不用
3.-A 备注所有库
4.-F 刷新binlog日志
5.--master-data 增加binglog日志文件名及对应的位置点
6.-x,--lock-all-tables
7.-l --lock-tables 只读锁表
8.-d 只备份表结构
9.-t 只备份数据
10.--single-transaction 适合innodb事务数据库备份
for myisam：
mysqldump --user=root --all-databases --flush-privileges --lock-all-tables \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql
for innodb：
mysqldump --user=root --all-databases --flush-privileges --single-transaction \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql myisam：mysqldump -uroot -phaha123 -A -B --master-data=1 -x |gzip > /opt/all.sql.gz
innodb mysqldump -uroot -phaha123 -A -B --master-data=1 --single-transaction|gzip > /opt/all.sql system ls /root
命令前加system 可以执行shell命令
查看数据库参数和状态:
show processlist; #查看正在执行的完整的SQL语句，**查看慢查询语句的命令 show variables like ''; #查看数据库的参数信息 show global status; #查看整个数据库运行状态信息，很重要，要分析并做好监控 set global key_buffer_size = 32777218; 直接设置数据库参数
show status #查看当前会话的数据库状态信息
mysqlbinlog 解析mysqlbinlog日志
bin_log 日志就是记录mysql数据库增删改的操作 mysqlbinlog -d 数据库名 mysqlbinlog >/opt/test.sql
指定起始和结束点:mysqlbinlog mysqlbinlog-0001 --start-position=start_num --end-position=end_num -r test.sql
指定开始和结束时间：mysqlbinlog --start-datetime='2017-12-11 16:67:00' --stop-datetime='2017-12-11 23:00:00' -r test.sql
如对本文有疑问， 点击进行留言回复！！
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
我要评论
网友评论