基于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
<user name="root"> /*连接Mycat的用户名*/
<property name="password">123456</property> /*连接Mycat的密码*/
/*修改分离策略*/
vim /app/mycat/conf/schema.xml
balance="1"
<dataNode name="dn1" dataHost="localhost1" database="mycat" />
<writeHost host="host1" url="192.168.47.101:3306" user="root" password="123456">
<readHost host="host2" url="192.168.47.102:3306" user="root" password="123456" />
</writeHost>
/*开启并查看是否成功*/
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;