mysql使用者管理、常用語句、資料分備份恢複

標籤：使用者管理

mysql使用者管理建立使用者並授權

• 指定登入ip

  使用root使用者登入錄建立授權新使用者：
  mysql> grant all on . to 'user1'@'127.0.0.1' identified by '123456'；
  // all 所有操作（增刪查改）
  // 第一個 通配所有庫名，第二個通配所有表名
  // user1 為使用者名稱
  // 127.0.0.1 指定登入ip，可用萬用字元%表示所有ip。
  // ‘123456’ 為user1使用者的登入密碼
  Query OK, 0 rows affected (0.00 sec)

  使用user1使用者登入，驗證：
  [[email protected] ~]# mysql -uuser1 -p
  Enter password: 
  ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
  // 登入失敗，因為mysql預設使用socket登入。需要指定地址。
  [[email protected] ~]# mysql -uuser1 -h127.0.0.1 -p
  Enter password: 
  Welcome to the MySQL monitor. Commands end with ; or \g.
  .....
  mysql> 
  // 登入成功。其中grant語句是不會記錄到命令曆史中的。

• 指定登入socket

  使用root使用者重新對user1使用者授權：
  mysql> grant all on . to 'user1'@'localhost' identified by '123456'；

  退出root使用者，使用user1 使用者登入：
  [[email protected] ~]# mysql -uuser1 -p
  Enter password: 
  Welcome to the MySQL monitor. Commands end with ; or \g.
  .....
  mysql> 
  // 因為指定登入主機為localhost，所以該使用者預設使用（監聽）本地mysql.socket檔案，不需要指定IP即可登入。

針對具體的許可權授權

使用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)

查看使用者授權資訊：

mysql> show grants;// 查看目前使用者的授權資訊mysql> show grants for [email protected];// 查看指定使用者的授權資訊

更改授權

使用方式：user2使用者不僅需要在192.168.159.132上登入，還需要在192.168.159.133上登入，這時候就需要把授權的命令全部在執行一遍

查看user2具有哪些許可權：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)執行user2的授權命令：// 將ip改成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'；查看[email protected]的授權：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)// 授權成功後，user3使用者就可以在192.168.159.133對db1進行操作。（如不能遠程連結，檢查以下防火牆規則。）// user2在兩個ip上登入使用的是用一個密碼。// 重新授權時，許可權行的內容和操作的庫表能修改。

常用sql語句

先用mysql使用者登入mysqlmysql> use db1; //切換到db1庫Database changedmysql> show tables; //查看當前庫中有哪些表。+---------------+| Tables_in_db1 |+---------------+| t1            |+---------------+1 row in set (0.00 sec)mysql> select count(*) from mysql.user;// 查看mysql庫中的user表有幾行+----------+| count(*) |+----------+|       15 |+----------+1 row in set (0.00 sec)mysql> select * from mysql.db \G;// 查看mysql庫的db表中的所有內容// * 表示所有內容，\G是為了讓輸出內容整齊*************************** 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......// 這種操作平時盡量不要用，內容多的情況下select*會比較佔資源。mysql> select db from mysql.db;// 查看db這個欄位，在mysql庫的db表中+---------+| db      |+---------+| test    || test\_% || db1     || db1     || db1     || db1     || db1     || db1     |+---------+8 rows in set (0.00 sec)mysql> select db,user from mysql.db;// 查看db和user欄位，在mysql庫的db表中// 需要查看的欄位之間用逗號隔開+---------+-------+| 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; // 查看所有IP匹配192.168.%的內容，從mysql庫的db表中// like  匹配，*************************** 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(40));// 在db1庫下建立表ti// 有id和name兩個欄位Query OK, 0 rows affected (0.39 sec)mysql> insert into db1.t1 values(1,'abc');// 向t1表中添加一行資料Query OK, 1 row affected (0.00 sec)mysql> select * from db1.t1;// 查看t1表中的內容+------+------+| id   | name |+------+------+|    1 | abc  |+------+------+1 row in set (0.00 sec)// 添加資料時，字串盡量加單引號。mysql> update db1.t1 set name='aaa' where id=1;// 更新表中id為1的那一行name 的內容。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;// 刪除表中id為1那一行的內容Query OK, 1 row affected (0.00 sec)mysql> select * from db1.t1;Empty set (0.00 sec)mysql> truncate db1.t1; // 清空表中的內容，表結構還在Query OK, 0 rows affected (0.09 sec)mysql> drop table t1;// 刪除表，整個表都刪除，包括內容和表結構Query OK, 0 rows affected (0.04 sec)mysql> drop database db1;// 刪除庫，整個庫都刪除，包括表Query OK, 0 rows affected (0.13 sec)// 平時盡量少用truncate和drop。mysql> use mysql；mysql> delete from user where User='user1' and Host='127.0.0.1';// 刪除使用者，在刪除使用者前需先指定使用者表Query OK, 1 row affected (0.06 sec)

mysqlDatabase Backup恢複

• 備份恢複庫

  [[email protected] ~]# mysqldump -uroot -p123456 mysql >/tmp/mysqlbak.sql
  // 備份指定庫（mysql庫）到/tmp/mysqlbak.sql檔案中

  [[email protected] ~]# mysqldump -uroot -p123456 -A >/rmp/mysqlbak_all.sql
  // 備份所有庫到/tmp/mysqbak_all.sql 檔案中

  [[email protected] ~]# mysql -uroot -p123456 mysql< /tmp/mysqlbak.sq
  // 恢複到mysql庫中。也可以恢複到新的庫中（需建立庫）

• 備份恢複表

  [[email protected] ~]#mysql -uroot -p123456 mysql user > /tmp/user.sql
  // 備份指定表。在庫名後空格跟表名就行

  [[email protected] ~]#mysqldump -uroot -p123456 -d mysql > /tmp/mysql_tb.sql
  // 只備份表結構

  [[email protected] ~]#mysql -uroot -p123456 mysql < /tmp/user.sql
  // 恢複的時候不需要加表名

  // 資料量小用mysqldump備份還可以，備份量大不適用。

mysql使用者管理、常用語句、資料分備份恢複