docker exec -it mysql-master /bin/bash
mysql -uroot -proot -P8066
开始创建刚刚配置的逻辑库,逻辑表:
-- 分别在每个逻辑主机创建test_db库(master创建test_db库)
CREATE DATABASE IF NOT EXISTS test_db;
-- 连接mycat成功,在test_db库执行以下SQL语句
CREATE TABLE IF NOT EXISTS t_test (
id BIGINT(20) NOT NULL,
title VARCHAR(100) NOT NULL ,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
INSERT INTO t_test (id,title) VALUES ('1','test0001');
INSERT INTO t_test (id,title) VALUES ('2','test0002');
INSERT INTO t_test (id,title) VALUES ('3','test0003');
INSERT INTO t_test (id,title) VALUES ('4','test0004');
INSERT INTO t_test (id,title) VALUES ('5','test0005');
INSERT INTO t_test (id,title) VALUES ('6','test0006');
SELECT * FROM t_test;
CREATE TABLE IF NOT EXISTS t_task_test (
id BIGINT(20) NOT NULL,
org_code VARCHAR(100) NOT NULL ,
title VARCHAR(100) NOT NULL ,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
INSERT INTO t_task_test (id,org_code,title) VALUES ('1','W12340001','test0001');
INSERT INTO t_task_test (id,org_code,title) VALUES ('2','W12340002','test0002');
INSERT INTO t_task_test (id,org_code,title) VALUES ('3','W12340003','test0003');
INSERT INTO t_task_test (id,org_code,title) VALUES ('4','W12340004','test0004');
INSERT INTO t_task_test (id,org_code,title) VALUES ('5','W12340005','test0005');
INSERT INTO t_task_test (id,org_code,title) VALUES ('6','W12340006','test0006');
SELECT * FROM t_task_test;
【mycat系列二】Docker安装mycat并实现MySQL的读写分离
四、附录
期间遇到mysql-master 插入数据报错,但是mysql-master2正常:
--数据库中插入数据或执行sql语句时一直报下面这个错误:
Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
首先,可以先查看一下 mysql-master 数据库现在的sql_mode的值,sql语句为:
select version(), @@sql_mode;
获取结果可能如下,不同的设置查询出来的有区别:
【mycat系列二】Docker安装mycat并实现MySQL的读写分离
然后可以使用如下语句,去设置自己需要的sql_mode.
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));