Krdytkyu

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) ...

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

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

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>';

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.

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.