Database connection error

Question

I never used Ubuntu, and now I installed Ubuntu 18.04, and I have a problem with connection to Database.

I'm not so familiar with the working with servers and things like that, so can anyone tell me what the problem is and how to fix it?

Solution

Check that your mysql server is running, if not enable it. From a terminal,

$ sudo systemctl enable mysql

Synchronizing state of mysql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable mysql

$ sudo systemctl status mysql

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2018-06-25 10:23:56 PDT; 56min ago
 Main PID: 1364 (mysqld)
    Tasks: 28 (limit: 4915)
   CGroup: /system.slice/mysql.service
           └─1364 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Check that you have it listen on port 3306.

$ sudo netstat -plunt

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1364/mysqld   

If not, you'll have to modify /etc/mysql/mysql.conf.d/mysqld.cnf

Check that you have root access, enter password when prompted.

$ mysql -u root -p

If you don't have root access or need to reset root password, follow the steps from here. Which basically says the simplest way is do the following and choose a new root password.

$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

Now, if you want simply want to connect to a remote database, specify the host and port along with the login user/password when creating the mysql connection.

If you run into password validation problem, you may consider moving validate_password plugin

$ mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.22-0ubuntu18.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> uninstall plugin validate_password;

Comments

[Kulfy]

Are you able to start MySQL from terminal?

[Simon]

@Kulfy I only know to use this: service mysql status, it seems to work, and don't know what next nor how to fix it...

[Kulfy]

Try mysql -u root -p in terminal

[Simon]

@Kulfy it says: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[Kulfy]

It means you have provided wrong password and most probably in the given application too.

[Simon]

@Kulfy do I need to reinstall mysql? will then be fixed? or is it other way? i'm sure i used correct one

[Kulfy]

No need to reinstall mysql. Try changing the password. If the password is correct then mysql wouldn't have denied the access. Try mysql -u root

[Simon]

@Kulfy ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) :(

[Kulfy]

It mean that you've set a password that isn't blank. Also, you are entering wrong password. I'll suggest you to create new password. Refer this to reset password askubuntu.com/a/489132/816190

[Simon]

I tried it, but it's not worth it, then I reinstalled everything, and again the same error is displayed but now i can access mysql in terminal...

[Simon]

i came to "three levels of password validation policy...", what should i do there? and how to specify the host? Sorry, but this is the firs time i'm doing this

[Bernard Wei]

Looks like you ran into the same problem as I did when I upgraded to 18.04. I have to uninstall validate_password plugin so that I can use my password. Otherwise it kept complaining it is not secure enough password for "online" login. I'll add some instruction to remove the plugin.

[Simon]

Yes, I've heard from a lot of people that they have errors since they were upgraded to 18.04, okay, I am waiting for instructions

[Bernard Wei]

In 18.04, they enable the security feature to protect remote login, i.e., connecting via host and port. So you need to re-enable that in order to use mysql the old way, which is the way that workbench connect to the database.