If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > MySQL > MySQL 5.5 Replication Issue

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: May 2012
Posts: 22
MySQL 5.5 Replication Issue

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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2012
Posts: 22
Following is my hostname.err file:


130201 15:43:23 [Note] Event Scheduler: Purging the queue. 0 events
130201 15:43:23 InnoDB: Starting shutdown...
130201 15:43:24 InnoDB: Shutdown completed; log sequence number 9595536
130201 15:43:24 [Note] /usr/sbin/mysqld: Shutdown complete

130201 15:43:24 mysqld_safe mysqld from pid file /var/lib/mysql/DBhost2.DBdomain2.pid ended
130201 15:46:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130201 15:46:40 [Note] Plugin 'FEDERATED' is disabled.
130201 15:46:40 InnoDB: The InnoDB memory heap is disabled
130201 15:46:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130201 15:46:40 InnoDB: Compressed tables use zlib 1.2.3
130201 15:46:40 InnoDB: Using Linux native AIO
130201 15:46:40 InnoDB: Initializing buffer pool, size = 128.0M
130201 15:46:40 InnoDB: Completed initialization of buffer pool
130201 15:46:40 InnoDB: highest supported file format is Barracuda.
130201 15:46:40 InnoDB: Waiting for the background threads to start
130201 15:46:41 InnoDB: 1.1.8 started; log sequence number 9595536
130201 15:46:41 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130201 15:46:41 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130201 15:46:41 [Note] Server socket created on IP: '0.0.0.0'.
130201 15:46:41 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=DBhost2-relay-bin' to avoid this problem.
130201 15:46:41 [ERROR] Failed to open the relay log './DBhost-relay-bin.000001' (relay_log_pos 4)
130201 15:46:41 [ERROR] Could not find target log during relay log initialization
130201 15:46:41 [ERROR] Failed to initialize the master info structure
130201 15:46:41 [Note] Event Scheduler: Loaded 0 events
130201 15:46:41 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.29-enterprise-commercial-advanced' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Enterprise Server - Advanced Edition (Commercial)
130201 15:46:58 [Note] /usr/sbin/mysqld: Normal shutdown

130201 15:46:58 [Note] Event Scheduler: Purging the queue. 0 events
130201 15:46:58 InnoDB: Starting shutdown...
130201 15:46:59 InnoDB: Shutdown completed; log sequence number 9595536
130201 15:46:59 [Note] /usr/sbin/mysqld: Shutdown complete

130201 15:46:59 mysqld_safe mysqld from pid file /var/lib/mysql/DBhost2.DBdomain2.pid ended
130201 15:47:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130201 15:47:03 [Note] Plugin 'FEDERATED' is disabled.
130201 15:47:03 InnoDB: The InnoDB memory heap is disabled
130201 15:47:03 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130201 15:47:03 InnoDB: Compressed tables use zlib 1.2.3
130201 15:47:03 InnoDB: Using Linux native AIO
130201 15:47:03 InnoDB: Initializing buffer pool, size = 128.0M
130201 15:47:03 InnoDB: Completed initialization of buffer pool
130201 15:47:03 InnoDB: highest supported file format is Barracuda.
130201 15:47:03 InnoDB: Waiting for the background threads to start
130201 15:47:04 InnoDB: 1.1.8 started; log sequence number 9595536
130201 15:47:04 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130201 15:47:04 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130201 15:47:04 [Note] Server socket created on IP: '0.0.0.0'.
130201 15:47:04 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=DBhost2-relay-bin' to avoid this problem.
130201 15:47:04 [ERROR] Failed to open the relay log './DBhost-relay-bin.000001' (relay_log_pos 4)
130201 15:47:04 [ERROR] Could not find target log during relay log initialization
130201 15:47:04 [ERROR] Failed to initialize the master info structure
130201 15:47:04 [Note] Event Scheduler: Loaded 0 events
130201 15:47:04 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.29-enterprise-commercial-advanced' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Enterprise Server - Advanced Edition (Commercial)

Also, following is my MySQL.err file:

130130 12:49:35 mysqld_safe mysqld from pid file /var/lib/mysql/DBhost2.DBdomain2.pid ended
130201 15:43:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130201 15:43:24 [Note] Plugin 'FEDERATED' is disabled.
130201 15:43:24 InnoDB: The InnoDB memory heap is disabled
130201 15:43:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130201 15:43:24 InnoDB: Compressed tables use zlib 1.2.3
130201 15:43:24 InnoDB: Using Linux native AIO
130201 15:43:24 InnoDB: Initializing buffer pool, size = 128.0M
130201 15:43:24 InnoDB: Completed initialization of buffer pool
130201 15:43:24 InnoDB: highest supported file format is Barracuda.
130201 15:43:24 InnoDB: Waiting for the background threads to start
130201 15:43:25 InnoDB: 1.1.8 started; log sequence number 9595536
130201 15:43:25 [ERROR] /usr/sbin/mysqld: unknown variable 'master-host=192.168.7.132'
130201 15:43:25 [ERROR] Aborting

130201 15:43:25 InnoDB: Starting shutdown...
130201 15:43:26 InnoDB: Shutdown completed; log sequence number 9595536
130201 15:43:26 [Note] /usr/sbin/mysqld: Shutdown complete

130201 15:43:26 mysqld_safe mysqld from pid file /var/lib/mysql/DBhost2.DBdomain2.pid ended


Please let me know if anybody knows the way out since I am badly stuck. Will be really thankful.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 860
Try removing the following:

master-host=IPAddress
master-connect-retry=60
master-user=root
master-password=password

These will get created in master.info. Have a look at the following MySQL 5.5 Server as Replication Slave Code-Kolbold Code Talk
__________________
Ronan Cashell
Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
http://www.it-iss.com
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2012
Posts: 22
Yeah I did that already, and some other tweaks, and I got it working. The two servers are working completely fine.

Thank you for your reply
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On