mysql读写分离和组复制_Mysql主从复制读写分离

1. yum安装mysql8

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

yum -y install mysql80-community-release-el7-3.noarch.rpm

yum repolist enabled | grep mysql.*

yum install mysql-community-server -y

systemctl start mysqld.service

五：初始化数据库密码

查看一下初始密码

grep "password" /var/log/mysqld.log

登录

mysql -uroot -p U5>oCmthRi?S gh5rPi;A,zxy

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Ge4PiSrx9gdLBWfx';

2. 配置mysql主从

1. 修改主库配置文件

vim /etc/my.cnf

#主服务器唯一ID

server-id=1

#启用二进制日志

log-bin=mysql-bin

# 设置不要复制的数据库(可设置多个)

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

#设置需要复制的数据库

binlog-do-db=mycatdb #需要复制的主数据库名字

#设置logbin格式

binlog_format=STATEMENT

2. 从库配置文件

vim /etc/my.cnf

#从服务器唯一ID

server-id=2

#启用中继日志

relay-log=mysql-relay

log-bin=mysql-bin #从库会基于此log-bin来做复制

replicate-do-db=mycatdb #用于读写分离的具体数据库

#设置logbin格式

binlog_format=STATEMENT

3. 主库创建主从用户

CREATE USER 'slave'@'%' IDENTIFIED BY 'slavepassword';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

flush privileges;

show master status;

4. 从库操作从库

stop slave;

change master to master_host='172.16.83.5',

master_port=3306,

master_user='slave',

master_password='slavepassword',

master_log_file='mysql-bin.000005',

master_log_pos=1228;

start slave;

show slave status\G;

3. proxysql读写分离

官网文档

cat <

[proxysql_repo]

name= ProxySQL YUM repository

baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever

gpgcheck=1

gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key

EOF

yum -y install proxysql

启动服务

systemctl start proxysql

查看端口

ss -lnput |grep proxysql

6032 是 ProxySQL 的管理端口号，6033是对外服务的端口号`

ProxySQL 的用户名和密码都是默认的 admin

登录

mysql -uadmin -padmin -h 127.0.0.1 -P 6032

1. ProxySQL添加主、从节点

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.83.6', 3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.83.5', 3306);

load mysql servers to runtime;

save mysql servers to disk;

2. 添加监控后端节点的用户

#主库服务器上配置：

#在master上执行，从节点会自动同步该账户。该账户用于proxysql识别哪个是read-only的数据库

create user 'monitor'@'172.16.83.%' identified by 'mWCt6SuyG';

grant all privileges on *.* to 'monitor'@'172.16.83.%' with grant option;

flush privileges;

#注意 8.0.x 用户认证的方式需要修改为 mysql_native_password

#需要在my.cnf 加上这个用户认证方式，再来创建用户

select user,host,plugin from mysql.user;

#[mysqld]

#default_authentication_plugin=mysql_native_password

ProxySQL上配置：

在ProxySQL上配置监控

set mysql-monitor_username='monitor';

set mysql-monitor_password='mWCt6SuyG';

加载到RUNTIME，并保存到disk

load mysql variables to runtime;

save mysql variables to disk;

#验证监控信息，ProxySQL 监控模块的指标都保存在monitor库的log表中

#connect_error的结果为NULL则表示正常

select * from monitor.mysql_server_connect_log;

#对心跳信息的监控(对ping 指标的监控)

select * from mysql_server_ping_log limit 10;

3. 设置分组信息

#需要修改的是main库中的mysql_replication_hostgroups表，该表有4个字段：writer_hostgroup，reader_hostgroup，check_type，comment, 指定写组的id为10，读组的id为20

insert into mysql_replication_hostgroups values(10,20,"read_only","test");

load mysql servers to runtime;

save mysql servers to disk;

#Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组

#查询后端状态

select hostgroup_id,hostname,port,status,weight from mysql_servers;

4. 在主库服务器创建用户

在master节点上创建访问用户，该账号给proxysql连接主从数据库用

#create user 'sqluser'@'172.16.83.%' identified by 'mWCt6SuyG';

create user 'sqluser'@'172.16.83.%' identified by '7a4JXEADeqX2Eh9K';

grant all on *.* to 'sqluser'@'172.16.83.%' with grant option;

=======================================================================

在ProxySQL服务器上配置：

在ProxySQL配置，将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库

insert into mysql_users(username,password,default_hostgroup) values('sqluser', '7a4JXEADeqX2Eh9K',10);

#update mysql_users set password='7a4JXEADeqX2Eh9K' where username='sqluser';

load mysql users to runtime;

save mysql users to disk;

5. 配置理由规则

# 添加规则

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);

load mysql query rules to runtime;

save mysql query rules to disk;

6. 测试

mysql -usqluser -p7a4JXEADeqX2Eh9K -P6033 -h172.16.83.5 -P6033 -e 'create database db1'

mysql -usqluser -p7a4JXEADeqX2Eh9K -P6033 -h172.16.83.5 -P6033 -e 'select @@server_id'

mysql -usqluser -p7a4JXEADeqX2Eh9K -P6033 -h127.0.0.1 -e 'select @@server_id'

SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;