Detailed explanation of common commands of MySQL8.0 +

Turn on remote access

Turn on root user remote access with 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: Among them, password is the password of root, and FLUSH PRIVILEGES is the refresh permission

Import data

To import the CSV table, open the following command:

SET GLOBAL local_infile = 1

Empty table contents

Not only does the table empty, but the self-growing ID starts at 0 with the following command:

TRUNCATE TABLE " Table name ";

Extension of knowledge points:

MySQL8.0 Operation Command

Because MySQL8.x has a big change compared with the commonly used MySQL5.x before, many commands can't run normally on MySQL8.x machine, so here I summarize some operating commands of MySQL8.x for your reference and make a memo for myself

MySQL 8.0 and MySQL 5.0 version of the encryption rules are not 1, and now many tools are not supported, we use the MySQL user login encryption rules modified to mysql_native_password method to solve.

Modify encryption rules

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

Update root user password

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

Refresh permissions

 FLUSH PRIVILEGES; 

MySQL 8.0 Create a new user

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

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

Summarize