Changing MySQL password in Linux

I’ll teach you how to change MySQL user password in this tutorial. For Ubuntu 18.04 I did it, but it will work for every modern Linux distribution. Depending on the MySQL or MariaDB server version you are running on your system, you will need to use different commands to change the user password.

You can find your database server version by issuing the following command:

jesse@library:~$ mysql --version

In my installation, I use MariaDB so the following results have been provided by my terminal

mysql Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

There is a slight change in the command syntax for MySQL older versions and newer MySQL and MariaDB.

Login to the MySQL shell as root

The first step is to stop the MySQL service on your Linux machine:

jesse@library:/$ sudo /etc/init.d/mysql stop

Now again restart the service with the following command

jesse@library:/$ sudo mysqld_safe --skip-grant-tables &

This command starts the MySQL service in safe mode without checking the table enforcing and checking the table privileges. Hence it lets you log in to the MySQL shell without any password. from where you can access the MySQL shell with elevated privileges by typing the following command and enter your MySQL root user password when prompted:

jesse@library:/$ mysql -u root

Once you step into the DB Shell, change the database to MySQL with the following command.

MariaDB [(none)]> use mysql;

Remember that from MySQL 5.7.6, only a password is stored in the user table with a string authentication column. Furthermore, the login column has been dropped. So, in the authentication_string field, we need to change the hash password manually. In my case, you can use your own password and user in the following order, but the remainder of the syntax remains the same.

MariaDB [mysql]> update user set authentication_string=PASSWORD("new_password") where user='root';

Make sure you change the root and new_password with the name of the user, you want to change the password to.

In both cases if all goes well, you will see the following output:

Query OK, 0 rows affected (0.00 sec)

Now, we must also look at the plugin authentication form, which is set to “MySQL native password” by default but if not, the user table is overrideable and modified using the command below.

MariaDB [mysql]> update user set plugin = "mysql_native_password" where user ="root";

After updating the password hash the ‘authentication_string’ and ‘mysql_native_password’ plugin for the ‘root’ user you can just flush privileges and restart the MySQL service.

MariaDB [mysql]> flush privileges;

The FLUSH PRIVILEGES statement is used to reload privileges from the grant table in the MySQL database.

[mysql]> exit;

Exit from the DB Shell, and restart the service and you will be able to log in with the updated user credentials, but additionally, I checked and updated the user to have database access with server IP in the host filed.

To see the host and user information issue following command

MariaDB [mysql]> SELECT User, Host, Password FROM mysql.user;

This selects the Username, Host and password column from the user table of MySQL database. In my case, since my root user had access from the localhost, my output was the following.

+--------------+-----------+-------------------------------------------+
| User | Host | Password |
+--------------+-----------+-------------------------------------------+
| root | localhost | *EC3BF2D93A44B009904C415F8539E3BA3846F322 |
+--------------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

If the user is connecting to the MySQL server from another host, change with the remote hostname or IP Address. To grant access privileges from a particular host, update the user table and grant privileges for your desired user, in my case I did this for root.

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.16' IDENTIFIED BY 'new_password';

You can search the user table with the same command that we used earlier once you have provided the privileges to the root user by remote address. And the revised details should be presented as follows.

MariaDB [mysql]> SELECT User, Host, Password FROM mysql.user;
+--------------+----------------+-------------------------------------------+
| User | Host | Password |
+--------------+----------------+-------------------------------------------+
| root | localhost | *EC3BF2D93A44B009904C415F8539E3BA3846F322 |
| root | 192.168.100.16 | *EC3BF2D93A44B009904C415F8539E3BA3846F322 |
+--------------+----------------+-------------------------------------------+
3 rows in set (0.00 sec)

After completing these steps, we may exit from the DB shell after flushing and reloading the privileges

MariaDB [mysql]> flush privileges;

type exit to quit the DB shell gracefully.

[mysql]> exit;

Try logging into MySQL server with modified credentials to verify the right implementation of the new password. Using the command below to do this. To determine your host, you can also use the -h switch.

mysql -u user-name -h localhost -p

Through default, the MySQL server is bound to the localhost and can not connect from another host or device. You should modify the configuration file of Mysql or MariaDB in the Linux /etc folder for this. For me, the following was a MariaDB.conf file and I accessed it through nano with elevated privileges.

jesse@library:/ $ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

In the configuration file locate the ‘bind-address’ line it should be in [mysqld] section of the configuration file.

bind-address = 127.0.0.1

• If MySQL binds to 127.0.0.1, then only software on the same computer will be able to connect (because 127.0.0.1 is always the local computer).
• If MySQL binds to 192.168.0.2 (and the server computer’s IP address is 192.168.0.2 and it’s on a /24 subnet), then any computers on the same subnet (anything that starts with 192.168.0) will be able to connect.
• If MySQL binds to 0.0.0.0, then connections are open on all interfaces and any computer which can reach the server computer over the network will be able to connect.

You can’t bind to different IP addresses, but then you can bind to all IP addresses. The physical network interface of the server can be specified by the IP address.

Restart Mysql Service to recognize the changes.

jesse@library:/ $ sudo systemctl restart mysql

Once I restarted the server, I was able to connect to the server through the root credentials on any other computer in my network.

However, I recommend that you remember the security aspects of ay host’s following root data access. It’s beneficial to use graphical Interface tools such as MySQL Workbench if you have not physically obtained access to the server or want to track and work on a database. The stable and viable alternative is otherwise to build an ssh connection for a database server.