mysql create database, add users, user authorization practical method

1. Create a MySQL database

1. Create database syntax

--Create a database named "testdb" and set the encoding set to utf8
CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

2. Create a User

1. Create a new user

 --Created a user named: test with password: 1234 create user 'test'@'localhost' identified by '1234';

Notice:
The "localhost" here means that the user can only log in locally and cannot log in remotely from another machine. If you want to log in remotely, change "localhost" to "%", which means you can log in from any computer. You can also specify a machine that can log in remotely.

2. Query users

--Query user select user,host from mysql.user;

3. Delete User

--Delete user "test"
drop user test@localhost ;
--If the created user is allowed to log in from any computer, delete the user as follows drop user test@'%';

4. Change your password

--Method 1, real-time password update; change the password of user "test" to "1122"
set password for test =password('1122');
--Method 2, requires refreshing; change the password of user "test" to "1234"
update mysql.user set password=password('1234') where user='test'
--refresh privileges;

5. User Assignment Permissions

--Grant user test all privileges on the database "testdb" through the external network IP grant all privileges on 'testdb'.* to 'test'@'%' identified by '1234'; 

--Flush privileges; 

--Grant user "test" the permissions to create, modify, and delete tables in the database "testdb" through the external network IP, as well as the permissions to add, delete, query, and modify table data grant create,alter,drop,select,insert,update,delete on testdb.* to test@'%';

6. Check user permissions

--View user "test"
show grants for test;

Note: After modifying the permissions, be sure to refresh the service, or restart the service. To refresh the service, use: flush privileges;

The above is all the relevant knowledge points introduced this time. Thank you for your learning and support for 123WORDPRESS.COM.

You may also be interested in:

MySQL user creation and authorization method
MySql add user, authorization, change password and other statements
How to create, authorize, and revoke MySQL users
Sharing of methods for creating new users and authorization in MySQL
User authorization and authorization deletion methods in MySQL
mysql5.7 create user authorization delete user revoke authorization
Specific method of viewing user authorization information in mysql
MySQL creates users, authorizes users, revokes user permissions, changes user passwords, and deletes users (practical tips)
Detailed explanation of creating, deleting users, and authorizing and removing rights in mysql8

<<: jQuery implements all selection and reverse selection operation case

>>: VirtualBox installs CentOS7 virtual machine and enhancement tools (picture and text)