Mysql的AB及读写
第1章 Mysql的AB配置
1.1 master配置
1.2 slave配置
1.2.1 192.168.13.190
1.2.2 192.168.13.191
1.2.3 192.168.13.192
1.2.4 192.168.13.193
1.2.4 192.168.13.189
第2章 读写分离
2.1 安装mycat
2.1.1 server.xml
2.1.1 schema.xml
2.2 启动mycat
2.2.1 启动mycat
2.2.2 mycat日志
2.3 登录mycat相关问题
2.4 小结
第3章 mysql-proxy
3.1 安装mysql-proxy
3.2 配置mysql-proxy
3.3 启动mysql-proxy和查看
第4章 集群
4.1 版本介绍
4.2 MHA
4.2.1 ssh互信
4.2.1 ABB(一主二从)/AABB(二主多从)架构搭建
4.2.1 MHA安装(管理节点MHA_manager)
4.2.2 MHA安装(其他节点安装MHA_node)
4.2.3 MHA配置
4.2.4 MHA互信和环境检查
4.2.5 启动及相关日志文件
4.2.6 MHA测试
4.3 集群小结
第5章 错误集锦
5.1 主从报错
5.2 集群报错
第1章 Mysql的AB配置
当MySQL的版本是5.5或高于5.5的时候,其中大部分的内容相似
主要是5.5之后不再支持master打头的参数
主|从
IP地址
允许同步的用户
读|写
mysql版本
Mycat版本
JDK版本
操作系统版本
master
192.168.13.189
slave
write
mysql-8.0.13
CentOS 7.4.1708
192.168.13.192
copy
write
mysql-8.0.13
Mycat-server-1.6.6.1
1.8.0_191
CentOS 7.4.1708
slave
192.168.13.190
slave
read
mysql-8.0.13
CentOS 7.4.1708
192.168.13.191
slave
read
mysql-8.0.13
CentOS 7.4.1708
192.168.13.193
copy
read
mysql-8.0.13
CentOS 7.4.1708
1.1master配置
Master:
在/etc/my.cnf 添加:
server-id = 1
log-bin=/home/mysql/logs/binlog/bin-log(若报错则不加路径,如下)
log-bin=bin-log
max_binlog_size = 500M
binlog_cache_size = 128K
binlog-do-db = adb
binlog-ignore-db = mysql
log-slave-updates
expire_logs_day=2(在centos7.3上易报错,若报错,可不加)
binlog_format="MIXED"
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
server-id = 1 #服务器标志号,注意在配置文件中不能出现多个这样的标识,如果出现多个的话mysql以第一个为准,一组主从中此标识号不能重复。
log-bin=/home/mysql/logs/binlog/bin-log #开启bin-log,并指定文件目录和文件名前缀。
max_binlog_size = 500M #每个bin-log最大大小,当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
binlog_cache_size = 128K #日志缓存大小
binlog-do-db = adb #需要同步的数据库名字,如果是多个,就以此格式在写一行即可。
binlog-ignore-db = mysql #不需要同步的数据库名字,如果是多个,就以此格式在写一行即可。
log-slave-updates #当Slave从Master数据库读取日志时更新新写入日志中,如果只启动log-bin而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。
expire_logs_day=2 #设置bin-log日志文件保存的天数,此参数mysql5.0以下版本不支持。
binlog_format=”MIXED” #设置bin-log日志文件格式为:MIXED,可以防止主键重复。
接着重启MySQL:/etc/init.d/mysqld restart
再进入MySQL:mysql -u root -p123456
创建backup用户:
创建一个用于让从数据库连接的用户(以前那种方式不能用了)
mysql> CREATE USER 'copy'@'%' IDENTIFIED WITH mysql_native_password BY 'tqw961110';(密码强度可能不够会报错,XINyang3009@@)或下面一个
mysql>CREATE USER 'slave'@'%' IDENTIFIED BY 'XINyang3009@@';
mysql>grant all on *.* to 'copy'@'%' identified by "10jqka@123"
mysql>flush privileges #刷新授权表
mysql> show master status;
Master的操作就完了
【server_id千万不要一样,否则会报错】
mysql> show master status;【192.168.13.192】
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 610 | mysql | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show master status;【192.168.13.189】
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 3490 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.2slave配置步骤
在/etc/my.cnf 添加:
server_id=2
replicate-do-db=database1 ------- 要同步的数据库的名字
replicate-ignore-db=mysql ------ ---被忽略的数据库
接着重启MySQL:/etc/init.d/mysqld restart
再进入MySQL:mysql -u root -p123456
同步执行此操作:
mysql> change master to
-> master_host='2.2.2.2',
-> master_user='backup',
-> master_password='123456',
-> master_log_file='bin_log.000003', -- master上记录的日志文件
-> master_log_pos=120; -- master上记录的日志文件位置
Query OK, 0 rows affected, 2 warnings (0.06 sec)
同步开启:start slave;
同步关闭:stop slave;
最后开启同步 start slave即可
1.2.1 192.168.13.190
mysql> change master to
-> master_host='192.168.13.189',
-> master_user='slave',
-> master_password='XINyang3009',
-> master_log_file='mysql-bin.000006',
-> master_log_pos=3490;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.13.189
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 3490
Relay_Log_File: ybb-test-mysql-2-relay-bin.000012
Relay_Log_Pos: 3704
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mysql
Slave_IO_Running: Yes(必须是yes)
Slave_SQL_Running: Yes(必须是yes)
Replicate_Do_DB: mysql
1.2.2 192.168.13.191
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.13.189
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 3490
Relay_Log_File: ybb-test-mysql-2-relay-bin.000012
Relay_Log_Pos: 3704
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mysql
Slave_IO_Running: Yes(必须是yes)
Slave_SQL_Running: Yes(必须是yes)
Replicate_Do_DB: mysql
1.2.3 192.168.13.192
1.2.4 192.168.13.193
1.2.4 192.168.13.189
至此,二主三从搭建完成。
#Slave_IO_Running:连接到主库,并读取主库的日志到本地,生成本地日志文件
#Slave_SQL_Running:读取本地日志文件,并执行日志里的SQL命令。
第2章读写分离
在这里读写分离中间件用的是mycat,部署在192.168.13.192上
2.1安装mycat
1、安装JDK,mycat依赖Java环境
2、解压Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz,同时修改环境变量 /etc/profile
[root@ybb-test-mysql-4 conf]# cat /etc/profile
3、进入配置文件目录cd ./mycat/conf
(1)server.xml是登陆mycat的用户进行配置的配置文件
(2)schema.xml 对真实数据库进行配置的配置文件
2.1.1 server.xml
[root@ybb-test-mysql-4 conf]# cat server.xml
97
99 XINyang3009@@
100 TESTDB
101
102
103
111
112
113
114 user
115 TESTDB
116
117
这里配置了两个可以来连接的用户
用户1 test 密码test给予了此用户TESTDB数据库的权限
用户2 user 密码user给予了此用户TESTDB数据库的只读权限
注意这里的TESTDB 不一定是你数据库上的真实库名.可以任意指定.只要和接下来的schema.xml的配置文件中的库名统一即可
2.1.1 schema.xml
[root@ybb-test-mysql-4 conf]# cat schema.xml
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
select user()
1、
这里TESTDB 就是我们对外声称的我们有数据库的名称 必须和server.xml中的用户指定的数据库名称一致,添加一个dataNode="dn1"是指定了我们这个库只在dn1上.没有进行分库
2、
这里只需要改database的名字db1就是你真实数据库服务上的数据库名,根据你自己的数据库名进行修改.
3、
这里只需要配置三个地方 balance="1"与writeType="0" ,switchType=”1”
a. balance 属性负载均衡类型,目前的取值有4种:
1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
2. balance="1",全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1 ->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。
3. balance="2",所有读操作都随机的在writeHost、readhost上分发。
4. balance="3", 所有读请求随机的分发到wiriterHost对应的readhost执行,writerHost不负担读压力,注意balance=3只在1.4及其以后版本有,1.3没有。
b. writeType 属性
负载均衡类型,目前的取值有 3 种:
1. writeType="0", 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个
writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties .
2. writeType="1",所有写操作都随机的发送到配置的writeHost。
3. writeType="2",没实现。
c. switchType 属性
- -1 表示不自动切换
- 1 默认值,自动切换
- 2 基于MySQL主从同步的状态决定是否切换
4、
这里是配置的我们的两台读写服务器IP地址访问端口和 访问用户的用户名和密码
2.2启动mycat
2.2.1 启动mycat
到bin目录下,执行./mycat restart
[root@ybb-test-mysql-4 bin]# ps -aux|grep mycat
root 30319 0.0 0.0 17816 744 ? Sl 15:51 0:00 /usr/local/mysql/mycat/bin/./wrapper-linux-x86-64 /usr/local/mysql/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mysql/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat
root 30321 3.5 6.5 6927816 253020 ? Sl 15:51 0:03 java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/asm-4.0.jar:lib/commons-collections-3.2.1.jar:lib/commons-lang-2.6.jar:lib/curator-client-2.11.0.jar:lib/curator-framework-2.11.0.jar:lib/curator-recipes-2.11.0.jar:lib/disruptor-3.3.4.jar:lib/dom4j-1.6.1.jar:lib/druid-1.0.26.jar:lib/ehcache-core-2.6.11.jar:lib/fastjson-1.2.12.jar:lib/guava-19.0.jar:lib/hamcrest-core-1.3.jar:lib/hamcrest-library-1.3.jar:lib/jline-0.9.94.jar:lib/joda-time-2.9.3.jar:lib/jsr305-2.0.3.jar:lib/kryo-2.10.jar:lib/leveldb-0.7.jar:lib/leveldb-api-0.7.jar:lib/libwrapper-linux-ppc-64.so:lib/libwrapper-linux-x86-32.so:lib/libwrapper-linux-x86-64.so:lib/log4j-1.2-api-2.5.jar:lib/log4j-1.2.17.jar:lib/log4j-api-2.5.jar:lib/log4j-core-2.5.jar:lib/log4j-slf4j-impl-2.5.jar:lib/mapdb-1.0.7.jar:lib/minlog-1.2.jar:lib/mongo-java-driver-2.11.4.jar:lib/Mycat-server-1.6.6.1-release.jar:lib/mysql-binlog-connector-java-0.16.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/netty-3.7.0.Final.jar:lib/netty-buffer-4.1.9.Final.jar:lib/netty-common-4.1.9.Final.jar:lib/objenesis-1.2.jar:lib/reflectasm-1.03.jar:lib/sequoiadb-driver-1.12.jar:lib/slf4j-api-1.6.1.jar:lib/univocity-parsers-2.2.1.jar:lib/velocity-1.7.jar:lib/wrapper.jar:lib/zookeeper-3.4.6.jar -Dwrapper.key=aBVwhEZHoe1K2Vpw -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=30319 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start
root 30458 0.0 0.0 112676 956 pts/2 S+ 15:53 0:00 grep --color=auto mycat
[root@ybb-test-mysql-4 bin]#netstat -anlpt|grep :*066
tcp6 0 0 :::8066 :::* LISTEN 30321/java
tcp6 0 0 :::9066 :::* LISTEN 30321/java
[root@ybb-test-mysql-4 bin]#
8066:数据端口
9066:管理端口
命令行的登陆是通过 9066 管理端口来操作。
2.2.2 mycat日志
[root@ybb-test-mysql-4 logs]# cd /usr/local/mysql/mycat/logs
warpper 日志:mycat启动,停止,添加为服务等都会记录到此日志文件,如果系统环境配置错误或缺少配置时,导致Mycat无法启动,可以通过查看warrpper.log定位具体错误原因。
mycat.log:为mycat主要日志文件,记录了启动时分配的相关buffer信息,数据源连接信息,连接池,动态类加载信息等等在log4j.xml文件中进行相关配置,如保留个数,大小,字符集,日志文件大小等。非启动状态下可以删除,启动后会自动生成该日志文件
2.3登录mycat相关问题
mysql -uroot -pXINyang3009@@ -h192.168.13.192 -P9066
[root@ybb-test-mysql-4 conf]# mysql -uroot -pXINyang3009@@ -h192.168.13.192 -P9066
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (HY000): Access denied for user 'root', because password is error
本次实验的mysql版本均为mysql-8.0.13,此故障折腾了我两三天,以上的配置查看了无数遍,后来查阅相关资料才知道目前MyCat仍主要面对MySql 5.5, 5.6, 5.7版,对最新的MySql 8尚未完全支持,需要用户对MySql 8和MyCat的配置进行一系列的修改。其中对mycat的登陆方式有改变,Mycat登录逻辑库的传统方式是:mysql -uroot -p -h127.0.0.1 -P8066 -DTESTDB,对于MySql 8,会报密码错误方式,这是由于Mysql 8的缺省加密方式已经改为caching_sha2_password,而MyCat对此尚不支持。当然可以通过修改MySQL8的加密方式使其通过,但未对MySQL8完全支持,所以会在功能上也会大打折扣,目前mycat官网并没有对MySQL8版本的详细文档,官网的文档目前对MySQL5.X依旧适用。
之前做实验之所以成功是因为我在云上也装了一台mysql 5.6,并且也是用5.6做的主从,当时做完之后没有用用mysql 8把版本做连接测试,用的是5.6版本,而此次用mysql8版本连接测试显示始终失败,还折腾两天左右,我才想到可能是版本的问题,为此我又在mysql5.6版本上重新做了一遍验证一下是否是由于版本太新导致mycat不支持从而出现的错误,于是又开始折腾很久。
1、版本简介:
OS版本
IP
读|写
MySQL版本
mycat版本
CentOS 7.2
2.2.2.10
读|写
5.6.19
Mycat-server-1.6.6.1
为尽快达到实验目的,只在一台虚机安装MySQL(时长约3h),并读写也在该主机上。
2、安装MySQL 5.6(详细过程略)
3、配置文件server.xml和schema.xml
4、mycat启动
5、登录
[root@localhost conf]# mysql -uroot -pXINyang3009@@ -h2.2.2.10 -P9066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.6.1-release-20181031195535 MyCat Server (monitor)
Copyright (c) 2000, 2014, 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>
当出现mycat版本就说明登录成功。
6、查看当前可读写的入口
mysql> show @@datasource;
+----------+--------+-------+----------+------+------+--------+------+------+---------+-----------+---
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------+------+------+--------+------+------+---------+-----------+---|dn1 | write1 | mysql | 2.2.2.10 | 3306 | W | 0 | 10 | 1000 | 442 | 2 | 1 |
| dn1 | write1 | mysql | 2.2.2.10 | 3306 | R | 0 | 8 | 1000 | 436 | 0 | 0 |
+----------+--------+-------+----------+------+------+--------+------+------+---------+-----------+---
2 rows in set (0.02 sec)
7、知识点总结
8、总结
由上可知,最新的mycat并不支持最新版本的mysql,也许能够连上去但在并未完全开放之前,相信很多mycat的功能会受限。
经过第二次试验可知,在mysql 8上线的配置并没有错,只不过是由于mycat的不支持最新的mysql版本导致,所以等mycat支持mysql 8的时候就可以生效了
2.4小结
由于mycat目前并不全面支持mysql最新版本,所以目前读写分离我选的是mysql官方推荐的mysql-proxy,功能虽小但基于mysql 8版本的过度期还是一款不错的产品。
第3章mysql-proxy
3.1 安装mysql-proxy
1、使用本地镜像安装相关环境:
yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib*
2、下载mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar
3、解压安装
4、环境变量
[root@localhost ~]# vi .bash_profile
PATH=$PATH:$HOME/bin:/mysqlprox/proxy/bin
3.2 配置mysql-proxy
mkdir /home/mysql-proxy/logs【日志文件位置】
mkdir /home/mysql-proxy/lua 【脚本位置】
cd /usr/local/mysql-proxy【mysql-proxy安装位置】
cp share/doc/mysql-proxy/rw-splitting.lua ./lua 【复制读写分离配置文件】
vi /etc/mysql-proxy.cnf【创建配置文件】
[mysql-proxy]
user=root 【运行mysql-proxy用户】
admin-username=proxy 【主从mysql共有的用户】
admin-password=123456 【用户的密码】
proxy-address=192.168.11.31:4040【mysql-proxy运行ip和端口,不加端口,默认4040】
proxy-read-only-backend-addresses=192.168.11.31 【指定后端从slave读取数据】
proxy-backend-addresses=192.168.11.34 【指定后端主master写入数据】
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua 【指定读写分离配置文件位置】
admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua 【指定管理脚本】
log-file=/var/log/mysql-proxy.log
log-level=info 【定义log日志级别】
daemon=true【以守护进程方式运行】
keepalive=true 【mysql-proxy崩溃时,尝试重启】
(1)(critical) Key file contains key “keepalive”which has a value that cannot be interpreted.
daemon=1
keepalive=1
<1> 给配置文件执行权限
chmod 660 /etc/mysql-porxy.cnf
<2> 修改读写分离配置文件
vim /usr/local/mysql-proxy/lua/rw-splitting.luaif not proxy.global.config.rwsplit
min_idle_connections = 1, 【默认超过4个连接数时,才开始读写分离,改为1】
max_idle_connections = 1, 【默认8,改为1】
3.3 启动mysql-proxy和查看
启动:/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
查看:netstat -tupln | grep 4000
这样一个读写分离就完全了
第4章 集群
目前Mysql集群用的MHA或keepalive的方式做的,但都通过VIP进行管理,若是在云上做集群会有一些隐患。和H3C云工程师进行交流了解,他们不建议我们在云上做任何的集群,因为集群有心跳检测,会与虚机的备份有冲突,当虚机进行备份时会造成心跳停止,导致集群报错,解决办法就是不使用虚机备份,使用其他方式进行备份方式。中国经济网就是采用虚机做集群但不采用虚机备份的方式。
其他备份方式与虚机备份又有区别:
(1)其他备份方式:只能备份指定的数据,不能备份虚机的相关信息。
(2)虚机备份:不仅能备份指定的数据还能备份虚机的相关配置信息,在虚机崩溃时还可以进行虚机恢复。
4.1 版本介绍
属性
IP地址
允许同步的用户
读|写
mysql版本
MHA版本
操作系统版本
master
192.168.13.189
slave
write
mysql-8.0.13
0.54
CentOS 7.4
192.168.13.190
slave
read
mysql-8.0.13
0.54
CentOS 7.4
slave
192.168.13.192
slave
read
mysql-8.0.13
0.58
CentOS 7.4
MHA_manager
192.168.13.171
mysql-8.0.13
0.58
CentOS 7.4
MHA实现机制:
(1)监控AB的状态
(2)完整的选举机制(看谁的数据跟master最接近)
(3)让一个B切换到新A
(4)保证数据的完整性(通过差异还原)
因为AB复制是异步复制,所以可能有一些数据尚没有被B拉到其relay_log中,即AB数据不一致,MHA是怎样解决这种情况的呢?
(1)mha_manager使用scp命令将A当前binlog