Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have two servers A and B I tried to setup replication between two servers for fixed dbs DBA and DBB but it's not working my configurations Tables are not getting updated why??

Master:(/etc/my.cnf)
server-id=4233865
log-bin=mysqld-bin
log-error=mysql-bin.err
relay-log=mysqld-relay-bin

Slave:(/etc/my.cnf)
symbolic-links=0
server-id=1359390567
relay-log-index = slave-relay-bin.index
relay-log   = slave-relay-bin
replicate-do-db=DBA,DBB

Show Slave Status

Slave_IO_State  Waiting for master to send event
Master_Host     10.0.0.163
Master_User     repl
Master_Port     3306
Connect_Retry   60
Master_Log_File     mysqld-bin.000007
Read_Master_Log_Pos     770800
Relay_Log_File  slave-relay-bin.000017
Relay_Log_Pos   770947
Relay_Master_Log_File   mysqld-bin.000007
Slave_IO_Running    Yes
Slave_SQL_Running   Yes
Replicate_Do_DB     DBA,DBB
Replicate_Ignore_DB     
Replicate_Do_Table  
Replicate_Ignore_Table  
Replicate_Wild_Do_Table     
Replicate_Wild_Ignore_Table     
Last_Errno  0
Last_Error  
Skip_Counter    0
Exec_Master_Log_Pos     770800
Relay_Log_Space     1134838
Until_Condition     None
Until_Log_File  
Until_Log_Pos   0
Master_SSL_Allowed  No
Master_SSL_CA_File  
Master_SSL_CA_Path  
Master_SSL_Cert     
Master_SSL_Cipher   
Master_SSL_Key  
Seconds_Behind_Master   0

Master Status

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000007 |   608837 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

And I am able to login with the replication user I created in Master from Slave. for the user I gave REPLICATION_SLAVE on % PERMISSIONS.

Am I missing out anything. No Error in Logs

UPDATE

Processes ID User Host Database Command Time Status SQL query

(Kill) (2) (system user) (None) (Connect) (149410) (Waiting for master to send event) (---)

Kill 1 system user None Connect -38185 Slave has read all relay log; waiting for the slave I/O thread to update it ---

As you can see SQL query is always --- why??

share|improve this question

1 Answer 1

up vote 3 down vote accepted

For debugging purposes I would use a user on the slave that has full permissions. After things were successfully replicating I'd try resetting the replication users permissions.

The main thing I see that's off is that the slave's expecting the master to be at a log position of 770800, but the master's actual position is 608837. The slave should never be ahead of the master.

You need to "start over" the replication configuration process, as in stop the slave, lock master, dump master, restore master on slave, setup replication on both, then unlock the slave. I've had to do this every time replication breaks. :(

Update 1:

Here are some notes I took on MySQL replication a while ago.

Enabling Replication:

Unfortunately, configuring MySQL replication can not be accomplished without completely locking up the MASTER database in order to obtain a static position in the the binary log for the SLAVEs to start from.

The scope of this configuration walk through is the configuring of the MASTER and SLAVE after the /etc/my.cnf file has been configured properly and the appropriate accounts have been setup on the MASTER and SLAVE servers.

In order to configure MySQL replication you will need to have two terminal sessions open on the MASTER and one terminal session open on the SLAVE. Terminal 1 and 2 will refer to the two sessions open on the MASTER. Terminal 3 will refer to the SLAVE session.

  1. On Terminal 1:

    • Connect to MySQL on the MASTER using the command line client.
    • Execute the query FLUSH TABLES WITH READ LOCK;
    • This will completely lock up the MASTER.
    • DO NOT EXIT FROM THE MYSQL CLIENT.
  2. On Terminal 2:

    • Perform a mysqldump on the database targeted for replication.
    • Ensure the database dump is either on shared storage or is copied to the slave.
    • Connect to MySQL on the MASTER using the command line client.
    • Execute the query SHOW MASTER STATUS;
    • Make note of the File and Position columns.
  3. On Terminal 3:

    • Restore the database dump that you just took from the MASTER.
    • Connect to MySQL on the SLAVE using the command line client.
    • Execute the query CHANGE MASTER TO MASTER_HOST='<master host>', MASTER_USER='<replication user>', MASTER_PASSWORD='<replication password>', MASTER_LOG_FILE='<Filename obtained from Terminal 2>', MASTER_LOG_POS=<Position obtained from Terminal 2>;​
    • Replication should now be enabled; however, the log position should not be incrementing.
    • If replication doesn't start properly make sure to check the mysqld log file.
    • Execute SHOW SLAVE STATUS;
    • Verify that the Master_Log_File and Read_Master_Log_Pos columns match the File and Position columns noted from Terminal 2.
  4. On Terminal 1:

    • As queries come in, execute the query SHOW MASTER STATUS; and note the File and Position columns.
  5. On Terminal 3:

    • Immediately after executing HOW MASTER STATUS; on Terminal 1, execute SHOW SLAVE STATUS; and compare the Master_Log_File and Read_Master_Log_Pos columns to File and Position on Terminal 1 respectively.

Repeat steps 4 and 5 a few times. If the file and position closely match then you've succeeded in enabling replication. Congratulations.

Disabling Replication: To disable replication on the MASTER, you must edit /etc/my.cnf and comment out the appropriate lines (see references for more information on the applicable lines).

To disable replication on the SLAVE you must execute the query STOP SLAVE;.

To re-enable replication the SLAVE you must execute the query START SLAVE;.

Troubleshooting Replication:

Occasionally, replication will break. This is typically caused by a surge in UPDATE queries. When replication breaks, the binary log on the master and slave begin to grow at an exponential rate. If the root cause of the surge in queries is not identified and extinguished immediately, then the disk space of the MySQL server is typically fully consumed in less than 10 hours.

The quickest way to ensure that your storage isn't exhausted is completely disable MySQL replication on the MASTER and shutdown MySQL on the SLAVE.

If your storage has started filling up, after you've disabled replication on the master and slave, feel free to remove all the mysql-bin and mysqld-relay-bin files on both the MASTER and SLAVE. These files are typically located in /var/lib/mysql when using RPM based MySQL distributions. The name of the binary log files is configurable in the global my.cnf, so if the files don't exist, look in the config file.

After breakage has occurred and you've disabled replication, you must go through the entire detailed earlier in this article to re-enable replication.

References:

share|improve this answer
    
I tried this but not working this is the status I have updated the status from phpmyadmin.I don't see my SQL thread running?? is it fine?? –  harish.venkat Feb 4 '13 at 9:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.