sqlhjalp: 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.

+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+

+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+

+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+

If you need grep the tables to find something?

./mysqlmetagrep --server=<username>:<password>@<HOST>:<PORT> --pattern=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.