MySQL在Windows的基本管理

MySQL登录和密码修改

MySQL登录

# 游客模式
PS C:\Users\xiezh> mysql

# 用户名密码登录
PS C:\Users\xiezh> mysql -u用户名 -p密码

# 完整命令
PS C:\Users\xiezh> mysql -u用户名 -p密码 -h主机域 -P端口 -S指定socket的文件目录 -e指定指定的sql语句

---

MySQL设置密码

# 未登录状态下（windows终端）
PS C:\Users\xiezh> mysqladmin -uroot -p原密码 password 新密码

# 登录状态下
mysql> set password=PASSWORD('密码')

---

忘记密码解决方案

1.关闭mysql服务

PS C:\Users\xiezh> net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

2.以跳过授权表的方式重新启动服务端

PS C:\Users\xiezh> mysqld --skip-grant-tables
2022-02-17 18:47:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-02-17 18:47:13 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-02-17 18:47:13 0 [Note] E:\mysql-5.6.51\bin\mysqld.exe (mysqld 5.6.51) starting as process 4380 ...

3.以管理员的身份登录

PS C:\Users\xiezh> mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

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.

4.修改管理员用户密码

mysql> update mysql.user set password=password('') where user="root" and host="localhost";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

5.关闭服务(ctrl+c)并重启服务

PS C:\Users\xiezh> net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

6.使用修改的用户名密码登录

PS C:\Users\xiezh> mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

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解压后，配置环境变量使起能够在windows终端中快速启动服务。启动mysql服务是mysqld，它会卡在窗口并且关闭后服务也关闭。然后我们需要重新开设一个windows终端启动mysql客户端做一些sql操作。

1、启动mysql服务：

PS C:\Users\xiezh> mysqld
2022-02-17 17:07:01 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-02-17 17:07:01 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-02-17 17:07:01 0 [Note] E:\mysql-5.6.51\bin\mysqld.exe (mysqld 5.6.51) starting as process 9956 ...

2、启动mysql客户端

PS C:\Users\xiezh> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

---

要实现mysql服务自启动，也就是说开机自动启动mysqld。这里我们把mysql做成windows系统服务，然后让它开机自动启动。

首先我们要以管理员身份打开windows终端,然后输入以下命令：

PS C:\Users\xiezh> mysqld --install
Service successfully installed.
PS C:\Users\xiezh> net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

我们可以通过cmd，输入services.msc查看windows系统服务。

关闭服务命令如下(管理员身份打开windows终端)：

PS C:\Users\xiezh> net stop mysql

MySQL配置文件统一字符编码

在mysql软件目录下新建my.ini配置文件，内容如下：

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8