Connecting to Mariadb/MySQL RDS from phpMyAdmin

The Amazon RDS ( AWS RDS ) – Managed relational database service from aws is a real blessing these days. Since its managed by aws, you don’t have to worry about the patching / administration / backups / restore aspects of it. All you need to do is upload the codes into it and make sure the connection between ec2 ( web-servers in this case ) and RDS is all good.

But, what if you want to manage the tables / dbs of the rds ? There is no way in which you can login into it directly and execute any codes.

You can make use of phpMyAdmin to login to the rds and manage it.

1) Install phpMyAdmin in the ec2-server ( Based on the linux distro it varies ) On a centos release, it comes with the EPEL Repository.

yum install epel-release ( installing the repo )
yum install phpmyadmin

2) Once installed, open the conf file at /etc/httpd/conf.d/phpMyAdmin.conf

You will find parameters which specify Require ip and Allow from which would be default to

3) Find your local public IP and replace the localhost entires with the public IP.

4) Restart the httpd service and make sure you can access phpMyAdmin at http://serverip/phpmyadmin.

If Step 3 is not done properly, you will get a 403 Forbidden error.

With this, the installations steps are complete, however, logging now to phpMyAdmin would give you only the option to manage the dbs on the local server now. To have the choice to connect to the RDS do the following :

1) Open the File – /etc/phpMyAdmin/ ( on centos this would be the path )

2) Find the section –

* End of servers configuration

Just above it, type in the following :

$cfg['Servers'][$i]['host'] = ''; ( rds endpoint )
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['verbose'] = 'hostname'; ( hostname of the ec2 )
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = TRUE;

Save the config file. Now when you login to http://serverip/phpmyadmin/ you will get a dropdown to select the server, instead of localhost, select the server which we just added.

In case the above dropdown to select the server is not showing up, it would be a case of missing permissions / ownership.

Make sure the /etc/phpMyAdmin/ folder has the permission – 755
and the /etc/phpMyAdmin/ file – 644

Note : The above was done on a virtualmin centos7 server.


Installing MySQLnd in your cPanel server !

You might want to install the the MySQL native driver for PHP (MySQLnd) over the custom MySQL driver provided with the default EasyApache.

To enable this manually, you will need to edit the file :

/var/cpanel/easy/apache/rawopts/all_php5 and make sure the following entries are in place there :


Save the above file, run EasyApache from WHM and recompile as normal.


Fix – “Cannot load from mysql.proc, The table is probably corrupted”

For someone who upgraded MySQL from versions 5.0.x to something like 5.5.x, this error might be a familiar one :

“Cannot load from mysql.proc. The table is probably corrupted”

The database activities would not work as expected and hence you will need to fix this error at the earliest.

The recommended solution from MySQL is to run the mysql_upgrade script which examines all tables in all databases for incompatibilities with the current version of MySQL Server.

mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

You can do this by :

# mysql_upgrade -u root

If you are using a cPanel based system, just run

# mysql_upgrade

To force the check, run

mysql_upgrade --force

Does this result in an error ? “”FATAL ERROR: Upgrade failed””

Look for the logs when this happens and try to spot if some invalid db folders are getting listed during the upgrade.

It might be something like :

mysqlcheck: Got error: 1102: Incorrect database name ‘#dbname#.bk’ when selecting the database

The above error indicates you have an invalid db under the mysql dbs folder, usually – /var/lib/mysql.

Navigate to /var/lib/mysql and find if there are any folders with any extension ( keep only valid db folders and move everything else, like backed up files or so from there )

Once this is complete, re-run the mysql_upgrade script !


MySQL databases not showing up space in cPanel

When trying to view the databases from the cPanel interface of an account, you might end up with no values at all. However, if you check the backend, you can find that these database folders consumes space.

To fix this issue, SSH to the server and open the file :  /var/cpanel/cpanel.config and make sure the variable disk_usage_include_sqldbs is set to 1 ( instead of 0 )

Once that change is made, hit the following from the shell :

# /scripts/update_db_cache

This should fix the issue !


MySQL server not starting – Part 4 !

When trying to restart the MySQL service, do you happen to find the following error in the logs ?

/usr/sbin/mysqld: Can’t create/write to file ‘/tmp/xx’ (Errcode: 122)
date InnoDB: Error: unable to create temporary file; errno: 122
date [ERROR] Can’t init databases
date [ERROR] Aborting

Under usual circumstances, this error can come up, when the permission of /tmp is inappropriate. The right one should be :

drwxrwxrwt  4 root root size date /tmp

( ie, it should be equivalent to # chmod 1777 /tmp )

You make sure this is the right one set for /tmp, you ensure that disk space aint full, still do you face this issue ?

– If so, check for the number of files and nature of them in /tmp. Delete unnecessary/unwanted hidden/temporary files and restart the service. This should fix it.


MySQL server not starting – Part 3

A common issue when MySQL versions are upgraded is the presence of depreciated commands in /etc/my.cnf that would prevent MySQL from restarting.  Other than the presence of depreciated ones, incomplete commands too can cause the issues.

When MySQL is upgraded to 5.5 from 5.1 or so, you might see that the service doesnt get restarted. Looking at the configuration file at /etc/my.cnf do you find something like this ?


Here the path is not referenced, which is the issue confronted. Usually if slow_query_log is enabled in the configuration, then the path to log file should be mentioned, like :


Either comment off the incorrect line or if you are depending on slow_query_log mention the path to the log file as shown above. Once this is given, your service would restart fine.


MySQL server not starting – Part 2

There are ton’s of causes for which MySQL might not start, ranging from disk space full ( highlighted here ) to databases getting corrupt.

First place where you have to check for a clue is the .err log
– ( /var/lib/mysql/hostname.err )

If the err corresponds to something like this :

InnoDB: Page lsn 0 40542, low 4 bytes of lsn at page end 40542
InnoDB: Page number (if stored to page already) 47,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 12
InnoDB: Also the page in the doublewrite buffer is corrupt.
InnoDB: Cannot continue operation.
InnoDB: You can try to recover the database with the my.cnf
InnoDB: innodb_force_recovery=X

One of the reason for this error is the use of multiple storage engines, MyISAM or InnoDB

Check your /etc/my.cnf for any lines which highlight the use
of multiple storage engines.

Following can be an example :


The above configuration implies MyISAM is the default storage engine, but another setting related to innoDB is already given, which conflicts.

If your default storage engine is MyISAM, then giving the following option in /etc/my.cnf would fix the issue :


Once the configuration file is edited, restart the service to save the changes

Note : Mostly applies to MySQL versions < 5.5.x

Memory drain issues on MySQL 5.6 ?

With the recent upgrade of WHM/cPanel, the users get the ability to upgrade their MySQL server to 5.6.x ( x > 6 ).

However, when this upgrade is done, lot of server owners are seeing memory drainage issues. An idle MySQL server tends to consume around 50% of your RAM, which is a very serious concern.  I had to upgrade a personal server of mine and faced serious issues with memory drainage. A more dig on this issue, highlighted a change brought around in the latest versions of MySQL with the parameter ‘performance_schema‘.

Starting from MySQL 5.6.6, this parameter performance_schema is enabled by default and it consumes the server memory even at an idle state. Performance Schema automatically sizes the values of several of its parameters at server start-up if they are not set explicitly, which causes the memory usage to spike up.

The workaround for this issue is to disable performance_schema. This can be done by adding the following value to the configuration file – my.cnf

performance_schema = 0

Add this line and restart MySQL server. Things should be fine from now 🙂

Note : When you try to upgrade MySQL to 5.6.x, from a VPS with 1GB of RAM provisioned you will need to edit the config file and pass the keyword to disable performance_schema ( Yes, before the upgrade ). If not, there are chances for your upgrade to fail partly, due to MySQL upgrade script installing MySQL server components each and it gets killed due to over-usage of RAM as performance_schema is enabled by default.


MySQL server not starting !

When trying to start MySQL server, are you facing this error :

“Timeout error occurred trying to start”

Check for the MySQL logs to see if you can track something.

# /var/log/mysqld.log or /var/lib/mysql/hostname.err

( whichever is the log location ) and see if you can trace :


[ERROR] /usr/libexec/mysqld: Disk is full writing ‘ (Errcode: 122). Waiting for someone to free space… Retry in 60 secs


Check for the server disk-space # df -h.

Clear any unwanted logs/files and make sure there is enough free space and restart your service.