Introduction

This article will have the content of showing how to configure and set the ‘root’ password of a MySQL Database Server manual installation. Furthermore, it is the first time after the manual installation is done. This article is the continuation of the previous article in this link. That article has a title of ‘How to Install MySQL Database Server Manually in Linux Ubuntu 19.10 Operating System’. The following is the actual step to achieve that purpose :

1. After the server is actually running, try to access the MySQL Command Console with the following command execution :

mysql -uroot -p 

The above command by default will try and attempt to connect to port 3306 by default where MySQL Database Server is actually running using the ‘root’ account. Actually, it will trigger an authentication method as follows :

user@hostname:/opt/mysql/tested/mysql-5.7.28-linux-glibc2.12-x86_64$ mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
user@hostname:/opt/mysql/tested/mysql-5.7.28-linux-glibc2.12-x86_64$

The output of the above command execution appear if there is no action for supplying the password. It merely just press enter in the ‘Enter password’ prompt. It is actually the same situation as in the description in the article in this link. That is an article with the title of ‘How to Solve Error Message ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)’.

user@hostname:/opt/mysql/tested/mysql-5.7.28-linux-glibc2.12-x86_64$ mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
user@hostname:/opt/mysql/tested/mysql-5.7.28-linux-glibc2.12-x86_64$ 

And in another output of the above command execution, there is actually an action for supplying the password. But the password itself is just typing a random keyword. It wil also generate the same response. It will end in a failure just as the description exist in this link. Furthermore, that article is an article with the title of ‘How to Solve Error Message ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)’.

But either supplying the password or not is not the main issue. The important point is that any input will not have an effect since it is a first time usage. There must be a way to reset or to initiate the ‘root’ password.

Solution

The solution for the actual execution for setting or initiating the root password is actually exist in this link and also in this link. The following step will explain the sequence to achieve it :

1. Basically, just edit the ‘my.cnf’ or the main MySQL Database Server’s configuration file. By adding a new line of configuration which is the ‘skip-grant-tables’, it will automatically skip the authentication process. Just don’t forget to restart the service again after saving the edited file. The actual content of the file after the editing process will be exist as follows :

[mysql]
port=3377
socket=/tmp/mysqld57.sock
[mysqld]
skip-grant-tables
port=3377
socket=/tmp/mysqld57.sock
datadir=/opt/mysql/tested/mysql-5.7.28-linux-glibc2.12-x86_64/data

2. Next, the step is just for accessing MySQL Command Console without having to supply any password. After that change the password. Do it as follows :

root@hostname:/opt/mysql/tested/mysql-5.7.28-linux-glibc2.12-x86_64# mysql -uroot -P3377 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set authentication_string=password("password") where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> quit;
Bye
root@hostname:/opt/mysql/tested/mysql-5.7.28-linux-glibc2.12-x86_64# 

3. Continue on into the above step, in order to prove that the above intialization password is working, just remove the ‘skip-grant-tables’ line configuration as it already done in the first step. Don’t forget to restart the MySQL Database Server’s service. Try to access the MySQL Command Console with the password :

root@hostname:/opt/mysql/tested/mysql-5.7.28-linux-glibc2.12-x86_64# mysql -uroot -P3377 -h127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

But after successfully logging in to the MySQL Command Console, there is an error as follows upon executing this query below :

mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password=password('password');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql>

The explanation of the above command execution is actually exist in another article. It exist in the following link with the title of ‘How to Solve MySQL Error Message ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.’. Just execute the following query to be able to use MySQL normally by using all the queries available :

mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password=password('password');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye

As in the output above, after executing the query of ‘set password=password(‘xxxxxxx’)’, the execution for any available query will be possible. So, the initialization password for the ‘root’ account is a success.