MySQL database root permission is missing (password lost)

Today colleagues, adjust MySQL security, delete the user accidentally deleted [email protected], and later added, but in the login found database many libraries are not, immediately confused forcing, consider recovering data, later I saw, think of the same year there have been similar situation, Mainly because the root user does not have permissions, so skip the authorization table, after the query permission, really missing, so it is changed from N to Y, in the second Test, OK, the following is the processing steps.

First step, skip the authorization form to log in to MySQL

1. Pkill Stop 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 normal start login can mysql-uroot-poldboy123

Second step, 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) Update all fields in the Mysql.user table to be 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 ';

This article is from the "funny Brother Notes" blog, be sure to keep this source http://qiuyt.blog.51cto.com/1229789/1920781

MySQL database root permission is missing (password lost)