MySQL database account creation, authorization, data export and import operation examples

This article describes the examples of creating accounts, authorization, data export and import operations in MySQL database. Share with you for your reference, the details are as follows:

1. Account creation and authorization

grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;

• privileges: means all permissions are granted to the user. You can also specify specific permissions, such as: SELECT, CREATE, DROP, etc.
• on: indicates which databases and tables these permissions are effective for. The format is database name.table name. Writing "*" here means all databases and all tables. If I want to specify that permissions be applied to the user table in the test library, I can write: test.user
• to: The user to whom the permission is granted. Format: "user name"@"login IP or domain name". % means there is no restriction and you can log in from any host. For example: "yangxin"@"192.168.0.%", which means that the user yangxin can only log in in the 192.168.0 IP segment
• identified by: specifies the user's login password
• with grant option: allows users to grant their permissions to other users

2. Check user permissions

show grants for root;

3. Reclaim permissions

revoke create on *.* from 'yangxin';

4. Delete User

drop user test;

5. Change password

1> Update mysql.user table

# Before mysql5.7 update mysql.user set password=password('123456') where user='root';
# After mysql5.7 update mysql.user set authentication_string=password('123456') where user='root';

2> Use the set password command

grammar:

set password for 'user name'@'login address'=password('password')

3>mysqladmin

Syntax: mysqladmin -u username -p old password password new password

Remember to refresh permissions:

flush privileges;

Data export:

1. Export structure but not data

mysqldump -d database name -uroot -p > xxx.sql

2. Export data but not structure

mysqldump -t database name -uroot -p > xxx.sql

3. Export data and table structure

mysqldump database name -uroot -p > xxx.sql

4. Export the structure of a specific table

mysqldump -uroot -p -B database name --table table name> xxx.sql
#mysqldump [OPTIONS] database [tables]

Import data:

mysql -uroot -p database name < file name

or:

Then select the database to be imported:

#use database;
#source /tmp/xxx.sql

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.