Shulou(Shulou.com)06/01 Report--

Today, my colleague, adjusting mysql security, mistakenly deleted root@localhost when deleting users, and then added it, but when I logged in, I found that many databases in the database were gone, so I was confused and considered restoring data. Later, I saw and remembered that a similar situation occurred in that year, mainly because root users did not have permissions, so they skipped the authorization table. After querying the permissions, they were really lacking, so they were all changed from N to Y in the test. OK, here are the processing steps.

The first step is to skip the authorization table and log into mysql

1. Pkill stops instance process 2, mysqld_safe-- defaults-file=/data/3306/my.cnf-- skip-grant-tables & 3, mysql-S / data/3306/mysql.sock4, UPDATE mysql.user SET password=PASSWORD ('oldboy123') WHERE user='root' and host='localhost';5, flush privileges;6, kill mysqld_safe process 7, / etc/init.d/msyql start start login normally to mysql-uroot-poldboy123

Second, modify permissions (you can also initialize the root password)

Mysql > use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > 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) all the fields in the updated MYSQL.USER table are Y for N. 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'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.