Results 1 to 4 of 4
  1. #1
    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.

  2. #2
    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.

  3. #3
    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

  4. #4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •