Change root password for mysql

Adjusting User Authentication and Privileges in MySql

Before you try to get into your mysql server, you need to change the password of root user.

sudo mysql

Then run this command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

The output will be something like this:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

You can see that the root user does in fact authenticate using the auth_socket plugin, to change the authentication to password for the root user:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Change the 'password' for yours. After you run the command the output will be something like this:

Query OK, 0 rows affected (0.02 sec)

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:

FLUSH PRIVILEGES;

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

SELECT user,authentication_string,plugin,host FROM mysql.user;

Now the output is similar to this:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

Now you can exit of mysql shell

exit;

create a new user

mysql -u root -p

From there, create a new user and give it a strong password:

CREATE USER 'newUser'@'localhost' IDENTIFIED BY 'password';

Then, grant your new user the appropriate privileges. For example, you could grant the user privileges to all tables within the database, as well as the power to add, change, and remove user privileges, with this command:

GRANT ALL PRIVILEGES ON *.* TO 'newUser'@'localhost' WITH GRANT OPTION;

Following this, exit the MySQL shell:

exit;

Finally, test the MySQL configuration.

----------

----------

-- Response ended

-- Page fetched on Tue May 21 13:49:02 2024