mysql accidentally delete root user or no root user – linux

Sometimes installing Mysql in Linux will result in no root user, or the root account will be deleted from the mysql.user table, which will result in many permissions being uncontrollable.

Solution Re-create the root user and grant all permissions as follows:
1. Modify the /etc/my.conf file and add skip-grant-tables

[mysqld]

Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0

Skip-grant-tables

[mysqld_safe]

Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid

2, restart the mysql service

/etc/init.d/mysqld restart

3, use the following command to login mysql, no password

[[email protected]]# mysql

4, after logging in, re-add root user

Mysql> use mysql;
Mysql> insert into user set user=’root’,ssl_cipher=”,x509_issuer=”,x509_subject=”;
Mysql> update user set Host=’localhost’, select_priv=’y’,

Mysql>Alter_priv=’y’,delete_priv=’y’,create_priv=’y’,drop_priv=’y’,reload_priv=’y’,shutdown_priv=’y’,Process_priv=’y’,file_priv=’y’, Grant_priv=’y’,References_priv=’y’,index_priv=’y’,create_user_priv=’y’,show_db_priv=’y’,super_priv=’y’,create_tmp_table_priv=’y’,Lock_tables_priv=’y’,execute_priv= ‘y’,repl_slave_priv=’y’,repl_client_priv=’y’,create_view_priv=’y’,show_view_priv=’y’,create_routine_priv=’y’,alter_routine_priv=’y’,create_user_priv=’y’ where user=’root ‘;

Mysql> quit;

5. Log in again using the following command, set the password for root

Mysql> mysql -u root
Mysql> update mysql.user set password=password(‘yourrootpasswd’) where user=’root’;
Mysql> update user set host = ‘%’ where user = ‘root’;

//% means setting up a remote machine to access mysql.
Mysql> flush privileges;
Mysql> quit;

6. Modify the /etc/my.conf file, remove the skip-grant-tables, and restart mysql.

[[email protected]]# /etc/init.d/mysqld restart

7. Normal, everything is OK

Leave a Reply