MySQL安装(Ubuntu版)

1. 软件的下载

下载地址官网：https://downloads.mysql.com/archives/community/

2. 上传解压

2.1 上传到/opt/software目录

jack@mysql01:/opt/software$ ll
总计 991460
drwxr-xr-x 2 jack jack      4096  4月  8 13:36 ./
drwxr-xr-x 4 root root        36  4月  8 13:10 ../
-rw-r--r-- 1 jack jack 507617280  4月  8 13:24 mysql-server_8.0.41-1ubuntu24.04_amd64.deb-bundle.tar

2.2 解压压缩包

jack@mysql01:/opt/software$ tar -xvf mysql-server_8.0.41-1ubuntu24.04_amd64.deb-bundle.tar
jack@mysql01:/opt/software$ ll
总计 991460
drwxr-xr-x 2 jack jack      4096  4月  8 13:36 ./
drwxr-xr-x 4 root root        36  4月  8 13:10 ../
-rw-r--r-- 1 jack jack   1477296 12月 16 19:28 libmysqlclient21_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack  28706080 12月 16 19:28 libmysqlclient-dev_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack     57998 12月 16 19:28 mysql-client_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack     59308 12月 16 19:28 mysql-common_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack   2045888 12月 16 19:28 mysql-community-client_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack   2131698 12月 16 19:28 mysql-community-client-core_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack   1377038 12月 16 19:28 mysql-community-client-plugins_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack     69616 12月 16 19:28 mysql-community-server_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack  27981710 12月 16 19:28 mysql-community-server-core_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack  34474704 12月 16 19:28 mysql-community-server-debug_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack 400278684 12月 16 19:29 mysql-community-test_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack   8824740 12月 16 19:29 mysql-community-test-debug_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack     57990 12月 16 19:29 mysql-server_8.0.41-1ubuntu24.04_amd64.deb
-rw-r--r-- 1 jack jack 507617280  4月  8 13:24 mysql-server_8.0.41-1ubuntu24.04_amd64.deb-bundle.tar
-rw-r--r-- 1 jack jack     58002 12月 16 19:29 mysql-testsuite_8.0.41-1ubuntu24.04_amd64.deb
[root@hadoop105 mysql-8.0.35]# mkdir data logs
[root@hadoop105 mysql-8.0.35]# touch logs/mysql.log
[root@hadoop105 mysql-8.0.35]# touch logs/mysql.pid

3. 安装MySQL

## 检查是否含有旧版的mysql
jack@mysql01:/opt/software$ dpkg -l | grep mysql
jack@mysql01:/opt/software$ dpkg -l | grep mariadb
## 安装依赖
jack@mysql02:/opt/software$ sudo apt install libmecab2 libaio1t64
## 安装mysql-community-server，需要安装顺序
jack@mysql01:/opt/software$ sudo dpkg -i mysql-community-client-plugins_8.0.41-1ubuntu24.04_amd64.deb
jack@mysql01:/opt/software$ sudo dpkg -i mysql-common_8.0.41-1ubuntu24.04_amd64.deb
jack@mysql01:/opt/software$ sudo dpkg -i libmysqlclient21_8.0.41-1ubuntu24.04_amd64.deb
jack@mysql01:/opt/software$ sudo dpkg -i mysql-community-client-core_8.0.41-1ubuntu24.04_amd64.deb
jack@mysql01:/opt/software$ sudo dpkg -i mysql-community-client_8.0.41-1ubuntu24.04_amd64.deb
jack@mysql01:/opt/software$ sudo dpkg -i mysql-client_8.0.41-1ubuntu24.04_amd64.deb
jack@mysql01:/opt/software$ sudo dpkg -i mysql-community-server-core_8.0.41-1ubuntu24.04_amd64.deb
jack@mysql01:/opt/software$ sudo dpkg -i mysql-community-server_8.0.41-1ubuntu24.04_amd64.deb

这时会弹出字符操作界面，设置root用户密码：
然后使用推荐的密码策略,然后继续安装剩下的安装包。

jack@mysql01:/opt/software$ sudo dpkg -i mysql-server_8.0.41-1ubuntu24.04_amd64.deb

安装过程中，deb包会自动创建mysql用户组和mysql用户。

jack@mysql01:/etc/mysql$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Tue 2025-04-08 14:05:50 CST; 22min ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 20958 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 20998 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 18616)
     Memory: 361.9M (peak: 377.5M)
        CPU: 14.362s
     CGroup: /system.slice/mysql.service
             └─20998 /usr/sbin/mysqld

4月 08 14:05:48 mysql01 systemd[1]: Starting mysql.service - MySQL Community Server...
4月 08 14:05:50 mysql01 systemd[1]: Started mysql.service - MySQL Community Server.

可以看到默认数据库已经是启动的。

4 配置数据库

4.1 配置mysql服务端

jack@mysql01:/etc/mysql$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在mysqld节点下增加如下内容：
[mysqld]
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id=1
#服务端口号 默认3306
port=3306
#开启慢查询日志
slow_query_log=1
#慢查询时间 超过3秒则为慢查询
long_query_time=3
#慢查询日志目录
slow_query_log_file=/var/log/mysql/mysql-slow.log
#数据库默认字符集,主流字符集支持一些特殊表情符号(特殊表情符占用4个字节)
character-set-server=utf8mb4
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server=utf8mb4_general_ci
#最大连接数
max_connections=400

4.2 配置数据库自启动

jack@mysql01:/etc/mysql$ sudo systemctl enable mysql

5. 客户端连接

使用mysql命令进行连接

## 使用之前设置的密码本地登陆
jack@mysql01:/etc/mysql$ mysql -uroot -proot
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 9
Server version: 8.0.41 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> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

6. 远程登录设置

-- 切换到mysql数据库
mysql> use mysql; 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed 
-- 查询用户表
mysql> select Host,User from user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)
-- 授权root用户任何IP访问
mysql> update user set host = '%' where user ='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

10. 客户端个性化配置

每次连接还可以设置连接提示，提示当前在连接信息,编辑my.cnf文件

## 增加mysql节点内容
[mysql]
prompt=(\\u@\\h)[\\d]>\\

重新登录mysql:

bash-5.1# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 472
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.

(root@localhost)[(none)]>use gmall;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(root@localhost)[gmall]>

12. 远程客户端连接

12.1 使用DBeaver连接

DBeaver是一个通用的数据库管理工具和SQL客户端，支持所有流行的数据库：MySQL、PostgreSQL、SQLite、Oracle、DB2、SQL Server、Sybase、MS Access等。 如果出现连接出错： 连接出现 Public Key Retrieval is not allowed, 这是因为MySQL 8及以上版本对客户端连接安全性要求更高，默认使用密码插件caching_sha2_password时，要求加密通信并且不允许通过不安全的方式获取服务器的公钥。解决办法有两个：

1. 在客户端JDBC的url中添加的参数：allowPublicKeyRetrieval=true和useSSL=false
2. 调整当前连接用户的默认密码插件，换成mysql_native_password即可

-- 使用密码策略为mysql_native_password
ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;