LVS+MYCAT+读写分离+MYSQL主备同步部署手册
1.6.3 执行change master to系列命令… 3
1 配置MYSQL主备同步
1.1 测试环境
mysql版本:5.6.24;
操作系统版本:Linux-3.13-0-32
主数据库IP:192.168.10.3;
主数据库名:db351353;
备用数据库IP:192.168.10.4;
备用数据库名:db352354。
1.2 配置主数据库
1.2.1 编辑my.cnf文件
#服务器唯一ID,一般取IP最后一段
server_id = 3
#启用二进制日志
log_bin=mysql_bin
#需要备份的数据库名 多个库以逗号分隔
Binlog_do_db =db351353
#若涉及及同步函数或者存储过程需要配置,否则主备会产生异常不能同步
log_bin_trust_function_creators=TRUE
1.2.2 重启数据库
# service myql.server restart
1.3 锁定主数据库DDL操作
mysql> flush tables with read lock;
1.4 主备已有数据同步
用navicat for mysql将主数据库数据同步到备数据库
1.5 停止从服务
mysql>stop slave;
1.6 配置备用数据库
1.6.1 编辑my.cnf文件
#服务器唯一ID,一般取IP最后一段
server_id = 5
1.6.2 登录主数据库查看master状态
mysql>show master status;
1.6.3 执行change master to系列命令
mysql> change master to
master_host=’192.168.10.3′,
master_user=’iom’,–该用户要有Repl_slave_priv权限,没有可用grant replication slave on *.* to ‘iom’@’%’ identified by ‘xf4851213’授权
master_password=’123’,
master_log_file=’mysql_bin.000005’, –根据主服务器show master status出来的File结果填写
master_log_pos=1192; –根据主服务器show master status出来的Position结果填写
1.7 重启从服务
mysql>start slave;
1.8 解锁主数据库
mysql> unlock tables;
1.9 验证主从同步
在备份数据库上执行
mysql>show slave status;
slave_IO_running和slave_SQL_running 为yes。
主数据库新建一个表,往表里插入几条数据,备份数据库可以同步过来表和表中的数据。
2 MYCAT安装与配置
2.1 MYCAT安装
参见 MyCat_In_Action_CN.doc
2.2 参数设置
重点介绍rule.xml schema.xml router.xml server.xml log4j.xml
2.2.1 rule.xml
分片规则配置文件,mycat支持的所有分片规则都在这个文件里。定义一个规则需要两个节点,一个是tableRule,一个是function。
以一致性哈希分片为例,function节点定义了分片规则的实现类与初始化参数和分片规则的算法名称。
属性name为规则算法名称
class为规则实现类
property子节点为初始化规则的参数,使用seed count virtualBucketTimes就可初始化一致性哈希规则
<function name=”murmur” class=”org.opencloudb.route.function.PartitionByMurmurHash”>
<property name=”seed”>0</property><!– 默认是0–>
<property name=”count”>2</property><!– 要分片的数据库节点数量,必须指定,否则没法分片–>
<property name=”virtualBucketTimes”>160</property><!– 一个实际的数据库节点被映射为这么多虚拟节点,默认是160倍,也就是虚拟节点数是物理节点数的160倍–>
</function>
tableRule节点定义了分片规则名(注意此处是规则名,前面的function节点的name属性是算法名)rule子节点指定用来分片的数据库表字段和分片算法名,也就是前面的function节点的name属性。
<tableRule name=”sharding-by-murmur”>
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
任何自定义的分片规则也可以这样配置
2.2.2 server.xml
此文件用来配置mycat全局参数
<system>节点
<property name=”defaultSqlParser”>druidparser</property>,指定SQL解析器,默认是fdbparser,经测试druidparser效率更高
<property name=”sequnceHandlerType”>1</property>,指定mycat自动序列号生成方式。0:在借助本地文件生成序列号,1:借助数据库生成序列号,更多信息请参考Mycat in action
<property name=”serverPort”>8066</property>指定mycat服务端口号,mycat通过这个端口接收数据库客户端的访问请求。
另外还有一个9066端口没有出现在配置文件中,用来接收mycat监控命令、查询mycat运行状况、重新加载配置文件等。更多信息请参考Mycat in action。
<user>节点
name属性指定mycat用户名
<property name=”password”>password</property><!—用户密码–>
<property name=”schema”>database_name</property><!—数据库名,用客户端建立连接后要操作的数据库名 –>
<property name=”readOnly”>false</property><!—指定是不是只读库–>
可以有多个user节点。
2.2.3 router.xml
<queryRouter schema=”schema”><!—没太搞清楚这个文件的意义,我把schema属性、<name>、<queryNode>都配置成了一样的,而且都与要连接的数据库名保持一致 –>
<!—schema的值会在schema.xml用到 –>
<dataNode>
<name>dataNodeName</name>
<queryNode>queryNode</queryNode>
</dataNode>
</queryRouter>
2.2.4 schema.xml
<schema name=”TESTDB” checkSQLschema=”true” sqlMaxLimit=”100″>
其中checkSQLschema表明是否检查并过滤SQL中包含schema的情况,如逻辑库为 TESTDB,则可能写为select * from TESTDB.aaa,此时会自动过滤TESTDB,SQL变为select * from aaa,若不会出现上述写法,则可以关闭属性为false
<schema name=”schema”><!—就是router.xml命名的schema–>
<table name=”table_name” <!—要与它代理的数据库物理表名一致–>
primaryKey=”pk” <!—被用作主键的字段名–>
dataNode=”dataNodeName” <!—下面将会出现的dataNode节点–>
rule=”sharding-by-murmur”<!—rule.xml的tableRule节点name属性值,指定这个节点表示用这个规则执行分片–>
authIncrement=”true”><!—插入数据时是否由mycat自动生成分片,指定true时,如果insert sql内没有指定主键的值,而主键又是自增长的,mycat会自动插入生成主键的代码,并在生成主键时按照指定的分片规则将数据保存到数据库–>
<!—mycat有自己的自增序列命令,更多信息请参考Mycat in action–>
<!—此处可以指定多个table子节点,一个table节点表示一张表–>
</schema>
<dataNode name=”dataNodeName” dataHost=”localhost“ database=”database”/>
<!—分别是节点名,就是schema节点里的table子节点dataNode属性
dataHost是节点主机名,在下面要出现的dataHost定义
database数据库名
–>
<dataHost name=”localhost” maxCon=”1000” minCon=”10” balance=”0”
wrteType=”0” dbType=”mysql” dbDriver=”native>
<!—name属性是dataHost名
maxCon minCon分别是连接到物理数据库的最大最小连接数
dbType指定数据库类型
dbDriver只有两个取值,分别是native和jdbc。native为mycat自带驱动,只支持mysql,jdbc为使用jdbc实现连接数据库,指定什么数据库的jdbc驱动就可以访问什么数据库,更灵活,但效率不如native
可以有多个dataHost节点
–>
<heartbeat>select 1</heartbeat><!—保持连接不断的心跳sql–>
<writeHost host=”hostM1” url=”localhost:3306” user=”user” password=”password”>
<reeadHost host=”hostS1” url=”localhost:3306″ user=”root” password=”123456″/>
</writeHost>
<!—可以有多个writeHost,一个writeHost里要吧有多个readHost–>
</dataHost>
2.3 启动和使用MyCat
2.3.1 启动MyCat
# mycat console
2.3.2 访问MyCat
访问MyCat同访问MySQL的方式完全相同, 常用访问方式如下:
mysql –h 127.0.0.1 –u test –p test -P8066 –DTESTDB
2.3.3 自测
可以自己编写一些语句进行测试,看是否按规则进行分配。
3 KEEPALIVED配置
编辑/etc/keepalived/keepalived.cnf文件,红色字体是为mycat新加的
global_defs {
router_id LVS_MASTER #BACKUP上修改为LVS_BACKUP
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.10.252 #virtual server
192.168.10.253 #mycat
}
}
virtual_server 192.168.10.252 8080 {
delay_loop 6
lb_algo rr
lb_kind DR
#lb_kind NAT
# persistence_timeout 3
protocol TCP
real_server 192.168.10.3 8080 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 8080
}
}
real_server 192.168.10.4 8080 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 8080
}
}
}
virtual_server 192.168.10.253 8066 {
delay_loop 6
lb_algo rr
lb_kind DR
#lb_kind NAT
# persistence_timeout 3
protocol TCP
real_server 192.168.10.3 8066 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 8066
}
}
real_server 192.168.10.4 8066 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 8066
}
}
}
4 mycat服务器执行脚本
lvs_mycat.sh脚本内容:
#!/bin/bash
VIP=192.168.10.253
case “$1” in
start)
ifconfig lo:0 $VIP netmask 255.255.255.255 broadcast $VIP
/sbin/route add -host $VIP dev lo:0
echo “1” >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo “2” >/proc/sys/net/ipv4/conf/lo/arp_announce
echo “1” >/proc/sys/net/ipv4/conf/all/arp_ignore
echo “2” >/proc/sys/net/ipv4/conf/all/arp_announce
sysctl -p >/dev/null 2>&1
echo “lvs_vip server start ok!”;;
stop)
ifconfig lo:0 down
/sbin/route del $VIP >/dev/null 2>&1
echo “0” >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo “0” >/proc/sys/net/ipv4/conf/lo/arp_announce
echo “0” >/proc/sys/net/ipv4/conf/all/arp_ignore
echo “0” >/proc/sys/net/ipv4/conf/all/arp_announce
echo “lvs_vip server stoped.”;;
*)
echo “arg start|stop.”
exit 1
esac
exit 0
在脚本所在目录执行#./lvs_mycat.sh start;脚本主要作用是为服务器的回环接口设定虚拟IP,并屏蔽该IP的arp请求和应答。
5 总体测验
5.1 网络架构图
客户机IP:192.168.10.1
负载机VIP:192.168.10.253
主mycat和mysql1在同一个服务器上,IP为:192.168.10.3
从mycat和mysql2在同一个服务器上,IP为:192.168.10.4
mysql1上创建两个数据库db351353(主),db352354(备),mysql2上创建两个数据库db352354(主),db351353(备);mysql2上db351353是mysql1上db351353的备用库,mysql1上的db352354是mysql2上db352354的备用库。
5.2 客户机测试环境
安装navicat for mysql,创建5个数据连接:
- 直连mysql1数据库 连接名:mysql1 ip:168.10.3 端口:3306
- 直连mysql2数据库 连接名:mysql2 ip:168.10.4 端口:3306
- 直连主mycat 连接名:mycat1 ip:168.10.3 端口:8066
- 直连从myca2 连接名:mycat2 ip:168.10.4 端口:8066
- 连接lvs 连接名:keepalived ip:168.10.253 端口:8066
5.3 MYSQL主备同步测试
在mysql1上的db351353 添加数据:
DROP TABLE IF EXISTS `so`;
CREATE TABLE `so` (
`so_nbr` int(8) NOT NULL DEFAULT ‘0’,
`local_net_id` int(8) DEFAULT NULL,
`proc_inst_id` int(8) DEFAULT NULL,
`prod_id` varchar(8) DEFAULT NULL,
PRIMARY KEY (`so_nbr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `so` VALUES (‘1’, ‘351’, null, null);
在mysql2上的db352354添加数据:
DROP TABLE IF EXISTS `so`;
CREATE TABLE `so` (
`so_nbr` int(8) NOT NULL DEFAULT ‘0’,
`local_net_id` int(8) DEFAULT NULL,
`proc_inst_id` int(8) DEFAULT NULL,
`prod_id` varchar(8) DEFAULT NULL,
PRIMARY KEY (`so_nbr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `so` VALUES (‘2’, ‘352’, null, null);
在各自的备份库上能查到对应的数据,可参考1.9
5.4 MYCAT测试
5.4.1 配置mycat主从策略和分片策略
按so表本地网水平分片,351和353放到节点dn1,352和354放到节点dn2。
5.4.1.1 schema.xml内容:
红色字体为注释,实际文件里没有,注意dn2节点的主从连接库顺序,按说明应该配在前面的写入数据库为主数据库,后面的写入数据库为从数据库,但dn2节点却正好相反,原因待查。
<?xml version=”1.0″?>
<!DOCTYPE mycat:schema SYSTEM “schema.dtd”>
<mycat:schema xmlns:mycat=”http://org.opencloudb/”>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″>
<table name=”prod” primaryKey=”prod_id” type=”global” dataNode=”dn1,dn2″ />
<table name=”so” primaryKey=”so_nbr” dataNode=”dn1,dn2″
rule=”sharding-by-intfile”>
</table>
</schema>
<dataNode name=”dn1″ dataHost=”hostdn1″ database=”db351353″ />
<dataNode name=”dn2″ dataHost=”hostdn2″ database=”db352354″ />
<dataHost name=”hostdn1″ maxCon=”1000″ minCon=”10″ balance=”1″
writeType=”0″ dbType=”mysql” dbDriver=”native”>
<heartbeat>select user()</heartbeat>
<!– can have multi write hosts –>
<writeHost host=”hostM1″ url=”192.168.10.3:3306″ user=”iom”–dn1节点主连接库
password=”xf4851213″>
<!– <readHost host=”hostS1″ url=”192.168.10.4:3306″ user=”iom” password=”123″ /> –>
</writeHost>
<writeHost host=”hostM2″ url=”192.168.10.4:3306″ user=”iom”–dn1节点从连接库
password=”123″/>
</dataHost>
<dataHost name=”hostdn2″ maxCon=”1000″ minCon=”10″ balance=”1″
writeType=”0″ dbType=”mysql” dbDriver=”native”>
<heartbeat>select user()</heartbeat>
<writeHost host=”host2M1″ url=”192.168.10.3:3306″ user=”iom” –dn2节点从连接库
password=”xf4851213″>
<!– <readHost host=”hostS1″ url=”192.168.0.3:3306″ user=”iom” password=”xf4851213″ />–>
</writeHost>
<writeHost host=”host2M2″ url=”192.168.10.4:3306″ user=”iom”–dn2节点主连接库
password=”123″/>
</dataHost>
</mycat:schema>
5.4.1.2 rule.xml内容
红色字体为注释,实际文件里没有。
<?xml version=”1.0″ encoding=”UTF-8″?>
<!–
–
– Licensed under the Apache License, Version 2.0 (the “License”);
– you may not use this file except in compliance with the License.
– You may obtain a copy of the License at
–
– http://www.apache.org/licenses/LICENSE-2.0
–
– Unless required by applicable law or agreed to in writing, software
– distributed under the License is distributed on an “AS IS” BASIS,
– WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
– See the License for the specific language governing permissions and
– limitations under the License.
–>
<!DOCTYPE mycat:rule SYSTEM “rule.dtd”>
<mycat:rule xmlns:mycat=”http://org.opencloudb/”>
<tableRule name=”rule1″>
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name=”rule2″>
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name=”sharding-by-intfile”>
<rule>
<columns>local_net_id</columns> –按本地网分片
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name=”auto-sharding-long”>
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tableRule name=”mod-long”>
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name=”sharding-by-murmur”>
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<function name=”murmur” class=”org.opencloudb.route.function.PartitionByMurmurHash”>
<property name=”seed”>0</property><!– 默认是0–>
<property name=”count”>2</property><!– 要分片的数据库节点数量,必须指定,否则没法分片–>
<property name=”virtualBucketTimes”>160</property><!– 一个实际的数据库节点被映射为这么多虚拟节点,默认是160倍,也就是虚拟节点数是物理节点数的160倍–>
<!–
<property name=”weightMapFile”>weightMapFile</property>
节点的权重,没有指定权重的节点默认是1。以properties文件的格式填写,以从0开始到count-1的整数值也就是节点索引为key,以节点权重值为值。所有权重值必须是正整数,否则以1代替 –>
<!–
<property name=”bucketMapPath”>/etc/mycat/bucketMapPath</property>
用于测试时观察各物理节点与虚拟节点的分布情况,如果指定了这个属性,会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件,没有默认值,如果不指定,就不会输出任何东西 –>
</function>
<function name=”hash-int” class=”org.opencloudb.route.function.PartitionByFileMap”>
<property name=”mapFile”>partition-hash-int.txt</property>
</function>
<function name=”rang-long” class=”org.opencloudb.route.function.AutoPartitionByLong”>
<property name=”mapFile”>autopartition-long.txt</property>
</function>
<function name=”mod-long” class=”org.opencloudb.route.function.PartitionByMod”>
<!– how many data nodes –>
<property name=”count”>3</property>
</function>
<function name=”func1″ class=”org.opencloudb.route.function.PartitionByLong”>
<property name=”partitionCount”>8</property>
<property name=”partitionLength”>128</property>
</function>
</mycat:rule>
5.4.1.3 partition-hash-int内容:
红色字体为注释,实际文件里没有。
351=0 –分配到dn1节点
352=1 –分配到dn2节点
353=0
354=1
5.4.2 测试mycat连接
客户机navicat打开mycat1连接,能看到一个数据库TESTDB,数据库中能查到db351353和db352354两个数据库的所有数据。mycat2连接亦同。
5.4.3 测试mycat读写分离
测试目的:由4.4.1.1可知,dn1连接的主数据库是mysql1的db351353,从数据库是mysql2的db351353,balance值为1,此模式下从数据库也为读数据库。mycat增删改操作应通过mysql1的db351353进行,读操作应通过mysql2的db351353进行。
测试方法:客户机通过navicat的mysql2连接更改db351353的so表记录,这样db351353在mysql1和mysql2上有两份不同的数据,在客户机navicat的mycat1连接上查看so表本地网为351的数据是否与mysql2相同,如相同则测试通过。测试过后要恢复mysql2上db351353的数据,以免影响剩余的测试。
5.4.4 测试mycat数据节点容错
测试目的:由4.4.1.1可知,dn1连接的主数据库是mysql1的db351353,从数据库是mysql2的db351353,如果mysql1服务挂掉,dn1节点的增删改操作应自动切换到mysql2的db351353。
测试方法:在mysql1主机上执行 #service mysql.server stop; 在客户机navicat的mycat1连接上看是否能正常查询so表数据,如果查询正常,修改so表本地网为351的数据,在客户机navicat的mysql2连接上查看db351353的so表数据是否和修改的一样,如果一样则测试通过。
注意:如果mysql1服务重新启动,则mysql1的db351353为从数据库,不会重新变为主数据库。
5.4.5 测试mycat节点容错
测试目的:mycat1和mycat2单个节点宕掉,不影响客户机使用数据库。
测试方法:客户机navicat使用keepalived连接,能正常查询和增删改数据。在mycat1上执行#mycat stop;客户机keepalived连接仍能正常使用。mycat1上执行#mycat console,mycat2上执行#mycat stop,客户机keepalived连接能正常使用。mycat2上执行#mycat console,客户机keepalived连接能正常使用,且数据请求路由和keepalived所配lb_algo策略相同。