CRIB is a CentRal Information Base for MySQL, a long time coming project since I worked on it on and off for a few weeks and now I decided it’s time to hand it over to the global MySQL community.

So, what is CRIB?

CRIB is a central database which collects information about all your MySQL instances which you set as clients. It is monitoring in a way, but not the typical number of connections, memory, index usage, table scans, cpu usage and such, but rather consists of a repository where, if you have tens, hundreds or even thousands of clients, you can see where a particular user was created, where a certain database name features or which tables does a database consists of. It also features a script which logs table sizes periodically (customizable) so you can graph disk usage over time and be able to forecast future disk space requirements easily.

Download the latest code with: bzr branch lp:crib

First of all lets create the user used to write to the server (mother instance)


[dcassar@ubt]$ use5151
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.1.51 MySQL Community Server (GPL)


Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license


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


mysql > grant create, insert, alter, update, delete, select, drop, execute, create temporary tables, create routine, alter routine, lock tables on crib.* to "mother"@"%" identified by "teacher";
Query OK, 0 rows affected (0.02 sec)


mysql > exit
Bye
[dcassar@ubt]$

Lets go ahead and install CRIB on both server and client


[dcassar@ubt]$ cd INSTALL
[dcassar@ubt]$ ./crib_install.sh

brought to you by Darren Cassar

Anytime you need to cancel installation just press ( Ctrl + C )

Would you like to setup source and destination or just source?
1. Client and Server
2. New Client
Enter choice (default 2): 1
Installation starting

This section you'll supply the destination (CentRal Information Base) details

Enter CRIB user name (default root): mother
Enter CRIB password (default 'toor'):
Enter CRIB hostname (default localhost): 127.0.0.1
Enter CRIB port (default 3306): 5151

This section you'll supply the client details

Enter client user name (default root): dcassar
Enter client password (default 'toor'):
Enter client hostname (default localhost): dolphin
Enter client port (default 3306): 3331
Installation complete
[dcassar@ubt]$

Now is the time to gather the data from the clients and log it onto the server.


[dcassar@ubt]$ ./populate.sh CONFIG/crib_3331.cnf
[dcassar@ubt]$ ./tablesize.sh CONFIG/crib_3331.cnf

Lets log into the CRIB database on the mother server and see what details we have at our disposal.


[dcassar@ubt]$ use5151
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.1.51 MySQL Community Server (GPL)


Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license


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

mysql > use crib
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
mysql > show tables;
+------------------+
| Tables_in_crib   |
+------------------+
| info_instances   |
| info_object_size |
| info_objects     |
| info_privileges  |
| info_users       |
| version          |
+------------------+
6 rows in set (0.00 sec)

mysql > select * from version;
+----+---------+---------------------+
| ID | VERSION | UPDATED_TIMESTAMP   |
+----+---------+---------------------+
|  1 | 0.1     | 2010-12-09 16:24:05 |
+----+---------+---------------------+
1 row in set (0.00 sec)

mysql > select * from info_instances;
+----+-------------------------------------+------+-----------+---------------+-----------------+------------+--------------+------+-------+
| id | hostname                            | port | server_id | mysql_version | os              | os_version | architecture | bits | notes |
+----+-------------------------------------+------+-----------+---------------+-----------------+------------+--------------+------+-------+
|  1 | dolphin                             | 3331 |  14123561 | 5.1.50        | sun-solaris2.10 | 10         | sparc        |   64 | NULL  |
+----+-------------------------------------+------+-----------+---------------+-----------------+------------+--------------+------+-------+
1 row in set (0.00 sec)

From the above you realize you can identify all versions used in your setup, what hardware you are using, port numbers used and much more!

Lets see what users we have so far.

mysql > select * from info_users;
+----+-------------+------------+---------------------------+-------------------------------------------+
| id | instance_id | username   | hostname                  | password                                  |
+----+-------------+------------+---------------------------+-------------------------------------------+
|  1 |           1 | root       | localhost                 | *BA44AFCA02B64C198FAEB3043F47CA3797638975 |
|  2 |           1 | root       | %.dev.domain.com          | *BA44AFCA02B64C198FAEB3043F47CA3797638975 |
|  3 |           1 | root       | 127.0.0.1                 | *BA44AFCA02B64C198FAEB3043F47CA3797638975 |
|  4 |           1 | abc        | %                         | *A4456E2A03EC0EE4E164BFA533443E82691DA163 |
|  5 |           1 | def        | localhost                 | *5162BA4456B42A01FD8DDBB4CDBE46AFAD06C5AB |
|  6 |           1 | ghi        | %                         | *95E6C48AFC85167C37A24130DD4F5FE0F48AB658 |
|  7 |           1 | jkl        | %                         | *7A2D095E620E354F216EE00635E163406AD47392 |
|  8 |           1 | mno        | %                         |                                           |
|  9 |           1 | qpr        | localhost                 | *5162BA4456B42A01FD8DDBB4CDBE46AFAD06C5AB |
+----+-------------+------------+---------------------------+-------------------------------------------+
9 rows in set (0.00 sec)

If we had multiple machines set up as clients then we would have another group with instance_id 2, so on and so forth.

Remember this is an alpha tool and whatever you do with it is your responsibility. You are advised NOT TO set this up on production for now.

VN:F [1.9.22_1171]
Rating: 8.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

CRIB – CentRal Information Base for MySQL, 8.0 out of 10 based on 1 rating

  1. [...] This post was mentioned on Twitter by planetmysql and ガレージ・キッド, Zuissi. Zuissi said: MySQL: CRIB – CentRal Information Base for MySQL: CRIB is a CentRal Information Base for MySQL, a long time comi… http://bit.ly/hFRcrN [...]

  2. [...]CRIB is a CentRal Information Base for MySQL[..]

  3. Blowmyhead says:

    this post see it before on twitter

*