遠(yuǎn)程連接mysql
MySQL之權(quán)限管理
一、MySQL權(quán)限簡介
1、關(guān)于mysql權(quán)限的簡單理解就是允許做權(quán)限以內(nèi)的事情，不可以越界。比如只分配了select權(quán)限，那么就只能操作select，不能操作其它 update、delete insert等
2、那么mysql的權(quán)限是如何實現(xiàn)的呢？這就要說到mysql的兩階段驗證。
(1)、第一階段：服務(wù)器首先會檢查你是否允許連接。因為創(chuàng)建用戶的時候會加上主機限制，可以限制成本地、某個IP、某個IP段、以及任何地方等，只允許你從配置的指定地方登陸。
(2)、第二階段：如果你能連接，mysql會檢查你發(fā)出的每個請求，看你是否有足夠的權(quán)限實施它。比如你要更新某個表、或者查詢某個表，Mysql會查看你對哪個表或者某個列是否有權(quán)限。如、你要運行某個存儲過程，Mysql會檢查你對存儲過程是否有執(zhí)行權(quán)限等。
MYSQL到底都有哪些權(quán)限呢？從官網(wǎng)復(fù)制一個表來看看：
權(quán)限 權(quán)限級別 權(quán)限說明
CREATE 數(shù)據(jù)庫、表或索引 創(chuàng)建數(shù)據(jù)庫、表或索引權(quán)限
DROP 數(shù)據(jù)庫或表 刪除數(shù)據(jù)庫或表權(quán)限
GRANT OPTION 數(shù)據(jù)庫、表或保存的程序 賦予權(quán)限選項
REFERENCES 數(shù)據(jù)庫或表
ALTER 表 更改表，比如添加字段、索引等
DELETE 表 刪除數(shù)據(jù)權(quán)限
INDEX 表 索引權(quán)限
INSERT 表 插入權(quán)限
SELECT 表 查詢權(quán)限
UPDATE 表 更新權(quán)限
CREATE VIEW 視圖 創(chuàng)建視圖權(quán)限
SHOW VIEW 視圖 查看視圖權(quán)限
ALTER ROUTINE 存儲過程 更改存儲過程權(quán)限
CREATE ROUTINE 存儲過程 創(chuàng)建存儲過程權(quán)限
EXECUTE 存儲過程 執(zhí)行存儲過程權(quán)限
FILE 服務(wù)器主機上的文件訪問 文件訪問權(quán)限
CREATE TEMPORARY TABLES 服務(wù)器管理 創(chuàng)建臨時表權(quán)限
LOCK TABLES 服務(wù)器管理 鎖表權(quán)限
CREATE USER 服務(wù)器管理 創(chuàng)建用戶權(quán)限
PROCESS 服務(wù)器管理 查看進程權(quán)限
RELOAD 服務(wù)器管理 執(zhí)行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的權(quán)限
REPLICATION CLIENT 服務(wù)器管理 復(fù)制權(quán)限
REPLICATION SLAVE 服務(wù)器管理 復(fù)制權(quán)限
SHOW DATABASES 服務(wù)器管理 查看數(shù)據(jù)庫權(quán)限
SHUTDOWN 服務(wù)器管理 關(guān)閉數(shù)據(jù)庫權(quán)限
SUPER 服務(wù)器管理 執(zhí)行kill線程權(quán)限
MYSQL的權(quán)限如何分布，就是針對表可以設(shè)置什么權(quán)限，針對列可以設(shè)置什么權(quán)限等等，這個可以從官方文檔中的一個表來說明：
權(quán)限分布 可能的設(shè)置的權(quán)限
表權(quán)限 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
列權(quán)限 'Select', 'Insert', 'Update', 'References'
過程權(quán)限 'Execute', 'Alter Routine', 'Grant'
二、MySQL權(quán)限實戰(zhàn)：
1、GRANT命令使用說明：
先來看一個例子，創(chuàng)建一個只允許從本地登錄的超級用戶ceshi，并允許將權(quán)限賦予別的用戶，密碼為：123456.
mysql> grant all privileges on *.* to ceshi@'localhost' identified by "123456" with grant option;
mysql> grant all privileges on *.* to 'ceshi'@'%' identified by '123456' with grant option;
Query OK, 0 rows affected (0.01 sec)
GRANT命令說明：
ALL PRIVILEGES 是表示所有權(quán)限，你也可以使用select、update等權(quán)限。
ON 用來指定權(quán)限針對哪些庫和表。
*.* 中前面的*號用來指定數(shù)據(jù)庫名，后面的*號用來指定表名。
TO 表示將權(quán)限賦予某個用戶。
ceshi@'localhost' 表示jack用戶，@后面接限制的主機，可以是IP、IP段、域名以及%，%表示任何地方。注意：這里%有的版本不包括本地，以前碰到過給某個用戶設(shè)置了%允許任何地方登錄，但是在本地登錄不了，這個和版本有關(guān)系，遇到這個問題再加一個localhost的用戶就可以了。
IDENTIFIED BY 指定用戶的登錄密碼。
WITH GRANT OPTION 這個選項表示該用戶可以將自己擁有的權(quán)限授權(quán)給別人。注意：經(jīng)常有人在創(chuàng)建操作用戶的時候不指定WITH GRANT OPTION選項導(dǎo)致后來該用戶不能使用GRANT命令創(chuàng)建用戶或者給其它用戶授權(quán)。
備注：可以使用GRANT重復(fù)給用戶添加權(quán)限，權(quán)限疊加，比如你先給用戶添加一個select權(quán)限，然后又給用戶添加一個insert權(quán)限，那么該用戶就同時擁有了select和insert權(quán)限。
2、刷新權(quán)限
使用這個命令使權(quán)限生效，尤其是你對那些權(quán)限表user、db、host等做了update或者delete更新的時候。以前遇到過使用grant后權(quán)限沒有更新的情況，只要對權(quán)限做了更改就使用FLUSH PRIVILEGES命令來刷新權(quán)限。
flush privileges;
3、查看權(quán)限
查看當(dāng)前用戶的權(quán)限：
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
查看某個用戶的權(quán)限：
mysql> show grants for 'jack'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for jack@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jack'@'%' IDENTIFIED BY PASSWORD '*9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4、回收權(quán)限
mysql> revoke delete on *.* from 'jack'@'localhost';
Query OK, 0 rows affected (0.01 sec)
5、刪除用戶
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | |
| rhel5.4 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| rhel5.4 | | |
| localhost | jack | *9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0 |
+-----------+------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> drop user 'jack'@'localhost';
Query OK, 0 rows affected (0.01 sec)
6、對賬戶重命名
mysql> rename user 'jack'@'%' to 'jim'@'%';
Query OK, 0 rows affected (0.00 sec)
7、修改密碼
(1)、用set password命令（修改密碼）
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
Query OK, 0 rows affected (0.00 sec)
(2)、用mysqladmin
[root@rhel5 ~]# mysqladmin -uroot -p123456 password 1234abcd
備注： 格式：mysqladmin -u用戶名 -p舊密碼 password 新密碼
(3)、用update直接編輯user表
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
(4)、在丟失root密碼的時候：
[root@rhel5 ~]# mysqld_safe --skip-grant-tables &
[1] 15953
[root@rhel5 ~]# 130911 09:35:33 mysqld_safe Logging to '/mysql/mysql5.5/data/rhel5.4.err'.
130911 09:35:33 mysqld_safe Starting mysqld daemon with databases from /mysql/mysql5.5/data
[root@rhel5 ~]# mysql -u root
mysql> use mysql
Database changed
mysql> update user set password = PASSWORD('123456') where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
三、mysql終端數(shù)據(jù)備份恢復(fù) -h ip地址
1、備份數(shù)據(jù) mysqldump
(1) 備份數(shù)據(jù)庫
$ mysqldump -umlpfpc -pmysql_mlpfpc mlpfpc > database_dump.txt
Enter password: ******
備注：-u用戶名 -p密碼 庫名（--all-databases 所有數(shù)據(jù)庫） > /路徑加名稱
(2) 備份數(shù)據(jù)表
導(dǎo)出：mysqldump -u root -p database_name table_name > dump.txt
備注：-u用戶名 -p密碼 庫名 表名 > /路徑加名稱
2、恢復(fù)數(shù)據(jù) source、mysql、mysqlimport
(1) 導(dǎo)入數(shù)據(jù)庫
導(dǎo)入單個：mysql -uroot -p123456 < runoob.sql
備注：mysql -u用戶名 -p密碼 < 要導(dǎo)入的數(shù)據(jù)庫數(shù)據(jù)(runoob.sql)
source
CREATE DATABASE test->use test ->set names utf8 -> source /路徑 名稱
登錄mysql ->創(chuàng)建庫->打開庫->設(shè)置編碼->導(dǎo)入備份數(shù)據(jù)庫
mysqlimport 沒試過去實踐
(2) 恢復(fù)數(shù)據(jù)表
導(dǎo)出：mysqldump -u root -p database_name table_name > dump.txt
備注：-u用戶名 -p密碼 庫名 表名 > /路徑加名稱
3、數(shù)據(jù)庫操作
(1)、刪除操作
刪庫：drop database test;
刪表：drop table test;
刪字段 ：alter table 表名 DROP COLUMN 字段名;
刪數(shù)據(jù) ：update from 表名 where id='id'
(2)、增加操作
增加字段:alter table user add COLUMN new2 VARCHAR(20) NOT NULL;
(3)、修改操作
字段類型:alter table user MODIFY new1 VARCHAR(10);
字段名稱:alter table user CHANGE new1 new4 VARCHAR(10);
表數(shù)據(jù)：UPDATE user SET name='name1' where id='要更改id';
(4)、查詢操作
AVG COUNT MIN MAX SUM
求平均值 返回行的數(shù)目 最小 最大 求和
where having group by ORDER BY with rollup
條件 分組 數(shù)據(jù)進行分組 排序 匯集總合
id=1 having count(1)>4; group by id order by as desc/asc group by 字段 with rollup;
鏈接方式：left join right join inner join
表名 鏈接方式 表名 on 條件=條件
函數(shù)返回指定的字段中第一個記錄的值 : SELECT FIRST(column_name) FROM table_name
函數(shù)返回指定的字段中最后一個記錄的值 : SELECT LAST(column_name) FROM table_name
表： select * from user where id='要查詢id';
儲存字段類型為（1,2,6,8）： select * from user where FIND_IN_SET('1',id);
多個狀態(tài)等于： select * from user where id in(1,2,3);
多個狀態(tài)不等于： select * from user where id not in(1,2,3);
統(tǒng)計每人下面的人數(shù)： SELECT supid,COUNT(t_userid),t_userid FROM denba_user GROUP BY t_userid;
字段不重復(fù)： SELECT DISTINCT supid,t_userid FROM denba_user;
模糊查詢 SELECT * FROM table_name WHERE column LIKE '%XXXX%'