Category: MySQL

Installing MySQL Utilities

Published on by Advait1 Comment

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

cd /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.

Example:

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.

Reference:

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 !!

Advertisement

Installing MySQL

Published on by Advait1 Comment

I am exploring another database storage solution “MySQL” to understand practical applications of using MySQL in our environment.

This is one of the many articles that I will be writing on MySQL. This article gives you brief steps to installing MySQL on Linux.

Nothing different than what is provided in MySQL documentation except that this is less elaborate with only required steps compared to MySQL documentation.

This is how you can do the installation:-

Step 1) Down the software from http://dev.mysql.com/downloads/

If you are downloading only for checking out usability and features and for personal learning – go for community version. Click on “MySQL Community Server” link.

Select the OS as “Linux – Generic”

Scroll to the bottom and you will see last 2 lines provides the option to download binaries in the form of tar.gz files – “Linux – Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive” and “Linux – Generic (glibc 2.5) (x86, 32-bit), Compressed TAR Archive”

Depending on the hardware server you have download either 32 bit or 64 bit version

Step 2) Extract the files on server (login as root) 

Once you download the software (may be on your laptop), SCP the same to the linux server on which you want to install MySQL.

From here on you should be logged in as “root” user on your linux server

Extract the software on linux server at /usr/local location

In my case software was lying on my linux server at /home/advait/mysql location. I extracted using following commands

cd /usr/local 
tar xvfz /home/advait/mysql/mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz

Create a new symlink “mysql” to point to extracted directory (it looks neat without version number and other details).

ln -s mysql-5.6.16-linux-glibc2.5-x86_64 mysql

Step 3) Run following steps to change ownerships

Since you want a superuser to have control of mysql and you dont want to login every time as “root” user on your server, you should be changing the ownership to the user which should be the administrator of mysql.

Usual convention suggests creating “mysql” user on your linux host and giving ownership to “mysql” user.

But on my desktop, I have a user called “advait” and I will be giving ownership to this user.

chown -R advait mysql
cd mysql
chown -R advait .
chgrp -R <grp name> .

Above <grp name> should be the group your user ID belongs to. In my case its again “advait”. To avoid confusion I have used <grp name> instead.

Step 4) Create MySQL database

you should be in /usr/local/mysql directory. Following script will create MySQL database named “mysql” and all required metadata tables inside that.

it will also create a test database for testing purpose.

scripts/mysql_install_db --user=advait

Step 5) Change permissions and create Log directory

you should be in /usr/local/mysql directory

chown -R advait data
chgrp -R <grp name> data
mkdir log
chown -R advait log
chgrp -R <grp name> log

data directory will hold data about tables and log directory will hold log files.

Step 6) Copy config file and modify

Copy my.cnf from /usr/local/mysql to /etc/

you should be in /usr/local/mysql directory

cp my.cnf /etc/my.cnf

modify following parameters in /etc/my.cnf and put them under [mysql.server] heading

[mysql.server]
log_bin = /usr/local/mysql/log
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
socket = /tmp/mysql.sock
user = advait
join_buffer_size = 256M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M

These parameters will be used by mysql instance to during startup.

Step 7) Start MySQL instance

You should start mysql server using your user ID to which you have given the ownership. In my case “advait”.

Login as “advait” and run following commands to start MySQL instance

cd /usr/local/mysql

Use one of the following command to start MySQL instance.

bin/mysqld_safe &
support-files/mysql.server start

You can actually copy support-files/mysql.server in /etc/init.d and make MySQL instance start automatically when server starts

Step 8) Make MySQL instance start automatically with server reboot

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql

Reference:

http://dev.mysql.com/doc/refman/5.0/en/installing.html

Hope this helps !!