如何在 Mysql Server 增加用戶權限

環境說明：

CentOS Linux release 7.9.2009 (Core)
MariaDB Ver 15.1 Distrib 5.5.68-MariaDB

常用指令：

[root@mysql ~]# mysql -uroot -p -h 192.168.0.2 -P 3306

-u 使用者帳號
-p 使用都密碼
-h 連線目標的IP(192.168.0.2)，如果是本地則不需加入
-P 大P 是端口(port)，預設為3306，若目標端口(port)有指定則需要填寫

一、登入 Mysql

[root@mysql ~]# mysql -uroot -p
Enter password: ********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

二、選用 mysql Database

  • 查看目前 Databases SHOW databases;
  • 選擇 Database USE mysql;
  • 查看現有用戶資料 SELECT user,host FROM user; 
MariaDB [(none)]> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

MariaDB [(none)]> 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

MariaDB [mysql]> SELECT user,host FROM user;
+----------+--------------+
| user     | host         |
+----------+--------------+
| root     | 127.0.0.1    |
| root     | ::1          |
| root     | localhost    |
| root     | mysql        |
+----------+--------------+
4 rows in set (0.00 sec)

三、建立使用者

  • ‘使用者帳號’@’來源IP’ 'root'@'192.168.0.100'
  • 建立新的使用者 CREATE USER 'root'@'192.168.0.100' IDENTIFIED BY 'Password';
  • 賦予使用者全部的權限，但grant權限要另外開 GRANT ALL ON *.* TO 'root'@'192.168.0.100';
  • 開啟grant權限 GRANT GRANT OPTION ON *.* TO 'root'@'192.168.0.100';
MariaDB [mysql]> CREATE USER 'root'@'192.168.0.100' IDENTIFIED BY 'Password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> GRANT ALL ON *.* TO 'root'@'192.168.0.100';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> GRANT GRANT OPTION ON *.* TO 'root'@'192.168.0.100';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SELECT user,host FROM user;
+----------+----------------+
| user     | host           |
+----------+----------------+
| root     | 127.0.0.1      |
| root     | 192.168.0.100  |
| root     | ::1            |
| root     | localhost      |
| root     | mysql          |
+----------+----------------+
5 rows in set (0.00 sec)

常見問題

遠端連線MySQL Server時報錯：

[root@mysql]# mysql -uroot -p -h 192.168.0.2
ERROR 2003 (HY000): Can’t connect to MySQL server ‘ip’ (113)

已建立好用戶及連線來原IP，但還是無法連線，這時需要檢查MySQL服務器是否有開放 3306 port，允需外部發起連線請求，要在iptables 加入允許外部對MySQL 3306 port 進行連線。

[root@mysql]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
[root@mysql]# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:mysql
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
ACCEPT     all  --  anywhere             anywhere

標籤: , ,