For longest time it had bugged me that there wasn’t an easy way to put “SHOW STATUS”, “SHOW VARIABLES”, or “SHOW PROCESSLIST” variables into queries, so I could easily calculate stuff like max connections percentage. Then MySQL 5.1.9 came out and you could final start to do SELECT statements on information_schema.GLOBAL_STATUS, information_schema.SESSION_STATUS, information_schema.GLOBAL_VARIABLES, information_schema.SESSION_VARIABLES and information_schema.PROCESSLIST.

The STATUS and VARIABLES tables have basically two columns VARIABLE_NAME and VARIABLE_VALUE. These correspond with the “SHOW STATUS” and “SHOW VARIABLES” command’s Variable_name and Value. The PROCESSLIST table has all the same columns as the “SHOW PROCESSLIST” command.

So now with that being said here is a quick little query that returns the Max Connection Limit, Connection Count, and Connect Percentage of max allowed connections.

/* requires MySQL 5.1.19 or higher */

SELECT v.VARIABLE_VALUE as “Max Connections”,
s.processcount as “Connection Count”,
((s.processcount/v.VARIABLE_VALUE)*100) as “% of max connections allowed”
FROM information_schema.GLOBAL_VARIABLES v,
(SELECT (@proc_connect:=count(*)) processcount FROM information_schema.PROCESSLIST) s
WHERE v.VARIABLE_NAME = ‘max_connections’;

So after my MySQL server-id idea. I was asked to come up with a function to Convert IP Address. So here it is.

use test;
DROP FUNCTION IF EXISTS IpToInteger;
DELIMITER //
CREATE FUNCTION IpToInteger ( ipAddress CHAR(15) )
RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN
DECLARE o1,o2,o3,o4 char(3);
DECLARE IpInteger INT UNSIGNED;
SET o1 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 1), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 1 – 1) ) + 1), ‘.’, ” );
SET o2 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 2), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 2 – 1) ) + 1), ‘.’, ” );
SET o3 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 3), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 3 – 1) ) + 1), ‘.’, ” );
SET o4 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 4), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 4 – 1) ) + 1), ‘.’, ” );
SELECT ( o1 << 24 ) + ( o2 << 16 ) + ( o3 << 8 ) + o4 INTO IpInteger;
RETURN IpInteger;
END//
DELIMITER ;

use test;
DROP FUNCTION IF EXISTS IntegerToIp;
DELIMITER //
CREATE FUNCTION IntegerToIp( ipInteger INT UNSIGNED )
RETURNS CHAR(16) DETERMINISTIC
BEGIN
DECLARE o1,o2,o3,o4 INT UNSIGNED;
SET o1 = ( ipInteger >> 24 );
SET o2 = ( ipInteger >> 16 << 16 )-( ipInteger >> 24 << 24) >> 16;
SET o3 = (ipInteger >> 8 << 8 ) – ( ipInteger >> 16 << 16 ) >> 8;
SET o4 = ipInteger – ( ipInteger >> 8 << 8 );
RETURN CONCAT( o1, “.” , o2, “.” , o3, “.”, o4 );
END//
DELIMITER ;

mysql> SELECT IpToInteger ( '10.8.208.30' ); +-------------------------------+ | IpToInteger ( '10.8.208.30' ) | +-------------------------------+ | 168349726 | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT IntegerToIp( 168349726 ); +--------------------------+ | IntegerToIp( 168349726 ) | +--------------------------+ | 10.8.208.30 | +--------------------------+ 1 row in set (0.00 sec) mysql>

Amazon is making it easier, and cheaper, to roll out copies of MySQL for very large websites across its cloud.

On Tuesday, the company announced an update to its Amazon Relational Database Service that will let users create and delete multiple Read Replicas of MySQL instances in minutes, via a point-and-click interface.

Full Story Here

The server-id, is used in replication to give each master and slave a unique identity. For each server participating in replication, you should pick a positive integer in the range from 1 to 2^32 – 1 to act as that server’s ID. To come up with this id convert the IP address of the server to an integer as follows

IP address 10.8.208.31

First Octet: 10
Second Octet: 8
Third Octet: 208
Fourth Octet: 31

(first octet * 256^3) + (second octet * 256^2) + (third octet * 256) + (fourth octet)
server-id = (10 * 16777216) + (8 * 65536) + (208 * 256) + (31)
server-id = 168349727