MySQL database management

MySQL Rights Management

• The Rights management information is stored in,,,, user db and in host table_priv column_priv procs_priv the table
• Through CREATE , DROP permissions can create new database/table, delete database/table
• SELECT,,, to INSERT UPDATE increase the DELETE deletion of existing tables
• INDEXAllow the creation and deletion of indexes on existing tables

MySQL Account management

• Using GRANT statements
  e.g.:GRANT ALL PRIVILEGES ON *.* TO ‘monkey‘@‘localhost‘ IDENTIFIED BY ‘some_password‘ WITH GRANT OPTION;
GRANT RELOAD,PROCESS ON *.* TO ‘admin‘@‘localhost‘;
GRANTCommand interpretation: PRIVILEGES represents any permission, or the following can be SELECT / UPDATE ; ON Indicates which tables are affected, the preceding represents the database, and the subsequent representation table; TO Indicates which user is authorized, the previous user name, the following can fill IP,IP segment, hostname or% (for any), followed by the optional password of the specified user
• Direct operation of the MySQL authorization form (user) Note that you can view the table structure before inserting and insert () at a time, DESCRIBE insert the password field to remember to PASSWORD(<密码>) encrypt, or insert failed; FLUSH PRIVILEGES Otherwise, changes to the authorization table will only be discovered if the server is restarted.

Remove user

• DROP USER

Restricting user account Resources

• You can restrict users to the following resources: 1. Number of queries issued per hour; 2. Number of updates issued per hour; 3. Number of servers that can be connected per hour
• Use GRANT statements to implement resource constraints: In fact, the above statement in account management with a GRANT WITH clause can be, e.g.:GRANT ALL ON customer TO ‘admin‘@‘localhost‘ IDENTIFIED BY ‘some_password‘ WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATE_PER_HOUR 15 MAX_CONNECTIONS_PER_HOUR 20 MAX_USER_CONNECTION 10;

Modify User Password

• To add a password for an existing user:mysqladmin -u root -h hostname password ‘new password‘;
• Change Password:SET PASSWORD FOR ‘user‘@‘localhost‘ = PASSWORD(‘new password‘);

Database backup and Recovery

• mysqldump/source

MySQL database management