MyToBiz
Yet another MySQL Extender
Abstract
MyToBiz is a Toolkit for MySQL which aims to help the DB admin or developer of MySQL database application. Its goal is to simplify dynamic SQL query execution and provide a suite of modules with a comprehensive set of DBA and development tools.
Instead of connecting directly to a MySQL-Server a MySQL-client connects to MyToBiz as proxy. MyToBiz passes the DataStream from the client to the server and vice versa but if it finds an MyToBiz-command in the Client DataStream, MyToBiz will execute this command and return the results back to the client.
Why MyToBiz?
MyToBiz come with the following capabilities:
o Developer doesn’t have to know or learn PERL to use MyToBiz futures totally integrated on MyToBiz transparently.
o A simple way to do complex scripts for data manipulation, which cannot be accomplished with queries or limited capabilities of stored procedures in MySQL.
o A comprehensive set of built-ins and Plug-ins to simplify developers and DB admin work:
§ A set of commands and alias like Shell commands to replace or simplify repetitive and long Query (ls, wc, set, alias, eval, exec, explain, …)
§ Context Perl programming and interpolate Perl variables into SQL Query.
§ A set of Plugins to extend capabilities of MySQL-Server or Client as the Query profiler, Comprehensive Database Statistics, Easy MySQL BuckUp, Rapid yahoo search, …
o Adding methods that support MySQL-specific SQL generation and query execution,
o …
TODO
o Modular architecture for various output formats (CVS, HTML, SQL, XML),
o Accrue security of DBMS:
§ DataStream between Server and client can be secured by different way,
§ Developers of thirty applications don’t have to care about protection of access to data:
- By simple configuration you can grant access on READ/WRITE mode to a database, table and column,
- You can indicate to MyToBiz exactly when, where and how many data can be exchanged between MySQL and a user.
- Also you can configure MyToBiz to check SQL Query received from the MySQL-client and try to avoid attack possibility (SQL injection, DOS, … )
o Export / Import data in different formats directly from MySQL Server,
o Schedule Export / Import to or from LOCAL MACHINE, FTP, SCP, HTTP,
o Extracting and simplify manipulating of MySQL metadata,
o Extension for pivot / cross tabulation of data helper,
o An integrated helper with a KB of FAQ, snippets, tips,
o Modular architecture for querying various different data sources:
§ SELECT title, Url, Summary FROM &YahooSearch WHERE keywords = "key word here";
§ SELECT title, keywords FROM &web WHERE url = "http://www.google.com";
§ LOAD DATA INFILE "ftp://host.net/t.txt" INTO TABLE db.t;
§ SELECT * INTO OUTFILE "scp://user:pass@host.net" ...;
§ DELETE FROM &imap WHERE user connection = "imap://user:pass@host.net" and date < NOW();
How to install?
MyToBiz is written in perl so you need an uptodate perl-version to 5.6. You also need to have the Perl-Compress-Zlib module installed which can be found on cpan (www.cpan.com)
You can start MyToBiz with:
$ tar xzf MyToBiz-version.tgz$ cd /path/to/MyToBiz |
Since version 1.4 config-file can be found in the dir ./mtb.conf, So you have to go to this directory and edit the config-file.
After everything is configured you can start MyToBiz daemon with:
$ /path/to/MyToBiz/ perl mtbd.pl --server-host mysql.example.net --server-port 3306 --client-port 9876Proxy launched at : Sat Jun 16 21:01:34 2007Proxy started at : Sat Jun 16 21:01:47 2007-- server-host : mysql.example.net-- server-port : 3306-- client-port : 9876 |
If everything is set up right it should no be possible to connect to MyToBiz. Try this to connect to server:
$ mysql -h host -u username -P 9876 -p password -b |
* host : hostname where running mtbd.pl (localhost if the mtbd.pl run in the same host of MySQL-client)
* 9876 : proxy port
* username : mysql username
* password : mysql password
Now you should see the normal mysql-prompt :
mysql> !about; -- DISPLAY MyToBiz ABOUT MESSAGE ;+--------------------------------------------------------------------------+| ABOUT |+--------------------------------------------------------------------------+| ABOUT MyToBiz || ------------------------------------------------------------------------ || || MyToBiz is a Toolkit for MySQL which aims to help the DBA or developer || of MySQL database application. Its goal is to simplify dynamic SQL || query execution and provide a suite of modules with a comprehensive || set of DBA and development tools. || || ------------------------------------------------------------------------ || - Report bugs to <momed at users.sourceforge.net>. - |+--------------------------------------------------------------------------+11 rows in set (0.00 sec) mysql> !ld my%; -- SHOW DATABASES LIKE 'my%';+-----------+| Database |+-----------+| mysql || mysql_bck |+-----------+2 rows in set (0.00 sec) mysql> !wc -l db.tst; -- SELECT count(*) FROM db.tst;+----------+| count(*) |+----------+| 3 |+----------+1 row in set (0.00 sec) mysql> !dump db.tst; -- SELECT * FROM db.tst;+--------+-------+| id | txt |+--------+-------+| 100000 | col-1 || 200000 | col-2 || 300000 | col-3 |+--------+-------+3 rows in set (0.03 sec) mysql> !call getDBSize(); -- get some db, data and indexes size & stats;+-----------------------------+----------+-----------+------------+----------+-------------------------+| base | DB (MB) | DATA (MB) | INDEX (MB) | Pourcent | Graph |+-----------------------------+----------+-----------+------------+----------+-------------------------+| ts | 0.0010 | 0.0000 | 0.0010 | 0.00 | * || information_schema | 0.0039 | 0.0000 | 0.0039 | 0.00 | * || test | 0.0060 | 0.0001 | 0.0059 | 0.00 | * || db | 0.0093 | 0.0006 | 0.0088 | 0.00 | * || mysql | 8.6328 | 5.3349 | 3.2979 | 1.98 | ** || mysql_bck | 9.9791 | 9.2330 | 0.7461 | 2.29 | ** || phpmyadmin | 36.0781 | 31.2627 | 4.8154 | 8.29 | **** || tsdb_spiderdb | 46.2438 | 41.0241 | 5.2197 | 10.62 | ***** || ftpsearch | 55.6699 | 30.4717 | 25.1982 | 12.79 | ***** || tm | 59.0429 | 45.5175 | 13.5254 | 13.56 | ****** || phptracking | 92.0994 | 50.8806 | 41.2187 | 21.15 | ******** || spiderdb1_pagelist | 92.0984 | 50.8806 | 41.2178 | 21.15 | ******** |-/- -/- -/- -/- -/- -/- -/-| NULL | 435.4264 | 290.7222 | 144.7041 | 100.00 | *********************** |+-----------------------------+----------+-----------+------------+----------+-------------------------+17 rows in set (1 min 18.31 sec)
|
A Sort of overview
So what's so darn interesting about all this then? Well, the key point is that MyToBiz supports "pluggable contexts". A context here means that it's a different way of interpreting the commands you type. For example:
mysql> use mysqlDatabase changedmysql> !ls; -- ls is an alias for SHOW TABLES+---------------------------+| Tables_in_mysql |+---------------------------+| db || func || help_category || help_keyword || help_topic || host || proc || time_zone_transition_type || user |/ /+---------------------------+17 rows in set (0.47 sec) mysql> !set $cmd = select Host, Db, User from db limit 2;+----------------------------------------------+| Set |+----------------------------------------------+| $cmd = select Host, Db, User from db limit 2 |+----------------------------------------------+1 row in set (0.75 sec) mysql> !e $cmd; -- e is an alias for EXPLAIN+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | db | index | NULL | PRIMARY | 420 | NULL | 8 | Using index |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.62 sec) mysql> !eq $cmd; -- eq is an alias for EXECUTE QUERRY +-----------+--------+------------+| Host | Db | User |+-----------+--------+------------+| localhost | moodle | moodleuser || localhost | mysql | tmadmin |+-----------+--------+------------+2 rows in set (0.86 sec)
|
The "!" prefix introduce text that's in a different context; The interpretation of the text depends on the context.
However, it gets clever in that MyToBiz has several different futures (and more can be added).
Built-ins Stuff
1 – LS:
MYTOBIZ_BUILTINS
Usage: ls [OPTION]... [DATABASE.TABLE]...
List information about database or tables: (the current database by default).
mysql> !ls -help;+-----------------------------------------------------------------------------------+| HELP LS |+-----------------------------------------------------------------------------------+| HELP LS || --------------------------------------------------------------------------------- || Usage: ls [OPTION]... [DATABASE.TABLE]... || List information about the database or tables (the current database by default). || || --help show this help || -d show databases || -t show tables (the current database by default). || -v show variables || -s show status || -e show errors || -w show WARNINGS || -p show PRIVILEGES || -pl show PROCESSLIST || -fpl show FULL PROCESSLIST || -cd show CREATE DATABASE || -ct show CREATE TABLE || -ls show list script || -bi show list built-ins || -gv show list global vars || -cfg show config || --------------------------------------------------------------------------------- || - Report bugs to <momed at users.sourceforge.net>. - |+-----------------------------------------------------------------------------------+23 rows in set (0.50 sec) |
mysql> !ls; -- IF no database select, ls will list all databases on server +-----------------------------+| Database |+-----------------------------+| information_schema || test || cdcol || wordpress || xoops2013 |/ /| xtab |+-----------------------------+58 rows in set (1.83 sec) mysql> use test;Database changedmysql> !ls; -- IF an database select, ls will list tables+----------------+| Tables_in_test |+----------------+| sales || t1 || t2 || tst |+----------------+ |
2 - ALIAS:
MYTOBIZ_BUILTINS
Alias [-p] [name [=value] ...]
Alias with no arguments or with the -p option prints the list of aliases in the form alias name=value on standard output.
When arguments are supplied, an alias is defined for each name whose value is given. A trailing space in value causes the next word to be checked for alias sub-stitution when the alias is expanded. For each name in the argument list for which no value is supplied, the name and value of the alias is printed. Alias returns true unless a name is given for which no alias has been defined.
mysql> !alias;+-------------------------------------+| Alias |+-------------------------------------+| ls -se='eq SHOW STORAGE ENGINES' || dump='eq SELECT * FROM' || le='ls -e' || ls -ml='eq SHOW MASTER LOGS' || ls -pl='eq SHOW PROCESSLIST' || lv='ls -v' || lp='ls -p' || ls -p='eq SHOW PRIVILEGES' || ls -bl='eq SHOW BINLOG EVENTS' || ls -ms='eq SHOW MASTER STATUS' || eq='exec_query' || ls -fpl='eq SHOW FULL PROCESSLIST' || e='eq EXPLAIN' || ls -sh='eq SHOW SLAVE HOSTS' || lt='ls -t' || ld='ls -d' || lss='ls -s' || wc -l='eq SELECT COUNT(*) FROM' || lw='ls -w' || ls -ss='eq SHOW SLAVE STATUS' |+-------------------------------------+ |
To add an alias:
mysql> !alias ll='exec ls -l' -> -- exec is an mytobiz builtin to execute system command -> -- on Proxy host -> ;+---------------------+| Alias |+---------------------+| ll = 'exec ls -l' |+---------------------+1 row in set (0.55 sec) mysql> !ll; -- run the new Alias+-------------------------------------------------------+| Exec |+-------------------------------------------------------+| total 28 || -rwxr-xr-x+ 1 User2 Aucun 10007 Jun 17 02:40 README || drwxr-xr-x+ 2 User2 Aucun 0 Jun 17 13:34 doc | | drwxr-xr-x+ 6 User2 Aucun 0 Jun 16 13:34 lib | | drwxr-xr-x+ 2 User2 Aucun 0 Jun 16 23:14 log | | -rwxr-xr-x+ 1 User2 Aucun 4108 Jun 17 13:27 mtb.conf | | -rwxr-xr-x+ 1 User2 Aucun 4578 Jun 17 00:23 mtbd.pl | | -rwxr-xr-x+ 1 User2 Aucun 0 Jun 15 23:52 t.pl | +-------------------------------------------------------+8 rows in set (1.42 sec) |
4 - HELP:
2 - SET:
3 – EXECUTE QUERRY:
5 - EXPLAIN:
6 - EVAL:
7 - EXEC: