目录
一.Mycat应用场景
Mycat适用的场景很丰富,以下是几个典型的应用场景单纯的读写分离,此时配置最为简单,支持读写分离,主从切换分表分库,对于超过1000万的表进行分片,最大支持1000亿的单表分片多租户应用,每个应用一个库,但应用程序只连接Mycat,从而不改造程序本身,实现多租户化报表系统,借助于Mycat的分表能力,处理大规模报表的统计替代Hbase,分析大数据作为海量数据实时查询的一种简单有效方案,比如100亿条频繁查询的记录需要在3秒内查询出来结果,除了基于主键的查询,还可能存在范围查询或其他属性查询,此时Mycat可能是最简单有效的选择。
二.Mycat总结
- 一个彻底开源的,面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
三.具体操作
master服务器:192.168.68.200
slave服务器:192.168.68.30
mycat服务器:192.168.68.40 ##不能有任何服务占用3306端口比如mysql
客户机:192.168.68.195
1.master服务器:192.168.68.200
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0
[root@localhost ~]# vim /etc/my.cnf
server-id = 1
log-bin=master-bin
binlog_format=MIXED
log-slave-updates=true
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# mysql -u root -p123123
mysql> grant replication slave on *.* to 'myslave'@'192.168.68.%' identified by '123456';
mysql> flush privileges;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 603 | | | |
+-------------------+----------+--------------+------------------+-------------------+
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mysqld.service ##重启数据库服务
2.slave服务器:192.168.68.30
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0
[root@localhost ~]# vim /etc/my.cnf
server-id = 2
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# mysql -u root -padmin123
mysql> change master to master_host='192.168.68.200',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
mysql> start slave;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mysqld.service ##重启数据库服务
3.mycat服务器:192.168.68.40
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0
[root@localhost ~]# yum install -y java
[root@localhost ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz ##下载软件
[root@localhost ~]# mkdir /apps
[root@localhost ~]# tar zxf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/ ##解压包
[root@localhost ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@localhost ~]# source /etc/profile.d/mycat.sh
[root@localhost ~]# mycat start
[root@localhost ~]# tail -f /apps/mycat/logs/wrapper.log
4.客户机:192.168.68.195
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0
[root@localhost ~]# mysql -uroot -p123456 -h 192.168.68.105 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)
5. mycat服务器:192.168.68.40
[root@localhost conf]# vim server.xml
45 <property name="serverPort">3306</property> <property name="managerPort">9066</property>
46 <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
47 <property name="dataNodeIdleCheckPeriod">300000</property>
48 <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
[root@localhost conf]# vim 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" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="192.168.68.200:3306" user="root" password="123456">
<readHost host="host2" url="192.168.68.30:3306" user="root" password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
[root@localhost conf]# mycat restart
[root@localhost conf]# mycat status
[root@localhost conf]# ss -natp |grep 3306
[root@localhost conf]# cat /apps/mycat/logs/wrapper.log ##successfully就说明正常
[root@localhost conf]# vim server.xml
[root@localhost conf]# vim schema.xml
6.master服务器:192.168.68.200
授权
7.客户机:192.168.68.195
8.验证
在主从服务器上
set global general_log=1;
在主服务器上
在客户端上
在数据表上输入数据
主服务器监测到
但是从服务器上没用添加数据的记录