MySQL permissions settings and backup restore

The maintenance work of MySQL database system mainly includes the setting of user's rights, backup and recovery of database, this blog will explain these operations.

User Authorization for the database

The root user account of the MySQL database has full access to all libraries and tables, and frequent use of the root account poses some risk to the database server. In the work, usually set up some low-privileged users, only responsible for a portion of the library, table management and maintenance operations, and even to query, modify, delete and other operations to further refine the restrictions, thus minimizing the risk of the database

1. Granting Permissions

GRANT statement: specifically used to set access permissions for the database. When the specified user name does not exist, the GRANT statement creates a new user, otherwise, the user's information will be modified, with the following format:

Grant permission list on Library name. Table name to User name @ source address [identified by ' Password ']

Each of these fields has the following meanings:

* Permissions list: such as "select,insert,update", separated by commas, using all to denote all permissions

* library name. Table Name: The name of the Library and table, you can use the wildcard "*", for example, "yang.*" to indicate that the authorization object is all tables in the Yang Library

* User name @ Source Address: The source address can be a domain name, IP address, you can also use the wildcard "%" to indicate an area or network segment, such as "%.ysf.com", "192.168.1.%" and so on

* identified by: User set the user's password, if omitted, the user's password is empty

User records authorized with the grant statement are saved in the MySQL library in the user, DB, host, Tables_priv, and other related tables, and can take effect without a refresh

2. View Permissions

Show grants statement: specifically used to view authorization information for a database user, the FOR clause allows you to specify which user object to view, with the following statement format:

Show grants for user name @ Source Address

3. Revoke Permissions

Revoke statement: The user revokes the specified user's database permissions, the user can still connect to the MySQL server after revoking permissions, but the corresponding database operation is forbidden, the statement format is as follows:

Revoke permissions list on database name. Table name from user name @ Source Address

For more information on MySQL statements, refer to the MySQL online reference manual

Backup and recovery of database

MySQL backup can be used in many ways

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

Mode 2: use a dedicated export tool, such as a MySQL-brought dump tool mysqldump

(1) Backing Up the database

The command format is as follows:

[[email protected] ~]# mysqldump [options] Library name [table name 1] [table Name 2] ... >/backup path/backup file name//back up some of the tables in the specified library [[email protected] ~]# Mysqldu MP [options]--databases library name 1 [library Name 2] ... >/backup path/backup file name//back up one or more libraries (including all tables in them) [[email protected] ~]# mysqldump [options]--all-da tabases >/backup path/backup file name//back up all libraries in MySQL server

(2) Recovering a database

The command format is as follows:

[[email protected] ~]# mysql [options] [library name] [table name] </backup path/backup file name

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

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

MySQL permissions settings and backup restore