Archive for September, 2013

Innotop installation on Centos and Redhat machines for MySQL performance:

Dependencies:
perl-DBI
perl-DBD-MySQL
perl-TermReadKey
perl-Time-HiRes

Step 1: Installation of dependencies:

      # yum install perl-DBI perl-DBD-MySQL perl-TermReadKey perl-Time-HiRes

Step 2: Download RPM:
     # wget ftp://ftp.muug.mb.ca/mirror/fedora/epel/6/x86_64/innotop-1.9.0-3.el6.noarch.rpm

Step 3: Now install this rpm with,
     # rpm -ivh innotop-1.9.0-3.el6.noarch.rpm

Step 4: Now check the database or mysql performance with

     # innotop -u root -p password

 

 

Mysqlhotcopy

Posted: September 18, 2013 in Uncategorized

Mysqlhotcopy command is used to take the backup of one database. 

Syntax:

# mysqlhotcopy database1 database2

Note: Dont use .(dot) or – (hyphen) in database2, It throws error. 

From the above command it takes the backup of database1 with the name of database2. Simply it creates the copy of existed database.

This can be used to rename the database as well but we need to drop the old database if we don’t need it all.

For suppose:

I have one database with the name test. I can rename it as follows

# mysqlhotcopy test live

Now connect to DB and check the databases. We find a database with the name of live, which is a copy of test database. 

 

# mysqlhotcopy mytestdb

creates a copy of database mytestdb with the name mytestdb_copy if we dont use any suffix option while running the above command. By default, suffix will be _copy. 

If we want to change suffix then, 

# mysqlhotcopy mytestdb –suffix=mytestdb_mydb

 

MySQL Master-Master replication:
Master-1 IP: 0.0.0.1
Master-2 IP: 0.0.0.2

Master-1 my.cnf configuration:

log-bin=/usr/local/mysql/log/mysql-bin

log_warnings

log_slow_queries = /usr/local/mysql/log/slow.log

long_query_time = 5

log_long_format

tmpdir = /tmp

server-id = 1

log_slave_updates

replicate-same-server-id = 0
auto_increment_increment = 10
auto_increment_offset = 1
relay-log = mysql-relay-bin

Master-2 my.cnf configuration:

log-bin=/usr/local/mysql/log/mysql-bin

log_warnings

log_slow_queries = /usr/local/mysql/log/slow.log

long_query_time = 5

log_long_format

tmpdir = /tmp

server-id = 2

replicate-same-server-id = 0

auto_increment_increment = 2

auto_increment_offset = 2

relay-log = mysql-relay-bin

Replication procedure:

1st setup Master1 as Master and Master2 as slave for Master-1:

Follow below steps:
On Master-1:

Step 1: mysql> grant replication slave on *.* to ‘replicationuser1’@’0.0.0.2’ identified by ‘password’;

Step 2: mysql> show master status;
It shows file name and position, Use these records on Master-2 to run it as slave for Master-1.

Step 3: Now log on to master-2 and run the below query:

CHANGE MASTER TO MASTER_HOST=’0.0.0.1′, MASTER_USER=’replicationuser1′,MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000017′,MASTER_LOG_POS=751;

Step 4: start slave
Step 5: show slave status \G

On this status, the following 2 records should be as follows
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

These 2 records indicates Replication status. If these parameters show “Yes” that means replication is running successfully.

Setup Master2 as Master and Master1 as slave for Master-2: 

On Master-2 server:

Step 1: mysql> grant replication slave on *.* to ‘replicationuser2’@’0.0.0.1’ identified by ‘password’;

Step 2: mysql> show master status;

Step 3: Now log on to master-1 and run the below query:
CHANGE MASTER TO MASTER_HOST=’0.0.0.2′, MASTER_USER=’replicationuser2′,MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000002′,MASTER_LOG_POS=536;
Step 4: start slave
Step 5: show slave status \G

The following parameters should show “Yes”, so that replication is running successfully

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

On both servers “slave_IO_Running” and “slave_SQL_Running” parameters should always be “Yes” for successful Master-Master Replication.

MySQL binary installation:

Get a binary from http://downloads.skysql.com/archive/index/p/mysql

Step 1: ]# cd /usr/local/
Step 2: Copy the binary to this path and extract it
]# tar -xvzf mysql-5.5.16-linux2.6-x86_64.tar.gz

Step 3: Rename the extracted directory or create a softlink with the name mysql
For rename: ]# mv mysql-5.0.67-linux-x86_64-glibc23 mysql
For softlink: ]#ln -s mysql-5.0.67-linux-x86_64-glibc23 mysql

Step 4: ]# chown -R mysql:mysql /usr/local/mysql
Step 5: ]# scripts/mysql_install_db –user=mysql –datadir=/usr/local/mysql/data
Step 6: ]# cp support-files/my-medium.cnf /etc/my.cnf
Step 7: ]# ./bin/mysqld_safe –user=mysql & (Then Hit enter)
Step 8: ]# cp support-files/mysql.server /etc/init.d/mysql
Step 9: ]# /etc/init.d/mysql status/stop/start/restart
Step 10: To log on to mysql
]#/usr/local/mysql/bin/mysql and hit enter

Shell script for mysql to be connected directly from anywhere of the path

Actually mysql is to be started as /usr/local/mysql/bin/mysql that could be exported as follows

Create a file with the name of mysql.sh at the path /etc/profile.d/

~]# vi /etc/profile.d/mysql.sh

Add following lines to the script. So that mysql can logged from any path instead of going to installation or base path

#!/bin/sh

PATH=$PATH:/usr/local/mysql/bin

export PATH

Save the file and quit,
Then follow below steps

~]# su root

~]# export $PATH

Then path will be exported. Now you can log on to mysql irrespctive of the path