MySQL service overloads was something quite common on shared web hosting enviroments before Cloudlinux appear in the web hosting market.
Previous to this, a poorly written PHP script could cause dissasters in a server while making SQL querys against the MYSQL service.
What is MySQL Governor?
Luckly we have MySQL Governor, a fantastic CloudLinux feature that can help you to monitor MySQL usage, and at the same time prevent users from overloading your shared hosting server by limiting abusers.
MySQL Governor tracks system usage per user (CPU and I/O) and if found, it throttles the MySQL queries. dbtop tool is a CL package that will let system administrators look into what is happening at MySQL level for each system user, pretty much like the classic mytop unix utility.
It’s 100% compatible with MySQL 5.1 to 5.6 and it even supports the high performance MariaDB from 5.x to 10.x.
MySQL Governor can be installed and is fully compatible with cPanel, DirectAdmin, Plesk, ISPManager, InterWorx, and even with plain based servers (without any control panel).
While this instructions should work for any of the mentioned type of servers, this were tested on a cPanel based box.
You must also be alware that after this, your MySQL / MariaDB daemon will be updated from the official CloudLinux repos.
Now that you know how it works, let’s see how to install MySQL governor on cPanel servers.
Backup your current MySQL / MariaDB databases and tables
Before making any changes to your system make sure you have updated full backups of all your databases and system tables. You can run this command to make a full backup of all your MySQL databases and user privileges:
mysqldump --all-databases > /root/all_databases.sql
Install MySQL Governor step by step
If you are running MySQL Server
Remove previous packages if installed
yum remove db-governor db-governor-mysql
Install the governor-mysql package
yum install governor-mysql -y
Now run this python script to complete the installation
/usr/share/lve/dbgovernor/mysqlgovernor.py --install
If you are running MariaDB, use this commands instead
yum install governor-mysql
On the next command, make sure you replace “mariadbXX” with the correct MariaDB version you are running (55 for MariaDB v5.5, 100 for MariaDB v10.0 and 101 for MariaDB v10.1)
/usr/share/lve/dbgovernor/db-select-mysql --mysql-version=mariadbXX /usr/share/lve/dbgovernor/mysqlgovernor.py --install
That’s all, now your CloudLinux system also uses MySQL governor to monitor and limit your MySQL script abusers.
MySQL Governor configuration
MySQL Governor configuration can be found at this location:
/etc/container/mysql-governor.xml
However, before editing its configuration manually we suggest you to use the official dbctl utility. Once you are satisfied with the governor configuration, you must restart the system service in order to apply the changes:
service db_governor restart
Further reading.