docker搭建主从数据库及mycat读写分离

1、安装准备

1.1、读写分离账号

搭建mysql主从数据库(使用docker镜像分别在两台服务器上搭建数据库）
主服务器ip：192.168.235.129
从服务器ip：192.168.235.130

拉取docker5.7

docker pull mysql:5.7

查看安装的mysql

docker images

自定义配置

修改master节点配置文件

进入/home/mysql/conf.d目录，添加my.cnf文件，增加如下内容

[mysqld]
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
binlog-do-db=test_mycat

修改slave节点配置文件
进入/home/mysql/conf.d目录，添加my.cnf文件，增加如下内容

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
log_slave_updates=1
replicate-do-db=test_mycat

运行镜像

docker run --privileged=true --name mysql -p 3306:3306 -v /home/mysql/data:/var/lib/mysql -v /home/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

–name：容器名
–p：映射宿主主机端口
-v：挂载宿主目录到容器目录
-e：设置环境变量，此处指定root密码
-d：后台运行容器
–privileged：权限

查看运行的mysql容器

docker ps

进入容器内

docker exec -it $(docker ps -q)  /bin/sh

登录

mysql -uroot -p123456

查看bin-log开启状态，如果是ON，表示bin-log配置已生效

show variables like 'log_bin';

查看master状态

 SHOW MASTER STATUS; 

重置bin-log

reset master; 

输入mysql查询语句

select host,user,plugin,authentication_string from mysql.user;

如果上图的plugin不是mysql_native_password可能会导致外网访问不了

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

关闭容器

查看镜像
docker ps

关闭镜像
docker stop mysql

启动镜像
docker start mysql

添加账号

配置master写账号

#创建写账号，创建了一个用户名为write_user密码为123456的用户。
create user`write_user`@`%` identified by'123456';

#为上述用户授权 
/*给予增删改权限*/
grant create,insert,delete,update on test_mycat.* to 'write_user'@'%';  
/*刷新权限*/
flush privileges; 

#查看权限
show grants for 'write_user'@'%';

主库创建拉取binary-log的账号供从库使用

GRANT REPLICATION SLAVE,FILE ON *.* TO 'master_to_slave'@'192.168.235.130' IDENTIFIED BY '123456';

配置slave读账号

```shell
#创建读账号，创建了一个用户名为read_user密码为123456的用户。
create user`read_user`@`%` identified by'123456';

#为上述用户授权
/*给予查询权限*/
grant select on test_mycat.* to 'read_user'@'%';  
/*刷新权限*/
flush privileges; 

#查看权限
show grants for 'read_user'@'%';

slave连接master

mysql> CHANGE MASTER TO MASTER_HOST='192.168.235.129',//主服务器ip  
    -> MASTER_USER='master_to_slave',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='master-bin.000001',//master服务器日志文件，主服务器mysql使用SHOW MASTER STATUS语句  
    -> MASTER_LOG_POS=0;//日志的开始位置

开启slave

start slave;

查看slave状态

能查看到这两个为yes则成功

Slave_IO_Running: Yes #表示slave的日志读取线程开启
Slave_SQL_Running: Yes #表示SQL执行线程开启

SHOW SLAVE STATUS;

测试master新增表及表数据是否会同步到slave节点

初始状态，数据库并不存在表，现在master创建test表

用写账号新增test_to_slave表，然后能看到在slave节点同样生成

额外脚本（快速启动or重启mysql服务）

#!/bin/bash
echo "正在停止所有docker里面的容器mysql..."
docker stop $(docker ps -q);
echo "停止成功,正在删除容器ing..."
docker rm mysql;

name=mysql;
port=3306;
fromData=/home/mysql/data;
toData=/var/lib/mysql;
fromConf=/home/mysql/conf.d;
toConf=/etc/mysql/conf.d;
password=123456;
docker run --privileged=true --name $name -p $port:$port -v $fromData:$toData -v $fromConf:$toConf -e MYSQL_ROOT_PASSWORD=$password -d mysql:5.7;
docker ps;
docker exec -it $(docker ps -q) /bin/sh;

1.2、jdk安装

jdk解压
命令行进入/usr/local/java文件夹
cd /usr/local/java
解压：tar zxvf 压缩包名称 （例如：tar zxvf jdk-8u191-linux-x64.tar.gz）后面的名称可以只输入一个tar zxvf j，然后使用
删除压缩包：rm -f 压缩包名称 （例如 rm -f jdk-8u191-linux-x64.tar.gz）
环境变量配置
编辑/etc/profile文件
vi /etc/profile

export JAVA_HOME=/usr/local/java/jdk1.8.0_191
export CLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export PATH=$PATH:$JAVA_HOME/bin

生效命令：source /etc/profile

验证安装成功与否
java -version

1.3、mycat安装

2、业务方案（目前仅涉及读写分离）

2.1、添加读写分离配置

修改/usr/local/mycat/conf/server.xml文件，修改root账号信息(也可以自定义)
包含系统配置信息。
system 标签：例如字符集、线程数、心跳、分布式事务开关等等。
user 标签：配置登录用户和权限。

<user name="root" >
		<property name="password">123456</property>
		<property name="schemas">test_mycat</property>
	</user>

标签解释：
user：访问用户
password：密码
schemas：schema.xml中对应的配置id

修改/usr/local/mycat/conf/schema.xml文件，添加智呈数据库读写配置
（schema.xml 包括逻辑库、表、分片规则、分片节点和数据源，可以定义多个 schema。）

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
		<schema name="test_mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" />
		<dataNode name="dn1" dataHost="localhost1" database="test_mycat" />
		<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
				  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
			<heartbeat>select user()</heartbeat>
			<!-- can have multi write hosts -->
			<writeHost host="192.168.235.129" url="192.168.235.129:3306" user="write_user"
					   password="123456">
				<readHost host="192.168.235.130" url="192.168.235.130:3306" user="read_user" password="123456" />
			</writeHost>
		</dataHost>
</mycat:schema>

其中：

schema 标签用于定义MyCat 实例中的逻辑库，MyCat 可以有多个逻辑库，每个逻辑库都有自己的相关配置。可以使用schema 标签来划分这些不同的逻辑库。
dataNode 标签定义了MyCat 中的数据节点，也就是我们通常说所的数据分片。一个dataNode 标签就是一个独立的数据分片。
dataHost标签直接定义了具体的数据库实例、读写分离配置和心跳语句。其中有几个重要的属性：

balance属性
负载均衡类型，目前的取值有3 种：
balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost 上。
balance="1"，全部的readHost 与stand by writeHost 参与select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1 与M2 互为主备)，正常情况下，M2,S1,S2 都参与select 语句的负载均衡。
balance="2"，所有读操作都随机的在writeHost、readhost 上分发。
balance="3"，所有读请求随机的分发到wiriterHost 对应的readhost 执行，writerHost 不负担读压
力，注意balance=3 只在1.4 及其以后版本有，1.3 没有。

writeType 属性
负载均衡类型，目前的取值有3 种：
writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个
writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
writeType="1"，所有写操作都随机的发送到配置的writeHost，1.5 以后废弃不推荐。

3、mycat服务启动测试

启动mycat

 cd /usr/local/mycat/bin
 ./mycat start

查询user_test表

插入数据

注意事项:

schema:name要和database一样，否则会报查询权限不足的问题，如下，为不一样的时候报错。

4、智呈项目连接mycat测试

添加连接mycat配置类

package com.dossen.store.workbench.application.dao.dossensw.support.datasource;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * @program: dossen-store-workbench-api
 * @description: 测试mycat
 * @author: wuzhuohui
 * @create: 2020-04-28 10:22
 **/
@Component
@ConfigurationProperties(prefix = "workbench.datasource.mycat")
public class MycatTestDataSourceConfig {
}

添加测试mycat数据源类

package com.dossen.store.workbench.application.dao.dossensw.support.dataaccess;

import com.dossen.store.workbench.application.dao.dossensw.support.datasource.DossenSwDataSourceConfig;
import com.dossen.store.workbench.application.dao.dossensw.support.datasource.MycatTestDataSourceConfig;
import com.dossen.web.utils.BeanUtils;

import javax.sql.DataSource;

/**
 * @program: dossen-store-workbench-api
 * @description: 测试mycat数据源
 * @author: wuzhuohui
 * @create: 2020-04-28 10:24
 **/
public class MycatDataSourceFactory {

    public static DataSource getDossenSwDataSource(){
        MycatTestDataSourceConfig config = BeanUtils.getBean(MycatTestDataSourceConfig.class);
        return config.dataSource();
    }
}

添加mycat数据源依赖

package com.dossen.store.workbench.application.dao.dossensw.support.dataaccess;

import com.dossen.data.dataaccess.spring.MySqlDataAccess;

/**
 * @program: dossen-store-workbench-api
 * @description:mycat数据源依赖
 * @author: wuzhuohui
 * @create: 2020-04-28 10:42
 **/
public class MycatDataSourceRepositoryBase<T> extends MySqlDataAccess<T> {
    public MycatDataSourceRepositoryBase(){
        super(MycatDataSourceFactory.getDossenSwDataSource());
    }
}

添加dao实现类

package com.dossen.store.workbench.application.dao.test_shardingjdbc;

import com.dossen.data.query.DataParameter;
import com.dossen.store.workbench.api.models.entity.user.Module;
import com.dossen.store.workbench.application.dao.datasource.ZhiChengDataSourceRepositoryBase;
import com.dossen.store.workbench.application.dao.dossensw.support.dataaccess.MycatDataSourceFactory;
import com.dossen.store.workbench.application.dao.dossensw.support.dataaccess.MycatDataSourceRepositoryBase;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

/**
 * 模块Dao
 * @author WuSJ
 * @date 2019/5/7
 */
@Component
@Lazy
public class UserTestDaoImpl extends MycatDataSourceRepositoryBase<UserTest> implements UserTestDao {
    @Autowired
    private UserTestDao userTestDao;





    /**
     * 根据模块编码列表获取模块信息
     */
    @Override
    public List<UserTest> getByUserTest(int id){
        List<DataParameter> dataParameters = new ArrayList<>();
        dataParameters.add(DataParameter.init("id",id));
        StringBuilder query = new StringBuilder();
        query.append("SELECT * " +
                " FROM user_test m  " +
                " WHERE m.id = :id ");
        List<UserTest> dataList = userTestDao.queryList(query.toString(),dataParameters);
        return dataList;
    }

    /**
     * 根据模块编码列表获取模块信息
     */
    @Override
    public int insertUserTest(UserTest request){
        int insert = userTestDao.insert(request);
        return insert;
    }
}

添加实体类

package com.dossen.store.workbench.application.dao.test_shardingjdbc;

import com.dossen.data.query.annotation.Key;
import com.dossen.data.query.annotation.Table;

/**
 * @program: dossen-store-workbench-api
 * @description:
 * @author: wuzhuohui
 * @create: 2020-04-20 16:01
 **/
@Table("user_test")
public class UserTest {

    @Key
    private int id;
    private String name;
    private int age;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

添加dao

package com.dossen.store.workbench.application.dao.test_shardingjdbc;

import com.dossen.data.dataaccess.spring.DataAccess;
import com.dossen.store.workbench.api.models.entity.user.Module;

import java.util.List;

/**
 * @Author wuzhuohui
 * @Description 
 * @Date 10:44 2020/4/28
 * @Param 
 * @return 
 **/
public interface UserTestDao extends DataAccess<UserTest> {

    List<UserTest> getByUserTest(int id);

    int insertUserTest(UserTest request);
}

mycat测试类

import com.alibaba.fastjson.JSONObject;
import com.dossen.Application;
import com.dossen.store.workbench.application.dao.test_shardingjdbc.UserTest;
import com.dossen.store.workbench.application.dao.test_shardingjdbc.UserTestDao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

/**
 * @program: dossen-store-workbench-api
 * @description: 测试mycat
 * @author: wuzhuohui
 * @create: 2020-04-28 10:39
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = Application.class)
public class MycatTest {
        @Autowired
        private UserTestDao userTestDao;

        @Test
        public void insertUserTest() throws Exception {
            UserTest request = new UserTest();
            request.setName("dossen");
            request.setAge(15);
            int i = userTestDao.insertUserTest(request);
            System.out.println(i);
        }

        @Test
        public void queryUserTest() throws Exception {
            List<UserTest> byUserTest = userTestDao.getByUserTest(4);
            System.out.println(JSONObject.toJSONString(byUserTest));
        }
}

插入数据

success!

查询插入数据

success!

报错分析,如果出现:java.sql.SQLNonTransientConnectionException: CLIENT_PLUGIN_AUTH is required

代表引入的mysql-connector-java.jar版本太高了,降到5版本即可