ubuntu14.04下MySQL主从数据库数据同步
本篇随笔主要记录和整理了这两天在ubuntu14.04系统中设置MySQL5.5主从数据库数据同步和性能测试的相关情况。
随笔主要分为三个部分：
1、MySQL数据库数据同步的相关知识（从官方文档和博客）；
2、主从数据库的相关配置；
3、不同机器上的性能测试情况；
1、MySQL数据库数据同步的相关知识（从官方文档和博客）
首先MySQL是可以通过设置配置文件来实现指定主从库，并且主服务器的数据能够同步复制到一个或多个从服务器数据库。
根据配置，可以同步复制数据库中的所有数据库，选定指定的数据库或者是选定指定的数据表。
默认情况下，复制是异步的，即从服务器和主服务器之间的网络不需要永久连接，网络断开重连后从数据库即可进行同步数据。MySQL是支持半同步复制的。
半同步复制和异步复制、同步复制的区别：
- 异步复制，主服务器将事件写入其二进制日志，而从服务器在准备就绪时请求它们。缺点是无法保证任何事件都能到达从服务器。
- 完全同步复制，当主服务器提交事务时，所有的从服务器也将在主服务器返回执行事务的会话之前提交事务。缺点是完成交易可能会有很多延迟。
- 半同步复制，主服务器在提交事务后等待，直到至少有一个从服务器接收并记录事件。主服务器不会等待所有的从服务器确认收到，并且它只需要接收，而不是在事件已经在从服务器完全执行和提交。
在数据复制的过程中，主服务器上的源表和从服务器上的复制表是否使用不同的引擎类型是无关紧要的。这提供了很多便利，主数据库和从数据库可根据不同的业务需求来设置不同的数据库引擎。
在主服务器和从服务器之间传输的二进制日志信息，是可以支持加密连接传输的。
MySQL数据同步复制的原理：MySQL中服务器之间的复制是基于二进制日志记录机制。主服务器中的数据更新和更改会作为“事件”写入二进制文件。根据数据库更改以不同的日志记录格式进行存储在二进制文件中。从数据库从主数据库读取二进制日志，并在从服务器上执行二进制日志中的事件。每个从服务器在读取主服务器二进制日志的时候都会保留最后读取二进制日志中的位置，因此可以实现将多个从服务器连接到主服务器并读取主服务器二进制日志的不同部分。
MySQL数据同步的原理图如下：分别为一对一（同步所有库）、一对一（同步指定库）（图片转载自：https://blog.csdn.net/q3dxdx/article/details/50962729）
MySQL同步复制的优点：
- 横向扩展解决方案--在多个从站之间分配负载以提高性能。数据写入和更新在主服务器上完成，读取在一个或多个设备上完成。这样可以提高写入的性能，同时也可显著提高数据读取的速度。
- 数据安全性，因为数据被复制到从站，并且从站可以承受主从网络连接暂时断开的风险，所以从站上运行备份服务。
- 分析性能，可在主数据库上创建实时数据，而信息分析放在从服务器上进行，从而不影响主服务器性能
- 远程数据分发，如果分支机构希望使用主数据副本，则可以通过复制机制来创建主数据的本地副本来提供使用，而无需永久访问主数据。
MySQL同步复制的风险：
- 因为MySQL复制是异步的。主站将时间写入其二进制日志中，但并不知道从站是否或合适检索或处理它们。因此在设置了主站和从站设备后，需要自己编写一个监视主服务器的应用程序或脚本来检查它是否已启动，并指示从服务器和应用程序在主站发生故障时更改主站。
- 从站在连接到主站之后，它不会检查主站上的数据库是否与从站上的数据库兼容。它只是从主站的二进制日志中的指定坐标开始读取和执行事件。
2、主从MySQL数据库相关配置
主服务器设置：必须的操作是设置唯一的服务器ID，并且启用二进制日志记录，然后重启MySQL数据库进程。
配置二进制日志和服务器ID，是在my.cnf或my.ini（my.ini好像是windows系统中MySQL的配置文件）文件中进行设定的。我的环境是ubuntu，是在my.cnf中进行配置的。在my.cnf中的[musqld]部分，如已有如下语句但被注释，则可取消注释并更改；如没有则可手动添加。修改完my.cnf文件后，使用命令：sudo service mysql restart来重启mysql数据库。
1 [mysqld] 2 #启用二进制日志并将文件名前缀设为mysql-bin 3 log-bin = mysql-bin 4 #将服务器id配置为1 5 server-id =1
注： 数据库ID用来标识组内的每个服务器，ID值必须是介于1和（2 32）-1 之间的正整数，数值可由自己设定。如未设定或设定为0，则主服务器拒绝来自所有服务器的连接。
从服务器设置：同样需要设置唯一的服务器ID。如要设置多个从服务器，则每个从服务器ID必须唯一且不和主服务器冲突。
[mysqld]
server-id = 2
#中继日志文件名前缀设为relay-bin
relay-log = relay-bin
#中继日志索引文件命前缀设为relay-bin-index
relay-log-index = relay-bin-index
#指定主服务器中的数据库名作为需要数据同步的库
replicate-do-db = test
如有其它需要可参考mysql官方文档：https://dev.mysql.com/doc/refman/5.5/en/replication-options.html
修改完my.cnf文件后，使用命令：sudo service mysql restart来重启mysql数据库；
使用命令：mysql -u root -p，然后输入密码连接到MySQL数据库；
再使用如下命令将从站连接到主站中；
1 mysql> stop slave; 2 mysql> change master to master_host='192.168.206.243',master_user='root',master_password='abcd'; 3 mysql> start slave; 4 mysql> show slave status\G;
如果看到下图中箭头指向的两个地方都是yes，则说明从数据库设置成功。
（图片来自：https://www.cnblogs.com/kylinlin/p/5258719.html）
如果Slave_SQL_Running为No，则查看show slave status \G;语句查询结果最下面的报错信息。
如果是由于事物回滚造成的，则可多次使用如下命令进行解决：
1 mysql> stop slave ; 2 mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 3 mysql> start slave ;
3、不同机器上性能测试
在三台机器上都安装了ubuntu14.04系统和MySQL5.5数据库，三台机器的硬件配置如下：
| 机器 | cpu | 内存 |
| 机器A(虚拟机64位） | i5-7500（单核） | 1G |
| 机器B（32位） | i3-4340 （4核） | 4G |
| 机器C（32位） | i3-2310E（4核） | 2G |
机器B作为主服务器，机器A作为从服务器，同步78W多条数据，大概耗时5分钟；机器C大概耗时20多个小时。
很奇怪为什么机器C为什么效果这么差，将机器C作为主服务器，直接导入mql文件测试，结果发现同样速度很慢，大概耗时20多个小时。相比较，机器B作为主服务器直接导入数据时速度也很快。因此暂时怀疑是机器C性能较差的原因，等待后续深入测试后再确定。
