MySQL Preacher

I would like to start off by excusing myself for having had a broken link on http://www.securich.com/downloads.html when I published the latest blog post about Securich.

The tool is downloadable from there and anyone can use it for free in accordance to GPLv2.

I wanted to throw out tutorial about how to install it and use it (Note this tutorial is for version securich version 0.1.2):

Steps:
1. Download it,
2. Install it,
3. Create a role named 'role1' having privileges: select insert update
4. Check roles,
5. Check role privileges,
6. Create a first user john@machine.domain.com (granting privileges on a whole database employees apart from one table),
7. Create a second user paul@10.0.0.2 (granting privileges on all tables in world having word Country in them),
8. Create a third user peter@localhost (granting privileges on the database test),
9. Check user privileges for (paul),
10. Update role created above and see changes (add delete to role 1),
11. Update password (for paul) and see changes,
12. Clone user paul to judas,
13. Check user privileges
14. Check user,
15. Rename user judas to james,
16. Revoke privileges from third user disconnecting any existing connections from that user (useful if a security breach is suspected or if you are a security paranoid thus wanting to make sure the person you are blocking out won't have any more access as from that point onwards).

1. Go to www.securich.com downloads page and download the install script
2. Untar the install script and run it using ./securich_install.sh and it'll install everything automatically

dcassar@ubuntu:~/Desktop$ ./securich_install.sh
Enter version number: 0.1.1
Which kind of installation would you like to do?
1. Install from file on disk
2. Download and install (recommended)
Enter choice (default 2):

Installation starting
–2009-06-19 16:27:56– http://www.securich.com/downloads/securich.0.1.1.tar.gz
Resolving www.securich.com… 64.202.163.10
Connecting to www.securich.com|64.202.163.10|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 29217 (29K) [application/x-tar]
Saving to: `securich.0.1.1.tar.gz'

100%[=====================================================================================================>] 29,217 64.7K/s in 0.4s

2009-06-19 16:27:59 (64.7 KB/s) – 'securich.0.1.1.tar.gz' saved [29217/29217]

Enter mysql root Password (default ):
Enter mysql Hostname/IP (default 127.0.0.1): localhost
Enter mysql Port (default 3306): 3306
Installation complete

3. #log into mysql
use securich;
call create_update_role('role1','select');
call create_update_role('role1','insert');
call create_update_role('role1','update');
4. call check_roles();
5. call check_role_privileges('role1');
6. call grant_privileges('john' , 'machine.domain.com' , 'employees' , '' , 'alltables' , 'role1' , 'john@domain.com');
call revoke_privileges('john' , 'machine.domain.com' , 'employees' , 'salaries' , 'table' , 'role1' , 'N');
7. call grant_privileges('paul' , '10.0.0.2' , 'world' , '^Country' , 'regexp' , 'role1' , 'paul@domain.com');
8. call grant_privileges('peter' , 'localhost' , 'test' , '' , 'all' , 'role1' , 'peter@domain.com');
9. call check_full_user_entries('paul');
10. call create_update_role('role1','delete');
call check_full_user_entries('paul');
11. call set_password('paul' , '10.0.0.2' , 'password123');
12. call clone_user('paul' , '10.0.0.2' , 'judas' , '10.0.0.2' , 'judas@domain.com');
13. call check_full_user_entries('judas');
14. call check_user_privileges('judas' , '10.0.0.2' , 'world' , 'role1');
15. call rename_user('judas' , 'james' , 'james@domain.com');
16. call create_update_role('role2','execute');
17. call grant_privileges('peter' , 'localhost' , 'securich' , 'my_privileges' , 'storedprocedure' , 'role2' , 'peter@domain.com');

18. #connect to mysql using thirduser peter in another session
show databases;
use securich;
show tables;
call my_privileges('test');
show processlist;

19. call revoke_privileges('peter' , 'localhost' , 'test' , '' , '' , 'role1' , 'Y');

20. #as user peter again from 2nd open instance run
show processlist;

dcassar@ubuntu:~/Desktop$ ./securich_install.sh
Enter version number: 0.1.1
Which kind of installation would you like to do?
1. Install from file on disk
2. Download and install (recommended)
Enter choice (default 2):

Installation starting
–2009-06-19 16:27:56– http://www.securich.com/downloads/securich.0.1.1.tar.gz
Resolving www.securich.com… 64.202.163.10
Connecting to www.securich.com|64.202.163.10|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 29217 (29K) [application/x-tar]
Saving to: `securich.0.1.1.tar.gz'

100%[=====================================================================================================>] 29,217 64.7K/s in 0.4s

2009-06-19 16:27:59 (64.7 KB/s) – `securich.0.1.1.tar.gz' saved [29217/29217]

Enter mysql root Password (default ):
Enter mysql Hostname/IP (default 127.0.0.1): localhost
Enter mysql Port (default 3306): 3306
Installation complete
dcassar@ubuntu:~/Desktop$ mysql -u root -p -h 127.0.0.1 -P 3306 Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 429
Server version: 5.1.33 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use securich;
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> call create_update_role('role1','select');
Query OK, 0 rows affected, 5 warnings (0.03 sec)

mysql> call create_update_role('role1','insert');
Query OK, 0 rows affected (0.04 sec)

mysql> call create_update_role('role1','update');
Query OK, 0 rows affected (0.04 sec)

mysql> call check_roles();
+—-+——-+
| ID | ROLE |
+—-+——-+
| 1 | read |
| 2 | write |
| 3 | role1 |
+—-+——-+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call check_role_privileges('role1');
+———–+
| PRIVILEGE |
+———–+
| INSERT |
| SELECT |
| UPDATE |
+———–+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call grant_privileges('john' , 'machine.domain.com' , 'employees' , '' , 'alltables' , 'role1' , 'john@domain.com');
+——————————————————————————————–+
| USER_PASSWORD |
+——————————————————————————————–+
| Password for user — john — contactable at — john@domain.com — is — bfcbd8234d9eb44 — |
+——————————————————————————————–+
1 row in set (0.16 sec)

Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql> call revoke_privileges('john' , 'machine.domain.com' , 'employees' , 'salaries' , 'table' , 'role1' , 'N');
Query OK, 0 rows affected (0.09 sec)

mysql> call grant_privileges('paul' , '10.0.0.2' , 'world' , '^Country' , 'regexp' , 'role1' , 'paul@domain.com');
+——————————————————————————————–+
| USER_PASSWORD |
+——————————————————————————————–+
| Password for user — paul — contactable at — paul@domain.com — is — bc4ab08785e1be6 — |
+——————————————————————————————–+
1 row in set (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> call grant_privileges('peter' , 'localhost' , 'test' , '' , 'all' , 'role1' , 'peter@domain.com');
+——————————————————————————————-+
| USER_PASSWORD |
+——————————————————————————————-+
| Password for user — peter — contactable at — peter@domain.com — is — 7b3b4746d04b — |
+——————————————————————————————-+
1 row in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> call check_full_user_entries('paul');
+———-+———-+————–+—————–+——-+———–+——-+
| USERNAME | HOSTNAME | DATABASENAME | TABLENAME | ROLE | PRIVILEGE | STATE |
+———-+———-+————–+—————–+——-+———–+——-+
| paul | 10.0.0.2 | world | Country | role1 | INSERT | A |
| paul | 10.0.0.2 | world | Country | role1 | SELECT | A |
| paul | 10.0.0.2 | world | Country | role1 | UPDATE | A |
| paul | 10.0.0.2 | world | CountryLanguage | role1 | INSERT | A |
| paul | 10.0.0.2 | world | CountryLanguage | role1 | SELECT | A |
| paul | 10.0.0.2 | world | CountryLanguage | role1 | UPDATE | A |
+———-+———-+————–+—————–+——-+———–+——-+
6 rows in set (0.01 sec)

Query OK, 0 rows affected, 4 warnings (0.01 sec)

mysql> call create_update_role('role1','delete');
Query OK, 0 rows affected (0.09 sec)

mysql> call check_full_user_entries('paul');
+———-+———-+————–+—————–+——-+———–+——-+
| USERNAME | HOSTNAME | DATABASENAME | TABLENAME | ROLE | PRIVILEGE | STATE |
+———-+———-+————–+—————–+——-+———–+——-+
| paul | 10.0.0.2 | world | Country | role1 | DELETE | A |
| paul | 10.0.0.2 | world | Country | role1 | INSERT | A |
| paul | 10.0.0.2 | world | Country | role1 | SELECT | A |
| paul | 10.0.0.2 | world | Country | role1 | UPDATE | A |
| paul | 10.0.0.2 | world | CountryLanguage | role1 | DELETE | A |
| paul | 10.0.0.2 | world | CountryLanguage | role1 | INSERT | A |
| paul | 10.0.0.2 | world | CountryLanguage | role1 | SELECT | A |
| paul | 10.0.0.2 | world | CountryLanguage | role1 | UPDATE | A |
+———-+———-+————–+—————–+——-+———–+——-+
8 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call set_password('paul' , '10.0.0.2' , 'password123');
Query OK, 1 row affected (0.02 sec)

mysql> call clone_user('paul' , '10.0.0.2' , 'judas' , '10.0.0.2' , 'judas@domain.com');
+———————————————————————————————-+
| USER_PASSWORD |
+———————————————————————————————-+
| Password for user — judas — contactable at — judas@domain.com — is — 70d5b79d80fab04 — |
+———————————————————————————————-+
1 row in set (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> call check_full_user_entries('judas');
+———-+———-+————–+—————–+——-+———–+——-+
| USERNAME | HOSTNAME | DATABASENAME | TABLENAME | ROLE | PRIVILEGE | STATE |
+———-+———-+————–+—————–+——-+———–+——-+
| judas | 10.0.0.2 | world | Country | role1 | DELETE | A |
| judas | 10.0.0.2 | world | Country | role1 | INSERT | A |
| judas | 10.0.0.2 | world | Country | role1 | SELECT | A |
| judas | 10.0.0.2 | world | Country | role1 | UPDATE | A |
| judas | 10.0.0.2 | world | CountryLanguage | role1 | DELETE | A |
| judas | 10.0.0.2 | world | CountryLanguage | role1 | INSERT | A |
| judas | 10.0.0.2 | world | CountryLanguage | role1 | SELECT | A |
| judas | 10.0.0.2 | world | CountryLanguage | role1 | UPDATE | A |
+———-+———-+————–+—————–+——-+———–+——-+
8 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call check_user_privileges('judas' , '10.0.0.2' , 'world' , 'role1');
+———–+
| PRIVILEGE |
+———–+
| DELETE |
| INSERT |
| SELECT |
| UPDATE |
+———–+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call rename_user('judas' , 'james' , 'james@domain.com');
+———————————————————————————————-+
| USER_PASSWORD |
+———————————————————————————————-+
| Password for user — james — contactable at — james@domain.com — is — 85c2fc100d83884 — |
+———————————————————————————————-+
1 row in set (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> call create_update_role('role2','execute');
Query OK, 0 rows affected (0.09 sec)

mysql> call grant_privileges('peter' , 'localhost' , 'securich' , 'my_privileges' , 'storedprocedure' , 'role2' , 'peter@domain.com');
Query OK, 0 rows affected (0.08 sec)

mysql> call revoke_privileges('peter' , 'localhost' , 'test' , '' , '' , 'role1' , 'Y');
Query OK, 0 rows affected (0.15 sec)

mysql>

dcassar@ubuntu:~/Desktop$ mysql -u peter -p7b3b4746d04b -h 127.0.0.1 -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 437
Server version: 5.1.33 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show processlist;
+—–+——-+—————–+——+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——-+—————–+——+———+——+——-+——————+
| 437 | peter | localhost:49022 | NULL | Query | 0 | NULL | show processlist |
+—–+——-+—————–+——+———+——+——-+——————+
1 row in set (0.00 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| securich |
| test |
+——————–+
3 rows in set (0.00 sec)

mysql> use securich;
Database changed
mysql> show tables;
Empty set (0.00 sec)

**** Note that the only privileges peter has on securich is on the stored procedure 'my_privileges' and definitely no tables

mysql> call my_privileges('test');
+———–+
| PRIVILEGE |
+———–+
| DELETE |
| INSERT |
| SELECT |
| UPDATE |
+———–+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+—–+——-+—————–+——+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——-+—————–+——+———+——+——-+——————+
| 437 | peter | localhost:49022 | NULL | Query | 0 | NULL | show processlist |
+—–+——-+—————–+——+———+——+——-+——————+
1 row in set (0.00 sec)

****** – In the meantime the dba revoked rights with terminate live connections from peter@localhost

mysql> show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
ERROR 1045 (28000): Access denied for user 'peter'@'localhost' (using password: YES)
ERROR:
Can't connect to the server

mysql>

I truly hope you enjoyed this run through. I excuse myself it's a tad too long, but I wished to illustrate some of the cool features of this package.

Cheers,
Darren

PS don't forget to check out Securich HERE

Advanced, MySQL, Uncategorized MySQL, mysql roles, mysql security, password, password history, privileges, roles, security

Lately there has been quite some talk about security on MySQL, and I’ve decided to GPL a package I wrote, implmenting Roles on MySQL.
This technology has been available on other databases for quite some time, but hasn’t quite yet made it to MySQL’s feature list and apart from this tool, the only solution I know of is google’s patches for MySQL 5.0.

The name is SECURICH and downloads as well as documentation are available at http://www.securich.com/. The bug list is available at http://www.securich.com/mantis/ (you’ll have to register and log in first). Privileges can be granted on a database or table level with a few combinations like:
1. database as one,
2. all tables in database (useful when you need to grant a bunch of privileges to all tables but a few thus revoking the few later),
3. single tables,
4. stored procedure or
5. tables through the use of regular expression.

The user has the facility to create a role and update it on the fly thus propagating the changes onto already existing users having the role in question. There are also loads of other functionalities available and I urge you to check the documentation on http://www.securich.com/.

A cool feature I like is password history which enables dbas to enforce changing of passwords after a certain period of time.

#################################################################################################################################################
## PLEASE NOTE THAT:
## 1. THIS IS AN ALPHA VERSION,
## 2. YOU SHOULD NOT INSTALL THIS PACKAGE ON A PRE-EXISTING MYSQL WHICH ALREADY HAS PRIVILEGES SET UP.
#################################################################################################################################################
##
## This tool will modify current privileges on the “mysql” database tables and should only be used with freshly installed mysqls.
##
##################################################################################################################################################

Features like block user and unblock user as well as many other functionalities are being developed and more features will be added as time goes by.

I would greatly appreciate your feedback about what you think of the package, features you might wish to include etc (feature requests should be done through http://www.securich.com/mantis/ as well.)

Advanced, Intermediate, MySQL groups, MySQL, password expiry, password history, roles, securich, security

Obtaining ansi SQL from RBR.

Recently I needed to replicate between MySQL and another database technology. You might say, why on earth would you want to do something like that, but believe me there are reasons and definitely not (to go away from MySQL to some other DB technology like Oracle or SQL server). Unsurprisingly there are quite a few different tools to do it from any platform towards MySQL but very few which do it the other way round, just to name a couple: Golden Gate and DSCallards.

Whilst not going into their tools (you can find more information on their websites), HIT from DSCallards needs its software to run on Windows and Golden Gate is an expensive beast which was too much for what I needed, thus I decided to have a look at doing the job myself. Although it might look an overkill to do so, it took me a few hours to find a solution and implement it and a couple more to test it and here is a simple description.

MySQL replication can be SBR (statement based), RBR (row based) or a mixture of both. Now despite the fact that the mixture provides the best performance, it would be the most complicated in order to achieve a home made solution, and SBR in my opinion would have also been a bit of a headache to make sure queries didn’t contain non ansi sql through the use of functions like now(), sysdate() and many others. I therefore decided that RBR would be the option of choice.

Although I wonder how many of you ever read an RBR binlog using `mysqlbinlog mysql-bin.000004`, it would be something like:

#090526 14:09:13 server id 1  end_log_pos 1420  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343353/*!*/;
BEGIN
/*!*/;
# at 1420
# at 1464
#090526 14:09:13 server id 1  end_log_pos 1464  Table_map: `test`.`t2` mapped to number 21
#090526 14:09:13 server id 1  end_log_pos 1498  Write_rows: table id 21 flags: STMT_END_F

BINLOG ‘
+ekbShMBAAAALAAAALgFAAAAABUAAAAAAAAABHRlc3QAAnQyAAID/gL+AQM=
+ekbShcBAAAAIgAAANoFAAAQABUAAAAAAAEAAv/+AQAAAA==
‘/*!*/;
# at 1498
#090526 14:09:13 server id 1  end_log_pos 1567  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343353/*!*/;
COMMIT
/*!*/;
# at 1567
#090526 14:09:38 server id 1  end_log_pos 1635  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343378/*!*/;
BEGIN
/*!*/;
# at 1635
# at 1679
#090526 14:09:38 server id 1  end_log_pos 1679  Table_map: `test`.`t2` mapped to number 21
#090526 14:09:38 server id 1  end_log_pos 1733  Update_rows: table id 21 flags: STMT_END_F

BINLOG ‘
EuobShMBAAAALAAAAI8GAAAAABUAAAAAAAAABHRlc3QAAnQyAAID/gL+AQM=
EuobShgBAAAANgAAAMUGAAAQABUAAAAAAAEAAv///AQAAAABZPwCAAAAAWT+AQAAAP4CAAAA
‘/*!*/;
# at 1733
#090526 14:09:38 server id 1  end_log_pos 1802  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343378/*!*/;
COMMIT

Now that isn’t the most readable text you ever seen right? As Giuseppe said “This is more difficult to read than ancient Etruscan. If you are a DBA, you curse and look for help.” But the replication guys at mysql created a nice -v for us to add to mysqlbinlog thus issuing `mysqlbinlog -v mysql-bin.000004` would result in the following:

#090526 14:09:13 server id 1  end_log_pos 1420  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343353/*!*/;
BEGIN
/*!*/;
# at 1420
# at 1464
#090526 14:09:13 server id 1  end_log_pos 1464  Table_map: `test`.`t2` mapped to number 21
#090526 14:09:13 server id 1  end_log_pos 1498  Write_rows: table id 21 flags: STMT_END_F

BINLOG ‘
+ekbShMBAAAALAAAALgFAAAAABUAAAAAAAAABHRlc3QAAnQyAAID/gL+AQM=
+ekbShcBAAAAIgAAANoFAAAQABUAAAAAAAEAAv/+AQAAAA==
‘/*!*/;
### INSERT INTO test.t2
### SET
###   @1=1
###   @2=NULL
# at 1498
#090526 14:09:13 server id 1  end_log_pos 1567  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343353/*!*/;
COMMIT
/*!*/;
# at 1567
#090526 14:09:38 server id 1  end_log_pos 1635  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343378/*!*/;
BEGIN
/*!*/;
# at 1635
# at 1679
#090526 14:09:38 server id 1  end_log_pos 1679  Table_map: `test`.`t2` mapped to number 21
#090526 14:09:38 server id 1  end_log_pos 1733  Update_rows: table id 21 flags: STMT_END_F

BINLOG ‘
EuobShMBAAAALAAAAI8GAAAAABUAAAAAAAAABHRlc3QAAnQyAAID/gL+AQM=
EuobShgBAAAANgAAAMUGAAAQABUAAAAAAAEAAv///AQAAAABZPwCAAAAAWT+AQAAAP4CAAAA
‘/*!*/;
### UPDATE test.t2
### WHERE
###   @1=4
###   @2=’d’
### SET
###   @1=2
###   @2=’d’
### UPDATE test.t2
### WHERE
###   @1=1
###   @2=NULL
### SET
###   @1=2
###   @2=NULL
# at 1733
#090526 14:09:38 server id 1  end_log_pos 1802  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343378/*!*/;
COMMIT

The exact same output with some decently readable output. The problem at this point is that the output is not really something any other technology would undestand (not even feeding it to MySQL would work! ….

At this point I needed to do some compromises in order to reduce the complexity of this job, i.e. I will not be creating and altering tables / databases or indexes during runtime (this I can do without as I can do the same things on the slave manually when I need to do anything like that on the master) and the replication won’t be in real time i.e. the slave will be fed the sql periodically through a script. The last compromise wasn’t actually a compromise but a decision based on speed of coding as I’m more proficient in bash than perl and as such I decided to go with bash as a proof of concept that this thing can be done. This would never happen in reality as it would be much slower as compared to coding the same thing in perl or C / C++ (choice is up to you).

Now parsing the binary logs is not rocket science is it? As you can see there are three hashes ‘###’ in front of the readable query so a simple grep is fine. A few subsitutions and text processing and you’ll end up with:

darrencassar@mysqlpreacher $ /home/dcassar/sandbox/5.1.30/bin/mysqlbinlog  | sed ’s/^ *//g’ | tr ‘\015\012′ ‘\020 ‘ | sed ’s/ INSERT/;\nINSERT/g’ | sed ’s/ DELETE/;\nDELETE/g’ | sed ’s/ UPDATE/;\nUPDATE/g’ | sed ‘${/^$/!s/$/;\
> /;}’
INSERT INTO test.t1 SET @1=1 @2=’a';
DELETE FROM test.t1 WHERE @1=1 @2=’a';
INSERT INTO test.t2 SET @1=2 @2=’d';
UPDATE test.t2 WHERE @1=2 @2=’d’ SET @1=4 @2=’d';
INSERT INTO test.t2 SET @1=1 @2=NULL;
UPDATE test.t2 WHERE @1=4 @2=’d’ SET @1=2 @2=’d';
UPDATE test.t2 WHERE @1=1 @2=NULL SET @1=2 @2=NULL ;
darrencassar@mysqlpreacher $

That is more readable but still not correctly formatted for our ANSI SQL slave.

`INSERT INTO test.t1 SET @1=1 @2=’a';` would need to be replaced with `INSERT INTO test.t1 (cola, colb) values(1,’a');` or `INSERT INTO test.t1 values(1,’a');`,
`DELETE FROM test.t1 WHERE @1=1 @2=’a';` would need to be replaced with `DELETE FROM test.t1 WHERE cola=1 AND colb=’a';` and
`UPDATE test.t2 WHERE @1=2 @2=’d’ SET @1=4 @2=’d';` would have to become `UPDATE test.t2 SET cola=4 , colb=’d’ WHERE cola=2 AND colb=’d';`

The above means we need to replace @1 and @2 with the appropriate column names done using a lookup table in my case by placing a files in data/dbname each bearing names of the different tables and listing each column in order:
I.E. for a database named test and table named table1 having columns cola and colb I used the database folder named test in the mysql data folder and placed a file named table1, the contents of which were:

cola
colb

The following piece of code does the job of replacing those dreadful @1, @2 etc with proper table names:

for (( i=1; i<=`wc -l $dbname/$tbname | cut -d ” ” -f 1`; i++ ))
do
columnname=`awk ‘NR==a’ a=$i $dbname/$tbname`
LINE_TEMP=`echo $LINE_TEMP | sed ’s/@’$i’=/AND ‘$columnname’=/g’ | sed ’s/SET AND/SET/g’ | sed ’s/WHERE AND/WHERE/g’`
done

If you were thinking how I extracted dbname and tbname, here it is:

dbname=`echo $LINE | cut -d ” ” -f$col | cut -d “.” -f 1`
tbname=`echo $LINE | cut -d ” ” -f$col | cut -d “.” -f 2`

where $LINE is each line extracted using the first command parsing mysqlbinary output and the variable col is 3 for delete and 2 for update which reflects the positioning of the database name and table name inside the extracted lines.

A rather ugly way of re-ordering the update command yet functional is:

echo $LINE_TEMP | cut -d “.” -f2 | sed ’s/’$tbname’ //’ | sed ’s/;//’ > templinefile
cat templinefile | sed ’s/ SET /\nSET /g’ > templinefile2
LINE_TEMP=”UPDATE $dbname.$tbname `tail -1 templinefile2  | sed ’s/ is NULL/=NULL/g’ | sed ’s/AND/,/g’` `head -1 templinefile2`;”

The last thing to remember is taking care of `=NULL` and replace it by `is NULL`.

LINE_TEMP=`echo $LINE_TEMP | sed ’s/=NULL/ is NULL/g’`

As I said this was a fast proof of concept rather than a full fledged optimized script doing the job! The total length of code is 37 lines (excluding comments but with correct and nead formatting).

Enjoy
Darren

Advanced, Intermediate, MySQL bash, MySQL, mysqlbinlog, parsing, replication, row based replication, statement based replication

A few interesting KPIs about certified people and their current location (the fact that many people travel from one country to the other for work is very popular these days especially in the IT sector):

Before starting to read, I am hereby assuming the MySQL list of MCDBAs is on a residence basis not citizenship.

There are at the time of this writing a total of 796 MCDBAs in the world, living in 57 countries.

When analyzing the number of MCDBAs / country, it is pretty obvious the scene is totally dominated by the US:

+----------------------+-------------+
| Country              | No of Certs |
+----------------------+-------------+
| UnitedStates         |         346 | 
| Unitedkingdom        |          47 | 
| Germany              |          46 | 
| India                |          34 | 
| Italy                |          28 | 
| Japan                |          26 | 
| Canada               |          23 | 
| China                |          20 | 
| Brazil               |          18 | 
| Australia            |          16 | 
| France               |          16 | 
| Netherlands          |          13 | 
| Switzerland          |          13 | 
| SouthKorea           |          12 | 
| Spain                |          11 | 
| HongKong             |          10 | 
| Sweden               |          10 | 
| SouthAfrica          |          10 | 
| Singapore            |           9 | 
| Taiwan               |           8 | 
| Poland               |           7 | 
| Philippines          |           6 | 
| Norway               |           5 | 
| Russia               |           5 | 
| Finland              |           5 | 
| Ireland              |           4 | 
| Israel               |           3 | 
| Srilanka             |           3 | 
| Ukraine              |           3 | 
| Austria              |           3 | 
| Belgium              |           3 | 
| Denmark              |           2 | 
| Egypt                |           2 | 
| Mexico               |           2 | 
| Malta                |           2 | 
| Thailand             |           2 | 
| Unitedarabemirates   |           2 | 
| Latvia               |           2 | 
| Bangladesh           |           1 | 
| Croatia              |           1 | 
| TrinidadandTobago    |           1 | 
| BosniaandHerzegovina |           1 | 
| Malaysia             |           1 | 
| Slovenia             |           1 | 
| SaudiArabia          |           1 | 
| Indonesia            |           1 | 
| Jordan               |           1 | 
| Kenya                |           1 | 
| Lithuania            |           1 | 
| Luxembourg           |           1 | 
| Guatemala            |           1 | 
| Nepal                |           1 | 
| Gibraltar            |           1 | 
| Newzealand           |           1 | 
| Pakistan             |           1 | 
| Portugal             |           1 | 
| Virginislands        |           1 | 
+----------------------+-------------+ 


But what happens when we look at the number of MCDBAS in respect to population / area?

The country with largest certs / capita is Gibralter with a MySQL Certified DBA in 31k population, followed by Virgin Islands at 1 for 110k and Malta at 2 / 412.6k.

The country with least certs / capita is Indonesia with a single MySQL Certified DBA out of almost 230M people.

+----------------------+------------+-------------+---------+
| Country              | Population | No of Certs | PPM     |
+----------------------+------------+-------------+---------+
| Gibraltar            |      31000 |           1 | 32.2580 | 
| Virgin Islands       |     110000 |           1 |  9.0900 | 
| Malta                |     412600 |           2 |  4.8470 | 
| Luxembourg           |     491700 |           1 |  2.0330 | 
| Singapore            |    4839400 |           9 |  1.8590 | 
| Switzerland          |    7700200 |          13 |  1.6880 | 
| Hong Kong            |    6985200 |          10 |  1.4310 | 
| United States        |  306088000 |         346 |  1.1300 | 
| Sweden               |    9259828 |          10 |  1.0790 | 
| Norway               |    4810122 |           5 |  1.0390 | 
| Finland              |    5331096 |           5 |  0.9370 | 
| Ireland              |    4517800 |           4 |  0.8850 | 
| Latvia               |    2260000 |           2 |  0.8840 | 
| Netherlands          |   16492476 |          13 |  0.7880 | 
| United Kingdom       |   61612300 |          47 |  0.7620 | 
| Trinidad and Tobago  |    1339000 |           1 |  0.7460 | 
| Australia            |   21707964 |          16 |  0.7370 | 
| Canada               |   33596000 |          23 |  0.6840 | 
| Germany              |   82062200 |          46 |  0.5600 | 
| Slovenia             |    2053355 |           1 |  0.4870 | 
| Italy                |   60090400 |          28 |  0.4650 | 
| United Arab Emirates |    4599000 |           2 |  0.4340 | 
| Israel               |    7382200 |           3 |  0.4060 | 
| Denmark              |    5511451 |           2 |  0.3620 | 
| Austria              |    8356700 |           3 |  0.3580 | 
| Taiwan               |   23027672 |           8 |  0.3470 | 
| Lithuania            |    3350400 |           1 |  0.2980 | 
| Belgium              |   10741000 |           3 |  0.2790 | 
| Bosnia Herzegovina   |    3767000 |           1 |  0.2650 | 
| South Korea          |   48333000 |          12 |  0.2480 | 
| France               |   65073482 |          16 |  0.2450 | 
| Spain                |   45853000 |          11 |  0.2390 | 
| New Zealand          |    4302010 |           1 |  0.2320 | 
| Croatia              |    4432000 |           1 |  0.2250 | 
| South Africa         |   48697000 |          10 |  0.2050 | 
| Japan                |  127630000 |          26 |  0.2030 | 
| Poland               |   38130300 |           7 |  0.1830 | 
| Jordan               |    6316000 |           1 |  0.1580 | 
| Sri Lanka            |   20238000 |           3 |  0.1480 | 
| Brazil               |  190902000 |          18 |  0.0940 | 
| Portugal             |   10631800 |           1 |  0.0940 | 
| Guatemala            |   14027000 |           1 |  0.0710 | 
| Philippines          |   92226600 |           6 |  0.0650 | 
| Ukraine              |   46143700 |           3 |  0.0650 | 
| Saudi Arabia         |   25721000 |           1 |  0.0380 | 
| Russia               |  141850000 |           5 |  0.0350 | 
| Malaysia             |   28067000 |           1 |  0.0350 | 
| Nepal                |   29331000 |           1 |  0.0340 | 
| Thailand             |   63389730 |           2 |  0.0310 | 
| India                | 1161120000 |          34 |  0.0290 | 
| Egypt                |   76030000 |           2 |  0.0260 | 
| Kenya                |   39802000 |           1 |  0.0250 | 
| Mexico               |  109610000 |           2 |  0.0180 | 
| China                | 1335962133 |          20 |  0.0140 | 
| Bangladesh           |  162221000 |           1 |  0.0060 | 
| Pakistan             |  165899500 |           1 |  0.0060 | 
| Indonesia            |  229850000 |           1 |  0.0040 | 
+----------------------+------------+-------------+---------+


On a per area basis, the country with most certified MySQL DBAs is again Gibralter given its 6Km squared of area, followed by Singapore, Hong Kong and Malta.

The least being Pakistan, Russia and Indonesia in order.

*PPK here means parts per 1000 Km squared

+----------------------+----------+-------------+----------+
| Country              | Area     | No of Certs | PPK*     |
+----------------------+----------+-------------+----------+
| Gibraltar            |        6 |           1 | 166.6667 | 
| Singapore            |      699 |           9 |  12.8755 | 
| Hong Kong            |     1108 |          10 |   9.0253 | 
| Malta                |      316 |           2 |   6.3291 | 
| Virgin Islands       |      347 |           1 |   2.8818 | 
| Luxembourg           |     2586 |           1 |   0.3867 | 
| Switzerland          |    41284 |          13 |   0.3149 | 
| Netherlands          |    41543 |          13 |   0.3129 | 
| Taiwan               |    36188 |           8 |   0.2211 | 
| Trinidad and Tobago  |     5130 |           1 |   0.1949 | 
| United Kingdom       |   242900 |          47 |   0.1935 | 
| Israel               |    22072 |           3 |   0.1359 | 
| Germany              |   357022 |          46 |   0.1288 | 
| South Korea          |    99678 |          12 |   0.1204 | 
| Belgium              |    30528 |           3 |   0.0983 | 
| Italy                |   301318 |          28 |   0.0929 | 
| Japan                |   377915 |          26 |   0.0688 | 
| Ireland              |    70273 |           4 |   0.0569 | 
| Slovenia             |    20273 |           1 |   0.0493 | 
| Denmark              |    43094 |           2 |   0.0464 | 
| Sri Lanka            |    65610 |           3 |   0.0457 | 
| United States        |  9629091 |         346 |   0.0359 | 
| Austria              |    83871 |           3 |   0.0358 | 
| Latvia               |    64589 |           2 |   0.0310 | 
| France               |   632760 |          16 |   0.0253 | 
| United Arab Emirates |    83600 |           2 |   0.0239 | 
| Poland               |   312685 |           7 |   0.0224 | 
| Sweden               |   450295 |          10 |   0.0222 | 
| Spain                |   505992 |          11 |   0.0217 | 
| Philippines          |   300000 |           6 |   0.0200 | 
| Bosnia Herzegovina   |    51197 |           1 |   0.0195 | 
| Croatia              |    56594 |           1 |   0.0177 | 
| Norway               |   323802 |           5 |   0.0154 | 
| Lithuania            |    65300 |           1 |   0.0153 | 
| Finland              |   338145 |           5 |   0.0148 | 
| Jordan               |    89342 |           1 |   0.0112 | 
| Portugal             |    92090 |           1 |   0.0109 | 
| India                |  3287263 |          34 |   0.0103 | 
| Guatemala            |   108889 |           1 |   0.0092 | 
| South Africa         |  1221037 |          10 |   0.0082 | 
| Bangladesh           |   143998 |           1 |   0.0069 | 
| Nepal                |   147181 |           1 |   0.0068 | 
| Ukraine              |   603500 |           3 |   0.0050 | 
| Thailand             |   513120 |           2 |   0.0039 | 
| New Zealand          |   270467 |           1 |   0.0037 | 
| Malaysia             |   329847 |           1 |   0.0030 | 
| Canada               |  9984670 |          23 |   0.0023 | 
| Brazil               |  8514877 |          18 |   0.0021 | 
| Australia            |  7692024 |          16 |   0.0021 | 
| China                |  9640821 |          20 |   0.0021 | 
| Egypt                |  1002000 |           2 |   0.0020 | 
| Kenya                |   580367 |           1 |   0.0017 | 
| Mexico               |  1964375 |           2 |   0.0010 | 
| Saudi Arabia         |  2000000 |           1 |   0.0005 | 
| Indonesia            |  1904569 |           1 |   0.0005 | 
| Russia               | 17098242 |           5 |   0.0003 | 
| Pakistan             |  7960951 |           1 |   0.0001 | 
+----------------------+----------+-------------+----------+


At this point I'd like to ask the community a couple of questions:

1. Do you think there are many good MySQL DBAs who never tried getting certified?

2. Would you think it is fair to assume, the people who never tried the MCDBA exam is proportional to does who did? (my guess is yes)

 
KPIs for developers and cluster certified people will follow in other blogs.

Advanced, Beginner, Databases, Intermediate, MySQL certification, exam, kpis, MCDBA, MySQL certified dba, statistics