mycat读写分离
- 准备环境
虚拟主机:Centos7.5版本,IP地址为
192.168.1.10
192.168.1.12
192.168.1.13
192.168.1.14
192.168.1.15
192.168.1.16 - 配置一主多从mysql数据库和mycat服务器,client需安装mysql服务(如何配置自己百度)
192.168.1.10 主
192.168.1.12 从
192.168.1.13 从
192.168.1.14 从
192.168.1.15 mycat
192.168.1.16 client - 修改mycat配置文件(schema.xml)
vim /usr/local/mycat/conf/schema.xml <?xml version="1.0"?>
<mycat:schema xmlns:mycat=“http://io.mycat/”>
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="master" url="192.168.1.10:3306" user="root" password="mycat@888bruno">
<!-- can have multi read hosts -->
<readHost host="db1" url="192.168.1.11:3306" user="admin" password="mycat@888bruno" />
<readHost host="db2" url="192.168.1.12:3306" user="admin" password="mycat@888bruno" />
<readHost host="db3" url="192.168.1.13:3306" user="admin" password="mycat@888bruno" />
</writeHost>
</dataHost>
</mycat:schema>
4. 查看配置文件server.xml(摘要部分作为查看)
…
#mycat登录用户
123456 #密码可自由更改
TESTDB
…
#mycat登录用户,仅用读的权限,用户名可自由更改
user #密码可自有更改
TESTDB
true
…
- 授权用户(只在mysql主服务器操作,自动同步到从)
1).登录mysql主服务器:
]# mysql -uroot -p"密码"
2).用户授权
mysql >grant all on . to root@’%’ identified by “密码”;
mysql>grant select on . to admin@’%’ identified by “密码”;
3).刷新权限
mysql>flush privileges;
6.客户端验证读操作(client)
1).手动验证
]# mysql -uuser -puser -h192.168.1.15 -P 8066
mysql> select @@hostname;
±---------------------+
| @@hostname |
±---------------------+
| mysql-repl2 |
±---------------------+
mysql> select @@hostname;
±---------------------+
| @@hostname |
±---------------------+
| mysql-repl1 |
±---------------------+
mysql> select @@hostname;
±---------------------+
| @@hostname |
±---------------------+
| mysql-repl3 |
±---------------------+
2)for循环验证
]# for i in {1…100}
> do
> mysql -uuser -p123456 -h192.168.1.15 -P 8066 -e ‘select @@hostname’;
> done | sort | uniq -c
100 @@hostname
25 mysql-repl1
36 mysql-repl2
39 mysql-repl3
7.客户端验证写操作
]# mysql -uuser -p123456 -h192.168.1.15 -P 8066
mysql> insert into test.exam values(10) ; #test库和库内的表exam之前已经预先创建
]# for i in 11 12 13
> do
> mysql -uroot -p"123qqq...A" -h192.168.1.$i -e 'select * from db1.a';
> done
+------+
| age |
+------+
| 10 |
+------+ 如果出现数据,则验证成功。