常用数据库优化方案(四)
主从服务器
1. 将读操作和写操作分离到不同的数据库上，避免主服务器出现性能瓶颈；
2. 主服务器进行写操作时，不影响查询应用服务器的查询性能，降低阻塞，提高并发；
3. 数据拥有多个容灾副本，提高数据安全性，同时当主服务器故障时，可立即切换到其他服务器，提高系统可用性；
读写分离的基本原理就是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE）操作，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到其他从数据库。以SQL为例，主库负责写数据、读数据。读库仅负责读数据。每次有写库操作，同步更新到读库。写库就一个，读库可以有多个，采用日志同步的方式实现主库和多个读库的数据同步。
1、主从数据库配置问题
在常见的网络项目（使用并不局限于web项目）中所有数据库操作中读写比例大概在8:2左右。文件IO的读写操作都会降低相关系统的响应速度等性能。所以才大型项目（大量批量用户访问、大量的数据读写操作）中将数据库读写操作分离对于系统性能的提升而言具有重要的作用，是大型项目优化的有效解决方案。
2、主从数据库配置原理&技术难点
原理：将数据的读写操作分开到不同的数据库中，但是不同的数据库中存储的数据被同步或者是异步复制成很多份
Replication原理
Mysql 的 Replication 是一个异步的复制过程，从一个MySQL节点（称之为Master）复制到另一个MySQL节点（称之Slave）。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程（SQL 线程和 I/O 线程）在 Slave 端，另外一个线程（I/O 线程）在 Master 端。
要实现 MySQL 的 Replication ，首先必须打开 Master 端的 Binary Log，因为整个复制过程实际上就是 Slave 从 Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。
看上去MySQL的Replication原理非常简单，总结一下：
* 每个从仅可以设置一个主。
* 主在执行sql之后，记录二进制log文件（bin-log）。
* 从连接主，并从主获取binlog，存于本地relay-log，并从上次记住的位置起执行sql，一旦遇到错误则停止同步。
从这几条Replication原理来看，可以有这些推论：
* 主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间，主从数据不一致。
* 如果主从的网络断开，从会在网络正常后，批量同步。
* 如果对从进行修改数据，那么很可能从在执行主的bin-log时出现错误而停止同步，这个是很危险的操作。所以一般情况下，非常小心的修改从上的数据。
* 一个衍生的配置是双主，互为主从配置，只要双方的修改不冲突，可以工作良好。
* 如果需要多主的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点。
主从设置
因为原理比较简单，所以Replication从MySQL 3就支持，并在所有平台下可以工作，多个MySQL节点甚至可以不同平台，不同版本，不同局域网。做Replication配置包括用户和my.ini（linux下为my.cnf）两处设置。
技术难点：1、数据库之间的同步
2、数据读写之间的加锁解锁
3、主从数据库配置步骤
1、首先在主MySQL节点上，为slave创建一个用户
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'192.168.1.10' IDENTIFIED BY 'slave';
实际上，为支持主从动态同步，或者手动切换，一般都是在所有主从节点上创建好这个用户
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '用户'@'IP地址' IDENTIFIED BY '密码';
2、然后就是MySQL本身的配置了，这需要修改my.cnf或者my.ini文件
在mysqld这一节下面增加：
server-id=1
auto-increment-increment=2
auto-increment-offset=1
log-bin
binlog-do-db=mstest
binlog_format=mixed
master-host=192.168.1.62
master-user=slave
master-password=slave
replicate-do-db=mstest
上面这两段设置，前一段是为主而设置，后一段是为从设置的。也就是说在两个MySQL节点上，各加一段就好。binlog-do-db和 replicate-do-db就是设置相应的需要做同步的数据库了（binlog-do-db是需要同步的数据库；binlog-ignore-db是忽略的数据库），auto-increment-increment和auto- increment-offset是为了支持双主而设置的（参考下一节），在只做主从的时候，也可以不设置
3、重启mysql服务，查看主机配置：
show master status;
显示：
File Position
mysql-bin.000002 5442170
4、从机配置
①在配置文件里C:\ProgramData\MySQL Server 5.7\my.ini
在最后添加：
#Slave Config
server-id=2
log-bin=mysql-bin
replicate-do-db=iot
②在mysql命令行里进行其他关联配置，否则导致从机的mysql服务不能启动，与数据库版本先关：
change master to master_host='49.123.105.71', master_user='root', master_password='123456', master_log_file='mysql-bin.000002',master_log_pos=5442170;
（如果两个数据库共用一个IP地址的话请务必加上端口地址 EG：master_port='3308'）
（配置值的对应关系：
master_host：主机地址
master_port: 主机端口
master_user：主机赋权限的用户名
master_password：主机赋权限时设置的密码
master_log_file：show master status之后的显示的File
master_log_pos：show master status之后的显示的Position）
③重启mysql服务，查看从机配置：
show slave status;
显示Slave_IO_Running:Yes
Slave_SQL_Running:Yes
则配置完全，正在同步。。
（如果显示不能对slave进行修改时，需要先停止slave：stop slave，完成修改之后再启动slave： start slave）
数据库的同步不在完成如上配置之后启动，一直不间断。。。
4、主从数据库配置知识点
从原理论来看MySQL也支持双主的设置，即两个MySQL节点互为主备，不过虽然理论上，双主只要数据不冲突就可以工作的很好，但实际情况中还 是很容发生数据冲突的，比如在同步完成之前，双方都修改同一条记录。因此在实际中，最好不要让两边同时修改。即逻辑上仍按照主从的方式工作。但双主的设置 仍然是有意义的，因为这样做之后，切换主备会变的很简单。因为在出现故障后，如果之前配置了双主，则直接切换主备会很容易。
双主在设置时，只需将上面的一段设置复制一份，分别写入两个MySQL节点的配置文件，但要修改相应的server-id，auto- increment-offset和master-host。auto-increment-offset就是为了让双主同时在一张表中进行添加操作时不 会出现id冲突，所以在两个节点上auto-increment-offset设置为不同的值就好。 另：不要忘了，在两个节点上都为对方创建用户。应用层的负载均衡 本文只介绍了MySQL自身的Repilication配置，在上面的图中也可以看出，有了Replication，还需要应用层（或者中间件）做一个负载均衡，这样才能最大程度发挥MySQL Replication的优势，这些将在以后探讨。
5、主从数据库配置扩展
详情请查阅常用数据库优化方案（五） - 双主数据库 参考资料
{MySQL 5.7 Replication 相关新功能说明}
6、主从数据库配置参考文献
windows下两个mysql5.7的主从配置实现数据库同步
2018-03-30 上午 后记
两星期前开始玩MySQL主从数据库期间装载虚拟机中的mysql成功，但是想要在win7中通过解压缩安装安装两个mysql问题重重。具体方式可能没有共性，但是大家可以根据文件夹目录中的data/xxx.err 错误日志文件来进行查看安装时有什么保存。可以具体分析错误的发生和百度相应的解决方案。自己一个人摸索是很难的，善于使用百度之类的搜索引擎是很重要的。其次是如果你有什么好的方案（玄学方案也行）还是希望可以分享出来大家一起进步。
阿义不在家 :痛苦预示着超脱
本文来自博客园，作者：阿义不在家，转载请注明原文链接：https://www.cnblogs.com/supperlhg/articles/8583669.html
