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

MySQL where to find user credentials file?

Posted June 20, 2017 26.4k views
MySQL

Dear viewers,

I am struggling with this problem for a while now. And it is just taking too long. I am having this error: SQLSTATE[HY000] [1045] Access…root@…password: YES) whenever I get on my website’s main page. This doesn’t occur when entering my website’s phpmyadmin page though. Now, people have told me to fck off and learn everything about MySQL and then come back, but there were also people who told me that my MySQL credentials were not set right. So, my question now is: “Where can I find these MySQL Credentials and in which file do I need to adjust them?*”

Warmest regards,

iAmNew

Add a comment
By:
iAmNew

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

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.

×
5 answers
SporkWitch June 20, 2017

MySQL and MariaDB store their credentials internally, so far as I recall (e.g. it’s not a file you edit directly, it’s an internal database that it stores it in).

The error, however, is the result of improper credentials in your web application. You need to read the documentation for that web application, and provision an account and database for it to use.

So yes, the people telling you to read are right; read the documentation of the things you’re using, it’ll tell you what to do.

Reply Report
iAmNew June 20, 2017

Dear SporkWitch,

Thank’s for the reply man. Could you maybe send me a link to the documentation I have to read?
Appreciate it man.

Kind regards,

iAmNew

Reply Report
jtittle June 20, 2017

@iAmNew

On some repositories, MySQL is not setup with a default root password, thus you’re able to login as root without a password. On others, you’re prompted to set a password during the initial install or when running the command:

mysql_secure_installation

If you can run the following from the CLI and gain instant access, a root password isn’t set:

mysql -u root

… and you would need to run mysql_secure_installation to set the root password.

On the other hand, if you are not able to gain instant access, then a root password is set and what it is depends on how you setup your installation.

If you installed a LAMP stack via a one-click image, your root password should be here:

/root/.digitalocean_password

If you didn’t install via a one-click image, the only way to reset the root password would be to use start MySQL in safe mode and do a reset on the password.

https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password

How To Reset Your MySQL or MariaDB Root Password
by Mateusz Papiernik
Forgetting passwords happens to the best of us. If you forget or lose the root password to your MySQL or MariaDB database, you can still gain access and reset the password if you have access to the server and a sudo-enabled user account. This tutorial will cover how to...
Reply Report
  • iAmNew June 20, 2017

    Hi Jtlttle,

    I am not using Digital Ocean as hosting service. I am sponsored by another company, eventhough I really like Digital Ocean.
    This probably sounds weird, me being sponsored. It’s temporary until I have setup my website and make income (my goal).

    To access the console of the host I use PuTTy, a remote console. In order to transfer files I use FileZilla.
    I have installed the LAMP pack manually trough the console.
    I also have installed mysql (incl. in the lamp ofc.) and setup a password for this.

    What I am actually is trying to find out is this: People have told me the: SQLSTATE[HY000] [1045] Access denied for user ‘root’@'localhost’ (using password: YES) error stands for me not having correctly setup the password in the mysql credentials string. This confuses me alot right now. I don’t know where to find the file or the DB where my users/passwords are stored.

    I hope that someone could help me with this here.

    Appreciate your reply,

    Kind regards,

    iAmNew

    Reply Report
    • jtittle June 20, 2017

      @iAmNew

      The first and last part of my reply would be valid, even for those not using DigitalOcean :-). The only thing you wouldn’t have is this file:

      /root/.digitalocean_password

      The root password is stored internally within MySQL, so you won’t be able to open a file or randomly find it elsewhere.

      Once it’s set, it’s encrypted and the only way to reset it is by starting MySQL in safe mode (as noted in the link I provided) and setting a new root password.

      If you can run:

      mysql -u root

      from the CLI and get the MySQL prompt, then you don’t have a root password set (notice I am not using the -p flag, that will always prompt you for a password, even if you don’t have one set).

      If you can access the MySQL CLI when running the command above, you need to run:

      mysql_secure_installation

      to set the password. From there, you can use:

      mysql -u root -p

      to login from the CLI, or use the root username and the password you just set to access other areas or software to manage your databases.

      Reply Report
    • SporkWitch June 20, 2017

      As I said, it’s the web application that is using the wrong credentials; that is where you need to configure the correct user and passphrase. It should not be connecting as root, it should have its own user and database to use. The documentation for the web application you’re trying to use will tell you what it expects. The documentation for your database management tool (mysql or mariadb it sounds like; the commands are virtually identical between them, as mariadb is intended to be a drop-in replacement for mysql) will tell you how to create users and databases and set permissions. The documentation for the web app will sometimes include the commands to use, as well.

      Reply Report
      • iAmNew June 21, 2017

        What web application do you mean?

        Reply Report
        • SporkWitch June 22, 2017

          Based on your other post, it sounds like the web application would be whatever this template you copied over was. Something in there is attempting to connect to the database as root and using some password. Typically, most web applications have an installation wizard that will come up the first time you browse to it and walk you through entering this information. There may also be some configuration file you’re expected to set things up in ahead of time. What it’s sounding like is that you copied this “template” from a running system, it thinks it’s already set up, and is attempting to connect to the database using the credentials it was configured to use.

          As the information is a bit vague and the back and forth slow, I would encourage you to join the ##digitalocean channel on the Freenode IRC network. I’m sure myself or someone else would be happy to try to work with you to get the information needed to narrow down your troubles and help you get moving with your project.

          https://webchat.freenode.net/

          Use whatever nick you like (though it’s a popular network, what you pick may already be taken; don’t stress it) and enter “##digitalocean” in the channel box. (don’t tick the “auth to services” box)

          NOTE: this is not an official DigitalOcean support channel, nor doe DigitalOcean run Freenode.

          Reply Report
          • iAmNew June 22, 2017

            So basicly you are telling me to browse through all the coding and search for strings trying to connect to a certain thing and adjust these to my own for example databases.

            Thank’s, helped me out :)

            Also thanks for your good help, appreciate it

            Reply Report
  • SporkWitch June 20, 2017

    @jtittle any chance you could delete my redundant messages on here? It looked like they weren’t posting (and didn’t show when I refreshed page), but apparently they did, and I see no delete option.

    Reply Report
    • jtittle June 20, 2017

      @SporkWitch

      We had a slight issue with the community which was flagging all responses as pending (we can see them, but nobody else could), though that’s all fixed as of now – apologies for that!

      I’ve removed the duplicates as of this reply :-).

      Reply Report
jtittle June 20, 2017

@iAmNew

The first and last part of my reply would be valid, even for those not using DigitalOcean :-). The only thing you wouldn’t have is this file:

/root/.digitalocean_password

The root password is stored internally within MySQL, so you won’t be able to open a file or randomly find it elsewhere.

Once it’s set, it’s encrypted and the only way to reset it is by starting MySQL in safe mode (as noted in the link I provided) and setting a new root password.

If you can run:

mysql -u root

from the CLI and get the MySQL prompt, then you don’t have a root password set (notice I am not using the -p flag, that will always prompt you for a password, even if you don’t have one set).

If you can access the MySQL CLI when running the command above, you need to run:

mysql_secure_installation

to set the password. From there, you can use:

mysql -u root -p

to login from the CLI, or use the root username and the password you just set to access other areas or software to manage your databases.

Reply Report
iAmNew June 21, 2017

Update:
This is my current setup I am having right now:

I own a “VDC-4”, a virtual datacenter. This is hosted through a company’s website.
I use Putty to connect to this datacenter and install packages on it.
I use FileZilla to transfer files to this datacenter.
I only have installed the LAMP-Pack yet and setup the phpMyAdmin yet.
Also, I have created a virtual host file in etc/apache2/sites-available/virtualhost.conf, in this I have put this code:
<VirtualHost *:80>
ServerAdmin root@localhost
ServerName domainname.tk
ServerAlias www.domainname.tk
DocumentRoot /var/www/
ErrorLog ${APACHELOGDIR}/error.log
CustomLog ${APACHELOGDIR}/access.log combined
</VirtualHost>
<Directory /var/www/>
Options Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
allow from all
</Directory>

Also, I have dragged a template website script (including .php, images, .js etc.) into the /var/www/ folder.
Then I have changed the .htaccess file to this content:
RewriteEngine on
RewriteRule ^(\w+)$ index.php?page=$1 [L,NC,QSA]
RewriteRule ^(\w+)+\/$ index.php?page=$1 [L,NC,QSA]

order allow,deny
deny from xx.xxx.xxx.xxx
allow from all

This is all I have done sofar. I was following a tutorial of someone setting up this website script. But when I tried to open it in my browser the SQLSTATE[HY000] [1045] Access denied for user ‘root’@'localhost’ (using password: YES) error showed up. And this did not occur when he did it.

So I am trying to figure out what I’ve done wrong, but I haven’t yet.

I hope this makes it easier to understand for you guys.

Kind regards,

iAmNew

Reply Report
  • iAmNew June 21, 2017
    Reply Report
  • jtittle June 21, 2017

    @iAmNew

    If you’re receiving an access denied error from MySQL, the Apache VirtualHost wouldn’t be the reason for it as Apache and MySQL are independent services.

    The issue is that the password you’re attempting to use is wrong for the root user or the root user does not have access to the database.

    That being said, it’s not advisable to use root for database access. You should create a new DB, add a user to it, and then use the new database and user for access. Using the root user opens you up to a big security issue in that root is able to access and delete anything. In the event there’s a security hole in your application, and your credentials are leaked, it’d be super easy for someone to just delete your users and databases with ease.

    …

    That being said, there’s a lot that goes in to managing a server – setting up Apache, PHP and MySQL is only part of it.

    Most guides don’t dive deep in to security, fine-tuning, and other aspects of server management as the guide would turn in to a book relatively quickly :-).

    To effectively manage a server, you need to be comfortable with the CLI and using the standard linux commands to perform common tasks. Control panels such as cPanel, VestaCP, and others can help offset some of the overall setup, though they don’t do everything for you and you still need to be able to work from the CLI.

    This is often hidden with shared hosting as the provider is doing all of this for you (or should be), so you’re able to simply use the control panel they provide and things appear to be seamless.

    Reply Report
    • iAmNew June 21, 2017

      Thank’s for the good help you provided, Jtittle.
      Appreciate it alot. I now know what I have to do and it isn’t this confusing anymore. Thanks :)

      Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help