MySql学习笔记

mysql安装

centos安装

点击跳转下载链接

centos选择Red Hat Enterprise Linux

历史版本选择Archives

将文件拷贝到centos下后使用命令安装

解压

1
tar -xvf 文件名

解压后会发现很多rpm包，逐个顺序安装会很慢还会产生依赖报错

1
rpm -ivh 文件名

全部安装

1
rpm  -ivh  *.rpm --nodeps --force

使用whereis mysql，发现目录

1
2
[root@localhost mysql]# whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

初始化数据库

1
[root@localhost etc]# mysqld --initialize --console
1
2
systemctl start mysqld #启动服务
systemctl status mysqld #查看状态

启动数据库报错

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

因为/var/lib/mysql目录权限不够，按照下列命令顺序执行就行

1
2
3
4
5
6
cd /var/lib/mysql
setenforce 0
chown -R mysql:mysql /var/lib/mysql
chmod -R 777 /var/lib/mysql
systemctl start mysqld.service
systemctl status mysqld

或者

1
chown -R mysql:mysql /var/lib/mysql/

安装完成后，会给root一个随机密码，用cat命令查看

1
cat /var/log/mysqld.log

可以看到密码是T!>/)U7k)d/V

1
2
3
4
5
[root@localhost mysql]# cat /var/log/mysqld.log
2025-03-26T01:59:19.670221Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.28) initializing of server in progress as process 78992
2025-03-26T01:59:19.683552Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-03-26T01:59:20.190253Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-03-26T01:59:20.780158Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: T!>/)U7k)d/V

使用默认密码登录mysql，然后修改默认密码

1
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

降低密码复杂度

重置默认密码后执行，查看当前密码策略

1
SHOW VARIABLES LIKE 'validate_password%';

validate_password_policy：密码策略，默认值为MEDIUM。可以设置为LOW、MEDIUM、STRONG或者自定义。例如，将其设置为LOW以降低密码复杂性要求。

1
SET GLOBAL validate_password_policy = LOW;

不同策略的要求：
0/LOW：只验证长度；
1/MEDIUM：验证长度、数字、大小写、特殊字符；默认值。
2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；

validate_password_length：密码最小长度，默认值为8。根据需要修改最小密码长度。

1
SET GLOBAL validate_password_length = 6;

validate_password_number_count：密码中的数字要求，默认值为1。可以增加或减少数字的要求。

1
SET GLOBAL validate_password_number_count = 1;

validate_password_special_char_count：密码中特殊字符的要求，默认值为1。可以增加或减少特殊字符的要求。

1
SET GLOBAL validate_password_special_char_count = 1;

validate_password_mixed_case_count：密码中大写字母和小写字母的要求，默认值为1。可以增加或减少大写字母和小写字母的要求。

1
SET GLOBAL validate_password_mixed_case_count = 1;

修改配置文件以使修改的密码策略永久生效。
打开MySQL的配置文件（通常是my.cnf或my.ini），将以下内容添加到文件中：

1
2
3
4
5
validate_password_policy=LOW
validate_password_length=6
validate_password_number_count=1
validate_password_special_char_count=1
validate_password_mixed_case_count=1

重启MySQL服务以应用更改：

1
sudo systemctl restart mysqld

windows安装

【手把手教你安装MySQL(最新版本安装)】 https://www.bilibili.com/video/BV1jcabemEr7/?share_source=copy_web&vd_source=e016d27d3a863600d631a6cfc5c3f208

默认windows安装的mysql服务名字为mysql80

命令提示符打开和关闭

1
2
net start mysql80
net stop mysql80

Ubuntu安装

1
sudo apt install mysql-server
  • 启动systemctl start mysql
  • 停止systemctl stop mysql
  • 查看运行状态systemctl status mysql

root密码

linux系统默认安装完成后默认是有密码的，密码和账户名在/etc/mysql/debian.cnf

默认的用户和密码不方便记忆，我们可以将其更改。

连接到mysql服务后可以用下面的语句修改root用户和密码

将密码改为admin123

首先进入数据库use mysql;

此命令只支持5.7.9以上的版本

1
alter user 'root'@'localhost' identified with mysql_native_password by 'admin123';

然后使用flush privileges刷新权限，然后就可以使用新的密码来登录了

版本号查看

操作系统 查看

1
mysql --version;

mysql内查看

1
2
status		#查看日志内的版本号
select version();	#直接查看版本号

mysql登录

本地登录命令

1
mysql -u用户名 -p密码

远程登录命令

1
mysql -u用户名 -p密码 -l服务器

关于密码

默认安装mysql会带有密码，密码存放在

1
cat /etc/mysql/debian.cnf

跳过密码

修改文件

1
vim /etc/my.cnf

添加

1
skip-grant-tables

登录密码不写直接回车

进入后flush privileges;刷新权限

进入mysql后重置密码

1
alter user 'root'@'localhost' identified by 'admin123';

修改密码

因为password字段和password函数都被废弃了，密码的加密方式也发生了变化所以需要用新的命令

mysql version>5.7.9

1
alter user 'root'@'localhost' identified with mysql_native_password by '123456';

mysql version<5.7.9

1
update user set password=PASSWORD("123456") where user='root';
1
update user set authentication_string=PASSWORD("admin123") where user='root';

远程登录

很多时候数据库是需要单独安装在服务器上的，这个时候我们会使用workbench或者navicat这样的可视化工具来连接远程客户端的mysql服务，一般在刚安装好之后经常会提示无法连接，这个时候有许多常见的原因，

3306端口

远程登录mysql需要开放3306端口

配置远程访问

登录后，执行以下步骤：

步骤1：创建用于远程连接的用户

sql

1
2
3
4
5
6
7
8
-- 创建新用户（推荐使用非root用户进行远程连接）
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'Remote@123456';

-- 授予所有权限（生产环境建议按需授予）
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

步骤2：检查用户是否创建成功

sql

1
2
3
4
5
-- 查看用户列表
SELECT user, host FROM mysql.user;

-- 查看新用户的权限
SHOW GRANTS FOR 'remote_user'@'%';

4. 完整的配置流程（从登录到配置完成）

如果您想从头开始操作，这里是完整的命令序列：

bash

1
2
3
4
# 1. 使用当前密码登录
mysql -u root -p123456

# 2. 在MySQL命令行中执行以下命令：

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 立即修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@123456';

-- 刷新权限
FLUSH PRIVILEGES;

-- 创建远程连接用户
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'Remote@123456';

-- 授予权限
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户确认
SELECT user, host, authentication_string FROM mysql.user WHERE user IN ('root', 'remote_user');

-- 退出
EXIT;

5. 配置MySQL允许远程连接

bash

1
2
# 编辑MySQL配置文件
vi /etc/my.cnf

在 [mysqld] 部分添加或修改以下行：

ini

1
2
[mysqld]
bind-address = 0.0.0.0

保存并退出。

6. 配置防火墙

bash

1
2
3
4
5
6
# 开放3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

# 查看端口是否开放
firewall-cmd --list-ports

7. 重启MySQL服务

bash

1
2
3
4
5
# 重启MySQL
systemctl restart mysqld

# 检查服务状态
systemctl status mysqld

8. 测试远程连接

首先在服务器上测试新用户：

bash

1
2
# 测试新用户连接
mysql -u remote_user -pRemote@123456 -e "SELECT '本地连接成功!' as Message;"

如果本地连接成功，然后您可以：

  1. 获取服务器IP地址：

    bash

    1
    ip addr show

  2. 在Navicat中配置：

    • 主机：服务器IP地址
    • 端口：3306
    • 用户名：remote_user
    • 密码：Remote@123456

9. 如果遇到连接问题，检查以下事项

检查MySQL监听地址：

bash

1
netstat -tlnp | grep mysql

应该显示：0.0.0.0:3306

检查错误日志：

bash

1
tail -20 /var/log/mysqld.log

临时关闭SELinux（仅用于测试）：

bash

1
2
3
4
5
6
7
# 查看状态
getenforce

# 如果是Enforcing，临时禁用
setenforce 0

# 测试连接

永久禁用SELinux（如果需要）：

bash

1
2
3
vi /etc/selinux/config
# 修改为：SELINUX=disabled
# 重启系统生效

10. 安全建议

为了安全起见，建议：

  1. 不要使用root用户远程连接
  2. 限制远程用户的权限：

sql

1
2
3
4
5
6
7
8
-- 创建仅具有必要权限的用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass@123';

-- 仅授予特定数据库的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'app_user'@'%';

-- 刷新权限
FLUSH PRIVILEGES;
  1. 使用更严格的访问控制：

sql

1
2
3
-- 只允许特定IP连接
CREATE USER 'secure_user'@'192.168.1.%' IDENTIFIED BY 'Secure@123';
GRANT ALL ON *.* TO 'secure_user'@'192.168.1.%';

查看端口

1
show variables like '%port%';

开放3306端口

首先查看端口是否打开

1
netstat -an|grep 3306

查看root用户权限

1
select user,host from user where user='root';

5.7.31版本select host,user,authentication_string from user;

5.7版本以前用select host,user,password from user;

5.7版本以后用select host,user,authentication_string from user;

user host
root localhost

如果host字段为localhost表示只能在本地访问，可以通过命令将localhost改为%，这样就可以在任何地方访问了

注意:有安全风险

给root用户远程连接权限

1
update user set host='%' where user='root';

或

实现远程连接(授权法)

允许root用户从任意ip登录（任意Ip用%表示），权限为ALL PRIVILEGES，密码为123456（密码可以改成自己喜欢的）

1
grant all privileges  on *.* to root@'%' identified by "123456";
  • GRANT ALL PRIVILEGES: 这部分表示授予所有权限。
  • ON *.*: 这指定了权限的范围。*.* 表示所有数据库（第一个 *）和所有表（第二个 *）。
  • TO 'root'@'%': 这指定了权限被授予给哪个用户。'root' 是用户名，'@%' 表示该用户可以从任何主机连接到数据库服务器。% 是一个通配符，代表任意主机。
  • 这部分用于设置或更改用户的密码。在这个上下文中，它尝试将 'root'@'%' 用户的密码设置为 '123456'。

实现远程连接(改表法)

将host字段的值改为%，表示在任何ip上均能以root用户登录到mysql服务器。

1
2
use mysql;
update user set host = '%' where user = 'root';

错误处理

1
mysqladmin flush-hosts

配置文件监听地址

mysql服务只监听本地的ip地址这样的话外部还是无法连接到mysql服务的

更改配置文件找到bind-address和mysqlx-bind-address，默认是127.0.0.1，是本机ip地址，需要将其改成0.0.0.0表示监听所有的ip地址保存退出重启一下mysql服务

1
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
1
systemctl restart mysql

image-20250907142011871

然后就可以进行远程连接了

使用Workbench时遇到的问题

但是我在使用GUI工具（Workbench）连接mysql的时候遇到一个问题：

总是提示我RuntimeError:Target host is configured as Windows,but seems to be a different OS,Please revisew the connection settings。按照上面所说的意思是原本目标主机是windows，但是检测为其他操作系统。我觉得这都什么乱七八糟的，找了一下资料是MYSQL Workbench软件在中国时区的bug，也有大佬解决了，改一下区域就行了

我的本地运行环境为：Windows10专业版，Workbench为8.0.38

服务器环境：Ubuntu buntu0.24.04.1及MySQL8.0.40

解决方法：

控制面板-区域-管理-更改系统区域设置-勾选【Beta 版: 使用 Unicode UTF-8 提供全球语言支持(U)】

然后重启电脑就可以了

宝塔mysql远程连接

首先放行3306端口

进入数据库

1
2
mysql-uroot -p
use mysql;

接着输入命令 select user,host from user \g; 可以看到root的是localhost 本地

image-20250907194611180

接下来用命令来修改

1
update user set host='%' where user='root' \g;

image-20250907195032672

我们用Navicat链接看一下，前提是端口是放行的image-20250907195132268

MySQL-Shell

MysqlShell是一个交互式JavaScript Python 和 SQL的终端，可以用于执行数据库管理任务，它提供了自动语法高亮、语法检查、自动完成和上下文感知的提示。

安装也非常简单，直接从官网下载对应版本的安装文件。安装完成后可以在命令行中输入mysqlsh命令启动，MysqlShell中的命令都是以反斜线开头的

启动MySQLShell命令mysqlsh

比如：\help命令可以查看帮助信息，`\connect可以连接到MySQL数据库

1
\connect root@localhost

\use进行选择数据库

MySQLshell是支持三种语言的，默认是JavaScript，想要切换语言的话写语言的缩写就行了

\py就可以切换python语言，可以直接输入python的代码

\sql切换sql语言，切换sql语言之后就可以直接进行各种sql的语句了

在终端或者Navicat中执行sql语句的效果是一样的。Oracle官方提供了vscode的MySQL-Shell的插件，

可以直接在vscode的扩展中搜索mysql shell就可以找到这个插件了，直接install就可以了

SQL基础

关系型数据库管理系统和非关系型数据库管理系统是两种不同的数据库管理系统

关系型数据库管理系统采用了关系模型来组织数据借助于集合 代数等数学概念和方法来处理数据，通过二维表来表示数据之间的联系，二维表的每一行表示一条数据记录每一列表示一个字段也就是记录的某个属性，不同的表之间通过关联字段来建立联系，这种关联关系就是关系型数据库管理系统的特点，MySQL Oracle PostgreSQL等等都是常见的关系型数据库管理系统的代表。

非关系型数据库管理系统，它是对关系型数据库管理系统的补充和扩展，由于互联网的快速发展，仅仅使用关系型数据库已经不能满足需求了，比如各种短视频，流媒体，地理位置信息，社交网络等等，这些数据的特点是数据库量大，数据结构复杂，数据类型多种多样，这又就决定了关系型数据库管理系统并不能很好地处理这些数据，所以就出现了非关系型数据库管理系统，常见的非关系型数据库有Redis，mongoDB，neo4j，cassandra，couchDB

SQL是一种用来操作关系型数据库的语言，在关系型数据库中数据一般都是以二维表的形式来存储的 ，SQL可以用来操作表中的数据，几乎所有的关系型数据库都支持SQL，一般来说sql语句并不区分大小写，但是为了提高可读性，我们一般会把关键字大写，把表明列名和其他名称小写

sql语言功能分类

DDL数据定义语言Data Definition Language：用来定义数据库对象

CREATE、DROP、ALTER、TRUNCATE

DML数据操作语言Data Manipulation Language：用来对数据库中的记录进行新增删除或者修改

INSERT、UPDATE、DELETE、CALl

DQL数据查询语言Data Query Laguage：用来查询数据库中的记录

SELECT、where

DCL数据控制语言Data Control Language：用来定义数据库的访问权限和安全级别

GRANT、REVOKE

常用的sql语句

show databases;查看当前存在的数据库

create databases game; 创建game数据库

drop database game; 删除game数据库