Detailed explanation of common commands in MySQL 8.0+

Enable remote access

Enable remote access rights for the root user by running the following command:

CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'root'@'%';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Note: Password is the root password, and FLUSH PRIVILEGES is the refresh permissions.

Importing Data

To import a CSV table, open the following command:

SET GLOBAL local_infile = 1

Clear table contents

The following command will not only clear all the contents of the table, but also start the auto-increment ID from 0. The specific commands are as follows:

TRUNCATE TABLE "table name";

Knowledge point expansion:

MySQL 8.0 operation commands

Since MySQL 8.x has major changes compared to the previously commonly used MySQL 5.x, many commands cannot run normally on MySQL 8.x machines. So here are some operation commands of MySQL 8.x for your reference and as a reminder for yourself.

The encryption rules of MySQL 8.0 and MySQL 5.0 are different, and many current tools do not support them. Here we use the method of changing the encryption rule of MySQL user login to mysql_native_password to solve the problem.

Modify encryption rules

 ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

Update the root user password

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'new_password';

Refresh permissions

FLUSH PRIVILEGES;

Create a new user in MySQL 8.0

PS: 'root'@'localhost' and 'root'@'%' are two different users, so to change to % you can do this

CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
FLUSH PRIVILEGES;

Summarize

The above are the common commands of MySQL8.0+ introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!