主重復制
主重復制原理
- 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/
環境說明與配置
| 數據庫角色 | IP | 應用與系統版本 |
|---|---|---|
| master | 192.168.25.51 | OpenEuler mysql-8.0.42 |
| slave | 192.168.25.52 | OpenEuler mysql-8.0.42 |
| slave2 | 192.168.25.53 | OpenEuler mysql-8.0.42 |
| maxscale | 192.168.25.100 | rocky linux9.4 maxscale-24.02.6 |
- rocky9安裝maxscale和mariadb
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bashyum -y install maxscaleyum -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=3306protocol=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
