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)

  Using User1 user login, verify that:
  [Email protected] ~]# mysql-uuser1-p
  Enter Password:
  ERROR 1045 (28000): Access denied for user ' user1 ' @ ' localhost ' (using Password:yes)
  Login failed because MySQL uses the socket login by default. You need to specify an address.
  [Email protected] ~]# mysql-uuser1-h127.0.0.1-p
  Enter Password:
  Welcome to the MySQL Monitor. Commands End With; or \g.
  .....
  Mysql>
  Login successful. Where the grant statement is not recorded in the command history.

• 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

使用root用户登录：mysql> grant SELECT,UPDATE,INSERT on db1.* to ‘user2‘@‘192.168.159.132‘ identified by ‘123456‘;// 创建user2用户，并授权其针对db1库中的所有表有SELECT,UPDATE,INSERT 权限。// user2用户仅能通过ip 192.168.159.132 登录。Query OK, 0 rows affected (0.00 sec)

To view user authorization information:

mysql> show grants;// 查看当前用户的授权信息mysql> show grants for [email protected];// 查看指定用户的授权信息

Change authorization

Usage: User2 users not only need to log on 192.168.159.132, but also need to login on the 192.168.159.133, this time it is necessary to have the authorization of the command all in the execution once

See what Permissions User2 has:mysql> show grants for [email protected] ' 192.168.159.132 '; +--------------------------------- -----------------------------------------------------------------------------------+| Grants for [email protected] |+---- --------------------------------------------------------------------------------------------------------------- -+| GRANT USAGE on *. 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) to perform user2 authorization commands:// Change IP to 192.168.159.133mysql>grant USAGE on *. 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 *. 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)//after authorization succeeds, User3 users can operate on the DB1 in 192.168.159.133. (If you cannot remotely link, check the following firewall rules.) )//User2 use a password on two IP logins. When re-authorizing, the contents of the permission line and the operation of the library table can be modified.

Common SQL statements

Log in with MySQL user first mysqlmysql> use DB1; Switch to DB1 library 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 the MySQL library with a few lines +--- -------+|       COUNT (*) |+----------+| |+----------+1 Row in Set (0.00 sec) mysql> Select * from mysql.db \g;//view everything in the DB table of the MySQL library/* represents all content, \g is to make the output neat * * 1. Row *************************** Host:% db:test user:select_ Priv:y insert_priv:y update_priv:y delete_priv:y create_priv:y DROP_PR Iv: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 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 db and User fields in the DB table in MySQL library//between fields that need to be viewed 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.%, from the DB table in the MySQL library//Like match, *************************** 1. Row *************************** host:192.168.159.131 db:db1 user:user 2 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:ncr Eate_tmp_table_priv:n lock_tables_priv:n create_view_priv:n show_view_priv:n create_routine_priv:n A Lter_routine_priv:n execute_priv:n event_priv:n trigger_priv:n......mysql> CREATE TABLE T 1 (' id ' int (4), ' name ' char (40));//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 ');//Add a row of data to the T1 table 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, the string is quoted as much as possible. mysql> Update db1.t1 set name= ' AAA ' where id=1;//update the contents of the row name in the table with ID 1.  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&Gt Delete from db1.t1 where id=1;//deletes 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; Clear the contents of the table, the table structure is still in query OK, 0 rows affected (0.09 sec) mysql> drop table t1;//Delete tables, the entire table is deleted, including the content and table structure query OK, 0 rows Affecte D (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)//usually try to use less truncate and drop. mysql> use mysql;mysql> Delete from user where user= ' user1 ' and host= ' 127.0.0.1 ';//delete user, you need to specify the user table before deleting the user query OK, 1 ro W 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