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

The following mainly brings you the advanced methods of operating MySQL database. I hope these contents can bring you practical use, which is also the main purpose of this article. All right, don't talk too much nonsense, let's just read the following.

1. Data backup and restore

(1) backup

Mysqldump:

Mysqldump-u username-p password dbname [tbname1 [tbname2....]] > filename.sql

Mysqldump-u root-p mydb2 > C:\ mysql\ dump\ mydb2_dump.sql

(2) restore: only the tables and records of the database can be restored, not the database itself.

Mysql: method 1: mysql-u username-p password [dbname] < filename.sql

Mysql: method 2, source command

From the MySQL command prompt: create the database to enter the database source xxx.sql file and execute the backed-up sql file in the current location

Source filename.sql / / path

2.user table

3. Create a normal user

(1) create a user using the Grant statement

GRANT privileges ON dtabase.table

TO 'username' @' hostname' [IDENTIFIED BY [PASSWORD] 'password'] [,' username' @ 'hostname' [IDENTIFIED BY [PASSWORD]' password']].

Grant select on mydb2.* to '' @' localhost' identified by '123456'

/ / error

(2) use create statement

CREATE USER 'username'@'hostname' [IDENTIFIED BY [PASSWORD]' password'] [, 'username'@'hostname' [IDENTIFIED BY [PASSWORD]' password']].

Create user ''@'localhost' identified by' 123456'

(3) use insert statement

4. Delete an ordinary user

~ ROP USER 'username'@'hostname' [,' username'@'hostname']

Drop user 'ha'@'localhost'

~ ELETE FROM mysql.user WHERE Host= 'hostname' AND User =' username'

Delete from mysql.user where host = 'localhost' and user =' ha'

Flush privileges; # to reload user rights because the operation is performed directly on the user table

5. Modify a user's password

(1) modify root user password

+ + +

UPDATE mysql.user set Password = PASSWORD ('new_password') WHERE User='username' and Host='hostname'

FLUSH PRIVILEGES

Update mysql.user set password=PASSWORD ('QWE123 recently purchased') where user='root' and host='localhost'

Flush privileges

+

/ / unsuccessful

(2) root users modify ordinary user passwords

SET PASSWORD FOR 'username'@'hostname'=PASSWORD (' new_password')

Set password for ''@'localhost'=PASSWORD (' 123')

+ +

/ / unsuccessful

(3) ordinary users change their passwords

SET PASSWORD=PASSWORD ('new_password')

6. Grant permissions: make different users have different permissions

(1) GRANT privileges [(columns)] [, privileges [(columns)]] ON database.table TO 'username'@'hostname' [IDENTIFIED BY [PASSWORD]' password'] ['username'@'hostname' [IDENTIFIED BY [PASSWORD]' password']].

[WITH with_option [with_option]...]

The with_option parameters are as follows:

(1) GRANT OPTION: grant your own permissions to other users

(2) MAX_QUERIES_PER_HOUR count: set the maximum number of queries per hour count.

(3) MAX_UPDATES_PER_HOUR count: sets the maximum number of updates per hour

(4) MAX_CONNECTIONS_PER_HOUR count: set the maximum number of connections per hour

(5) MAX_USER_CONNECTIONS: set the maximum number of connections each user can establish at the same time

GRANT INSERT,SELECT ON mydb2.star TO ''@'localhost' IDENTIFIED BY' 123456'

WITH GRANT OPTION

+

/ / unsuccessful

7. View permissions

(1) SHOW GRANTS FOR 'username'@'hostname'

Show grants for ''@'localhost'

8. Take back the authority

REVOKE privileges [(columns)] [, privileges [(columns)]] ON database.table FROM 'username'@'hostname' [,' username'@'hostname']....

For the above high-level methods of operating MySQL database, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.