如何在 CentOS 8 Linux 上安装 MySQL 服务器

发表 admin at 2024年8月26日

类别

未分类

标签

如何在 Linode 和 AWS 云上运行的 CentOS 8 Linux 服务器上安装 MySQL 服务器 8.0？如何在新创建的 CentOS 服务器上添加和设置新的 MySQL 用户和数据库帐户？

Oracle MySQL 服务器版本 8.0 是一款免费的开源免费数据库服务器。它是互联网上 Web 应用程序和网站中最流行的数据库系统之一。通常，MySQL 是 LAMP（Linux、Apache/Nginx、MySQL、Perl/Python/PHP）堆栈的一部分。流行的开源软件（如 WordPress、MediaWiki 等）被 MySQL 深深用作数据库存储引擎。让我们看看如何在 CentOS 8 Linux 服务器上安装 MySQL 服务器版本 8.x。

教程要求
要求	CentOS Linux 8
Root 权限	是的
难度等级	简单的
预计阅读时间	9 分钟
目录 ↓ 1更新 CentOS 8 2安装MySQL 8 3在启动时启用 MySQL 8 4保护 MySQL 8 服务器 5启动/停止/重启服务 6测试 7创建新的数据库和用户帐户 8配置 MySQL 8 服务器 {优化} 9防火墙配置 10结论

如何在 CentOS 8 上安装 MySQL 服务器

首先，打开终端应用程序，然后使用 ssh 命令登录到您的 CentOS 服务器：
$ ssh vivek@centos-8-ec2-box-ip
现在，使用 dnf 命令/ yum 命令更新 CentOS 系统以在 Linux 系统上应用安全更新和修复：示例输出：
$ sudo yum update ## or ## $ sudo dnf update

CentOS-8 - AppStream                            21 MB/s | 5.8 MB     00:00
CentOS-8 - Base                                 14 MB/s | 2.2 MB     00:00
CentOS-8 - Extras                               50 kB/s | 8.6 kB     00:00
Dependencies resolved.
Nothing to do.
Complete!

步骤 1 – 安装 MySQL 8 服务器

幸运的是，我们的 CentOS 8 盒子附带了 MySQL 8 服务器包。让我们搜索它：我们看到：
$ sudo yum search mysql-server $ sudo yum module list mysql

Last metadata expiration check: 0:02:47 ago on Mon Nov 23 16:26:31 2020.
===================== Name Exactly Matched: mysql-server ======================
mysql-server.x86_64 : The MySQL server and related files

接下来，找出版本信息，运行：
$ sudo yum info mysql-server
我们看到的是：

Last metadata expiration check: 0:02:22 ago on Mon Nov 23 16:26:31 2020.
Available Packages
Name         : mysql-server
Version      : 8.0.21
Release      : 1.module_el8.2.0+493+63b41e36
Architecture : x86_64
Size         : 22 M
Source       : mysql-8.0.21-1.module_el8.2.0+493+63b41e36.src.rpm
Repository   : AppStream
Summary      : The MySQL server and related files
URL          : http://www.mysql.com
License      : GPLv2 with exceptions and LGPLv2 and BSD
Description  : MySQL is a multi-user, multi-threaded SQL database server. MySQL
             : is a client/server implementation consisting of a server daemon
             : (mysqld) and many different client programs and libraries. This
             : package contains the MySQL server and some accompanying files
             : and directories.

安装：
$ sudo yum install mysql-server

第 2 步 - 启用 MySQL 8 mysqld.service,server

服务名称是 mysqld.service，我们需要使用以下 systemctl 命令启用它：显示确认：
$ sudo systemctl enable mysqld.service

reated symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

启动服务然后验证：
$ sudo systemctl start mysqld.service $ sudo systemctl status mysqld.service

● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2020-11-23 16:50:14 UTC; 4s ago
  Process: 551 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)
  Process: 681 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
  Process: 601 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
  Process: 577 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
 Main PID: 637 (mysqld)
   Status: "Server is operational"
    Tasks: 39 (limit: 24960)
   Memory: 331.0M
   CGroup: /system.slice/mysqld.service
           └─637 /usr/libexec/mysqld --basedir=/usr

Nov 23 16:50:13 centos-aws-mysql systemd[1]: Stopped MySQL 8.0 database server.
Nov 23 16:50:13 centos-aws-mysql systemd[1]: Starting MySQL 8.0 database server...
Nov 23 16:50:14 centos-aws-mysql systemd[1]: Started MySQL 8.0 database server.

步骤 3 – 保护 MySQL 8 服务器

您需要做的就是输入以下命令，它将保护 CentOS Linux 上的 MySQL 8 服务器安装：
$ sudo mysql_secure_installation

Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

步骤 4 – 启动/停止/重新启动 MySQL 8 服务器

语法是：使用 journalctl 命令查看 MySQL 8 服务日志如下：MySQL 8 日志文件示例条目：
$ sudo systemctl start mysql.service $ sudo systemctl stop mysql.service $ sudo systemctl restart mysql.service

$ sudo journalctl -u mysqld.service -xe $ sudo tail -f /var/log/mysql/mysqld.log

2020-11-23T16:55:19.101316Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.21).
2020-11-23T16:55:21.728819Z 0 [Warning] [MY-010909] [Server] /usr/libexec/mysqld: Forcing close of thread 10  user: 'root'.
2020-11-23T16:55:23.083389Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.21)  Source distribution.
2020-11-23T16:56:19.225544Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.21) starting as process 524
2020-11-23T16:56:19.237500Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-11-23T16:56:19.562441Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-11-23T16:56:19.677202Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock
2020-11-23T16:56:19.754024Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-11-23T16:56:19.754207Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-11-23T16:56:19.780843Z 0 [System] [MY-010931] [Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.21'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution.

步骤 5 – 测试 MySQL 8 安装

到目前为止，一切顺利。您学习了如何在 CentOS 8 Linux 云服务器上安装、设置、保护和启动/停止 MySQL 8。现在是时候以
mysql root 用户身份登录了。语法是：让我们在提示符下输入一些 SQL 命令：
$ mysql -u root -p $ mysql -u USER -h host -p $ mysql -u USER -h host -p mysql
mysql>
STATUS; SHOW VARIABLES LIKE "%version%"; quit
在 CentOS 8 上测试 MySQL

步骤 6 – 创建新的 MySQL 8 数据库和用户帐户以及密码

让我们创建一个名为“ spacedb ”的新数据库，在mysql>提示符下输入：接下来，我们将为名为“ spacedb ”的数据库创建一个名为“ mars ”的新用户，如下所示：最后，授予权限：当然，我们也可以授予所有权限，如下所示：请参阅 MySQL 8 用户及其授予/权限，如下所示：测试新用户设置和数据库，如下所示：其中，
CREATE DATABASE spacedb;

CREATE USER 'mars'@'%' IDENTIFIED BY 'User_Password_Here';

GRANT SELECT, INSERT, UPDATE, DELETE ON spacedb.* TO 'mars'@'%';

GRANT ALL PRIVILEGES ON spacedb.* TO 'mars'@'%';

SELECT user,host FROM mysql.user; SHOW GRANTS for mars; quit

mysql -u mars -p spacedb mysql -u mars -h localhost -p spacedb
在 CentOS 8 上创建 MySQL 8 数据库并设置用户和密码

-u mars;：登录用户名
-h localhost：连接到名为 localhost 的服务器
-p：提示输入密码
spacedb：连接到名为spacedb的数据库

步骤 7 – 在 CentOS 8 上配置 MySQL 8 服务器

让我们使用cat 命令查看默认配置文件：
# cat /etc/my.cnf.d/mysql-server.cnf
配置：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

想要允许远程连接到你的 MySQL 服务器吗？编辑/etc/my.cnf.d/mysql-server.cnf并添加以下行[mysqld]：
bind_address = 0.0.0.0

警告：请参阅 MySQL文档，详细了解针对每个服务器和设置的独特调整选项。请勿盲目设置值。我提供它们作为优化 MySQL 8 安装和值的起点，具体取决于可用的 RAM、CPU 核心、服务器负载和其他情况。

设置 InnoDB 设置：

default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 1
innodb_buffer_pool_size         = 512M
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 512M
innodb_stats_on_metadata        = 0
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64

MyISAM 设置：

# UPD
key_buffer_size                 = 32M   
low_priority_updates            = 1
concurrent_insert               = 2
# UPD
max_connections                 = 100   
back_log                        = 512
thread_cache_size               = 100
thread_stack                    = 192K
interactive_timeout             = 180
wait_timeout                    = 180

缓冲区设置 UPD：

join_buffer_size                = 4M    
read_buffer_size                = 3M    
read_rnd_buffer_size            = 4M    
sort_buffer_size                = 4M

如果需要，编辑并配置日志记录（默认情况下 slow_query 已禁用）：

log_queries_not_using_indexes   = 1
long_query_time                 = 5
#slow_query_log                  = 0     
#slow_query_log_file             = /var/log/mysql/mysql_slow.log

这对于 mysqldump 命令进行备份很有用：

[mysqldump]
quick
quote_names
max_allowed_packet              = 64M

步骤 8 - 防火墙配置以打开 MySQL 服务器 TCP 端口 3306

您是否远程使用 MySQL 8 服务器？您在另一台服务器上有 Apache/Nginx/PHP/Python/Perl 应用程序吗？然后为所有人打开端口：
$ sudo firewall-cmd --zone=public --add-service=mysql --permanent
仅允许从192.168.1.0/24 CIDR 访问：以上是细粒度的防火墙访问规则，用于将对 MySQL 8 服务器的访问限制为仅限 VLAN 用户。有关更多信息，请参阅如何在 CentOS 8 Linux 上使用 FirewallD 设置防火墙。
$ sudo firewall-cmd \ --add-rich-rule 'rule family="ipv4" \ source address="192.168.1.0/24" \ service name="mysql" accept' --permanent

结论

至此，Oracle MySQL 服务器版本 8.x 已在 CentOS Linux 8 服务器上正确设置并运行，并配置了 Firewalld。此外，您还学习了如何为项目添加新数据库、用户和密码，包括 MySQL 8 服务器调优选项。