MySQL User management

First, User Login

Format: mysql-h host address-u user name-P user Password

Mysql-h110.110.110.110-uroot-p123

Local can directly mysql–uroot-p

Second, the user exits

Exit, quit

Third, add users

Mysql.user The table holds the user's login information

1. Add no permissions directly

Insert into Mysql.user (Host,user,password) VALUES ('% ', ' Jifei ', password (' Jifei '));

1. Add and assign weights

Grant SELECT on database. * to ' username ' @ ' login host ' identified by ' password '

Iv. User Rights

1. Add permissions

Grant permissions on the database. Table to ' username ' @ ' login host ';

Permissions: SELECT, Update,delete,insert (table data), create,alert,drop (table structure), references (foreign key), create temporary tables (create temp table), index ( Operation index), create View,show view, create Routine,alert routine,execute (stored procedure), All,all privileges (All rights)

Database: Database name or * (all databases)

Table: Table name or * (all tables under a database)

Host: Host name or% (any other host)

Example: Grant Selec,insert,update,delete on * * to ' jifei ' @ '% ';

1. Revoke permissions

Revoke permissions on the database. Table from ' username ' @ ' login host ';//change to from

Example: Revoke all on * * from ' jifei ' @ '% ';

1. View Permissions

Show grants;//Himself

Show grants for [email protected];//specify user-specified host

V. Delete users

Delete from Mysql.user where user= ' and host= ';

Six, change the password

Update Mysql.user set Password=password (' 111111 ') where user= ' root ';

Seven, retrieve the password

1. Turn off MySQL service

Killall-term mysqld

1. Modifying a configuration file

Vi/etc/my.cnf

Add a sentence to the paragraph in [mysqld]: Skip-grant-tables

For example:

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip-grant-tables

1. Restart Mysqld

Service mysqld Restart

1. Login

Mysql-uroot-p

1. Change Password

Update Mysql.user set Password=password (' 111111 ') where user= ' root ';

Flush privileges;//Refresh Permissions

1. Modifying a configuration file

Vi/etc/my.cnf

Remove the previous changes

1. Restart Service
2. Set up a remote user

Viii. Remote Users

① restrictions on specifying IP login host for IP details see Add permissions

② in any remote IP login host for% details see Add permissions

1. Remote access

mysql-h110.110.110.110-uroot-p123;//Specify h for IP details see user Login

Some standard examples:

1. Mysql.user Table instance: In general, the host field is limited by IP, not the machine name (machine name is variable, not particularly reliable)

Select Host, user from user;

| 172.17.% | dev |
| 172.17.0.% | Export |
| 172.17.0.20 | Demo |
| 172.28.0.% | dev |
| 192.168.% | dev |
| 110.111.126.% | Demo |
| 110.111.126.103 | Helper |
| 110.111.127.% | Webnav |
| localhost | Backup |
| localhost | Backupdata |
| localhost | Root |
+-----------------+-----------------+

2. Authorization example: Show grants for ' helper ' @ ' 110.111.127.% '

+-------------------------------------------------------------------------------------------------------------- ------------------------------------+
| GRANT USAGE on * * to ' helper ' @ ' 110.111.127.% ' identified by PASSWORD ' xxxxxxxxxxxxxxxxx ' with max_user_connections 200 |
| GRANT all Privileges "helper_online". * to ' helper ' @ ' 110.111.127.% ' |
+-------------------------------------------------------------------------------------------------------------- ------------------------------------+

MySQL User management