Friday, February 14, 2014

Innotop on Centos 6.5 MySQL version 5.5.34

innotop - MySQL and InnoDB transaction/status monitor.

INSTALLATION

wget https://innotop.googlecode.com/files/innotop-1.9.1-1.el6.noarch.rpm
rpm -ivh innotop-1.9.1-1.el6.noarch.rpm

Posible errros:

error: Failed dependencies:
        perl(Term::ReadKey) is needed by innotop-1.9.1-1.el6.noarch
        perl(Time::HiRes) is needed by innotop-1.9.1-1.el6.noarch
        perl-TermReadKey is needed by innotop-1.9.1-1.el6.noarch
        perl-Time-HiRes is needed by innotop-1.9.1-1.el6.noarch

more info about innotop: http://innotop.googlecode.com/svn/html/manual.html



[root@mercury tmp]# yum install perl-TermReadKey
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
 * base: centos.mirrors.hoobly.com
 * extras: mirrors.xmission.com
 * updates: centos.eecs.wsu.edu
 * webtatic: us-east.repo.webtatic.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-TermReadKey.x86_64 0:2.30-13.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================
 Package                                 Arch                          Version                              Repository                   Size
==============================================================================================================================================
Installing:
 perl-TermReadKey                        x86_64                        2.30-13.el6                          base                         31 k

Transaction Summary
==============================================================================================================================================
Install       1 Package(s)

Total download size: 31 k
Installed size: 62 k
Is this ok [y/N]: y
Downloading Packages:
perl-TermReadKey-2.30-13.el6.x86_64.rpm                                                                                |  31 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : perl-TermReadKey-2.30-13.el6.x86_64                                                                                        1/1
  Verifying  : perl-TermReadKey-2.30-13.el6.x86_64                                                                                        1/1

Installed:
  perl-TermReadKey.x86_64 0:2.30-13.el6

Complete!
[root@mercury tmp]# yum install perl-Time-HiRes
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
 * base: centos.mirrors.hoobly.com
 * extras: mirrors.xmission.com
 * updates: centos.eecs.wsu.edu
 * webtatic: us-east.repo.webtatic.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-Time-HiRes.x86_64 4:1.9721-136.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================
 Package                               Arch                         Version                                  Repository                  Size
==============================================================================================================================================
Installing:
 perl-Time-HiRes                       x86_64                       4:1.9721-136.el6                         base                        48 k

Transaction Summary
==============================================================================================================================================
Install       1 Package(s)

Total download size: 48 k
Installed size: 56 k
Is this ok [y/N]: y
Downloading Packages:
perl-Time-HiRes-1.9721-136.el6.x86_64.rpm                                                                              |  48 kB     00:01
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : 4:perl-Time-HiRes-1.9721-136.el6.x86_64                                                                                    1/1
  Verifying  : 4:perl-Time-HiRes-1.9721-136.el6.x86_64                                                                                    1/1

Installed:
  perl-Time-HiRes.x86_64 4:1.9721-136.el6

Complete!


[root@mercury tmp]# rpm -ivh innotop-1.9.1-1.el6.noarch.rpm
Preparing...                ########################################### [100%]
   1:innotop                ########################################### [100%]

Mytop on Centos 6.5 Mysql Version : 5.5.34



Mytop

mytop is a console-based (non-gui) tool for monitoring the threads
and overall performance of a MySQL server.
It runs on most Unix systems which have Perl,
DBI, and Term::ReadKey installed. And with Term::ANSIColor installed
you even get color.

If you install Time::HiRes, you’ll get good real-time queries/second stats.

As of version 0.7, it even run

INSTALLATIONS

Download Package: http://jeremy.zawodny.com/mysql/mytop/
tar -zxvf mytop-.tar.gz
cd mytop-
perl Makefile.PL
make
make install


Possible Errors during installation:

Warning: prerequisite DBD::mysql 1 not found.
Warning: prerequisite DBI 1.13 not found.
Warning: prerequisite Term::ReadKey 2.1 not found.

Solution:

perl-Class-Data-Inheritable-0.08-1.el5.rf.noarch.rpm
perl-DBD-MySQL-4.014-1.el5.rfx.x86_64.rpm
perl-DBI-1.616-1.el5.rfx.x86_64.rpm
perl-DBIx-ContextualFetch-1.03-1.el5.rf.noarch.rpm
perl-Ima-DBI-0.35-1.el5.noarch.rpm
perl-Ima-DBI-0.35-1.el5.src.rpm
perl-TermReadKey-2.30-4.el5.

Error after a successful installation:

Use of uninitialized value $host in substitution (s///) at /usr/local/bin/mytop line 958.

Solution:

Remove this line

        $host =~ s/^([^.]+).*/$1/;
        $thread->{Host} = $host;

Replace with this

            if ($host)
                {
              $host =~ s/^([^.]+).*/$1/;
              $thread->{Host} = $host;
                }



Error in option spec: "long|!"

Solution:

The fix for this is to edit the mytop script and comment out the line containing long|!

vi /usr/local/bin/mytop

Run a search for long|! and comment out the following line:

"long|!" => \$config{long_nums},

Start mytop and you should be fine.



Sources: 

Thursday, February 13, 2014

MySQL 5.5 Installation on Centos/RHEL 6.5 via Yum

I've been using RPM every time i will install MySQL server in my Linux boxes. But today i will show you on how to install MySQL 5.5 on Centos 6.5 via Yum.

First, to set up the yum repository, install the webtatic-release RPM based on your CentOS/RHEL release.


[root@localhost ~]# rpm -Uvh http://mirror.webtatic.com/yum/el6/latest.rpm
Retrieving http://mirror.webtatic.com/yum/el6/latest.rpm
warning: /var/tmp/rpm-tmp.q3Y9pR: Header V4 DSA/SHA1 Signature, key ID cf4c4ff9: NOKEY
Preparing...                ########################################### [100%]

   1:webtatic-release       ########################################### [100%]



Remove the pre-installed mysql-libs with no dependecy.

[root@localhost ~]# rpm -e  mysql-libs
error: Failed dependencies:
        libmysqlclient.so.16()(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64
        libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64
        mysql-libs is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64


[root@localhost ~]# rpm -e  --nodeps mysql-libs

After removing the previous version of mysql-libs you can now start the installation of MYSQL via Yum


[root@localhost ~]# yum install mysql55 mysql55-server

Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
 * base: centos.corenetworks.net
 * extras: centos.mirror.freedomvoice.com
 * updates: mirrors.usinternet.com
 * webtatic: sp.repo.webtatic.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mysql55.x86_64 0:5.5.34-1.w6 will be installed
--> Processing Dependency: mysql55-libs = 5.5.34-1.w6 for package: mysql55-5.5.34-1.w6.x86_64
---> Package mysql55-server.x86_64 0:5.5.34-1.w6 will be installed
--> Processing Dependency: perl-DBI for package: mysql55-server-5.5.34-1.w6.x86_64
--> Processing Dependency: perl-DBD-MySQL for package: mysql55-server-5.5.34-1.w6.x86_64
--> Processing Dependency: perl(DBI) for package: mysql55-server-5.5.34-1.w6.x86_64
--> Running transaction check
---> Package mysql55-libs.x86_64 0:5.5.34-1.w6 will be installed
--> Processing Dependency: libmysqlclient16 for package: mysql55-libs-5.5.34-1.w6.x86_64
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
--> Running transaction check
---> Package libmysqlclient16.x86_64 0:5.1.69-1.w6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================================
 Package                                 Arch                          Version                              Repository                       Size
==================================================================================================================================================
Installing:
 mysql55                                 x86_64                        5.5.34-1.w6                          webtatic                        5.5 M
 mysql55-server                          x86_64                        5.5.34-1.w6                          webtatic                         10 M
Installing for dependencies:
 libmysqlclient16                        x86_64                        5.1.69-1.w6                          webtatic                        1.0 M
 mysql55-libs                            x86_64                        5.5.34-1.w6                          webtatic                        778 k
 perl-DBD-MySQL                          x86_64                        4.013-3.el6                          base                            134 k
 perl-DBI                                x86_64                        1.609-4.el6                          base                            705 k

Transaction Summary
==================================================================================================================================================
Install       6 Package(s)

Total download size: 18 M
Installed size: 82 M
Is this ok [y/N]: y
Downloading Packages:
(1/6): libmysqlclient16-5.1.69-1.w6.x86_64.rpm                                                                             | 1.0 MB     00:06
(2/6): mysql55-5.5.34-1.w6.x86_64.rpm                                                                                      | 5.5 MB     00:45
(3/6): mysql55-libs-5.5.34-1.w6.x86_64.rpm                                                                                 | 778 kB     00:05
(4/6): mysql55-server-5.5.34-1.w6.x86_64.rpm                                                                               |  10 MB     01:13
(5/6): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm                                                                               | 134 kB     00:02
(6/6): perl-DBI-1.609-4.el6.x86_64.rpm                                                                                     | 705 kB     00:06
--------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                             130 kB/s |  18 MB     02:22
warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID cf4c4ff9: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-webtatic-andy
Importing GPG key 0xCF4C4FF9:
 Userid : Andy Thompson
 Package: webtatic-release-6-4.noarch (installed)
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-webtatic-andy
Is this ok [y/N]: y
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID c105b9de: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Importing GPG key 0xC105B9DE:
 Userid : CentOS-6 Key (CentOS 6 Official Signing Key)
 Package: centos-release-6-5.el6.centos.11.1.x86_64 (@anaconda-CentOS-201311272149.x86_64/6.5)
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : perl-DBI-1.609-4.el6.x86_64                                                                                                    1/6
  Installing : libmysqlclient16-5.1.69-1.w6.x86_64                                                                                            2/6
  Installing : mysql55-libs-5.5.34-1.w6.x86_64                                                                                                3/6
  Installing : mysql55-5.5.34-1.w6.x86_64                                                                                                     4/6
  Installing : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                                              5/6
  Installing : mysql55-server-5.5.34-1.w6.x86_64                                                                                              6/6
  Verifying  : libmysqlclient16-5.1.69-1.w6.x86_64                                                                                            1/6
  Verifying  : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                                              2/6
  Verifying  : mysql55-libs-5.5.34-1.w6.x86_64                                                                                                3/6
  Verifying  : perl-DBI-1.609-4.el6.x86_64                                                                                                    4/6
  Verifying  : mysql55-5.5.34-1.w6.x86_64                                                                                                     5/6
  Verifying  : mysql55-server-5.5.34-1.w6.x86_64                                                                                              6/6

Installed:
  mysql55.x86_64 0:5.5.34-1.w6                                         mysql55-server.x86_64 0:5.5.34-1.w6

Dependency Installed:
  libmysqlclient16.x86_64 0:5.1.69-1.w6  mysql55-libs.x86_64 0:5.5.34-1.w6  perl-DBD-MySQL.x86_64 0:4.013-3.el6  perl-DBI.x86_64 0:1.609-4.el6

Complete!

After installing, the commands needed to activate it to run at system startup depend on your operating system. On Linux, you can use chkconfig:

[root@localhost ~]# chkconfig --add mysqld

On some Linux systems, the following command also seems to be necessary to fully enable the mysql script:

[root@localhost ~]# chkconfig --level 345 mysqld on

You can now start MySQL as a service.

[root@localhost bin]# service mysqld start
Starting mysqld:                                           [  OK  ]
[root@localhost bin]# ps ax | grep mysql
26597 pts/2    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
26804 pts/2    Sl     0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
26836 pts/2    S+     0:00 grep mysql
[root@localhost bin]#








Tuesday, December 11, 2012

MySQL: Localhost vs 127.0.0.1

Yesterday i was trying to connect to MySQL localhost with another port aside from 3306:

mysql -uroot -p -h localhost --port=3390

to my suprise i was login to the default port. My collegue told me that i need to used 127.0.0.1 instead of localhost so i tried it then i search everywhere to find the answer on why oh why its like this until i found a blog that explained the difference between localhost and 127.0.0.1.

source: http://blogs.sakienvirotech.com/index.php/random/2011/08/30/mysql-101-connecting-to-a

With network connections, there are two special addresses that always refer to the machine you are currently using. This is either called 'localhost' or has the IP address of 127.0.0.1.  In general using these is equivalent and interchangeable, and uses a special path through the networking subsystem called the local loopback interface. However MySQL has reserved 'localhost' for the special purpose of indicating the use of a UNIX socket and not use networking.  This can cause some confusion to new users, but if you understand what is going on it is pretty clear.
'localhost' in MySQL uses the UNIX socket and ignores any port you may supply.
127.0.0.1 in MySQL uses networking and requires a port, using the default if you don't supply it.



Tuesday, November 20, 2012

ERROR 1018 (HY000): Can't read dir of '.' (errno: 24)

We encountered an error in MySQl below:

ERROR 1018 (HY000): Can't read dir of '.' (errno: 24)

Upon searching for some solutions at google we found out that the error means 'Too many open files'. Everything you do in linux is a file (or rather a file descriptor). Every mysql connection, every apache connection etc.

To see how many files a user has open try this

sudo lsof -u mysql


edit the file in /etc/security/limits.conf and add these two mysql lines in at the bottom



#@student        -       maxlogins       4
mysql   soft    nofile  4096
mysql   hard    nofile  8192
# End of file



There is some debate as to whether you need to reboot for this to take effect (i say debate, i rebooted, and didnt google for it as i was in a rush ymmv).
Once this is set you need to make some changes in the mysql file as well. Assuming yours lives in /etc/mysql/my.cnf - edit or add these values

open-files-limit = 2048
table_cache            = 512

The table_cache should be researched (start here: http://dev.mysql.com/doc/refman/5.0/en/table-cache.html) and changes monitored. The default value is 64, so 512 should be a safe bet to begin with.


Thursday, August 2, 2012

MySQL anonymous user login

After a fresh install of mysql at our linux box server. I was able to login to mysql with any user which we didn't defined. 
[root@spica ~]# mysql -utest

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.61 Source distribution


Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> quit
Bye

To fix you need to delete the blank user at the mysql.user tables.
[root@spica ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+------+------------+
| user | host       |
+------+------------+
| root | 127.0.0.1  |
|      | localhost  |
| root | localhost  |
|      | meitnerium |
| root | meitnerium |
+------+------------+
5 rows in set (0.00 sec)

mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+------+------------+
| user | host       |
+------+------------+
| root | 127.0.0.1  |
| root | localhost  |
| root | meitnerium |
+------+------------+
3 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@spica ~]# mysql -utest
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)
[root@spica ~]#

Wednesday, August 1, 2012

crontab: command not found


Upon issuing crontab command got an error below


[root@shizune installer]# crontab -e
-bash: crontab: command not found


To Fix install crontabs package.


[root@shizune installer]# yum --enablerepo=remi install crontabs
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
 * base: ossm.utm.my
 * extras: ossm.utm.my
 * remi: remi-mirror.dedipower.com
 * updates: centosk3.centos.org
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package crontabs.noarch 0:1.10-33.el6 will be installed
--> Processing Dependency: /etc/cron.d for package: crontabs-1.10-33.el6.noarch
--> Running transaction check
---> Package cronie.x86_64 0:1.4.4-7.el6 will be installed
--> Processing Dependency: dailyjobs for package: cronie-1.4.4-7.el6.x86_64
--> Running transaction check
---> Package cronie-anacron.x86_64 0:1.4.4-7.el6 will be installed
--> Finished Dependency Resolution


Dependencies Resolved


==================================================================================================================================================================
 Package                                     Arch                                Version                                  Repository                         Size
==================================================================================================================================================================
Installing:
 crontabs                                    noarch                              1.10-33.el6                              base                               10 k
Installing for dependencies:
 cronie                                      x86_64                              1.4.4-7.el6                              base                               70 k
 cronie-anacron                              x86_64                              1.4.4-7.el6                              base                               29 k


Transaction Summary
==================================================================================================================================================================
Install       3 Package(s)


Total download size: 110 k
Installed size: 211 k
Is this ok [y/N]: y
Downloading Packages:
(1/3): cronie-1.4.4-7.el6.x86_64.rpm                                                                                                       |  70 kB     00:00
(2/3): cronie-anacron-1.4.4-7.el6.x86_64.rpm                                                                                               |  29 kB     00:00
(3/3): crontabs-1.10-33.el6.noarch.rpm                                                                                                     |  10 kB     00:00
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                             102 kB/s | 110 kB     00:01
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : cronie-1.4.4-7.el6.x86_64                                                                                                                      1/3
  Installing : crontabs-1.10-33.el6.noarch                                                                                                                    2/3
  Installing : cronie-anacron-1.4.4-7.el6.x86_64                                                                                                              3/3
  Verifying  : crontabs-1.10-33.el6.noarch                                                                                                                    1/3
  Verifying  : cronie-1.4.4-7.el6.x86_64                                                                                                                      2/3
  Verifying  : cronie-anacron-1.4.4-7.el6.x86_64                                                                                                              3/3


Installed:
  crontabs.noarch 0:1.10-33.el6


Dependency Installed:
  cronie.x86_64 0:1.4.4-7.el6                                                 cronie-anacron.x86_64 0:1.4.4-7.el6


Complete!




[root@shizune installer]# service crond start
Starting crond:                                            [  OK  ]
Google