MySQL使用者管理、常用SQL語句、MySQLDatabase Backup恢複

標籤：har   sql   常用   oracl   注意   let   ant   tmp   star   

mysql使用者管理1.建立一個普通使用者並授權

[[email protected] ~]# mysql -uroot -p‘szyino-123‘Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 24Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> grant all on *.* to ‘user1‘@‘127.0.0.1‘ identified by ‘szyino-123‘;  //建立一個普通使用者並授權Query OK, 0 rows affected (0.00 sec)

用法解釋說明：

• grant：授權；
• all:表示所有的許可權（如讀、寫、查詢、刪除等操作）；
• .：前者表示所有的資料庫,後者表示所有的表；
• identified by：後面跟密碼，用單引號括起來；
• ‘user1‘@‘127.0.0.1‘:指定IP才允許這個使用者登入，這個IP可以使用%代替，表示允許所有主機使用這個使用者登入;

2.測試登入

[[email protected] ~]# mysql -uuser1 -pszyino-123 //由於指定IP，報錯不能登入Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user ‘user1‘@‘localhost‘ (using password: YES)[[email protected] ~]# mysql -uuser1 -pszyino-123 -h127.0.0.1 //加-h指定IP登入，正常Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 26Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> mysql> grant all on *.* to ‘user1‘@‘localhost‘ identified by ‘szyino-123‘;  //授權localhost，所以該使用者預設使用（監聽）本地mysql.socket檔案，不需要指定IP即可登入Query OK, 0 rows affected (0.00 sec)mysql> ^DBye[[email protected] ~]# mysql -uuser1 -pszyino-123  //正常登入Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 28Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> 

3.查看所有授權

mysql> show grants;+----------------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                                              |+----------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*B1E761CAD4A61F6FD6B02848B5973BC05DE1C315‘ WITH GRANT OPTION || GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION                                                                           |+----------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

4.指定使用者查看授權

mysql> show grants for [email protected]‘127.0.0.1‘;+-----------------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                            |+-----------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘user1‘@‘127.0.0.1‘ IDENTIFIED BY PASSWORD ‘*B1E761CAD4A61F6FD6B02848B5973BC05DE1C315‘ |+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

注意：假設你想給同個使用者授權增加一台電腦IP授權訪問，你就可以直接拷貝查詢使用者授權檔案，複製先執行一條命令再執行第二條，執行的時候把IP更改掉,這樣就可以使用同個使用者密碼在另外一台電腦上登入。常用sql語句1.最常見的查詢語句

第一種形式:

mysql> use db1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select count(*) from mysql.user; +----------+| count(*) |+----------+|        8 |+----------+1 row in set (0.00 sec)//注釋：mysql.user表示mysql的user表，count(*)表示表中共有多少行。

第二種形式:

mysql> select * from mysql.db;//它表示查詢mysql庫的db表中的所有資料mysql> select db from mysql.db;+---------+| db      |+---------+| test    || test\_% |+---------+2 rows in set (0.00 sec)//查詢db表裡的db單個欄位mysql> select db,user from mysql.db;+---------+------+| db      | user |+---------+------+| test    |      || test\_% |      |+---------+------+2 rows in set (0.00 sec)//查看db表裡的db，user多個欄位mysql> select * from mysql.db where host like ‘192.168.%‘\G;//查詢db表裡關於192.168.段的ip資訊

2.插入一行

mysql> desc db1.t1;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(4)   | YES  |     | NULL    |       || name  | char(40) | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> select * from db1.t1;  Empty set (0.00 sec)mysql> insert into db1.t1 values (1, ‘abc‘);  //插入一行資料Query OK, 1 row affected (0.01 sec)mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | abc  |+------+------+1 row in set (0.00 sec)mysql> insert into db1.t1 values (1, ‘234‘);Query OK, 1 row affected (0.00 sec)mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | abc  ||    1 | 234  |+------+------+2 rows in set (0.00 sec)

3.更改表的一行。

mysql> update db1.t1 set name=‘aaa‘ where id=1;Query OK, 2 rows affected (0.01 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | aaa  ||    1 | aaa  |+------+------+2 rows in set (0.00 sec)

4.清空某個表的資料

mysql> truncate table db1.t1;  //清空表Query OK, 0 rows affected (0.03 sec)mysql> select * from db1.t1;Empty set (0.00 sec)mysql> desc db1.t1;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(4)   | YES  |     | NULL    |       || name  | char(40) | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)

5.刪除表

mysql> drop table db1.t1;Query OK, 0 rows affected (0.01 sec)mysql> select * from db1.t1;ERROR 1146 (42S02): Table ‘db1.t1‘ doesn‘t exist

6.刪除資料庫

mysql> drop database db1;Query OK, 0 rows affected (0.00 sec)

mysqlDatabase Backup恢複1.備份恢複庫

[[email protected] ~]# mysqldump -uroot -pszyino-123 mysql > /tmp/mysql.sql  //備份庫Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pszyino-123 -e "create database mysql2"  //建立一個新的庫Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/mysql.sql  //恢複一個庫Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pszyino-123 mysql2Warning: Using a password on the command line interface can be insecure.Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 38Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> select database();+------------+| database() |+------------+| mysql2     |+------------+1 row in set (0.00 sec)

2.備份恢複表

[[email protected] ~]# mysqldump -uroot -pszyino-123 mysql user > /tmp/user.sql  //備份表Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/user.sql  //恢複表Warning: Using a password on the command line interface can be insecure.

3.備份所有庫

[[email protected] ~]# mysqldump -uroot -pszyino-123 -A > /tmp/mysql_all.sqlWarning: Using a password on the command line interface can be insecure.[[email protected] ~]# less /tmp/mysql_all.sql

4.只備份表結構

[[email protected] ~]# mysqldump -uroot -pszyino-123 -d mysql > /tmp/mysql.sqlWarning: Using a password on the command line interface can be insecure.

MySQL使用者管理、常用SQL語句、MySQLDatabase Backup恢複