About MySQL user management, note 1, creating a new user after logging in by the root user creates >> grant all privileges on *. * to [email protected] Identified by "123456" ;//create a new user, the user name is TestUser, the password is 123456; >> grant all privileges on *. * to& nbsp [email protected] Identified by "123456" ;//set user testuser, can be accessed locally mysql >> grant all Privileges on * * to [email protected] "%" identified by "123456" ;//set user testuser, can access MySQL remotely >> flush privileges ;//mysql New user or change password after the need to flush privileges refresh the MySQL system permissions related tables, otherwise there will be access denied, there is a way to is to restart the MySQL server for the new settings to take effect 2, set the user Access database permissions >> grant all privileges on test_db.* to [email Protected] identified by "123456";//Set user testuser, only Access database test_db, other databases cannot access ; >> grant all Privileges on *.* to [email protected] identified by "123456";//Set user testuser to access all databases on MySQL; >> grant All Privileges On test_db.user_infor to [email protected] identified by "123456";//Set user testuser, only Access database test_db table user_infor, other tables in the database cannot access ; 3, set user operation rights >> grant all privileges on * * to [email protected] identified by "123456" With GRANT option ;//set user testuser, with all operational privileges, i.e. Administrator; >> grant select on * * to [email protected] identified by "123456" with GRANT option ;//set user testuser, with only "query" operation permission; >> grant select,insert on * * to [email protected] identified by "123456" ; Set user testuser, only have "query \ Insert" Operation permission; >> grant select,insert,update,delete on *. * to [ Email protected] identified by "123456";//Set user testuser, only have "query \ Insert" Operation permission; >> revoke select , insert on what from testuser//Cancel user testuser "query \ Insert" operation right; 4, set user remote access (fixed IP address remote access) >> grant all Privileges on * * to [email protected] "192.168.1.100" identified by "123456" ;//set user TestUser, only on the client IP is 192.168.1.100 to remote access to MySQL; 5. Access settings settings for root users All users can access MySQL remotely, Modify the MY.CNF configuration file, bind-address = 127.0.0.1 front Plus "#" comment out, so that you can allow other machines to remotely access the native MySQL; >> grant all privileges on * * to [email protected] "%" identified by "123456" ; Set user root, can be accessed remotely mysql >> select host,user from user; Query MySQL for all user rights turn off root user remote access (increase privacy) >> delete from user where user= "root" and host= "%";// Prevents root users from accessing Mysql >> flush on remote machines privileges ; After modifying permissions, refresh the MySQL system permission related table to be effective
MySQL user rights settings, remote access settings, set fixed IP remote access, set root user cannot remotely access MySQL database