您好,登錄后才能下訂單哦!
今天同事,調(diào)整mysql安全,在刪除用戶的時候誤刪了 root@localhost,后來加進去了,但是在登錄發(fā)現(xiàn)數(shù)據(jù)庫很多庫都沒有了,頓時懵逼,考慮恢復(fù)數(shù)據(jù),稍后我看到了,想起當(dāng)年也出現(xiàn)過類似情況,主要是因為root用戶沒有權(quán)限,所以跳過授權(quán)表,經(jīng)過查詢權(quán)限,真的缺少,所以將其都由N改為Y ,在次測試,OK,以下是處理步驟。
第一步,跳過授權(quán)表登錄mysql
1、pkill 停止 實例進程 2、mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables & 3、mysql -S /data/3306/mysql.sock 4、UPDATE mysql.user SET password=PASSWORD('oldboy123') WHERE user='root' and host='localhost'; 5、flush privileges; 6、kill掉mysqld_safe 進程 7、/etc/init.d/msyql start 正常啟動 登錄即可 mysql -uroot -poldboy123
第二步、修改權(quán)限(也可以初始化root密碼)
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | oldboy_gbk | | performance_schema | | qiuyuetao | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 24 rows in set (0.00 sec) 更新的MYSQL.USER表的所有字段中為N的為Y就可以了。 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'; 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';
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。