Sometimes you often forget the root password or often get an error like this:

or this one: ERROR 1130 (HY000): Host ” is not allowed to connect to this MySQL server [duplicate]

Here’s how I set up the command to do it successfully:

  1. Stop DB:
sudo systemctl stop mysql

2. Configuring MySQL to Start Without Grant Tables:

sudo systemctl edit mysql

3. These change the default service parameters for MySQL:

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking

1st row: ExecStart statement clears the default value.

2nd row: to disable loading the grant tables and networking capabilities.

4. Reload the systemd configuration to apply these changes:

sudo systemctl daemon-reload
sudo systemctl start mysql

5. Changing the Root Password:

5.1 Connect to the database as the root user:

sudo mysql -u root

5.2 Reload the grant tables by issuing

FLUSH PRIVILEGES;
UPDATE mysql.user SET authentication_string = PASSWORD('s3c23t@!126') WHERE user = 'root';
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root';

6. Reverting Your Database Server to Normal Settings

sudo systemctl revert mysql

Output:

7. reload the systemd configuration to apply the changes:

sudo systemctl daemon-reload
sudo systemctl restart mysql

8. Check:

mysql -u root -p

9. Done.

How To Reset Your MySQL Root Password on Ubuntu 18.04
Tagged on:         

Leave a Reply

Your email address will not be published. Required fields are marked *