初识MySQL（三）之主从配置与读写分离实战

主重复制

主重复制原理

• master开启二进制日志记录
• slave开启IO进程，从master中读取二进制日志并写入slave的中继日志
• slave开启SQL进程，从中继日志中读取二进制日志并进行重放
• 最终，达到slave与master中数据一致的状态，我们称作为主从复制的过程。

基础环境准备

## 确保两台主机能够连接互联网并关闭防火墙和selinux
yum -y install mysql-server#两台主机安装mysql
timedatectl set-timezone Asia/Shanghai#同步时间
systemctl restart chronyd.service #重启同步服务

配置主服务器

hostnamectl set-hostname master
bash
vim /etc/my.cnf
##########以下是配置文件内容
[mysqld]
log-bin=mysql-bin
binlog_format="statement"
server-id=11
log-slave-updates=true
########################
systemctl restart mysqld
ls /var/lib/mysql/#查看是否启用binlog

#在主服务器上配置可访问用户，并查看master正在使用的日志文件及日志书写位置
mysql -uroot -p123456 #根据自己主机情况登录mysql
# 根据自生网段创建并授权用户，并修改密码加密插件为 mysql_native_password
create user slave@'192.168.25.%' identified by '123.com';
grant all on *.* to 'slave'@'192.168.25.%';
ALTER USER 'slave'@'192.168.25.%' IDENTIFIED WITH mysql_native_password BY '123.com';
flush privileges;
# 查看主服务器正在使用的日志文件及日志书写位置，查看后不可再对master做insert、update、delete、create、drop等操作！！！
show master status;

配置从服务器

hostnamectl set-hostname slave
bash
vim /etc/my.cnf
##########以下是追加到配置文件中的内容
relay-log-index=slave-bin.index
server-id=22
##########
systemctl restart mysqld#重启MySQL服务

mysql -uroot#登录mysql,注意笔者此主机无密码，复现需根据自身情况登录

change master to master_host='192.168.25.51',master_user='slave',master_password='123.com',master_log_file='mysql-bin.000002',master_log_pos=1172;
start slave;
show slave status\G;

测试配置情况

#主服务器
create database testMS;
#从服务器
show databases;

读写分离

MaxScale是maridb开发的一个mysql数据中间件，其配置简单，能够实现读写分离，并且可以根据主从状态实现写库的自动切换。

• 官网：https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-20/

环境说明与配置

• rocky9安装maxscale和mariadb

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash
 yum -y install maxscale
 yum -y install mariadb

配置maxscale

• 在主库配置授权用户

mysql -uroot -p123456
create user maxscale@'%' identified by 'maxscale';
## 必须更新密码组件 否则不能连接
ALTER USER 'maxscale'@'%' IDENTIFIED WITH mysql_native_password BY 'maxscale';
grant select on *.* to maxscale@'%';
grant show databases on *.* to maxscale@'%';
create user admin@'192.168.25.%' identified by 'admin';
## 必须更新密码组件 否则不能连接
ALTER USER admin@'192.168.25.%' IDENTIFIED WITH mysql_native_password BY 'admin';
GRANT CREATE, SELECT, INSERT, UPDATE, DELETE ON *.* TO 'admin'@'192.168.25.%';
create user monitor@'%' identified by 'monitor';
## 必须更新密码组件 否则不能连接
ALTER USER monitor@'%' IDENTIFIED WITH mysql_native_password BY 'monitor';
grant replication client on *.* to monitor@'%';
grant replication slave on *.* to monitor@'%';
grant super,reload on *.* to monitor@'%';
flush privileges；

• 在maxscale上测试连接

mariadb -u admin -padmin -h 192.168.25.51 -P 3306 --skip-ssl

• 修改maxscale配置文件

# /etc/maxscale.cnf
[maxscale]
threads=auto

[server1]
type=server
address=192.168.25.51
port=3306

protocol=MySQLBackend
[server2]
type=server
address=192.168.25.52
port=3306
protocol=MySQLBackend

[server3]
type=server
address=192.168.25.53
port=3306
protocol=MySQLBackend

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=monitor
password=monitor
monitor_interval=2s

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
password=maxscale
version_string=8.0

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=mariadbprotocol
port=3306

• 启动maxscale并查看提供服务

systemctl start maxscale
maxctrl list services
maxctrl list servers