Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
How to solved server socket? Question Question
Cannot allocate memory for the buffer pool Question Question

Question

Locked out of MySQL

Posted October 16, 2019 1.1k views
CentOSMySQL

Hi guys,

So, I’ve just tried to secure my mysql connection and put a password which I read was the right thing to do.

Having said that, now I can’t actually connect to MySQL using the following command

mysql

It says I don’t have access. I’m at a loss, what to do?

The good thing is, my applications are running

Add a comment
Remdore
By:
Remdore

Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
How to solved server socket? Question Question
Cannot allocate memory for the buffer pool Question Question

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.

×
Submit an Answer
1 answer
KFSys October 16, 2019

Hi @Remdore,

Don’t worry, you can still enter your mysql like that

mysql -u root -p

That will connect you with your root user once you provide your password.

Having said that, if you don’t want to type in your password every single time, there is a solution to the problem as well. Create a file at the following location

touch ~/.my.cnf

In the file put the following

vi ~/.my.cnf

[mysql]
user=root
password=YourPAsswordHere

That should be enough for you to be able to enter your MySQL service just by typing mysql.

Regards,
KDSys

Reply Report
  • Remdore October 17, 2019

    Thank you, that worked like a charm.

    Despite this, I noticed other issues. I now can’t access my mysqladmin options. I tried with and without providing a password but the end result was the same.

    Reply Report
    • KFSys October 17, 2019

      Glad to hear it’s working.

      To resolve the issue you are facing now, in the same file 

      ~/.my.cnf

      You can add the following 

      [mysqladmin]
user=root
password=YourPAsswordHere

      The principle is the same for every other command you’ll want to execute.

      Regards,
      KDSys

      Reply Report
      • Remdore October 17, 2019

        Thank you!

        So you are saying that in theory if my mysqldump command doesn’t work(haven’t tested it out yet) with the same error for needing a password, I can just add it to the file .my.cnf like this

        [mysqldump]
user=root
password=

        It will be fine?

        What If I want to use another user than root, will I be able to?

        Reply Report
        • KFSys October 17, 2019

          Hi @Remdore, you are getting the hang of it!

          Yes, setting that in your ~/.my.cnf file will work and you’ll be able to use mysqldump just the way you described it.

          As for your second question, you’ll still be able to connect or dump a database using another user. The lines you’ve added to your file don’t in anyway prevent you from doing so.

          Regards,
          KDSys

          Reply Report

Related

Looking for something else?

Ask a new question Search for more help