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';

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;

新加主从

我们现在已经有了一套mysql的主从复制集群，但是随着业务的发展现在需要再增加一个数据库并加入到主从复制，实现方法如下：
1.首先，在从库上停掉主从复制

stop slave;

2.去主库上导出新的数据库

mysqldump -uroot -p --master-data --single-transaction -R --databases quartz > quartz.sql

3.修改从数据库上my.cnf，在binlog-do-db参数里增加新的需要同步的数据库
4.查找刚才备份下来的数据库的日志位置

cat quartz.sql |grep "MASTER_LOG_FILE"

然后启动slave(注意MASTER_LOG_FILE和MASTER_LOG_POS要和上面查出来的必须完全一致)

start slave until MASTER_LOG_FILE='mysql-bin.000170', MASTER_LOG_POS=5444027;

5.在从库上导入新的数据库

nohup mysql -uroot -p123456 -f quartz < quartz .sql > /dev/null 2>&1 & 

6.启动主从同步

start slave;

3. proxysql读写分离

官网文档

https://proxysql.com/documentation/installing-proxysql/

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[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;