15-MySQL安装与配置指南

一、为什么选择MySQL？

MySQL是世界上最流行的开源关系型数据库管理系统之一，广泛应用于Web开发、企业应用等领域。

1.1 核心优势

特性 说明
开源免费 GPL开源协议，免费使用
性能卓越 高并发处理能力强
生态完善 丰富的工具和驱动支持
易于使用 SQL标准语法，学习成本低
跨平台 支持Windows、Linux、macOS

1.2 应用场景

MySQL典型应用场景：
Web网站后端数据库
电商平台订单/商品数据存储 │
博客/CMS系统数据管理

二、Windows系统安装

2.1 下载地址

2.2 安装步骤

1
2
3
4
5
6
7
8
9
10
11
# 1. 下载Windows Installer (.msi格式)

# 2. 双击安装包，选择 "Developer Default" 或 "Full"

# 3. 设置root用户密码（牢记此密码）

# 4. 配置环境变量（推荐）
#    此电脑 → 属性 → 高级系统设置 → 环境变量
#    在Path中添加: C:\Program Files\MySQL\MySQL Server 8.0\bin

# 5. 完成安装

2.3 验证安装

1
2
3
4
5
6
# 打开命令提示符（CMD）或PowerShell

mysql --version

mysql -u root -p
# 输入密码，成功连接后会显示 mysql>

三、Linux系统安装（Ubuntu）

1
2
3
4
5
6
7
8
9
10
11
12
# 1. 更新软件源
sudo apt update

# 2. 安装MySQL服务器
sudo apt install mysql-server

# 3. 启动并设置开机自启
sudo systemctl start mysql
sudo systemctl enable mysql

# 4. 运行安全配置向导
sudo mysql_secure_installation

安全配置向导选项：

  • 设置root密码
  • 删除匿名用户
  • 禁止root远程登录
  • 删除test数据库
  • 重新加载权限表
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
[root@localhost ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): <–-初次运行直接回车
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n]    //是否设置root用户密码，输入Y并回车或直接回车

New password:               //设置root用户的密码

Re-enter new password:      //再次输入你设置的密码
Password updated successfully!
Reloading privilege tables..
… Success!

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? [Y/n]   //是否删除匿名用户,生产环境建议删除，所以直接回车
… 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? [Y/n] //是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
… 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? [Y/n] //是否删除test数据库,直接回车
- 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? [Y/n] //是否重新加载权限表，直接回车
… Success!
Cleaning up…
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

3.1 验证安装

1
2
3
4
5
# 查看服务状态
sudo systemctl status mysql

# 连接MySQL
mysql -u root -p

四、常用命令速查

4.1 服务管理

1
2
3
4
5
6
7
8
9
# Linux
sudo systemctl start mysql    # 启动
sudo systemctl stop mysql     # 停止
sudo systemctl restart mysql  # 重启
sudo systemctl status mysql   # 状态

# Windows
net start mysql
net stop mysql

4.2 数据库操作

1
2
3
4
SHOW DATABASES;                              -- 查看所有数据库
CREATE DATABASE mydb;                        -- 创建数据库
USE mydb;                                    -- 使用数据库
DROP DATABASE mydb;                          -- 删除数据库

4.3 表操作

1
2
3
4
5
6
7
8
9
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);                                           -- 创建表

SHOW TABLES;                                 -- 查看所有表
DESC users;                                  -- 查看表结构
DROP TABLE users;                            -- 删除表

4.4 数据操作

1
2
3
4
5
INSERT INTO users (username, email) VALUES ('张三', 'zhangsan@example.com');  -- 插入
SELECT * FROM users;                         -- 查询
SELECT * FROM users WHERE id = 1;           -- 条件查询
UPDATE users SET email = 'new@example.com' WHERE id = 1;                      -- 更新
DELETE FROM users WHERE id = 1;             -- 删除

五、用户与权限

5.1 创建用户

1
2
3
4
5
-- 创建本地用户
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password123';

-- 创建远程用户（允许任意主机连接）
CREATE USER 'myuser'@'%' IDENTIFIED BY 'password123';

5.2 授权管理

1
2
3
4
5
6
7
8
-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost';

-- 授予特定数据库权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'myuser'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

5.3 查看用户

1
SELECT user, host FROM mysql.user;

六、远程连接配置

6.1 开放防火墙端口

1
2
sudo ufw allow 3306/tcp
sudo ufw reload

6.2 创建远程访问用户

1
2
3
CREATE USER 'remote'@'%' IDENTIFIED BY 'YourP@ss123';
GRANT ALL PRIVILEGES ON *.* TO 'remote'@'%';
FLUSH PRIVILEGES;

6.3 远程连接测试

1
mysql -h 192.168.1.100 -u remote -p

七、常见问题解决

7.1 忘记root密码

1
2
3
4
5
6
7
8
9
10
11
12
13
# 1. 停止MySQL
sudo systemctl stop mysql

# 2. 跳过权限表启动
sudo mysqld --skip-grant-tables --user=mysql &

# 3. 连接并重置密码
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

# 4. 重启服务
sudo systemctl restart mysql

7.2 连接被拒绝

1
2
3
4
5
# 检查服务状态
sudo systemctl status mysql

# 检查端口监听
netstat -tlnp | grep 3306

7.3 中文乱码

1
2
3
4
# 在 /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld]段添加
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
1
sudo systemctl restart mysql

八、配置文件说明

8.1 配置文件位置

系统 路径
Windows C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
Linux /etc/mysql/mysql.conf.d/mysqld.cnf

8.2 常用配置

1
2
3
4
5
[mysqld]
port = 3306
bind-address = 0.0.0.0           -- 允许远程连接
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

九、总结

阶段 命令
安装 apt install mysql-server
启动 systemctl start mysql
连接 mysql -u root -p
创建用户 CREATE USER 'user'@'%' IDENTIFIED BY 'pwd'
授权 GRANT ALL ON *.* TO 'user'@'%'

💡 提示：开发环境建议设置强密码，生产环境请配置定期备份！

工具配置
#MySQL #数据库 #环境配置
15-MySQL安装与配置指南
https://r0f2.my/post/15-mysql-install-and-config-guide/
作者
JA
发布于
2026年2月12日
许可协议