1. MySQL读写分离概念：
- MYSQL读写分离的原理其实就是让Master数据库处理事务性增、删除、修改、更新操作（CREATE、INSERT、UPDATE、DELETE），而让Slave数据库处理SELECT操作，MYSQL读写分离前提是基于MYSQL主从复制，这样可以保证在Master上修改数据，Slave同步之后，WEB应用可以读取到Slave端的数据。
1.1 读写分离实现方式：
实现MYSQL读写分离可以基于第三方插件，也可以通过开发修改代码实现，具体实现的读写分离的常见方式有如下四种：
- Amoeba读写分离；
- MySQL-Proxy读写分离；
- Mycat读写分离；
- 基于程序读写分离(效率很高，实施难度大，开发改代码)；
Amoeba是阿里08年开源的以MySQL为底层数据存储，并对WEB、APP应用提供MySQL协议接口的proxy。它集中地响应WEB应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行，基于此可以实现负载均衡、读写分离、高可用性等需求。
Mysql-Proxy是MySQL官方提供的mysql中间件服务，支持无数客户端连接，同时后端可连接若干台Mysql-Server服务器，MYSQL-Proxy自身基于MySQL协议，连接MYSQL-Proxy的客户端无需修改任何设置， 跟正常连接MYSQL Server没有区别，无需修改程序代码。
Mycat是基于阿里12年开源的cobar开发的一个数据库中间件，在架构体系中是位于数据库和应用层之间的一个组件，并且对于应用层是透明的，它可实现读写分离，分库分表。
基于Mycat实现读写分离：
- Mycat基于阿里开源的Cobar产品而研发 , 一个彻底开源的，面向企业应用开发的大数据库集群 , 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群 ，MYCAT并不依托于任何一个商业公司， 永不收费，永不闭源 ！
- mycat:192.168.2.10
- master:192.168.2.20
- slave:192.168.2.30
- slave:192.168.2.40
1. 安装mycat:
下载mycat:
[root@localhost ~]# wget http://dl.mycat.org.cn/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
解压mycat:
[root@localhost ~]# tar -xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin boost etc games include lib lib64 libexec mycat mysql sbin share src
安装java-jdk:
[root@localhost ~]# yum -y install java-1.8.0-openjdk
配置mycat环境变量：
[root@localhost ~]# echo "export PATH=/usr/local/mycat/bin:$PATH" >/etc/profile.d/mycat.sh
[root@localhost ~]# . /etc/profile.d/mycat.sh
[root@localhost ~]# echo $PATH
/usr/local/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
2. 配置一主多从：
2.1. 配置master服务器：
修改配置文件：
在mysqld指令块下添加以下内容：
server-id=1
log-bin=ys.bin
重启服务：
[root@localhost ~]# systemctl restart mysqld
查看log_bin和sql_log_bin是否均为on
[root@localhost ~]# mysql -uroot -p1234
mysql> show variables like "%log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
| sql_log_bin | ON |
+---------------+-------+
2 rows in set (0.01 sec)
授权从库：
mysql> grant replication slave on *.* to 'ys'@'192.168.2.%' identified by '1234';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
查看master状态：
mysql> show master status;
+-----------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------+
| ys.000001 | 154 | | | |
+-----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.2. 配置slave端：
修改配置文件；
[root@localhost ~]# vim /etc/my.cnf
在mysqld指令块添加以下内容：
192.168.2.30修改如下：
server-id=2
192.168.2.40修改如下：
server-id=3
重启服务：
[root@localhost ~]# systemctl restart mysqld
指定master：
[root@localhost ~]# mysql -uroot -p1234
mysql> change master to
-> master_user="ys",
-> master_password="1234",
-> master_host="192.168.2.20",
-> master_log_file="ys.000001",
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看slave状态；
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.10
Master_User: ys
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ys.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 313
Relay_Master_Log_File: ys.000001
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: 154
Relay_Log_Space: 524
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: b7283fc3-a614-11ea-a339-000c292d0b9e
Master_Info_File: /data/mysql57/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
注意：上面我没有单独讲哪个slave配置什么意思两个slave都配置
3. 授权mycat：
在主库执行授权信息，从库自动会同步：
mysql> grant all privileges on *.* to 'mycat-proxy'@'192.168.2.10' identified by '1234';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
4. 配置mycat:
# 配置server.xml
[root@localhost ~]# cd /usr/local/mycat/
[root@localhost mycat]# cp conf/server.xml{,.bak} 备份server.xml文件
[root@localhost mycat]# vim conf/server.xml
# 默认管理用户，可读可写：
.....
<user name="mycat" defaultAccount="true">
<property name="password">1234</property>
<property name="schemas">ysdb</property>
</user>
#只读用户：
<user name="user">
<property name="password">user</property>
<property name="schemas">ysdb</property>
<property name="readOnly">true</property>
</user>
....
#配置schema.xml：
[root@localhost mycat]# cp conf/schema.xml{,.bak}
[root@localhost mycat]# vim conf/schema.xml
#设置逻辑库以及数据库节点
......
<schema name="ysdb" checkSQLschema="false" sqlMaxLimit="100" dataNote="dn1">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="ys" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
负载均衡类型：
（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 没有。
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.2.20:3306" user="mycat-proxy"
password="1234">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.2.30:3306" user="mycat-proxy" password="1234" />
<readHost host="hostS2" url="192.168.2.40:3306" user="mycat-proxy" password="1234" />
</writeHost>
<writeHost host="hostM2" url="192.168.2.30:3306" user="mycat-proxy"
password="1234" />
</dataHost>
5. 启动mycat:
[root@localhost mycat]# mycat start
Starting Mycat-server...
6. 连接测试：
[root@localhost mycat]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1805/master
tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 4233/java
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1707/mysqld
tcp 0 0 0.0.0.0:58382 0.0.0.0:* LISTEN 1464/rpc.statd
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1068/rpcbind
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1454/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1805/master
tcp6 0 0 :::1984 :::* LISTEN 4233/java
tcp6 0 0 :::8066 :::* LISTEN 4233/java
tcp6 0 0 :::38566 :::* LISTEN 4233/java
tcp6 0 0 :::33161 :::* LISTEN 4233/java
tcp6 0 0 :::9066 :::* LISTEN 4233/java
tcp6 0 0 :::37708 :::* LISTEN 1464/rpc.statd
tcp6 0 0 :::111 :::* LISTEN 1068/rpcbind
tcp6 0 0 :::22 :::* LISTEN 1454/sshd
[root@localhost mycat]# mysql -umycat -p123456 -P8066 -h127.0.0.1
mysql> show databases;
+----------+
| DATABASE |
+----------+
| ysdb |
+----------+
1 row in set (0.01 sec)
在后端创建一个表格并插入数据：
mysql> use ys;
Database changed
mysql> create table t1(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t1 values(1,'bob');
在前端查看：
mysql> use ysdb;
Database changed
mysql> show tables;
+--------------+
| Tables_in_ys |
+--------------+
| t1 |
+--------------+
1 row in set (0.01 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | bob |
+------+------+
1 row in set (0.00 sec)
在从库往表格里面添加数据：
mysql> insert into t1 values(2,'xiaoming');
Query OK, 1 row affected (0.00 sec)
在mycat服务器查询：
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | bob |
+------+------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | bob |
| 2 | xiaoming |
+------+----------+
2 rows in set (0.01 sec)
从这里可以看到查询走的从库，但是走哪个从库是随机的
在主库查不到数据，通过代理可以查到，即可验证读写分离成功。
7. 报错解决：
mysql> show tables;
ERROR 1184 (HY000): Invalid DataSource:0
-
可能是后端节点出现了问题，也有可能是代理端无法连上后端节点导致：
-
可以先在代理端直接用授权用户名和密码登录后端数据库测试连接问题：
[root@localhost macat]# mysql -umycat-proxy -h192.168.75.134 -p123456
ERROR 1129 (HY000): Host ‘localhost’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’
- 可以看到因为多次错误，代理端服务器被锁定了，所以也会出现上面的报错：
- 在后端主库执行如下命令：
[root@localhost macat]# mysqladmin flush-hosts
再次测试，一般问题就能解决。 - mycat启动不起来查看日志如下：
STATUS | wrapper | 2021/02/20 23:47:55 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/02/20 23:47:55 | Launching a JVM...
INFO | jvm 1 | 2021/02/20 23:47:55 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
INFO | jvm 1 | 2021/02/20 23:47:56 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2021/02/20 23:47:56 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2021/02/20 23:47:56 |
INFO | jvm 1 | 2021/02/20 23:47:56 |
INFO | jvm 1 | 2021/02/20 23:47:56 | WrapperSimpleApp: Encountered an error running main: java.lang.ExceptionInInitializerError
INFO | jvm 1 | 2021/02/20 23:47:56 | java.lang.ExceptionInInitializerError
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.MycatStartup.main(MycatStartup.java:53)
INFO | jvm 1 | 2021/02/20 23:47:56 | at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO | jvm 1 | 2021/02/20 23:47:56 | at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
INFO | jvm 1 | 2021/02/20 23:47:56 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
INFO | jvm 1 | 2021/02/20 23:47:56 | at java.lang.reflect.Method.invoke(Method.java:498)
INFO | jvm 1 | 2021/02/20 23:47:56 | at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:240)
INFO | jvm 1 | 2021/02/20 23:47:56 | at java.lang.Thread.run(Thread.java:748)
INFO | jvm 1 | 2021/02/20 23:47:56 | Caused by: io.mycat.config.util.ConfigException: org.xml.sax.SAXParseException; lineNumber: 6; columnNumber: 60; Attribute "database" is required and must be specified for element type "dataNode".
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:121)
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.config.loader.xml.XMLSchemaLoader.<init>(XMLSchemaLoader.java:78)
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.config.loader.xml.XMLSchemaLoader.<init>(XMLSchemaLoader.java:82)
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.config.ConfigInitializer.<init>(ConfigInitializer.java:76)
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.config.MycatConfig.<init>(MycatConfig.java:72)
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.MycatServer.<init>(MycatServer.java:166)
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.MycatServer.<clinit>(MycatServer.java:109)
INFO | jvm 1 | 2021/02/20 23:47:56 | ... 7 more
INFO | jvm 1 | 2021/02/20 23:47:56 | Caused by: org.xml.sax.SAXParseException; lineNumber: 6; columnNumber: 60; Attribute "database" is required and must be specified for element type "dataNode".
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(ErrorHandlerWrapper.java:203)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.error(ErrorHandlerWrapper.java:134)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:396)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:327)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:284)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.addDTDDefaultAttrsAndValidate(XMLDTDValidator.java:1171)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.handleStartElement(XMLDTDValidator.java:1917)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.emptyElement(XMLDTDValidator.java:763)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanStartElement(XMLDocumentFragmentScannerImpl.java:1339)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(XMLDocumentFragmentScannerImpl.java:2784)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:602)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:505)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:842)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:771)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:141)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.parsers.DOMParser.parse(DOMParser.java:243)
INFO | jvm 1 | 2021/02/20 23:47:56 | at com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderImpl.parse(DocumentBuilderImpl.java:339)
INFO | jvm 1 | 2021/02/20 23:47:56 | at javax.xml.parsers.DocumentBuilder.parse(DocumentBuilder.java:121)
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.config.util.ConfigUtil.getDocument(ConfigUtil.java:115)
INFO | jvm 1 | 2021/02/20 23:47:56 | at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:111)
INFO | jvm 1 | 2021/02/20 23:47:56 | ... 13 more
STATUS | wrapper | 2021/02/20 23:47:58 | <-- Wrapper Stopped
解决方法：可以看得出来日志提示schema.xml文档中第六行的第60列出现错误，可以查看一下。
扫一扫
4399
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
