MySql Basic Operation Organizing for Myself

MySQL is an open source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.
                                       —— Wikipedia

Preparation

Object Value
MySql Version mysql-5.7.23
Operation System Windows 10 x64

Install

Download

Object Value
Offical Product Removed
Unoffical Link mysql-5.7.23-winx64.zip
Access Code NULL

Download and unzip at suitable location, such as D:\mysql-5.7.23-winx64

[*] Caution! Using D:\mysql-5.7.23-winx64 as a location is a precondition for next all the examples. Change one and must change all.

Add Path

Computer -> Attributes -> Advanced System Settings -> Environmental Variable -> System Variable -> Path -> Edit -> New : D:\mysql-5.7.23-winx64\bin -> OK

Configure

Edit Configuration File

New my.ini at D:\mysql-5.7.23-winx64\

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# D:\mysql-5.7.23-winx64\my.ini
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\\mysql-5.7.23-winx64
# 设置 mysql数据库的数据的存放目录
datadir=D:\\mysql-5.7.23-winx64\\data
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

Initialize Database

1
2
3
4
5
> cd D:\mysql-5.7.23-winx64\bin # Under administrator privileges
> mysqld --install # if exist, `mysqld --remove` and `mysqld --install`
Service successfully installed.
> mysqld --initialize # `D:\mysql-5.7.23-winx64\data\` will be producted
> net start mysql

Change Initial Password

First cmd

1
2
> net stop mysql
> mysqld --skip-grant-tables # Keep this cmd running and open second cmd

Second cmd

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
> mysql -u root -p # Enter MySql Server without code
Enter password: # Press `ENTER`
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23
... ...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>update mysql.user set authentication_string=password("PASSWORD") where user="root"; # Type your password instead of `PASSWORD`
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql>flush privileges; # Refresh
Query OK, 0 rows affected (0.01 sec)

mysql>quit; # Exit Database
Bye

>taskkill /im cmd.exe -F # Close `First cmd` and `Second cmd`

Log In

Log in database with code changed

1
2
3
4
5
> mysql -u root -p 
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
... ...
mysql>

Change Password Again

1
2
3
4
5
6
7
8
mysql>SET PASSWORD = PASSWORD('NEWPASSWORD'); # Type your new password instead of `NEWPASSWORD`
... ...
mysql>ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
... ...
mysql>flush privileges;
... ...
mysql>quit
... ...

Base Usage


Unfinished