mysql doesn't ask for root password when installing

Question

I used apt install mysql-server to install MySQL on Ubuntu 16.04 but during the installation, it did not ask for root password.

After installation I got ERROR 1045 when I tried to login as root and mysql_secure_installation threw the same error. I purged and autoremoved then reinstalled but it does not work.

How could i fix this? Can I set the root password if I didn't set it during installation?

This is my installation log:

The following additional packages will be installed:
  libaio1 mysql-client-5.7 mysql-client-core-5.7 mysql-server-5.7
  mysql-server-core-5.7
Suggested packages:
  mailx tinyca
The following NEW packages will be installed:
  libaio1 mysql-client-5.7 mysql-client-core-5.7 mysql-server mysql-server-5.7
  mysql-server-core-5.7
0 upgraded, 6 newly installed, 0 to remove and 0 not upgraded.
Need to get 0 B/17,9 MB of archives.
After this operation, 160 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Preconfiguring packages ...
Selecting previously unselected package libaio1:amd64.
(Reading database ... 227144 files and directories currently installed.)
Preparing to unpack .../libaio1_0.3.110-2_amd64.deb ...
Unpacking libaio1:amd64 (0.3.110-2) ...
Selecting previously unselected package mysql-client-core-5.7.
Preparing to unpack .../mysql-client-core-5.7_5.7.12-0ubuntu1_amd64.deb ...
Unpacking mysql-client-core-5.7 (5.7.12-0ubuntu1) ...
Selecting previously unselected package mysql-client-5.7.
Preparing to unpack .../mysql-client-5.7_5.7.12-0ubuntu1_amd64.deb ...
Unpacking mysql-client-5.7 (5.7.12-0ubuntu1) ...
Selecting previously unselected package mysql-server-core-5.7.
Preparing to unpack .../mysql-server-core-5.7_5.7.12-0ubuntu1_amd64.deb ...
Unpacking mysql-server-core-5.7 (5.7.12-0ubuntu1) ...
Selecting previously unselected package mysql-server-5.7.
Preparing to unpack .../mysql-server-5.7_5.7.12-0ubuntu1_amd64.deb ...
Unpacking mysql-server-5.7 (5.7.12-0ubuntu1) ...
Selecting previously unselected package mysql-server.
Preparing to unpack .../mysql-server_5.7.12-0ubuntu1_all.deb ...
Unpacking mysql-server (5.7.12-0ubuntu1) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for man-db (2.7.5-1) ...
Processing triggers for systemd (229-4ubuntu4) ...
Processing triggers for ureadahead (0.100.0-19) ...
Setting up libaio1:amd64 (0.3.110-2) ...
Setting up mysql-client-core-5.7 (5.7.12-0ubuntu1) ...
Setting up mysql-client-5.7 (5.7.12-0ubuntu1) ...
Setting up mysql-server-core-5.7 (5.7.12-0ubuntu1) ...
Setting up mysql-server-5.7 (5.7.12-0ubuntu1) ...
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Checking if update is needed.
This installation of MySQL is already upgraded to 5.7.12, use --force if you still need to run mysql_upgrade
Setting up mysql-server (5.7.12-0ubuntu1) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...

L. D. James · Accepted Answer · 2017-03-13T14:07:32.747

70

You can recover or set root password without knowing the current one by starting mysql without loading the grant-tables.

Please note the $ in the commands. This is specifying the terminal prompt you see when typing in the command. It's showing it's a line of text, but and actual typed terminal command. The "mysql>" prefix is also a prompt. That is the prompt you get when running mysql interactivately.

This is the cli (command line) to do this:
(Be sure to stop the current server before performing the steps. Only one server can run at a time.)

$ sudo mkdir /var/run/mysqld; sudo chown mysql /var/run/mysqld
$ sudo mysqld_safe --skip-grant-tables&

Now you can log in as root without a password and perform all commands, as in this case, set the root password as root.

$ sudo mysql --user=root mysql

This is the set root password that you will perform inside mysql if you have MySQL 5.6 or below:

mysql> update user set Password=PASSWORD('new-password') where user='root';
flush privileges;

In MySQL 5.7 or above

mysql> update user set authentication_string=PASSWORD('new-password') where user='root';
flush privileges;

From there, quit (kill the running msqld) mysql and start it as normal.

Notes on starting and stopping the mysql service:

Stop mysql:

$ sudo service mysql stop

Start mysql (normal):

$ sudo service mysql start

Kill the temporary mysql safe mode session:

$ sudo mysqladmin shutdown

edited Mar 13 '17 at 14:07

answered May 03 '16 at 14:27

L. D. James

25,036

11

in mysql 5.7.12 there is no column named password in user table. Hence following command worked for me. update user set authentication_string=PASSWORD('new-password') where user='root'; – The Code Jun 20 '16 at 19:05
9

I'm still getting ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) – llamerr Jul 18 '16 at 16:51
@ErelSegal-Halevi There are two things you should do. Run $ mysql_upgrade -u root -p. Also try the command above given by RP Singh . – L. D. James Aug 04 '16 at 13:16
I am also still getting this error. – Jamie Hutber Jan 02 '19 at 13:59
SET PASSWORD FOR 'root' = PASSWORD('your-password') if you are using mariadb – user1036719 Aug 08 '19 at 03:14
5

before executing FLUSH PRIVILEGES;, execute one more query: UPDATE user SET plugin='mysql_native_password'; so that the MySQL server uses its native password plugin to authenticate. – Aryo Mar 04 '20 at 00:11
Thanks much @Aryo. I was only able to login after UPDATE user SET plugin='mysql_native_password' – Arun Apr 24 '20 at 14:12
Newer versions of MySQL do not support the PASSWORD function. – Flimm Jan 04 '21 at 09:33
1

For Mysql 8 -> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; – makkasi Mar 08 '21 at 17:05
@makkasi ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement – scrollout Jun 22 '23 at 22:28

d a i s y · Answer 2 · 2018-12-06T03:41:42.453

48

It will not ask for the password while installing mysql in Ubuntu 16.04 but you can set it after successful installation in following way:

After completion of mysql installation, run command:

sudo mysql_secure_installation

It will show:

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here.

Enter current password for root (enter for none): (here press Enter)

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.
Set root password? [Y/n] y   (press 'y' to set new password)
New password: 
Re-enter new password:
Password updated successfully! Reloading privilege tables.. ... Success!

For Ubuntu 18.04 OR mysql-server version 5.7.22, THIS METHOD WILL NOT WORK

To set root password in Ubuntu 18.04, First follow the first three commands or first two steps of L.D. James's answer then run,

mysql> alter user 'root'@'localhost' identified by '<password>';

Password for root user is set!

OR

Follow these steps to set root password in 18.04:

As there is no password set for root user, simply login with blank password

sudo mysql -u root -p
Enter password: (press enter as no password is set)

after then can easily run query

ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';

edited Dec 06 '18 at 03:41

answered May 04 '16 at 04:55

d a i s y

5,511

4

The problem in the case of this question is that the user can't get to the prompts you're mentioned because he doesn't know the current password for root. – L. D. James May 04 '16 at 05:12
@L.D.James yes,you're right. I don't remember about ERROR : 1405 when i run mysql_secure_installation but sudo mysql_secure_installation worked for me. mysql installation doesn't ask for password. It happened with me the same. and here press enter means press enter without password. – d a i s y May 04 '16 at 05:35
2

As concers ubuntu 18.04 or debian 9+ it won't connect with blank password and even after resetting pass for root, it still refuses to connect – chefarov Oct 11 '18 at 18:04
@chefarov Facing the same issue, intensely frustrating – RedactedProfile Dec 06 '18 at 01:52
Indeed, I still get the same error – Jamie Hutber Jan 02 '19 at 14:01
It works, problem solved here with mysql-server v15.1 10.1.38-MariaDB – Murilo Melo Aug 05 '19 at 12:16
... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters. – scrollout Jun 22 '23 at 22:33

DimiDak · Answer 3 · 2021-08-21T12:50:09.427

26

After the installation just:

sudo mysql (yes, no user no pass)
You are now in the mysql console. Execute:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my_new_pass';
FLUSH PRIVILEGES;

That's it. You can now enter the console next time as usual:

mysql -u root -pmy_new_pass

edited Aug 21 '21 at 12:50

answered Apr 12 '19 at 15:38

DimiDak

631

5

I tried many other answers, but this one worked on Amazon EC2 – Haris ur Rehman Apr 21 '19 at 07:39
3

This was the correct answer for WSL Ubuntu (Linux on Windows). – Fer García May 14 '19 at 20:20
3

This is the only answer which worked for me on ubuntu 18.04 new installation on google cloud. – Aakash Jun 17 '19 at 06:19
2

Worked for me on Ubuntu 19.04. I did run mysql_secure_myinstallation, but it seemed that the root password was not set. – Jens Wagemaker Aug 20 '19 at 11:24
3

I tried dozens of solutions. This was the easiest and the only one that worked. – mreff555 Oct 10 '19 at 01:25
On your system, access to the MySQL root user is disabled through password, because password access on root simplifies brute forcing. So instead of enabling password access to root, I would rather recommend to create a new user with another username that is not easy to guess and grant all privileges on it so you can use it like root. – Christopher K. Feb 02 '20 at 15:26
1

I tried all answers, not solve my solution, but this one. Using ubuntu 20.04 LTS and mysql 8.0 – Ilya Trianggela Apr 10 '21 at 12:46
1

Finally!! It works! – scrollout Jun 22 '23 at 22:48

score 24 · Answer 4 · answered Nov 12 '16 at 09:57

24

Apparently the mysql-server installation on 16.04 (or any 5.7 installation?) allows root access not through password, but through the auth_socket plugin. Running sudo mysql -u root (n.b. w/o a password) will give you mysql console whereas running the command as non-root prompts you for a password.

It would seem that changing the password doesn't make much of a difference since the auth backend doesn't even check for a password. There is a very comprehensive article on how to change the plugin and switch to password authentication.

answered Nov 12 '16 at 09:57

carp

357

4

I was able to confirm with a fresh installation of apt install mysql-server without specifying a password on 18.04. The symptom and solution is exactly as described. – EricC Jun 18 '18 at 02:45
so how the hell do i connect? – Jamie Hutber Jan 02 '19 at 14:02
@JamieHutber change the authentication plugin to use by executing: UPDATE user SET plugin='mysql_native_password'; just before the FLUSH PRIVILEGES; so that it uses its native password plugin to authenticate. – Aryo Mar 04 '20 at 00:14
confirmed on Ubuntu 20.04.3 LTS – lineage Dec 29 '21 at 11:05

score 9 · Answer 5 · answered Jan 16 '19 at 03:39

there is a default user account.

$ vim /etc/mysql/debian.cnf

you will get this account from it like this:

user     = debian-sys-maint
password = nUZTARYslBsASzpw

you login with this account
```
$ mysql -u debian-sys-maint -p
```

edit you root password

mysql> show databases；

mysql> use mysql;

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

mysql> update user set plugin="mysql_native_password";

mysql> flush privileges;

mysql> quit;

restart mysql
```
$ /etc/init.d/mysql restart;
```

then you can mysql -u root -p with you previous setting.

I tried every other solution, and this is the only one that worked for me. (Linux Mint 19.2). — ElectroBuddha, Oct 26 '19 at 10:45

score 0 · Answer 6 · answered Sep 15 '17 at 08:03

Adding to the main answer, If you are stuck at

"mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql"

1 thing to remember that you have to use another terminal window to enter the following command.

$ sudo mysql --user=root mysql

If you try to do it in the same window, it will shut down the mysqld safemode process.

score -1 · Answer 7 · answered Jul 27 '19 at 01:45

Here's how to setup mysql-server with a terminal password prompt in Ubuntu 18.10.

Download The MySQL APT Repository via https://dev.mysql.com/downloads/repo/apt/
cd into your directory where you downloaded and run dpkg -i mysql-apt-config_0.8.13-1_all.deb (check your version number)
From the prompt menu select the last option "ok" and then "ok" again.
Update and install mysql-server and mysql-client:

sudo apt update
sudo apt install mysql-server mysql-client

Type in your root password in the promt.
Select "Use Legacy Authentication Method" if your running frameworks/applications (such as laravel) which do not support the new "Strong Password Encryption" yet, otherwise if your framework/application supports "Strong Password Encryption" then select it.
Login as root and check if it works.

mysql doesn't ask for root password when installing

7 Answers7

OR

Linked

Related