MySQL读写分离
MySQL读写分离
读写分离概念
读写分离的基本原理是让主数据库处理增、删、改操作（INSERT、DELETE、UPDATE），从数据库处理查询操作（SELECT）。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
读写分离的作用
因为数据库的“写”（写100,00条数据到MySQL可能要3分钟）操作是比较耗时的，但是数据库的“读”（从MySQL读100,00条数据可能只要5秒钟）。所以读写分离可以解决数据库写入时影响查询效率的问题。
读写分离应用场景
数据库不一定要运用读写分离，当数据库使用多、更新少、查询较多的情况下会考虑使用。利用数据库主从同步，可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。例如使用memcache、分表、搜索引擎等方法。
读写分离案例实施
在拥有主从服务器基础上添加一台虚拟机部署mycat数据库中间件服务
1、规划节点
| IP | 主机名 | 节点 |
|---|---|---|
| 192.168.200.10 | lamp | 数据库集群主节点 |
| 192.168.200.20 | mycat | Mycat中间件服务节点 |
| 192.168.200.30 | lnmp | 数据库集群从节点 |
2、配置三台虚拟机的hosts和关闭防火墙
[root@lamp ~]# vim /etc/hosts //同传给全部节点
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.loca
ldomain4
::1 localhost localhost.localdomain localhost6 localhost6.loca
ldomain6
192.168.200.10 lamp
192.168.200.20 mycat
192.168.200.30 lnmp
//关闭防火墙（全部节点）
[root@lamp ~]#iptables -F
[root@lamp ~]#iptables -X
[root@lamp ~]#iptables -Z
[root@lamp ~]#iptables-save
3、查看主从服务器是否连接
lamp下：
[root@lamp ~]# mysql -uroot -p
Enter password: //输入密码
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| atlinux01.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
lnmp下：
[root@lnmp ~]# mysql -uroot -p
Enter password: //输入密码
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: atlinux01.000003
Read_Master_Log_Pos: 120
Relay_Log_File: antong-relay-bin.000005
Relay_Log_Pos: 283
Relay_Master_Log_File: atlinux01.000003
Slave_IO_Running: Yes //两个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: 120
Relay_Log_Space: 620
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: 10
Master_UUID: 6c8995bb-e951-11eb-9ef3-000c2994a838
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
ERROR:
No query specified
4、在主节点中创建数据库test，验证主从同步
主节点操作：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table company(id int not null primary key,name varchar(50),addr varchar(255));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into company values(1,"facebook","usa");
Query OK, 1 row affected (0.01 sec)
mysql> select * from company;
+----+----------+------+
| id | name | addr |
+----+----------+------+
| 1 | facebook | usa |
+----+----------+------+
1 row in set (0.00 sec)
从节点操作：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| at |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> select * from company;
+----+----------+------+
| id | name | addr |
+----+----------+------+
| 1 | facebook | usa |
+----+----------+------+
1 row in set (0.00 sec)
5、部署Mycat读写分离中间件服务
（1）安装Mycat服务
将Mycat服务的二进制软件包上传到/usr/local/src/下
[root@mycat src]# pwd
/usr/local/src
[root@mycat src]# ls
Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
将软件包解压到/use/local目录中。赋予解压后的Mycat目录权限。
[root@mycat src]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
[root@mycat src]# chmod -R 777 /usr/local/mycat/
在环境变量添加Mycat服务
[root@mycat src]# echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile
[root@mycat src]# source /etc/profile
（2）编辑Mycat的逻辑库配置文件
配置Mycat服务读写分离的schema.xml配置文件在/usr/local/mycat/conf/目录下，可以在文件中定义一个逻辑库，使用户可以通过Mycat服务管理该逻辑库对应的mysql数据库。在这里定义一个逻辑库schema，name为USERDB；该逻辑库USERDB对应数据库database为test（在部署主从数据库时已安装）；设置数据库写入节点为主节点db1；设置数据库读取节点为从节点db2。
可以直接删除原来schema.xml的内容，替换为如下。
[root@mycat src]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode
="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="test" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbTy
pe="mysql" dbDriver="native" writeType="0" switchType="1" slaveThresh
old="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.200.10:3306" user="root" pas
sword="000000">
<readHost host="hostS1" url="192.168.200.30:3306" user="root"
password="000000" />
</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。
（3）修改配置文件权限
[root@mycat src]# chown root:root /usr/local/mycat/conf/schema.xml
（4）编辑mycat的访问用户
修改/usr/local/mycat/conf/目录下的server.xml文件，访问Mycat的逻辑库为USERDB
[root@mycat src]# vim /usr/local/mycat/conf/server.xml
//在配置文件的最后部分修改以下内容
<user name="root">
<property name="password">000000</property>
<property name="schemas">USERDB</property>
</user>
//删除如下几行：
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
（5）启动Mycat服务
[root@mycat src]# /bin/bash /usr/local/mycat/bin/mycat start
[root@mycat src]# netstat -ntpl
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:32000 0.0.0.0:* LISTEN 13169/java
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 912/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1013/master
tcp6 0 0 :::40476 :::* LISTEN 13169/java
tcp6 0 0 :::1984 :::* LISTEN 13169/java
tcp6 0 0 :::8066 :::* LISTEN 13169/java
tcp6 0 0 :::43427 :::* LISTEN 13169/java
tcp6 0 0 :::9066 :::* LISTEN 13169/java
tcp6 0 0 :::22 :::* LISTEN 912/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1013/master
如果有开放8066和9066端口，则表示Mycat服务开启成功。
6、验证数据库读写分离功能
（1）用Mycat服务查询数据库信息
挂载gpmall本地yum源进行安装，自行操作
[root@mycat opt]# yum install -y MariaDB-client.x86_64
[root@mycat opt]# mysql -h127.0.0.1 -P8066 -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| USERDB |
+----------+
1 row in set (0.002 sec)
MySQL [(none)]> use USERDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [USERDB]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| company |
+----------------+
1 row in set (0.001 sec)
MySQL [USERDB]> select * from company;
+----+----------+------+
| id | name | addr |
+----+----------+------+
| 1 | facebook | usa |
+----+----------+------+
1 row in set (0.052 sec)
（2）用Mycat服务添加表数据
MySQL [USERDB]> insert into company values(2,"bastetball","usa");
Query OK, 1 row affected (0.029 sec)
MySQL [USERDB]> select * from company;
+----+------------+------+
| id | name | addr |
+----+------------+------+
| 1 | facebook | usa |
| 2 | bastetball | usa |
+----+------------+------+
2 rows in set (0.002 sec)
（3）验证Mycat服务对数据库读写操作分离
Mycat虚拟机节点使用mysql命令，通过9066端口查询对数据库读写操作的分离信息。可以看到所有的写入操作WRITE_LOAD数都在db1主数据库节点上，所有的读取操作READ_LOAD数都在db2主数据库节点上。由此可见，数据库读写操作已经分离到db1和db2节点上了。
[root@mycat opt]# mysql -h127.0.0.1 -P9066 -uroot -p000000 -e 'show @@datasource;'
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.200.10 | 3306 | W | 0 | 10 | 1000 | 88 | 0 | 1 |
| dn1 | hostS1 | mysql | 192.168.200.30 | 3306 | R | 0 | 7 | 1000 | 87 | 4 | 0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
