1

I installed MySQL (fresh) on my Ubuntu 18.04. And I'm having problems logging into the database. During installation there was no request for password and when I try

mysql -u root -p

the password was somehow set and I do not not how it is possible as it's a fresh installation.

Because I was confused, I tried removing MySQL packages as was somewhere suggested

sudo apt-get remove -y mysql-*
sudo apt-get purge -y mysql-*

After this I made a new installation 

sudo apt-get install mysql-server

but the result is the same. Not possible to login as root without password. I went through many discussions about root password reset and no success.

I tried to start mysql server with special init file via

mysqld --init-file=/home/me/mysql-init &

the file consisted of 

UPDATE mysql.user SET Password=PASSWORD('pass') WHERE User='root';
FLUSH PRIVILEGES;

result is 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I also tried 

sudo dpkg-reconfigure mysql-server-5.7

5.7. is correct version of my mysql server, but no result - as I read in another discussion this is possibly obsolete solution but I tried it anyway.

I also tried

sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
UPDATE user SET authentication_string=PASSWORD('newpass') WHERE user='root';
FLUSH PRIVILEGES;

but the new password does not work at all.

During installation there should be a request for root password, right? Or how can the password be set in the new installation? I'm quite new to Linux but I would expect that reinstall with purge should remove all settings and the new installation should be without root password?

Zanna
  • 70,465
P.J.
  • 23

2 Answers2

2

You can Set, Change and reset your root password On Ubuntu with 4 steps simply :

  1. Stop your server with init.d

    sudo /etc/init.d/mysql stop

  2. Start with mysqld configuration

    sudo mysqld --skip-grant-tables $

  3. Login as root

    mysql -u root mysql

  4. Replace your new password

    UPDATE user SET password=PASSWORD('new_password') WHERE user=root;
    FLUSH PRIVILEGES;
    EXIT;

Hope this helps.

Community
  • 1
abu-ahmed al-khatiri
  • 1,739
  • 1
    Changing password is not enough. MySQL accounts have specific login methods. Specifically, root account is setup such way that the socket via which you connect to mysql has to be owned by root. In order for password to work you also have to change which login method ( aka plugin ) the account uses. See https://askubuntu.com/a/1105982/295286 – Sergiy Kolodyazhnyy Jan 09 '19 at 18:06
  • @SergiyKolodyazhnyy yo're right, i check it before the OP's question marked as duplicate. should i delete it? – abu-ahmed al-khatiri Jan 10 '19 at 08:33
  • No, you can just click edit button right above our comments and add that information to your answer. There's no need to delete. People still can find duplicate posts and the answer can be useful for them – Sergiy Kolodyazhnyy Jan 10 '19 at 08:36
  • 1
    Thanks to @SergiyKolodyazhnyy , your link to askubuntu.com/a/1105982/295286 helped and I can now work with the database :-) – P.J. Jan 28 '19 at 21:32
  • @PetrJ. Awesome ! Have fun making SQL queries and don't forget the closing ; in the queries :D – Sergiy Kolodyazhnyy Jan 28 '19 at 21:35
1

I'm guessing you actually installed MariaDB? If so, you can get root (or sudo) and log in like this:

sudo mysql

That should get you in.

Eric Mintz
  • 2,516
  • 12
  • 24
  • Whether it's MariaDB or MySQL is irrelevant in this case, MariaDB and MySQL work the same in this regard. However, the answer is pretty much correct. By default root account in MySQL is set up with auth_socket plugin, so the connection socket has to be owned by root and that's what sudo will do. Note also that root in MySQL is internal account, it has nothing to do with either sudo or root on the host system itself. – Sergiy Kolodyazhnyy Jan 09 '19 at 18:09