阿里BaBa電腦筆記

使用者工具

網站工具

足跡: • account
database:mysql:account

目錄表

預設為Linux平台下運行 

/etc/init.d/mysqld start  *起動MySql伺服器

Mysql登入 存取權限流程圖

資料來源:Mandrake9.2 Linux玩家寶典一書-吳佳彥工作室

帳號登入

1
mysql -u root -p db_name

修改密碼

1
root帳號登入
mysql>SET password FOR root@localhost=PASSWORD('密碼');

新增帳號用戶帳號

假定建立使用者：ali 

      密碼為：ali1234
      權限為：select,update,delete,drop,insert,Create
      僅能訪問的資料庫：mysql

* 直接操作MySQL授權資料表 方法一

1
mysql> INSERT INTO USER(host,USER,password)VALUES('localhost','ali',PASSWORD('ali1234'));
mysql> INSERT INTO db(host,db,USER,Select_priv,update_priv,delete_priv,drop_priv,insert_priv,create_priv)
-> VALUES('localhost','mysql','ali','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

* 使用GRANT語句 方法二

Grant 權限 on 資料庫.資料表 to 使用者＠host identified by '密碼';

1
mysql> GRANT SELECT,UPDATE,DELETE,DROP,INSERT,CREATE
-> ON mysql.*
-> TO ali@localhost IDENTIFIED BY 'ali1234';

* 使用GRANT語句 方法三

利用Grant 給予全部權限

1
mysql>GRANT ALL PRIVILEGES ON *.* TO '使用者'@'主機位置或IP' IDENTIFIED BY '密碼' WITH GRANT OPTION;

看權限 show grants

看目前此帳號登入的權限 

mysql> show grants;

看某個'使用者'@'主機位置或IP'的權限,通常只有MySQL管理者才給予查看的權利 

mysql >show grants for ali@'localhost';

撤銷權限 revoke

*revoke 只能撤銷權限,無法刪除帳號。 

mysql > revoke  SELECT,UPDATE,DELETE,DROP,INSERT,CREATE ON mysql.* from  ali@localhost;show grants for ali@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for ali@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ali'@'localhost' IDENTIFIED BY PASSWORD '*1CFD39EAD5BAE48B8040DAA7F969AFDEC465B414' |
+------------------------------------------------------------------------------------------------------------+

刪帳號

語法： 

DROP USER user [, user] ...
1
DROP USER ali@localhost;

Mysql登入 存取權限 範例

根據上圖 Mysql登入 存取權限流程,列出 user全域權限表;db權限表;tables_priv 權限表;columns權限表等範例。

  • mysql.user表
    mysql>  grant all privileges on *.* to ali@'localhost' identified by '1234'

    觀看權限表的變化 

    mysql > select * from mysql.user where user='ali' \G
  • mysql.db表
    mysql> show tables from world;
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+
    grant all privileges on world.* to ali22@'localhost' identified by 'qwedcxza';

    觀看權限表的變化 

    select * from mysql.user where user='ali22' \G
    select * from mysql.db where user='ali22' \G
  • mysql.table_priv表
    grant all privileges on world.Country to ali33@'localhost' identified by 'qwedcxza';

    觀看權限表的變化 

    select * from mysql.user where user='ali33' \G
    select * from mysql.db where user='ali33' \G
    select * from mysql.tables_priv where user='ali33' \G
  • mysql.columns_priv表

只給予更新權限 DB world/table City/欄位名稱columns Name,CountryCode 

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       |                |
+-------------+----------+------+-----+---------+----------------+
grant update(Name,CountryCode) on world.City to ali66@'localhost' identified by 'qwedcxza';

觀看權限表的變化 

mysql > select * from mysql.user where user='ali66' \G
mysql> select * from mysql.db where user='ali66' \G
mysql> select * from mysql.tables_priv where user='ali66' \G
mysql> select * from mysql.columns_priv where user='ali66' \G

忘記root密碼時該如何處理

作法原理：如作業系統登入安全模式一般，再去修改root的密碼 

停止mysql
[ali@andy ~]#/etc/init.d/mysqld stop
編輯mysql設定檔
[ali@andy ~]#vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

#加入下面這一段，表明直接跳過權限
skip_grant_tables

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

啟動mysql
[ali@andy ~]#/etc/init.d/mysqld start

不用密碼即可登入mysql command line，去修改root密碼

1
[ali@andy ~]#mysql -u root 
mysql>USE mysql;
mysql>UPDATE USER SET password=PASSWORD('密碼') WHERE USER='root';
mysql>FLUSH Privileges;

以不用重新啟動MySQL方式,來恢復MySQL密碼

上個小節找回MySQL root密碼 需要重新啟動 MySQL服務。假若正在上線的MySQL,要使用某一個帳號登入,此時忘記了密碼而無法登入,那如何在不需要重新啟動 MySQL 前提之下,來恢復密碼。

利用再創建一個MySQL實例方式,來恢復密碼。

需要恢復密碼的 實例 (3306)

假若有一個帳號 test1 ,忘記密碼。 

# 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

建立一個實例

此實例預設的儲存引擎用MyISAM。 

# mkdir /Mydata
# chown  mysql:mysql /Mydata/
# mysql_install_db --user=mysql --datadir=/Mydata/
# /usr/libexec/mysqld --print-defaults
# /usr/libexec/mysqld  --basedir=/usr --datadir=/Mydata/ --user=mysql --skip-innodb --default-storage-engine=myisam --socket=/Mydata/mysql2.sock --port=3307 --log-error=/Mydata/error2.log --pid-file=/Mydata/mysql2.pid &

登入此實例

# mysql -uroot -h127.0.0.1 -P3307 -p
mysql2>

複製 實例 3306 /var/lib/mysql/mysql/user.*

# cp -p /var/lib/mysql/mysql/user.* /Mydata/mysql/
cp：是否覆寫 ‘/Mydata/mysql/user.frm’? Y
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)

在 實例 3307改變密碼

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;

複製實例 3307 /Mydata/mysql/user.* 到 實例 3306

# 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;

驗證登入 test1

mysql -utest1 -ptest1234 -h127.0.0.1 -P3306

資料參考

  1. Recover MySQL root password without restarting MySQL

資料參考

  1. 杰哥數位教室
  2. MySql官方文件
  3. 查詢 MySQL 對 此帳號 開放(GRANT)哪些權限
  4. mysql權限體系
  5. 權限體系入門
  6. MYSQL教程:MySQL用戶帳號管理
database/mysql/account.txt · 上一次變更: 2015/05/10 11:31 (外部編輯)

頁面工具

若無特別註明，本 wiki 上的內容都是採用以下授權方式： CC Attribution-Share Alike 3.0 Unported
CC Attribution-Share Alike 3.0 Unported Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki