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

The maintenance of MySQL database system mainly includes the setting of user permissions, database backup and recovery. This blog will explain how to do these operations.

User authorization of the database

The root user account of the MySQL database has full permissions for all databases and tables. Frequent use of the root account will bring certain risks to the database server. In the work, some low-privilege users are usually set up, who are only responsible for the management and maintenance of some libraries and tables, and even make further refinement restrictions on queries, modifications, deletions and other operations, so as to minimize the risk of the database.

1. Grant authority

Grant statement: specifically used to set access to the database. When the specified user name does not exist, the grant statement creates a new user, otherwise, the user's information is modified. The statement format is as follows:

Grant permission list on library name. Table name to username @ source address [identified by 'password']

The meanings of each field are as follows:

* permission list: for example, "select,insert,update" is separated by commas, and all permissions are represented by all

* name of the library. Table name: the name of the library and table, using the wildcard "*". For example, "yang.*" indicates that the authorization object is all tables in the yang library.

* user name @ source address: the source address can be a domain name or IP address, or a wildcard "%" can be used to indicate an area or network segment, such as "% .ysf.com", "192.168.1%", etc.

* identified by: the user sets the user's password. If omitted, the user's password is empty.

The user records authorized by the Grant statement will be saved to user, db, host, tables_priv and other related tables in the mysql library, and will take effect without refreshing.

two。 View permissions

Show grants statement: specially used to view the authorization information of database users. The user object to be viewed can be specified through the for clause. The statement format is as follows:

Show grants for user name @ Source address

3. Revoke the authority

Revoke statement: the user revokes the database permissions of the specified user. After revoking the permissions, the user can still connect to the MySQL server, but the corresponding database operation is prohibited. The statement format is as follows:

Revoke permission list on database name. Table name from user name @ Source address

To learn more about MySQL statements, please refer to MySQL's online reference manual

Backup and recovery of Database

MySQL can be backed up in many ways

Method 1: package the database folder / usr/local/mysql/data directly

Method 2: use a dedicated export tool, such as the dumping tool mysqldump that comes with MySQL

(1) backup the database

The format of the command is as follows:

[root@localhost ~] # mysqldump [option] Library name [Table name 1] [Table name 2]... > / backup path / backup file name / / backup some tables in the specified library [root@localhost ~] # mysqldump [option]-databases library name 1 [library name 2]... > / backup path / backup file name / / back up one or more libraries (including all tables) [root@localhost ~] # mysqldump [option]-- all-databases > / backup path / backup file name / / back up all libraries in the MySQL server

(2) restore the database

The format of the command is as follows:

[root@localhost ~] # mysql [option] [library name] [table name] < / backup path / backup file name

When the backup file contains only the backup of the table, the library name must be specified and the library must exist when importing.

When the backup file contains the complete library information, you do not need to specify the library name when importing

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.