[系统运维]MyCat2双主双从读写分离

双主双从+mycat读写分离

思路

1,先完成主从复制

一共四次同步
2.配置mycat完成读写分离

一、环境准备

1.mysql安装(4台都要)

mysql8.0安装：(本次搭建使用8.0)

wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
yum install mysql80-community-release-el7-7.noarch.rpm  -y
yum install mysql-community-server -y

mysql5.7安装：

 #选择默认安装的版本  默认的是8.0 版本
 #安装 YUM 管理工具包，此包提供了 yum-config-manager 命令工具
yum install mysql80-community-release-el7-7.noarch.rpm  -y
yum install yum-utils -y
yum-config-manager  --disable  mysql80-community
yum-config-manager  --enable  mysql57-community
yum install mysql-community-server -y
yum repolist enabled | grep mysql # 检查安装是否正确

2.初始化

#启动mysqld 服务端
systemctl start mysqld
#查看是否启动
systemctl status mysqld

初始化 MySQL服务器初始化（从MySQL 5.7开始）
在 MySQL 服务器初始启动时，如果服务器的数据目录为空，则会发生以下情况：
MySQL 服务器已初始化。
在数据目录中生成SSL证书和密钥文件。
安装并启用该 validate_password 插件。
将创建一个超级用户
帐户’root’@‘localhost’。并会设置超级用户的密码，将其存储在错误日志文件/var/log/mysqld.log中

grep 'temporary password' /var/log/mysqld.log

mysql -uroot -p'.1Y!IhLxqyi1'

# 出现以下错误的原因是需要修改初始化密码
1. show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

2. alter user   root@localhost   identified  by  '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

#太过简单的密码会失败，因为不满足密码复杂度的要求

# 修改密码
1. alter user   root@localhost   identified  by  'MySQL@666';
Query OK, 0 rows affected (0.00 sec)
# 刷新权限
2.  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 要设置比较简单的密码就需要取消密码复杂度，编辑 /etc/my.cnf配置文件, 在 [mysqld]配置块儿中添加如下内容(企业生成环境不建议使用这种配置)
plugin-load=validate_password.so 
validate-password=OFF

远程连接

远程登录还需要授权远程登录 Mysql默认不允许远程登录，我们需要设置关闭selinux或者防火墙，不关防火墙就开放3306端口；

# 允许本地用户链接
1. grant all privileges on *.* to root@localhost identified by '密码';
 Query OK, 0 rows affected, 1 warning (0.00 sec)
# 允许任意IP连接
2. grant all privileges on *.* to root@'%' identified by '密码';
 Query OK, 0 rows affected, 1 warning (0.00 sec)

添加开放3306端口的iptables规则

iptables -t filter -I INPUT -p tcp --dport 3306 -j ACCEPT

（新增说明)卸载已经安装的MySQL

//rpm包安装方式卸载
查包名：rpm -qa|grep -i mysql
删除命令：rpm -e –nodeps 包名
 
//yum安装方式下载
1.查看已安装的mysql
命令：rpm -qa | grep -i mysql
2.卸载mysql
命令：yum remove mysql-community-server-5.6.36-2.el7.x86_64
查看mysql的其它依赖：rpm -qa | grep -i mysql
3.卸载原库防止影响换版本
rm -rf /usr/lib/mysql

二、配置主从复制

master1配置

#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema
#设置需要复制的数据库 binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是 1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

slave1配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2             #新增
relay-log=mysql-relay   #新增

master2配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=3
log-bin=mysql-bin
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema
binlog_format=STATEMENT
log-slave-updates
auto-increment-increment=2
auto-increment-offset=2

slave2配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=4             #新增
relay-log=mysql-relay   #新增

**两台master需要分别为两台slave建立从机复制的账户，也应考虑两台master互为主从的情况。**
```perl
#在主机MySQL里执行授权命令
CREATE USER 'slave2'@'%' IDENTIFIED BY '123123';
GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'%'; #此语句必须执行。否则见下面。
ALTER USER 'slave2'@'%' IDENTIFIED WITH mysql_native_password BY '123123';
#查询Master1的状态 记录下两台master的状态。
show master status;

#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址', MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

验证主从复制是否生效
1.在master1上创建库和表
2.在master2上插入数据
3.分别在四个库查看是否有数据 有则配置成功没有则配置失败

三，Mycat2 读写分离配置

cd /usr/local/mycat/conf/datasources/
cp prototypeDs.datasource.json master01.datasource.json

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE", 
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"master01",           #需要修改
        "password":"123456",               
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.100.12:3306/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8", #查看连接的是否为你创建的数据库
        "user":"root",
        "weight":0

cp prototypeDs.datasource.json master02.datasource.json

 {
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE", 
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"master02",           #需要修改
        "password":"123456",               
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.100.13:3306/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8", #查看连接的是否为你创建的数据库
        "user":"root",
        "weight":0

cp prototypeDs.datasource.json slave01.datasource.json

配置如下

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE", 
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"slave01",           #需要修改
        "password":"123456",               
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.100.11:3306/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8", #查看连接的是否为你创建的数据库
        "user":"root",
        "weight":0
}

cp prototypeDs.datasource.json slave02.datasource.json

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE", 
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"slave02",       #需要修改
        "password":"123456",                
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.100.10:3306/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8", #查看连接的是否为你创建的数据库
        "user":"root",
        "weight":0
}

vim /usr/local/mycat/conf/clusters/mycluster.cluster.json #创建一个文件  

[配置如下]

{
        "clusterType":"GARELA_CLUSTER",
	"heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetry":3,
                "minSwitchTimeInterval":300,
                "slaveThreshold":0
        },
        "masters":[
                "master01","master02"    
        ],
        "replicas":["slave01","slave02"],
        "maxCon":2000,
        "name":"mycluster",        
        "readBalanceType":"BALANCE_ALL",
        "switchType":"SWITCH"
}

vim /usr/local/mycat/conf/schemas/mydb1.schema.json

{
	"customTables":{},
	"globalTables":{},
	"normalProcedures":{},
	"normalTables":{},
	"schemaName":"mydb1",
	"shardingTables":{},
	"targetName":"mycluster",
	"views":{}
}

重启mycat2

/usr/local/mycat/bin/mycat start
ss -tunlp #查看是否有端口8066
mysql -uroot -p123456 -h 192.168.100.12 -P8066 #进入mycat数据库
mysql> show databases;
+--------------------+
| `Database`         |
+--------------------+
| information_schema |
| mydb1              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

mysql> use mydb1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into mytb1 values (2,@@hostname);

查看各库的的数据情况

总结