"Error establishing a database connection" MySQL/MariaDB error
IONOS editorial team6 mins
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:
ps -aux | grep mysqlThis 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 mariadb
- Ubuntu 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:
mysql -u root -pYou 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.
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 4172
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (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 social media in minutes
- Manage it all from one platform
- Works with any product or service
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:
mysql -u root -pNext, 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
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:
mysql -u root -pNext, 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:
mysql -u root -pNext, 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:
<?php
$test_connect = mysql_connect('localhost', 'root', 'password');
if (!$test_connect) {
die('Could not connect: ' . mysql_error());
}
echo 'Successful database connection.';
mysql_close($test_connect);
?>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.
Be sure to delete this file once you have finished your tests.
Check the MySQL hostname
If the script is hosted on the same server as the MySQL database (e.g. your Linux Cloud Server), the hostname is localhost by default.
To double-check the MySQL hostname, log into the command-line client as root with the command:
mysql -u root -pNext, show the hostname with the command:
SHOW VARIABLES WHERE Variable_name = 'hostname';If your script is accessing a remote database, the hostname will be the URL of that database.
