Oracle Linux8 安装 MySQL 8.4.3,搭建一主一从

安装依赖

yum install -y numactl libaio ncurses-compat-libs

获取安装包

[root@oracleLinux8 mysql]# ll /root/mysql-8.4.3-linux-glibc2.17-aarch64.tar.xz
-rw-r--r-- 1 root root 896423176 Apr 20 21:40 /root/mysql-8.4.3-linux-glibc2.17-aarch64.tar.xz

解压

cd /root
tar xf mysql*xz -C /usr/local/
ln -s /usr/local/mysql-8.4.3-linux-glibc2.17-aarch64 /usr/local/mysql

准备相关目录

cd /usr/local/mysql
mkdir -p 3306/{data,socket,error_log,slow_error_log,pid_log,file}
/usr/local/mysql/bin/mysqld --initialize --user=mysql  --datadir=/usr/local/mysql/3306/data2025-04-20T14:08:36.281629Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2025-04-20T14:08:36.283506Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.4.3) initializing of server in progress as process 2983
2025-04-20T14:08:36.296320Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
get_mempolicy: Function not implemented
2025-04-20T14:08:36.299476Z 1 [Warning] [MY-011873] [InnoDB] Failed to set NUMA memory policy to MPOL_INTERLEAVE: Function not implemented
get_mempolicy: Function not implemented
2025-04-20T14:08:36.299650Z 0 [Warning] [MY-011879] [InnoDB] Failed to set NUMA memory policy of buffer pool page frames with mbind(0xffff77d10000,137297920,MPOL_INTERLEAVE,...,...,MPOL_MF_MOVE) failed with Function not implemented
2025-04-20T14:08:36.303475Z 1 [Warning] [MY-011875] [InnoDB] Failed to set NUMA memory policy to MPOL_DEFAULT: Function not implemented
2025-04-20T14:08:36.564595Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-20T14:08:37.648592Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !s:Cll=ey4Wz
2025-04-20T14:08:39.366449Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.

设置配置文件

cat > /usr/local/mysql/3306/data/my.cnf <<"EOF"
[mysqld]
# Basic Settings
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/3306/data
socket = /usr/local/mysql/3306/socket/mysql.sock
pid-file = /usr/local/mysql/3306/pid_log/mysql.pid
secure_file_priv = /usr/local/mysql/3306/file
log-bin=mysql-bin
server_id=1
mysql_native_password=ON
innodb_flush_log_at_trx_commit=1
sync_binlog=1# Network
port = 3306
bind-address = 0.0.0.0# Logging
log_error = /usr/local/mysql/3306/error_log/mysql-error.log
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/3306/slow_error_log/mysql-slow.log
long_query_time = 2# InnoDB Settings
innodb_buffer_pool_size = 128M
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1# Security
skip-name-resolve
local-infile = 0# Performance
max_connections = 100
thread_cache_size = 8
table_open_cache = 1000[client]
socket = /usr/local/mysql/3306/socket/mysql.sock
port = 3306[mysql]
socket = /usr/local/mysql/3306/socket/mysql.sock
auto-rehash
EOF

启动数据库

touch /usr/local/mysql/3306/error_log/mysql-error.log
mkdir -p /usr/local/mysql/file
chown mysql:mysql -R /usr/local/mysql/
chown mysql:mysql -R /usr/local/mysql/3306/*/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/3306/data/my.cnf &[root@oracleLinux8 mysql]#  /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/3306/data/my.cnf &
[1] 5743
[root@oracleLinux8 mysql]# 2025-04-20T14:21:31.458910Z mysqld_safe Logging to '/usr/local/mysql/3306/error_log/mysql-error.log'.
2025-04-20T14:21:31.468890Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/3306/data[root@oracleLinux8 mysql]#
[root@oracleLinux8 mysql]# ps -ef |grep mysql
root        5743    2266  0 22:21 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/3306/data/my.cnf
mysql       6093    5743 11 22:21 pts/1    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/3306/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/3306/error_log/mysql-error.log --pid-file=/usr/local/mysql/3306/pid_log/mysql.pid --socket=/usr/local/mysql/3306/socket/mysql.sock --port=3306
root        6138    2266  0 22:21 pts/1    00:00:00 grep --color=auto mysql
[root@oracleLinux8 mysql]#
[root@oracleLinux8 mysql]#
[root@oracleLinux8 mysql]# ss -tunlp | grep mysql
tcp   LISTEN 0      100          0.0.0.0:3306       0.0.0.0:*    users:(("mysqld",pid=6093,fd=22))
tcp   LISTEN 0      70                 *:33060            *:*    users:(("mysqld",pid=6093,fd=18))

连接数据库

/usr/local/mysql/bin/mysql -u root -p  -S /usr/local/mysql/3306/socket/mysql.sock
密码在前面日志中有 !s:Cll=ey4Wzmysql> alter user user() identified by 'root';
Query OK, 0 rows affected (0.02 sec)mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

socket 文件优化

每次登录, 输入 socket 文件过长,比较麻烦

cat >> ~/.bash_profile <<"EOF"
export MYSQL_UNIX_PORT=/usr/local/mysql/3306/socket/mysql.sock
export PATH=/usr/local/mysql/bin:$PATH
EOF
. ~/.bash_profile再次登录
[root@oracleLinux8 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.4.3 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>查看状态
[root@oracleLinux8 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.4.3 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>  select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.02 sec)mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.3     |
+-----------+
1 row in set (0.00 sec)mysql> status;
--------------
mysql  Ver 8.4.3 for Linux on aarch64 (MySQL Community Server - GPL)Connection id:		13
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.4.3 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/usr/local/mysql/3306/socket/mysql.sock
Binary data as:		Hexadecimal
Uptime:			7 min 35 secThreads: 2  Questions: 21  Slow queries: 0  Opens: 130  Flush tables: 3  Open tables: 46  Queries per second avg: 0.046
--------------

同样方法准备 3307 数据库实例

mkdir -p 3307/{data,error_log,slow_error_log,socket,pid_log,file}
chown -R  mysql:mysql 3307[root@oracleLinux8 3307]# /usr/local/mysql/bin/mysqld --initialize --user=mysql  --datadir=/usr/local/mysql/3307/data
2025-04-20T14:52:26.584588Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2025-04-20T14:52:26.586051Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.4.3) initializing of server in progress as process 7278
2025-04-20T14:52:26.611537Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
get_mempolicy: Function not implemented
2025-04-20T14:52:26.615731Z 1 [Warning] [MY-011873] [InnoDB] Failed to set NUMA memory policy to MPOL_INTERLEAVE: Function not implemented
get_mempolicy: Function not implemented
2025-04-20T14:52:26.616091Z 0 [Warning] [MY-011879] [InnoDB] Failed to set NUMA memory policy of buffer pool page frames with mbind(0xffff67d10000,137297920,MPOL_INTERLEAVE,...,...,MPOL_MF_MOVE) failed with Function not implemented
2025-04-20T14:52:26.622379Z 1 [Warning] [MY-011875] [InnoDB] Failed to set NUMA memory policy to MPOL_DEFAULT: Function not implemented
2025-04-20T14:52:26.808322Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-20T14:52:28.185961Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: BwtrQvcy-0_q
2025-04-20T14:52:29.564978Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.

设置配置文件

cat > /usr/local/mysql/3307/data/my.cnf <<"EOF"
[mysqld]
# Basic Settings
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/3307/data
socket = /usr/local/mysql/3307/socket/mysql.sock
pid-file = /usr/local/mysql/3307/pid_log/mysql.pid
secure_file_priv = /usr/local/mysql/3307/file
log-bin=mysql-bin
server_id=2
mysql_native_password=ON
innodb_flush_log_at_trx_commit=1
sync_binlog=1# Network
port = 3307
bind-address = 0.0.0.0# 调整X插件配置（避免端口和socket冲突）
mysqlx_port = 33070
mysqlx_socket = /tmp/mysqlx3307.sock# Logging
log_error = /usr/local/mysql/3307/error_log/mysql-error.log
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/3307/slow_error_log/mysql-slow.log
long_query_time = 2# InnoDB Settings
innodb_buffer_pool_size = 128M
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1# Security
skip-name-resolve
local-infile = 0# Performance
max_connections = 100
thread_cache_size = 8
table_open_cache = 1000[client]
socket = /usr/local/mysql/3307/socket/mysql.sock
port = 3307[mysql]
socket = /usr/local/mysql/3307/socket/mysql.sock
auto-rehash
EOF

主要修改 server_id 与如下两个配置
mysqlx_port = 33070
mysqlx_socket = /tmp/mysqlx3307.sock

启动 3307 实例数据库

[root@oracleLinux8 3307]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/3307/data/my.cnf &
2025-04-20T14:56:00.541265Z mysqld_safe Logging to '/usr/local/mysql/3307/error_log/mysql-error.log'.
2025-04-20T14:56:00.551499Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/3307/data[root@oracleLinux8 3307]# ss -tunlp |grep mysql
tcp   LISTEN 0      100          0.0.0.0:3306       0.0.0.0:*    users:(("mysqld",pid=6093,fd=22))
tcp   LISTEN 0      100          0.0.0.0:3307       0.0.0.0:*    users:(("mysqld",pid=7700,fd=22))
tcp   LISTEN 0      70                 *:33060            *:*    users:(("mysqld",pid=6093,fd=18))
tcp   LISTEN 0      70                 *:33070            *:*    users:(("mysqld",pid=7700,fd=18))

连接并查看 3307 数据库实例

密码在之前初始化的打印中

[root@oracleLinux8 3307]# mysql -p -S /usr/local/mysql/3307/socket/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.4.3Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user user() identified by 'root';
Query OK, 0 rows affected (0.05 sec)mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.3     |
+-----------+
1 row in set (0.00 sec)mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)mysql> status;
--------------
mysql  Ver 8.4.3 for Linux on aarch64 (MySQL Community Server - GPL)Connection id:		11
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.4.3
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/usr/local/mysql/3307/socket/mysql.sock
Binary data as:		Hexadecimal
Uptime:			4 min 20 secThreads: 2  Questions: 11  Slow queries: 0  Opens: 130  Flush tables: 3  Open tables: 46  Queries per second avg: 0.042
--------------

基于 bin log 搭建主从模式

规划: 3306 是主库; 3307 实例是从库

• 主库

create USER 'repuser'@'%' IDENTIFIED WITH mysql_native_password BY 'repuser' ;
GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'%';
FLUSH PRIVILEGES;SHOW BINARY LOG STATUS
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      443 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

• 从库

stop replica;
change replication source to source_host='127.0.0.1', source_port=3306, source_user='repuser', source_password='repuser', source_log_file='mysql-bin.000008', source_log_pos=443;
show replica status\G第一次报错如下:Last_IO_Errno: 2061Last_IO_Error: Error connecting to source 'repuser@127.0.0.1:3306'. This was attempt 6/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.然后在主库与从库配置文件的 mysqld 中 , 都启用了 mysql_native_password=on, 然后重启了数据库服务
然后同步状态正常如下;
mysql> show replica status\G
*************************** 1. row ***************************Replica_IO_State: Waiting for source to send eventSource_Host: 127.0.0.1Source_User: repuserSource_Port: 3306Connect_Retry: 60Source_Log_File: mysql-bin.000009Read_Source_Log_Pos: 158Relay_Log_File: oracleLinux8-relay-bin.000002Relay_Log_Pos: 328Relay_Source_Log_File: mysql-bin.000009Replica_IO_Running: YesReplica_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Source_Log_Pos: 158Relay_Log_Space: 546Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File:Source_SSL_CA_Path:Source_SSL_Cert:Source_SSL_Cipher:Source_SSL_Key:Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Source_Server_Id: 1Source_UUID: f37cc1a2-1df0-11f0-a107-92faf2fcb2f7Source_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State: Replica has read all relay log; waiting for more updatesSource_Retry_Count: 10Source_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Source_SSL_Crl:Source_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Source_TLS_Version:Source_public_key_path:Get_Source_public_key: 0Network_Namespace:
1 row in set (0.00 sec)-- 主库
mysql>  create database test2;
mysql> use test2;
Database changed
mysql> show tables;
Empty set (0.00 sec)mysql> create table test(id int );
Query OK, 0 rows affected (0.06 sec)mysql> insert into test values(1),(2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0-- 从库
mysql> use test2;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)mysql>