Default password of mysql in ubuntu server 16.04

Question

I have installed ubuntu 16.04 server. Mysql server was installed by default in it. When I am trying to access the mysql with mysql -u root -p, I am unable to log in to mysql because I dont have the password. Is there any default password?

I have also tried with --skip-grant-tables, even this does not work. Even trying to log in with just mysql -u root is a failure.

Answer 1

• Use sudo mysql -u root -p, it lets you in with any password if not then use sudo mysql.
• If you're entering the console successfully then do the following.

Drop the root user.

DROP USER 'root'@'localhost';

Create it again.

CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';

Grant the permission.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Restart mysql service.

service mysql restart 

---

If you still can't enter the console, you have to re-install the mysql.

sudo apt-get remove --purge mysql-server mysql-client mysql-common -y

sudo apt-get autoremove -y

sudo apt-get autoclean

sudo rm -rf /etc/mysql

Delete all MySQL files on your server:

sudo find / -iname 'mysql*' -exec rm -rf {} \;

Install mysql.

sudo apt update
sudo apt install mysql-server

Do the installation.

sudo mysql_secure_installation

• I did the same when I had this problem, hope it helps!

Answer 2

Edit:

You can login with user debian-sys-maint, which has all the expected privileges, the password is in the file /etc/mysql/debian.cnf

---

Original answer:

As of Ubuntu 20.04 with MySql 8.0 : the function PASSWORD do not exists any more, hence the right way is:

1. login to mysql with sudo mysql -u root

2. change the password:

USE mysql;
UPDATE user set authentication_string=NULL where User='root';
FLUSH privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'My-N7w_And.5ecure-P@s5w0rd';
FLUSH privileges;
QUIT

now you should be able to login with mysql -u root -p (or to phpMyAdmin with username root) and your chosen password.

Answer 3

On MySQL 8.0.15 (maybe earlier than this too): the PASSWORD() function does not work anymore, so you have to do:

Make sure you have stopped MySQL first (Go to: 'System Preferences' >> 'MySQL' and stop MySQL).

Run the server in safe mode with privilege bypass:

sudo mysqld_safe --skip-grant-tables
mysql -u root
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

Then

mysql -u root
ALTER USER 'root'@'lo`enter code here`calhost' IDENTIFIED WITH caching_sha2_password BY 
'yourpasswd';

Finally, start your MySQL again.

Answer 4

For new installation, mysql password for root is generated and written to the logs /var/log/mysqld.log. If you're trying to automate mysql solution, you can use password from variable:

mysql_pass=$(sudo grep -oP "temporary password is generated for root@localhost: \K(.*)" /var/log/mysqld.log)

mysql -uroot -p"$mysql_pass" < somescript.sql

Answer 5

Following gave me cred for freshly installed mysql

sudo cat /etc/mysql/debian.cnf

I tried connceting with workbench and it worked out for me following is to install workbench

sudo apt-get install mysql-workbench

Answer 6

this worked for me on Ubuntu 20.04 with mysql 8, the weird hashing thing is because the native PASSWORD() function was removed in mysql 8 (or earlier?)

UPDATE mysql.user SET 
 plugin = 'mysql_native_password',
 Host = '%',
 authentication_string = CONCAT('*', UPPER(SHA1(UNHEX(SHA1('insert password here'))))) 
WHERE User = 'root';
FLUSH PRIVILEGES;

Answer 7

The only option that worked for me is the one described in this link.

In summary (in case the website goes down), it says:

To install MySQL:

sudo apt update
sudo apt install mysql-server

On the next prompt, you will be asked to set a password for the MySQL root user. Once you do that the script will also ask you to remove the anonymous user, restrict root user access to the local machine and remove the test database. You should answer “Y” (yes) to all questions.

sudo mysql_secure_installation

Then

sudo mysql

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

After that you can exit:

exit

Now you can easily login with (Remember to type your very_strong_password):

mysql -u root -p
# type the password now.

This was the only option that worked for me after many hours trying the options above.

Answer 8

If you install your mysql with apt install mysql.
you can just login to your mysql with sudo mysql.

Answer 9

Early versions allowed root password to be blank but, in Newer versions set the root password is the admin(main) user login password during the installation.

Answer 10

As of Ubuntu 20.04, using the default MariaDB 10.3 package, these were the necessary steps (remix of earlier answers from this thread):

1. Log in to mysql as root: sudo mysql -u root
2. Update the password:

USE mysql;
UPDATE user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';
UPDATE user set plugin="mysql_native_password" where User='root';
FLUSH privileges;
QUIT

3. sudo service mysql restart

After this, you can connect to your local mysql with your new password: mysql -u root -p

Answer 11

In latest version, mySQL uses auth_socket, so to login you've to know about the auto generated user credentials. or if you download binary version, while installation process, you can choose lagacy password.

To install SQL in linux debian versions

sudo apt install mysql-server

to know about the password

sudo cat /etc/mysql/debian.cnf

Now login

mysql -u debian-sys-maint -p

use the password from debian.cnf

How to see available user records:

USE mysql
SELECT User, Host, plugin FROM mysql.user;

Now you can create a new user. Use the below commands:

use mysql;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'username'@'localhost';
flush privileges;

To list the grants for the particular mysql user

SHOW GRANTS FOR 'username'@'localhost';

How to revoke all the grants for the particular mysql user

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'localhost';

To delete/remove particular user from user account list

DROP USER 'username'@'localhost';

For more commands:

$ man 1 mysql

Please don't reset the password for root, instead create a new user and grant rights. This is the best practice.

Answer 12

I had a fresh installation of mysql-server on Ubuntu 18.10 and couldn't login with default password. Then only I got to know that by default root user is authenticated using auth_socket. So as in the answer when the plugin changed to mysql_native_password, we can use mysql default password

$ sudo apt install mysql-server
$ sudo cat /etc/mysql/debian.cnf

You can find the following lines in there

user     = debian-sys-maint
password = password_for_the_user

Then:

$ mysql -u debian-sys-maint -p
Enter password: 

type the password from debian.cnf

mysql> USE mysql
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | auth_socket           |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> COMMIT; 

Either:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Or:

// For MySQL 5.7+

mysql>UPDATE mysql.user SET authentication_string=PASSWORD('new_password') where user='root';

--Update--

Sometimes you will need to restart your mysql server.

sudo service mysql restart

or

sudo systemctl restart mysql

Answer 13

Note that in Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. you will need to switch its authentication method from auth_socket to mysql_native_password

as @BeNiza said, they changed the security model. I did following steps and it works for mysql 5.7.27 on ubuntu 18.04

sudo apt install mysql-server

The MySQL database software is now installed, but its configuration is not yet complete.

To secure the installation, MySQL comes with a script that will ask whether we want to modify some insecure defaults. Initiate the script by typing:

sudo mysql_secure_installation

you should press Y and hit the ENTER key at each prompt.

This will cause issues if you use a weak password

You can simply login to your mysql as root without providing any password by adding sudo before your mysql command.

sudo mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-password';

If you set a weak password you would see the following error:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> FLUSH PRIVILEGES;

mysql> exit

Note: After configuring your root MySQL user to authenticate with a password, you’ll no longer be able to access MySQL with the sudo mysql command used previously. Instead, you must run the following: mysql -u root -p

After entering the password you just set, you will see the MySQL prompt.

Answer 14

Although this is an old question, there are several of us still struggle to find an answer. At least I did. Please don't follow all the lengthy solutions. You could simply login to your mysql as root without providing any password (provided it is a fresh installation or you haven't changed the password since your installation) by adding sudo before your mysql command. $sudo mysql -uroot -p mysql> This is because mysql changed the security model in one of the latest versions.

Hope this helps

Answer 15

Mysql by default has root user's authentication plugin as auth_socket, which requires the system user name and db user name to be the same.

Specifically, log in as root or sudo -i and just type mysql and you will be logged in as mysql root, you can then create other operating users.

If you do not have a root on host, I guess you should not be allowed to login to mysql as root?

Answer 16

This is what you are looking for:
sudo mysql --defaults-file=/etc/mysql/debian.cnf
MySql on Debian-base Linux usually use a configuration file with the credentials.

Answer 17

I think another place to look is /var/lib. If you go there you can see three mysql folders with 'interesting' permissions:

user   group 
mysql  mysql

Here is what I did to solve my problem with root password:

after running

sudo apt-get purge mysql*
sudo rm -rf /etc/mysql

I also ran the following (instead of my_username put yours):

cd /var/lib
sudo chown --from=mysql <my_username> mysql* -R
sudo rm -rf mysql*

And then:

sudo apt-get install mysql-server

which prompted me to select a new root password. I hope it helps

Answer 18

Simply run sudo dpkg-reconfigure mysql-server-5.7

You can find the version you have installed by running dpkg --get-selections | grep mysql-server

Answer 19

You can simply reset the root password by running the server with --skip-grant-tables and logging in without a password by running the following as root or with sudo:

service mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root

mysql> use mysql;
mysql> update user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

# service mysql stop
# service mysql start
$ mysql -u root -p

Answer 20

1. the first you should stop mysql
2. use this command sudo mysqld_safe --skip-grant-tables --skip-networking &
3. and then input mysql -u root try this way,I have been solved my problem with this method.