一、准备工作
1. 两台内网工作机。我是一台window、一台mac os。两台都装了版本5.7.15的mysql。版本最好一样!
2. 指定mac os主机为master、window主机为slave。在master主机装好mycat,版本5.6.29。我装在当前用户底下的Application/mycat。
/Users/用户名/Application/mycat
先备份一下mycat/conf文件夹,防止修改配置出错可以替换回来。
3. 记录下master主机ip:10.0.40.122、slave主机ip:10.0.40.176
二、做mysql的主从复制
1. 先来个参考连接https://www.cnblogs.com/gl-developer/p/6170423.html
2. mac os装的mysql,找不到my.cnf配置文件的解决方法,参考https://blog.csdn.net/jyongchong/article/details/77862819
(顺道提下在window底下mysql的配置文件是my.ini)
3. 对master主机的mysql的配置文件进行操作,进入命令行:
3.1 找到主数据库的配置文件my.cnf,我的在/etc/my.cnf,在[mysqld]部分插入如下两行:
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
3.2 重启下master主机的mysql
创建用于同步的用户账号
mysql -h127.0.0.1 -uroot -p123456 -P3306 各自的连接用户与密码不一样,按实际的输入
创建用户并授权:用户:johnson 密码:123456
mysql>create user 'johnson'@'10.0.40.176' IDENTIFIED BY '123456'; #创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'johnson'@'10.0.40.176'; #分配权限
mysql>flush privileges; #刷新权限
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
> MASTER_PASSWORD='123456',
> MASTER_LOG_FILE='mysql-bin.000003',
> MASTER_LOG_POS=73;
Master_Host: 10.0.40.122
Master_User: johnson
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 11662
Relay_Log_File: mysqld-relay-bin.000022
Relay_Log_Pos: 11765
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了,比如在主master数据库的test数据库的一张表中插入一条数据,在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效,还可以关闭slave(mysql>stop slave;),然后再修改master,看slave是否也相应修改(停止slave后,master的修改不会同步到slave),就可以完成主从复制功能的验证了。
4.4 在master主机mysql建立两个数据库,分别为yii2、yii2-1。这时slave主机的mysql也自动复制了两个相对应的数据库
4.5 进入slave主机的mysql,创建给mycat连接的用户
mysql>create user 'jackie'@'10.0.40.122' identified by '123456';
mysql>grant select,insert,update,delete on *.* to 'jackie'@'10.0.40.122'; #分配权限
mysql>flush privileges; #刷新权限
三、对mycat进行配置
1. 把原来mycat/conf/schema.xml繁杂的代码替换以下代码, 以下相对简单理解
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<table name="tb_user" dataNode="dn1,dn2" rule="mod-long"/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="yii2" />
<dataNode name="dn2" dataHost="localhost1" database="yii2-1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="127.0.0.1:3306" user="root"
password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="10.0.40.176:3306" user="jackie" password="123456" />
</writeHost>
<!-- <writeHost host="hostM2" url="10.0.40.176:3306" user="jackie"
password="123456"></writeHost> -->
</dataHost>
</mycat:schema>
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
</mycat:rule>
这个例子是对tb_user的id字段进行取模分表,在schema.xml里tb_user的数据放在两个dataNode里。所以规则函数里<property name="count">2</property>。3.创建数据表
3.1 目前mycat有两个端口:8066数据端口和9066管理端口,登录方式类似于MySQL的服务端登录。
查看Application/mycat/conf/server.xml代码,找到
这个就是连接的用户root,密码123456,进入mycat能访问到的数据库TESTDB
3.2 启动mycat: Application/mycat/bin/mycat start
停止mycat: Application/mycat/bin/mycat stop
重启mycat: Application/mycat/bin/mycat restart
3.3 打开命令终端进入mycat数据端,mycat是装在master主机上
mysql -h127.0.0.1 -uroot -p123456 -P8066
caisimingdeMac-mini:~ caisiming$ cd ~
caisimingdeMac-mini:~ caisiming$ Application/mycat/bin/mycat start
Starting Mycat-server...
caisimingdeMac-mini:~ caisiming$ mysql -h127.0.0.1 -uroot -p123456 -P8066
再新开一个命令终端进入mycat管理端
mysql -h127.0.0.1 -uroot -p123456 -P9066
3.4 切换到mycat数据端
mysql> use TESTDB;
Database changed
mysql> CREATE TABLE `tb_user` (
-> `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
-> `user_name` varchar(30) NOT NULL,
-> `user_pwd` varchar(50) NOT NULL,
-> `phone` int(11) DEFAULT NULL,
-> `email` varchar(50) DEFAULT NULL,
-> `sex` tinyint(1) NOT NULL DEFAULT '1',
-> `into_time` int(10) NOT NULL,
-> `status` tinyint(1) NOT NULL DEFAULT '1',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
到数据库yii2、yii2-1查看,同时生成了一张tb_user表。从库也相应生成表
3.5 插入5条数据
mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(1,'johnson-1','123456','1524896651');
Query OK, 1 row affected (0.07 sec)
mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(2,'johnson-2','123456','1524896651');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(3,'johnson-3','123456','1524896651');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(4,'johnson-4','123456','1524896651');
Query OK, 1 row affected (0.06 sec)
mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(5,'johnson-5','123456','1524896651');
Query OK, 1 row affected (0.01 sec)
看yii2.tb_user有两条数据,yii2-1.tb_user有三条数据。实现分库分表
4. 读写分离,在schema.xml里设置balance="1",所有读操作都发送到了当前的writeHost对应的readhost和备用的writehost上
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="127.0.0.1:3306" user="root"
password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="10.0.40.176:3306" user="jackie" password="123456" />
</writeHost>
可以看到readHost的url是连接slave主机上的,所以select操作是对从库进行的。
验证,master上的主库数据不变,对slave上的从库的数据做修改。查询一下数据select * from tb_user,看看数据是不是跟着变
5.进入mycat管理端,可以查查连接状态
caisimingdeMac-mini:~ caisiming$ mysql -h127.0.0.1 -uroot -p123456 -P9066
mysql> show @@heartbeat;
+--------+-------+-------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+-------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 127.0.0.1 | 3306 | 1 | 0 | idle | 0 | 0,0,0 | 2018-04-28 14:47:59 | false |
| hostS1 | mysql | 10.0.40.176 | 3306 | 1 | 0 | idle | 0 | 2,2,3 | 2018-04-28 14:47:59 | false |
+--------+-------+-----------
RS_CODE = 1 代表正常状态