MySQL utilities are set of scripts provided for DBAs for general database administration. These are basically command line utilities which can be used to perform various tasks.
Following is the listing of MySQL utility version 1.3 that I installed in my environment
advait.desktop$ ls -lL /usr/local/bin/mysql* -rwxr-xr-x 1 root root 10546 Mar 26 00:34 /usr/local/bin/mysqlauditadmin -rwxr-xr-x 1 root root 13443 Mar 26 00:34 /usr/local/bin/mysqlauditgrep -rwxr-xr-x 1 root root 10142 Mar 26 00:34 /usr/local/bin/mysqldbcompare -rwxr-xr-x 1 root root 13509 Mar 26 00:34 /usr/local/bin/mysqldbcopy -rwxr-xr-x 1 root root 14781 Mar 26 00:34 /usr/local/bin/mysqldbexport -rwxr-xr-x 1 root root 12719 Mar 26 00:34 /usr/local/bin/mysqldbimport -rwxr-xr-x 1 root root 9551 Mar 26 00:34 /usr/local/bin/mysqldiff -rwxr-xr-x 1 root root 6339 Mar 26 00:34 /usr/local/bin/mysqldiskusage -rwxr-xr-x 1 root root 13554 Mar 26 00:34 /usr/local/bin/mysqlfailover -rwxr-xr-x 1 root root 16487 Mar 26 00:34 /usr/local/bin/mysqlfrm -rwxr-xr-x 1 root root 5218 Mar 26 00:34 /usr/local/bin/mysqlindexcheck -rwxr-xr-x 1 root root 4717 Mar 26 00:34 /usr/local/bin/mysqlmetagrep -rwxr-xr-x 1 root root 5298 Mar 26 00:34 /usr/local/bin/mysqlprocgrep -rwxr-xr-x 1 root root 6452 Mar 26 00:34 /usr/local/bin/mysqlreplicate -rwxr-xr-x 1 root root 14169 Mar 26 00:34 /usr/local/bin/mysqlrpladmin -rwxr-xr-x 1 root root 5612 Mar 26 00:34 /usr/local/bin/mysqlrplcheck -rwxr-xr-x 1 root root 5796 Mar 26 00:34 /usr/local/bin/mysqlrplshow -rwxr-xr-x 1 root root 7228 Mar 26 00:34 /usr/local/bin/mysqlserverclone -rwxr-xr-x 1 root root 4881 Mar 26 00:34 /usr/local/bin/mysqlserverinfo -rwxr-xr-x 1 root root 6194 Mar 26 00:34 /usr/local/bin/mysqluc -rwxr-xr-x 1 root root 6667 Mar 26 00:34 /usr/local/bin/mysqluserclone
You can find the complete usage of these scripts in official documentation of mysql utilities. I have provided the link for the same in reference section of this article.
I will cover the installation part of MySQL utilities in this article.
Step 1) Download MySQL utilities from http://dev.mysql.com/downloads/tools/utilities/ location
Select the OS as “Linux – Generic” and download .tar.gz file
Step 2) Extract the file
Copy the file to the server on which you have installed MySQL or on any server where you want to install utilities and extract the tar file.
In my case I am going to install this on my desktop where MySQL is running
So my tar file is residing at /home/advait/mysql/mysql-utilities-1.3.6.tar.gz
I can extract the content in one of the directory under my MySQL installation.
So I extracted above tar file at /usr/local/mysql
tar xvfz /home/advait/mysql/mysql-utilities-1.3.6.tar.gz
Step 3) Build and install utilities
Once you extract you will see mysql-utilities-1.3.6 directory. Inside this directory we set setup.py
Use setup.py to build and install utilities
Build should be done using mysql (non root) user. In my case I am using advait user to start/stop and manage mysql. So I will build using advait user
python setup.py build
advait.desktop$ python setup.py build running build running build_py creating build creating build/lib creating build/lib/mysql copying mysql/__init__.py -> build/lib/mysql creating build/lib/mysql/utilities copying mysql/utilities/exception.py -> build/lib/mysql/utilities copying mysql/utilities/__init__.py -> build/lib/mysql/utilities creating build/lib/mysql/utilities/command copying mysql/utilities/command/read_frm.py -> build/lib/mysql/utilities/command copying mysql/utilities/command/rpl_admin.py -> build/lib/mysql/utilities/command copying mysql/utilities/command/serverinfo.py -> build/lib/mysql/utilities/command copying mysql/utilities/command/utilitiesconsole.py -> build/lib/mysql/utilities/command ... changing mode of build/scripts-2.7/mysqlserverclone from 644 to 755 changing mode of build/scripts-2.7/mysqldbcompare from 644 to 755 changing mode of build/scripts-2.7/mysqlserverinfo from 644 to 755 changing mode of build/scripts-2.7/mysqluserclone from 644 to 755
Install should be done using “root” user since we have installed MySQL using root user. If you use any other user (other than root), that user may not have permissions on python libraries and so install can fail.
advaitd-2.desktop$ python setup.py install running install running build running build_py running build_scripts running install_lib creating /usr/local/lib/python2.7/site-packages/mysql copying build/lib/mysql/__init__.py -> /usr/local/lib/python2.7/site-packages/mysql creating /usr/local/lib/python2.7/site-packages/mysql/utilities copying build/lib/mysql/utilities/exception.py -> /usr/local/lib/python2.7/site-packages/mysql/utilities creating /usr/local/lib/python2.7/site-packages/mysql/utilities/common copying build/lib/mysql/utilities/common/lock.py -> /usr/local/lib/python2.7/site-packages/mysql/utilities/common ... changing mode of /usr/local/bin/mysqldbexport to 755 changing mode of /usr/local/bin/mysqldbimport to 755 changing mode of /usr/local/bin/mysqlindexcheck to 755 changing mode of /usr/local/bin/mysqlrplcheck to 755 changing mode of /usr/local/bin/mysqlserverinfo to 755 changing mode of /usr/local/bin/mysqlfrm to 755 running install_egg_info
Step 4) Testing
Once install is done, you can verify running one of the script if things are working fine.
All MySQL utilities will get installed at /usr/local/bin directory and they should be part of PATH variable. So no need to give complete path.
advait.desktop$ mysqldiskusage --server=root:<password>@localhost # Source on localhost: ... connected. # Database totals: +---------------------+------------+ | db_name | total | +---------------------+------------+ | deo | 546,720 | | mysql | 1,542,216 | | performance_schema | 396,071 | +---------------------+------------+ Total database disk usage = 2,485,007 bytes or 2.37 MB #...done.
You might face following error if python connector module is not installed
advait.desktop$ mysqldiskusage --server=root:<password>@localhost Traceback (most recent call last): File "/usr/local/bin/mysqldiskusage", line 35, in <module> from mysql.utilities.common.server import connect_servers File "/usr/local/lib/python2.7/site-packages/mysql/utilities/common/server.py", line 31, in <module> import mysql.connector ImportError: No module named connector
If you face this issue, you need to install python connector from http://dev.mysql.com/downloads/connector/python/ location
Step 5) Installing python connector
Again download the connector from http://dev.mysql.com/downloads/connector/python/ location
scp the same to the machine on which you were installing MySQL utilities
Extract the .tar.gz file to one of the directories in MySQL installation. In my case I am installing them under utilities directory – /usr/local/mysql/mysql-utilities-1.3.6
Build using non-root user (advait in my case).
python setup.py build
Install using root user
python setup.py install
After this you should be good to use MySQL utilities.
MySQL Utilities download – http://dev.mysql.com/downloads/tools/utilities/
Connector download – http://dev.mysql.com/downloads/connector/python/
MySQL Utilities overview – http://dev.mysql.com/doc/mysql-utilities/1.3/en/utils-overview.html
Hope this helps !!