"Error establishing a database connection" MySQL/MariaDB ...

Rsdaa 22/01/2022 1651

Learn about the MySQL/MariaDB error "Error establishing a database connection" or "Unable to connect to database server," and how to fix this error.

Make sure MySQL is running

If MySQL has stopped running, scripts and websites will be unable to connect. On a Linux server, you can see if MySQL is running by using the command:

This will return a list of running processes which have "mysql" in the name. The list will include the command you just ran. If MySQL is running, it will also include the MySQL process.

If MySQL/MariaDB is not running, you will only see your grep command listed.

Start MySQL/MariaDB with the command:

CentOS 7 (MariaDB): systemctl start mariadbUbuntu and Debian (MySQL): service mysql start or /etc/init.d/mysqld start

Be sure to use the correct username and password for your MySQL user. These are different from your IONOS login credentials. The required username and password are set when you create the MySQL database.

You will also need to set the correct hostname.

Verify the database connection from the command line

Once you know that MySQL/MariaDB is running, the next step is to connect from the command line with the command:

You should be prompted to enter the root MySQL user's password. By default, the password for the root MySQL user is the same as the original root password for your server.

Note

If you change the password for the root server user, this will not change the password for the root MySQL user.

After you enter the password, you should find yourself in the MySQL/MariaDB client, which should look something like this:

Welcome to the MariaDB monitor.Commands end with ; or \g.Your MariaDB connection id is 4172Server version: 5.5.44-MariaDB MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>

If you are having trouble establishing a MySQL connection with a different username and password, do this test a second time, replacing root with the other username.

If instead of logging in to the client, you get an error like this:

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

You are not entering the correct password for the username. Verify that the spelling of both the username and the password are correct. Note that the username is case-sensitive - Root is not the same as root.

Sell on your existing website or blog

Verify the MySQL username

It is worth checking to be sure that you are using the correct spelling and capitalization for the username. To list all of the MySQL/MariaDB users, log in to the command-line client as root with the command:

Next, list all of the MySQL users and their hosts with the command:

select host, user from mysql.user;

This will show you the username exactly as it was created, as well as the location (host) it is allowed to connect from.

Reset the user's MySQL password

Note

Be sure to use caution when using this command, particularly when resetting the root MySQL user's password.

To reset a user's MySQL/MariaDB password, log in to the command-line client as root with the command:

Next, reset the user's password with the command:

update mysql.user set password = MD5('(new_password)') where user = "jdoe";

Replace new_password with the new password, and jdoe with the username.

Make sure the user has the correct privileges

If you are certain that the username and password are correct, the problem may be that the user does not have the correct privileges (grants) for that database.

To check a user's privileges (grants), log in to the command-line client as root with the command:

Next, show the user's grants with the command:

show grants for 'jdoe'@'localhost';

Replace jdoe with the username. If applicable, change localhost to the host name.

You should get a list of the privileges that user has for the relevant database. It should look something like this:

+-----------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'jdoe'@'localhost' IDENTIFIED BY PASSWORD '*CD2BAEA88178935C438649F12126752DB1B24D7B' || GRANT ALL PRIVILEGES ON `example_database`.* TO 'jdoe'@'localhost'|+-----------------------------------------------------------------------------------------------------------------------+

Note that this user has all privileges on a database named example_database.

If the user has not been granted privileges on any databases, you will only see something like this:

+-------------------------------------------------------------------------------------------------------------+| Grants for jdoe@localhost |+-------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'jdoe'@'localhost' IDENTIFIED BY PASSWORD '*00E247AC5F9AF26AE0194B41E1E769DEE1429A29' |+-------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Note that this user only has USAGE privileges on ".", and does not have privileges on any databases.

Verify the database connection from the web

Use a simple script to test whether your database will accept the connection. Create a file called database-connection-test.php with the following content:

Replace the word password with your root MySQL user's password.

Place this file in your web directory, then visit it in a browser. If you are able to connect with the root MySQL username and password, then you know that your database is accepting connections.

If you are having trouble establishing a MySQL connection with a different username and password, do this test a second time, replacing root with the other username and password with the password.

Note

Be sure to delete this file once you have finished your tests.