文章目录
- 1.下载合适版本
- 2.安装
- 3.初始密码，并允许远程登录
- 4.终极脚本
- 5.其他常用sql
概述：本文介绍 Linux 下如何安装 mysql 9.1 并设置初始密码，不想看步骤内容，安装好后直接到脚本部分，复制脚本到mysql服务器设置即可。
1.下载合适版本
https://dev.mysql.com/downloads/mysql/
- 例如我的下载：
https://cdn.mysql.com//Downloads/MySQL-9.1/mysql-server_9.1.0-1ubuntu22.04_amd64.deb-bundle.tar
2.安装
例 UBuntu 22.4：
- 上传到服务器解压，中间缺什么依赖装什么即可
[jn@jn mysql]$ ls
mysql-server_9.1.0-1ubuntu22.04_amd64.deb-bundle.tar
[jn@jn mysql]$ tar -xvf mysql-server_9.1.0-1ubuntu22.04_amd64.deb-bundle.tar
libmysqlclient24_9.1.0-1ubuntu22.04_amd64.deb
libmysqlclient-dev_9.1.0-1ubuntu22.04_amd64.deb
mysql-client_9.1.0-1ubuntu22.04_amd64.deb
mysql-common_9.1.0-1ubuntu22.04_amd64.deb
mysql-community-client_9.1.0-1ubuntu22.04_amd64.deb
mysql-community-client-core_9.1.0-1ubuntu22.04_amd64.deb
mysql-community-client-plugins_9.1.0-1ubuntu22.04_amd64.deb
mysql-community-server_9.1.0-1ubuntu22.04_amd64.deb
mysql-community-server-core_9.1.0-1ubuntu22.04_amd64.deb
mysql-community-server-debug_9.1.0-1ubuntu22.04_amd64.deb
mysql-community-test_9.1.0-1ubuntu22.04_amd64.deb
mysql-community-test-debug_9.1.0-1ubuntu22.04_amd64.deb
mysql-server_9.1.0-1ubuntu22.04_amd64.deb
mysql-testsuite_9.1.0-1ubuntu22.04_amd64.deb
[jn@jn mysql]$ sudo dpkg -i *.deb
(Reading database ... 298729 files and directories currently installed.)
Preparing to unpack libmysqlclient24_9.1.0-1ubuntu22.04_amd64.deb ...
Unpacking libmysqlclient24:amd64 (9.1.0-1ubuntu22.04) over (9.1.0-1ubuntu22.04) ...
Preparing to unpack libmysqlclient-dev_9.1.0-1ubuntu22.04_amd64.deb ...
...
...
Setting up mysql-server (9.1.0-1ubuntu22.04) ...
Setting up mysql-testsuite (9.1.0-1ubuntu22.04) ...
Processing triggers for libc-bin (2.35-0ubuntu3.8) ...
Processing triggers for man-db (2.10.2-1) ...
[jn@jn mysql]$
CentOS 貌似如下:
rpm -ivh *.rpm
-i ：安装
-v ：可视化
-h ：显示进度
3.初始密码，并允许远程登录
默认无密码，需手动设置
[jn@jn mysql]$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 9.1.0 MySQL Community Server - GPLCopyright (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.mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'dujn123456';
Query OK, 0 rows affected (0.00 sec)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 -ADatabase changed
mysql> UPDATE user SET host='%' WHERE user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql> quit
Bye
[jn@jn mysql]$ sudo systemctl restart mysql.service
[jn@jn mysql]$ sudo mysql -uroot -pdujn123456 -h 192.168.3.35
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: 9.1.0 MySQL Community Server - GPLCopyright (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.mysql> quit
Bye
[jn@jn mysql]$
解释上述操作：
-
修改密码：ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH caching_sha2_password BY ‘dujn123456’;
-
切换数据库：use mysql;
-
允许所有IP访问：UPDATE user SET host=‘%’ WHERE user=‘root’;
-
刷新权限和用户数据：FLUSH PRIVILEGES;
(Re-reads the privileges from the grant tables in the mysql system schema.)
详见 https://dev.mysql.com/doc/refman/8.4/en/flush.html#flush-privileges
-
host 和 user
host 字段的常用设置选项
host 字段可以配置特定的主机名或 IP 地址，也可以使用通配符来控制访问范围：
| User Value | Host Value | Permissible Connections |
|---|---|---|
| ‘fred’ | ‘h1.example.net’ | fred, connecting from h1.example.net |
| ‘’ | ‘h1.example.net’ | Any user, connecting from h1.example.net |
| ‘fred’ | ‘%’ | fred, connecting from any host |
| ‘’ | ‘%’ | Any user, connecting from any host |
| ‘fred’ | ‘%.example.net’ | fred, connecting from any host in the example.net domain |
| ‘fred’ | ‘x.example.%’ | fred, connecting from x.example.net, x.example.com, x.example.edu, and so on |
| ‘fred’ | ‘198.51.100.177’ | fred, connecting from the host with IP address 198.51.100.177 |
| ‘fred’ | ‘198.51.100.%’ | fred, connecting from any host in the 198.51.100 class C subnet |
| ‘fred’ | ‘198.51.100.0/255.255.255.0’ | Same as previous example |
详细可见： https://dev.mysql.com/doc/refman/8.4/en/connection-access.html
4.终极脚本
setup_mysql.sh
#!/bin/bash# 检查是否提供了密码参数
if [ -z "$1" ]; thenecho "请提供新密码作为参数。"echo "使用示例: $0 your_pass_word"exit 1
fiNEW_PASSWORD=$1# 运行 SQL 命令
sudo mysql << EOF
-- 修改 root 用户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '$NEW_PASSWORD';-- 切换到 mysql 数据库
USE mysql;-- 允许所有 IP 访问 root 用户
UPDATE user SET host='%' WHERE user='root';-- 刷新权限
FLUSH PRIVILEGES;
EOF# 检查是否成功执行
if [ $? -eq 0 ]; thenecho "MySQL 配置已更新。"
elseecho "配置失败。"
fi
运行脚本即可：
[jn@jn mysql]$ sudo sh setup_mysql.sh dujingning123
MySQL 配置已更新。
[jn@jn mysql]$ sudo mysql -uroot -pdujingning123 -h 192.168.3.35
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 11
Server version: 9.1.0 MySQL Community Server - GPLCopyright (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.mysql> quit
Bye
[jn@jn mysql]$
5.其他常用sql
- 查看当前的host及用户信息匹配顺序，先host顺序匹配、后user顺序匹配
mysql> SELECT authentication_string, host, user,account_locked FROM user ORDER BY host desc ,user desc;
+------------------------------------------------------------------------+-----------+------------------+----------------+
| authentication_string | host | user | account_locked |
+------------------------------------------------------------------------+-----------+------------------+----------------+
| $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost | mysql.sys | Y |
| $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost | mysql.session | Y |
| $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost | mysql.infoschema | Y |
| $A$005$,:A1Pu]7
W{QOZinSOkbuGwF8uhFbOq2nmMd76bqbMm0f1JCcijX1pa6 | % | root | N |
+------------------------------------------------------------------------+-----------+------------------+----------------+
4 rows in set (0.00 sec)mysql>
- 看当前用户连接方式 和 当前用户认证方式
mysql> select user(),CURRENT_USER();
+-------------------+----------------+
| user() | CURRENT_USER() |
+-------------------+----------------+
| root@192.168.3.35 | root@% |
+-------------------+----------------+
1 row in set (0.00 sec)mysql>
- 修改字段为索引
CREATE UNIQUE INDEX idx_myTable_imei ON myTable(name); - 移除字段为索引
DROP INDEX idx_myTable_imei ON myTable;
