TutorialsLinux

How To Reset MySQL Password in Ubuntu Server

I already wrote about how to install and configure mysql in Ubuntu server. That time, I'm using Ubuntu 14.04 LTS trusty. It's slightly different now with 18.04 LTS bionic. Here several things that I done for mysql installation in Ubuntu 18.04 server. I have installed mysql server with command:

$ sudo apt-get install mysql-server

To check the version of mysql:

$ mysql -V
mysql  Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using  EditLine wrapper

Edit mysql configuration

To allow connection from remote host, edit /etc/mysql/mysql.conf.d/mysqld.cnf and comment out bind-address = 127.0.0.1

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address           = 127.0.0.1

Change mysql root password?

I need to login as root to setup users and databases. But, I am unable to log in because I don't have the password. So here the solution:

debian-sys-maint is one user that is installed by default on the mysql-server packages installed from the Ubuntu repositories. This user together with the credentials in /etc/mysql/debian.cnf used for:

  • Used by the init scripts to stop the server (shutdown) and/or reload.
  • Select (and update) on table mysql.user , which we will do it later

To get the password for this user:

$ sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = h21c23khpb9yFwjU
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = h21c23khpb9yFwjU
socket   = /var/run/mysqld/mysqld.sock

Use the password to login, connect to mysql database:

$ mysql -u debian-sys-maint -p mysql
Enter password:

In MySQL monitor, select from user table:

mysql> SELECT User, Host, plugin, password_last_changed, account_locked FROM user;
+------------------+-----------+-----------------------+-----------------------+----------------+
| User             | Host      | plugin                | password_last_changed | account_locked |
+------------------+-----------+-----------------------+-----------------------+----------------+
| root             | localhost | auth_socket           | 2020-01-02 15:57:57   | N              |
| mysql.session    | localhost | mysql_native_password | 2020-01-02 15:57:58   | Y              |
| mysql.sys        | localhost | mysql_native_password | 2020-01-02 15:57:58   | Y              |
| debian-sys-maint | localhost | mysql_native_password | 2020-01-02 15:57:59   | N              |
+------------------+-----------+-----------------------+-----------------------+----------------+
4 rows in set (0.00 sec)

By default root user is authenticated using auth_socket. We need to change the plugin to mysql_native_password, so we can use mysql password.

mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> COMMIT;

Then, change root password:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'N3wP@ssw0rd';

You maybe need to restart your mysql server.

Host Is Not Allowed to Connect to This MySQL Server

When trying to connect remotely to MySQL server, you may encounter this error: Host 'xxx' is not allowed to connect to this MySQL server.

It's a security precaution, we can try to add a new administrator account:

mysql> CREATE USER 'sqlrem'@'%' IDENTIFIED BY 'Tru$tM3';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sqlrem'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Please be careful that grant all privileges means granting above user administrative privileges, and it's recommended to not having a user with this kind of access open to any IP (%). In my case, I need this user to manage my SQLyog connection for development purpose.

References:

Published 4 February 2020

Desson Ariawan

Desson AriawanProgrammer

tags