windows - mysql

66 阅读 0 评论 44 点赞
我是靠谱客的博主认真台灯，最近开发中收集的这篇文章主要介绍windows - mysql，觉得挺不错的，现在分享给大家，希望可以做个参考。
概述

Windows:(mysql)
操作:

0.下载安装mysql

www.mysql.org

downloads

community 5.7.21

下载5.6 Microsoft Windows

解压到C:
C:mysql-5.6.39-winx64

C:mysql-5.6.39-winx64bin

bin/mysql
客户端

bin/mysqld
服务端

设置环境变量:

我的电脑 属性 高级系统设置 环境变量

系统变量 Path 新建 将 C:mysql-5.6.39-winx64bin 粘贴 确定...

启动cmd:

>>>:mysqld

>>>:mysql

将mysqld做成系统服务,开机自动启动:

1.先杀死之前开启的mysqld:

C:Usersbj>tasklist | findstr mysql

mysqld.exe
67404 Console
1
453,740 K

C:WINDOWSsystem32>taskkill /F /PID 67404

成功: 已终止 PID 为 66732 的进程。

2.制作系统服务:

C:WINDOWSsystem32>mysqld --install
制作系统服务

Service successfully installed.

C:WINDOWSsystem32>mysqld --remove
解除系统服务

Service successfully removed.

3.服务

服务-->MySQL-->启动-->ok...

或者:

C:WINDOWSsystem32>net start MySQL

MySQL 服务正在启动 .

MySQL 服务已经启动成功。


C:WINDOWSsystem32>net stop MySQL

MySQL 服务正在停止.

MySQL 服务已成功停止。

1.验证安装成功

C:Usersbj>mysql

Welcome to the MySQL monitor.
Commands end with ; or g.

Your MySQL connection id is 15

Server version: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+----------------+

| user()
|

+----------------+

| ODBC@localhost |

+----------------+

1 row in set (0.00 sec)


mysql> exit

Bye

2. root默认没有密码

C:Usersbj>mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.
Commands end with ; or g.

Your MySQL connection id is 8

Server version: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+----------------+

| user()
|

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)


mysql> exit

Bye

3.设置初始密码:

C:Usersbj>mysqladmin -uroot -p password "123"

Enter password:

Warning: Using a password on the command line interface can be insecure.

C:Usersbj>mysql -uroot -p123

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: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+----------------+

| user()
|

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)


mysql> exit

Bye

4.修改密码

C:Usersbj>mysqladmin -uroot -p123 password "456"

Warning: Using a password on the command line interface can be insecure.

C:Usersbj>mysql -uroot -p456

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 19

Server version: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+----------------+

| user()
|

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)


mysql> exit

Bye

5.忘记密码,破解密码,跳过授权表;

C:WINDOWSsystem32>net stop MySQL

MySQL 服务正在停止.

MySQL 服务已成功停止。


C:WINDOWSsystem32>mysqld --skip-grant-tables
# 跳过授权表 启动 mysqld

2018-04-08 10:56:49 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-04-08 10:56:49 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2018-04-08 10:56:49 0 [Note] mysqld (mysqld 5.6.39) starting as process 66732 ...


C:Usersbj>mysql

Welcome to the MySQL monitor.
Commands end with ; or g.

Your MySQL connection id is 1

Server version: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+--------+

| user() |

+--------+

| ODBC@
|

+--------+

1 row in set (0.00 sec)


mysql> exit

Bye


C:Usersbj>mysql -uroot -p
# 跳过了授权 不需要输入密码了

Enter password:

Welcome to the MySQL monitor.
Commands end with ; or g.

Your MySQL connection id is 2

Server version: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+--------+

| user() |

+--------+

| root@
|

+--------+

1 row in set (0.00 sec)


mysql> update mysql.user set password=password("123") where user="root" and host="localhost";
# 修改密码

Query OK, 1 row affected (0.00 sec)

Rows matched: 1
Changed: 1
Warnings: 0


mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)


mysql> exit

Bye


C:Usersbj>mysql -uroot -p123
# 修改密码成功

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: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+----------------+

| user()
|

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)


mysql> exit

Bye


# 杀死之前 开启的 跳过授权的 mysqld

C:Usersbj>tasklist | findstr mysql

mysqld.exe
66732 Console
1
453,740 K


C:WINDOWSsystem32>taskkill /F /PID 66732

成功: 已终止 PID 为 66732 的进程。


C:WINDOWSsystem32>net start mysql

MySQL 服务正在启动 .

MySQL 服务已经启动成功。


C:Usersbj>mysql -uroot -p123
# 用之前设置的密码 登录便可

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 3

Server version: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+----------------+

| user()
|

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)


mysql> exit

Bye

6.登录mysql的两种方式:

C:Usersbj>mysql -uroot -p123

C:Usersbj>mysql -uroot -p123 -h 127.0.0.1 -P 3306
# 默认端口是3306

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 8

Server version: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+----------------+

| user()
|

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)


mysql> exit

Bye


C:Usersbj>mysql -h 127.0.0.1 -P 3306

Welcome to the MySQL monitor.
Commands end with ; or g.

Your MySQL connection id is 9

Server version: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> select user();

+----------------+

| user()
|

+----------------+

| ODBC@localhost |

+----------------+

1 row in set (0.00 sec)


mysql> exit

Bye

7.统一字符编码:

C:WINDOWSsystem32>mysql -uroot -p123

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 17

Server version: 5.6.39 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, 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> s

--------------

mysql
Ver 14.14 Distrib 5.6.39, for Win64 (x86_64)


Connection id:
17

Current database:

Current user:
root@localhost

SSL:
Not in use

Using delimiter:
;

Server version:
5.6.39 MySQL Community Server (GPL)

Protocol version:
10

Connection:
localhost via TCP/IP

Server characterset:
latin1

Db
characterset:
latin1

Client characterset:
gbk

Conn.
characterset:
gbk

TCP port:
3306

Uptime:
1 hour 12 min 59 sec


Threads: 1
Questions: 36
Slow queries: 0
Opens: 67
Flush tables: 1
Open tables: 60
Queries per second avg: 0.008

-----------------------

增加: C:mysql-5.6.39-winx64my.ini


#1. 修改配置文件

[mysqld]

default-character-set=utf8

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8


#mysql5.5以上：修改方式有所改动

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8


my.ini

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8


net stop mysql

再重启

net start mysql

mysql> s

--------------

mysql
Ver 14.14 Distrib 5.6.39, for Win64 (x86_64)


Connection id:
4

Current database:

Current user:
root@localhost

SSL:
Not in use

Using delimiter:
;

Server version:
5.6.39 MySQL Community Server (GPL)

Protocol version:
10

Connection:
localhost via TCP/IP

Server characterset:
utf8

Db
characterset:
utf8

Client characterset:
utf8

Conn.
characterset:
utf8

TCP port:
3306

Uptime:
1 min 9 sec


Threads: 1
Questions: 6
Slow queries: 0
Opens: 67
Flush tables: 1
Open tables: 60
Queries per second avg: 0.086

--------------

或者:

mysql> show variables like '%char%';

+--------------------------+----------------------------------------+

| Variable_name
| Value
|

+--------------------------+----------------------------------------+

| character_set_client
| utf8
|

| character_set_connection | utf8
|

| character_set_database
| utf8
|

| character_set_filesystem | binary
|

| character_set_results
| utf8
|

| character_set_server
| utf8
|

| character_set_system
| utf8
|

| character_sets_dir
| C:mysql-5.6.39-winx64sharecharsets |

+--------------------------+----------------------------------------+

8 rows in set (0.00 sec)