Skip to main content

How to start MySQL with skip-grant-tables

James Wright
  • Updated

Introduction

 

If the 'root' user has lost access to MySQL due to removed grants, it may be necessary to start mysql with 'skip-grant-tables' in order to regain control of the root user.

 

**IMPORTANT WARNING**

These actions can be dangerous and these changes should be reverted as soon as access is restored. If you are not comfortable making these changes, we recommend reaching out to a system administrator.

**IMPORTANT WARNING**

 

Procedure

 

To help mitigate some of the risk, we will be starting MySQL without networking as well as customizing the socket location. We will start by turning off service monitoring for MySQL:

 

whmapi1 configureservice service=mysql enabled=1 monitored=0

 

Then stop the MySQL service:

 

/scripts/restartsrv_mysql --stop

 

Now we want to start MySQL with a customized socket location:

 

mkdir -pv /var/${your-random-folder-name} && chown -v mysql. /var/${your-random-folder-name} && chmod -v 0700 /var/${your-random-folder-name} && /usr/bin/mysqld_safe --skip-grant-tables --skip-networking --socket=/var/${your-random-folder-name}/mysql.sock &

 

Example:

 

mkdir -pv /var/fCVmf4sk5m6JK && chown -v mysql. /var/fCVmf4sk5m6JK && chmod -v 0700 /var/fCVmf4sk5m6JK && /usr/bin/mysqld_safe --skip-grant-tables --skip-networking --socket=/var/fCVmf4sk5m6JK/mysql.sock &

 

You should now see that MySQL is running in the process list:

 

ps fauxww | grep [m]ysql
mysql 15154 1.9 4.1 1829408 77476 ? Ssl 17:45 0:00 /usr/sbin/mysqld --skip-grant-tables --skip-networking --socket=/var/fCVmf4sk5m6JK/mysql.sock

 

Log into MySQL via CLI using the below command and make any necessary changes, to alter the password:

 

mysql -o mysql --socket=/var/${your-folder-name-here}/mysql.sock -e "UPDATE user SET Password=PASSWORD('replace_this_with_new_password') WHERE User='root';"

 

To update grants for the root user:

Enter the command prompt:

mysql -o mysql --socket=/var/fCVmf4sk5m6JK/mysql.sock


Update the privileges:

update user set Select_priv='Y', Insert_priv='Y', Update_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', Alter_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', Event_priv='Y', Trigger_priv='Y' where User='root';

 

Flush privileges:

FLUSH PRIVILEGES;

 

Exit MySQL command line:

 

quit

 

**IMPORTANT**

 

Once the necessary changes have been made, we must revert our changes. Initiate a standard MySQL shutdown by specifying your customized socket file:

 

mysqladmin --socket=/var/${your-file-here}/mysql.sock shutdown

 

Example:

 

mysqladmin --socket=/var/fCVmf4sk5m6JK/mysql.sock shutdown

 

Re-enable the MySQL service monitoring:

 

whmapi1 configureservice service=mysql enabled=1 monitored=1

 

Update the root user's password in - /root/.my.cnf - if necessary.

 

Ensure MySQL is started:

 

/scripts/restartsrv_mysql --start

 

Confirm that MySQL once more requires passwords for users to connect by using the following, replacing '$cPuser' with a cPanel user present on your system:

 

[root@live-transfer-test ~]# mysql -u $cPuser
ERROR 1045 (28000): Access denied for user '$cPuser'@'localhost' (using password: YES)

 

Ensure MySQL is up and responding to queries:

 

mysqladmin stat proc --verbose

 

**IMPORTANT**

 

 

 

 

 

 

 

Related articles