Locked out of MySQL

October 16, 2019 121 views
MySQL CentOS

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

1 Answer

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

  • 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.

    • 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

      • 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?

        • 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

Have another answer? Share your knowledge.