Related

Product Now Available: Managed MySQL Databases Product
Install MySQL on New Droplet Question Question
How can I solve error "SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)" Question Question

Question

Error Connecting to MySQL as Root User with 1-click LAMP

Posted November 10, 2020 105 views
MySQL

Using the root MySQL password from /root/.digitalocean_password I am not able to log in using root. Using DBeaver, ssh connection and the tunnel connect is working correctly. I get the error “Access denied for user ‘root’@'localhost’”

Is there something else I need to do?

Add a comment
geoffAU
By:
geoffAU

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer
bobbyiliev November 10, 2020

Hi there @geoffAU,

What is the exact command that you are using? Note that you need to specify the -p flag so that you could get prompted for a password:

  • mysql -u root -p

Another thing that you could do is add your MySQL password to your ~/.my.cnf file so that you don’t have to enter the password each time as described here.

To do so follow these steps here:

  • Create the file:
  • touch ~/.my.cnf
  • Give it secure permissions:
  • chmod 600 ~/.my.cnf
  • Using your favorite text editor edit the file:
  • nano ~/.my.cnf
  • And add the following configuration
[client]
user=root
password=YOUR_MYSQL_PASSWORD
  • Save and exit

Then you will be able to login to MySQL by just running mysql.

Regards,
Bobby

Reply Report
  • geoffAU November 10, 2020

    Hello,

    Are you referring to accessing MySQL from the consol? This is possible already after the droplet was installed. The problem is logging in as root user using DBeaver using ssh funnel. The ssh funnel connects but I still get an error connecting to MySQL as the root@localhost user with password.

    Reply Report
    • bobbyiliev November 10, 2020

      Hi there,

      Ah yes, I was referring to the command line.

      As the LAMP 1-Click image comes with MySQL 8, in DBever when adding the new database connection, are you choosing MySQL 8+?

      Regards,
      Bobby

      Reply Report
      • geoffAU November 10, 2020

        Yes, and the connectors for MySQL 8 are set up in the drivers for MySQL connection in DBeaver.

        Reply Report
        • bobbyiliev November 13, 2020

          Hi there @geoffAU,

          What worked for me was the following:

          • Access MySQL and then update your password using:
          ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-secure-password';

          After that you might have to restart MySQL as well:

          sudo systemctl restart mysql

          But for me, a better and more secure approach would be to create a non-root system user and use it instead of the root user.

          Let me know how it goes!
          Regards,
          Bobby

          Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help