-----------
20190111
MySQL-CreateUser.txt

新增使用者, 不要使用root. 
測試環境固定使用 usermysql1 / Passmysql1!
Try this to create the user:
CREATE USER 'your_name'@'localhost' IDENTIFIED BY 'your_password';
CREATE USER 'user'@'hostname';
CREATE USER 'usermysql1'@'localhost';
CREATE USER 'usermysql1'@'localhost' IDENTIFIED BY 'Passmysql1!';

Try this to give it access to the database dbTest:
GRANT ALL PRIVILEGES ON *.* TO 'your_name'@'localhost' IDENTIFIED BY 'your_password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbTest.* To 'usermysql1'@'localhost' IDENTIFIED BY 'Passmysql1!';
GRANT ALL PRIVILEGES ON *.* To 'usermysql1'@'localhost' IDENTIFIED BY 'Passmysql1!';
GRANT ALL PRIVILEGES ON *.* TO 'usermysql1'@'localhost' IDENTIFIED BY 'Passmysql1!' WITH GRANT OPTION;

If you are running the code/site accessing MySQL on the same machine, hostname would be localhost.

Now, the break down.
GRANT - This is the command used to create users and grant rights to databases, tables, etc.
ALL PRIVILEGES - This tells it the user will have all standard privileges. This does not include the privilege to use the GRANT command however.
dbtest.* - This instructions MySQL to apply these rights for use in the entire dbtest database. You can replace the * with specific table names or store routines if you wish.
TO 'user'@'hostname' - 'user' is the username of the user account you are creating. Note: You must have the single quotes in there. 'hostname' tells MySQL what hosts the user can connect from. If you only want it from the same machine, use localhost
IDENTIFIED BY 'password' - As you would have guessed, this sets the password for that user.

在本機測試使用者可執行:
# mysql -uroot -p -e "select 1"
# mysql -uusermysql1 -p -e "select 1"
# mysql -uusermysql1 -pPassmysql1! -e "select 1"


允許使用者遠端登入mysql: 
必須同一個使用者帳號, 同時建立 (@'localhost' 和 @'%')兩個帳號
In order to connect remotely you have to have MySQL bind port 3306 to your machine's IP address in my.cnf. Then you have to have created the user in both localhost and '%' wildcard and grant permissions on all DB's as such . See below:
my.cnf (my.ini on windows)

# Replace xxx with your IP Address 
bind-address        = xxx.xxx.xxx.xxx
或 (全開, 但是靠防火牆過濾 ip 跟 port):
#bind-address = 127.0.0.1
bind-address=*

then
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';
CREATE USER 'usermysql1'@'localhost' IDENTIFIED BY 'Passmysql1!';
CREATE USER 'usermysql1'@'%' IDENTIFIED BY 'Passmysql1!';

Then
GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';
GRANT ALL ON *.* TO 'usermysql1'@'localhost';
GRANT ALL ON *.* TO 'usermysql1'@'%';

然後遠端登入看看, 記得防火牆 port 3306 要開啟
Depending on your OS you may have to open port 3306 to allow remote connections. 


限制用戶資源
可以透過下列設定限制每個用戶可使用的資源
MAX_QUESTIONS
MAX_USER_CONNECTIONS
MAX_QUERIES_PER_HOUR
MAX_UPDATES_PER_HOUR
MAX_CONNECTIONS_PER_HOUR