设为首页 - 加入收藏 ASP站长网（Aspzz.Cn）- 科技、建站、经验、云计算、5G、大数据,站长网！
热搜： 手机 数据 公司
当前位置： 首页 > 服务器 > 安全 > 正文

mycat结合MySQL双主实现读写分离

发布时间：2021-01-24 09:09 所属栏目：53 来源：网络整理
导读：《mycat结合MySQL双主实现读写分离》要点： 本文介绍了mycat结合MySQL双主实现读写分离，希望对您有用。如果有疑问，可以联系我们。 作者介绍 牛恒博 ,一线运维人,擅长为中小企业快速实现运维从无到有,再到自动化.现就职于某医疗大数据公司. 服务器拓扑 服务

《mycat结合MySQL双主实现读写分离》要点：
本文介绍了mycat结合MySQL双主实现读写分离，希望对您有用。如果有疑问，可以联系我们。

作者介绍

牛恒博,一线运维人,擅长为中小企业快速实现运维从无到有,再到自动化.现就职于某医疗大数据公司.

服务器拓扑

MySQL

服务器架构

mycat结合MySQL双主实现读写分离

安装MySQL

创建mysql用户

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

安装MySQL

yum install -y libaio
cd /usr/local/src/
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
tar -zxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz 
cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3306
cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3307
chown -R mysql:mysql /data/app/mysql-3306
chown -R mysql:mysql /data/app/mysql-3307
/data/app/mysql-3306/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-3306 --datadir=/data/app/mysql-3306/data
/data/app/mysql-3307/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-3307 --datadir=/data/app/mysql-3307/data

修改my.cnf

需要修改的参数：?

  • server-id：保证每个配置文件唯一
  • 两台master的自增长ID必须不同

linux-node2

master

cat > /data/app/mysql-3306/my.cnf<<EOF
[client]
port = 3306
socket = /data/app/mysql-3306/mysql.sock
[mysqld]

port = 3306
user = mysql
server-id = 1
bind-address = 0.0.0.0
basedir = /data/app/mysql-3306
datadir = /data/app/mysql-3306/data
socket = /data/app/mysql-3306/mysql.sock
pid-file = /data/app/mysql-3306/mysql.pid
log-error = /data/app/mysql-3306/mysqld.log

skip-name-resolve
log_bin = mysql-bin
log-slave-updates
auto-increment-increment = 2 
auto-increment-offset = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
EOF
chown mysql.mysql /data/app/mysql-3306/my.cnf

slave

cat > /data/app/mysql-3307/my.cnf<<EOF
[client]
port = 3307
socket = /data/app/mysql-3307/mysql.sock
[mysqld]

port = 3307
user = mysql
server-id = 11
bind-address = 0.0.0.0
basedir = /data/app/mysql-3307
datadir = /data/app/mysql-3307/data
socket = /data/app/mysql-3307/mysql.sock
pid-file = /data/app/mysql-3307/mysql.pid
log-error = /data/app/mysql-3307/mysqld.log

skip-name-resolve
log_bin = mysql-bin


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
EOF
chown mysql.mysql /data/app/mysql-3307/my.cnf

linux-node3

master

cat > /data/app/mysql-3306/my.cnf<<EOF
[client]
port = 3306
socket = /data/app/mysql-3306/mysql.sock
[mysqld]

port = 3306
user = mysql
server-id = 2
bind-address = 0.0.0.0
basedir = /data/app/mysql-3306
datadir = /data/app/mysql-3306/data
socket = /data/app/mysql-3306/mysql.sock
pid-file = /data/app/mysql-3306/mysql.pid
log-error = /data/app/mysql-3306/mysqld.log

skip-name-resolve
log_bin = mysql-bin
log-slave-updates
auto-increment-increment = 2 
auto-increment-offset = 2

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
EOF
chown mysql.mysql /data/app/mysql-3306/my.cnf

slave

cat > /data/app/mysql-3307/my.cnf<<EOF
[client]
port = 3307
socket = /data/app/mysql-3307/mysql.sock
[mysqld]

port = 3307
user = mysql
server-id = 22
bind-address = 0.0.0.0
basedir = /data/app/mysql-3307
datadir = /data/app/mysql-3307/data
socket = /data/app/mysql-3307/mysql.sock
pid-file = /data/app/mysql-3307/mysql.pid
log-error = /data/app/mysql-3307/mysqld.log

skip-name-resolve
log_bin = mysql-bin


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
EOF
chown mysql.mysql /data/app/mysql-3307/my.cnf

启动MySQL

启动服务

linux-node2和linux-node3都执行如下命令

touch /data/app/mysql-3306/mysqld.log && chown mysql.mysql /data/app/mysql-3306/mysqld.log
sed -i 's#/usr/local/mysql#/data/app/mysql-3306#g' /data/app/mysql-3306/bin/mysqld_safe 
/data/app/mysql-3306/bin/mysqld_safe --defaults-file=/data/app/mysql-3306/my.cnf --basedir=/data/app/mysql-3306 --datadir=/data/app/mysql-3306/data --user=mysql &


touch /data/app/mysql-3307/mysqld.log && chown mysql.mysql /data/app/mysql-3307/mysqld.log
sed -i 's#/usr/local/mysql#/data/app/mysql-3307#g' /data/app/mysql-3307/bin/mysqld_safe 
/data/app/mysql-3307/bin/mysqld_safe --defaults-file=/data/app/mysql-3307/my.cnf --basedir=/data/app/mysql-3307 --datadir=/data/app/mysql-3307/data --user=mysql &

检查端口

ss -lntup |egrep '3306|3307'
tcp ? ?LISTEN ? ? 0 ? ? ?80 ? ? ? ? ? ? ? ? ? ? *:3306 ? ? ? ? ? ? ? ? ?*:* ? ? ?users:(("mysqld",19973,22))
tcp ? ?LISTEN ? ? 0 ? ? ?80 ? ? ? ? ? ? ? ? ? ? *:3307 ? ? ? ? ? ? ? ? ?*:* ? ? ?users:(("mysqld",20537,22))

配置双主

配置主从

linux-node2

master

cd /data/app/mysql-3306/
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> CREATE USER 'repl'@'192.%' IDENTIFIED BY 'mysql';
Query OK,0 rows affected (0.05 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.%';
Query OK,0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | ? ? ?613 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

slave

cd /data/app/mysql-3307/
./bin/mysql -uroot -p -S mysql.sock -P 3307
mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.12',? ?-> ? ? ? ? ? ? ? ? ?MASTER_PORT=3306,? ?-> ? ? ? ? ? ? ? ? ?MASTER_USER='repl',? ?-> ? ? ? ? ? ? ? ? ?MASTER_PASSWORD='mysql',? ?-> ? ? ? ? ? ? ? ? ?MASTER_LOG_FILE='mysql-bin.000001',? ?-> ? ? ? ? ? ? ? ? ?MASTER_LOG_POS=613;
Query OK,0 rows affected,2 warnings (0.04 sec)

mysql> start slave;
Query OK,0 rows affected (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
 ? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event
 ? ? ? ? ? ? ? ? ?Master_Host: 192.168.56.12
 ? ? ? ? ? ? ? ? ?Master_User: repl
 ? ? ? ? ? ? ? ? ?Master_Port: 3306
 ? ? ? ? ? ? ? ?Connect_Retry: 60
 ? ? ? ? ? ? ?Master_Log_File: mysql-bin.000001
 ? ? ? ? ?Read_Master_Log_Pos: 613
 ? ? ? ? ? ? ? Relay_Log_File: linux-node2-relay-bin.000002
 ? ? ? ? ? ? ? ?Relay_Log_Pos: 320
 ? ? ? ?Relay_Master_Log_File: mysql-bin.000001
 ? ? ? ? ? ? Slave_IO_Running: Yes
 ? ? ? ? ? ?Slave_SQL_Running: Yes

linux-node3

master

cd /data/app/mysql-3306/
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> CREATE USER 'repl'@'192.%' IDENTIFIED BY 'mysql';
Query OK,0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | ? ? ?613 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

slave

cd /data/app/mysql-3307/
./bin/mysql -uroot -p -S mysql.sock -P 3307
mysql> 
CHANGE MASTER TO MASTER_HOST='192.168.56.13',? ? ? ? ? ? ? ? MASTER_PORT=3306,? ? ? ? ? ? ? ? MASTER_USER='repl',? ? ? ? ? ? ? ? MASTER_PASSWORD='mysql',? ? ? ? ? ? ? ? MASTER_LOG_FILE='mysql-bin.000001',? ? ? ? ? ? ? ? MASTER_LOG_POS=613;
Query OK,0 rows affected (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
 ? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event
 ? ? ? ? ? ? ? ? ?Master_Host: 192.168.56.13
 ? ? ? ? ? ? ? ? ?Master_User: repl
 ? ? ? ? ? ? ? ? ?Master_Port: 3306
 ? ? ? ? ? ? ? ?Connect_Retry: 60
 ? ? ? ? ? ? ?Master_Log_File: mysql-bin.000001
 ? ? ? ? ?Read_Master_Log_Pos: 613
 ? ? ? ? ? ? ? Relay_Log_File: linux-node2-relay-bin.000002
 ? ? ? ? ? ? ? ?Relay_Log_Pos: 320
 ? ? ? ?Relay_Master_Log_File: mysql-bin.000001
 ? ? ? ? ? ? Slave_IO_Running: Yes
 ? ? ? ? ? ?Slave_SQL_Running: Yes

配置双主

master的binlog位置

linux-node2 master端

d /data/app/mysql-3306/
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | ? ? ?613 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

linux-node3 master端

cd /data/app/mysql-3306/
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | ? ? ?613 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

linux-node2 master配置跟linux-node3 master同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.13',? ? ? ? ? ? ? ? MASTER_LOG_POS=613;
mysql> start slave;
Query OK,0 rows affected (0.03 sec)

mysql> show slave status\G
*************************** 1. row ***************************
 ? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event
 ? ? ? ? ? ? ? ? ?Master_Host: 192.168.56.13
 ? ? ? ? ? ? ? ? ?Master_User: repl
 ? ? ? ? ? ? ? ? ?Master_Port: 3306
 ? ? ? ? ? ? ? ?Connect_Retry: 60
 ? ? ? ? ? ? ?Master_Log_File: mysql-bin.000001
 ? ? ? ? ?Read_Master_Log_Pos: 613
 ? ? ? ? ? ? ? Relay_Log_File: linux-node2-relay-bin.000002
 ? ? ? ? ? ? ? ?Relay_Log_Pos: 320
 ? ? ? ?Relay_Master_Log_File: mysql-bin.000001
 ? ? ? ? ? ? Slave_IO_Running: Yes
 ? ? ? ? ? ?Slave_SQL_Running: Yes ? ? ? ?

linux-node3 master配置跟linux-node2 master同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.12',0 rows affected (0.00 sec)

mysql> 
mysql> show slave status\G
*************************** 1. row ***************************
 ? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event
 ? ? ? ? ? ? ? ? ?Master_Host: 192.168.56.12
 ? ? ? ? ? ? ? ? ?Master_User: repl
 ? ? ? ? ? ? ? ? ?Master_Port: 3306
 ? ? ? ? ? ? ? ?Connect_Retry: 60
 ? ? ? ? ? ? ?Master_Log_File: mysql-bin.000001
 ? ? ? ? ?Read_Master_Log_Pos: 613
 ? ? ? ? ? ? ? Relay_Log_File: linux-node3-relay-bin.000002
 ? ? ? ? ? ? ? ?Relay_Log_Pos: 320
 ? ? ? ?Relay_Master_Log_File: mysql-bin.000001
 ? ? ? ? ? ? Slave_IO_Running: Yes
 ? ? ? ? ? ?Slave_SQL_Running: Yes

验证

从linux-node2上验证

在linux-node2 master上创建数据

mysql> create database test;
Query OK,1 row affected (0.01 sec)

mysql> use test;
Database changed

mysql> create table temp(id int,name varchar(64));
Query OK,0 rows affected (0.11 sec)

mysql> insert into temp values(1,'aaa');
Query OK,1 row affected (0.28 sec)

mysql> CREATE TABLE temp2(id INT PRIMARY KEY ?NOT NULL AUTO_INCREMENT,nname VARCHAR(64));
Query OK,0 rows affected (0.01 sec)

mysql> insert into temp2(nname) values('bbb');
Query OK,1 row affected (0.01 sec)

mysql> select * from test.temp;
+------+------+
| id ? | name |
+------+------+
| ? ?1 | aaa ?|
+------+------+
1 row in set (0.01 sec)

在linux-node2 slave上查看数据

mysql> select * from test.temp;
+------+------+
| id ? | name |
+------+------+
| ? ?1 | aaa ?|
+------+------+
1 row in set (0.00 sec)

在linux-node3 master上查看数据

mysql> select * from test.temp;
+------+------+
| id ? | name |
+------+------+
| ? ?1 | aaa ?|
+------+------+
1 row in set (0.00 sec)

在linux-node3 slave上查看数据

mysql> select * from test.temp;
+------+------+
| id ? | name |
+------+------+
| ? ?1 | aaa ?|
+------+------+
1 row in set (0.00 sec)

从linux-node3上验证

在linux-node3 master上创建数据

mysql> use test;

mysql> insert into temp2(nname) values('ddd');
Query OK,1 row affected (0.02 sec)
mysql> ?insert into temp2(nname) values('fff');
Query OK,1 row affected (0.00 sec)

mysql> select * from test.temp2;
+----+-------+
| id | nname |
+----+-------+
| ?1 | bbb ? |
| ?2 | ddd ? |
| ?4 | fff ? |
+----+-------+

在linux-node3 slave上查看数据

mysql> select * from test.temp2;
+----+-------+
| id | nname |
+----+-------+
| ?1 | bbb ? |
| ?2 | ddd ? |
| ?4 | fff ? |
+----+-------+
3 rows in set (0.00 sec)

在linux-node2 master上查看数据

mysql> select * from test.temp2;
+----+-------+
| id | nname |
+----+-------+
| ?1 | bbb ? |
| ?2 | ddd ? |
| ?4 | fff ? |
+----+-------+
3 rows in set (0.00 sec)

在linux-node2 slave上查看数据

mysql> select * from test.temp2;
+----+-------+
| id | nname |
+----+-------+
| ?1 | bbb ? |
| ?2 | ddd ? |
| ?4 | fff ? |
+----+-------+
3 rows in set (0.00 sec)

结论

  • 在任意一个master端更新数据,其他任意端都可以更新数据
  • 两台服务器配置了间隔自增长,数据不同冲突

linux-node1上安装mycat

安装mycat

cd /usr/local/src
wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz 
mv mycat /data/app/mycat-1.6
ln -s /data/app/mycat-1.6 /data/app/mycat

修改schema.xml

  • balance="1"
    全部的readHost与stand by writeHost参与select语句的负载均衡
  • writeType="0"
    所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个 writeHost,重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties
  • switchType="1"
    默认值为1,自动切换
cd /data/app/mycat
cp conf/schema.xml conf/schema.xml.bak
cat > conf/schema.xml <<EOF
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>
 ? ?<dataNode name="dn1" dataHost="node1" database="test" />
 ? ?<dataHost name="node1" maxCon="10" minCon="5" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
 ? ? ? ?<heartbeat>select user()</heartbeat>
 ? ? ? ?<writeHost host="master1" url="192.168.56.12:3306" user="root" password="mysql">
 ? ? ? ? ? ?<readHost host="slave2" url="192.168.56.13:3307" user="root" password="mysql" />
 ? ? ? ?</writeHost>
 ? ? ? ?<writeHost host="master2" url="192.168.56.13:3306" user="root" password="mysql">
 ? ? ? ? ? ?<readHost host="slave2" url="192.168.56.13:3307" user="root" password="mysql" />
 ? ? ? ?</writeHost>
 ? ?</dataHost>
</mycat:schema>
EOF

启动mycat

./bin/mycat start 
ss -lntup |egrep ?'(8066|9066)' ?
tcp ? ?LISTEN ? ? 0 ? ? ?100 ? ? ? ? ? ? ? ? ? :::8066 ? ? ? ? ? ? ? ? :::* ? ? ?users:(("java",16546,79))
tcp ? ?LISTEN ? ? 0 ? ? ?100 ? ? ? ? ? ? ? ? ? :::9066 ? ? ? ? ? ? ? ? :::* ? ? ?users:(("java",75))

验证mycat服务是否正常

在linux-node2-master端配置mycat连接账号

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'192.%' IDENTIFIED BY 'mysql'; 
Query OK,1 warning (0.07 sec

在mycat服务器上安装mysql服务,但是不启动

（编辑：ASP站长网）
相关内容
网友评论
推荐文章
    热点阅读

      【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱nqiang002@foxmail.com我们将及时予以处理。

      建议您使用1366×768 分辨率、Microsoft Internet Explorer 11浏览器以获得本站的出色浏览效果

      Copygight © 2008-2023 http://www.aspzz.cn All Rights Reserved. ASP站长网

      站长：nqiang002#foxmail.com（请把#换成@）