Category: Databases

If your mysql root user has no grant option on Ubuntu, here’s the procedure to fix this:

First you will have to start mysql without using the grant tables

sudo systemctl edit mysql

This will open an editor, add the following lines and save/quit the editor

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --skip-grant-tables

Issue the following commands:

sudo systemctl daemon-reload 
sudo systemctl start mysql

You can now connect to mysql using just the following command (without password)

mysql

Now change the user settings as follows:

mysql> UPDATE mysql.user SET Grant_priv = 'Y', Super_priv = 'Y' WHERE User = 'root';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.18 sec)

Now issue the first commands again (uptil the systemctl start mysql command), but remove the lines you’ve added to the mysql config.