江苏龙网

  1. 主页 > 生活百科 > >

MySQL MyCAT 读写分离实战( 二 )

MyCAT

【MySQL MyCAT 读写分离实战】通过4040代理端口插入数据 ， 该sql语句会走master ， 于是可以激活master状态：
mysql -h192.168.75.133 -umysql-proxy -p123456 -P4040 -e "create database lutixia charset utf8;"在4041管理端口 ， 再次查看：

MySQL MyCAT 读写分离实战

文章插图
2.7通过代理查询数据：#先在从库选择lutixia数据库（因为主从关系 ， 在主库创建的lutixia会同步至从库） ， 创建表格 ， 并插入数据：MariaDB [(none)]> use lutixia Database changedMariaDB [lutixia]> create table t1( id int, name varchar(20) ); Query OK, 0 rows affected (0.00 sec)MariaDB [lutixia]> insert t1 values(1,"xiaoming"); Query OK, 1 row affected (0.00 sec)通过4040代理端口查询数据 ， 该sql语句会走slave ， 于是可以激活slave状态：
# 多执行几次！[root@node4 ~]# mysql -h192.168.75.133 -umysql-proxy -p123456 -P4040 -e "select* from lutixia.t1;"+------+----------+| id| name|+------+----------+|1 | xiaoming |+------+----------+在4041管理端口 ， 再次查看：
mysql> select * from backends;+-------------+---------------------+-------+------+------+-------------------+| backend_ndx | address| state | type | uuid | connected_clients |+-------------+---------------------+-------+------+------+-------------------+|1 | 192.168.75.134:3306 | up| rw| NULL |0 ||2 | 192.168.75.135:3306 | up| ro| NULL |0 |+-------------+---------------------+-------+------+------+-------------------+2 rows in set (0.00 sec)3.基于Mycat实现读写分离：Mycat基于阿里开源的Cobar产品而研发 , 一个彻底开源的 ， 面向企业应用开发的大数据库集群 , 一个可以视为MySQL集群的企业级数据库 ， 用来替代昂贵的Oracle集群  ， MYCAT并不依托于任何一个商业公司 ，  永不收费 ， 永不闭源 ！
MySQL MyCAT 读写分离实战

文章插图
mycat:192.168.75.133master:192.168.75.134slave:192.168.75.1353.1安装mycat:# 下载mycat:wget http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042- linux.tar.gz# 解压：tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz-C /usr/local/# 安装JAVA-jdk：yum install java-1.8.0-openjdk -y# 配置mycat环境变量：echo "export PATH=/usr/local/mycat/bin:$PATH" > /etc/profile.d/mycat.sh. /etc/profile.d/mycat.sh3.2授权mycat:# 在主库执行授权信息 ， 从库会自动同步：grant all on *.* to "mycat-proxy"@"192.168.75.133" identified by "123456";3.3配置mycat:# 配置server.xml# 默认管理用户 ， 可读可写：<user name="mycat" defaultAccount="true"><property name="password">123456</property><property name="schemas">lutixiadb</property>...</user># 只读用户：<user name="user"><property name="password">user</property><property name="schemas">lutixiadb</property><property name="readOnly">true</property></user># 配置schema.xml# 设置逻辑库以及数据库节点<schema name="lutixiadb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema># 配置数据库节点对应的后端真实的数据库：<dataNode name="dn1" dataHost="localhost1" database="students" /># 配置读写库以及均衡：<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="192.168.75.134:3306" user="mycat-proxy" password="123456"><!-- can have multi read hosts --><readHost host="hostS1" url="192.168.75.135:3306" user="mycat-proxy" password="123456" /></writeHost></dataHost>3.3启动mycat:mycatstart3.4连接测试：mysql -umycat -p123456 -P8066 -h127.0.0.1 MySQL [(none)]> show databases;+-----------+| DATABASE|+-----------+| lutixiadb |+-----------+1 row in set (0.00 sec)# 可以在后端主库创建一个表 ， 继续查询表测试：MySQL [(none)]> use lutixiadb;Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changedMySQL [lutixiadb]> show tables;+--------------------+| Tables_in_students |+--------------------+| t1|+--------------------+1 row in set (0.01 sec)# 在从库插入数据 ， 继续查询：MySQL [lutixiadb]> select * from t1;+------+----------+| id| name|+------+----------+|1 | xiaowang |+------+----------+1 rows in set (0.00 sec)


推荐阅读


上一篇：Unix与Linux有什么关系？OSX从何而来？Window又是怎么回事？

下一篇：安全基线检查和配置脚本源码