1、读写分离介绍

  1.  对于目前单机运行MySQL服务。会导致MySQL连接数过多。最终导致mysql的宕机。因此可以使用多台MySQL服务器一起承担压力。考虑到项目中读写比例的不一致可以区分服务器进行操作。但是实现读写分离的依据为主从复制

介绍与配置 - 图1

2、主从复制原理

介绍与配置 - 图2
1、当客户端连接主服务器发送写入的SQL语句
2、主服务器将SQL语句执行。再讲对应的SQL语句存储到自己二进制日志文件中（格式为二进制格式但是本质是SQL语句）
3、从服务器中的IO线程使用特殊的授权账号读取主服务器中的二进制日志文件。存储到自己的中继日志文件中
4、再从服务器中的SQL线程读取自己的中继日志文件转换为SQL语句执行。最终数据统一
切记：在主从复制的过程中本质是同步执行写入操作对的SQL语句

基本环境

　主机名 　 镜像 　　IP
　　　MYSQL１ CentOS-7-x86_64-DVD-1511.iso 192.168.200.10
　　　MYSQL２ CentOS-7-x86_64-DVD-1511.iso 192.168.200.20
　　　mycat CentOS-7-x86_64-DVD-1511.iso 192.168.200.30

搭建

  1. [root@localhost ~]# hostnamectl set-hostname mycat
  2. [root@localhost ~]# bash
  3. [root@mycat ~]# vim /etc/hosts
  4. [root@mycat ~]# cat /etc/hosts
  5. 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
  6. ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
  7. 192.168.200.10 mysql1
  8. 192.168.200.20 mysql2
  9. 192.168.200.30 mycat
  11. //数据库集群需要安装 MariaDB 数据库服务，需要给集群虚拟机配置 Yum 安装源文件，
  12. 使用提供的 gpmall-repo 文件上传至 mycat 虚拟机的/opt 目录下，设置本地 Yum 源。
  13. 首先将/etc/yum.repo.d 目录下的文件移动到/media 下，命令如下：
  14. [root@mycat ~]# mkdir /opt/centos
  15. [root@mycat ~]# mount /dev/cdrom /opt/centos/
  16. mount: /dev/sr0 is write-protected, mounting read-only
  17. [root@mycat ~]# mv /etc/yum.repos.d/* /media/ 
  18. [root@mycat ~]# vim /etc/yum.repos.d/local.repo
  19. [root@mycat ~]# cat /etc/yum.repos.d/local.repo 
  20. [centos]
  21. name=centos
  22. baseurl=file:///opt/centos
  23. gpgcheck=0
  24. enabled=1
  25. [mariadb]
  26. name=mariadb
  27. baseurl=file:///opt/gpmall-repo
  28. gpgcheck=0
  29. enabled=1
  30. [root@mycat ~]# yum repolist
  31. Loaded plugins: fastestmirror
  32. centos                                                          | 3.6 kB  00:00:00     
  33. mariadb                                                         | 2.9 kB  00:00:00     
  34. (1/3): centos/group_gz                                          | 155 kB  00:00:00     
  35. (2/3): mariadb/primary_db                                       | 144 kB  00:00:00     
  36. (3/3): centos/primary_db                                        | 2.8 MB  00:00:00     
  37. Loading mirror speeds from cached hostfile
  38. repo id                                 repo name                                status
  39. centos                                  centos                                   3,723
  40. mariadb                                 mariadb                                    165
  41. repolist: 3,888
  42. [root@mycat ~]# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
  43. [root@mycat ~]# java -version
  44. openjdk version "1.8.0_222"
  45. OpenJDK Runtime Environment (build 1.8.0_222-b10)
  46. OpenJDK 64-Bit Server VM (build 25.222-b10, mixed mode)
  47. （5）关闭防火墙
  48. [root@mycat ~]# iptables -F
  49. [root@mycat ~]# iptables -X
  50. [root@mycat ~]# iptables -Z
  51. [root@mycat ~]# iptables-save
  52. //安装 Mycat 服务
  53. //将 Mycat 服务的二进制软件包 Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
  54. 上传到Mycat虚拟机的/root目录下，并将软件包解压到/use/local目录中。赋予解压后的Mycat
  55. 目录权限。
  56. [root@mycat ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C 
  57. /usr/local/
  58. [root@mycat ~]# chmod -R 777 /usr/local/mycat/
  59. //在/etc/profile 系统变量文件中添加 Mycat 服务的系统变量，并生效变量。
  60. [root@mycat ~]# echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile
  61. [root@mycat ~]# source /etc/profile
  62. [root@mycat ~]# vim /usr/local/mycat/conf/schema.xml 
  64. [root@mycat ~]# cat /usr/local/mycat/conf/schema.xml 
  65. <?xml version="1.0"?>
  66. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  67. <mycat:schema xmlns:mycat="http://io.mycat/">
  68. <schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" 
  69. dataNode="dn1"></schema>
  70. <dataNode name="dn1" dataHost="localhost1" database="test" />
  71. <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" 
  72. dbDriver="native" writeType="0" switchType="1" slaveThreshold="100">
  73.  <heartbeat>select user()</heartbeat>
  74.  <writeHost host="hostM1" url="192.168.200.10:3306" user="root" 
  75. password="000000">
  76.  <readHost host="hostS1" url="192.168.200.20:3306" user="root" 
  77. password="000000" />
  78.  </writeHost>
  79. </dataHost>
  80. </mycat:schema>
  82. [root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml
  83. //（4）编辑 mycat 的访问用户
  84. //修改/usr/local/mycat/conf/目录下的 server.xml 文件，修改 root 用户的访问密码与数据库，
  85. 密码设置为 000000，访问 Mycat 的逻辑库为 USERDB，命令如下。
  86. [root@mycat ~]# cat /usr/local/mycat/conf/server.xml 
  87. //在配置文件的最后部分，
  88. <user name="root">
  89. <property name="password">000000</property>
  90. <property name="schemas">USERDB</property>
  91. //然后删除如下几行：
  92. <user name="user">
  93. <property name="password">user</property>
  94. <property name="schemas">TESTDB</property>
  95. <property name="readOnly">true</property>
  96. </user>
  97. 保存并退出 server.xml 配置文件
  98. [root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat start
  99. [root@mycat ~]# netstat -nltp
  100. Active Internet connections (only servers)
  101. Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
  102. tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1409/sshd           
  103. tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1936/master         
  104. tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      3373/java           
  105. tcp6       0      0 :::9066                 :::*                    LISTEN      3373/java           
  106. tcp6       0      0 :::38890                :::*                    LISTEN      3373/java           
  107. tcp6       0      0 :::22                   :::*                    LISTEN      1409/sshd           
  108. tcp6       0      0 ::1:25                  :::*                    LISTEN      1936/master         
  109. tcp6       0      0 :::34910                :::*                    LISTEN      3373/java           
  110. tcp6       0      0 :::1984                 :::*                    LISTEN      3373/java           
  111. tcp6       0      0 :::8066                 :::*                    LISTEN      3373/java

测试

  1. 5.验证数据库集群服务读写分离功能
  2. （1）用 Mycat 服务查询数据库信息
  3. 先在 Mycat 虚拟机上使用 Yum 安装 mariadb-client 服务。
  4. [root@mycat ~]# yum install -y MariaDB-client
  5. 在 Mycat 虚拟机上使用 mysql 命令查看 Mycat 服务的逻辑库 USERDB，因为 Mycat 的
  6. 逻辑库 USERDB 对应数据库 test（在部署主从数据库时已安装），所以可以查看库中已经
  7. 创建的表 company。命令如下。
  8. [root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p000000
  9. Welcome to the MariaDB monitor. Commands end with ; or \g.
  10. Your MySQL connection id is 2
  11. Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server 
  12. (OpenCloundDB)
  13. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
  14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. MySQL [(none)]> show databases;
  16. +----------+
  17. | DATABASE |
  18. +----------+
  19. | USERDB |
  20. +----------+
  21. 1 row in set (0.001 sec)
  22. MySQL [(none)]> use USERDB
  23. Reading table information for completion of table and column names
  24. You can turn off this feature to get a quicker startup with -A
  25. Database changed
  26. MySQL [USERDB]> show tables;
  27. +----------------+
  28. | Tables_in_test |
  29. +----------------+
  30. | company |
  31. 10
  32. +----------------+
  33. 1 row in set (0.003 sec)
  34. MySQL [USERDB]> select * from company;
  35. +----+----------+------+ | id | name | addr |
  36. +----+----------+------+
  37. | 1 | alibaba | china | +----+----------+------+
  38. 1 row in set (0.005 sec)
  39. （2）用 Mycat 服务添加表数据
  40. 在 Mycat 虚拟机上使用 mysql 命令对表 company 添加一条数据(2,"basketball","usa")，添
  41. 加完毕后查看表信息。命令如下。
  42. MySQL [USERDB]> insert into company values(2,"bastetball","usa");
  43. Query OK, 1 row affected (0.050 sec)
  44. MySQL [USERDB]> select * from company;
  45. +----+------------+------+
  46. | id | name | addr |
  47. +----+------------+------+
  48. | 1 | alibaba | china |
  49. | 2 | bastetball | usa |
  50. +----+------------+------+
  51. 2 rows in set (0.002 sec)
  52. （3）验证 Mycat 服务对数据库读写操作分离
  53. 在 Mycat 虚拟机节点使用 mysql 命令，通过 9066 端口查询对数据库读写操作的分离信
  54. 息。可以看到所有的写入操作 WRITE_LOAD 数都在 mysql1 主数据库节点上，所有的读取
  55. 操作 READ_LOAD 数都在 mysql2 主数据库节点上。由此可见，数据库读写操作已经分离到
  56. mysql1 和 mysql2 节点上了。命令如下。
  57. [root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p000000 -e 'show @@datasource;'
  58. +----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
  59. | DATANODE | NAME   | TYPE  | HOST           | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
  60. +----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
  61. | dn1      | hostM1 | mysql | 192.168.200.10 | 3306 | W    |      0 |   10 | 1000 |     148 |         0 |          1 |
  62. | dn1      | hostS1 | mysql | 192.168.200.20 | 3306 | R    |      0 |    8 | 1000 |     148 |         4 |          0 |
  63. +----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+