A big part of the content on the Internet is stored on databases for which MySQL is a popular choice. But what to do when suddenly your dynamic content doesn’t load, or when returning to your website you are greeted by a nearly empty white page with the message “Error establishing a database connection.” This guide is aimed at helping with troubleshooting MySQL databases on cloud servers, and by following the steps listed here you’ll hopefully be able to restore your database functionality.
Check that the service is running
If your website cannot connect to your database, it is possible the service is simply not listening. Check your MySQL state, on Ubuntu and Debian systems this can be done with the following command.
sudo service mysql status
CentOS and other Red Hat variants use MySQL as well, but it is named MariaDB instead, so use this command instead.
sudo service mariadb status
The output from the status check on CentOS and Debian will show something along the lines of this example from CentOS below, Debian output would be nearly identical with the exception of a different service name.
mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled)
Active: active (running) since Wed 2015-08-05 11:53:38 EEST; 3h 23min ago
Main PID: 2451 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─2451 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─2609 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql...The printout is rather verbose, but the important part is usually coloured to stand out better. In green ‘active (running)’ means the service should be running normally if instead, it says ‘active (exited)’ or ‘inactive (dead)’ the process has been stopped or killed.
Ubuntu condenses the same information to a one-liner like an example output underneath.
mysql start/running, process 5897
If your service status says something other than ‘running’, try to restart the process using the same service command as before but with ‘restart’ instead of ‘status’.
sudo service mysql restart sudo service mariadb restart
Should the database service restart without encountering errors, you can try to connect to it using the command below. Enter the root password when prompted.
mysql -u root -p
If you are greeted with “Welcome to the MySQL/MariaDB monitor” the connection was successful and the database service is running. If instead, you get an error like this example below you probably mistyped the password for the root user. Try again, or if you are not sure about the root password, log in with another user account you have access to by just replacing root with the other username.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
If you have your database set up on a separate server from your web host, make sure the two servers can reach each other. You can test the database connection from your web server with the command underneath using the correct username for your installation.
mysql -u <user name> -p -h <database server private IP>
Check the configuration
When MySQL is running but your website still doesn’t load as it should, or when attempting to connect to your database manually you get an error message like the one below, you should take a look at the service configuration.
ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
On Debian and Ubuntu servers the configuration file for MySQL is usually saved at /etc/mysql/. It’s also possible to have user-specific settings stored at /home/<user>/.my.cnf, which would override the global configurations. Check if any user level overrides have been set. It is commonly advised to have separate usernames for different web applications, so check at least those relevant to your page loading issues. You can open the global configuration file with the first of the following two commands below, and the user-specific with the latter by replacing the <user> with a database username.
sudo nano /etc/mysql/my.cnf sudo nano /home/<user>/.my.cnf
By scrolling down past [client] and [mysqld_safe] settings you’ll find something like the example here.
[mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1
With CentOS and other Red Hats, the primary configuration file is stored at the slightly different location, open it for inspection with
sudo vi /etc/my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock
The lines here to pay close attention to are ‘socket’, ‘datadir’ and ‘bind-address’. The parameters in the example above are in their default values, and in most cases, your configuration would look the same. Make sure the settings point to the correct directories so that MySQL can actually find the required files. The easiest way to check the ‘datadir’ is to use this command below
sudo ls -l /var/lib/mysql/
The output will list all files in that directory, it should contain at least the following plus any databases you have created.
drwx------ 2 mysql root 4096 Aug 5 12:23 mysql drwx------ 2 mysql mysql 4096 Aug 5 12:29 performance_schema
If the data directory or socket has been moved and MySQL doesn’t know where they are, fix the configuration file to point to the correct directories. You can search for the folders with the following command.
sudo find / -name performance_schema && sudo find / -name mysql.sock
The third parameter you’ll need to check is the bind address, this is only really relevant if your database needs to be accessed remotely. In Debian and Ubuntu installations the bind is by default set to the loopback address, which prevents database calls from outside the localhost. CentOS doesn’t have the same parameter unless manually set. For any setup where your web service is on a different server to the database, this bind address should be set to the server’s own private IP.
Check the error logs
If the configuration seems correct and the service is running, but your website still doesn’t load as it should, try checking the logs for any hints to as what might be the cause.
Debian and Ubuntu servers store error logs to /var/log/mysql/error.log. You can read through the logs with ‘less’, but this might not be very convenient as the log includes more than just critical errors. Instead, search the logs using ‘grep’.
sudo grep -i error /var/log/mysql/error.log
Should you not be able to find anything within the most recent logs, check the archived ones as well. To do this, use ‘zgrep’ with otherwise the same command as regular ‘grep’
sudo zgrep -i error /var/log/mysql/error.log.1.gz
Since the database under CentOS is named MariaDB instead of MySQL, the logs are also saved under a different name. You can search the logs with the following command.
sudo grep -i error /var/log/mariadb/mariadb.log
Debian systems also report MySQL events to /var/log/syslog, to filter out everything else, use ‘grep’ with two keywords separated by .* to express ‘and’ like in the command below.
sudo grep -i -E 'mysql.*error' /var/log/syslog
If you are having difficulties finding anything helpful, try different keywords such as ‘start’ to see when the service was last restarted, or ‘failed’ to find any less critical problems that might not be reported as errors.
Ask for help
Optimistically by now your database should be up and running again, but in case you’ve encountered a more persistent error, feel free to ask for help. Contact our support team and try to explain the problem to the best of your ability, also include the steps you’ve taken with their results while troubleshooting the issue. This will help the team in helping you with the problem.
Pramendra
Database error established in my website how can i resolved it.
Janne Ruostemaa
Hi there, it seems your website cannot reach your database. This could be due to a number of issues depending on your configuration. If your database is on the same server as your web service, make sure it’s running and accessible with the credentials given to the web service. If the database is on a separate server, in addition to the aforementioned, check that the host is connected and reachable to the website server.
James R Martin
the first command that worked was: sudo ls -l /var/lib/mysql/
which listed:
total 168120
-rw-r—–. 1 mysql mysql 56 Jan 29 09:14 auto.cnf
-rw-r—–. 1 mysql mysql 178 Feb 3 17:00 binlog.000006
-rw-r—–. 1 mysql mysql 178 Feb 4 17:04 binlog.000007
-rw-r—–. 1 mysql mysql 178 Feb 5 09:02 binlog.000008
-rw-r—–. 1 mysql mysql 178 Feb 5 17:58 binlog.000009
-rw-r—–. 1 mysql mysql 178 Feb 6 17:30 binlog.000010
-rw-r—–. 1 mysql mysql 178 Feb 7 17:10 binlog.000011
-rw-r—–. 1 mysql mysql 178 Feb 8 17:15 binlog.000012
-rw-r—–. 1 mysql mysql 178 Feb 11 09:53 binlog.000013
-rw-r—–. 1 mysql mysql 178 Feb 11 16:09 binlog.000014
-rw-r—–. 1 mysql mysql 178 Feb 12 17:51 binlog.000015
-rw-r—–. 1 mysql mysql 178 Feb 13 17:08 binlog.000016
-rw-r—–. 1 mysql mysql 178 Feb 14 16:58 binlog.000017
-rw-r—–. 1 mysql mysql 178 Feb 15 17:48 binlog.000018
-rw-r—–. 1 mysql mysql 178 Feb 17 17:34 binlog.000019
-rw-r—–. 1 mysql mysql 178 Feb 18 17:25 binlog.000020
-rw-r—–. 1 mysql mysql 178 Feb 19 16:17 binlog.000021
-rw-r—–. 1 mysql mysql 178 Feb 20 16:38 binlog.000022
-rw-r—–. 1 mysql mysql 178 Feb 20 16:55 binlog.000023
-rw-r—–. 1 mysql mysql 178 Feb 20 17:48 binlog.000024
-rw-r—–. 1 mysql mysql 178 Feb 21 16:56 binlog.000025
-rw-r—–. 1 mysql mysql 178 Feb 22 16:46 binlog.000026
-rw-r—–. 1 mysql mysql 178 Feb 25 09:13 binlog.000027
-rw-r—–. 1 mysql mysql 178 Feb 25 17:34 binlog.000028
-rw-r—–. 1 mysql mysql 178 Feb 26 16:30 binlog.000029
-rw-r—–. 1 mysql mysql 178 Feb 28 09:22 binlog.000030
-rw-r—–. 1 mysql mysql 178 Feb 28 16:41 binlog.000031
-rw-r—–. 1 mysql mysql 178 Feb 29 16:11 binlog.000032
-rw-r—–. 1 mysql mysql 178 Mar 2 09:00 binlog.000033
-rw-r—–. 1 mysql mysql 178 Mar 3 21:12 binlog.000034
-rw-r—–. 1 mysql mysql 155 Mar 4 06:37 binlog.000035
-rw-r—–. 1 mysql mysql 480 Mar 4 06:37 binlog.index
-rw——-. 1 mysql mysql 1680 Jan 29 09:14 ca-key.pem
-rw-r–r–. 1 mysql mysql 1112 Jan 29 09:14 ca.pem
-rw-r–r–. 1 mysql mysql 1112 Jan 29 09:14 client-cert.pem
-rw——-. 1 mysql mysql 1676 Jan 29 09:14 client-key.pem
-rw-r—–. 1 mysql mysql 3329 Mar 3 21:12 ib_buffer_pool
-rw-r—–. 1 mysql mysql 12582912 Mar 4 06:37 ibdata1
-rw-r—–. 1 mysql mysql 50331648 Mar 4 06:37 ib_logfile0
-rw-r—–. 1 mysql mysql 50331648 Jan 29 09:14 ib_logfile1
-rw-r—–. 1 mysql mysql 12582912 Mar 4 06:37 ibtmp1
drwxr-x—. 2 mysql mysql 187 Mar 4 06:37 ‘#innodb_temp’
drwxr-x—. 2 mysql mysql 143 Jan 29 09:14 mysql
-rw-r—–. 1 mysql mysql 25165824 Mar 4 06:37 mysql.ibd
srwxrwxrwx. 1 mysql mysql 0 Mar 4 06:37 mysql.sock
-rw——-. 1 mysql mysql 5 Mar 4 06:37 mysql.sock.lock
drwxr-x—. 2 mysql mysql 8192 Jan 29 09:14 performance_schema
-rw——-. 1 mysql mysql 1676 Jan 29 09:14 private_key.pem
-rw-r–r–. 1 mysql mysql 452 Jan 29 09:14 public_key.pem
-rw-r–r–. 1 mysql mysql 1112 Jan 29 09:14 server-cert.pem
-rw——-. 1 mysql mysql 1680 Jan 29 09:14 server-key.pem
drwxr-x—. 2 mysql mysql 28 Jan 29 09:15 sys
-rw-r—–. 1 mysql mysql 10485760 Mar 4 06:37 undo_001
-rw-r—–. 1 mysql mysql 10485760 Mar 4 06:37 undo_002
these commands resulted in:
[jmartin@localhost ~]$ sudo grep -i error /var/log/mysql/error.log
grep: /var/log/mysql/error.log: No such file or directory
[jmartin@localhost ~]$ sudo zgrep -i error /var/log/mysql/error.log.1.gz
gzip: /var/log/mysql/error.log.1.gz: No such file or directory
[jmartin@localhost ~]$ sudo grep -i error /var/log/mariadb/mariadb.log
grep: /var/log/mariadb/mariadb.log: No such file or directory
Janne Ruostemaa
Hi James, thanks for the comment. It seems your configuration is recording the error logs somewhere else. You can check the ls -l /var/log/ directory to see where the MySQL logs are and then review them using grep or zgrep.
uday
Help i cant start mysql server ,here is the log:
2021-05-27T08:32:45.344369Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.25-0ubuntu0.20.04.1) starting as process 9768
2021-05-27T08:32:45.354695Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-05-27T08:32:45.516369Z 1 [ERROR] [MY-012562] [InnoDB] We scanned the log up to 388936174. A checkpoint was at 388936184 and the maximum LSN on a database page was 0. It is possible that the database is now corrupt!
2021-05-27T08:32:45.516521Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: log0recv.cc:3942 thread 140625372075776
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
08:32:45 UTC – mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x55d9e4dcc800
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7fe5e5594d20 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x55d9dfb60681]
/usr/sbin/mysqld(handle_fatal_signal+0x31b) [0x55d9de9b0f6b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0) [0x7fe5f242d3c0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb) [0x7fe5f1a8b18b]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x12b) [0x7fe5f1a6a859]
/usr/sbin/mysqld(+0xea257e) [0x55d9de6da57e]
/usr/sbin/mysqld(recv_recovery_from_checkpoint_start(log_t&, unsigned long)+0x234a) [0x55d9dfd245da]
/usr/sbin/mysqld(srv_start(bool)+0x206e) [0x55d9dfe2666e]
/usr/sbin/mysqld(+0x2428a9f) [0x55d9dfc60a9f]
/usr/sbin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x9e) [0x55d9df8ef17e]
/usr/sbin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x1a9) [0x55d9dfb338a9]
/usr/sbin/mysqld(+0x1234016) [0x55d9dea6c016]
/usr/sbin/mysqld(+0x28dfefa) [0x55d9e0117efa]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x9609) [0x7fe5f2421609]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43) [0x7fe5f1b67293]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 1
Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Janne Ruostemaa
Hi Uday, thanks for the comment. Judging by the error message, there’s a chance either the database has been corrupted or you are experiencing a storage hardware failure. You could try starting the DB in recovery mode or look into restoring from any backups you may have to a new storage device.
Jason
[root@vps-29bfc160 centos]# systemctl status mysqld
● mysqld.service – MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Wed 2021-07-28 16:20:38 UTC; 1h 50min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 18242 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 18214 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 18242 (code=exited, status=1/FAILURE)
Status: “Server startup in progress”
Jul 28 16:20:36 vps-29bfc160.vps.ovh.ca systemd[1]: Starting MySQL Server…
Jul 28 16:20:38 vps-29bfc160.vps.ovh.ca systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jul 28 16:20:38 vps-29bfc160.vps.ovh.ca systemd[1]: Failed to start MySQL Server.
Jul 28 16:20:38 vps-29bfc160.vps.ovh.ca systemd[1]: Unit mysqld.service entered failed state.
Jul 28 16:20:38 vps-29bfc160.vps.ovh.ca systemd[1]: mysqld.service failed.
Janne Ruostemaa
Hi Jason, thanks for the comment. The system status output for your MySQL doesn’t say much on what caused the issue but it seems the main process failed at server startup. If you recently made changes to your database configuration, I’d suggest reverting the changes to see if that resolves the issue. Otherwise, you’ll likely need to check the logs for more clues.
Jaskirat Singh
Hi, I am unable to make the connection from my website to the database. It shows status 200 but data undefined!
Case 1: Although the database is giving response from my local machine to the remote machine!!
Case 2: The database is giving responses from my local Virtual Private Server(VPS) machine to the local Virtual Private Server(VPS) database
Case 3: The issue comes here!! The user is same with same credentials, now the connection has been made but data is undefined always!! I hace tried making changes to bind-address but no sucess!!
Could you please help? Stuck here from past 3 days!
Janne Ruostemaa
Hi Jaskirat, thanks for the comment. Is your database listening to the default port 3306? If not, that could cause applications querying the database to get incorrect responses. Otherwise, it’s likely down to the implementation of your application on how it handles the response.
Intan Salma
Hi, I’m Intan. I already follow all the step. but still get notification that “Your PHP installation appears to be missing the MySQL extension is required by WordPress”. But when I check the status of mysql is active and running.
drwx—— 2 mysql mysql 4096 Oct 19 08:20 wordpress
The problem was come out when i try to access wordpress in web browser.
Can You please help?
Janne Ruostemaa
Hi Intan, thanks for the comment. The error is likely referring to the PHP-MySQL extension package. Depending on your operating system, you could install it with the following command:
You can find out more about the required packages in our LAMP stack tutorial.
mir
cannot store data in mysql database when values given in apache webserver
inda
how to check if the write in table are succes or not because somtime we can’t find the rows on table
Janne Ruostemaa
Hi Mir, thanks for the comment. Try checking your database details and that your webserver is able to connect to the database. If that’s not the issue, then you might need to investigate the way your webserver is submitting the values to the database.
Janne Ruostemaa
Hi Inda, thanks for the question. If you are using PHP, you could use the affected_rows() function to check what effect the previous INSERT, UPDATE, REPLACE or DELETE query had.
Milton
I am not able to start MySQL. Every time when I tried this thing happened to me
9:01:48 PM [mysql] Status change detected: stopped
9:01:48 PM [mysql] Error: MySQL shutdown unexpectedly.
9:01:48 PM [mysql] This may be due to a blocked port, missing dependencies,
9:01:48 PM [mysql] improper privileges, a crash, or a shutdown by another method.
9:01:48 PM [mysql] Press the Logs button to view error logs and check
9:01:48 PM [mysql] the Windows Event Viewer for more clues
9:01:48 PM [mysql] If you need more help, copy and post this
9:01:48 PM [mysql] entire log window on the forums
Samir Haliru
Hi. This looks like an error produced by XAMPP. As suggested by the error message, you’ll need to check the logs in order to determine what is causing MySQL to stop unexpectedly. This guide describes a few methods you can use to try to fix the issue. If that doesn’t work, please reach out to the XAMPP community for further assistance with this.