mysql數(shù)據(jù)庫ROOT賬號權(quán)限丟失問題解決方法

2024-07-24 12:39:42

字體：大中小

供稿：網(wǎng)友

ROOT賬號權(quán)限丟失了我們就幾乎不能做很多的事情了,今天本文章來為各位介紹mysql數(shù)據(jù)庫ROOT賬號權(quán)限丟失問題解決方法吧,希望文章可以幫助到各位.

數(shù)據(jù)庫的操作軟件有很多,在線的話phpmyadmin是非常不錯的軟件,比較坑爹的是我的一個機器用的是amh的面板,遇到了一件無比郁悶的事.

大家都知道在沒有獨立安裝phpmyadmin的情況下,amh面板的數(shù)據(jù)庫操作是AMYSQL…這個軟件,怎么說呢,操作不當(dāng),引起的后果可是灰常嚴重的,其他軟件操作不當(dāng)也會出大事.

由于想給網(wǎng)站提速,數(shù)據(jù)庫要遷移到阿里云RDS,數(shù)據(jù)庫的遷移當(dāng)然最簡單的方法是直接遷移,這里就需要我們開放源數(shù)據(jù)庫的遠程連接.

由于安全問題,我們默認的時候把數(shù)據(jù)庫的鏈接設(shè)置的都是本地localhost,那么我們需要改為%,OK 無比郁悶的是我在改為%之后，在amysql面板操作的,竟然直接導(dǎo)致我的root賬號沒有任何權(quán)限….直接等于賬號被鎖定,root沒有任何操作權(quán)限,尼瑪..

對于我這樣的計算機小白來說,這尼瑪讓我淚奔啊,剛開始不知道什么原因的時候我差點都要回滾磁盤了有木有….

沒辦法,只能自己先折騰了.

解決辦法:首先,你要有服務(wù)器的root權(quán)限,不然….那還搞什么啊..

這里介紹一個很使用的命令:skip-grant-tables →非常有用的mysql啟動參數(shù).

先停止mysql的運行,這里建議是正常停止mysql,盡量不要pkill,因為你的pkill可能導(dǎo)致mysqld dead but subsys locked.

如果已經(jīng)pkill了那就再mysqld status下嘍,然后再stop,然后運行# mysqld_safe –skip-grant-tables & &,表示在后臺運行,不再后臺運行的話,就再打開一個終端咯,這個&很實用哦~！

然后mysql進入我們的數(shù)據(jù)庫,跳過驗證了,也就不必那么麻煩,直接mysql進入,然后 use mysql；

我們這里呢介紹下實用update賦予root賬號權(quán)限.

update user set Update_priv =’Y’ where user = ‘root';

如果像我這樣沒有了所有權(quán)限,更新的MYSQL.USER表的所有字段中為Ｎ的為Ｙ就可以了.

update user set Select_priv ='Y' where user = 'root';

update user set Insert_priv ='Y' where user = 'root';

update user set Update_priv ='Y' where user = 'root';

update user set Delete_priv ='Y' where user = 'root';

update user set Create_priv ='Y' where user = 'root'; --Vevb.com

update user set Drop_priv ='Y' where user = 'root';

update user set Reload_priv ='Y' where user = 'root';

update user set Shutdown_priv ='Y' where user = 'root';

update user set Process_priv ='Y' where user = 'root';

update user set File_priv ='Y' where user = 'root';

update user set Grant_priv ='Y' where user = 'root';

update user set References_priv ='Y' where user = 'root';

update user set Index_priv ='Y' where user = 'root';

update user set Alter_priv ='Y' where user = 'root';

update user set Show_db_priv ='Y' where user = 'root';

update user set Super_priv ='Y' where user = 'root';

update user set Create_tmp_table_priv ='Y' where user = 'root';

update user set Lock_tables_priv ='Y' where user = 'root';

update user set Execute_priv ='Y' where user = 'root';

update user set Repl_slave_priv ='Y' where user = 'root';

update user set Repl_client_priv ='Y' where user = 'root';

update user set Create_view_priv ='Y' where user = 'root';

update user set Show_view_priv ='Y' where user = 'root';

update user set Create_routine_priv ='Y' where user = 'root';

update user set Alter_routine_priv ='Y' where user = 'root';

update user set Create_user_priv ='Y' where user = 'root';

update user set Event_priv ='Y' where user = 'root';

update user set Trigger_priv ='Y' where user = 'root';