[mysql] Default password of mysql in ubuntu server 16.04

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.

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

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

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.

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

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.

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.

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.

P,S:

You can also login with user debian-sys-maint, the password is in the file /etc/mysql/debian.cnf

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.

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

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.

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.

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?

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;

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

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

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