第一次使用 MySQL 時, 他的預設登入權限, 僅同意本機登入, 即來源IP為 127.0.0.1 或 localhost, 登入帳號為 root, 免密碼

但若要使用外部mysql 工具登入是不行的, 這時僅須進去修登入Host為%, 即可讓所有的人進入修改, 但記得, 要改回來, 不然太危險了

(建議直接新增一個管理者)

進入MySQL

# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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.

顯示有Database

mysql> SHOW DATABASES;
 
+--------------------+
| DATABASE           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 ROWS IN SET (0.00 sec)

進入 Database - mysql

mysql> USE mysql;
 
Reading TABLE information FOR completion OF TABLE AND COLUMN names
You can turn off this feature TO GET a quicker startup WITH -A
 
DATABASE changed

顯示所有 Table

mysql> SHOW TABLES;
 
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| USER                      |
+---------------------------+
23 ROWS IN SET (0.00 sec)

查看 Table - user 內容

mysql> SELECT Host, USER, Password FROM USER;
 
+-------------+------+----------+
| Host        | USER | Password |
+-------------+------+----------+
| localhost   | root |          |
| wifi-sensor | root |          |
| 127.0.0.1   | root |          |
| localhost   |      |          |
| wifi-sensor |      |          |
+-------------+------+----------+
5 ROWS IN SET (0.00 sec)

修改 Host 為 %(任何主機), 從127.0.0.1改

mysql> UPDATE USER SET Host="%" WHERE Host="127.0.0.1";
 
Query OK, 1 ROW affected (0.00 sec)
ROWS matched: 1  Changed: 1  Warnings: 0

看結果

mysql> SELECT Host, USER, Password FROM USER;
 
+-------------+------+----------+
| Host        | USER | Password |
+-------------+------+----------+
| localhost   | root |          |
| wifi-sensor | root |          |
| %           | root |          |
| localhost   |      |          |
| wifi-sensor |      |          |
+-------------+------+----------+
5 ROWS IN SET (0.00 sec)

重啟MySQL才會生效

# /etc/init.d/mysqld restart

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

或

GRANT ALL PRIVILEGES ON *.* to 'root'@'%' IDENTIFIED BY 'password';

當更改了權限, 記得重啟或 下以下command, 使他生效

FLUSH PRIVILEGES;

其他

CREATE USER 'root'@'%' IDENTIFIED BY 'wieson6802';
 
GRANT GRANT OPTION ON *.* TO 'root'@'%';
 
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'%';