สร้าง Remote Mysql User

1 แก้ไข file my.cnf

bind-address = 0.0.0.0

restart myqsl

sudo systemctl restart mysqld

2 สร้าง user

แทนค่า myuser และ mypass

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

3 กำหนดสิทธิ

mysql> GRANT ALL ON *.* TO 'myuser'@'localhost';
mysql> GRANT ALL ON *.* TO 'myuser'@'%';

ตรวจสอบ

mysql> SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";
mysql> FLUSH PRIVILEGES;

แสดง permission ปัจจุบัน

mysql> SHOW GRANTS username;

4 ทดสอบการเชื่อมต่อจาก remote

$ mysql -h HOST -u USERNAME -pPASSWORD

5 ถอดถอนสิทธิของ user

แทน USERNAME ด้วย user ที่ต้องการ

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'%';
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'localhost';

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user full access to a designated database (or if no database is selected, global access across the system)
CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables
INSERT- allows them to insert rows into tables
SELECT- allows them to use thecommand to read through databases
UPDATE- allow them to update table row
GRANT OPTION- allows them to grant or remove other users' privileges

mysql-remoteuser