Mysql主从复制与读写分离配置

一、服务器规划

二、Mysql安装配置

两个节点(主-从模式)的安装

yum -y install mysql-community-server
主节点配置：
（1）修改主配置文件
vi /etc/my.cnf
……
server-id=123
log-bin=abc
……
（2）启动mysql服务
systemctl enable --now mysqld
(3)修改密码策略
mysql>set global validate_password.policy=LOW; 
mysql>set global validate_password.length=4; 
（3）创建同步账号slave：
mysql>create user 'slave'@'%' identified by '123456';
mysql>grant  replication slave on *.* to 'slave'@'%';
mysql>flush privileges;
(4)查看binlog文件名及位置：
mysql> SHOW BINARY LOG STATUS;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| abc.000001 |     861 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从节点配置：
（1）修改从配置文件
vi /etc/my.cnf
……
server-id=456
……
（2）修改数据库的配置
mysql -uroot -p
stop replica;

CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.168.105',
SOURCE_USER = 'slave',
SOURCE_PASSWORD = '123456',
SOURCE_PORT = 3306,
SOURCE_LOG_FILE = 'abc.000001',
SOURCE_LOG_POS = 861,
SOURCE_SSL = 1;

start replica;

（3）查看同步状态：
mysql> show replica status \G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.168.105
                  Source_User: slave
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: abc.000006
          Read_Source_Log_Pos: 7991
               Relay_Log_File: localhost-relay-bin.000012
                Relay_Log_Pos: 5986
        Relay_Source_Log_File: abc.000006
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes

双主模式

每个节点上：
（1）启动mysql服务systemctl enable --now mysqld
 (2)修改密码策略
mysql>set global validate_password.policy=LOW; 
mysql>set global validate_password.length=4; 
（3）创建同步账号slave：
mysql>create user 'slave'@'%' identified by '123456';
mysql>grant slave replication on *.* to 'slave'@'%';
mysql>flush privaleges;

节点1配置：
（1）修改配置文件
vi /etc/my.cnf
……
server-id=123
log-bin=abc
auto_increment_offset=1  # 自增字段起始值，与服务器A配合避免冲突
auto_increment_increment=2  # 自增字段步长
sync_binlog=1  # 每提交一次事务同步一次二进制日志到磁盘
gtid_mode=ON  # 开启全局事务标识符模式
enforce_gtid_consistency=ON  # 强制GTID一致性
……
（2）修改数据库的配置
mysql -uroot -p
stop replica;

CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.168.106',
SOURCE_USER = 'slave',
SOURCE_PASSWORD = '123456',
SOURCE_PORT = 3306,
SOURCE_AUTO_POSITION = 1,
SOURCE_SSL = 1;

start replica;
(3)查看同步状态：

节点2配置：
（1）修改配置文件
vi /etc/my.cnf
……
server-id=456
log-bin=cde
auto_increment_offset=2  # 自增字段起始值，与服务器A配合避免冲突
auto_increment_increment=2  # 自增字段步长
sync_binlog=1  # 每提交一次事务同步一次二进制日志到磁盘
gtid_mode=ON  # 开启全局事务标识符模式
enforce_gtid_consistency=ON  # 强制GTID一致性
……
（2）修改数据库的配置
mysql -uroot -p
stop replica;

CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.168.105',
SOURCE_USER = 'slave',
SOURCE_PASSWORD = '123456',
SOURCE_PORT = 3306,
SOURCE_AUTO_POSITION = 1,
SOURCE_SSL = 1;

start replica;

（3）查看同步状态：

三、Mycat安装配置

yum -y install jdk1.8
（1）解压mycat
（2）升级jdbc
把新版本的mysql-connector-j-8.4.0.jar拷贝到mycat目录下的lib中，并把原版本的mysql-connector删除。
（3）修改配置文件：
vi conf/schema.xml
———————————————————————————————————）——
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false"  sqlMaxLimit="100" dataNode="dn1">
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="ruoyi" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"
                      writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <writeHost host="hostM1" url="jdbc:mysql://192.168.168.105:3306?allowPublicKeyRetrieval=true&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="slave"
                               password="123456">
                    <readHost host="hostS1" url="jdbc:mysql://192.168.168.106:3306?allowPublicKeyRetrieval=true&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="slave" password="123456" />
            </writeHost>
    </dataHost>
</mycat:schema>
—————————————————————————————————————end—
vi conf/server.xml
把TESTDB修改为你的数据库的名称ruoyi

四、测试

在mycat上测试：
mysql -P9066 -uroot -p123456
mysql> show @@heartbeat ;