Installing OurDelta's Google MySQL Tools to cPanel

[Miraenda]

This how-to will show the installation of patches to include these capabilities to your MySQL installation.

First, check your MySQL rpms:

# rpm -qa|grep -i mysql
MySQL-server-5.0.89-0.glibc23
MySQL-bench-5.0.89-0.glibc23
MySQL-client-5.0.89-0.glibc23
MySQL-devel-5.0.89-0.glibc23
MySQL-shared-5.0.89-0.glibc23

Now, remove those rpms:

rpm -e MySQL-server-5.0.89-0.glibc23
rpm -e MySQL-bench-5.0.89-0.glibc23
rpm -e MySQL-client-5.0.89-0.glibc23
rpm -e MySQL-devel-5.0.89-0.glibc23
rpm -e MySQL-shared-5.0.89-0.glibc23

Install the OurDelta MySQL patches:

cd /root/setup
wget http://mirror.ourdelta.org/yum/CentOS/4/i386/RPMS/MySQL-OurDelta-client-5.0.87.d10-65.el4.i386.rpm
wget http://mirror.ourdelta.org/yum/CentOS/4/i386/RPMS/MySQL-OurDelta-devel-5.0.87.d10-65.el4.i386.rpm
wget http://mirror.ourdelta.org/yum/CentOS/4/i386/RPMS/MySQL-OurDelta-server-5.0.87.d10-65.el4.i386.rpm
wget http://mirror.ourdelta.org/yum/CentOS/4/i386/RPMS/MySQL-OurDelta-shared-5.0.87.d10-65.el4.i386.rpm

Please note that your patches might be different RPMs. My system runs CentOS 4 i386. If you run CentOS 5 and/or use x86_64, you would want to search http://ourdelta.org/centos for the right RPMs.

Now that you have the RPMs, you would install them en masse:

rpm -ivh MySQL-OurDelta*

If you get an error about a missing dependency for perl-DBI, grab a copy of it and install that rpm as well (again, please ensure to get the copy of the CentOS version and environment you are using, mine is CentOS 4 and i386 in this example):

wget http://mirror.centos.org/centos/4/os/i386/CentOS/RPMS/perl-DBI-1.40-9.i386.rpm
rpm -ivh perl-DBI-1.40-9.i386.rpm

In /etc/my.cnf, add the following line:

userstat_running = 1

Because cPanel's /scripts/mysqlup will try to override and remove the patched MySQL rpms with others, you have to stop the system from doing MySQL upgrades by editing the /etc/cpupdate.conf file and changing MYSQLUP to never in this line:

MYSQLUP=never

Save the file, then run /scripts/easyapache to ensure that PHP is compiled with the new patched MySQL.

Your server will need to be under MySQL query load to begin generating stats for the show user_statistics command, so you might not have the results available immediately. If you want to force a test, you can run something like the following:

/usr/local/apache/bin/ab -kc 30 -t 30 "http://errorcodex.com/forumdisplay.php?f=6"

The above is a database-driven site on my server, so you would need to pick something similar on your server (i.e., do not use my site url for your tests, use your own). The -t would be the timeout value, which is set to 30 so the test will stop after that timeout value has been reached. When you do have information, it will be retrieved using something like this (please note I've replaced the column names with c1 to c20 to ensure a shorter display with each of the column actual names listed below the table):

mysql> show user_statistics;
+--------------+---+--+----+--+--+-------+--------+--+----+---+----+---+---+---+---+---+---+---+---+---+
|    User      | c1|c2| c3 |c4|c5|  c6   |   c7   |c8| c9 |c10| c11|c12|c13|c14|c15|c16|c17|c18|c19|c20|
+--------------+---+--+----+--+--+-------+--------+--+----+---+----+---+---+---+---+---+---+---+---+---+
| root         |146| 0|1731| 0| 0|  10877|  750197| 0|  21|  4| 194| 22|  7|237| 0 | 0 | 8 | 3 | 8 |  1| 
| admin_admins |  4| 0|   0| 0| 0|     20|       0| 0|   0|  0|   0|  0|  0|  0| 0 | 0 | 0 | 0 | 0 |  0| 
| admin_osc1   |  1| 0|   0| 0| 0|      0|       0| 0|   0|  0|   0|  0|  0|  0| 0 | 0 | 1 | 0 | 0 |  0| 
| error_codex  |388| 0| 343|17|17|1537495|95535206| 0|1333|202|1450|756|382|  0| 0 | 0 | 0 | 0 | 0 | 84| 
| eximstats    | 27| 0|1456| 1| 1|  12983|    3395| 0|   0|273| 298|  7| 72|117| 0 | 0 | 0 | 0 | 0 |  7| 
| cphulkd      | 11| 0|   0| 0| 0|   7709|    7147| 0|  29| 14|  29|  0|  0| 11| 0 | 0 | 0 | 0 | 0 |  0| 
| modsec       |  1| 0|   0| 0| 0|     26|      11| 0|   0|  0|   0|  0|  0|  1| 0 | 0 | 0 | 0 | 0 |  0| 
+--------------+---+--+----+--+--+-------+--------+--+----+---+----+---+---+---+---+---+---+---+---+---+

c1 = Total_connections
c2 = Concurrent_connections
c3 = Connected_time
c4 = Busy_time
c5 = Cpu_time
c6 = Bytes_received
c7 = Bytes_sent
c8 = Binlog_bytes_written
c9 = Rows_fetched
c10 = Rows_updated
c11 = Table_rows_read
c12 = Select_commands
c13 = Update_commands
c14 = Other_commands
c15 = Commit_transactions
c16 = Rollback_transactions
c17 = Denied_connections
c18 = Lost_connections
c19 = Access_denied
c20 = Empty_queries

Enjoy!

[Shackbase]

This looks really interesting - would this work with mySQL 5.0 as well?

Where would I find the RPMs for RH ES 5.0 x64?

Kind Regards,
--Tone

[Miraenda]

Actually, the RH ES are the same as the CentOS 5 x86_64 ones, so it would be these:

http://master.ourdelta.org/yum/CentO...r/x86_64/RPMS/

The set ones you'd want to use would be these:

http://master.ourdelta.org/yum/CentO...el5.x86_64.rpm
http://master.ourdelta.org/yum/CentO...el5.x86_64.rpm
http://master.ourdelta.org/yum/CentO...el5.x86_64.rpm
http://master.ourdelta.org/yum/CentO...el5.x86_64.rpm

The perl-DBI one for CentOS 5 for 64-bit (x86_64):

http://mirror.centos.org/centos/5.5/...el5.x86_64.rpm

I had thought these were only MySQL 5.1 ones, but they are actually MySQL 5.0 ones.

[Shackbase]

can I use the latter list for after I upgrade to 5.1 as well?

Thanks Miraenda!
Tony

[Miraenda]

You wouldn't be able to use that list for MySQL 5.1.

I have to track down the MySQL 5.1 ones for CentOS 5 as the OurDelta site mentions them, but they aren't linked on the CentOS page. It might be tomorrow before I find out as I might have to email them (or ask a friend of mine who uses these who might know where they are located).

[Shackbase]

Originally Posted by Miraenda

before I find out as I might have to email them (or ask a friend of mine who uses these who might know where they are located).

Oh no... I managed to stump you!

I really did not think that would have been possible; but seriously, don't worry about it - I am not entirely sure if this would benefit me too much anyway.

I am a statswhore (pardon the expression) - I just like to know what is happening and where or why and numbers.. gotta love numbers.

Thanks Miraenda
Tony

[Miraenda]

I like stats a lot myself. Can't ever have too many details haha

I'll still try to find out the information as I'd be interested myself in knowing where they are located. Once I do, I'll update the tutorial, since it isn't easy to locate the MySQL 5.1 ones right now.

[Shackbase]

Stats are the bomb; which is why this lil hack spoke to me.

I did just (as you know) upgrade to 5.1 which was surprisingly easy as well - thank you for the help!

Looking forward to trying this one out when you figure out where to get the 5.1 rpms; btw, will this interfere with anything or just add functionality?

Cheers,
Tony

[Miraenda]

The one concern you might have with switching to using these rpms is that you cannot use the cPanel /scripts/mysqlup script anymore (nor will MySQL automatically upgrade), since cPanel uses their own MySQL rpms normally. This means you'd have to upgrade manually by getting new rpms instead. I'm fine by that in order to get this functionality on one of my machines, but a lot of people wouldn't like having to manually install new rpms whenever they want to upgrade MySQL.

[Shackbase]

Yeah... That makes it an iffy upgrade for me - not being part Linux god like you; I do like my automatic and easy one button upgrades and even those make me nervous at times.

Curious though, that is an odd name for a mySQL upgrade - any idea why Google named it OurDelta?

Cheers,
--Tone