读写分离
环境说明:
角色 | IP | 应用与系统版本 | 备注 |
---|---|---|---|
master | 192.168.100.240 | rockylinux9.3 mysql-8.0.35 | 主数据库 |
slave | 192.168.100.230 | rockylinux9.3 mysql-8.0.35 | 从数据库1 |
slave2 | 192.168.100.220 | rockylinux9.3 mysql-8.0.35 | 从数据库2 |
maxscale | 192.168.100.200 | rockylinux9.3 mysql-8.0.35 | 读写分离 |
windows | 192.168.100.100 | rockylinux9.3 mysql-8.0.35 | 客户机 |
前期准备
本次续接上次GTID实验
主数据库已经和从数据库1已经同步(具体参考GTID.md)
本次GTID同步是主数据库和从数据库2同步
同步
主数据库
//授权
mysql> create user repl@'192.168.100.220' identified with mysql_native_password by 'repl123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repl@'192.168.100.220';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
完全备份
主数据库和从数据库1已经同步了
下载加入2,如果吧完全备份会导致数据不一致,无法同步
//主数据库
[root@master ~]# mkdir /data/
[root@master ~]# mysqldump -uroot -p123456 --all-databases > /data/all-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
[root@master ~]# cd /data/
[root@master data]# ls
all-20231228.sql
[root@master data]# scp all-20231228.sql 192.168.100.220:/opt
root@192.168.100.220's password:
all-20231228.sql 100% 0 0.0KB/s 00:00
[root@master data]#
//从数据库
[root@slave2 ~]# mysql -u root -p'123456789' < /opt/all-20231228.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
从数据库2
//配置
[root@slave ~]# vim /etc/my.cnf
[root@slave ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
authentication_policy = mysql_native_password
socket = /tmp/mysql.sock
server-id=30
relay-log=myrelay
gtid_mode=on
log-slave-updates=on
read_only=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
enforce-gtid-consistency=on
[root@slave ~]# systemctl restart mysqld
//数据库同步
mysql> change master to
-> master_host='192.168.100.240',
-> master_user='repl',
-> master_password='repl123',
-> master_port=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.100.240
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000008
Read_Master_Log_Pos: 197
Relay_Log_File: myrelay.000009
Relay_Log_Pos: 413
Relay_Master_Log_File: mysql_bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 197
Relay_Log_Space: 1736
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 03262576-a458-11ee-9e66-000c2955ab78
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 03262576-a458-11ee-9e66-000c2955ab78:1-14
Executed_Gtid_Set: 03262576-a458-11ee-9e66-000c2955ab78:1-14
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
mysql>
读写分离
安装
仓库
[root@maxscale ~]# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 11.2 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg ~
~
# [info] Successfully added trusted package signing keys
# [info] Cleaning package cache...
25 files removed
[root@maxscale ~]# ls /etc/yum.repos.d/
mariadb.repo rocky-addons.repo rocky-devel.repo rocky-extras.repo rocky.repo
[root@maxscale ~]#
//安装
[root@maxscale ~]# yum clean all
0 files removed
[root@maxscale ~]# yum makecache
MariaDB Server 77 kB/s | 624 kB 00:08
MariaDB MaxScale 753 B/s | 6.7 kB 00:09
MariaDB Tools 6.9 kB/s | 25 kB 00:03
Rocky Linux 9 - BaseOS 1.4 MB/s | 2.2 MB 00:01
Rocky Linux 9 - AppStream 3.6 MB/s | 7.4 MB 00:02
Rocky Linux 9 - Extras 10 kB/s | 14 kB 00:01
Metadata cache created.
[root@maxscale ~]# yum -y install maxscale
主数据库
//为maxscale创建用户账号
mysql> CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON mysql.* TO 'maxscale'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE USER 'admin'@'192.168.100.200' IDENTIFIED BY 'admin';
Query OK, 0 rows affected (0.01 sec)
mysql>
授权
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'admin'@'192.168.100.200';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER 'admin'@'192.168.100.100' IDENTIFIED BY 'admin';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'admin'@'192.168.100.100';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION CLIENT on *.* to 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVe on *.* to 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> GRANT SUPER,RELOAD on *.* to 'monitor'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
maxscale配置
[root@maxscale ~]# vim /etc/maxscale.cnf
[server1]
type=server
address=192.168.100.240
port=3306
protocol=MySQLBaclend
[server2]
type=server
address=192.168.100.230
port=3306
protocol=MySQLBaclend
[server3]
type=server
address=192.168.100.220
port=3306
protocol=MySQLBaclend
//监控
[MySQL-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=monitor
password=monitor
monitor_interval=2s
读写分离服务
注释掉只读
#[Read-Only-Service]
#type=service
#router=readconnroute
#servers=server1
#user=service_user
#password=service_pw
#router_options=slave
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
password=maxscale
#[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#protocol=mariadbprotocol
#port=4008
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=mariadbprotocol
port=3306
启动·
[root@maxscale ~]# systemctl start maxscale.service
[root@maxscale ~]# systemctl status maxscale.service
● maxscale.service - MariaDB MaxScale Database Proxy
Loaded: loaded (/usr/lib/systemd/system/maxscale.service; enabled; preset: disabled)
Active: active (running) since Thu 2023-12-28 03:47:06 EST; 36s ago
Process: 35032 ExecStartPre=/usr/bin/install -d /var/run/maxscale -o maxscale -g maxscale (code=exited, status=0/SUCCESS)
Process: 35033 ExecStartPre=/usr/bin/install -d /var/lib/maxscale -o maxscale -g maxscale (code=exited, status=0/SUCCESS)
Process: 35034 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS)
Main PID: 35035 (maxscale)
Tasks: 11 (limit: 10685)
Memory: 14.1M
CPU: 174ms
CGroup: /system.slice/maxscale.service
└─35035 /usr/bin/maxscale
Dec 28 03:47:06 maxscale systemd[1]: Starting MariaDB MaxScale Database Proxy...
Dec 28 03:47:06 maxscale maxscale[35035]: /var/lib/maxscale/maxscale.cnf.d does not exist, not reading.
Dec 28 03:47:06 maxscale maxscale[35035]: Module 'mariadbmon' loaded from '/usr/lib64/maxscale/libmariadbmon.so'.
Dec 28 03:47:06 maxscale maxscale[35035]: Module 'readwritesplit' loaded from '/usr/lib64/maxscale/libreadwritesplit.so'.
Dec 28 03:47:06 maxscale maxscale[35035]: Using up to 259.83MiB of memory for query classifier cache
Dec 28 03:47:06 maxscale systemd[1]: Started MariaDB MaxScale Database Proxy.
[root@maxscale ~]#
服务
[root@maxscale ~]# maxctrl list services
Service | Router | Connections | Total Connections | Targets |
---|---|---|---|---|
Read-Write-Service | readwritesplit | 0 | 0 | server1, server2, server3 |
[root@maxscale ~]# maxctrl list servers
Server | Address | Port | Connections | State | GTID | Monitor |
---|---|---|---|---|---|---|
server1 | 192.168.100.240 | 3306 | 0 | Master, Running | MySQL-Monitor | |
server2 | 192.168.100.230 | 3306 | 0 | Slave, Running | MySQL-Monitor | |
server3 | 192.168.100.220 | 3306 | 0 | Slave, Running | MySQL-Monitor |
[root@maxscale ~]# maxctrl list listeners
Name | Port | Host | State | Service |
---|---|---|---|---|
Read-Write-Listener | 3306 | :: | Running | Read-Write-Service |
[root@maxscale ~]#
权限验证
mysql> show grants for ‘admin’@‘192.168.100.100’;
±-------------------------------------------------------------------------+
| Grants for admin@192.168.100.100 |
±-------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON . TO admin
@192.168.100.100
|
±-------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW GRANTS FOR ‘admin’@‘192.168.100.200’;
±-------------------------------------------------------------------------+
| Grants for admin@192.168.100.200 |
±-------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON . TO admin
@192.168.100.200
|
±-------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
验证