LVS+MYCAT读写分离+MYSQL同步部署手册(第三版)

2017-01-11 15:23:47来源:oschina作者:xingfei人点击

1 配置MYSQL主备同步
1.1 测试环境

mysql版本:5.6.24;


操作系统内核版本:Linux-3.13-0-32


主数据库IP:192.168.10.3;


主数据库名:db351353;


备用数据库IP:192.168.10.4;


备用数据库名:db351353。


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 = 4


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 配置MYSQL主主同步
2.1 测试环境

mysql版本:5.6.24;


操作系统内核版本:Linux-3.13-0-32


数据库A IP:192.168.10.3;


数据库B IP:192.168.10.4;


要同步的数据库名:db351353;db352354


2.2 编辑配置文件
2.2.1 A机配置文件

# vi my.cnf

#服务器唯一ID,一般取IP最后一段


server_id = 3


#启用二进制日志


log_bin=mysql_bin


#需要备份的数据库名


binlog_do_db =db351353


binlog_do_db =db352354


binlog_ignore_db=mysql


#若涉及同步函数或者存储过程需要配置,否则主备会产生异常不能同步


log_bin_trust_function_creators=TRUE


#同步参数: #保证slave挂在任何一台master上都会接收到另一个master的写入信息


log_slave_updates sync_binlog=1


# auto_increment_offset表示自增长字段从哪个数开始,两主机应一个奇数一个偶数。


auto_increment_offset=1


#auto_increment_increment表示自增长字段每次增长几位数,两主机应该一模一样


auto_increment_increment=2


#过滤掉一些没啥大问题的错误


slave_skip_errors=all


#需要进行同步的数据库.如果有多个数据库可使用多个replicate_do_db选项


replicate_do_db = db351353


replicate_do_db = db352354


#不需要同步的数据库.如果有多个数据库可使用多个replicate_ignore_db选项


replicate_ignore_db = mysql


replicate_ignore_db = information_schema


2.2.2 B机配置文件

# vi my.cnf

server_id = 4


log_bin=mysql_bin


binlog_do_db = db351353


binlog_do_db = db352354


binlog_ignore_db=mysql


replicate_do_db = db351353


replicate_do_db = db352354


log_bin_trust_function_creators=TRUE

replicate_ignore_db = mysql


replicate_ignore_db = information_schema

log_slave_updates


sync_binlog=1


auto_increment_offset=2 –此处与另一台机子不同


auto_increment_increment=2


slave_skip_errors=all


2.2.3 配置好my.cnf后,重启数据库A和B。

mysql>service mysql.server restart

2.3 执行配置命令
2.3.1 设置同步帐户

创建帐户并赋权


A机执行:


mysql> grant replication slave on *.* to ‘iomsync’@’192.168.10.4’ identified by ‘123’;


B机执行:


mysql> grant replication slave on *.* to ‘iomsync’@’192.168.10.3’ identified by ‘123’;


2.3.2 锁定数据库,查看主机服务状态

分别在A,B上执行:


mysql>flush tables with read lock;#防止进入新的数据


mysql>show master status;#查看主机服务状态


2.3.3 同步已有数据

使用navicat的数据传输功能同步数据,把源和目标两属性填写正确,左下角的数据库对象选择全选,点开始。


注意:同步时目标mysql必须先建好要同步的数据库。

2.3.4指定同步位置

master_log_file和master_log_pos 两个参数根据2.4.2的show master status命令结果填写,master_log_file对应File,master_log_pos对应Position。


A上执行:


mysql>change master to master_host=’192.168.10.4’,master_user=’iomsync’,master_password=’123’, master_log_file=’mysql_bin.000024’,master_log_pos=3946;–填B机上的show master status结果


B上执行:


mysql>change master to


master_host=’192.168.10.3’,master_user=’ iomsync’,master_password=’123’, master_log_file=’mysql_bin.000008′,master_log_pos=8538;– 填A机上的show master status结果


2.3.5 解锁数据库

在A,B上分别执行


mysql> unlock tables;


2.3.6启动从服务器线程

在A,B上分别执行


mysql> start slave;


如果启动报错:Slave failed to initialize relay log info structure from the repository,可以先执行mysql> reset slave;指令,然后再执行2.4.3指令。

2.3.7 查看从服务状态

在A,B上分别执行


mysql> show slave status;


结果集中Slave_IO_Running: Yes ,Slave_SQL_Running: Yes,就标识从服务启动成功。


2.4 执行配置命令(故障重启)

一般情况下,不论是mysql进程突然终止还是正常关闭,双主机部署可以在重启mysql后正常同步关机期间另一台主机上执行的写操作。但某些情况例外,比如Linux主机非正常关闭。例如A,B两台服务器主主同步,A服务器因掉电直接停机,A修复故障开机后,A机能正常同步B机的数据,但B机无法正常同步A机数据(B无法同步A对实际生产不会造成任何影响,因为此时客户端写操作都是在B上执行),A机上也没有Binlog Dump线程(见2.6.1),此时在B机上重新执行slave指令即可。

重启后的机子没有Binlog Dump线程,在另一台机上执行

mysql>stop slave;


mysql>start slave;


2,重启后的机子有Binlog Dump线程,不需做任何操作。


2.5 查看同步状态
2.5.1 查看同步线程

在A,B上分别执行


mysql> show processlist;

这两个是slave服务器与master服务器同步数据的线程,一个负责读取master的Binlog Dump线程发送的语句,并且把它们拷贝到其数据目录下的中继日志(relay logs)中,另一个负责读取中继日志,然后执行它们来更新数据。time字段显示最近的一次跟新离语句执行时的间隔时间,默认单位是秒。

这个线程是master服务器的Binlog Dump线程,负责给slave服务器发送更新日志。


2.5.2 强制数据同步

如果A机数据比B机新,要想让B机完全同步A机,可按如下操作:


在A机上执行:


mysql> flush tables with read lock;


mysql> show master status


记录下show语句输出中的复制坐标的值。


在 B机上执行:(参数使用上一步获得的值)


mysql> select master_pos_wait(‘log_name’, log_pos)


这个select语句会保持运行,直到slave根据指定的日志文件和位置完成和master的同步,然后该select语句返回。


在master执行:(解除更新限制)


mysql> unlock tables;


2.6 使用GTID同步

mysql在5.6版本后,加入了gtid同步功能,使用gtid同步,可以在执行change master to指令时不用手工指定master_log_file和master_log_pos,方便日常维护。


2.6.1 GTID配置步骤:

锁定表,A,B两机上执行(首次执行)


mysql>flush tables with read lock ;


A,B两机上编辑my.cnf文件,在原有主主配置基础上增加


gtid_mode=ON


enforce_gtid_consistency=ON


指定主库


A机:


mysql>change master to master_host=’192.168.10.4′,master_port=3306,master_user=’iomsync’,master_password=’123′,master_auto_position=1;


— master_port参数不能加单引号,否则会报语法错误。


B机:


mysql>change master to master_host=’192.168.10.3′,master_port=3306,master_user=’iomsync’,master_password=’123′,master_auto_position=1;


开启slave服务,A,B都执行


mysql>start slave;


查看同步状态


mysql>show slave status;


如果Auto_Position字段值为1,则已开启gtid复制。


2.6.2 GTID同步问题

不能使用下列语句:


mysql> create table test as select * from so where 1=1;


1786 – CREATE TABLE … SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1;.


事务内部不能执行创建删除临时表语句,但可以在事务外执行,且必须设置set autocommit = 1;


在同一事务中更新事务表与非事务表将导致多个GTID分配给同一事务;

2.7 一些问题使用replicate_do_db和replicate_ignore_db时有一个隐患,跨库更新时会出错,如B机设置 replicate_do_db=db351353,在A机执行use mysql; update db351353.so set ……,B机不会同步这条update语句。网上介绍replicate_wild_do_table=db351353.%可解决此问题,但我机子上测试没解决。
MySQL官方文档Reference Manual,版本6.10关于主主复制问题描述:MySQL复制当前不支持master和slave之间的任何锁定协议来保证跨服务器分发更新的原子性。(原子性:作为事务一部分的所有步骤或者都发生,或者都不发生。) 举例来说,客户端A在master1上作了一个更新,与此同时,在这个更新传播到master2前,客户端B在master2上也作了一个与客户端A不同的更新,因此,当客户端A的更新到达master2,它要处理的表数据将不同于在master1上的,master2上的更新传播到master1上,也会面临同样的问题。这意味着,除非你确定你的更新与次序无关,或者在客户端代码中采取某些方法处理无序的更新,否则你不能把两个服务器连在一起做成双向复制。

这就决定我们在配置mycat时需将客户端的写请求分发到同一个数据库实例。


3 MYCAT安装与配置
3.1 MYCAT安装

解压mycat压缩包


#tar –xzvf Mycat-server-XXX-linux.tar.gz /usr/local/mycat


3.2 参数设置

重点介绍rule.xml schema.xml router.xml server.xml log4j.xml


3.2.1 rule.xml

分片规则配置文件,mycat支持的所有分片规则都在这个文件里。定义一个规则需要两个节点,一个是tableRule,一个是function。

以一致性哈希分片为例,function节点定义了分片规则的实现类与初始化参数和分片规则的算法名称。


属性name为规则算法名称


class为规则实现类


property子节点为初始化规则的参数,使用seed count virtualBucketTimes就可初始化一致性哈希规则



0


2


160


tableRule节点定义了分片规则名(注意此处是规则名,前面的function节点的name属性是算法名)rule子节点指定用来分片的数据库表字段和分片算法名,也就是前面的function节点的name属性。




id


murmur



任何自定义的分片规则也可以这样配置

3.2.2 server.xml

此文件用来配置mycat全局参数


节点


druidparser,指定SQL解析器,默认是fdbparser,经测试druidparser效率更高

1,指定mycat自动序列号生成方式。0:在借助本地文件生成序列号,1:借助数据库生成序列号,更多信息请参考Mycat in action

8066指定mycat服务端口号,mycat通过这个端口接收数据库客户端的访问请求。


另外还有一个9066端口没有出现在配置文件中,用来接收mycat监控命令、查询mycat运行状况、重新加载配置文件等。更多信息请参考Mycat in action。

节点


name属性指定mycat用户名


password


database_name


false


可以有多个user节点。

3.2.3 router.xml

都配置成了一样的,而且都与要连接的数据库名保持一致 –>




dataNodeName


queryNode



3.2.4 schema.xml


其中checkSQLschema表明是否检查并过滤SQL中包含schema的情况,如逻辑库为 TESTDB,则可能写为select * from TESTDB.aaa,此时会自动过滤TESTDB,SQL变为select * from aaa,若不会出现上述写法,则可以关闭属性为false



primaryKey=”pk”


dataNode=”dataNodeName”


rule=”sharding-by-murmur”


authIncrement=”true”>






dataHost是节点主机名,在下面要出现的dataHost定义


database数据库名


–>



writeType=”0” dbType=”mysql” dbDriver=”native>



maxCon minCon分别是连接到物理数据库的最大最小连接数


balance 0表示不开启读写分离,1 表示除主写入节点外其它节点都参与读操作,2表示所有节点都参与写入操作


writetype 0表示当一个写节点失效,会自动切换到另一个,1表示随机分配写节点,如果某个写节点宕机,不影响服务,如果其恢复,会自动重新加入写节点集群。


dbType指定数据库类型


dbDriver只有两个取值,分别是native和jdbc。native为mycat自带驱动,只支持mysql,jdbc为使用jdbc实现连接数据库,指定什么数据库的jdbc驱动就可以访问什么数据库,更灵活,但效率不如native

可以有多个dataHost节点


–>


select 1







3.3 启动和使用MyCat
3.3.1 启动MyCat

注意:一定要先启动数据库,再启动mycat,否则mycat中配置的主从机顺传会混乱


# mycat console


3.3.2 访问MyCat

访问MyCat同访问MySQL的方式完全相同, 常用访问方式如下:


mysql –h 127.0.0.1 –u test –p test -P8066 –DTESTDB


3.3.3 自测

可以自己编写一些语句进行测试,看是否按规则进行分配。


4 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/24 #virtual server


192.168.10.253/24 #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


}


}


}


5 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请求和应答。


6 总体测验
6.1 网络架构图

主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的备用库。


6.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 6.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


6.4 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);

查询mysql2的db351353,看是否和mysql1的db351353一样。


查询mysql1的db352354,看是否和mysql2的db352354一样。


6.5 MYCAT测试
6.5.1 配置mycat主从策略和分片策略

按so表本地网水平分片,351和353两个地市的放到节点dn1,352和354两个地市的放到节点dn2。


6.5.1.1 schema.xml内容:

红色字体为注释,实际文件里没有,注意dn2节点的主从连接库顺序,按说明应该配在前面的写入数据库为主数据库,后面的写入数据库为从数据库,但dn2节点却正好相反,原因待查。

<?xml version=”1.0″?>







rule=”sharding-by-intfile”>





writeType=”0″ dbType=”mysql” dbDriver=”native”>


select user()




password=”123″>


–>




password=”123″/>



writeType=”0″ dbType=”mysql” dbDriver=”native”>


select user()



password=”xf4851213″>


–>




password=”123″/>



6.5.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.


–>






id


func1





user_id


func1





local_net_id –按本地网分片


hash-int






id


rang-long






id


mod-long






id


murmur




0


2


160



weightMapFile


节点的权重,没有指定权重的节点默认是1。以properties文件的格式填写,以从0开始到count-1的整数值也就是节点索引为key,以节点权重值为值。所有权重值必须是正整数,否则以1代替 –>



/etc/mycat/bucketMapPath


用于测试时观察各物理节点与虚拟节点的分布情况,如果指定了这个属性,会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件,没有默认值,如果不指定,就不会输出任何东西 –>




partition-hash-int.txt




autopartition-long.txt





3



8


128




6.5.1.3 partition-hash-int内容:

红色字体为注释,实际文件里没有。


351=0–分配到dn1节点


352=1 –分配到dn2节点


353=0


354=1

6.5.2 测试mycat连接

客户机navicat打开mycat1连接,能看到一个数据库TESTDB,数据库中能查到db351353和db352354两个数据库的所有数据。mycat2连接亦同。

6.5.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的数据,以免影响剩余的测试。


6.5.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为从数据库,不会重新变为主数据库。

6.5.5 测试mycat节点容错

测试目的:mycat1和mycat2单个节点宕掉,不影响客户机使用数据库。


测试方法:客户机navicat使用keepalived连接,能正常查询和增删改数据。在mycat1上执行#mycat stop;客户机keepalived连接仍能正常使用。mycat1上执行#mycat console,mycat2上执行#mycat stop,客户机keepalived连接能正常使用。mycat2上执行#mycat console,客户机keepalived连接能正常使用,且数据请求路由和keepalived所配lb_algo策略相同。

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台