User Databases Missing from cPanel

After upgrading to cPanel 11.28, a number of our users indicated that databases were missing from their cPanels, despite the actual databases existing on the server. There are a number of things that can cause this, including corrupted Perl modules and MySQL not running, but the major cause that I’ve seen is from the new database mapping functionality.

Disk Space for MySQL Databases in cPanel Show as 0MB

When you upgrade from cPanel 11.24 to 11.25, your users may notice that in cPanel, their MySQL databases show 0MB of disk space used, when they know their databases are much larger. This is due to an option in cPanel that you specifically have to enable. To enable MySQL disk usage tracking:
1) Edit /var/cpanel/cpanel.config
Change:
disk_usage_include_sqldbs=0
to
disk_usage_include_sqldbs=1
Then run the following:
/scripts/update_db_cache
This may take a few minutes if you have a ton of users with databases, but after this, you should see the database disk usage show up accurately in cPanel.

Roundcube: MySQL or SQLite?

cPanel 11.25 introduces a new feature: The ability to have RoundCube use SQLite instead of MySQL. After benchmarking resource usage and performance, I’ve come to the conclusion that SQLite is definitely the best way to go for Roundcube. Having trou
ble making the decision? This article may help clear things up for you.

Restoring Database Privileges

I’ve seen several cases in the last few months where database users seemingly “disappear” or privileges appear to become invalid when restoring MySQL data or an entire server from raw backups. This may commonly be attributed to a MySQL version mismatch, for example, when a server is running MySQL 5.0 and then is suddenly running MySQL 5.1 or higher (or data is directly restored to a server running such), without having the database tables updated.

How to Change the Location of MySQL on cPanel

There may be some situations where you have to move the location of MySQL, for example, if you’re out of disk space or perhaps looking to host it on another device to increase performance. Whatever the reason, moving MySQL is simple and has no impact ct on cPanel’s functionality.

Upgrading or Downgrading MySQL

Note:  These instructions are intended for versions 11.34 and earlier.
You can easily change the major version of MySQL running on your server, keeping in mind that the actual version will be dependent on what cPanel has released in their repository. You may notice that in WHM > Tweak Settings, where MySQL upgrades and downgrades are usually done, you can no longer downgrade to versions under 5.1, nor (at the time of this writing) can you choose MySQL 5.5. Luckily cPanel gives you a way to get around this.

How To Reset MySQL password

1. Stop MySQL server
# /usr/local/etc/rc.d/mysql-server stop
2. Start MySQL server  with skip grant table mode
# mysqld_safe –skip-grant-tables &
Output:

How To Export MySQL data to CSV file

In this article I'll show you how to export MySQL database to a CSV file.
1. Export MySQL data to CSV file using a simple "SELECT" statement

If you want to export your mysql table into a csv file you need to run the following command:
# mysql -u username -ppassword database -e "SELECT * INTO OUTFILE '/tmp/filename.csv'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'  LINES TERMINATED BY '\r\n' FROM table;"

UNIX Tools: mytop – MySQL Monitoring

MyTop is a console-based tool for monitoring the threads and overall performance of a MySQL server. It runs on most Unix systems. MyTop is a top clone for MySQL Server.
To install MyTop under FreeBSD, enter:

1	make install clean -C /usr/ports/databases/mytop

Convert MyISAM Tables to InnoDB

The InnoDB engine is superior. There is no reason to run MyISAM anymore. Here is a Bash script that will convert all tables, that can be converted, to InnoDB. If you do not have access to a script shell, you can do the same using phpMyAdmin or other MySQL management tools. Be sure to take a backup of the database before running this script.

Restoring Mysql Database Privileges

Run the following command to dump the privilege table into a MySQL syntax that can be easily imported:

InnoDB database recovery

If mysql shutdown unexpectly, innodb might crashed and not possible to
repair through phpmyadmin or mysql konsole.
Solution:

Cannot add domain in ENSIM:: Failed to add site to database. DBERR: error ‘ERROR: duplicate key violates unique constraint “domain_key” ‘ in ‘ INSERT INTO siteinfo

You might get this following error while adding new doman in ensim control panel.
Failed to add site to database. DBERR: error ‘ERROR: duplicate key violates unique constraint “domain_key” ‘ in ‘ INSERT INTO siteinfo (site_id, domain, admin_user, email) VALUES (8, ‘prajith.in’ ‘prajith’, ‘xxx@domain.com’)’
To sort this error just follow following simple step:

Install MariaDB in cPanel/WHM

MariaDB is “An enhanced, drop-in replacement for MySQL”. Below you will find some steps on how to affectively stop cPanel/WHM from maintaining MySQL and begin utilizing MariaDB  for any and all database activity on your server but please note that,  cPanel/WHM no longer maintains MySQL on your system it is on the Systems Administrator to manage and maintain any updates and maintenance on the database software.  We recommend only experienced systems administrators perform the above and we are not responsible for any possible data loss.