本文记录在最新的Ubuntu24.04 LTS系统上安装mysql的过程
安装命令
sudo apt install mysql-server安装成功后会自动启动mysql
root@zx-VirtualBox:/home/zx# systemctl status mysql● mysql.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled) Active: active (running) since Mon 2024-05-13 22:13:16 CST; 45s ago Process: 5258 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Main PID: 5266 (mysqld) Status: "Server is operational" Tasks: 38 (limit: 7027) Memory: 365.3M (peak: 379.8M) CPU: 1.712s CGroup: /system.slice/mysql.service └─5266 /usr/sbin/mysqld查看MySQL版本信息及端口占用，Ubuntu 24.0 LTS目前适配的是mysql 8.0版本
root@zx-VirtualBox:/home/zx# mysql --versionmysql Ver 8.0.36-2ubuntu3 for Linux on x86_64 ((Ubuntu))root@zx-VirtualBox:/home/zx# netstat -nlutp | grep mysqltcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 4354/mysqld tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4354/mysqldmysql命令进入数据库操作界面，查询数据库用户对不同数据库的验证方法
root@zx-VirtualBox:/home/zx# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.36-2ubuntu3 (Ubuntu)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;+------------------+------------------------------------------------------------------------+-----------------------+-----------+| user | authentication_string | plugin | host |+------------------+------------------------------------------------------------------------+-----------------------+-----------+| debian-sys-maint | $A$005$c%G%XZCy~)jVneEgd9ztLU.Lwy1rAlwVY6/AdXmEmNsit1QOR7DWo9 | caching_sha2_password | localhost || mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost || mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost || mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost || root | | auth_socket | localhost |+------------------+------------------------------------------------------------------------+-----------------------+-----------+5 rows in set (0.00 sec)将root用户改为密码验证，再次查询发现root用户已经修改为mysql_native_password方式验证
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;+------------------+------------------------------------------------------------------------+-----------------------+-----------+| user | authentication_string | plugin | host |+------------------+------------------------------------------------------------------------+-----------------------+-----------+| debian-sys-maint | $A$005$c%G%XZCy~)jVneEgd9ztLU.Lwy1rAlwVY6/AdXmEmNsit1QOR7DWo9 | caching_sha2_password | localhost || mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost || mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost || mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost || root | *B9362550CA134A8AD5D4F0B93DFAC83B129ED543 | mysql_native_password | localhost |+------------------+------------------------------------------------------------------------+-----------------------+-----------+5 rows in set (0.00 sec)再次进入mysql需要指定用户名并输入密码才能进入，mysql -u root -p
root@zx-VirtualBox:/home/zx# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 12Server version: 8.0.36-2ubuntu3 (Ubuntu)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>配置远程连接，编辑配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，修改bind-address为0.0.0.0
bind-address = 0.0.0.0重启mysql服务
systemctl restart mysql还需要创建一个从任意主机登录的root用户并赋权，默认只允许localhost登录，修改配置后记得执行flush privileges命令刷新数据库，否则后续sql执行可能会失败
mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select host,user from user;+-----------+------------------+| host | user |+-----------+------------------+| localhost | debian-sys-maint || localhost | mysql.infoschema || localhost | mysql.session || localhost | mysql.sys || localhost | root |+-----------+------------------+5 rows in set (0.00 sec)mysql>创建root用户，因为user表使用了复合主键user、host，只要求user+host是唯一的，所以可以建立两个root用户
mysql> create user 'root'@'%' identified by 'zx410012';Query OK, 0 rows affected (0.02 sec)mysql> select host,user from user;+-----------+------------------+| host | user |+-----------+------------------+| % | root || localhost | debian-sys-maint || localhost | mysql.infoschema || localhost | mysql.session || localhost | mysql.sys || localhost | root |+-----------+------------------+6 rows in set (0.00 sec)为root用户赋权
mysql> grant all privileges on *.* to 'root'@'%' with grant option;Query OK, 0 rows affected (0.03 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)之后使用navicat工具远程连接mysql成功
不过，发现使用navicat不管是测试连接还是连接mysql的过程反应都比较慢，几秒钟之后才会成功，这个原因是远程连接mysql时会尝试解析域名，这个过程会导致连接过程比较慢，解决的办法是编辑配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，在最后添加一个行配置，重启mysql之后再次尝试连接就快到飞起啦。
skip-name-resolve参考文献[1]. https://zhuanlan.zhihu.com/p/128148271
