這裏顯示兩個版本的差異處。
database:mysql:account [2015/04/26 18:27] ali88 [以不用重新啟動MySQL方式,來恢復MySQL密碼] |
database:mysql:account [2015/05/10 11:31] |
||
---|---|---|---|
行 1: | 行 1: | ||
- | 預設為Linux平台下運行 | ||
- | <code>/etc/init.d/mysqld start *起動MySql伺服器</code> | ||
- | ======Mysql登入 存取權限流程圖====== | ||
- | {{:database:mysql:mysql_權限.jpg?|}} | ||
- | 資料來源:Mandrake9.2 Linux玩家寶典一書-吳佳彥工作室 | ||
- | ======帳號登入====== | ||
- | <code sql 1> | ||
- | mysql -u root -p db_name | ||
- | </code> | ||
- | ======修改密碼====== | ||
- | <code sql 1> | ||
- | root帳號登入 | ||
- | mysql>set password for root@localhost=PASSWORD('密碼'); | ||
- | </code> | ||
- | ======新增帳號用戶帳號====== | ||
- | 假定建立使用者:ali | ||
- | 密碼為:ali1234 | ||
- | 權限為:select,update,delete,drop,insert,Create | ||
- | 僅能訪問的資料庫:mysql | ||
- | * 直接操作MySQL授權資料表 方法一 | ||
- | <code sql 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; | ||
- | </code> | ||
- | * 使用GRANT語句 方法二 | ||
- | Grant 權限 on 資料庫.資料表 to 使用者@host identified by '密碼'; | ||
- | <code sql 1> | ||
- | mysql> grant select,update,delete,drop,insert,create | ||
- | -> on mysql.* | ||
- | -> to ali@localhost IDENTIFIED by 'ali1234'; | ||
- | </code> | ||
- | * 使用GRANT語句 方法三 | ||
- | |||
- | 利用Grant 給予全部權限 | ||
- | <code sql 1> | ||
- | mysql>GRANT ALL PRIVILEGES ON *.* TO '使用者'@'主機位置或IP' IDENTIFIED BY '密碼' WITH GRANT OPTION; | ||
- | </code> | ||
- | ======刪帳號====== | ||
- | 語法: | ||
- | <code> | ||
- | DROP USER user [, user] ... | ||
- | </code> | ||
- | |||
- | <code sql 1> | ||
- | drop user ali@localhost; | ||
- | </code> | ||
- | |||
- | ======忘記root密碼時該如何處理====== | ||
- | 作法原理:如作業系統登入安全模式一般,再去修改root的密碼 | ||
- | <code> | ||
- | 停止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 | ||
- | |||
- | </code> | ||
- | |||
- | 不用密碼即可登入mysql command line,去修改root密碼 | ||
- | <code sql 1> | ||
- | [ali@andy ~]#mysql -u root | ||
- | mysql>use mysql; | ||
- | mysql>update user set password=PASSWORD('密碼') where user='root'; | ||
- | mysql>Flush Privileges; | ||
- | </code> | ||
- | |||
- | ======以不用重新啟動MySQL方式,來恢復MySQL密碼====== | ||
- | 上個小節找回MySQL root密碼 需要重新啟動 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。 | ||
- | <code># 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 &</code> | ||
- | ===登入此實例=== | ||
- | <code># mysql -uroot -h127.0.0.1 -P3307 -p | ||
- | mysql2></code> | ||
- | |||
- | ===複製 實例 3306 /var/lib/mysql/mysql/user.*=== | ||
- | <code> | ||
- | # 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) | ||
- | </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> | ||
- | ===驗證登入 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://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|權限體系入門]] |