MySQL 5.5 Replication Issue

[Rabail Abbasi]

Hi everyone,

I am trying to do a POC on MySQL 5.5.29 enterprise edition (Trial version). For this purpose I set up two virtual machines, with same OS (RHEL 6) and MySQL version. I installed MySQL 5.5 enterprise trial edition on first virtual machine, created the database and then copied this virtual machine to another physical machine.

To set up Master Server, I created a new my.cnf file in /etc as there was no my.cnf file in the system. I added the following details to my.cnf file:

[mysqld]
server-id = 1
binlog-do-db=dbname
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

Afterwards I did the following.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 107 | dbname | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> quit;

NOW, I tried to configure Slave Server by creating and then editing the my.cnf file in /etc

[mysqld]
server-id = 2
master-host=IPAddress
master-connect-retry=60
master-user=root
master-password=password
replicate-do-db=dbname
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

Now when I restart the MySQL service in Slave Server, I get the following error:

[root@DBhost2 ~]# /etc/init.d/mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL...The server quit without updating PID file (/var/lib/mysql/DBhost2.DBdomain2.pid). [FAILED]

After tweaking a little bit I realized that when I remove the my.cnf file from slave server or keep an empty file, the MySQL service restarts successfully.

[root@DBhost2 ~]# /etc/init.d/mysql stop
Shutting down MySQL. [ OK ]
[root@DBhost2 ~]# /etc/init.d/mysql start
Starting MySQL.. [ OK ]
[root@DBhost2 ~]#

It doesn't work with the my.cnf configurations. I am stuck and I don't know how to solve this issue.

Please let me know if anybody knows how to make it work.

Thanks in advance.