MySQL user management, common statements, data sub-backup recovery

MySQL user management create user and authorize

• Specify the logon IP

  Create an authorized new user with the root user login:
  Mysql> Grant all in . to ' user1 ' @ ' 127.0.0.1 ' identified by ' 123456 ';
  All operations (additions, deletions and modifications)
  First wildcard with all library names, second wildcard all table names
  User1 for user name
  127.0.0.1 Specifies the logon IP, and the wildcard% is used for all IPs.
  ' 123456 ' is the login password for the User1 user
  Query OK, 0 rows Affected (0.00 sec)

• Specify a login socket

  Use the root user to re-authorize the User1 User:
  Mysql> Grant all on . to ' user1 ' @ ' localhost ' identified by ' 123456 ';

  To exit the root user, log in using the User1 User:
  [Email protected] ~]# mysql-uuser1-p
  Enter Password:
  Welcome to the MySQL Monitor. Commands End With; or \g.
  .....
  Mysql>
  Because the specified login host is localhost, the user uses the local mysql.socket file by default (listening) and does not need to specify an IP to log on.

For specific permission authorizations

Use the root user to login:mysql> grant Select,update,insert on db1.* to ' user2 "@ ' 192.168.159.132 ' identified by ' 123456 ';// Create User2 users and authorize them to have Select,update,insert permissions for all tables in the DB1 library. User2 users can only log on via IP 192.168.159.132. Query OK, 0 rows Affected (0.00 sec)

To view user authorization information:

Mysql> Show grants;//View authorization information for the current user mysql> show grants for [email protected];//View authorization information for the specified user

Change authorization

usage: User2 users not only need to log on on 192.168.159.132, but also need to log on to 192.168.159.133, this time they need to execute the command all over again

See what permissions User2 have: mysql> show grants for [email protected] ' 192.168.159.132 '; +--------- -----------------------------------------------------------------------------------------------------------+|  grants for [email protected]                                                                                       |+ --------------------------------------------------------------------------------------------------------------- -----+| grant usage on *.* to  ' user2 ' @ ' 192.168.159.132 '  identified by  password  ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 '  | |  GRANT SELECT, INSERT, UPDATE ON  ' db1 ' .* to  ' user2 ' @ ' 192.168.159.132 '        +------------------------------------------------------------------- -------------------------------------------------+2 rows in set  (0.00 SEC) Execute user2 Authorization command://  change IP to 192.168.159.133mysql>grant usage on *.* to  ' user2 ' @ ' 192.168.159.132 '  IDENTIFIED BY PASSWORD  ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ' MySQL > GRANT SELECT, INSERT, UPDATE ON  ' db1 ' .* to  ' user2 ' @ ' 192.168.159.132 '; view [email protected] 's authorization:mysql> show grants for [email  Protected] ' 192.168.159.133 '; +--------------------------------------------------------------------------------- -----------------------------------+| grants for [email protected]                                                                                       |+------------------------------------------------------ --------------------------------------------------------------+| grant usage on *.*  to  ' user2 ' @ ' 192.168.159.133 '  IDENTIFIED BY PASSWORD  ' * 6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 '  | |  GRANT SELECT, INSERT, UPDATE ON  ' db1 ' .* to  ' user2 ' @ ' 192.168.159.133 '        +------------------------------------------------------------------- -------------------------------------------------+2 rows in set  (0.00 SEC)//  authorized success, User3 users can operate on the DB1 in 192.168.159.133. (If you cannot remotely link, check the following firewall rules.) )// user2 is logged on two IPs using a password.   Re-authorization, the contents of the permission line and the operation of the library table can be modified.

Common SQL statements

Switch to DB1 library with MySQL user login mysqlmysql> use db1; //First database changedmysql> show  tables; //See which tables are in the current library. +---------------+| tables_in_db1 |+---------------+| t1             |+---------------+1 row in set  (0.00 sec) MySQL > select count (*)  from mysql.user;//  view the user table in MySQL library with a few lines +----------+| count (*)  |+----------+|       15 |+----------+1 row in set   (0.00 SEC) mysql> select * from mysql.db \g;//  View all the contents of the DB table in the MySQL library//  *  represents everything, \g is for the output to be neat *************************** 1. row ***************************                  host:  %                   db: test                  user:           select_priv: y           Insert_priv: Y           Update_priv: Y           delete_priv: y          create_priv: y             Drop_priv: Y            grant_priv: n      references_priv:  Y           Index_priv: Y            Alter_priv: YCreate_tmp_table_priv: Y      lock_tables_priv: y     create_view_priv: y        Show_view_priv: Y  Create_routine_priv: Y   Alter_routine_priv:  n         execute_priv: n            Event_priv: Y          trigger_priv: y......//  This operation usually try not to use, the content of the case select* will be more resources. mysql> select db from mysql.db;//  View db in this field, in the DB table of the MySQL library +---------+| db       |+---------+| test    | |  test\_% | |  db1     | |  db1     | |  db1     | |  db1     | |  db1     | |  db1     |+---------+8 rows in set  (0.00 SEC) mysql> select db,user from mysql.db;//  View the DB and User fields in the DB table in the MySQL library//  The fields you need to see are separated by commas +---------+-------+| db      | user  |+------- --+-------+| test    |       | |  test\_% |       | |  db1     | user2 | |  db1     | user3 | |  db1     | user4 | |  db1     | user2 | |  db1     | user3 | |  db1     | user2 |+---------+-------+8 rows in set   (0.00 SEC) mysql> select * from mysql.db where host like  ' 192.168.% ' \g; //  view the contents of all IP matching 192.168.%,// like   matches from the DB table in the MySQL library, **********************  1. row ***************************                  Host: 192.168.159.131                    Db: db1                  User: user2           Select_priv: Y           insert_priv: y          update_priv:  Y          Delete_priv: N           Create_priv: N             drop_priv: n           grant_ priv: n      References_priv: N            Index_priv: N           Alter_priv:  ncreate_tmp_table_priv: n     lock_tables_priv: n      Create_view_priv: N       Show_view_priv: N   create_routine_priv: n   alter_routine_priv: n          execute_priv: n           event_ priv: n         trigger_priv: n......mysql>  CREATE TABLE T1 (' ID '  int (4), ' name '  char ());//  CREATE table under DB1 Library ti//  has ID and name two fields query  OK, 0 rows affected  (0.39 sec) mysql> insert into db1.t1  VALUES (1, ' abc ');//  to the T1 tableAdd a row of data query ok, 1 row affected  (0.00 SEC) mysql> select * from  db1.t1;//  View the contents of the T1 table +------+------+| id   | name |+------+------+|     1 | abc  |+------+------+1 row in set  (0.00  SEC)//  When adding data, enclose the string as much as possible in single quotes. Mysql> update db1.t1 set name= ' AAA '  where id=1;//  Update table in the row with ID 1 name  The content. query ok, 1 row affected  (0.00 sec) rows matched: 1  changed:  1  warnings: 0mysql> select * from db1.t1;+------+------+| id    | name |+------+------+|    1 | aaa  |+--- ---+------+1 row in set  (0.00 sec) mysql> delete from db1.t1  where id=1;//  Delete the contents of the row with ID 1 in the table query ok, 1 row affected  (0.00 SEC) mysql> select * from db1.t1; empty set  (0.00 SEC) mysql> truncate db1.t1; //  clears the contents of the table, and the table structure is still query  ok, 0 rows affected  (0.09 sec) mysql> drop table t1;//  Delete table, The entire table is deleted, including the content and table structure query ok, 0 rows affected  (0.04 SEC) mysql> drop  database db1;//  Delete the library, the entire library is deleted, including the table query ok, 0 rows affected  (0.13 SEC)//   Use less truncate and drop as usual. Mysql> use mysql;mysql> delete from user where user= ' user1 '  and  host= ' 127.0.0.1 ';//  Delete the user, you need to specify the user table before deleting the user query ok, 1 row affected  (0.06  Sec

MySQL Database backup recovery

• Backup Recovery Library

  [Email protected] ~]# mysqldump-uroot-p123456 MySQL >/tmp/mysqlbak.sql
  Back up the specified library (MySQL library) into the/tmp/mysqlbak.sql file

  [Email protected] ~]# mysqldump-uroot-p123456-a >/rmp/mysqlbak_all.sql
  Back up all libraries into the/tmp/mysqbak_all.sql file

  [Email protected] ~]# mysql-uroot-p123456 mysql</tmp/mysqlbak.sq
  Back to the MySQL library. You can also revert to the new library (need to create a new library)

• Backup Recovery table

  [[Email protected] ~] #mysql-uroot-p123456 mysql user >/tmp/user.sql
  Backs up the specified table. A space followed by a table name after the library name.

  [[Email protected] ~] #mysqldump-uroot-p123456-d mysql >/tmp/mysql_tb.sql
  Back up table structure only

  [[Email protected] ~] #mysql-uroot-p123456 MySQL </tmp/user.sql
  No need to add a table name when recovering

  Small amount of data with mysqldump Backup can also, large amount of backup does not apply.

MySQL user management, common statements, data sub-backup recovery