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
