阅读 50

mysql双主双从集群+mycat读写分离

 

 实验:

环境准备
环境:centos7.3、docker
角色
ip
端口
master1
100.98.100.186
3306
master2
100.98.100.186
3307
slave1
100.98.100.186
3308
slave2
100.98.100.186
3309
mycat
100.98.100.186
8066
 
 
1、使用docker启动4个docker节点
mysql双主双从集群配置
mkdir -p /data/mysql{1..4}/{conf,logs,data}
docker run -p 3306:3306 --name master1 -v /data/mysql1/conf:/etc/mysql/conf.d -v /data/mysql1/logs:/logs -v /data/mysql1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker run -p 3307:3306 --name master2 -v /data/mysql2/conf:/etc/mysql/conf.d -v /data/mysql2/logs:/logs -v /data/mysql2/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker run -p 3308:3306 --name slave1 -v /data/mysql3/conf:/etc/mysql/conf.d -v /data/mysql3/logs:/logs -v /data/mysql3/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker run -p 3309:3306 --name slave2 -v /data/mysql4/conf:/etc/mysql/conf.d -v /data/mysql4/logs:/logs -v /data/mysql4/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
2、配置每个docker的远程密码
docker exec -it master1 /bin/bash
mysql -uroot -p123456
GRANT ALL ON *.* TO ‘root‘@‘%‘;
flush privileges;
ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘123456‘ PASSWORD EXPIRE NEVER;
ALTER USER ‘root‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘123456‘;
flush privileges;
exit
exit

docker exec -it master2 /bin/bash
mysql -uroot -p123456
GRANT ALL ON *.* TO ‘root‘@‘%‘;
flush privileges;
ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘123456‘ PASSWORD EXPIRE NEVER;
ALTER USER ‘root‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘123456‘;
flush privileges;
exit
exit

docker exec -it slave1 /bin/bash
mysql -uroot -p123456
GRANT ALL ON *.* TO ‘root‘@‘%‘;
flush privileges;
ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘123456‘ PASSWORD EXPIRE NEVER;
ALTER USER ‘root‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘123456‘;
flush privileges;
exit
exit

docker exec -it slave2 /bin/bash
mysql -uroot -p123456
GRANT ALL ON *.* TO ‘root‘@‘%‘;
flush privileges;
ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘123456‘ PASSWORD EXPIRE NEVER;
ALTER USER ‘root‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘123456‘;
flush privileges;
exit
exit
集群配置
1、修改master1配置
cat < /data/mysql1/conf/my.cnf
[mysqld]
#主服务器唯一id
server-id=1
#启用二进制日志
log-bin=mysql-bin
#设置不用复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=test
#设置logbin格式
binlog_format=STATEMENT
#在作为从数据库的时候,有写入操作也要更新二进制日志文件
#log-slave-updates
log_replica_updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围1..65535
auto-increment-increment=2
#表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1..65535
auto-increment-offset=1
EOF
2、修改master2配置
cat < /data/mysql2/conf/my.cnf
[mysqld]
#主服务器唯一id 2留给master1的slave
server-id=3
#启用二进制日志
log-bin=mysql-bin
#设置不用复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=test
#设置logbin格式
binlog_format=STATEMENT
#在作为从数据库的时候,有写入操作也要更新二进制日志文件
#log-slave-updates
log_replica_updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围1..65535
auto-increment-increment=2
#表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1..65535,这儿应该是2了
auto-increment-offset=2
EOF
3、修改从机slave1
cat < /data/mysql3/conf/my.cnf
[mysqld]
server-id=2
relay-log=mysql-relay
EOF
4、修改从机slave2
cat < /data/mysql4/conf/my.cnf
[mysqld]
server-id=4
relay-log=mysql-relay
EOF
重启服务
docker ps -a -q|xargs docker restart
创建同步用户、slave配置同步
1、节点1配置主从
查看master1状态
docker exec -it master1 /bin/bash
mysql -uroot -p123456
#创建用户并授权
create user ‘slave‘@‘%‘ identified by  ‘123123‘;
grant REPLICATION SLAVE ON *.* TO ‘slave‘@‘%‘ with grant option;
ALTER USER ‘slave‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘123123‘;
flush privileges;
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000011 |      616 | test         | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
slave1节点加入到master1
docker exec -it slave1 /bin/bash
mysql -uroot -p123456
mysql> CHANGE MASTER TO MASTER_HOST=‘100.98.100.186‘,
MASTER_USER=‘slave‘,
MASTER_PASSWORD=‘123123‘,
MASTER_PORT= 3306,
MASTER_LOG_FILE=‘mysql-bin.000011‘,MASTER_LOG_POS=616;
#启动
mysql> start slave;
mysql> show slave status \G;
--如果出问题重新初始化
stop slave;
reset master;
2、节点2配置主从
docker exec -it master2 /bin/bash
mysql -uroot -p123456
#创建用户并授权
create user ‘slave‘@‘%‘ identified by  ‘123123‘;
grant REPLICATION SLAVE ON *.* TO ‘slave‘@‘%‘ with grant option;
ALTER USER ‘slave‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘123123‘;
flush privileges;
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      860 | test         | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
slave2节点加入到master2
docker exec -it slave2 /bin/bash
mysql -uroot -p123456
mysql> CHANGE MASTER TO MASTER_HOST=‘100.98.100.186‘,
MASTER_USER=‘slave‘,
MASTER_PASSWORD=‘123123‘,
MASTER_PORT= 3307,
MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=860;
#启动
mysql> start slave;
mysql> show slave status \G;
--如果出问题重新初始化
stop slave;
reset master;
3、master1和master2两个都是复制对方
master1 使用master2的binglog,即slave2的同步配置
CHANGE MASTER TO MASTER_HOST=‘100.98.100.186‘,
MASTER_USER=‘slave‘,
MASTER_PASSWORD=‘123123‘,
MASTER_PORT= 3307,
MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=860;
mysql> start slave;
mysql> show slave status \G;
master2 使用master1的binglog,即slave1的同步配置
CHANGE MASTER TO MASTER_HOST=‘100.98.100.186‘,
MASTER_USER=‘slave‘,
MASTER_PASSWORD=‘123123‘,
MASTER_PORT= 3306,
MASTER_LOG_FILE=‘mysql-bin.000011‘,MASTER_LOG_POS=616;
#启动
mysql> start slave;
mysql> show slave status \G;
验证集群
建库建表插入数据
create database test 
 DEFAULT CHARACTER SET utf8mb4
 DEFAULT COLLATE utf8mb4_0900_ai_ci;
 use test;
create table student(
      id int,
      name varchar(100)
)
insert into student value(1,‘zs‘);
mycat配置

 

 

[root@node-1 conf]# more schema.xml




        
        
        
        
                select user()
                
                   
                
                
                   
                
        

验证读写分离
mysql -uroot -p123456 -h100.98.100.186 -P8066
 mysql> select @@hostname from student;

  

  

原文:https://www.cnblogs.com/wukc/p/15250733.html

文章分类
百科问答
版权声明:本站是系统测试站点,无实际运营。本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 XXXXXXo@163.com 举报,一经查实,本站将立刻删除。
相关推荐