Problem with mysql root password

I am on Ubuntu 16.04 LTS. What is my mysql root password? If I enter

mysql -u root -p

It asks me to enter a password: I don't know it, and no password won't work either. Mysql was pre-installed, so I didn't choose any root password. If I try to install it again, with

sudo apt-get install mysql-server

It says that mysql-server is already the newest version (5.7.18-0ubuntu0.16.04.1)

Any help, hints? Thanks.

1

4 Answers

For the new my-sql 5.7 if password is left empty then it uses auth_plugin.

In that case login with sudo privilege.

$ sudo mysql -u root -p

After login you can simply use this to update password.

$ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';

For complete refrence check here.

EDIT: One can also use this (without -u root):

$ sudo mysql
$ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';

1

Two options:

First is purging mysql(only if you don't already have a database):

Use

apt-get purge mysql-server

to purge and then

apt-get install mysql-server

to simply reinstall it. While installing it should ask you for a password. This is the easiest and fastest way, however it could come to problems if some programs like phpmyadmin already connected to the existing MySQL so I'd suggest you to use the

Or do the following: Stop the MySQL Server using

sudo service mysql stop 

Then start the safe mode with

sudo /usr/bin/mysqld_safe --skip-grant-tables & 

Select localhost as host(After the last command there won't be a command prompt, so after the message Starting mysqld daemon with databases from [...] type in the next command)

mysql -h localhost

select mysql with

USE mysql 

(Replace YOURNEWPASSWORD with your new password!) Type

UPDATE mysql.user
SET authentication_string=PASSWORD('new_password')
WHERE user='root' AND host='localhost'; 

Exit with

quit 

And restart MySQL with

sudo mysqladmin shutdown
sudo service mysql start

Edit

That error has something to do with your MySQL socket. First you want to find all socket files on your system with:

sudo find / -type s

Your socket should be something similar to this

/var/lib/mysql/mysql.sock

(important is the /mysql.sock part)

Once you find where the socket is being opened, add or edit the line to your

/etc/my.cnf 

file with the path to the socket file:

put

[mysqld]

at the very beginning and

socket=/path/to/your/socket/

at the very end of the file.

Try the 2. option again now. If it doesn't work continue with this explanation.

If this doesn't solve your problem something overrides the my.cnf location, and that would result in a socket not being found where the my.cnf file indicates it should be. Then when you try to run the mysql command line client, it will read my.cnf to find the socket, but it will not find it since it deviates from where the server created one. So, Unless you care where the socket resides, just changing the my.cnf to match should work.

To fix this start with shutting down the mysqld process

pkill -9 mysqld

After you do this you might want to look for a pid file in /var/run/mysqld/ and delete it

Make sure the permissions on your socket is such that whatever user mysqld is running as can read/write to it. An easy test is to open it up to full read/write and see if it still works:

chmod 755 /var/run/mysqld/mysqld.sock

Sources:

9

For MySQL 5.7, the default password is printed in the logs. A quick grep can return it.

grep 'temporary password' /var/log/mysqld.log

References:

6

UBUNTU 16.04 and MYSQL 5.7.20

I did not find ADDB's instructions completely helpful. After searching I discovered the following (a small re-write for Option 2):

sudo /etc/init.d/mysql stop sudo mkdir /var/run/mysqld/ sudo chown mysql /var/run/mysqld/ sudo mysqld_safe --skip-grant-tables & mysql -uroot use mysql; update user set authentication_string=PASSWORD("NEW_PASSWORD_GOES_HERE") where User='root'; flush privileges; quit; sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start 

Reference:

My preferred method:

I used the Mysql user "debian-sys-maint" (a root equivelant). The password can be found in /etc/mysql/debian.cnf. After logging into Mysql as this user change the password for any Mysql user.

USE mysql
SELECT root@localhost;
ALTER USER user IDENTIFIED BY 'auth_string';
ALTER USER 'Name_of_user_to_be_modified'@'Hostname (ie:localhost)' IDENTIFIED BY 'NEW_PASSWORD';
flush privileges;
exit;

Reference:

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy