MySQL user-related actions

MySQL Default has a root user, but this user rights are too large, generally only in the management of the database when used. If you are connecting to a MySQL database in your project, it is recommended that you create a new user with a smaller permission to connect.

You can create a new user for MySQL by entering the following command in MySQL command line mode:

CREATE USER username IDENTIFIED BY ‘password‘;

The new user is created, but at this point, if logged in as this user, will be an error, because we have not assigned the appropriate permissions for this user, the command to assign permissions are as follows:

GRANT ALL PRIVILEGES ON *.* TO ‘username‘@‘localhost‘ IDENTIFIED BY ‘password‘;

Grant all permissions for the username user on all databases.

GRANT REPLICATION SLAVE ON *.* TO ‘backup‘@‘address‘ IDENTIFIED BY ‘backup_xxx‘;Create accounts for master-slave synchronization data

If you find that the permission you just gave is too large, if we just want to grant it permissions on a database, then we need to switch to the root user to revoke the permissions just now and re-authorize:

REVOKE ALL PRIVILEGES ON *.* FROM ‘username‘@‘localhost‘;

GRANT ALL PRIVILEGES ON dbnames.* TO ‘username‘@‘localhost‘ IDENTIFIED BY ‘password‘;

You can even specify that the user can only perform select and UPDATE commands:

GRANT SELECT, UPDATE ON dbnames.* TO ‘username‘@‘localhost‘ IDENTIFIED BY ‘password‘;

In this way, to log in to MySQL again with username, only the Dbnames database is visible to it, and if you only grant it SELECT permission, then it cannot execute statements other than select.

In addition, each time you adjust permissions, you typically need to perform the following statement refresh permissions:

FLUSH PRIVILEGES;

Delete the user you just created:

DROP USER [email protected];

With the above commands in mind, you can find that the host of the response (that is, the content after the @ symbol) is specified, either by authorization or by revoking the authorization, since the above pass command is actually the user table in the MySQL database and can be viewed with the following command:

SELECT User, Host FROM user;

Of course, this table also contains a lot of other things such as user passwords, permission settings, and so on, especially when you need to be careful.

Forgot root password:

Turn on the firewall to make sure that no one else is connected to the database:

Close MySQL and modify the configuration file (MY.CNF): [mysqld] Add the following: skip-grant-tables .

Restart MySQL to change the root password after connection

MySQL user-related actions