預設為Linux平台下運行 /etc/init.d/mysqld start *起動MySql伺服器 ======Mysql登入 存取權限流程圖====== {{:database:mysql:mysql_權限.jpg?|}} 資料來源:Mandrake9.2 Linux玩家寶典一書-吳佳彥工作室 ======帳號登入====== mysql -u root -p db_name ======修改密碼====== root帳號登入 mysql>set password for root@localhost=PASSWORD('密碼'); ======新增帳號用戶帳號====== 假定建立使用者：ali 密碼為：ali1234 權限為：select,update,delete,drop,insert,Create 僅能訪問的資料庫：mysql * 直接操作MySQL授權資料表 方法一 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 '密碼'; mysql> grant select,update,delete,drop,insert,create -> on mysql.* -> to ali@localhost IDENTIFIED by 'ali1234'; * 使用GRANT語句 方法三 利用Grant 給予全部權限 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] ... 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' \Gselect * 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' \Gselect * from mysql.db where user='ali33' \Gselect * 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' \Gmysql> select * from mysql.db where user='ali66' \Gmysql> select * from mysql.tables_priv where user='ali66' \Gmysql> 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密碼 [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 ===資料參考=== - [[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://sjchen.im.nuu.edu.tw/Database/98/MySQL_Ch2_02/MySQL_Ch2_02.html|杰哥數位教室]] - [[http://twpug.net/docs/mysql-5.1/|MySql官方文件]] - [[http://blog.longwin.com.tw/2009/06/query-mysql-show-grant-permission-2009/|查詢 MySQL 對 此帳號 開放(GRANT)哪些權限]] - [[http://ourmysql.com/archives/1014|mysql權限體系]] - [[http://www.5ienet.com/note/html/mysql_priv/index.shtml|權限體系入門]] - [[http://dettori.pixnet.net/blog/post/5258187|MYSQL教程:MySQL用戶帳號管理]]