准备环境

节点 IP
db1 192.168.100.10
db2 192.168.100.30
mycat 192.168.100.50

配置过程

  1. # 主机名
  2. [root@localhost ~]# hostnamectl set-hostname mycat
  3. [root@localhost ~]# su
  6. # 配置hosts文件
  7. [root@mycat ~]# vim /etc/hosts
  8. 192.168.100.50 mycat
  9. 192.168.100.10 db1
  10. 192.168.100.30 db2
  12. # 配置本地yum源
  13. [root@mycat ~]# mv /etc/yum.repos.d/* /media/
  14. [root@mycat ~]# vim /etc/yum.repos.d/local.repo
  15. [mariadb]
  16. name=mariadb
  17. baseurl=file:///opt/gpmall-repo
  18. gpgcheck=0
  19. enabled=1
  20. [centos]
  21. name=centos
  22. baseurl=file:///opt/centos
  23. gpgcheck=0
  24. enabled=1
  26. [root@mycat ~]# mount /opt/CentOS-7-x86_64-DVD-1511.iso /opt/centos/
  27. mount: /dev/loop0 is write-protected, mounting read-only
  29. # 安装jdk
  30. [root@mycat ~]# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
  31. [root@mycat ~]# java -version
  32. openjdk version "1.8.0_222"
  33. OpenJDK Runtime Environment (build 1.8.0_222-b10)
  34. OpenJDK 64-Bit Server VM (build 25.222-b10, mixed mode)
  36. # db1、db2节点授权
  37. mysql> grant all privileges  on *.* to root@'%' identified by "000000";
  38. Query OK, 0 rows affected (0.00 sec)
  40. # 安装mycat
  41. [root@mycat ~]# tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
  42. [root@mycat ~]# chmod -R 777 /usr/local/mycat
  43. [root@mycat ~]# echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile
  44. [root@mycat ~]# source /etc/profile
  46. # 修改配置文件
  47. [root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
  48. <?xml version="1.0"?>
  49. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  50. <mycat:schema xmlns:mycat="http://io.mycat/">
  51. <schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>
  52. <dataNode name="dn1" dataHost="localhost1" database="test" />
  53. <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" dbDriver="native" writeType="0" switchType="1"  slaveThreshold="100">
  54.     <heartbeat>select user()</heartbeat>
  55.     <writeHost host="hostM1" url="192.168.100.10：3306" user="root" password="000000">
  56.         <readHost host="hostS1" url="192.168.100.50:3306" user="root" password="000000" />
  57.     </writeHost>
  58. </dataHost>
  59. </mycat:schema>
  61. # 80行左右修改
  62. [root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml
  64. <property name="password">000000</property>
  65. <property name="schemas">USERDB</property>
  67. # 最下面删除
  70. [root@mycat ~]# /usr/local/mycat/bin/mycat start 
  71. Starting Mycat-server...
  72. [root@mycat ~]# netstat -ntlp
  73. Active Internet connections (only servers)
  74. Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
  75. tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1095/sshd           
  76. tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      2221/master         
  77. tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      12070/java          
  78. tcp6       0      0 :::9066                 :::*                    LISTEN      12070/java          
  79. tcp6       0      0 :::59094                :::*                    LISTEN      12070/java          
  80. tcp6       0      0 :::22                   :::*                    LISTEN      1095/sshd           
  81. tcp6       0      0 ::1:25                  :::*                    LISTEN      2221/master         
  82. tcp6       0      0 :::1984                 :::*                    LISTEN      12070/java          
  83. tcp6       0      0 :::8066                 :::*                    LISTEN      12070/java          
  84. tcp6       0      0 :::38310                :::*                    LISTEN      12070/java          
  87. # 测试
  88. [root@mycat mycat]#  yum install -y MariaDB-client
  89. [root@mycat mycat]# mysql -h127.0.0.1 -P8066 -uroot -p000000
  90. MySQL [(none)]> show databases
  91.     -> ;
  92. +----------+
  93. | DATABASE |
  94. +----------+
  95. | USERDB   |
  96. +----------+
  97. 1 row in set (0.002 sec)
  99. MySQL [(none)]> use USERDB
  100. Database changed
  101. MySQL [USERDB]> create table company(id int not null primary key,name varchar(50),addr varchar(255));
  102. Query OK, 0 rows affected (0.180 sec)
  104. MySQL [USERDB]> show tables;
  105. +----------------+
  106. | Tables_in_test |
  107. +----------------+
  108. | company        |
  109. +----------------+
  110. 1 row in set (0.002 sec)
  112. MySQL [USERDB]> insert into company values(1,"facebook","usa");
  113. Query OK, 1 row affected (0.040 sec)
  115. MySQL [USERDB]> select * from company
  116.     -> ;
  117. +----+----------+------+
  118. | id | name     | addr |
  119. +----+----------+------+
  120. |  1 | facebook | usa  |
  121. +----+----------+------+
  122. 1 row in set (0.048 sec)
  124. [root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p000000 -e 'show  @@datasource;'
  125. +----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
  126. | DATANODE | NAME   | TYPE  | HOST           | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
  127. +----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
  128. | dn1      | hostM1 | mysql | 192.168.100.10 | 3306 | W    |      0 |   10 | 1000 |     116 |         0 |          2 |
  129. | dn1      | hostS1 | mysql | 192.168.100.30 | 3306 | R    |      0 |    7 | 1000 |     115 |         4 |          0 |
  130. +----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+

至此，读写分离配置结束