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

已于 2025-04-21 23:07:03 修改
阅读量366 收藏 5
点赞数 10
文章标签： mysql
于 2025-04-21 00:22:29 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yueludanfeng/article/details/147376397
版权

文章目录

安装依赖

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/data


2025-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=ey4Wz

mysql> 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 - GPL

Copyright (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 - GPL

Copyright (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 sec

Threads: 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.3

Copyright (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 sec

Threads: 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: 2061
                Last_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 event
                  Source_Host: 127.0.0.1
                  Source_User: repuser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000009
          Read_Source_Log_Pos: 158
               Relay_Log_File: oracleLinux8-relay-bin.000002
                Relay_Log_Pos: 328
        Relay_Source_Log_File: mysql-bin.000009
           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: 158
              Relay_Log_Space: 546
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_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: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: f37cc1a2-1df0-11f0-a107-92faf2fcb2f7
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_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>
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值