Shulou(Shulou.com)06/01 Report--

This paper gives an example of creating account, authorization, data export and import operation of 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 that all permissions are granted to the user. You can also specify specific permissions, such as SELECT, CREATE, DROP, etc. On: indicates the database and table for which these permissions take effect. Format: database name. Table name, where "*" means all databases, all tables. If I want to specify that permissions are applied to the user table of the test library, I can write: test.userto: which user to grant permissions to. Format: "user name" @ "sign in to IP or domain name". % means there is no limit and can be logged in on any host. For example, "yangxin" @ "192.168.0.%" means that the yangxin user can only log in to identified by in the 192.168.0IP segment: specify the user's login password with grant option: allow the user to authorize his permissions to other users.

2. View user permissions

Show grants for root

3. Reclaim permissions

Revoke create on *. * from 'yangxin'

4. Delete a user

Drop user test

5. Change the password

1 > Update the mysql.user table

# before mysql5.7 update mysql.user set password=password ('123456') where user='root';# mysql5.7 followed by update mysql.user set authentication_string=password (' 123456') where user='root'

2 > use the set password command

Syntax:

Set password for 'username' @ '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 does not export data

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

two。 Export data does not export 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 MySQL-related content can check out this site's special topics: "Summary of MySQL Index Operation skills", "Summary of MySQL Common functions", "Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.