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:

There is one user, debian-sys-maint 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 are used by the init scripts to stop the server as they would require knowledge of the mysql root users password else. 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 '[email protected]';

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.