Friday, November 4, 2011

MySQL Utilities


I recently explored the mysql-utilities scripts available on launchpad ( https://launchpad.net/mysql-utilities ) The examples below are from my local test db.

The code is available via a quick bzr download after you sign in with your launchpad id of course.


bzr launchpad-login <user_id_ from_launchpad> 


bzr branch lp:~mysql/mysql-utilities/trunk 

MySQL Utilities code does have some requirements, Python between 2.6 and 3.x . So check your python version , some of the distributions have older versions. ( # python –version ) If you want the manuals, then Sphinx (which also requires Jinja) is also required. Of course a connector to the database would be helpful. You can get the latest python connector, via launchpad as well, at https://launchpad.net/myconnpy.

If your curious about more of the options for setup the help commands will give you a full list. ( python setup.py –help-commands ). Otherwise a quick set up is as simple as python setup.py install. I happen to not have the /etc/profile.d/mysql-utilities.sh on my system and the set up tossed an error. A simple touch of this file and all worked fine.

So you have it all installed, now what? You will find inside the scripts directory (../trunk/scripts) approximately 15 different python scripts.

mysqldbcompare.py , mysqldbcopy.py, mysqldbexport.py ,mysqldbimport.py , mysqldiff.py , mysqldiskusage.py , mysqlindexcheck.py, mysqlmetagrep.py , mysqlprocgrep.py , mysqlreplicate.py, mysqlrplcheck.py , mmysqlrplshow.py , mmysqlserverclone.py, mmysqlserverinfo.py , mysqluserclone.py

Some quick examples via the employee database

Index Checks:
./mysqlindexcheck –server=<username>:<password>@<HOST>:<PORT> employees

# Source on 192.168.0.2: ... connected.
# The following indexes are duplicates or redundant for table employees.dept_emp:
#
CREATE INDEX emp_no ON employees.dept_emp (emp_no) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE employees.dept_emp ADD PRIMARY KEY (emp_no, dept_no)
# The following indexes are duplicates or redundant for table employees.dept_manager:
#
CREATE INDEX emp_no ON employees.dept_manager (emp_no) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE employees.dept_manager ADD PRIMARY KEY (emp_no, dept_no)
# The following indexes are duplicates or redundant for table employees.salaries:
#
CREATE INDEX emp_no ON employees.salaries (emp_no) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE employees.salaries ADD PRIMARY KEY (emp_no, from_date)
# The following indexes are duplicates or redundant for table employees.titles:
#
CREATE INDEX emp_no ON employees.titles (emp_no) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE employees.titles ADD PRIMARY KEY (emp_no, title, from_date)


Quick Server information:
./mysqlserverinfo --server=<username>:<password>@<HOST>:<PORT> employees
# Source on 192.168.0.2: ... connected.
+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+
| server | version | datadir | basedir | plugin_dir | config_file | binary_log | binary_log_pos | relay_log | relay_log_pos |
+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+
192.168.0.2:3306 | 5.6.3-m6-log | /var/lib/mysql/ | /usr | /usr/lib64/mysql/plugin | /etc/my.cnf | mysql-bin.000340 | 717 | None | None |
+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+


If you need grep the tables to find something?
./mysqlmetagrep --server=<username>:<password>@<HOST>:<PORT> --pattern=emp_no
+----------------------------+--------------+---------------+------------+-------------+----------+
| Connection | Object Type | Object Name | Database | Field Type | Matches |
+----------------------------+--------------+---------------+------------+-------------+----------+
| kdl:*@192.168.0.2:3306 | TABLE | dept_emp | employees | COLUMN | emp_no |
| kdl:*@192.168.0.2:3306 | TABLE | dept_manager | employees | COLUMN | emp_no |
| kdl:*@192.168.0.2:3306 | TABLE | employees | employees | COLUMN | emp_no |
| kdl:*@192.168.0.2:3306 | TABLE | salaries | employees | COLUMN | emp_no |
| kdl:*@192.168.0.2:3306 | TABLE | titles | employees | COLUMN | emp_no |
+----------------------------+--------------+---------------+------------+-------------+----------+

Disk Usage:
./mysqldiskusage --server=<username>:<password>@<HOST>:<PORT>
# Source on 192.168.0.2: ... connected.
# Database totals:
+---------------------+-----------------+
| db_name | total |
+---------------------+-----------------+
.

Total database disk usage = 24,721,809,136 bytes or 23.00 GB


They are tools that are quick and easy to get installed and use to help and enhance your MySQL experience.

MySQL Utilities are part of the MySQL Workbench but they can be used as a stand alone option.
Also available is the testing suite “mut” which is available under the mysql-test directory.

# ./mut.py --server=<username>:<password>@<HOST>:<PORT>
MySQL Utilities Testing - MUT
Parameters used:
Display Width = 75
Sorted = True
Force = False
Test directory = './t'
Utilities directory = '../scripts'
Starting port = 3310
Servers:
Connecting to 192.168.0.2 as user kdl on port 3306: CONNECTED
---------------------------------------------------------------------------
TEST NAME STATUS TIME
===========================================================================
experimental.t1 [pass] 48
experimental.t2 [pass] 14
experimental.t3 [pass] 3
experimental.t4 [pass] 10
main.check_index [FAIL]
ERROR: Result file mismatch:
- # Source on localhost: ... connected.
+ # Source on 192.168.0.2: ... connected.
- # Source on localhost: ... connected.
+ # Source on 192.168.0.2: ... connected.
- # Source on localhost: ... connected.
+ # Source on 192.168.0.2: ... connected.
- # Source on localhost: ... connected.
+ # Source on 192.168.0.2: ... connected.
- # Source on localhost: ... connected.
+ # Source on 192.168.0.2: ... connected.
- # Source on localhost: ... connected.
+ # Source on 192.168.0.2: ... connected.
---------------------------------------------------------------------------
Testing completed: Friday 04 November 2011 14:38:02
4 of 75 tests completed.
The following tests failed or were skipped: check_index
Deleting temporary files...success.


More information is available :



You can of course find this and numerous other MySQL code instances, some are old, at https://launchpad.net/mysql