企业架构MySQL，mycat读写分离

企业架构MySQL
1、master配置
mysql > grant replication slave on *.* to'slave'@'192.168.17.%'identified by'123456';
vim /etc/my.cnf
配置文件内容
[mysqld]
log-bin = mysql-index
server-id = 1
制作一个完整的备份
mysqldump -uroot -p'123456' --alldatabases > /root/mysql.sql
查看master上的bin-log及其position位置
mysql > show master status;
2、slave配置
①恢复备份文件
如果slave和master的数据本身就是相同的，可以不选择备份和导入
vim /etc/my.cnf
[mysqld]
server-id=2
③配置slave replication
mysql > stop slave;
mysql> change master to
master_host='192.168.17.101',
master_user='slave1',
master_password='123456,
master_log_file='mysql-bin.000002',
master_log_pos=333;
mysql > start slave;
mysql > show slave status\G;

mycat读写分离
[root@localhost ~]# tar -xf jdk-8u192-linux-x64_\(1\).tar.gz 
[root@localhost ~]# tar -xf Mycat-server-1.6.5-release-20180122220033-linux_\(1\).tar.gz 
[root@localhost ~]# cp -r jdk1.8.0_192/ /usr/local/jdk
[root@localhost ~]# cp -r mycat/ /usr/local/
[root@localhost ~]# sed -i '$aexport JAVA_HOME=/usr/local/jdk' /etc/profile
[root@localhost ~]# source /etc/profile
[root@localhost ~]# sed -i '$aexport PATH=$PATH:$JAVA_HOME/bin' /etc/profile
[root@localhost ~]# source /etc/profile
[root@localhost ~]# java -version
java version "1.8.0_192"
Java(TM) SE Runtime Environment (build 1.8.0_192-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.192-b12, mixed mode)
[root@localhost ~]# javac -version
javac 1.8.0_192
[root@localhost ~]# /usr/local/mycat/bin/mycat console //启动mycat
[root@localhost ~]# ls /usr/local/mycat/conf/
autopartition-long.txt      index_to_charset.properties  rule.xml                              server.xml
auto-sharding-long.txt      log4j2.xml                   schema.xml                            sharding-by-enum.txt
[root@localhost ~]# vim /usr/local/mycat/conf/server.xml
 93         <user name="root" defaultAccount="true">   //user name=可修改
 94                 <property name="password">root1234</property>  //可修改
 95                 <property name="schemas">test</property>  //可修改
 96 
 97                 <!-- 表级 DML 权限设置 -->
 98                 <!--            
 99                 <privileges check="false">
100                         <schema name="TESTDB" dml="0110" >
101                                 <table name="tb01" dml="0000"></table>
102                                 <table name="tb02" dml="1111"></table>
103                         </schema>
104                 </privileges>           
105                  -->
106         </user>
107 <!--  //加注释
108         <user name="user">
109                 <property name="password">user</property>
110                 <property name="schemas">TESTDB</property>
111                 <property name="readOnly">true</property>
112         </user>
113 -->   //加注释
[root@localhost ~]# vim /usr/local/mycat/conf/schema.xml 
 <!--1.名称为真实数据库名称，添加一个-->
  5         <schema name="test" dataNode="dn1" checkSQLschema="false" sqlMaxLimit="100"> //修改
  6         </schema>
  7         <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
  8                 /> -->
  9         <dataNode name="dn1" dataHost="localhost1" database="test" /> //修改
 10 <!--    <dataNode name="dn2" dataHost="localhost1" database="db2" />
 11         <dataNode name="dn3" dataHost="localhost1" database="db3" />--> //注释
20                 <writeHost host="hostM1" url="192.168.1.40:3306" user="zhangmin" //修改
 21                                    password="root1234"> //修改
 22                         <!-- can have multi read hosts -->
 23                         <readHost host="hostS2" url="192.168.1.41:3310" user="zhangmin" password="root1234" /> //修改
 24                 </writeHost>
 25         <!--    <writeHost host="hostS1" url="localhost:3316" user="root"
 26                                    password="123456" />--> //注释
[root@localhost ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@localhost ~]# netstat -lnput | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      1957/java           
MySQL主：
[root@localhost bin]# ./mysql -h192.168.1.60 -P8066 -uzhangmin -proot1234