基于mycat的mysql_基于MYCAT中间件实现MYSQL读写分离

/* 主节点：192.168.47.101 从节点：192.168.47.102 */ /*mycat为同一网段客户端*/

/* 修改主节点基础配置文件 */ /* */

[mysqld]

server-id=101

log-bin

/*重启服务 */

#systemctl restart mariadb

/*创建从节点的账号 */

CREATE USER 'xie'@'192.168.47.102' IDENTIFIED BY '123456';

grant replication slave on *.* to xie@'192.168.47.102';

/*创建mycat*/ 能操作的账号

mysql> create database mycat;

mysql>GRANT ALL ON *.* TO 'root'@'192.168.8.10' IDENTIFIED BY '123456' WITH

GRANT OPTION;

mysql> flush privileges;

/* 查看二进制日志位置*/

show master status ;

+----------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+----------------------+----------+--------------+------------------+

| localhost-bin.000001 | 729 | | |

+----------------------+----------+--------------+------------------+

/*从节点 */

/* 修改主节点基础配置文件 */ /* */

[mysqld]

server-id=102

read-only=on

relay_log=/data/relay-log

#log-bin

/*重启服务 */

#systemctl restart mariadb

/*创建从属性 */

CHANGE MASTER TO MASTER_HOST='192.168.47.101',

MASTER_USER='xie', MASTER_PASSWORD='123456', MASTER_PORT=3306,

MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=729;

/* 开启从复制 */

start slave ;

/* 查看开启详情 */

show slave status \G

...

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

/* 出现可忽略的错误解决方法 */

/*修改全局变量*/

stop slave;

sql_slave_skip_counter = 数量;

start slave;

/*修改配置文件 填入错误id 或者all*/

[mysqld]

slave_skip_errors=1007|ALL

/*下载安装mycat*/

yum -y install java

/*确认安装成功*/

java -version

/*安装wget*/

yum -y install wget

/*wget mycat包*/

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-

linux.tar.gz

/*创建安装目录*/

mkdir /apps

/*解压到安装目录*/

tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /app

/* 环境变量 */

echo 'export PATH=/apps/mycat/bin:$PATH' /etc/profile.d/mycat.sh

. /etc/profile.d/mycat.sh

/*修改配置文件*/

vim /app/mycat/conf/server.xml

/*连接Mycat的用户名*/

123456 /*连接Mycat的密码*/

/*修改分离策略*/

vim /app/mycat/conf/schema.xml

balance="1"

/*开启并查看是否成功*/

mycat start

cat /app/mycat/logs/wrapper.log | grep successfully

/*连接测试*/

mysql -uroot -pmagedu -h127.0.0.1 -P8066 -DTESTDB

mysql> show databases;

+----------+

| DATABASE |

+----------+

| TESTDB | //只能看一个虚拟数据库

+----------+

mysql> use TESTDB;

mysql> create table t1(id int);

MySQL> select @@server_id;

/*查看数据库的日志，发现修改在主服务器，读取在从服务器*/

set global general_log=on;