基于docker搭建mysql主从复制和读写分离
本次教程基于两台服务器,最好是两台独立的服务器,如果实在不行可以在一台电脑上通过设置不同的端口,来实现主从复制和读写分离。
演示资源
IP | 类型 | 主机名 |
---|---|---|
192.168.1.230 | Master | panku-230 |
192.168.1.233 | Slave | panku-233 |
以panku-230为主,panku-233为从,进行环境搭建,搭建之前,请先参考【docker教程】分别在两台服务器上创建mysql容器
1 主从复制
1.1 配置Master(主)
修改panku-230主机上的mysql配置文件,增加以下内容
[mysqld] ##保证唯一性 server-id=1000 #开启二进制日志功能,可以随便取名字 log-bin=mysql-bin
配置完毕后,重启docker容器,使配置生效。
1.2 在Master数据库创建数据同步用户
需要授予用户slave REPLICATION SLAVE权限和REPLICATION CLIENT权限,用于在主从库之间同步数据。
create user 'slave'@'%' identified by 'pankujava'; grant replication slave,replication client on *.* to 'slave'@'%';
1.3 配置Slave(从)
与配置主一样,在slave配置文件my.cnf中添加如下配置:
[mysqld] # 设置server_id,需要保证唯一性 server_id=1001 #开启二进制日志功能,以备Slave作为其他Slave的Master时使用 log-bin=mysql-slave-bin # relay_log配置中继日志 relay_log=panku-mysql-relay-bin
配置完之后重启docker容器,使配置生效。
1.4 链接Master(主)和Slave(从)
在Master进入mysql,执行 show master status;
File和Position字段的值后面将会用到,在后面的操作完成之前,需要保证Master库不能做任何操作,否则将会引起状态变化,File和Position字段的值变化。
进入Slave的mysql容器中,执行:
change master to master_host='192.168.1.230', master_user='slave', master_password='pankujava', master_port=3366, master_log_file='mysql-bin.000002', master_log_pos= 154, master_connect_retry=30;
命令说明:
master_host: master的地址
master_port: master的端口号,指的是容器的端口号
master_user:用于数据同步的用户
master_password: 用于同步的用户的密码
master_log_file:指定 Slave 从哪个日志文件开始复制数据,即上文中提到的 File 字段的值
master_log_pos:从哪个 Position 开始读,即上文中提到的 Position 字段的值
master_connect_retry:如果连接失败,重试的时间间隔,单位是秒,默认是60秒
在Slave 中的mysql终端执行show slave status \G;
用于查看主从同步状态。
正常情况下,SlaveIORunning 和 SlaveSQLRunning 都是No,因为我们还没有开启主从复制过程。使用start slave
开启主从复制过程,然后再次查询主从同步状态show slave status \G;
。
SlaveIORunning 和 SlaveSQLRunning 都是Yes,说明主从复制已经开启。此时可以测试数据同步是否成功。
注意,如果在连接主库的过程中配置错了,就执行 stop slave; 之后重新执行 change master to 命令即可。
1.5 测试主从复制
在master中创建数据库,看从库是否同步数据库。
1.6 停止主从复制
在slave库中,执行 stop slave;再创建数据库试试。
可见从库并没有同步主库的数据,如果这个时候再开启主从复制,会发生什么?
从库自动复制了,停止同步之后的数据。
2 读写分离
读写分离需要借助于中间件,用于实现将读请求发送到server1,写请求发送到server2;
另外基于中间件,可以让业务无需改动,只需要对数据库连接进行修改,由原来的直接连接MySQL库,变更为连接数据库中间件。
本次中间件选用mycat
IP | 类型 | 主机名 |
---|---|---|
192.168.1.230:3366 | 写库-只写 | panku-230 |
192.168.1.233:3366 | 读库-只读 | panku-233 |
2.1 mycat的安装
安装java后,下载文件解压即可
java安装方法,参考【centos7安装java环境】
linux:
./mycat start 启动
./mycat stop 停止
./mycat console 前台运行
./mycat install 添加到系统自动启动(暂未实现)
./mycat remove 取消随系统自动启动(暂未实现)
./mycat restart 重启服务
./mycat pause 暂停
./mycat status 查看启动状态
win:
直接运行startup_nowrap.bat,如果出现闪退,在cmd 命令行运行,查看出错原因。
启动前,一般需要修改JVM配置参数,打开conf/wrapper.conf文件,如下行的内容为2G和2048,可根据本机配置情况修改为512M或其它值。 以下配置跟jvm参数完全一致,可以根据自己的jvm参数调整。
Mycat连接测试:
测试mycat与测试mysql完全一致,mysql怎么连接,mycat就怎么连接。
推荐先采用命令行测试:
mysql -uroot -proot -P8066 -h127.0.0.1
如果采用工具连接,1.4,1.3目前部分工具无法连接,会提示database not selected,建议采用高版本,navicat测试。1.5已经修复了部分工具连接。
2.2 配置mycat
在conf/server.xml 配置文件中设置mycat的连接用户信息
<user name="root" defaultAccount="true"> <property name="password">pankujava</property> <property name="schemas">test</property> <property name="defaultSchema">test</property> <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema,不设置则为null,报错 --> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> <user name="user"> <property name="password">pankujava</property> <property name="schemas">test</property> <property name="readOnly">true</property> <property name="defaultSchema">test</property> </user> <!-- 设置远程访问 --> <firewall> <whitehost> <host host="%" user="root"/> </whitehost> <blacklist check="false"> </blacklist> </firewall>
在schema.xml中配置读写分离信息
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 数据库配置,与server.xml中的数据库对应 --> <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="db_node"></schema> <!-- 分片配置 --> <dataNode name="db_node" dataHost="db_host" database="test" /> <!-- 物理数据库配置 --> <dataHost name="db_host" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="192.168.1.230:3366" user="root" password="pankujava"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.1.233:3366" user="root" password="pankujava" /> </writeHost> </dataHost> </mycat:schema>
配置说明:
sqlMaxLimit 配置默认查询数量 database 为真实数据库名 balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost 上。 balance="1",全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1 ->S1 , M2->S2,并且 M1 与 M2 互为主备),正常情况下, M2,S1,S2 都参与 select 语句的负载均衡。 balance="2",所有读操作都随机的在 writeHost、 readhost 上分发。 balance="3", 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力,注意 balance=3 只在 1.4 及其以后版本有, 1.3 没有。 writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties . writeType="1",所有写操作都随机的发送到配置的 writeHost。 writeType="2",没实现。 switchType=-1 表示不自动切换 switchType=1 默认值,自动切换 switchType=2 基于MySQL 主从同步的状态决定是否切换
2.3 启动mycat
#启动 ./mycat start mysql -uroot -p123456 -P8066 -h127.0.0.1 #停止 ./mycat stop #查看mycat启动状态 ./mycat status # 看到 Mycat-server is running 证明服务启动成功如果启动失败查看 wrapper.log中的内容,进行定位错误 #常见错误 ERROR: Could not write pid file /home/program/mycat/logs/mycat.pid: No such file or directory #解决方法,只需要在mycat目录下创建logs目录再重新启动即可
连上之后跟普通的MySQL一模一样
#mycat 默认端口未8066,管理端口为9066 mysql -uroot -P8066 -h127.0.0.1
2.4 navicat 连接
使用ssh连接方式,具体截图如下
2.5 验证读写分离
验证方式,可将conf/log4j2.xml文件中的日志等级,调整为debug,之后分别查询和新增数据,看访问的数据库主机是哪个。
另外一种验证方式,是通过修改读库的数据来实现的具体步骤如下:
-
先停止主从复制
-
修改读节点数据
-
执行查询,看返回的结果是否是修改后的数据,如果是则证明读走的 是读库,否则证明mycat配置有问题
3 常见错误
mysqld: File './mysql-bin.index' not found
如果修改文件权限没有效果,那么就是selinux的问题,
setenforce 0 getenforce
4 设置mycat 开机自启
第一种方法:
1、建立软连接
ln -s /home/program/mycat/bin/mycat /etc/init.d/mycat
2、加入开机启动
chkconfig --add mycat
3、查看mycat状态
service mycat status
4、启动mycat
service mycat start
5、停止mycat
service mycat stop
解决主从同步问题
查看从库状态
show slave status\G;
找到如下信息
Relay_Log_File: cul-tourism-0003-relay-bin.000271 Relay_Log_Pos: 251303215
查看从库执行慢的操作
show relaylog events in 'cul-tourism-0003-relay-bin.000271' from 251303215 limit 1 ;
Pos为251303215的event如下
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info | | cul-tourism-0003-relay-bin.000271 | 251303215 | Anonymous_Gtid | 1 | 251303067 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
卡在这个SET @@SESSION.GTID_NEXT= 'ANONYMOUS' ,不知道是啥,把这个event跳过
stop slave ; set global sql_slave_skip_counter=1; start slave;
再次查看从库状态
show slave status\G;
发现偏移量不断增大,落后量越来越小,说明在不断缩小和主库的差距。
Relay_Log_Pos: 489820012 Seconds_Behind_Master: 1548752