MySQL 5.6.13基于GTID的复制,以及Amoeba读写分离的实现
一、MySQL 5.6介绍
MySQL 5.6引入的GTID(Global Transaction IDs)使得其复制功能的配置、监控及管理变得更加易于实现,且更加健壮,主要在查询性能的优化、InnoDB改进以支持高吞吐量的事务、分区功能的改进、数据复制的改进,增加 PERFORMANCE_SCHEMA 库以获得数据库性能信息等。
复制功能的改进:
1、支持多线程复制,指的是针对每个database开启相应的独立线程。即每个库有一个单独的(sql thread)如果线上业务中,只有一个database或者绝大多数压力集中在个别database的话,多线程并发复制特性就没有意义了。
2、支持启用GTID,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向,mysql5.6里,无须再知道binlog和POS点,需要知道master的IP、端口,账号密码即可,因为同步复制是自动的,mysql通过内部机制GTID自动找点同步。
二、Amoeba介绍
Amoeba是一个以MySQL为底层数据存储,并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求,依据用户事先设置的规则,将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。
Amoeba相当于一个SQL请求的路由器,目的是为负载均衡、读写分离、高可用性提供机制,而不是完全实现它们。用户需要结合使用MySQL的 Replication等机制来实现副本同步等功能。amoeba对底层数据库连接管理和路由实现也采用了可插拨的机制,第三方可以开发更高级的策略类来替代作者的实现。
Amoeba主要优势:
1、数据切分后复杂数据源整合;
2、提供数据切分规则并降低数据切分规则给数据库带来的影响;
3、降低数据库与客户端连接;
4、读写分离路由。
Amoeba不足:
1、不支持事务;
2、不支持存储过程;
3、不适合从amoeba导数据的场景或者对大数据量查询的query并不合适(比如一次请求返回10w以上甚至更多数据的场合);
4、不支持分库分表,amoeba目前只做到分数据库实例,每个被切分的节点需要保持库表结构一致。
三、实验环境
192.168.30.115 OS:CentOS 6.4 x86_64 amoeba.luojianlong.com
192.168.30.116 OS:CentOS 6.4 x86_64 master.luojianlong.com
192.168.30.117 OS:CentOS 6.4 x86_64 slave.luojianlong.com
MySQL version:mysql-5.6.13-linux-glibc2.5-x86_64
Amoeba version:amoeba-mysql-binary-2.1.0-RC5
拓扑图如下:
首先,分别在master.luojianlong.com和slave.luojianlong.com上安装mysql 5.6.13,这里使用通用的二进制包来安装
[root@master ~]# useradd -r -u 27 mysql [root@master ~]# tar zxvf mysql-5.6.13-linux-glibc2.5-x86_64.tar.gz -C /usr/local/ [root@master ~]# cd /usr/local/ [root@master local]# ln -s mysql-5.6.13-linux-glibc2.5-x86_64 mysql [root@master local]# ln -s mysql-5.6.13-linux-glibc2.5-x86_64 mysql [root@master local]# cd mysql [root@master mysql]# mkdir /mydata/data -p [root@master mysql]# chown -R root.mysql ./* [root@master mysql]# chown -R mysql.mysql /mydata/data/ [root@master mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data [root@master mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld [root@master mysql]# chkconfig --add mysqld [root@master mysql]# chkconfig mysqld on [root@master mysql]# chmod +x /etc/rc.d/init.d/mysqld [root@master ~]# vi /etc/profile.d/mysql.sh export PATH=/usr/local/mysql/bin:$PATH [root@master ~]# . /etc/profile.d/mysql.sh # 由于mysql 5.6版本默认的配置文件配置参数很少,需要自己定制,为了方便,这里使用mysql 5.5的配置文件 [root@master mysql]# cp my-large.cnf /etc/my.cnf [root@master mysql]# vi /etc/my.cnf # 在[mysqld]中添加如下 datadir = /mydata/data innodb_file_per_table = 1 binlog-format=ROW log-bin=master-bin log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=1 report-port=3306 port=3306 report-host=192.168.30.116
在slave.luojianlong.com中也使用相同的方式安装mysql 5.6
# 安装完成后,修改配置文件 [root@slave ~]# vi /etc/my.cnf # 添加如下 datadir = /mydata/data innodb_file_per_table = 1 binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=11 report-port=3306 port=3306 log-bin=mysql-bin.log datadir=/mydata/data report-host=192.168.30.117
binlog-format:二进制日志的格式,有row、statement和mixed几种类型;
需要注意的是:当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW,现在MySQL官方认为STATEMENT这个已经不再适合继续使用;但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致;
log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host:用于启动GTID及满足附属的其它需求;
master-info-repository和relay-log-info-repository:启用此两项,可用于实现在崩溃时保证二进制及从服务器安全的功能;
sync-master-info:启用之可确保无信息丢失;
slave-paralles-workers:设定从服务器的SQL线程数;0表示关闭多线程复制功能;
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum:启用复制有关的所有校验功能;
binlog-rows-query-log-events:启用之可用于在二进制日志记录事件相关的信息,可降低故障排除的复杂度;
log-bin:启用二进制日志,这是保证复制功能的基本前提;
server-id:同一个复制拓扑中的所有服务器的id号必须惟一;
分别启动俩台服务器的mysql
[root@master ~]# service mysqld start Starting MySQL............ SUCCESS! [root@slave ~]# service mysqld start Starting MySQL... SUCCESS!
在master服务器上创建复制用户
mysql> grant replication slave on *.* to ‘repluser‘@‘192.168.30.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.06 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
启动从节点的复制线程
mysql> change master to master_host=‘192.168.30.116‘, master_user=‘repluser‘, master_password=‘123456‘, master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.45 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.12 sec)
在slave上查看线程启动情况
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.30.116 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 539 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 751 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 539 Relay_Log_Space: 955 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: b05c9b5c-bfac-11e3-a4bc-000c29f3fcba Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: b05c9b5c-bfac-11e3-a4bc-000c29f3fcba:1-2 Executed_Gtid_Set: b05c9b5c-bfac-11e3-a4bc-000c29f3fcba:1-2 Auto_Position: 1 1 row in set (0.00 sec)
发现gtid复制已经启动成功,并可以查看主从服务器的uuid
mysql> show global variables like ‘%uuid‘; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | b05c9b5c-bfac-11e3-a4bc-000c29f3fcba | +---------------+--------------------------------------+ 1 row in set (0.00 sec) mysql> show global variables like ‘%uuid‘; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | ba83181a-bfac-11e3-a4bc-000c295b50f9 | +---------------+--------------------------------------+ 1 row in set (0.00 sec)
下面开始配置amoeba,首先安装jdk环境
[root@amoeba ~]# tar zxvf jdk-7u25-linux-x64.gz -C /usr/local/ [root@amoeba ~]# cd /usr/local/ [root@amoeba local]# ln -s jdk1.7.0_25 jdk [root@amoeba local]# vi /etc/profile.d/jdk.sh export JAVA_HOME=/usr/local/jdk export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib export PATH=/usr/local/jdk/bin:$PATH [root@amoeba local]# . /etc/profile.d/jdk.sh [root@amoeba local]# java -version java version "1.7.0_25" Java(TM) SE Runtime Environment (build 1.7.0_25-b15) Java HotSpot(TM) 64-Bit Server VM (build 23.25-b01, mixed mode)
下载安装amoeba
[root@amoeba ~]# mkdir /usr/local/amoeba [root@amoeba ~]# tar zxvf amoeba-mysql-binary-2.1.0-RC5.tar.gz -C /usr/local/amoeba/ [root@amoeba ~]# vi /etc/profile.d/amoeba.sh export PATH=/usr/local/amoeba/bin:$PATH [root@amoeba ~]# . /etc/profile.d/amoeba.sh [root@amoeba ~]# cp /usr/local/amoeba/conf/amoeba.xml /usr/local/amoeba/conf/amoeba.xml.bak [root@amoeba ~]# cp /usr/local/amoeba/conf/dbServers.xml /usr/local/amoeba/conf/dbServers.xml.bak
调整amoeba配置文件,设置读写分离
[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml # 在<service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">子标签中修改如下几项 <property name="port">3306</property> <property name="ipAddress">0.0.0.0</property> <property name="password">mypass</property> # 在<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">子标签中启用修改下面几项 <property name="writePool">server1</property> <property name="readPool">server2</property> [root@amoeba ~]# vi /usr/local/amoeba/conf/dbServers.xml # 启动并修改 <dbServer name="abstractServer" abstractive="true">子标签中的 <property name="password">mypass</property> # 修改 <dbServer name="server1" parent="abstractServer">子标签中的 <property name="ipAddress">192.168.30.116</property> # 新添加一个dbServer如下 <dbServer name="server2" parent="abstractServer"> <factoryConfig> <!-- mysql ip --> <property name="ipAddress">192.168.30.117</property> </factoryConfig> </dbServer>
在master和slave中分别创建用于amoeba连接俩台mysql的用户
# 在master上面创建 mysql> grant all on *.* to ‘root‘@‘192.168.30.%‘ identified by ‘mypass‘; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) #发现已经同步到slave mysql> show grants for ‘root‘@‘192.168.30.%‘; +-------------------------------------------------------------------------------------------------------------------------+ | Grants for [email protected]% | [email protected]---------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘192.168.30.%‘ IDENTIFIED BY PASSWORD ‘*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4‘ | +-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
启动amoeba
[root@amoeba ~]# vi /usr/local/amoeba/bin/amoeba DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k" # 修改为 DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k" [root@amoeba ~]# amoeba start log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml 2014-04-09 15:09:15,121 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.1.0-RC5 log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf 2014-04-09 15:09:15,482 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on /0.0.0.0:3306. 2014-04-09 15:09:15,489 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:53201. [root@amoeba ~]# netstat -anptl | grep :3306 tcp 0 0 :::3306 :::* LISTEN 19208/java tcp 0 0 ::ffff:192.168.30.115:33408 ::ffff:192.168.30.116:3306 ESTABLISHED 19208/java tcp 0 0 ::ffff:192.168.30.115:59880 ::ffff:192.168.30.117:3306 ESTABLISHED 19208/java [root@amoeba ~]# mysql -u root -pmypass -h 192.168.30.115 -P 3306 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1783342624 Server version: 5.1.45-mysql-amoeba-proxy-2.1.0-RC5 MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.03 sec)
启动成功,并访问正常
下面使用tcpdump命令抓包测试读写分离效果
[root@master ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.116 [root@slave ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.117 # amoeba服务器上登录mysql,并创建数据库 mysql> create database abc; Query OK, 1 row affected (0.03 sec) [root@master ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.116 tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes 15:16:42.492828 IP 192.168.30.115.33408 > 192.168.30.116.3306: Flags [P.], seq 1149949367:1149949391, ack 113323290, win 123, options [nop,nop,TS val 8373727 ecr 10837334], length 24 E..L&"@.@.VR...s...t....D.....-....{4]..... ......]V.....create database abc 15:16:42.501837 IP 192.168.30.115.33408 > 192.168.30.116.3306: Flags [.], ack 12, win 123, options [nop,nop,TS val 8373736 ecr 11221763], length 0 E..4&#@.@.Vi...s...t....D.....-%...{.I..... ......;. 15:16:42.502356 IP 192.168.30.117.38626 > 192.168.30.116.3306: Flags [.], ack 3194819787, win 170, options [nop,nop,TS val 9637577 ecr 11221763], length 0 E..4.0@.@..Q...u...t.........m............. ......;. # 发现在master上面抓到了创建数据库的包 [root@slave ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.117 tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes # slave上面没有抓到任何包 # 下面在amoeba上面执行创建表,插入,查询语句 mysql> use abc Database changed mysql> create table test(id int); Query OK, 0 rows affected (0.41 sec) mysql> insert into test values(1); Query OK, 1 row affected (0.04 sec) mysql> select * from test; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.01 sec) [root@master ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.116 tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes 15:20:42.054548 IP 192.168.30.115.33419 > 192.168.30.116.3306: Flags [P.], seq 976065559:976065590, ack 2591052457, win 123, options [nop,nop,TS val 8613289 ecr 11418139], length 31 E..S."@.@..J...s...t....:-...pR....{.H..... ..m...:......insert into test values(1) 15:20:42.085305 IP 192.168.30.117.38626 > 192.168.30.116.3306: Flags [.], ack 3194820258, win 189, options [nop,nop,TS val 9877161 ecr 11461346], length 0 E..4.2@.@..O...u...t.........m.......*..... ........ 15:20:42.086686 IP 192.168.30.115.33419 > 192.168.30.116.3306: Flags [.], ack 12, win 123, options [nop,nop,TS val 8613320 ecr 11461347], length 0 E..4.#@.@..h...s...t....:-.6.pR....{./..... ..m..... [root@slave ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.117 tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes 15:20:52.076327 IP 192.168.30.115.59899 > 192.168.30.117.3306: Flags [P.], seq 201259223:201259232, ack 2661648116, win 115, options [nop,nop,TS val 8623261 ecr 9819401], length 9 E..=..@.@......s...u...............s.&..... ....... .....abc. 15:20:52.076821 IP 192.168.30.115.59899 > 192.168.30.117.3306: Flags [.], ack 12, win 115, options [nop,nop,TS val 8623261 ecr 9887103], length 0 E..4..@.@......s...u...............s#q..... ........ 15:20:52.077609 IP 192.168.30.115.59899 > 192.168.30.117.3306: Flags [P.], seq 9:32, ack 12, win 115, options [nop,nop,TS val 8623262 ecr 9887103], length 23 E..K..@.@......s...u...............s....... .............select * from test 15:20:52.118046 IP 192.168.30.115.59899 > 192.168.30.117.3306: Flags [.], ack 82, win 115, options [nop,nop,TS val 8623303 ecr 9887105], length 0 E..4..@.@......s...u...........E...s"...... 观察抓包情况,发现所有的更新操作都在master,查询操作都在slave 上面
登录主从数据库查看数据是否一致
# master mysql> use abc Database changed mysql> select * from test; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) # slave mysql> use abc Database changed mysql> select * from test; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) # 发现数据一致
四、Amoeba优化注意以下几点:
1、 查询的类型,是属于并发少,但是语句复杂的查询,还是属于并发高,语句不复杂的查询。
2、 查询的结果,查询结果返回的数据量大小
3、 最终的优化效果取决于后端真实mysqldb的配置,比如并发数,每个并发的缓存分配等,以及mysqldb自身的优化
简单的优化配置:
1、 修改amoeba启动参数,增加JAVA虚拟机的内存
修改amoeba执行程序,查找DEFAULT_OPTS,修改为以下内容:
DEFAULT_OPTS="-server -Xms1024m -Xmx1024m -Xss256k"
2、 amoeba.xml中关于多线程和网络的优化
[root@amoeba ~]# vim /usr/local/amoeba/conf/amoeba.xml # 修改如下几项 <property name="readThreadPoolSize">500</property> <property name="clientSideThreadPoolSize">500</property> <property name="serverSideThreadPoolSize">500</property> <property name="sendBufferSize">256</property> <property name="receiveBufferSize">128</property>
3、 amoeba.xml中还有一个处理客户端连接和网络信息使用CPU核数的配置,不过默认配置是主机拥有的CPU核的数量,一般不用修改,如果你的amoeba负载较高,并且机器上还有其他服务,你就可以降低这个配置,避免系统或者其他服务没有资源,或者出现资源争抢,从而导致整理的处理能力下降。
查找amoeba.xml文件中connectionManagerList关键字,修改
[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml <!-- default value is avaliable Processors <property name="processors">5</property> --> # 取消注释,并设置processors数量。
到此,MySQL 5.6基于GTID的复制,以及Amoeba读写分离的实现配置完成。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。