MySQL database maintenance comparison

Maintain MySQL database 1. database user authorization grant permission list on database name. Table name to user name @ Source Address [identifiedby

Maintain MySQL database 1. database user authorization grant permission list on database name. Table name to user name @ Source Address [identified

Maintain the MySQL database

1. database user authorization
Grant permission list on database name. Table name to user name @ Source Address [identified by 'Password'];
Permission list: Used to list various database operations authorized for use, separated by commas. For example, "select, insert, update ".
All indicates all permissions and can be authorized to perform any operations.
Database Name. Table Name: Specifies the database and table name for the authorized operation. The wife can use the wildcard "*" to indicate all.
User @ Source Address: used to specify the user name and the client address that is allowed to access,
That is, who can connect and where can connect.
The source address can be a domain name, ip address, or "%" to indicate all addresses in a region or CIDR block, for example:
"% .Linuxidc.com", "192.168.1.% ".
Identified by: used to set the password string used BY the user to connect to the database. If this parameter is omitted, the password is blank.
Note: user records authorized using the grant statement,
It is saved to the user, db, tables_priv, and other related tables of the mysql database,
It takes effect without refreshing.
Grant select on auth. * to 'wwu' @ 'localhost' identified by '123 ';
Verification:
Mysql-u wwu-p123123
Select * from auth. users;
Select * from mysql. user;
Create database linuxfan; # Set databases for remote website servers and grant permissions
Grant all on linuxfan. * to 'dbuser' @ '192. 168.1.95 'identified by '123 ';
Show grants for username @ source address; # view Permissions
Show grants for 'dbuser' @ '192. 168.1.95 ';
Revoke permission list on database name. Table name from user name @ Source Address
Revoke all on auth. * from 'wwu' @ 'localhost ';
Show grants for 'wwu' @ 'localhost ';

2. database backup and recovery
Back up database
Mysqldump [Option] database name [Table Name 1] [Table name 2]...>/backup path/backup file name # export some tables in the specified database
Mysqldump [Option] -- database name 1 [database name 2]...>/backup path/backup file name # export one or more complete databases
Mysqldump [Option] -- all-databases>/backup path/backup file name # Back up the entire mysql database
Mysqldump-uroot-p123123 mysql user> mysql-user. SQL
Mysqldump-uroot-p123123 -- database auth> auth. SQL
Mysqldump-uroot-p123123 -- opt -- all-databases> all-data. SQL
Grep-v "^ --" auth. SQL | grep-v "^/" | grep-v "^ $" # view backup file content
Note: The SQL script file exported using mysqldump is a text file, where "/*... */" or "--" starts with the annotation information.
Restore database
Mysql [Option] [database name [Table name] </backup path/backup file name
# When the backup file only contains the table backup, but does not contain the statement for creating a database,
# The database name must be specified during the import operation, and the database must exist.
Mysql-uroot-p123123 test ### If the backup file contains the complete database information, you do not need to specify the database name when performing the import operation.
Cd/usr/local/mysql/var/
Mv linuxfan auth/tmp/# transfer some databases and simulate faults
Mysql-uroot-p123123 <~ /All-data. SQL ### perform recovery operations
Ls linuxfan auth # confirm the result