AWS RDS 创建MySQL数据库，并通过Ec2服务器连接
创建数据库时，根据自己的需求，按RDS提示创建数据库即可，比如选择MySQL社区版本创建，
选择自己的EC2资源设置连接，其他按需要选择配置，或直接使用默认设置即可。
创建成功后，保存好终端连接节点和账号密钥。
登录EC2连接数据库，如果没有安装mysql客户端时，选择安装。
安装客户端失败提示
1
2
3
4
|
> sudo dnf install -y mysql-community-client
Last metadata expiration check: 0:05:03 ago on Mon Sep 9 15:33:04 2024.
No match for argument: mysql-community-client
Error: Unable to find a match: mysql-community-client
|
查看是否有安装源
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
> sudo yum search mysql
Last metadata expiration check: 0:09:20 ago on Mon Sep 9 15:33:04 2024.
=========================================================================== Name & Summary Matched: mysql ===========================================================================
apr-util-mysql.x86_64 : APR utility library MySQL DBD driver
bind-dlz-mysql.x86_64 : BIND server mysql and mysqldyn DLZ modules
collectd-mysql.x86_64 : MySQL plugin for collectd
dovecot-mysql.x86_64 : MySQL back end for dovecot
mysql-selinux.noarch : SELinux policy modules for MySQL and MariaDB packages
perl-DBD-MySQL.x86_64 : A MySQL interface for Perl
perl-DateTime-Format-MySQL.noarch : Parse and format MySQL dates and times
php8.1-mysqlnd.x86_64 : A module for PHP 8.1 applications that use MySQL databases
php8.2-mysqlnd.x86_64 : A module for PHP 8.2 applications that use MySQL databases
php8.3-mysqlnd.x86_64 : A module for PHP 8.3 applications that use MySQL databases
postfix-mysql.x86_64 : Postfix MySQL map support
============================================================================== Summary Matched: mysql ===============================================================================
mariadb105-devel.x86_64 : Files for development of MariaDB/MySQL applications
mariadb105-server-utils.x86_64 : Non-essential server utilities for MariaDB/MySQL applications
perl-DBD-MariaDB.x86_64 : MariaDB and MySQL driver for the Perl5 Database Interface (DBI)
|
安装MySQL Yum Repository
这个时候就需要安装mysql源来解决
打开MySQL Community Downloads MySQL Yum Repository
选择一个版本安装
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
> sudo yum install -y https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm
Last metadata expiration check: 0:12:56 ago on Mon Sep 9 15:33:04 2024.
mysql84-community-release-el9-1.noarch.rpm 44 kB/s | 13 kB 00:00
Dependencies resolved.
=====================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================
Installing:
mysql84-community-release noarch el9-1 @commandline 13 k
Transaction Summary
=====================================================================================================================================================================================
Install 1 Package
Total size: 13 k
Installed size: 14 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : mysql84-community-release-el9-1.noarch 1/1
Verifying : mysql84-community-release-el9-1.noarch 1/1
=====================================================================================================================================================================================
WARNING:
A newer release of "Amazon Linux" is available.
Available Versions:
Version 2023.5.20240903:
Run the following command to upgrade to 2023.5.20240903:
dnf upgrade --releasever=2023.5.20240903
Release notes:
https://docs.aws.amazon.com/linux/al2023/release-notes/relnotes-2023.5.20240903.html
=====================================================================================================================================================================================
Installed:
mysql84-community-release-el9-1.noarch
Complete!
|
执行命令安装客户端
这时再执行就会安装成功
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
> sudo yum install -y mysql-community-client
MySQL 8.4 LTS Community Server 14 MB/s | 434 kB 00:00
MySQL Connectors Community 4.4 MB/s | 61 kB 00:00
MySQL Tools 8.4 LTS Community 10 MB/s | 192 kB 00:00
Dependencies resolved.
=====================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================
Installing:
mysql-community-client x86_64 8.4.2-1.el9 mysql-8.4-lts-community 3.0 M
Installing dependencies:
mysql-community-client-plugins x86_64 8.4.2-1.el9 mysql-8.4-lts-community 1.4 M
mysql-community-common x86_64 8.4.2-1.el9 mysql-8.4-lts-community 577 k
mysql-community-libs x86_64 8.4.2-1.el9 mysql-8.4-lts-community 1.5 M
Transaction Summary
=====================================================================================================================================================================================
Install 4 Packages
Total download size: 6.4 M
Installed size: 95 M
Downloading Packages:
(1/4): mysql-community-common-8.4.2-1.el9.x86_64.rpm 15 MB/s | 577 kB 00:00
(2/4): mysql-community-client-plugins-8.4.2-1.el9.x86_64.rpm 15 MB/s | 1.4 MB 00:00
(3/4): mysql-community-libs-8.4.2-1.el9.x86_64.rpm 20 MB/s | 1.5 MB 00:00
(4/4): mysql-community-client-8.4.2-1.el9.x86_64.rpm 22 MB/s | 3.0 MB 00:00
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 47 MB/s | 6.4 MB 00:00
MySQL 8.4 LTS Community Server 3.0 MB/s | 3.1 kB 00:00
Importing GPG key 0xA8D3785C:
Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
Fingerprint: BCA4 3417 C3B4 85DD 128E C6D4 B7B3 B788 A8D3 785C
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : mysql-community-client-plugins-8.4.2-1.el9.x86_64 1/4
Installing : mysql-community-common-8.4.2-1.el9.x86_64 2/4
Installing : mysql-community-libs-8.4.2-1.el9.x86_64 3/4
Running scriptlet: mysql-community-libs-8.4.2-1.el9.x86_64 3/4
Installing : mysql-community-client-8.4.2-1.el9.x86_64 4/4
Running scriptlet: mysql-community-client-8.4.2-1.el9.x86_64 4/4
Verifying : mysql-community-client-8.4.2-1.el9.x86_64 1/4
Verifying : mysql-community-client-plugins-8.4.2-1.el9.x86_64 2/4
Verifying : mysql-community-common-8.4.2-1.el9.x86_64 3/4
Verifying : mysql-community-libs-8.4.2-1.el9.x86_64 4/4
=====================================================================================================================================================================================
WARNING:
A newer release of "Amazon Linux" is available.
Available Versions:
Version 2023.5.20240903:
Run the following command to upgrade to 2023.5.20240903:
dnf upgrade --releasever=2023.5.20240903
Release notes:
https://docs.aws.amazon.com/linux/al2023/release-notes/relnotes-2023.5.20240903.html
=====================================================================================================================================================================================
Installed:
mysql-community-client-8.4.2-1.el9.x86_64 mysql-community-client-plugins-8.4.2-1.el9.x86_64 mysql-community-common-8.4.2-1.el9.x86_64 mysql-community-libs-8.4.2-1.el9.x86_64
Complete!
|
此时执行mysql命令即可看到安装好的命令
1
2
3
|
> mysql
mysql mysql_migrate_keyring mysqlbinlog mysqldump mysqlshow
mysql_config_editor mysqladmin mysqlcheck mysqlimport mysqlslap
|
连接数据库
用创建数据库时，保存的终端节点和账号密钥，连接到数据库，示例如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
> mysql -h xxx.rds.amazonaws.com -P 3306 -u admin -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.35 Source distribution
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
|
创建数据库
创建数据库app-db,如下：
1
2
3
4
5
6
7
8
|
mysql> show create database app-db;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| app-db | CREATE DATABASE `app-db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
|
创建用户并授权
创建用户，并给用户授权数据库操作权限,示例如下:
1
2
3
4
5
6
7
8
9
10
|
CREATE USER 'app-user'@'%' IDENTIFIED BY 'my-password'; -- 创建用户
GRANT select,insert,update,delete,create ON app-db.* TO 'app-user'@'%'; --用户授权数据库
-- 或者授权所有权限，命令如下
-- GRANT ALL ON app-db.* TO 'app-user'@'%';
FLUSH PRIVILEGES; --刷新权限生效
SHOW GRANTS FOR 'app-user'@'%'; --查看用户权限
|
参考