Basic use of database

1. Log in and log out of the mysql server:

Mysql-h hostname-u username-p password-e "sql command"

For example, 1:mysql-uroot-p123456 # root user logs in to the database with a password of 123456.

For example, 2:mysql-uroot-p123456-e "desc mysql.user" # root user, login with password 123456 and view the user table structure.

For example, a 3:mysql-uroot-p123456 mysql # root user with a password of 123456 logs in and directly enters the mysql database.

two。 Create a new normal user:

2.1 create a new user using the create user statement.

Example: create user zhangsan@localhost identified by "132456"; # create user Zhang San and specify a password of 123456.

Example: create user zhangsan@ "%" identified by "123456"; # create user Zhang San and specify a password of 123456.

2.2 create a user using the grant statement:

Example: grant all on. To zhangsan@localhost identified by "123456"; # creates the Zhang San user and grants all permissions to all tables in all databases.

Select from mysql.user where user='zhangsan'\ G; # to view the information of zhangsan users, the permission is y.

3. Delete a normal user:

Example 3. 1: drop mysql.user zhangsan@localhost; # delete user zhangsan

Example 3.2: delete from mysql.user where user='zhangsan'; # deletes user zhangsan.

4.root users change their passwords:

4.1 mysqladmin-uroot-p123456 password '654321' # command line modification,-p specifies the old password, and the new password is in quotation marks.

4.2 update mysql.user set password=password ('123456') where user='root'; flush privileges; # put the root user password as 123456; refresh the permissions table.

4.3 set password=password ('654321'); # change the root user password to 123456

5.root users modify the password of an ordinary user:

Set password for zhangsan@localhost = password ('123456'); # change the zhangsan password to 123456.

5.2 update mysql.user set password=password ('123456') where user='zhangsan';flush privileges; # change the password of the zhangsan user to 123456; refresh the permissions table.

5.3 grant usage on. * to zhangsan@localhost identified by '123456password; # change the zhangsan user password to 123456

Ordinary users change their passwords:

6.1 set password=password ('123456'); # Log in to the database with an ordinary user and execute the command.

The solution to the loss of 7.root user password:

7.1 modify configuration file / etc/my.cnf add skip-grant-tables under [mysql]

7.2 systemctl restart mariadb # restart the service

7.3 mysql # logging in to the database

7.4 set password=password ('123456'); # change password

7.5 vim / etc/my.cnf comment out # skip-grant-tables

7.6 systemctl restart mariadb # restart the service

8.localhost is local login "%" is remote login