MySQL数据库安装

着重安装、基础SQL语句操作。

一、介绍

MySQL是一款开源的关系型数据库，应用非常广泛。自从被Oracle收购，有闭源风险，所有有MariaDB 作为社区分支。

二、CentOS7安装MySQL5.7

有多种安装方式，源码安装、配置YUM 源安装。这里以mysql 5.7 为例。

2.1 源码编译安装

hostnamectl set-hostname source-mysql

2.1.1 安装前准备

1. 关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

2. 关闭SELinux

setenforce 0
sed -i 's/^SELINUX=*/SELINUX=disable/g' /etc/selinux/config

3. 配置yum

yum repolist

4. 清理之前的环境

• 清理之前的mysql 包

yum -y remove mariadb mariadb-server mariadb-libs mariadb-devel

• 删除mysql用户

userdel -r mysql

• 删除myqsl 相关目录

rm -rf /var/lib/mysql
rm -rf /etc/my*

5. 创建mysql用户

useradd -r mysql -M -s /bin/false

2.1.2 下载安装

1. 安装编译工具

yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake

2. 下载源码包

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.27.tar.gz

3. 解压

tar xf mysql-boost-5.7.27.tar.gz

4. 编译安装前检查，及参数设置

cd  mysql-5.7.27

cmake . \
-DWITH_BOOST=boost/boost_1_59_0/ \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DSYSCONFDIR=/etc \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DPID_FILE=/usr/local/mysql/data/mysqld.pid \
-DINSTALL_MANDIR=/usr/share/man \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \

• -DWITH_BOOST ：依赖Boost库，告诉cmake Boost 库的位置。
• -DCMAKE_INSTALL_PREFIX ：安装路径
• -DSYSCONFDIR： 配置文件的路径
• -DMYSQL_DATADIR ：数据存储的路径
• -DPID_FILE mysqld ：服务的PID 路径
• -DINSTALL_MANDIR ：帮助文档的路径
• -DMYSQL_TCP_PORT： TCP端口
• -DMYSQL_UNIX_ADDR ：UNIX 套接字文件路径
• -DDEFAULT_CHARSET：默认字符集
• -DEXTRA_CHARSETS：所有额外的字符集
• -DDEFAULT_COLLATION ：默认字符集排序
• -DWITH_READLINE：让MySQL命令行支持命令历史、行编辑等交互功能
• -DWITH_SSL ：指定SSL支持来源为系统，而不是MySQL自带的版本
• -DWITH_EMBEDDED_SERVER：启用嵌入式服务器的构建。可以把MySQL嵌入应用
• -DENABLED_LOCAL_INFILE：允许从本地文件导入数据
• -DWITH_INNOBASE_STORAGE_ENGINE：启用 InnoDB 存储引擎

最终执行结果如下

....

-- CMAKE_CXX_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
-- Configuring done
-- Generating done
CMake Warning:
  Manually-specified variables were not used by the project:

    PID_FILE
    WITH_READLINE


-- Build files have been written to: /root/mysql-5.7.27
[root@source-mysql mysql-5.7.27]#

5. 编译安装，大概半个小时

make && make install

最终效果如下

...


-- Installing: /usr/local/mysql/support-files/mysql-log-rotate
-- Installing: /usr/local/mysql/support-files/magic
-- Installing: /usr/local/mysql/share/aclocal/mysql.m4
-- Installing: /usr/local/mysql/support-files/mysql.server
[root@source-mysql mysql-5.7.27]#

2.1.3 基础配置

1. 修改文件夹权限，初始化，会显示root密码

cd /usr/local/mysql
chown -R mysql.mysql .

./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

[root@source-mysql mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2025-10-13T12:00:08.000667Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-10-13T12:00:08.153045Z 0 [Warning] InnoDB: New log files created, LSN=45790
2025-10-13T12:00:08.179711Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2025-10-13T12:00:08.234856Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 29d529ae-a82c-11f0-9b66-000c29e8cb85.
2025-10-13T12:00:08.235246Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2025-10-13T12:00:08.559344Z 0 [Warning] CA certificate ca.pem is self signed.
2025-10-13T12:00:08.713531Z 1 [Note] A temporary password is generated for root@localhost: Btj/X,X18arj
[root@source-mysql mysql]#

2. 新建配置文件

vim /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock
# default-character-set = utf8		--- mysqlbinlog无法使用旧的字符集参数

[mysqld]
port = 3306
user = mysql
basedir = /usr/local/mysql 
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
character_set_server = utf8
pid-file=/usr/local/mysql/mysql.pid

3. 临时启动

/usr/local/mysql/bin/mysqld --user=mysql &

验证

ps aux |grep mysql

登录验证，需要上面的密码

/usr/local/mysql/bin/mysql -uroot -p

4. 修改root密码

/usr/local/mysql/bin/mysqladmin -u root -p'旧密码'  password '新密码'

5. 加入环境变量

cat >> /etc/profile <<EOL

export PATH=$PATH:/usr/local/mysql/bin
EOL

加载环境变量

source /etc/profile

6. 登录

mysql -uroot -p

2.1.4 systemd管理

1. 杀掉 mysql 进程

ps aux |grep mysql
kill mysql进程号

2. 编辑service 文件

cat > /etc/systemd/system/mysqld.service <<EOL
[Unit]
Description=MySQL Server

[Service]
ExecStart=/usr/local/mysql/bin/mysqld
User=mysql
Group=mysql
Restart=always
RestartSec=3
LimitNOFILE=infinity

[Install]
WantedBy=multi-user.target
EOL

3. 重载systemd配置文件

systemctl daemon-reload

4. 开机自启mysql，名字与上一步的名称对应

systemctl enable --now mysqld

5. 登录验证

mysql -uroot -p

2.2 YUM安装

hostnamectl set-hostname yum-mysql

2.2.1 安装前准备

1. 关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

2. 关闭SELinux

setenforce 0
sed -i 's/^SELINUX=*/SELINUX=disable/g' /etc/selinux/config

3. 配置yum

yum repolist

4. 清理之前的环境

• 清理之前的mysql 包

yum -y remove mariadb mariadb-server mariadb-libs mariadb-devel

• 删除mysql用户

userdel -r mysql

• 删除myqsl 相关目录

rm -rf /var/lib/mysql
rm -rf /etc/my*

2.2.2 YUM安装

1. 配置MSQL安装源

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

rpm -ivh mysql80-community-release-el7-3.noarch.rpm

yum -y install yum-utils
yum-config-manager --enable mysql57-community
yum-config-manager --disable mysql80-community

2. 导入GPG密钥，验证密钥文件存在

# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql
sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
ls -l /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

3. 安装

yum -y install mysql-community-server

...

Installed:
  mysql-community-server.x86_64 0:5.7.44-1.el7
Dependency Installed:
  mysql-community-client.x86_64 0:5.7.44-1.el7         mysql-community-common.x86_64 0:5.7.44-1.el7         mysql-community-libs.x86_64 0:5.7.44-1.el7

Complete!
[root@yum-mysql ~]# systemctl enable mysqld --now

4. 启动

systemctl enable mysqld --now

5. 查找临时密码

grep password /var/log/mysqld.log

[root@yum-mysql ~]# grep password /var/log/mysqld.log
2025-10-13T12:58:16.949323Z 1 [Note] A temporary password is generated for root@localhost: .z=8eWU*KW+V

6. 修改密码，编译安装的不能弱密码

mysqladmin -u root -p'Polish21..' password 'Root21..'
mysqladmin -u root -p'旧密码'  password '新密码'

或者登录mysql 后

alter user root@localhost identified by '新密码';

7. 验证

mysql -uroot -p

2.3 两种安装方式对比

两种安装方式对比	源码安装	YUM源安装
配置文件	/etc/my.cnf，可自定义	/etc/my.cnf
数据库目录	/var/local/mysql/data，可自定义	/var/lib/mysql
systemd管理	需要自行配置	默认支持
初始化	手动	自动
难易程度	一般	简单
安装所需时间	大约半个小时	几分钟

三、CentOS7安装MySQL80/84

hostnamectl set-hostname yum-mysql8

3.1 YUM安装

1. 卸载旧的组件

yum remove mariadb* -y

2. 添加MySQL官方的仓库

从MySQL官网 或MySQL repo服务器找对应系统的 rpm 源

• mysql80

wget https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm
yum localinstall mysql80-community-release-el7-11.noarch.rpm -y

• mysql84

https://dev.mysql.com/get/mysql84-community-release-el7-2.noarch.rpm

3. 验证已经添加仓库

yum repolist enabled | grep mysql

[root@yum-mysql8 ~]# yum repolist enabled | grep mysql

mysql-connectors-community/x86_64 MySQL Connectors Community                 286
mysql-tools-community/x86_64      MySQL Tools Community                      116
mysql80-community/x86_64          MySQL 8.0 Community Server                 579
[root@yum-mysql8 ~]#

4. 安装 MySQL-Server

yum install mysql-community-server -y

5. 开机启动

systemctl start mysqld
systemctl enable mysqld

6. 验证启动成功

systemctl status mysqld

启动成功效果

[root@yum-mysql8 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2025-10-14 10:06:58 CST; 17s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 4928 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─4928 /usr/sbin/mysqld

Oct 14 10:06:53 yum-mysql8 systemd[1]: Starting MySQL Server...
Oct 14 10:06:58 yum-mysql8 systemd[1]: Started MySQL Server.
[root@yum-mysql8 ~]#

7. 从日志中获取临时密码

grep 'temporary password' /var/log/mysqld.log

[root@yum-mysql8 ~]# grep 'temporary password' /var/log/mysqld.log
2025-10-14T02:06:54.878116Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: FkSpsqblF1.a
[root@yum-mysql8 ~]#

8. 登录mysql 后改密码

mysql -u root -p

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root21..';

9. 验证新密码

mysql -u root -p'Root21..'

[root@yum-mysql8 ~]# mysql -u root -p'Root21..'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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>

四、debian系安装MySQL

选项	说明	适用场景
mysql-8.0	MySQL 8.0 版本的普通服务器	大多数生产环境，稳定可靠
mysql-innovation	MySQL 最新创新版本（实验性功能）	测试新特性，不建议生产环境
mysql-8.4-lts	MySQL 8.4 LTS（长期支持）	适合希望用新功能但又要稳定的环境
mysql-cluster-8.0	MySQL Cluster 8.0	用于需要高可用和分布式集群的场景
mysql-cluster-innovation	MySQL Cluster 最新创新版本	测试分布式集群新功能
mysql-cluster-8.4-lts	MySQL Cluster 8.4 LTS	高可用集群的长期支持版本
None	不安装服务器，只配置客户端等工具	只想用 MySQL 客户端或工具，不安装数据库

1. MySQL官网仓库，安装deb 包时可以文本图形化选择不同的数据库版本。

wget https://dev.mysql.com/get/mysql-apt-config_0.8.32-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.32-1_all.deb
sudo apt update

2. 安装最新版本

sudo apt install mysql-server

3. 安全设置

sudo mysql_secure_installation

ubuntu@sg-oracle-2c12g-01:~$ sudo mysql_secure_installation

Securing the MySQL server deployment.
## 1. 首先需要验证root密码
Enter password for user root:

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file
# 2. 是否启用“密码强度验证组件（VALIDATE PASSWORD）按0，1，2来选择不同的密码强度
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Using existing password for root.

Estimated strength of the password: 100
# 3. 是否要 修改 root 用户的密码。
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

 ... skipping.
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.
# 4. 是否删除匿名用户。
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.
# 5. 是否禁止 root 用户远程登录。
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.

# 6. 默认会有一个名为 test 的数据库，是否删除它
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.
# 7. 是否立即重新加载权限表（privilege tables
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
ubuntu@sg-oracle-2c12g-01:~$

4. 设置密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
FLUSH PRIVILEGES;

5. 登录验证

mysql -u root -p

五、封面图