Advanced operation of the mysql< database >

advanced operations for Databases

MySQL provides a mysqldump command that enables backup of data

Backup of Data

1. Backing up a single database

Mysqldump-uusername-ppassword dbname [tbname1 [tbname2 ...] >filename.sql

2. Backing up multiple databases

Mysqldump–uusername–ppassword-–database dbname1 [dbname2 dbname3 ...]

>filename.sql

3. Back up all databases

Mysqldump–uusername–ppassword-–all-databases>filename.sql

restoration of Data

When the data in the database is destroyed, the data can be restored by the backed up data file, which refers to restoring the data in the database, and the library cannot be restored.

Mysql–uusername–ppassword [dbname] <filename.sql

1, first need to use the drop statement to delete the database chapter08

DROP DATABASE chapter08;

2, because the library can not be restored, so the first to create a database chapter08

CREATE DATABASE chapter08;

3. Use the MySQL statement to restore the Chapter08_20140305.sql file in the C:/backup directory

Mysql-uroot-pitcast chapter08 <c:/backup/chapter08_20140305.sql

4, in order to verify that the data has been restored successfully, you can use the SELECT statement to query the data in chapter08 select * from student;

User Management User Table

The user table is the most important permission table that records the account information that is allowed to connect to the server, as well as some global-level permission information.

There are 42 fields in the user table, which can be roughly divided into 4 types of user columns, permission columns, security columns, resource control columns

Create a normal user

1. Creating a user with the GRANT statement is the most common way to create a user

GRANT Privileges on database.table

To ' username ' @ ' hostname ' [identified by [PASSWORD] ' PASSWORD ']

[, ' username ' @ ' hostname [identified by [PASSWORD] ' PASSWORD '] ...

2. Creating a user using the Create USERS statement

CREATE USER ' username ' @ ' hostname ' [identified by [PASSWORD] ' PASSWORD ']

[, ' username ' @ ' hostname ' [identified by [PASSWORD] ' PASSWORD '] ...

3. Create user with INSERT statement

INSERT into Mysql.user (Host,user,password,ssl_cipher, X509_issuer, X509_subject)

VALUES (' hostname ', ' username ', PASSWORD (' PASSWORD '), ', ', ');

Delete a normal user

1. Delete users using the DROP USER statement

DROP USER ' username ' @ ' hostname ' [, ' username ' @ ' hostname '];

2. Delete user using DELETE statement

DELETE from Mysql.user WHERE host= ' hostname ' and user= ' username ';

Modify User Password

Root user changes root user password

1. Use the mysqladmin command to modify

Mysqladmin–u username [–h hostname]–p password New_password

2. Use the UPDATE statement to modify

UPDATE Mysql.user Set Password=password (' New_password ')

WHERE user= ' username ' and host= ' hostname ';

3. Use the SET statement to modify

SET Password=password (' New_password ');

Root user to modify normal user password

1. Use the grant Usage statement to modify

GRANT USAGE on *. username ' @ ' localhost ' identified by [PASSWORD] ' new_password ';

2. Use the UPDATE statement to modify

UPDATE Mysql.user Set Password=password (' New_password ') WHERE user= ' username ' and host= ' hostname ';

3. Use the SET statement to modify

SET Password=password (' New_password ');

Normal User Change Password

SET Password=password (' New_password ');

How to resolve root user password loss issues

Stop MySQL Service

net stop MySQL

Start the MySQL service with--skip-grant-tables

MySQL--skip-qrant-tables

Log in to the MySQL server

Re-open a Run dialog box and log in to the MySQL server in the Run dialog box, as follows:

Mysql-u Root

To set the root user password by using the UPDATE statement

UPDATE mysql.user SET password=password (' itcast ') WHERE user= ' root ' and host= ' localhost ';

Load Permissions Table

FLUSH privileges;

After the above steps are complete, you can use the exit or \q command to exit the server and then log back in with the new password. This completes the password setting for the root user.

Rights Management permissions for MySQL

The permissions information in MySQL is stored in the user, DB, host, Tables_priv, Column_priv, and Procs_priv tables of the MySQL database, which automatically loads the permission information when MySQL starts and reads the permission information into memory.

granting Permissions

The reason why we can make more and more changes to the data is because the users in the database have different permissions and reasonable authorization can guarantee the security of the database.

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] ...]

Privileges: Represents a permission type.

Columns: The parameter means that the permission is applied to a column, which can be omitted without writing, at which time the permission is applied to the entire table, Username: represents the user name.

Hostname represents the host name.

Identified by: parameter sets the password for the user.

The password parameter is a keyword.

Password a new password for the user. The WITH keyword can be followed by a number of parameter with_option, which has five values.

Grant OPTION: Grant your own permissions to other users Max_queries_per_hour count: Sets the maximum number of times (count) queries can be executed per hour.

Max_updates_per_hour count: Sets the maximum number of updates that can be performed per hour.

Max_connections_per_hour count: Sets the maximum number of connections per hour.

Lmax_user_connections: Sets the maximum number of simultaneous connections that can be established per user.

View Permissions

SHOW GRANTS for ' username ' @ ' hostname ';

Revoke Permissions

In order to ensure the security of the database, it is necessary to reclaim the user's unnecessary permissions

1. Reclaim user-specified permissions

REVOKE privileges [columns][,privileges[(columns)] "on database.table

From ' username ' @ ' hostname ' [, ' username ' @ ' hostname '] ...

2. Recover all user rights

REVOKE all Privileges,grant OPTION

From ' username ' @ ' hostname ' [, ' username ' @ ' hostname '] ...

Advanced operation of the mysql< database >