mysql用戶管理

1.創(chuàng)建一個普通用戶并授權(quán)

[root@gary-tao ~]# 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 24 Server 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 its affiliates. Other names may be trademarks of their respective owners.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'; //創(chuàng)建一個普通用戶并授權(quán) Query OK, 0 rows affected (0.00 sec)

用法解釋說明：

• grant：授權(quán)；
• all:表示所有的權(quán)限（如讀、寫、查詢、刪除等操作）；
• .：前者表示所有的數(shù)據(jù)庫,后者表示所有的表；
• identified by：后面跟密碼，用單引號括起來；
• 'user1'@'127.0.0.1':指定IP才允許這個用戶登錄，這個IP可以使用%代替，表示允許所有主機使用這個用戶登錄;

2.測試登錄

[root@gary-tao ~]# 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) [root@gary-tao ~]# 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 26 Server 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 its affiliates. Other names may be trademarks of their respective owners.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'; //授權(quán)l(xiāng)ocalhost，所以該用戶默認使用（監(jiān)聽）本地mysql.socket文件，不需要指定IP即可登錄 Query OK, 0 rows affected (0.00 sec)mysql> ^DBye [root@gary-tao ~]# 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 28 Server 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 its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

3.查看所有授權(quán)

mysql> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | 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.指定用戶查看授權(quán)

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

注意：假設(shè)你想給同個用戶授權(quán)增加一臺電腦IP授權(quán)訪問，你就可以直接拷貝查詢用戶授權(quán)文件，復制先執(zhí)行一條命令再執(zhí)行第二條，執(zhí)行的時候把IP更改掉,這樣就可以使用同個用戶密碼在另外一臺電腦上登錄。

常用sql語句

1.最常見的查詢語句

第一種形式:

mysql> use db1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changed mysql> 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表中的所有數(shù)據(jù)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表里關(guān)于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'); //插入一行數(shù)據(jù) 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.清空某個表的數(shù)據(jù)

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.刪除數(shù)據(jù)庫

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

mysql數(shù)據(jù)庫備份恢復

1.備份恢復庫

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 mysql > /tmp/mysql.sql //備份庫 Warning: Using a password on the command line interface can be insecure. [root@gary-tao ~]# mysql -uroot -pszyino-123 -e "create database mysql2" //創(chuàng)建一個新的庫 Warning: Using a password on the command line interface can be insecure. [root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/mysql.sql //恢復一個庫 Warning: Using a password on the command line interface can be insecure. [root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2 Warning: Using a password on the command line interface can be insecure. Reading table information for completion of table and column names You 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 38 Server 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 its affiliates. Other names may be trademarks of their respective owners.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.備份恢復表

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

3.備份所有庫

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 -A > /tmp/mysql_all.sql Warning: Using a password on the command line interface can be insecure. [root@gary-tao ~]# less /tmp/mysql_all.sql

4.只備份表結(jié)構(gòu)

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 -d mysql > /tmp/mysql.sql Warning: Using a password on the command line interface can be insecure.

轉(zhuǎn)載于:https://blog.51cto.com/taoxie/2061242

總結(jié)

以上是生活随笔為你收集整理的MySQL用户管理、常用SQL语句、MySQL数据库备份恢复的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。