# 安装MySQL
[root@mysql-master ~]# dnf install -y mysql-server

# 配置主服务器
[root@mysql-master ~]# cat > /etc/my.cnf.d/replication.cnf << 'EOF' [mysqld] # 服务器ID server-id = 1 # 二进制日志 log-bin = mysql-bin binlog_format = ROW binlog_cache_size = 1M max_binlog_size = 100M expire_logs_days = 7 # 复制配置 gtid_mode = ON enforce_gtid_consistency = ON log_slave_updates = ON # 同步配置 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 # 字符集 character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci EOF # 启动MySQL [root@mysql-master ~]# systemctl enable --now mysqld Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service. # 创建复制用户 [root@mysql-master ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32 Source distribution mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘Repl@123456’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 查看主服务器状态
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 689 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

# 安装MySQL
[root@mysql-slave ~]# dnf install -y mysql-server

# 配置从服务器
[root@mysql-slave ~]# cat > /etc/my.cnf.d/replication.cnf << 'EOF' [mysqld] # 服务器ID server-id = 2 # 中继日志 relay-log = relay-bin relay-log-index = relay-bin.index # 复制配置 gtid_mode = ON enforce_gtid_consistency = ON log_slave_updates = ON read_only = ON # 字符集 character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci EOF # 启动MySQL [root@mysql-slave ~]# systemctl enable --now mysqld # 配置复制 [root@mysql-slave ~]# mysql -u root mysql> CHANGE REPLICATION SOURCE TO
-> SOURCE_HOST=’192.168.1.10′,
-> SOURCE_USER=’repl’,
-> SOURCE_PASSWORD=’Repl@123456′,
-> SOURCE_AUTO_更多学习教程公众号风哥教程itpux_comPOSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START REPLICA;
Query OK, 0 rows affected (0.01 sec)

# 查看复制状态
mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.1.10
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 689
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 324
Relay_Source_Log_File: mysql-bin.000001
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 689
Relay_Log_Space: 521
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Seconds_Behind_Source: 0

# 安装ProxySQL
[root@proxysql ~]# dnf install -y proxysql

# 配置ProxySQL
[root@proxysql ~]# cat > /etc/proxysql.cnf << 'EOF' datadir="/var/lib/proxysql" admin_variables= { admin_credentials="admin:admin;radmin:radmin" mysql_ifaces="0.0.0.0:6032" } mysql_variables= { threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033" default_schema="information_schema" stacksize=1048576 server_version="8.更多视频教程www.fgedu.net.cn0.32" connect_timeout_server=3000 monitor_username="monitor" monitor_password="Monitor@123456" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 } EOF # 启动ProxySQL [root@proxysql ~]# systemctl enable --now proxysql # 配置后端服务器 [root@proxysql ~]# mysql -u admin -padmin -h 127.0.0.1 -P 6032 mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES (10, ‘192.168.1.10’, 3306, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES (20, ‘192.168.1.11’, 3306, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mysql_servfrom PG视频:www.itpux.comers(hostgroup_id, hostname, port, weight) VALUES (20, ‘192.168.1.12’, 3306, 1);
Query OK, 1 row affected (0.00 sec)

# 配置用户
mysql> INSERT INTO mysql_users(username, password, default_hostgroup) VALUES (‘appuser’, ‘App@123456’, 10);
Query OK, 1 row affected (0.00 sec)

# 配置读写分离规则
mysql> INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, ‘^SELECT’, 20, 1);
Query OK, 1 row affected (0.00 sec)

# 加载配置
mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

my学习交流加群风哥QQ113257174sql> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

mysql> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.00 sec)

# 测试连接
[root@client ~]# mysql -u appuser -p’App@123456′ -h 192.168.1.100 -P 6033 -e “SELECT @@hostname”
+————+
| @@hostname |
+————+
| mysql-slave|
+————+