Home / ubuntu / Questions / 931001
Accepted
A. N. Other
Asked: 2017-07-02 06:05:32 +0800 CST

Problem with mysql root password

  • 772

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.

mysql

4 Answers

  1. 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';
    • 4
    • 1
  3. Best Answer

    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:

    https://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html

    https://ubuntu.flowconsult.at/en/mysql-set-change-reset-root-password/

    https://stackoverflow.com/questions/11990708/error-cant-connect-to-local-mysql-server-through-socket-var-run-mysqld-mysq

    • 1

  4. 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: http://rricketts.com/reset-root-password-mysql-5-7-ubuntu-16-04-lts/

    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: https://serverfault.com/questions/9948/what-is-the-debian-sys-maint-mysql-user-and-more

    • 0