How to address “Error establishing a database connection” in WordPress

“Error establishing a database connection” is one the  most common error for custom WordPress installation. It could be due to one of the following reasons

  1. MySQL username and password is incorrect in wp-config.php file.
  2. MySQL database is down
  3. MySQL database is corrupted ( Don’t get scared by this reason. This is least likely reason)

MySQL username and password is incorrect in wp-config.php file.

Most likely you will get this error during installation of wordpress on your server or local machine if you have not updated database username and password in wp-config.php.

Please check following section in wp-config.php file.

/** The name of the database for WordPress */
define('DB_NAME', 'DATABASENAME');

/** MySQL database username */
define('DB_USER', 'DATABASENAME_USERNAME');

/** MySQL database password */
define('DB_PASSWORD', 'DATABASENAME_PASSWORD');

/** MySQL hostname */
define('DB_HOST', 'localhost');

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');

 

To avoid getting this error, please follow WordPress installation guide.

MySQL database is down

There could be multiple reasons for database to be done. Simply restart MySQL database using following command

sudo service mysql start

Most likely this will solve your issue. If it does not solve, it will throw following error.

$sudo service mysql start
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

Here is the output of both the commands

$sudo systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: inactive (dead) (Result: exit-code) since Sun 2018-04-29 02:10:42 UTC; 53s ago
Process: 10667 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid (code=exited, status=1/FAILURE)
Process: 10626 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 3812 (code=killed, signal=KILL)

Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: Failed to start MySQL Community Server.
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Unit entered failed state.
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: Stopped MySQL Community Server.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: mysql.service: Start request repeated too quickly.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: Failed to start MySQL Community Server
$ sudo journalctl -xe
-- Unit mysql.service has begun starting up.
Apr 29 02:10:41 ubuntu-512mb-server audit[10666]: AVC apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=10666 comm="apparmor_parser"
Apr 29 02:10:41 ubuntu-512mb-server kernel: audit: type=1400 audit(1524967841.661:66): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=10666 comm="apparmor_parser"
Apr 29 02:10:41 ubuntu-512mb-server mysqld[10667]: Initialization of mysqld failed: 0
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Control process exited, code=exited status=1
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has failed.
-- 
-- The result is failed.
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Unit entered failed state.
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has finished shutting down.

This tells me this is not norma mysql shutdown and I need to check further logs. Use following command after trying MySQL restart.

$sudo tail -30 /var/log/mysql/error.log
2018-04-29T02:15:31.641869Z 0 [ERROR] Failed to initialize builtin plugins.
2018-04-29T02:15:31.641872Z 0 [ERROR] Aborting

2018-04-29T02:15:31.643813Z 0 [Note] Binlog end
2018-04-29T02:15:31.643871Z 0 [Note] Shutting down plugin 'MyISAM'
2018-04-29T02:15:31.644102Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2018-04-29T02:15:32.135955Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-04-29T02:15:32.138090Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.21) starting as process 10937 ...
2018-04-29T02:15:32.141779Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-04-29T02:15:32.141810Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-04-29T02:15:32.141818Z 0 [Note] InnoDB: Uses event mutexes
2018-04-29T02:15:32.141825Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-04-29T02:15:32.141831Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-04-29T02:15:32.141837Z 0 [Note] InnoDB: Using Linux native AIO
2018-04-29T02:15:32.142157Z 0 [Note] InnoDB: Number of pools: 1
2018-04-29T02:15:32.142291Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-04-29T02:15:32.144099Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-04-29T02:15:32.144141Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2018-04-29T02:15:32.144149Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-04-29T02:15:32.144154Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2018-04-29T02:15:32.144161Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2018-04-29T02:15:32.144165Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-04-29T02:15:32.144169Z 0 [ERROR] Failed to initialize builtin plugins.
2018-04-29T02:15:32.144172Z 0 [ERROR] Aborting

2018-04-29T02:15:32.146255Z 0 [Note] Binlog end
2018-04-29T02:15:32.146304Z 0 [Note] Shutting down plugin 'MyISAM'
2018-04-29T02:15:32.146569Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Please note the error statements highlighted in bold.

Root cause.

This error is coming because my system is falling short of RAM memory. Normally when you use server with 512 MB RAM, this kind of issues occur.

Solution

  1. Increase the RAM of the server by upgrading your server capacity
  2. Add swapfile. I solved above problem by adding swapfile. Please refer to this excellent guide to add swap file.

 

MySQL database is corrupted

If your MySQL installation is corrupted, I hope you have some database backup available with you. There are options to recover data. If you have backup data available, best option is to reinstall MySQL. Please follow how to install MySQL on Ubuntu guide.

How to install wordpress on Ubuntu 16.04

Here is quick reference that I created for myselft for creating local wordpress blog installation. Hoep it helps you as well.

  • Installation

Make sure you have following softwares installed on your local machine

  1. MySQL
  2. NGINX
  • Configure MySQL

Create user for wordpress

mysql -u root -p
mysql> CREATE DATABASE localwordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
mysql> GRANT ALL ON localwordpress.* TO 'localwordpresssuser'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> exit;
  • Download  WordPress.

curl -O https://wordpress.org/latest.tar.gz
tar xzvf latest.tar.gz
cp wordpress/wp-config-sample.php wordpress/wp-config.php
mkdir wordpress/wp-content/upgrade
sudo cp -a wordpress  /var/www/wordpress1

After moving WordPress folder to required path, you need to change few folder permissions.

sudo chmod g+w /var/www/html/wp-content
sudo chmod -R g+w /var/www/html/wp-content/themes
sudo chmod -R g+w /var/www/html/wp-content/plugins
sudo chown -R www-data:www-data /var/www/html
  • Configure WordPress

Open wp-config.php file and make following changes

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'localwordpress');

/** MySQL database username */
define('DB_USER', 'localwordpresssuser');

/** MySQL database password */
define('DB_PASSWORD', 'password_here');

/** MySQL hostname */
define('DB_HOST', 'password');

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');

Visit following link for generate AUTH KEY and other remaining fields

https://api.wordpress.org/secret-key/1.1/salt/

simply copy paste values displayed on above link  into config.php file at appropriate location. Here is the sample. (Please don’t copy paste below values into your installation)

define('AUTH_KEY',         'Johd+fp5c.esU?J26hZb8^6Gi GAL+^Abs-{k4%g0G4IEGVKlZ`|MEk4B;W++%s*');
define('SECURE_AUTH_KEY',  '_J}pBRjcek6f+wj*BNF}lPu-xNX$^.+`nIx|*kg-YjY+v)%Qi<J`pI?|zE/BIa<U');
define('LOGGED_IN_KEY',    '2.T%wJpUwsSeZQV?K,R)7$u }yl]rfLt|.gg,uI.SP&U>u_7q+*uH2+Gy4}-AmYC');
define('NONCE_KEY',        '~^T R=oy/Ej`>q8&FP7rPM1vZ%;}*@oz7^b~~>>Clw{.LGYdbDjV$-t<U5/(&;M7');
define('AUTH_SALT',        'Nn?`[;?9=niA=Jun:ikXi(BR%%Y7MYVtMd+,a&_ZKQei3S ;Z8XteX{=f8~~=D~p');
define('SECURE_AUTH_SALT', 'WCRf7Un/.BX9z~_4dt}!-k$<Y02mm=fKEvHo;5{]!s9=w/x/@9-,Q?ib-jMM#47/');
define('LOGGED_IN_SALT',   '$~+K5U>rd3_B#+X*^G2hlBTnD:)W](rer%VMS #G8jJ^f(5Gr@.aF:6`hg~:OkS(');
define('NONCE_SALT',       'xdtH,{ir(-I5|/NIHaD^eFu.pKCIC-5!Gn`YBDq#?bRfhI5,-c,;?^^<6V%P04iD');

Also add FS_METHOD  after “define (‘WP_DEBUG’, false);”. It should look as below

define('WP_DEBUG', false);
define('FS_METHOD', 'direct');
  • Troubleshooting

I got error “Authentication is needed to run ‘/bin/cp’ as the super user” while saving wp-config.php file.

Its not a big deal. Simply go to terminal and run following commands

sudo chmod 777 projectname
sudo chown $USER -R projectname/
  • Configure nginx

  • You need to configure nginx so that typed url will direct to correct installation directory. Following is the sample code. You can copy this as is but make sure you change the server_name parameter to your domain name.Create following file in folder /etc/nginx/sites-available/. You name is as you with but for easy reference, add name of domain in the file name.
    server {
    listen 80;
    listen [::]:80;
    
    root /var/www/wordpress1;
    index index.html index.htm index.nginx-debian.html;
    
    server_name localwordpress1.com www.localwordpress1.com;
    
    location / {
    #try_files $uri $uri/ =404;
    # original content
    try_files $uri /index.php$is_args$args;
    #try_files $uri $uri/ /index.php$is_args$args;
    }
    
    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
    location ~ \.php$ {
    include snippets/fastcgi-php.conf;
    
    # With php7.0-cgi alone:
    #fastcgi_pass 127.0.0.1:9000;
    # With php7.0-fpm:
    fastcgi_pass unix:/run/php/php7.0-fpm.sock;
    }
    
    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #
    #location ~ /\.ht {
    # deny all;
    #}
    }

    copy this file to sites-enabled folder using following command and restart nginx

    $sudo ln -s /etc/nginx/sites-available/wordpress1.com /etc/nginx/sites-enabled/
    $sudo nginx -t
    $sudo systemctl restart nginx

    Considering we need to access local installation on custom url, that url need to point to localhost. This needs to be changed in host file.

    sudo nano /etc/hosts
    127.0.1.1 localwordpress1.com
  • Access your installation # localwordpress1.com
install wordpress ubuntu 16.04
install wordpress ubuntu 16.04

MySQL Quick Reference

Here is the list of commands for MySQL that will be evry handy while using MySQL. Over the period of time, you will remember these commands but to get started, you can use this as a cheat sheet for MySQL commands.

Connecting to MySQL

  • Check status of MySQL
$ systemctl status mysql
  • Start and Stop MySQL Server using
$ sudo systemctl start mysql
$ sudo systemctl stop mysql
  • Start MySQL without admin privileges
sudo mysqld_safe --skip-grant-tables --skip-networking &  
  • Login to MySQL Database from command line. Once authentication is completed successfully, mysql> prompt will appear.
$mysql -u USERNAME -p PASSWORD

OR

$ mysql --host=localhost --user=USERNAME --password PASSWORD

If you want to connect to any remote MySQL server, replace ‘localhost’ with remote db address name and add port parameter.

$ mysql --host=remote.example.com --port=13306 --user=USERNAME --password PASSWORD

Commands to be used in mysql> prompt:

Use following command to get list of databases

mysql> show databases;

Use following command to create database

mysql> create database <newDatabaseName>;

Create new user

mysql>CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

grant access to newly created user

mysql> grant create, delete, insert, select, update, alter,references ON <databaseName>.* TO 'usernmae'@'localhost';

Use following command to delete or drop  database

mysql> drop database DatabaseName;

Use following command to get list of users

mysql> SELECT User FROM mysql.user;

 

To check the access level of the user

mysql>SHOW GRANTS FOR 'root'@'localhost';

To use specific database name use following command

mysql>use DBNAME

To export database

$mysqldump -u [username] -p [databaseName] > [databaseName].sql

To import database

$mysql -u [username] -p newdatabase < [databaseName].sql

 

How to migrate Ghost blog from SQLite3 To MySQL

Migrating Ghost blog from SQLite3 to MySQL is fairly simple process. You need to export your posts, configure MySQL for Ghost and lastly, you need to import previously exported posts and you are done. Please follow below mentioned steps.

Step#1: Exporting the Ghost Blog posts.
Login to ghost blog as admin using following link
http://yourblog.com/ghost/ OR http://yourblog.com/admin

Go to labs option and clock on export.

It will create a json file. Keep this file handy as we will import same file after we have configured MySQL.

Step#2: Install MySQL
Please ensure MySQL is installed on your machine. You can follow how to install MySQL on ubuntu.

Step#3: create user for ghost blog
Login to MySQL as root using following command. It will ask for root password.

$ mysql -u root -p

Create database as well as user by using following commands

mysql> create database ghost;  
mysql> CREATE USER 'ghost'@'localhost' IDENTIFIED BY 'password';  

Now add grant user to database

mysql> grant create, delete, insert, select, update, alter,references ON ghost.* TO 'ghost'@'localhost';  

and now flush the newly added access.

mysql> flush privileges;  

Step#4: edit config file to use MySQL
Configure to use production database as mentioned in this post.
Your final config file snippet is as below:

        database: {
            client: 'mysql',
            connection: {
                host: 'localhost',
                user: 'username',
                password: 'password',
                database: 'ghost',
                charset: 'utf8'
            },
            debug: true
        },

Step#4: Now run your blog in production mode as mentioned in post using ghost in production and development mode.

$ npm start --production

Step#5 Importing previously exported blog dump.
Again Login to ghost blog as admin using following link
http://yourblog.com/ghost/ OR http://yourblog.com/admin

Go to labs option and clock on import and use the previously exported file.

This worked well for me, please let me know if you run into any issues…

Recommendations.
Having seen that, I would recommend using SQLite3 as a database someone has said, don’t fix it until its broken and considering SQLite can handle 100k hits per days (which cover most of the websites) there is no need to create extra work for yourself by moving database to MySQL. However you must note that, GhostPro uses MySQL database and ghost officially has dropped support for PostgreSQL.

Please refer to SQLite documentation for further clarification.

Configure Ghost Blog using MySQL

By default Ghost uses sqlite3 database. If you want to use MySQL, this can be done by changing configuration config.js. Please follow changes suggested in below post to use MySQL in Ghost blog.

Ghost blog configuration is as below:

        database: {
            client: 'sqlite3',
            connection: {
                filename: path.join(__dirname, '/content/data/ghost.db')
            },
            debug: false
        },

This needs to be changed to following

        database: {
            client: 'mysql',
            connection: {
                host: 'localhost',
                user: 'username',
                password: 'password',
                database: 'ghost',
                charset: 'utf8'
            },
            debug: true
        },

You can make this change in production section of configuration and keep development section as is. You need to ensure that you start Ghost server in production by following command

npm start --production  

Using MySQL workbench

MySQL Workbench provides DBAs and developers an integrated tools environment for:

  • Database Design & Modeling
  • SQL Development
  • Database Administration
  • Database Migration
  • Installation:

Download installation package as per our OS from following link
https://dev.mysql.com/downloads/workbench/

and follow normal installation process.

Alternative way.
If you are getting any error while install by first method, you can use following commands to get it installed

sudo apt-get install mysql-workbench  

Once installation is done, you will find MySQL Workbench in your list of applications.

When you open MySQL workbench, following screen will appear.

After you connect to local server, you will see following window.

Install MySQL server ubuntu

MySQL is an open-source relational database management system (RDBMS). I had always prepferred PostgreSQL but there are some reasons you can not avoid MySQL (more on this later). Here are the following steps I followed to get started with MySQL on ubuntu.

Step#1
Download deb package that handles configuring and installing the official MySQL software repositories. You can download this package manually from following link
https://dev.mysql.com/downloads/repo/apt/

Alternatively, you can download this using curl

curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.3-1_all.deb  

We need to pass two command line flags to curl. -O instructs curl to output to a file instead of standard output. The L flag makes curl follow HTTP redirects, necessary in this case because the address we copied actually redirects us to another location before the file downloads.

Step#2
Install this file using following command

sudo dpkg -i mysql-apt-config_0.8.3-1_all.deb  

You will get following window. Select ok. First and second options are already selected.

The package will now finish adding the repository. Refresh your apt package cache to make the new software packages available:

sudo apt-get update  

Step#3 Install MySQL
Now you can install MySQL using following command.

sudo apt-get install mysql-server  

It will ask for confirmation, Type y then ENTER.
You will be asked to set a root password during the configuration phase of the installation. Be sure to choose a secure password, enter it twice, and the process will complete.

During installation, you will be prompted for root password.

MySQL should be installed and running now. You can check the status MySQL using systemctl command:

systemctl status mysql  

Step#4 Securing MySQL (Optional)
You can secure your MySQL database using following command

$ mysql_secure_installation

If you runinto any issues try reinstalling MySQL.

$ sudo apt-get remove -y mysql-*
$ sudo apt-get purge -y mysql-*
$ sudo apt-get purge mysql-server mysql-client 
$ mysql-common mysql-server-5.7
$ sudo apt-get autoclean
$ sudo apt-get update && sudo apt-get upgrade

once done install MySQL using following command

$ sudo apt-get install mysql-server-5.7

Please not suffice -5.7 at the end. I am not sure why but it works with this prefix.