這裏顯示兩個版本的差異處。
Both sides previous revision 前次修改 下次修改 | 前次修改 | ||
database:mysql:account [2015/04/26 18:00] ali88 [以不用重新啟動MySQL方式,來恢復MySQL密碼] |
database:mysql:account [2015/05/10 11:31] (目前版本) |
||
---|---|---|---|
行 39: | 行 39: | ||
mysql>GRANT ALL PRIVILEGES ON *.* TO '使用者'@'主機位置或IP' IDENTIFIED BY '密碼' WITH GRANT OPTION; | mysql>GRANT ALL PRIVILEGES ON *.* TO '使用者'@'主機位置或IP' IDENTIFIED BY '密碼' WITH GRANT OPTION; | ||
</code> | </code> | ||
+ | ======看權限 show grants====== | ||
+ | 看目前此帳號登入的權限<code>mysql> show grants;</code> | ||
+ | 看某個**'使用者'@'主機位置或IP'**的權限,通常只有MySQL管理者才給予查看的權利<code>mysql >show grants for ali@'localhost';</code> | ||
+ | ======撤銷權限 revoke====== | ||
+ | *revoke 只能撤銷權限,無法刪除帳號。 | ||
+ | <code>mysql > revoke SELECT,UPDATE,DELETE,DROP,INSERT,CREATE ON mysql.* from ali@localhost;show grants for ali@'localhost';</code> | ||
+ | <code>+------------------------------------------------------------------------------------------------------------+ | ||
+ | | Grants for ali@localhost | | ||
+ | +------------------------------------------------------------------------------------------------------------+ | ||
+ | | GRANT USAGE ON *.* TO 'ali'@'localhost' IDENTIFIED BY PASSWORD '*1CFD39EAD5BAE48B8040DAA7F969AFDEC465B414' | | ||
+ | +------------------------------------------------------------------------------------------------------------+</code> | ||
======刪帳號====== | ======刪帳號====== | ||
語法: | 語法: | ||
行 49: | 行 60: | ||
</code> | </code> | ||
+ | ======Mysql登入 存取權限 範例====== | ||
+ | 根據上圖 Mysql登入 存取權限流程,列出 **user**全域權限表;db權限表;tables_priv 權限表;columns權限表等範例。 | ||
+ | * **mysql.user表**<code>mysql> grant all privileges on *.* to ali@'localhost' identified by '1234'</code>觀看權限表的變化<code>mysql > select * from mysql.user where user='ali' \G</code> | ||
+ | * **mysql.db表**<code>mysql> show tables from world; | ||
+ | | Tables_in_world | | ||
+ | +-----------------+ | ||
+ | | City | | ||
+ | | Country | | ||
+ | | CountryLanguage | | ||
+ | +-----------------+</code><code>grant all privileges on world.* to ali22@'localhost' identified by 'qwedcxza';</code>觀看權限表的變化<code>select * from mysql.user where user='ali22' \G</code><code>select * from mysql.db where user='ali22' \G</code> | ||
+ | * **mysql.table_priv表**<code>grant all privileges on world.Country to ali33@'localhost' identified by 'qwedcxza';</code>觀看權限表的變化<code>select * from mysql.user where user='ali33' \G</code><code>select * from mysql.db where user='ali33' \G</code><code>select * from mysql.tables_priv where user='ali33' \G</code> | ||
+ | * **mysql.columns_priv表** | ||
+ | 只給予更新權限 DB **world**/table **City**/欄位名稱columns **Name,CountryCode**<code>show columns from world.City; | ||
+ | +-------------+----------+------+-----+---------+----------------+ | ||
+ | | Field | Type | Null | Key | Default | Extra | | ||
+ | +-------------+----------+------+-----+---------+----------------+ | ||
+ | | ID | int(11) | NO | PRI | NULL | auto_increment | | ||
+ | | Name | char(35) | NO | | | | | ||
+ | | CountryCode | char(3) | NO | | | | | ||
+ | | District | char(20) | NO | | | | | ||
+ | | Population | int(11) | NO | | 0 | | | ||
+ | +-------------+----------+------+-----+---------+----------------+ | ||
+ | </code><code>grant update(Name,CountryCode) on world.City to ali66@'localhost' identified by 'qwedcxza';</code>觀看權限表的變化<code>mysql > select * from mysql.user where user='ali66' \G</code><code>mysql> select * from mysql.db where user='ali66' \G</code><code>mysql> select * from mysql.tables_priv where user='ali66' \G</code><code>mysql> select * from mysql.columns_priv where user='ali66' \G</code> | ||
======忘記root密碼時該如何處理====== | ======忘記root密碼時該如何處理====== | ||
作法原理:如作業系統登入安全模式一般,再去修改root的密碼 | 作法原理:如作業系統登入安全模式一般,再去修改root的密碼 | ||
行 92: | 行 126: | ||
======以不用重新啟動MySQL方式,來恢復MySQL密碼====== | ======以不用重新啟動MySQL方式,來恢復MySQL密碼====== | ||
上個小節找回MySQL root密碼 需要重新啟動 MySQL服務。假若正在上線的MySQL,要使用某一個帳號登入,此時忘記了密碼而無法登入,那如何在不需要重新啟動 MySQL 前提之下,來恢復密碼。 | 上個小節找回MySQL root密碼 需要重新啟動 MySQL服務。假若正在上線的MySQL,要使用某一個帳號登入,此時忘記了密碼而無法登入,那如何在不需要重新啟動 MySQL 前提之下,來恢復密碼。 | ||
- | 利用再創建一個MySQL實例方式,來恢復密碼。 | + | |
+ | **利用再創建一個MySQL實例方式,來恢復密碼。** | ||
+ | |||
+ | ===需要恢復密碼的 實例 (3306)=== | ||
+ | 假若有一個帳號 test1 ,忘記密碼。 | ||
+ | <code> | ||
+ | # mysql -B -uroot -h127.0.0.1 -P3306 -p -e "select host,user,password from mysql.user where user='test1'" | ||
+ | host user password | ||
+ | 127.0.0.1 test1 *A4B6157319038724E3560894F7F932C8886EBFCF | ||
+ | </code> | ||
===建立一個實例=== | ===建立一個實例=== | ||
此實例預設的儲存引擎用MyISAM。 | 此實例預設的儲存引擎用MyISAM。 | ||
行 103: | 行 146: | ||
<code># mysql -uroot -h127.0.0.1 -P3307 -p | <code># mysql -uroot -h127.0.0.1 -P3307 -p | ||
mysql2></code> | mysql2></code> | ||
- | ===需要恢復密碼的 實例 (3306)=== | + | |
- | 假若有一個帳號 test1 ,忘記密碼。 | + | ===複製 實例 3306 /var/lib/mysql/mysql/user.*=== |
<code> | <code> | ||
- | # mysql -B -uroot -h127.0.0.1 -P3306 -p -e "select host,user,password from mysql.user where user='test1'" | + | # cp -p /var/lib/mysql/mysql/user.* /Mydata/mysql/ |
- | host user password | + | cp:是否覆寫 ‘/Mydata/mysql/user.frm’? Y |
- | 127.0.0.1 test1 *A4B6157319038724E3560894F7F932C8886EBFCF | + | cp:是否覆寫 ‘/Mydata/mysql/user.MYD’? Y |
+ | cp:是否覆寫 ‘/Mydata/mysql/user.MYI’? Y | ||
+ | |||
+ | |||
+ | # mysql -uroot -h127.0.0.1 -P3307 -p | ||
+ | mysql2>select host,user,password from mysql.user \g | ||
+ | +-----------+-------+-------------------------------------------+ | ||
+ | | host | user | password | | ||
+ | +-----------+-------+-------------------------------------------+ | ||
+ | | localhost | root | | | ||
+ | | andyhome | root | | | ||
+ | | 127.0.0.1 | root | | | ||
+ | | localhost | ali88 | *A6486D4F5065D0D98142AAE0603CECF90E57BDAB | | ||
+ | +-----------+-------+-------------------------------------------+ | ||
+ | |||
+ | mysql2>flush tables; | ||
+ | mysql2>select host,user,password from mysql.user \g | ||
+ | +--------------+-------+-------------------------------------------+ | ||
+ | | host | user | password | | ||
+ | +--------------+-------+-------------------------------------------+ | ||
+ | | localhost | root | | | ||
+ | | andyhome | root | | | ||
+ | | 127.0.0.1 | root | | | ||
+ | | localhost | ali88 | *A6486D4F5065D0D98142AAE0603CECF90E57BDAB | | ||
+ | | localhost | andy | | | ||
+ | | 127.0.0.1 | test1 | *A4B6157319038724E3560894F7F932C8886EBFCF | | ||
+ | | 192.168.88.% | ali | *A6486D4F5065D0D98142AAE0603CECF90E57BDAB | | ||
+ | +--------------+-------+-------------------------------------------+ | ||
+ | 7 rows in set (0.00 sec) | ||
+ | </code> | ||
+ | ===在 實例 3307改變密碼=== | ||
+ | <code> | ||
+ | mysql2> select host,user,password from mysql.user where user='test1'; | ||
+ | mysql2>select PASSWORD('test1234'); | ||
+ | +-------------------------------------------+ | ||
+ | | PASSWORD('test1234') | | ||
+ | +-------------------------------------------+ | ||
+ | | *3D3B92F242033365AE5BC6A8E6FC3E1679F4140A | | ||
+ | +-------------------------------------------+ | ||
+ | mysql2>update mysql.user set password='*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' where user='test1'; | ||
+ | mysql2>flush privileges; | ||
+ | </code> | ||
+ | ===複製實例 3307 /Mydata/mysql/user.* 到 實例 3306=== | ||
+ | <code> | ||
+ | # cp -p /Mydata/mysql/user.* /var/lib/mysql/mysql/ | ||
+ | cp:是否覆寫 ‘/var/lib/mysql/mysql/user.frm’? Y | ||
+ | cp:是否覆寫 ‘/var/lib/mysql/mysql/user.MYD’? Y | ||
+ | cp:是否覆寫 ‘/var/lib/mysql/mysql/user.MYI’? Y | ||
+ | |||
+ | #mysql -uroot -p -P3306 -h127.0.0.1 | ||
+ | mysql>flush tables; | ||
+ | mysql>select host,user,password from mysql.user where user='test1'; | ||
+ | mysql>flush privileges; | ||
</code> | </code> | ||
+ | ===驗證登入 test1=== | ||
+ | <code>mysql -utest1 -ptest1234 -h127.0.0.1 -P3306</code> | ||
===資料參考=== | ===資料參考=== | ||
- [[http://www.percona.com/blog/2014/12/10/recover-mysql-root-password-without-restarting-mysql-no-downtime/|Recover MySQL root password without restarting MySQL]] | - [[http://www.percona.com/blog/2014/12/10/recover-mysql-root-password-without-restarting-mysql-no-downtime/|Recover MySQL root password without restarting MySQL]] | ||
行 118: | 行 215: | ||
- [[http://ourmysql.com/archives/1014|mysql權限體系]] | - [[http://ourmysql.com/archives/1014|mysql權限體系]] | ||
- [[http://www.5ienet.com/note/html/mysql_priv/index.shtml|權限體系入門]] | - [[http://www.5ienet.com/note/html/mysql_priv/index.shtml|權限體系入門]] | ||
+ | - [[http://dettori.pixnet.net/blog/post/5258187|MYSQL教程:MySQL用戶帳號管理]] |