Backup existing MySQL data

Make sure to save all existing data just in case there are any issues.

cp -Rf /var/lib/mysql /var/lib/mysql-old
mv /etc/my.cnf /etc/my.cnf-old

Disable the targets so cPanel no longer handles MySQL updates

The following will mark the versions of MySQL we distribute as uninstalled so they are no longer maintained by cPanel/WHM

/scripts/update_local_rpm_versions –edit target_settings.MySQL50 uninstalled
/scripts/update_local_rpm_versions –edit target_settings.MySQL51 uninstalled
/scripts/update_local_rpm_versions –edit target_settings.MySQL55 uninstalled

Remove existing MySQL RPM’s so theres a clean slate for MariaDB

The below command will uninstall the MySQL RPM’s!

/scripts/check_cpanel_rpms –fix –targets=MySQL50,MySQL51,MySQL55

Create a yum repository for MariaDB

vi /etc/yum.repos.d/MariaDB.repo

place the following inside of it depending on the DISTRO (https://downloads.mariadb.org/mariadb/repositories/):

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5.29/centos6-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Remove php from the /etc/yum.conf file then run the following commands

yum install MariaDB-server MariaDB-client MariaDB-devel
/etc/init.d/mysql start
mysql_upgrade
/etc/init.d/mysql restart

Add php back to the /etc/yum.conf file to ensure future php updates don’t get clobbered

Rebuild easyapache/php to ensure modules are intact/working

/scripts/easyapache –build

Sometimes it may happen that while restarting mysql service on server you are getting below error.

mysql is disabled by sys admin

Try running below script to bring mysql up again

/scripts/mysqlup

If it’s Up then ok, but sometime it will give below error

Updates for mysql /etc/mysql disabled

If it is not working then go to /etc folder

cd /etc

Run the below command there

[email protected] [/etc]#ls -ld mysql*

It will give below output

-rw-r–r– 1 root root 0 Aug 16 21:19 mysqldisable

Move that file using below command

[email protected][/etc]# mv mysqldisable mysqldisable-bak

Then restart mysql will fix your issue.

[email protected][/etc]# /scripts/mysqlup

Sometimes, because of the Brute Force Attack we are unable to login to WHM if we are using correct details also. Then at that time try using below method.

Login to server via SSH.

Connect with mysql database using mysql command. It will show below output.

[email protected] [/var/log]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 172123
Server version: 5.1.65-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Then type show databases commands in mysql it will show all databases.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| cphulkd |
| eximstats |
| horde |
| leechprotect |
| logaholicDB_test |
| modsec |
| mysql |
| roundcube |
+——————–+
9 rows in set (0.00 sec)

Then go to cphulkd database using below command.

mysql> use cphulkd;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Now, it’s time to show tables.

mysql> show tables;
+——————-+
| Tables_in_cphulkd |
+——————-+
| auths |
| blacklist |
| brutes |
| good_logins |
| logins |
| report |
| whitelist |
+——————-+
7 rows in set (0.00 sec)

You can take backup of tables using below command

mysql>BACKUP TABLE `brutes` TO ‘/path/to/backup/directory’;

Run below command to check your IP is blocked in CPHulk Brute Froce database. Replace xxx.xxx.xxx.xxx with your IP

mysql> SELECT * FROM `brutes` WHERE `IP`=’xxx.xxx.xxx.xxx’;

Run below command to remove IP from database

mysql> DELETE FROM `brutes` WHERE `IP`=’xxx.xxx.xxx.xxx’;

Also, check IP in login table also by running the below command

mysql> SELECT * FROM ‘logins’ WHERE ‘IP’ =’xxx.xxx.xxx.xxx’;

Remove the IP from login table using below command.

mysql> DELETE FROM ‘logins’ WHERE ‘IP’ =’xxx.xxx.xxx.xxx’;

OR

You can use below method to clear brutes and login tables. Please note it will remove all the IP’s from database.

Now empty some tables by giving the below commands.

mysql> DELETE FROM brutes;
Query OK, 60 rows affected (0.00 sec)

mysql> DELETE FROM logins;
Query OK, 34 rows affected (0.00 sec)

9 rows in set (0.00 sec)

How to Uninstall MySQL Governor

Run the below commands to uninstall MySQL Governor

/usr/share/lve/dbgovernor/install-mysql.py –delete
yum erase db-governor db-governor-mysql

Upgrading MySQL Governor on CPanel, Plesk and DirectAdmin server

Run the below command to update MySQL Governor

yum update db-governor db-governor-mysql –enablerepo=cloudlinux-updates-testing

Run the below command to restart the service

service db_governor restart

MySQL Governor installation in without control panel server

Run the below command to install MySQL Governor in plain server or without control panel server.

yum install db-governor –enablerepo=cloudlinux-updates-testing
yum update mysql-server mysql-devel mysql –enablerepo=cloudlinux-updates-testing

Add the below line in my.cnf with root password

 [dbgovernor]

Run the below command to start.

service db_governor restart

How to install MySQL Governor on CPanel, Plesk and DirectAdmin

MySQL governor is set of utilities to monitor and restrict MySQL usage in shared hosting environment. The monitoring is done via USER_STATISTICS table that is available in CloudLinux version of MySQL as well as via tracking of slow queries.

To install MySQL governor on cPanel server

yum install db-governor –enablerepo=cloudlinux-updates-testing
/usr/share/lve/dbgovernor/install-mysql.py –install

Note :

MySQL on cPanel servers will be updated from CloudLinux RPMs.

MySQL Governor is compatible only with MySQL 5.x

Mysql manager of pid file quit without updating file

While Starting Mysql service getting the below error message.

Mysql manager of pid file quit without updating file

This problem occurs because mysql database crashed.

To repair mysql database use the below command.

cd /var/lib/mysql/
myisamchk -r *.MYI

Then run the below command

ps aux | grep -i mysql

If it is showing in output then kill that process using the below command.

kill -9 PID

How to enable mysql query cache

Please note that i am enabling the mysql query caching limit to 20MB. I have converted the 20MB to 20971520KB

Login to the server via SSH.

Type “mysql” command

It will show the below output.

[email protected] [~]# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 99955 Server version: 5.1.62-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

Then fire the below command in mysql

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;

Then fire the below command to set the mysql query cache.

mysql> SET GLOBAL query_cache_size =20971520;

Then check the cache value again.

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;

Then exit the mysql be pressing Ctrl+D

Open my.cnf file and insert the below line vi /etc/my.cnf

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

save the file and restart mysql

How to install WatchMySQL Plugin in WHM

Installation

Go to the below path

cd /sbin

Remove the file or take the backup

rm -f watchmysql

Download watchmysql plugin using the below link

wget http://www.ndchost.com/cpanel-whm/scripts/watchmysql/download.php


Change the ownership of that file

chown root.root watchmysql


Change permission of the file.

chmod 0750 watchmysql

Configuration

Before you run the script you must first configure it. Use edit to open the watchmysql script and set the options.

#Sets the maximum concurrent connections per MySQL user.


my $max_concurrent_connections = '15';

#Who to tell about this user... (email)


my $warning_email = '[email protected]';

#Kill Abuser's MySQL Connections (0 = no, 1 = yes)


my $kill_user = '0';

#MySQL polling in seconds (600 = 10 minutes)

my $check_interval = '600';

Running

To start this script simply run the following command

/sbin/watchmysql &

If you want the script to start when the system boots you need to add the above line to /etc/rc.local