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-winx64\bin
        bin/mysql   客戶端
        bin/mysqld  服務端
    設置環境變量:
        我的電腦 屬性 高級系統設置 環境變量
        系統變量 Path 新建 將 C:\mysql-5.6.39-winx64\bin 粘貼 確定...
    啟動cmd:
        >>>:mysqld
        >>>:mysql
    將mysqld做成系統服務,開機自動啟動:
        1.先殺死之前開啟的mysqld:
            C:\Users\bj>tasklist | findstr mysql
            mysqld.exe                   67404 Console                    1    453,740 K
            C:\WINDOWS\system32>taskkill /F /PID 67404
            成功: 已終止 PID 為 66732 的進程。
        2.制作系統服務:
            C:\WINDOWS\system32>mysqld --install   制作系統服務
            Service successfully installed.
            C:\WINDOWS\system32>mysqld --remove    解除系統服務
            Service successfully removed.
        3.服務
            服務-->MySQL-->啟動-->ok...
            或者:
            C:\WINDOWS\system32>net start MySQL
                MySQL 服務正在啟動 .
                MySQL 服務已經啟動成功。

            C:\WINDOWS\system32>net stop MySQL
                MySQL 服務正在停止.
                MySQL 服務已成功停止。

1.驗證安裝成功
    C:\Users\bj>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:\Users\bj>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:\Users\bj>mysqladmin -uroot -p password "123"
    Enter password:
    Warning: Using a password on the command line interface can be insecure.
    C:\Users\bj>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:\Users\bj>mysqladmin -uroot -p123 password "456"
    Warning: Using a password on the command line interface can be insecure.
    C:\Users\bj>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:\WINDOWS\system32>net stop MySQL
    MySQL 服務正在停止.
    MySQL 服務已成功停止。

    C:\WINDOWS\system32>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:\Users\bj>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:\Users\bj>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:\Users\bj>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:\Users\bj>tasklist | findstr mysql
    mysqld.exe                   66732 Console                    1    453,740 K

    C:\WINDOWS\system32>taskkill /F /PID 66732
    成功: 已終止 PID 為 66732 的進程。

    C:\WINDOWS\system32>net start mysql
    MySQL 服務正在啟動 .
    MySQL 服務已經啟動成功。

    C:\Users\bj>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:\Users\bj>mysql -uroot -p123
    C:\Users\bj>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:\Users\bj>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:\WINDOWS\system32>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-winx64\my.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-winx64\share\charsets\ |
    +--------------------------+----------------------------------------+
    8 rows in set (0.00 sec)