【诗檀软件汪伟华】 MySQL Replication 复制高可用配置方案
MySQL Replication 复制高可用配置方案:
本文目的
对MySQL高可用架构原理进行学习总结。
相关参考
- MySQL Replication:
http://dev.mysql.com/doc/refman/5.6/en/replication.html
- MySQL Replication Pausing:
http://dev.mysql.com/doc/refman/5.6/en/replication-administration-pausing.html
架构原理
MySQL Replication
MySQL Replication可以使得数据从一个MySQL数据库服务器(主库)被复制到一个或其他更多MySQL数据库服务器(备库)上。复制默认是异步模式。
FAQ
问题1:
我该如何强制阻止主库更新，直到备库的同步作业赶上主库呢？
回答:
可使用以下步骤：
- 在主库上，执行以下命令:
| mysql> flush tables with read lock;
mysql> show master status; |
查看show命令输出，并记录下replication坐标(当前主库binary日志文件名和其位置)。
- 在备库上，执行以下语句，其中master_pos_wait()函数参数值为之前所获取的replication坐标值。
| mysql> select master_pos_wait(‘log_name’, log_pos) |
此语句会一直处于运行状态直至备库同步到其指定的日志文件和位置时才结束。这时，语句返回且表明备库和主库同步完成。
- 在主库中，执行以下命令重新允许主库开始处理更新操作。
| mysql> unlock tables; |
问题2:
我应该如何使用Replication来提升系统性能？
回答:
将一个服务器作为主库并使其负载有的写操作。同时按你的预算和机架空间配置尽可能多地的备库, 将读操作分布到主库和备库上。你也可以在备库上启用–low-priority-updates(仅对myISAM)和–delay-key-write=ALL等参数项来提升备库速度。在这种情况下，备库上使用非事务型MyISAM表以替代InnoDB表，减少了事务开销，从而获得更快的速度。
问题3:
如何量化MySQL replication对系统的性能提升？
回答:
MySQL replication对于那些需要处理频繁读和非频繁写的系统非常有益。理论上，通过在进行单主库多备库建设时，你可以增加更多备库来扩展你的系统，直到用完所有网络带宽，或直到你的更新负载已经达到主库所能达到的最大值时为止。
为了在建设前评估需备库数量和性能提高比率，你必须了解你的查询模式，并以主库和备库的读写关系为基准进行判断。下面用了一个简单计算模型来显示你从replication中可以获得的系统提升:
首先统计每秒相对的读(read)和写(write)次数。
假设当前系统负载中包含10%写和90%读，而我们已经确定读写基准关系为读的次数为1200减去写次数的两倍。换言之，系统可以在无写操作的情况下达到每秒1200次读，其平均写时间比平均读时间慢2倍, 两者的关系是线性的。假设主库和备库性能相同，且我们使用1主N备。那么，对每个服务器(主库或备库)，我们得出以下模型：
| reads = 1200 – 2 * writes
reads = 9 * writes / (N + 1) (读被分布在各个服务器上, 写则被replicate到多个备库上) 9 * writes / (N + 1) + 2 * writes = 1200 writes = 1200 / (2 + 9/(N + 1)) |
最后的等式表明在N个备库的情况下和写之间的关系，通过分析可得出以下结论：
- 如果N=0（意味着无replication），系统每秒可处理约1200/11=109次写。
- 如果N=1，可得到每秒为184次写。
- 如果N=8，每秒写提升至400次。
- 如果N=17，每秒写为480次。
- 最终N趋于无穷大，我们可获得约600次写每秒。系统性能提升约5倍。然而，仅8台备库的情况下，我们就能获得大约4倍的性能提升了。
以上这些计算都是在假设有无限带宽且没有其他巨大负面因素的基础下。在许多情况下，你可能并不能使用类似此模型来如此精确地预估增加N台replication备库后的性能效果。然而，通过回答以下问题应该能帮助你判断大致增加多少replication会对你的系统性能有一定的提高：
- 系统的读/写比率是多少？
- 如果你降低读负载，服务器可更多承贷多少写负载？
- 你的网络带宽足够多少台备库使用？
问题4:
如何分辨主库正使用statement-based还是row-based binary 日志格式。
回答:
| mysql> show variable like ‘binlog_format’; |
值显示模式为STATEMENT, ROW或 MIXED。对于MIXED模式，默认在replication时使用statement-based，在某些情况，如非安全语句，则会自动切换使用row-based日志模式。
问题5:
在replication复制到备库时，该如何避免GRANT和REVOKE语句也被备库复制？
回答:
启用备库时, 使用 –replicate-wild-ignore-table=mysql.%参数项以忽略从主库mysql数据库中复制表。
环境准备
软件准备
- vbox虚拟软件:
- RedHat Enterprise Linux 6.6 64bit介质:
rhel-server-6.6-x86_64-dvd.iso
- SecureCRT
- FlashFXP或其他FTP软件
节点安装
MySQL Replication要求至少2个节点，用于主备(Master-Slave)。
这里我们先装1个节点，安装Linux系统和Mysql 5.6. 然后进行虚拟机clone，克隆出2节点。
建立时选桥接网卡即可。
| Linux安装 |
使用vbox新建一个虚拟机，根据你的介质初步选择你的虚拟机系统。之后启动虚拟机，并将介质iso文件加载至虚拟机光驱, 之后的Linux安装设置大致都默认即可。
这里的主机名hostname按情况填写即可(ex1.dbdao)
密码如果偏弱，可能会有警告，Use Anyway即可。
勾选Review and modify partitioning layout, 这样就在后一页中提供磁盘分区的详细信息。
Format
Write Changes to disk.
这里选Customize now，将Desktop相关的组件都先装上，以便可以提供Window X桌面支持。
安装好后重启。
装好后第一次启动会进入设置界面：
选No.
因为这里虚拟机设置了1G内存，不足以开启此功能，但是我们这里仅是做实验，因此不必关心此警告。即便内存够，也不用开启。
使用root登陆成功
Username: root
Password: <passowrd>.
安装vbox guest additions:
检查网络
检查Yum
如果Yum不存在，使用rpm找到rhel6.6 iso中的yum安装包进行安装。
| shell> rpm -qa|grep yum
shell> cd /media shell> touch cdrom shell> mount /dev/cdrom ./cdrom shell> cd /etc/yum.repos.d shell> touch rhel6.6-self.repo shell> vi rhel6.6-self.repo |
检查Firefox(可选)
| shell> yum search firefox
shell> yum install firefox |
调整网卡IP，DNS设置，vbox虚拟机网卡使用Bridged桥接网卡模式。检查是否可上网(MySQL yum安装需要网络可访问)。
检查SSH
| shell> rpm -qa|grep openssh
shell> service sshd status |
检查FTP
| shell> rpm -qa|grep vsftp
shell> yum search vsftp shell> yum install vsftpd shell> service vsftpd status |
安装完后还未启动，需要设置。
| shell> cd /etc/vsftpd
shell> vi /etc/vsftpd/vsftpd.conf shell> vi user_list |
以下为要更改的选项:
anonymous_enable=NO
ascii_upload_enable=YES
ascii_download_enable=YES
新增 userlist_deny 选项， user_list 文件中的用户才允许访问：
userlist_deny=NO
Comment