Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote: You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) Master: +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.001225 | 107 | | | +--+--+--+--+ 1 row in set (0.00 sec) Slave * 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001225 Read_Master_Log_Pos: 107 Relay_Log_File: ubuntu2-relay-bin.02 Relay_Log_Pos: 16426 Relay_Master_Log_File: mysql-bin.001139 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Skip_Counter: 0 Exec_Master_Log_Pos: 90012430 Relay_Log_Space: 29727610 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Replicate_Ignore_Server_Ids: Master_Server_Id: 3 1 row in set (0.00 sec) 1 transaction entered: Master: +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.001225 | 1837 | | | +--+--+--+--+ 1 row in set (0.00 sec) SLave *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001225 Read_Master_Log_Pos: 1837 Relay_Log_File: ubuntu2-relay-bin.02 Relay_Log_Pos: 16426 Relay_Master_Log_File: mysql-bin.001139 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Skip_Counter: 0 Exec_Master_Log_Pos: 90012430 Relay_Log_Space: 29729340 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Replicate_Ignore_Server_Ids: Master_Server_Id: 3 1 row in set (0.00 sec)
Re: Replication problem
On 8/29/2014 5:11 PM, wagnerbianchi.com wrote: Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? It was not, I reconfigured and restarted mysql and... 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? Not - see prior post 3-) Why are you reconfiguring all the replication just because the link went down? AFAIK, I am no reconfiguring, after the link comes back up, the slave does not start replicating - sometimes. Happens quite infrequently. I intend to be just restoring the database and restarting replication Cheers, Thanks, I need the cheers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote: You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) after a more complex transaction; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.001225 | 5952 | | | +--+--+--+--+ *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001225 Read_Master_Log_Pos: 5952 Relay_Log_File: ubuntu2-relay-bin.02 Relay_Log_Pos: 16426 Relay_Master_Log_File: mysql-bin.001139 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Skip_Counter: 0 Exec_Master_Log_Pos: 90012430 Relay_Log_Space: 29733455 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Replicate_Ignore_Server_Ids: Master_Server_Id: 3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
There's a duplicate key on the audit table, 18699. Delete it and restart slave (start slave). Check slave status again, might be more rows in there duplicated. You might want to compare the row to master to ensure it's a duplicate before deleting from slave. On Aug 30, 2014 7:52 AM, william drescher will...@techservsys.com wrote: On 8/29/2014 7:40 PM, Suresh Kuna wrote: You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) after a more complex transaction; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.001225 | 5952 | | | +--+--+--+--+ *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001225 Read_Master_Log_Pos: 5952 Relay_Log_File: ubuntu2-relay-bin.02 Relay_Log_Pos: 16426 Relay_Master_Log_File: mysql-bin.001139 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Skip_Counter: 0 Exec_Master_Log_Pos: 90012430 Relay_Log_Space: 29733455 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Replicate_Ignore_Server_Ids: Master_Server_Id: 3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote: You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) global var, below +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.001225 | 8919 | | | +--+--+--+--+ *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001225 Read_Master_Log_Pos: 8919 Relay_Log_File: ubuntu2-relay-bin.02 Relay_Log_Pos: 16426 Relay_Master_Log_File: mysql-bin.001139 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Skip_Counter: 0 Exec_Master_Log_Pos: 90012430 Relay_Log_Space: 29736422 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Replicate_Ignore_Server_Ids: Master_Server_Id: 3 mysql show global variables like 'log-bin%'; Empty set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Based on the SHOW SLAVE STATUS output you've sent us, I'd suggest that you check what the application is doing, understand *why* the application is violating the PK of the table information_server.audit, repair the possible application problem and the, reconfigure the replication. It seems that there's something not really good happening on the application side since the error 1062 is being threw out by a INSERT sent to the database ... by the application. *PS.: looking at the purpose of information_server.audit, I wonder if this is not a *design* problem!* 1-) Fix the application if you can, investigating the problem the replication is showing up on SHOW SLAVE STATUS - remember that the column Last_SQL_Error is one of SHOW SLAVE STATUS best friends in this context; 2-) After to fix the problem, if design or application, recreate the slave and then, start replication again; It's possible to use another things to make the replication to bypass this kind of problem, but, it's not that cool to have it configured. due to that, I'd like to omit it at this point. Let's keep in touch, happy mysql'ing!! -- *Wagner Bianchi* 2014-08-30 9:54 GMT-03:00 Johnny Withers joh...@pixelated.net: There's a duplicate key on the audit table, 18699. Delete it and restart slave (start slave). Check slave status again, might be more rows in there duplicated. You might want to compare the row to master to ensure it's a duplicate before deleting from slave. On Aug 30, 2014 7:52 AM, william drescher will...@techservsys.com wrote: On 8/29/2014 7:40 PM, Suresh Kuna wrote: You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) after a more complex transaction; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.001225 | 5952 | | | +--+--+--+--+ *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001225 Read_Master_Log_Pos: 5952 Relay_Log_File: ubuntu2-relay-bin.02 Relay_Log_Pos: 16426 Relay_Master_Log_File: mysql-bin.001139 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Skip_Counter: 0 Exec_Master_Log_Pos: 90012430 Relay_Log_Space: 29733455 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Replicate_Ignore_Server_Ids: Master_Server_Id: 3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 30/08/14 09:39, william drescher wrote: On 8/29/2014 7:40 PM, Suresh Kuna wrote: You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) Master: +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.001225 | 107 | | | +--+--+--+--+ 1 row in set (0.00 sec) Slave * 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001225 Read_Master_Log_Pos: 107 Relay_Log_File: ubuntu2-relay-bin.02 Relay_Log_Pos: 16426 Relay_Master_Log_File: mysql-bin.001139 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Skip_Counter: 0 Exec_Master_Log_Pos: 90012430 Relay_Log_Space: 29727610 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Replicate_Ignore_Server_Ids: Master_Server_Id: 3 1 row in set (0.00 sec) 1 transaction entered: Master: +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.001225 | 1837 | | | +--+--+--+--+ 1 row in set (0.00 sec) SLave *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001225 Read_Master_Log_Pos: 1837 Relay_Log_File: ubuntu2-relay-bin.02 Relay_Log_Pos: 16426 Relay_Master_Log_File: mysql-bin.001139 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Skip_Counter: 0 Exec_Master_Log_Pos: 90012430 Relay_Log_Space: 29729340 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert into audit (audit_type, subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 0, 'Login: WPD from location: Suite B')' Replicate_Ignore_Server_Ids: Master_Server_Id: 3 1 row in set (0.00 sec) Well , it seems that you have a 'Duplicate entry ' issue on slave, so the Slave_SQL_Running threads is locked :) , there's something wrong here with the initial slave status, maybe you have to rebuild the slave from scratch(backup or a dump from master), and be sure that you don't change anything on slave( phpmyadmin? ) Bye Julian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote: You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) mysql show global variables like 'log_bin%'; +-+---+ | Variable_name | Value | +-+---+ | log_bin | ON| | log_bin_trust_function_creators | OFF | +-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
On 30/08/14 12:56, william drescher wrote: Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill Just after you resync the slave, last error should be empty. Bye Julian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
On 8/30/2014 12:53 PM, Jose Julian Buda wrote: On 30/08/14 12:56, william drescher wrote: Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill Just after you resync the slave, last error should be empty. Bye Julian Thanks Julian, it is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? 3-) Why are you reconfiguring all the replication just because the link went down? Cheers, -- *WB* 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com: Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) On Fri, Aug 29, 2014 at 5:11 PM, wagnerbianchi.com m...@wagnerbianchi.com wrote: Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? 3-) Why are you reconfiguring all the replication just because the link went down? Cheers, -- *WB* 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com: Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- -- Thanks Suresh Kuna MySQL Database Consutant MongoDB DBA Hadoop Admin
Re: replication problem
On 12/3/2012 7:24 AM, Trimurthy wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. ... snipped ... This one should have been easy for the list to advise you about. Let me step in to keep things on track. For each instance of MySQL that creates binary logs there will be an 'index' file that keeps track of which files this instance is managing. For example, if the binary logs are called mysql-bin.00, then the index file will be called mysql-bin.index What has happened is that the contents of the index file no longer matches the inventory of actual binary logs present on disk. They have either been moved, erased, or had their permissions changed so that the system user that the mysqld daemon executes as can no longer see them. The solution is to manually update the .index file with the current listing of binary log files. The longer term solution is to stop manually removing binary log files and allow the system to perform that for you with a PURGE BINARY LOGS command. When you have MySQL do the purging, the .index file will be automatically updated. For more details about the binary log, please read: http://dev.mysql.com/doc/refman/5.5/en/binary-log.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication problem
HI share detail Slave_IO_Running: Yes/No Slave_SQL_Running: Yes/No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: On Tue, Dec 4, 2012 at 5:59 AM, Rick James rja...@yahoo-inc.com wrote: SHOW SLAVE STATUS\G SHOW MASTER STATUS; What directory are the binlogs in? -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Monday, December 03, 2012 8:53 AM To: trimur...@tulassi.com Cc: mysql@lists.mysql.com Subject: Re: replication problem Trimurthy, you will have to describe the method you are using to setup replication. The error message seems plain but an observer could not reasonably guess what caused it without more information. - michael dykman On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication problem
Trimurthy, you will have to describe the method you are using to setup replication. The error message seems plain but an observer could not reasonably guess what caused it without more information. - michael dykman On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: replication problem
SHOW SLAVE STATUS\G SHOW MASTER STATUS; What directory are the binlogs in? -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Monday, December 03, 2012 8:53 AM To: trimur...@tulassi.com Cc: mysql@lists.mysql.com Subject: Re: replication problem Trimurthy, you will have to describe the method you are using to setup replication. The error message seems plain but an observer could not reasonably guess what caused it without more information. - michael dykman On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication problem
Hi. Can you give me a flow of your master/slave? On Wed, Jun 24, 2009 at 7:45 PM, 赵琦 tyzha...@gmail.com wrote: hi all: I have three mysql database,tow run as master and the other one runs as slave. Some tables in the database have an autoincreament field named as 'rowid'. These tables have 100 records on master, but some of these tables on the slave only have thousands of record. The tables on the slave are not the same as those on master. When i use 'show slave status', i find that Last_Errno equals to 0. How to solve this problem. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: Replication problem
Hi, Jorge Martínez wrote: We have a windows server running our main database and an IIS server. We also have a linux box with apache/php that uses the same database server. We have configured a replication from that host to another windows server. Then we verified that not all the queries were replicated properly. We find out that some queries that come from the linux box and have cr/lf on them are not being replicated. ie: sql = select * from table_1; SELECT queries are not replicated. Only queries that change data are replicated. I think that may be some issues about how linux manages cr/lf. Whan can I do? thank you all! _ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication problem
I'm sorry. I have posted a wrong example. Here you have a better one: update table_1 \n set field_1 = 'xx', \n field_2 = 'yy'; \n Date: Tue, 21 Aug 2007 11:16:50 -0400 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Replication problem Hi, Jorge Martínez wrote: We have a windows server running our main database and an IIS server. We also have a linux box with apache/php that uses the same database server.We have configured a replication from that host to another windows server. Then we verified that not all the queries were replicated properly. We find out that some queries that come from the linux box and have cr/lf on them are not being replicated. ie: sql = select * from table_1; SELECT queries are not replicated. Only queries that change data are replicated. I think that may be some issues about how linux manages cr/lf. Whan can I do?thank you all! _ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Llama a tus amigos de PC a PC: ¡Es GRATIS! http://get.live.com/messenger/overview
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for another database. If you have even one of these you must make all updates in the default database if you want them to be replicated. This is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to 5.0 the same day which explains why we did not see this behavior sooner. I removed the option from the config file and just used replicate-ignore-db instead and all is right in the world again. Hope this helps someone else down the road. Thanks for all of your helpful information and suggestions. http://dev.mysql.com/doc/refman/5.0/en/binary-log.html -Ed Logan, David (SST - Adelaide) wrote: Hi Ed, You may like to look at this, especially the last statement. If you are not using these options however, I would suspect a bug. Regards --replicate-do-db=db_name Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database. An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated: USE prices; UPDATE sales.january SET amount=amount+1000; The main reason for this just check the default database behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need. If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 6.9, How Servers Evaluate Replication Rules. --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Wednesday, 13 December 2006 10:55 AM To: Ed Pauley II Cc: mysql@lists.mysql.com Subject: Re: Replication Problem? Ed, this is unfortunately by design. Personally I don't get why this choice was made... Reference: http://lists.mysql.com/mysql/190869 Regards, Atle FCI, Inc. - Unix Systems Administrator On Tue, 12 Dec 2006, Ed Pauley II wrote: We recently upgraded to MySQL 5.0. Since upgrading I have noticed that queries of the form INSERT INTO test.test_table VALUES('test','1') no longer replicate. If you connect to or change to the test database and then execute INSERT INTO test_table VALUES('test','1') the query replicates. Is this normal behavior? Is there a configuration setting that I can change to make replication accept explicit database naming in the query? Any help would be greatly appreciated! -- Ed Pauley II [EMAIL PROTECTED] -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for another database. If you have even one of these you must make all updates in the default database if you want them to be replicated. This is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to 5.0 the same day which explains why we did not see this behavior sooner. I removed the option from the config file and just used replicate-ignore-db instead and all is right in the world again. Hope this helps someone else down the road. Thanks for all of your helpful information and suggestions. http://dev.mysql.com/doc/refman/5.0/en/binary-log.html -Ed Logan, David (SST - Adelaide) wrote: Hi Ed, You may like to look at this, especially the last statement. If you are not using these options however, I would suspect a bug. Regards --replicate-do-db=db_name Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database. An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated: USE prices; UPDATE sales.january SET amount=amount+1000; The main reason for this just check the default database behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need. If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 6.9, How Servers Evaluate Replication Rules. --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Wednesday, 13 December 2006 10:55 AM To: Ed Pauley II Cc: mysql@lists.mysql.com Subject: Re: Replication Problem? Ed, this is unfortunately by design. Personally I don't get why this choice was made... Reference: http://lists.mysql.com/mysql/190869 Regards, Atle FCI, Inc. - Unix Systems Administrator On Tue, 12 Dec 2006, Ed Pauley II wrote: We recently upgraded to MySQL 5.0. Since upgrading I have noticed that queries of the form INSERT INTO test.test_table VALUES('test','1') no longer replicate. If you connect to or change to the test database and then execute INSERT INTO test_table VALUES('test','1') the query replicates. Is this normal behavior? Is there a configuration setting that I can change to make replication accept explicit database naming in the query? Any help would be greatly appreciated! -- Ed Pauley II [EMAIL PROTECTED] -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this choice was made... Reference: http://lists.mysql.com/mysql/190869 Regards, Atle FCI, Inc. - Unix Systems Administrator On Tue, 12 Dec 2006, Ed Pauley II wrote: We recently upgraded to MySQL 5.0. Since upgrading I have noticed that queries of the form INSERT INTO test.test_table VALUES('test','1') no longer replicate. If you connect to or change to the test database and then execute INSERT INTO test_table VALUES('test','1') the query replicates. Is this normal behavior? Is there a configuration setting that I can change to make replication accept explicit database naming in the query? Any help would be greatly appreciated! -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem?
Hi Ed, You may like to look at this, especially the last statement. If you are not using these options however, I would suspect a bug. Regards --replicate-do-db=db_name Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database. An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated: USE prices; UPDATE sales.january SET amount=amount+1000; The main reason for this just check the default database behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need. If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 6.9, How Servers Evaluate Replication Rules. --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Wednesday, 13 December 2006 10:55 AM To: Ed Pauley II Cc: mysql@lists.mysql.com Subject: Re: Replication Problem? Ed, this is unfortunately by design. Personally I don't get why this choice was made... Reference: http://lists.mysql.com/mysql/190869 Regards, Atle FCI, Inc. - Unix Systems Administrator On Tue, 12 Dec 2006, Ed Pauley II wrote: We recently upgraded to MySQL 5.0. Since upgrading I have noticed that queries of the form INSERT INTO test.test_table VALUES('test','1') no longer replicate. If you connect to or change to the test database and then execute INSERT INTO test_table VALUES('test','1') the query replicates. Is this normal behavior? Is there a configuration setting that I can change to make replication accept explicit database naming in the query? Any help would be greatly appreciated! -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Dave, I tried reset slave as you mentioned - no change. Nothing updating. Same error. Brett Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:28 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Dave, I tried reset slave as you mentioned - no change. Nothing updating. Same error. Brett Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- -- - - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- - - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives: http
RE: Replication Problem
David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 903763 Relay_Log_Space: 901275 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 1 row in set (0.00 sec) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com);
RE: Replication Problem
Hi Brett, Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 903763 Relay_Log_Space: 901275 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 1 row in set (0.00 sec) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, I could try and create a table. But I've got it set just for the one database FMS. The error log, last night, was showing this (I think I had it in my first post) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed The two are on the same network, same switch in fact. I can do commands from one to the other without problem. The logs seem to update in that sense but when looking at the data - no go. I'll check binlogs on the slave. Brett Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 903763 Relay_Log_Space: 901275 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 1 row in set (0.00 sec) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 11:06 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, I could try and create a table. But I've got it set just for the one database FMS. The error log, last night, was showing this (I think I had it in my first post) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed The two are on the same network, same switch in fact. I can do commands from one to the other without problem. The logs seem to update in that sense but when looking at the data - no go. I'll check binlogs on the slave. Brett Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 903763 Relay_Log_Space: 901275 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
RE: Replication Problem
David, Thanks for attempting to help. When I look at my show slave status, its updating. When I look at the FMSbuilding-relay-bin, its updating! It has the information that's taken place on the master server. I can see information in the FMSbuilding-relay-bin on the slave server that I did on the master. But, the databases/tables/data on the slave are not being updated. Brett I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 25 July 2006 9:19 AM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, Thanks for attempting to help. When I look at my show slave status, its updating. When I look at the FMSbuilding-relay-bin, its updating! It has the information that's taken place on the master server. I can see information in the FMSbuilding-relay-bin on the slave server that I did on the master. But, the databases/tables/data on the slave are not being updated. Brett I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, It is pointing correctly. Plus, I can use a mysql browser and 3rd party tools on the slave server and read the databases/tables properly. I've tried stopping and restarting. I've rebooted... I'm baffled too, obviously. lol Thanks! Brett I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Another suggestion, look for other files on the system ending in .MYD or put a query through that you know has updated a particular table/database and do a find looking for a file that has been updated in the last 5 minutes. See which ones have. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 25 July 2006 10:18 AM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, It is pointing correctly. Plus, I can use a mysql browser and 3rd party tools on the slave server and read the databases/tables properly. I've tried stopping and restarting. I've rebooted... I'm baffled too, obviously. lol Thanks! Brett I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, that error is only when doing a stop/shutdown. So it doesnt make a lot of sense that would be the problem. Here's the log where I did a stop and start. 060724 20:54:25 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown 060724 20:54:25 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060724 20:54:25 [Note] Slave I/O thread killed while reading event 060724 20:54:25 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.01', position 5582202 060724 20:54:25 [Note] Error reading relay log event: slave SQL thread was killed 060724 20:54:28 [Warning] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Forcing close of thread 6 user: 'bharvey' 060724 20:54:28 [Warning] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Forcing close of thread 5 user: 'bharvey' 060724 20:54:28 InnoDB: Starting shutdown... 060724 20:54:29 InnoDB: Shutdown completed; log sequence number 0 44044 060724 20:54:29 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete 060724 20:55:10 InnoDB: Started; log sequence number 0 44044 060724 20:55:10 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.22-community-nt-log' socket: '' port: 3306 MySQL Community Edition (GPL) 060724 20:55:10 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.01' at position 5582202, relay log '.\FMSbuilding-relay-bin.05' position: 17820 060724 20:55:10 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.01' at position 5582202 I did some manual inserts into the database on the slave, just to make sure there was no problem - those worked fine and were searchable. Brett Are there any other messages apart from the 2013 error? Are there any options on the filesystem that are perhaps stopping any writing by the mysql user? At this point, I have to say I'm just about out of ideas. Regards -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem
Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar - Original Message - From: Brett Harvey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 24, 2006 8:49 AM Subject: Replication Problem Well, I successfully setup replication for one server/slave. Now I'm trying to do the same for another set. I've tried both the tar method and load data from master and can't seem to get either to work. Nothing replicates to the slave. I'm running Mysql 5.022. === My error log is showing this - note the error reading packet from server. 060723 21:01:01 InnoDB: Started; log sequence number 0 44044 060723 21:01:01 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.04' at position 349828, relay log '.\FMSbuilding-relay-bin.06' position: 236 060723 21:01:01 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.04' at position 349828 060723 21:01:01 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.21-community-nt-log' socket: '' port: 3306 MySQL Community Edition (GPL) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed 060723 21:01:41 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.04' at position 98, relay log '.\FMSbuilding-relay-bin.01' position: 4 060723 21:01:41 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.04' at position 98 After taring my Master Status was this: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.04 | 98 | | | +---+--+--+--+ 1 row in set (0.00 sec) Upon doing a Start Slave I had his for Slave Status show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 206.103.218.36 Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.04 Read_Master_Log_Pos: 349828 Relay_Log_File: FMSbuilding-relay-bin.02 Relay_Log_Pos: 349966 Relay_Master_Log_File: FMSweb-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 349828 Relay_Log_Space: 349966 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 1 row in set (0.00 sec) My Master Status is mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.04 | 349828 | | | +---+--+--+--+ 1 row in set (0.00 sec) === My Master Config is server-id=1 My Salve Config is server-id=2 replicate-do-db=FMS master-host=xxx.xxx.xxx.xxx master-user=FMSReplicate master-password=password relay-log=FMSbuilding-relay-bin log-bin=FMSbuilding-bin I did: CHANGE MASTER TO - MASTER_HOST='xxx.xxx.xxx.xxx', - MASTER_USER='TenenzReplicate', - MASTER_PASSWORD='password', - MASTER_LOG_FILE='FMSweb-bin.04', - MASTER_LOG_POS=98; any ideas? how do I get it to replicate properly? Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under
Re: Replication Problem
Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem
Hi, Check whether u have starting with the correct master position from slave. Thanks Regards Dilipkumar - Original Message - From: Brett Harvey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 24, 2006 9:36 AM Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hello. However, this does not seem to replicate to the slave, and no error shows Have you compared the slave's relay logs and master's binary logs? Does this query present in both logs? See: http://dev.mysql.com/doc/mysql/en/slave-logs.html http://dev.mysql.com/doc/mysql/en/Binary_log.html http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html Terry Riley [EMAIL PROTECTED] wrote: We are running 4.1.13 standard on Linux as a master, and 4.1.14 on NT4 as a slave. Replication seems to work extremely well, except in the following circumstance. On the master server, running a query similar to: insert into zmast.leagueinfo (countieslist, defaultleaguecode, leaguename ...) select countieslist, 'DDLS2005', leaguename .. from zmast.leagueinfo where defaultleaguecode='ddls2004'. using the same table as source and target, with the only changes being the defaultleaguecode and the autoincrement ID field, without any problem. However, this does not seem to replicate to the slave, and no error shows until later when an attempt is made to insert a record into a table where a relationship to this record is required ('Cannot update child record'-type message). The manual that I have does specify that currently you cannot insert into a table and select from the same table in a subquery. Clearly this is not actually the case, but for some reason, it will not replicate. Anyone else had/got this problem? Is there a sensible solution - the only one I can come up with is changing the intitial 'insert ... select' into a 'create table temp select from...'/'insert ... select from temp'/ 'drop table temp' set. This insert ... select stuff is not done on a regular basis, and then only by an administrator. Ideas welcome Cheers Terry -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
- Original Message - However, this does not seem to replicate to the slave, and no error shows Have you compared the slave's relay logs and master's binary logs? Does this query present in both logs? The hint was what I needed, Gleb. Spasibo. It turns out that the administrator doing this is using an elderly version of SQLyog to run his scripts, and either by accident or design, SQLyog doesn't make entries in the master bin-log - so no replication is ever going to happen. The query (actually a set of queries) don't appear in logs on either server. This GUI has been in use for over a year, but we only switched on the replication system a couple of weeks ago, and this is the first 'occasional' update - that was frequently used on the old master-only system without problems. Thanks again for the hint! Terry See: http://dev.mysql.com/doc/mysql/en/slave-logs.html http://dev.mysql.com/doc/mysql/en/Binary_log.html http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html Terry Riley [EMAIL PROTECTED] wrote: We are running 4.1.13 standard on Linux as a master, and 4.1.14 on NT4 as a slave. Replication seems to work extremely well, except in the following circumstance. On the master server, running a query similar to: insert into zmast.leagueinfo (countieslist, defaultleaguecode, leaguename ...) select countieslist, 'DDLS2005', leaguename .. from zmast.leagueinfo where defaultleaguecode='ddls2004'. using the same table as source and target, with the only changes being the defaultleaguecode and the autoincrement ID field, without any problem. However, this does not seem to replicate to the slave, and no error shows until later when an attempt is made to insert a record into a table where a relationship to this record is required ('Cannot update child record'-type message). The manual that I have does specify that currently you cannot insert into a table and select from the same table in a subquery. Clearly this is not actually the case, but for some reason, it will not replicate. Anyone else had/got this problem? Is there a sensible solution - the only one I can come up with is changing the intitial 'insert ... select' into a 'create table temp select from...'/'insert ... select from temp'/ 'drop table temp' set. This insert ... select stuff is not done on a regular basis, and then only by an administrator. Ideas welcome Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
If nothing helps, trace files could spill the light on your problem. many thanks for your help. With tracing I found the problem. Like almost every time it was just a typing error :-( Regards Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Hello. If nothing helps, trace files could spill the light on your problem. See: http://dev.mysql.com/doc/mysql/en/making-trace-files.html Jan Roehrich [EMAIL PROTECTED] wrote: --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Hello. Please send your configuration files for master and slave. Check that you don't have skip-networking in your slave's configuration file. Jan Roehrich [EMAIL PROTECTED] wrote: I have a strange problem setting up replication. I followed each step of setting up replication in the mysqld documentation (http://dev.mysql.com/doc/mysql/en/replication-howto.html). But there are some error messages on the salve: 050828 13:22:58 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'alturo.vlugnet.org' (13)' errno: 2003 retry-time: 60 retries: 86400 When I try to connect to the master using same hostname, same user, same password with the mysql client it works without problems. I tried to drag the problem with ethereal. It seems that the slave doesn't even try to connect to the master. There are not even connection attempts between master and slave on port 3306. Also have a look at http://forums.mysql.com/read.php?26,36766 it seems that he has the same problem. As slave I'm using mysql version 4.1.12 on Fedora Core 4. Master is 3.23.58 on Fedora Core 2. Any ideas? Regards Jan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Please send your configuration files for master and slave. Check that you don't have skip-networking in your slave's configuration file. Master: -- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=bin-log server-id=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- Slave: --- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 server-id=2 master-host=82.165.25.207 master-port=3306 master-user=replication master-password=hw4Gabfy [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- Could not see skip-networking anywhere. Greetings Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Hello. Could not see skip-networking anywhere. --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. Jan Roehrich [EMAIL PROTECTED] wrote: Please send your configuration files for master and slave. Check that you don't have skip-networking in your slave's configuration file. Master: -- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=bin-log server-id=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- Slave: --- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 server-id=2 master-host=82.165.25.207 master-port=3306 master-user=replication master-password=hw4Gabfy [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- Could not see skip-networking anywhere. Greetings Jan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
--skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Jan Roehrich wrote: --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ It can be a firewall problem... Are you sure that the slave didn't try to connect to the master, and got no response, or got a connection refused? Check the bin-log of the slave for errors in the bin-log. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hello. I suggest you to check what query was written to the master binary log and then report a bug. Please include all information you've found in the report. Weicheng Pan [EMAIL PROTECTED] wrote: Dear Gleb: The problem has occured again, and I grab some output. This box run mysql 4.1.11 on FreeBSD 5.4 AMD64, with 8GB RAM In this case, The original query is INSERT INTO LOW_PRIORTY INTO `statistics`.`search` (`userid`, `id`, `func`) VALUES('lty0105', '8', 2) The strange query is 'INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p) Slave server got a strange character 'p' after character '2' Slave: mysql show slave status\G; *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: db0 Master_User: repl Master_Port: 3306 Connect_Retry: 5 Master_Log_File: db0-bin.56 Read_Master_Log_Pos: 755005566 Relay_Log_File: db10-relay-bin.60 Relay_Log_Pos: 572461705 Relay_Master_Log_File: db0-bin.56 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: blog,album_database Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: album_database.%,blog.% Last_Errno: 1054 Last_Error: Error 'Unknown column '2p' in 'field list'' on query. Default database: ''. Query: 'INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p)' Skip_Counter: 0 Exec_Master_Log_Pos: 692584947 Relay_Log_Space: 634882324 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: NULL 1 row in set (0.00 sec) Master: mysql show master status\G; *** 1. row *** File: db0-bin.56 Position: 761127837 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) Finally I dump the slave log: [EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db10-relay-bin.60 | grep ( 'lty0105', '8', 2p) INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p); [EMAIL PROTECTED] [ /home/mysql ] With best regards, Weicheng. - Original Message --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Dear Gleb: I forgot to give the master's bin log dump data, The data writen in master log is correct: [EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db0-bin.56 | grep ( 'lty0105', '8', 2p) INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2 ); And the slave log: [EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db10-relay-bin.60 | grep ( 'lty0105', '8', 2p) INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p); I took your suggestion and have sent a report. And thank you very much. With best regards, Weicheng Pan Jun 3, 2005. - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 02, 2005 4:51 PM Subject: Re: Replication problem Hello. I suggest you to check what query was written to the master binary log and then report a bug. Please include all information you've found in the report. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Dear Gleb: The problem has occured again, and I grab some output. This box run mysql 4.1.11 on FreeBSD 5.4 AMD64, with 8GB RAM In this case, The original query is INSERT INTO LOW_PRIORTY INTO `statistics`.`search` (`userid`, `id`, `func`) VALUES('lty0105', '8', 2) The strange query is 'INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p) Slave server got a strange character 'p' after character '2' Slave: mysql show slave status\G; *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: db0 Master_User: repl Master_Port: 3306 Connect_Retry: 5 Master_Log_File: db0-bin.56 Read_Master_Log_Pos: 755005566 Relay_Log_File: db10-relay-bin.60 Relay_Log_Pos: 572461705 Relay_Master_Log_File: db0-bin.56 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: blog,album_database Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: album_database.%,blog.% Last_Errno: 1054 Last_Error: Error 'Unknown column '2p' in 'field list'' on query. Default database: ''. Query: 'INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p)' Skip_Counter: 0 Exec_Master_Log_Pos: 692584947 Relay_Log_Space: 634882324 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: NULL 1 row in set (0.00 sec) Master: mysql show master status\G; *** 1. row *** File: db0-bin.56 Position: 761127837 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) Finally I dump the slave log: [EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db10-relay-bin.60 | grep ( 'lty0105', '8', 2p) INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p); [EMAIL PROTECTED] [ /home/mysql ] With best regards, Weicheng. - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, May 30, 2005 5:20 PM Subject: Re: Replication problem Hello. Not enough information to make a conclusion. What version of MySQL do you use? Usually debugging of replication problems begins from researching of binary logs with mysqlbinlog. See: http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html http://dev.mysql.com/doc/mysql/en/slave-logs.html http://dev.mysql.com/doc/mysql/en/binary-log.html Please, next time include the output of SHOW SLAVE STATUS (executed on the slave) and SHOW MASTER STATUS (executed on the master) as well. Weicheng Pan [EMAIL PROTECTED] wrote: Hi all: I have replication problem on my FreeBSD 5.4 AMD64 box. Sometimes replication will stop and throw an error code 1054, but the query is not the original one in the master. A query like UPDATE table_a SET col_a = 'abc' where col_b = 234 the right most character will become a strange character, and cannot print out in error log. I have twelve machines doing the replication work, but only got problem on this AMD64 machine. Anyone has encounter this type problem? please give me some advise. Thanks and have a nice day. Regards, Weicheng. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hello. Not enough information to make a conclusion. What version of MySQL do you use? Usually debugging of replication problems begins from researching of binary logs with mysqlbinlog. See: http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html http://dev.mysql.com/doc/mysql/en/slave-logs.html http://dev.mysql.com/doc/mysql/en/binary-log.html Please, next time include the output of SHOW SLAVE STATUS (executed on the slave) and SHOW MASTER STATUS (executed on the master) as well. Weicheng Pan [EMAIL PROTECTED] wrote: Hi all: I have replication problem on my FreeBSD 5.4 AMD64 box. Sometimes replication will stop and throw an error code 1054, but the query is not the original one in the master. A query like UPDATE table_a SET col_a = 'abc' where col_b = 234 the right most character will become a strange character, and cannot print out in error log. I have twelve machines doing the replication work, but only got problem on this AMD64 machine. Anyone has encounter this type problem? please give me some advise. Thanks and have a nice day. Regards, Weicheng. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hello. Error: 'Unknown MySQL server host 'server5.domain.com' (1)' errno: 2005 Does the problem remain if you remove the following line from the my.cnf on the server8: skip-networking Chris Mason [EMAIL PROTECTED] wrote: I have two servers, server5.mydomain.com and server8.mydomain.com. I = want to replicate one database on server5 to server 8. I did the whole proceedure as recommended in http://dev.mysql.com/doc/mysql/en/replication-howto.html - 6.4. How to = Set Up Replication I setup the GRANT statement on server5 for the slave. mysql SHOW GRANTS FOR [EMAIL PROTECTED]; += --- ---+ | Grants for [EMAIL PROTECTED] | += --- ---+ | GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO = 'server8'@'MyServer8IP' IDENTIFIED BY PASSWORD 'xxx' | += --- ---+ 1 row in set (0.00 sec) But when I stop and start the slave, I get: 050316 8:56:42 [Note] Slave SQL thread initialized, starting = replication in log 'mysql-bin.04' at position 79, relay log './server8-relay-bin.01' position: 4 050316 8:56:42 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Unknown MySQL server host 'server5.domain.com' (1)' errno: 2005 retry-time: 60 retries: 86400 When I try from the command line, I get it to work without problem: [EMAIL PROTECTED] mysql]# mysql -h server5.domain.com -u server8 -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.10-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql=20 Both /etc/my.cnf files are listed below [EMAIL PROTECTED] mysql]# cat /etc/my.cnf=20 [mysqld] datadir=3D/var/lib/mysql socket=3D/var/lib/mysql/mysql.sock old_passwords skip-locking key_buffer =3D 256M max_allowed_packet =3D 1M table_cache =3D 256 sort_buffer_size =3D 1M read_buffer_size =3D 1M read_rnd_buffer_size =3D 4M myisam_sort_buffer_size =3D 64M thread_cache =3D 8 query_cache_size=3D 16M skip-networking sort_buffer=3D2M log-bin server-id=3D2 log-slave-updates=20 log-warnings=20 replicate-ignore-db=3Dmysql replicate-do-db=3Danguillaguide [isamchk] key_buffer =3D 128M sort_buffer_size =3D 128M read_buffer =3D 2M write_buffer =3D 2M [myisamchk] key_buffer =3D 128M sort_buffer_size =3D 128M read_buffer =3D 2M write_buffer =3D 2M [mysqlhotcopy] interactive-timeout =20 [mysql.server] user=3Dmysql basedir=3D/var/lib [safe_mysqld] err-log=3D/var/log/mysqld.log pid-file=3D/var/run/mysqld/mysqld.pid [EMAIL PROTECTED] mysql]# cat /etc/my.cnf=20 [mysqld] old-passwords max_connections =3D 500 key_buffer =3D 16M myisam_sort_buffer_size =3D 64M join_buffer_size =3D 2M read_buffer_size =3D 2M sort_buffer_size =3D 3M table_cache =3D 1500 thread_cache_size =3D 128 wait_timeout =3D 14400 connect_timeout =3D 10 max_allowed_packet =3D 16M max_connect_errors =3D 10 query_cache_limit =3D 1M query_cache_size =3D 32M query_cache_type =3D 1 skip-innodb log-bin=3Dmysql-bin server-id=3D1 [mysqld_safe] open_files_limit =3D 8192 [mysqldump] quick max_allowed_packet =3D 16M [myisamchk] key_buffer =3D 64M sort_buffer =3D 64M read_buffer =3D 16M write_buffer =3D 16M Chris Mason Anguilla =20 --=20 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 3/18/2005 =20 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem with a cross database query
On 10/06/2004 6:23 PM, Bill Thomason [EMAIL PROTECTED] wrote: I originally posted a query about a problem entitled Table doesn't exist on query replication problem... The original title might be a little misleading. The slave replication is halting on a transaction that contains a query that spans two databases - one that is being replicated and the other is not. Could anyone provide me with some general rules of thumb about breaking down such a query? This may sound like a vague or possibly stupid question since this predicament is probably very specific to the situation. I didn't write the original query but I am establishing the master/slave relationships and uncovered the bug in doing so. Other than altering your database schema or replicating the reporting database, your options appear to be very limited. You can't use 'SQL_SLAVE_SKIP_COUNTER = 1' unless your slave is not running, and bookending the troublesome query with 'SQL_LOG_BIN = 0' and 'SQL_LOG_BIN = 1' only works from within the mysql client. Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem
On Friday 20 August 2004 07:08, [EMAIL PROTECTED] might have typed: Is replication of database in MySQL is possible. Actually i want to replicate the changes to the database to different servers located at different locations. A tiny bit of legwork would have answered it for you. http://dev.mysql.com/doc/mysql/en/Replication.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Tue, Aug 03, 2004 at 12:44:05PM +0530, [EMAIL PROTECTED] wrote: Hi Friends: We are using mysql version 4.0.17 on Linux with a master and a single slave both running on the same node. We have encountered a problem in replication in the following scenario: First the slave got abnormally terminated while there were some active connections to the master. The master was also terminated abnormally in quick succession. After both master and slave was brought up, we noticed that the last transaction in the master just before the abnormal termination is getting replicated twice in the slave. Thanks in advance. Please pardon me if there is a confidentiality note that gets automatically attached with this mail and do ignore it. I'm going to make an educated guess. There's a race condition and you lost. The slave copied that query from the master's binlog but was shutdown before it could update the master.info file to reflect it's new position in reading the master's binlog. When you started it back up, it fetched it a second time, not knowing it had done so previously. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem on 3.23.57 3.23.58
somebody here can make sense of it. On the slave I get error messages like: 040630 2:43:52 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.163' at position 37919441 It does that several times between 2:20am and 4:30am. and every few Ah.. right.. I'm not-so-smart. I didn't actually post the entire error log message: 040701 14:56:05 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 14:56:05 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 14792980 040701 14:56:05 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 14792980 040701 15:02:13 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 15:02:13 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 25852745 040701 15:02:13 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 25852745 040701 15:09:14 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 15:09:14 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 37090598 040701 15:09:14 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 37090598 040701 15:15:28 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 15:15:28 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 47700336 040701 15:15:28 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 47700336 are the messages that come out of the slave. I'm currently running a test where I back up the entire slave, run it forward through the logs, and restore the slave back exactly to it's previous point, and run it through the logs again. See if it dies in the same places or if it's somewhat random. -Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hi, On Wed, Apr 21, 2004 at 06:35:53PM +0200, Mechain Marc wrote: Add this in your my.cnf slave file: slave-skip-errors = 1062 Ahh, that's what i was looking for. Seems as if i had not read chapter 6 of the documentation thoroughly enough... Thanks, Georg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication problem
Add this in your my.cnf slave file: slave-skip-errors = 1062 Marc. -Message d'origine- De : Georg Horn [mailto:[EMAIL PROTECTED] Envoyé : mercredi 21 avril 2004 17:31 À : [EMAIL PROTECTED] Objet : Replication problem Hi, i'm new to this list, but i use mysql for years an are very happy with it. However, today i ran into a problem that i couldn't find a solution for: I set up database replication with a master and one slave, and it works fine so far. I rewrote my application (web based written in php) so that it executes all queries that insert, delete or update rows are executed on the master, and all other queries on the slave. Fine. But what, if the master fails? I want users to be able to continue working on the slave, and this works fine for webpages that just do select statements. I thought that, in case of the master being down, i could execute all data-modifying queries on the slave, and also store these queries in a special table or file, and re-execute them later on the master if the master becomes available again. The problem is, that i then may get Duplicate entry ... for key ... errors on the slave, if a record was already inserted into a table with unique keys, and that the sql-thread on the slave then exits. Is it possible to make the slave ignore such errors (i found no option for this in the docs) and just stupidly continue replication, or does anyone have a better idea how to set up such a scenario? Bye, Georg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem (XP+mysql4.0.14)
I have your same problem in my network where the master i 3.23.52 and then slave are 4.0.14; before, in all 4.0.13, it was ok, without this bug. It will be solved in 4.0.15 ? Best regards Massimo - Original Message - From: I.P. [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 22, 2003 11:56 AM Subject: Replication Problem (XP+mysql4.0.14) Hi, it's my story. I have two 4.0.14 mysql server on one machine with win XP Professional polish version. First acts as master: on port 3300 Second acts as slave: on port 3301 below my configuration: ### FOR MASTER # # This will be passed to all mysql clients [client] #password=my_password port=3300 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] basedir = c:/mysql4/ datadir = c:/mysql4/data/ port=3300 language=polish default-character-set=latin2 log-bin server-id=1 log-warnings set-variable = key_buffer=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K query_cache_size = 1024K # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:50M innodb_data_home_dir = d:\\innodb\\mysql4\\master\\ibdata innodb_log_group_home_dir = d:\\innodb\\mysql4\\master\\iblogs innodb_log_arch_dir = d:\\innodb\\mysql4\\master\\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [mysqlhotcopy] interactive-timeout ### ###FOR SLAVE # # This will be passed to all mysql clients [client] #password=my_password port=3301 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] basedir = c:/mysql4_slave/ datadir = c:/mysql4_slave/data/ port=3301 language=polish default-character-set=latin2 server-id=2 # log-bin # log-slave-updates master-host=127.0.0.1 master-user=irek master-password=XX master-port=3300 master-connect-retry=30 # log-update=log_updates.log log-warnings set-variable = key_buffer=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K query_cache_size = 1024K # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:50M innodb_data_home_dir = d:\\innodb\\mysql4\\slave\\ibdata innodb_log_group_home_dir = d:\\innodb\\mysql4\\slave\\iblogs innodb_log_arch_dir = d:\\innodb\\mysql4\\slave\\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M ## END configuration So i have noticed this things. I start replication with master and slave: 1) Master is running ... --- Slave is running ... and at console can i see: 030804 22:55:36 InnoDB: Started 030804 22:55:36 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3300', r eplication started in log 'FIRST' at position 4 030804 22:55:36 Slave SQL thread initialized, starting replication in log 'FIRS T' at position 0, relay log '.\hq-relay-bin.001' position: 4 mysqld-max-nt: ready for connections. Version: '4.0.14-max-nt' socket: '' port: 3301 --- That is ok. 3) checked master data dir: hq-bin.001 hq-bin.index 4) checked slave data dir: hq-relay-bin.001 hq-relay-bin.index master.info relay-log.info 5) I stopped slave - shutdown nicely. 6) I stopped and started master 3 times. 7) I checked master data dir and i can see: hq-bin.001 hq-bin.002 hq-bin.003 hq-bin.004
RE: Replication Problem
You can use an option in my.cnf slave-skip-error=list error nums separated by ,. The error below means that the master had a shutdown event and the slave can't tell if it added / deleted / changed that row or not. I wouldn't advise in skipping this error less you want your data to be inconsistent or you can recover from the possible skipped slave event. ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Monday, August 18, 2003 2:51 PM --To: [EMAIL PROTECTED] --Subject: Replication Problem -- -- --I am having a problem with replication in MySQL 4.0.13. I have a --my.cnf file in the data directory of the slave that should exclude a --specific table in one of my databases from replication. I am getting --the following error message: -- -- error 'unexpected success or fatal error' on query 'UPDATE --excludedDB.excludedTABLE.' . -- -- I am sure the syntax is correct in the my.cnf I am excluding several -- --other tables and that seems to be working fine. The above query is --for software that is in beta so it is not running all of the time. I --can use SET GLOBAL SQL_SLAVE_SKIP_COUNTER to by-pass them for now but -- --I need to find a permanent solution. Any help would be greatly --appreciated. -- --Thanks, -- --Ed Pauley II --Bloodstock Research Information Services --Phone: (859) 223- ext. 297 --E-mail: [EMAIL PROTECTED] --Web: http://www.brisnet.com -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wed, Aug 06, 2003 at 12:16:40PM -0400, walt wrote: On Wednesday 06 August 2003 11:50 am, Andy Smith wrote: $ cat mysql/master.info angora-bin.001 20102800 127.0.0.1 repl removed 3306 60 Looks fine to me. :( Looks fine to me as well Are you still getting the 1200 error when you try SLAVE START ? Yes, afraid so.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wed, Aug 06, 2003 at 07:00:33PM -0700, Steven Roussey wrote: So does anyone else have any ideas what is going on here? Shall I report this as a bug? Did you post how you setup the servers to load the different my.cnf files? Hopefully you don't have one at a default location. I don't use an /etc/my.cnf for this reason. The main one uses /var/lib/mysql/my.cnf and the slave one /data/mysql-backup/mysql/my.cnf. Is there a quick way to have it tell you which files it is reading and what settings will get set? Otherwise, it sounds like the config information is not properly set -- either some user setup error that is alluding all of us, or a config loading error in MySQL. I'd like to check how the config files are located, etc., before doing a bug report. My two cents. PS: I'd also have both servers running the latest version, just in case it was a bug that was already fixed. Good point, I might as well try that now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wed, Aug 06, 2003 at 11:23:31AM -0400, walt wrote: On Wednesday 06 August 2003 11:13 am, Andy Smith wrote: On Wed, Aug 06, 2003 at 03:59:53PM +0100, Andy Smith wrote: On Wed, Aug 06, 2003 at 10:52:54AM -0400, walt wrote: Andy, I don't see log-slave-updates in your master setup. I see log-bin, but I think that only applies to updates done directly to the database (not replication updates). I'm not 100% sure about that, but it may be worth looking into. OK, so why would that prevent my _slave_ from even doing slave start? OK, so I added log-slave-updates to the master and it did not fix my problem. Thanks for your help though! Any other suggestions? Andy, I just noticed that you have a mix of port numbers. Can you try `netstat -an | grep 3306` from the command line and see if the master is indeed listening on that port? $ netstat -an | grep 3306 tcp0 0 0.0.0.0:33060.0.0.0:* LISTEN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wed, Aug 06, 2003 at 03:59:53PM +0100, Andy Smith wrote: On Wed, Aug 06, 2003 at 10:52:54AM -0400, walt wrote: Andy, I don't see log-slave-updates in your master setup. I see log-bin, but I think that only applies to updates done directly to the database (not replication updates). I'm not 100% sure about that, but it may be worth looking into. OK, so why would that prevent my _slave_ from even doing slave start? OK, so I added log-slave-updates to the master and it did not fix my problem. Thanks for your help though! Any other suggestions? -- SCSI is usually fixed by remembering that it needs three terminations: One at each end of the chain. And the goat. -- Andrew McDonald, HantsLUG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem...
System [EMAIL PROTECTED] wrote: Hello All, I have setup Replication on two Redhat linux box 7.3 and 9.0 with MySQL-4.0.13 Running.. On Master the my.cnf is == [client] port= 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock server-id = 1 log-bin [mysqldump] quick max_allowed_packet = 16M On Slave it is = [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib master-host=66.xxx.xxx.xx3 master-user=repl master-password=password server-id=2 replicate-do-db=database [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid When I start Slave on the slave box it says this: mysql SLAVE START; ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO So I Executed This: mysql CHANGE MASTER TO MASTER_HOST='66.xxx.xxx.xx3', MASTER_USER='repl', MASTER_PASSWORD='password',MASTER_LOG_FILE='server-bin.004', MASTER_LOG_POS=4994; and START SLAVE but the same error occurs. I have the master.info and relay-info cat master.info 4 test 3306 60 cat relay-log.info = ./server-relay-bin.001 4 0 Replace all replication option from [mysql.server] section to the [mysqld] section of my.cnf. mysql.server can read only datadir, basedir and pid-file options. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wednesday 06 August 2003 11:50 am, Andy Smith wrote: There is one more thing I can think of to check... Can you send me a copy of the master.info file. I've had to manually change it before after changing the master in the my.cnf file. I found out later that you could do CHANGE MASTER TO I assume you mean from the slave. This is a newly set up slave specifically for this purpose, so all I did was use CHANGE MASTER.. myself. $ cat mysql/master.info angora-bin.001 20102800 127.0.0.1 repl removed 3306 60 Looks fine to me. :( Looks fine to me as well Are you still getting the 1200 error when you try SLAVE START ? -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wed, Aug 06, 2003 at 11:39:26AM -0400, walt wrote: On Wednesday 06 August 2003 11:26 am, Andy Smith wrote: Andy, I just noticed that you have a mix of port numbers. Can you try `netstat -an | grep 3306` from the command line and see if the master is indeed listening on that port? $ netstat -an | grep 3306 tcp0 0 0.0.0.0:33060.0.0.0:* LISTEN There is one more thing I can think of to check... Can you send me a copy of the master.info file. I've had to manually change it before after changing the master in the my.cnf file. I found out later that you could do CHANGE MASTER TO I assume you mean from the slave. This is a newly set up slave specifically for this purpose, so all I did was use CHANGE MASTER.. myself. $ cat mysql/master.info angora-bin.001 20102800 127.0.0.1 repl removed 3306 60 Looks fine to me. :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
So does anyone else have any ideas what is going on here? Shall I report this as a bug? On Wed, Aug 06, 2003 at 02:19:21PM +0100, Andy Smith wrote: Hi folks, trying to set up replication and I'm getting this problem which I can't see how to fix despite reading of the manual and google. I have set up a master and a slave according to the manual. The master runs 4.0.12 and the slave runs 4.0.14, they are both actually on the same machine (it's a long story, but it's what I need). I did LOAD DATA FROM MASTER on the slave which completed successfully, and then I tried SLAVE START: ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO but.. it is! on the slave: mysql show slave status\G *** 1. row *** Master_Host: 127.0.0.1 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: angora-bin.001 Read_Master_Log_Pos: 20102800 Relay_Log_File: angora-relay-bin.001 Relay_Log_Pos: 4 Relay_Master_Log_File: angora-bin.001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 20102800 Relay_log_space: 8 1 row in set (0.00 sec) On the master: mysql show master status; ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | angora-bin.001 | 24954609 | | | ++--+--+--+ 1 row in set (0.01 sec) Any ideas what I missed? -- I remember the first time I made love. Perhaps it was not love exactly but I made it and it still works. -- The League Against Tedium -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- thoughtless whats the best place you've ever done it? G-Shock mum and dads bed MrMoves Adam, you were abused???!! ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
So does anyone else have any ideas what is going on here? Shall I report this as a bug? Did you post how you setup the servers to load the different my.cnf files? Hopefully you don't have one at a default location. Otherwise, it sounds like the config information is not properly set -- either some user setup error that is alluding all of us, or a config loading error in MySQL. I'd like to check how the config files are located, etc., before doing a bug report. My two cents. PS: I'd also have both servers running the latest version, just in case it was a bug that was already fixed. --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Thu, Aug 07, 2003 at 07:54:18AM +0100, Andy Smith wrote: On Wed, Aug 06, 2003 at 07:00:33PM -0700, Steven Roussey wrote: So does anyone else have any ideas what is going on here? Shall I report this as a bug? Did you post how you setup the servers to load the different my.cnf files? Hopefully you don't have one at a default location. I don't use an /etc/my.cnf for this reason. The main one uses /var/lib/mysql/my.cnf and the slave one /data/mysql-backup/mysql/my.cnf. ..and so putting the my.conf into /data/mysql-backup instead wasn't very useful. Problem solved. Sorry for the annoyance, and thanks to all who tried to help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wednesday 06 August 2003 11:13 am, Andy Smith wrote: On Wed, Aug 06, 2003 at 03:59:53PM +0100, Andy Smith wrote: On Wed, Aug 06, 2003 at 10:52:54AM -0400, walt wrote: Andy, I don't see log-slave-updates in your master setup. I see log-bin, but I think that only applies to updates done directly to the database (not replication updates). I'm not 100% sure about that, but it may be worth looking into. OK, so why would that prevent my _slave_ from even doing slave start? OK, so I added log-slave-updates to the master and it did not fix my problem. Thanks for your help though! Any other suggestions? Andy, I just noticed that you have a mix of port numbers. Can you try `netstat -an | grep 3306` from the command line and see if the master is indeed listening on that port? -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wed, Aug 06, 2003 at 10:32:23AM -0400, walt wrote: Andy, Can you send a copy of your my.cnf file for both the slave and master database. Sure. Slave: [client] port=3307 socket=/data/mysql-backup/mysql.sock [mysqld] port=3307 socket=/data/mysql-backup/mysql.sock server-id=4 log-warnings master-host=127.0.0.1 master-user=repl master-password=removed master-port=3306 master: [mysqld] skip-name-resolve skip-innodb log-slow-queries log-bin master-host=another-host master-user=repl master-password=removed master-port=13306 server-id=3 replicate-do-db=liveservices tmpdir=/var/tmp (master is also a slave from another-host and is replicating the db liveservices, it should be noted that this replication is working fine and has been for a very long time) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wednesday 06 August 2003 11:26 am, Andy Smith wrote: Andy, I just noticed that you have a mix of port numbers. Can you try `netstat -an | grep 3306` from the command line and see if the master is indeed listening on that port? $ netstat -an | grep 3306 tcp0 0 0.0.0.0:33060.0.0.0:* LISTEN There is one more thing I can think of to check... Can you send me a copy of the master.info file. I've had to manually change it before after changing the master in the my.cnf file. I found out later that you could do CHANGE MASTER TO -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
Beacuse of this type of mail , i got listed on spam bag for using the mailing lists inform the linux comunity of my site , wich is a free service. If see this kind of junk on any mailing list ill make it my personal mission to have you domain fried Don't post unless it's goning to help the opens source comunity ... - Original Message - From: Jonathan Fowler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 31, 2003 4:34 AM Subject: Re: replication problem www.kapadiaz.com = download free movies songs and also free 5 gb hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
www.kapadiaz.com = download free movies songs and also free 5 gb hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
Naoufal Rih [EMAIL PROTECTED] wrote: Hi I have 3 machines M1 M2 and M3, i need to setup the following : M1 is the master M2 is reading from M1 M2 is a master for M3 M3 is reading from M2 The problem i got, is when i run a command in M2, it's replicated in M3, but when the command is being run in M1, it's replicated in M2 but not in M3. does anybody know how i can fix this problem ? Add log-slave-updates option: http://www.mysql.com/doc/en/Replication_Options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem: Slave not starting
Andrew Staples wrote: I've setup my my.cnf file on the slave as: [mysql.server] user=mysql basedir=/var/lib socket=/var/lib/mysql/mysql.sock server-id=2 master-host=206.xxx.xxx.xxx master-user=replicateuser master-password=replicatepassword Master.info is: tux-bin.001 3109 206.xxx.xxx.xxx replicateuser replicatepassword 3306 60 This indicates that replication is running. Show slave status indicates NO under Slave_running, and I get: mysql slave start; ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO You would get this message on the master. You are running this command on the slave? Server has been restarted. Version is 3.23.56 Any ideas? Andrew Instead of trying to build newer and bigger weapons of destruction, we should be thinking about getting more use out of the ones we already have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem (alter table fails)
Looks like we've mixed up two threads into one ;-) As I understood from various forums and maillists, versions 4.x are still in development state. The newest production version is 3.23.55, am I right? Do you recommend this version for testing? Regards, Kirill Egor Egorov wrote: On Tuesday 25 February 2003 14:17, Kirill Peskov (WEB-EU) wrote: Coud you, please, give an advice to solve following problem: I've got two Mysql Servers: * Master: Sparc Solaris 8, MySQL 3.23.32 * Slave: RedHat Linux 7.2, MySQL 3.23.32 Replication of data runs fine, all insert into and delete from made on master successfully change the slave database. Creation and dropping of the whole tables also work fine. But if I add or delete certain column in any table on master DB (e.g. run ALTER TABLE commands), slave ignores is and later on stops replication because it cannot replicate the data appeared or changed on the master afterwards (because these fields not exist on the slave). Is there any way to tell either Master or Slave to replicate ALTER TABLE queries as well? Since 3.23.32 there were a lot of bug fixed related to the replication. Please, upgrade servers and test it again. -- Kirill Peskov UNIX Application Engineer WEB-EU TREND MICRO Deutschland GmbH Lise-Meitner-Strasse 4 Unterschleissheim D-85716 Germany Tel: +49(0)89 37479-850 Fax: +49(0)89 20 37479-700 Mobile: +49(0)172 265-1635 E-Mail: [EMAIL PROTECTED] WWW: www.trendmicro.de http://www.trendmicro.de The information contained in this electronic mail message and any attachments hereto is privileged and confidential information intended only for the use of the individual or entity named above or their designee. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error please immediately notify us by return message or by telephone and delete the original message from your mail system. Thank you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: Replication problem (alter table fails)
On Wednesday 26 February 2003 10:19, Kirill Peskov (WEB-EU) wrote: Looks like we've mixed up two threads into one ;-) As I understood from various forums and maillists, versions 4.x are still in development state. Yup, 4.0.X is gamma. The newest production version is 3.23.55, am I right? Do you recommend this version for testing? Yes, test it on 3.23.55 and tell me about results. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Replication problem (alter table fails)
On Tuesday 25 February 2003 14:17, Kirill Peskov (WEB-EU) wrote: Coud you, please, give an advice to solve following problem: I've got two Mysql Servers: * Master: Sparc Solaris 8, MySQL 3.23.32 * Slave: RedHat Linux 7.2, MySQL 3.23.32 Replication of data runs fine, all insert into and delete from made on master successfully change the slave database. Creation and dropping of the whole tables also work fine. But if I add or delete certain column in any table on master DB (e.g. run ALTER TABLE commands), slave ignores is and later on stops replication because it cannot replicate the data appeared or changed on the master afterwards (because these fields not exist on the slave). Is there any way to tell either Master or Slave to replicate ALTER TABLE queries as well? Since 3.23.32 there were a lot of bug fixed related to the replication. Please, upgrade servers and test it again. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication problem (alter table fails)
Egor, I am sorry, my bad. I misrepresented the versions of mysql I am using. Must have been a typo. Here is what I am using. Master: Sparc Solaris 7 MySQL 4.0.9-gamma-log Slave: RedHat Linux 7.2 MySQL 4.0.8-gamma-log. Could you continue to help me with this ? Thanks, floyd -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 8:28 AM To: [EMAIL PROTECTED] Subject: re: Replication problem (alter table fails) On Tuesday 25 February 2003 14:17, Kirill Peskov (WEB-EU) wrote: Coud you, please, give an advice to solve following problem: I've got two Mysql Servers: * Master: Sparc Solaris 8, MySQL 3.23.32 * Slave: RedHat Linux 7.2, MySQL 3.23.32 Replication of data runs fine, all insert into and delete from made on master successfully change the slave database. Creation and dropping of the whole tables also work fine. But if I add or delete certain column in any table on master DB (e.g. run ALTER TABLE commands), slave ignores is and later on stops replication because it cannot replicate the data appeared or changed on the master afterwards (because these fields not exist on the slave). Is there any way to tell either Master or Slave to replicate ALTER TABLE queries as well? Since 3.23.32 there were a lot of bug fixed related to the replication. Please, upgrade servers and test it again. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: RE: Replication problem (alter table fails)
On Tuesday 25 February 2003 16:36, Floyd Wellershaus wrote: Egor, I am sorry, my bad. I misrepresented the versions of mysql I am using. Must have been a typo. Here is what I am using. Master: Sparc Solaris 7 MySQL 4.0.9-gamma-log Slave: RedHat Linux 7.2 MySQL 4.0.8-gamma-log. Could you continue to help me with this ? I tested on 4.0.10 .. What replication options do you have in my.cnf? Do you use any replicate-* options? Could you provide a test case? -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 8:28 AM To: [EMAIL PROTECTED] Subject: re: Replication problem (alter table fails) On Tuesday 25 February 2003 14:17, Kirill Peskov (WEB-EU) wrote: Coud you, please, give an advice to solve following problem: I've got two Mysql Servers: * Master: Sparc Solaris 8, MySQL 3.23.32 * Slave: RedHat Linux 7.2, MySQL 3.23.32 Replication of data runs fine, all insert into and delete from made on master successfully change the slave database. Creation and dropping of the whole tables also work fine. But if I add or delete certain column in any table on master DB (e.g. run ALTER TABLE commands), slave ignores is and later on stops replication because it cannot replicate the data appeared or changed on the master afterwards (because these fields not exist on the slave). Is there any way to tell either Master or Slave to replicate ALTER TABLE queries as well? Since 3.23.32 there were a lot of bug fixed related to the replication. Please, upgrade servers and test it again. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication problem
I've narrowed it down a lot. We have two different PHP interfaces for the database. One is for employees to access the data, and the other is for customers. The UPDATEs and INSERTs on the system that customers use is not being replicated, but the one that employees is working perfectly. The system the employees use is located on localhost, while the other isn't. Do you think this would make a difference, how about different versions of the client libs? Any ideas? Phillip Salzman [EMAIL PROTECTED] said: Hello, I just setup replication on mysql 3.23.55 and seem to be having some problems. The slave machine does not seem to want to copy all the data on start. I have a table with 54,000 rows on the master, and after I load up the slave there are only 54,850 on that machine. I can drop the table and do a 'load table from master', and it will update the changes to each row, but won't add new rows to the slave. Has anyone seen this before? I've been looking online and cannot seem to find anything. Any assistance would be very much appriciated. Thanks, --- Phillip Salzman - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Replication problem with update queries
On Friday 22 November 2002 17:19, RAHARD Matthieu wrote: I set up replication on my servers since the version 3.23.43. Recently I upgrade all my servers to version 4.0.3. The replication work fine for insert and delete queries (also alter table) but doesn't take care of update queries. replicate-do-table causes this bug. Use replicate-do-db or upgrade to upcoming 4.0.6. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: replication problem
David, Monday, September 23, 2002, 6:03:58 PM, you wrote: DP We are having a problem which seems to have appeared recently with DP replication on our mysql servers. DP - We have 3 servers set up with mysql 3.23.52 (they were running .49 but DP we upgraded in an attempt to fix this problem, which did not work), set DP up as a master and 2 slaves to the master. DP - There is a large script that runs on the master server. This script DP DROPs a large table, then recreates it. It is basically a DROP followed DP by a CREATE and many INSERTs. DP - Up until recently we have had no issues with this problem. Lately DP however the slave servers are not updating. The error we see in the logs DP is the table which it attempts to create already exists, therefore from DP that point on the replication stops. We can't understand why suddenly DP the DROP TABLE doesn't get replicated. David, does it happen every time when you drop the table or only sometimes? Is DROP TABLE statement present in binary logs? I tested on 3.23.52 and DROP TABLE replicated fine for me. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: replication problem
Adding a new discovery to this topic: The replication error only occurs on one of the 2 slave servers. There is no difference between the two in configuration. Hardware-wise, there is only one difference: the server which has the problem has 1 gig of RAM, and the one that does not experience the problem has 2 gigs. This being the only difference, I can only guess that it is the source of the problem. The table is very large that I am dealing with - the physical size is just over 1 gig. My only guess would be perhaps it is trying to do a CREATE TABLE before the DROP completes. David Piasecki -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 8:34 AM To: [EMAIL PROTECTED] Subject: re: replication problem David, Monday, September 23, 2002, 6:03:58 PM, you wrote: DP We are having a problem which seems to have appeared recently with DP replication on our mysql servers. DP - We have 3 servers set up with mysql 3.23.52 (they were running .49 but DP we upgraded in an attempt to fix this problem, which did not work), set DP up as a master and 2 slaves to the master. DP - There is a large script that runs on the master server. This script DP DROPs a large table, then recreates it. It is basically a DROP followed DP by a CREATE and many INSERTs. DP - Up until recently we have had no issues with this problem. Lately DP however the slave servers are not updating. The error we see in the logs DP is the table which it attempts to create already exists, therefore from DP that point on the replication stops. We can't understand why suddenly DP the DROP TABLE doesn't get replicated. David, does it happen every time when you drop the table or only sometimes? Is DROP TABLE statement present in binary logs? I tested on 3.23.52 and DROP TABLE replicated fine for me. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication problem: slave can't log into master
I don't even have a master.info file to remove. The master.info file never gets written. It won't start replication at all. It just keeps writing the same error to the log file: Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec I don't understand what I have missed that a connection from the slave to the master is not allowed. Again, here is what is in my.cnf on the master and the slave. Master: --- [mysqld] log-bin server-id=21 max_binlog_size=512M --- Slave: --- [mysqld] master-host=ctiadb1 master-user=repl master-password=replctia master-port=3306 server-id=87 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock innodb_data_home_dir= innodb_data_file_path=/innodb_data/idbdata1:10M;/innodb_data/idbdata2:50M;/t est/innodb_data/idbdata3:100M:autoextend:max:512M # Set buffer pool size to 50-80% of computer's memory set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=16M # Set log file size to 25% of buffer pool size set-variable = innodb_log_file_size=40M set-variable = innodb_log_buffer_size=8M # Set innodb_flush_log_at_trx_commit to 0 if you can afford losing some last transactions innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/lib/mysql/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 08, 2002 3:45 PM To: Jamie Beu Cc: [EMAIL PROTECTED] Subject: Re: Replication problem: slave can't log into master On Thu, Sep 05, 2002 at 09:52:05AM -0400, Jamie Beu wrote: I am attempting to setup MySQL replication between the ctiadb1 (master) server and the ctiadb2 (slave) server. When I look at the mysql.log file on the slave, I see the following (repeated every 60 seconds): 020904 12:00:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec 020904 12:01:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec I already typed the following on the Master server: grant file on *.* to 'repl'@'%' identified by 'replctia'; flush privileges; Perhaps the master.info file on the slave is wrong. Try removing it and restatring the slave. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 33 days, processed 671,118,015 queries (231/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication problem: slave can't log into master
On Thu, Sep 05, 2002 at 09:52:05AM -0400, Jamie Beu wrote: I am attempting to setup MySQL replication between the ctiadb1 (master) server and the ctiadb2 (slave) server. When I look at the mysql.log file on the slave, I see the following (repeated every 60 seconds): 020904 12:00:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec 020904 12:01:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec I already typed the following on the Master server: grant file on *.* to 'repl'@'%' identified by 'replctia'; flush privileges; Perhaps the master.info file on the slave is wrong. Try removing it and restatring the slave. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 33 days, processed 671,118,015 queries (231/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication problem: slave can't log into master (update)
I don't have a master.info file in my /usr/local/mysql/data dir, only 2 directories: mysql and test, neither of which contain master.info. What is supposed to be in the master.info file? I don't recall any reference to a master.info file in the replication setup procedure described in the MySQL online manual. Jamie Beu Lockheed-Martin Information Systems Software Engineer CTIA (407) 306-2484 The true soldier fights not because he hates what is in front of him, but because he loves what is behind him. G. K. Chesterton - Illustrated London News, 1/14/11 -Original Message- From: Dicky Wahyu Purnomo [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 11:02 PM To: Jamie Beu Cc: [EMAIL PROTECTED] Subject: Re: Replication problem: slave can't log into master (update) Pada Thu, 5 Sep 2002 12:35:45 -0400 Jamie Beu [EMAIL PROTECTED] menulis : I am still having problems with the slave server being able to connect to the master, but a thought occured to me while we were working another topic. Please let me know what I'm missing. I can do the following: mysql -hctiadb1 -urepl -preplctia go to your slave server : look at master info file in your mysql data directory, e.g : /usr/local/mysql/data/master.info change if necessary the replication configuration ;-) -- ---Original Message--- I am attempting to setup MySQL replication between the ctiadb1 (master) server and the ctiadb2 (slave) server. When I look at the mysql.log file on the slave, I see the following (repeated every 60 seconds): 020904 12:00:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec 020904 12:01:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec I already typed the following on the Master server: grant file on *.* to 'repl'@'%' identified by 'replctia'; flush privileges; So, I am now able to log into the master from the slave as follows: mysql -hctiadb1 -urepl -preplctia and I can access any database whenever I wish. I just can't seem to get replication to work. Please tell me what I am doing wrong or missing. The /etc/my.cnf file on the master (ctiadb1) is as follows: --- [mysqld] log-bin server-id=21 max_binlog_size=512M --- The /etc/my.cnf file on the slave (ctiadb2) is as follows: --- [mysqld] master-host=ctiadb1 master-user=repl master-password=replctia master-port=3306 server-id=87 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock innodb_data_home_dir= innodb_data_file_path=/innodb_data/idbdata1:10M;/innodb_data/idbdata2:50M;/t est/innodb_data/idbdata3:100M:autoextend:max:512M # Set buffer pool size to 50-80% of computer's memory set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=16M # Set log file size to 25% of buffer pool size set-variable = innodb_log_file_size=40M set-variable = innodb_log_buffer_size=8M # Set innodb_flush_log_at_trx_commit to 0 if you can afford losing some last transactions innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/lib/mysql/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication problem: slave can't log into master (update)
Pada Thu, 5 Sep 2002 12:35:45 -0400 Jamie Beu [EMAIL PROTECTED] menulis : I am still having problems with the slave server being able to connect to the master, but a thought occured to me while we were working another topic. Please let me know what I'm missing. I can do the following: mysql -hctiadb1 -urepl -preplctia go to your slave server : look at master info file in your mysql data directory, e.g : /usr/local/mysql/data/master.info change if necessary the replication configuration ;-) -- Let's call it an accidental feature. -- Larry Wall MySQL 3.23.51 : up 76 days, Queries : 360.006 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan 12790 Phone: +62 21 79199577 - HP: +62 8158787286 - Web: http://www.1rstwap.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication problem
At 05:27 PM 8/20/2002 -0400, walt wrote: I have added this one, thanks. Hmmm... When you set up the master database, did you copy all the *.frm, *.MYD, *.MYI to the slave database? Ok, after a lot of hair-pulling, I downgraded and used 3.23, and what do you know, replication worked out fine without any hitch! I was using MySQL 4.0.2 and for some reason, replication doesn't work. Is this is a bug? FWIW, when i use 4.0.2 and do a show processlist on the master server, it doesn't display Slave connection: waiting for binlog update. (I know, I know, 4.0 is supposed to be alpha :)) Thanks again walt Froilan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication problem
Froilan Mendoza wrote: Hello, I have two machines that I am trying to use replication on. Here are their configurations: master (my.cnf) log-bin serveri-d = 1 slave: server-id = 2 master-host=master.domain.com master-user=replicate master-password=replicatedb log-bin I followed the steps in the manual but found this error on my slave logs: 020820 13:24:52 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 020820 13:24:52 Error updating slave list: 020820 13:24:52 Slave I/O thread exiting, read up to log 'FIRST', position 4 What does this mean? Whenever I make changes to the master, the Position changes: mysql show master status; ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | master-bin.002 | 208 | | | ++--+--+--+ but doesn't reflect on the slave: mysql show master status; ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | slave-bin.002 | 79 | | | ++--+--+--+ Should the files be the same for both master and slave? Here are other things that might help: (slave) mysql show processlist; | 2 | system user | none | NULL | Connect | 1945 | Slave: waiting for binlog update | NULL | mysql show slave status; | master.domain.com | replicate | 3306| 60| | 4 | slave-relay-bin.002 | 4 | | No | Yes | | | 0 || 0| 0 | 4 | Any tips/advice? TIA! Froilan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php on the slave you should run show slave status also on the slave check /path/to/mysql/master.info this file gives you good info and I had to change it manually when I changed master server names in the my.cnf file also, add set-variable = slave_net_timeout=3600 set-variable = net_read_timeout=3600 in the my.cnf on the slave to keep it from fillng the log with junk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication problem
Walt, At 01:09 PM 8/20/2002 -0400, walt wrote: on the slave you should run show slave status on slave: mysql show slave status; +-+-+-+---+-+-+--+---+---+--+---+-+-+++--+-+-+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +-+-+-+---+-+-+--+---+---+--+---+-+-+++--+-+-+ | master.idsk.com | replicate | 3306| 60| | 4 | slave-relay-bin.001 | 4 | | No | Yes | | | 0 || 0| 0 | 4 | +-+-+-+---+-+-+--+---+---+--+---+-+-+++--+-+-+ 1 row in set (0.00 sec) What in particular should I be looking at here? I can see Slave_SQL_Running: Yes. When i insert info into one of the master's table, slave's show master status is: ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | slave-bin.001 | 79 | | | ++--+--+--+ master's show master status is: ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | master-bin.002 | 162 | | | ++--+--+--+ I re installed everything and still got the same error: 020820 18:05:20 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 020820 18:05:20 Error updating slave list: 020820 18:05:20 Slave I/O thread exiting, read up to log 'FIRST', position 4 also on the slave check /path/to/mysql/master.info this file gives you good info and I had to change it manually when I changed master server names in the my.cnf file my slave's master.info looks like: 4 master.idsk.com replicate replicatedb 3306 60 138206092 Anything wrong with it? also, add set-variable = slave_net_timeout=3600 set-variable = net_read_timeout=3600 I have added this one, thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication problem
On Tuesday 20 August 2002 09:02 pm, Froilan Mendoza wrote: Walt, At 01:09 PM 8/20/2002 -0400, walt wrote: on the slave you should run show slave status on slave: mysql show slave status; +-+-+-+---+ -+-+--+---+- --+--+---+-+ -+++--+- +-+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +-+-+-+---+ -+-+--+---+- --+--+---+-+ -+++--+- +-+ | master.idsk.com | replicate | 3306| 60| | 4 | slave-relay-bin.001 | 4 | | No | Yes | | | 0 || 0| 0 | 4 | +-+-+-+---+ -+-+--+---+- --+--+---+-+ -+++--+- +-+ 1 row in set (0.00 sec) What in particular should I be looking at here? I can see Slave_SQL_Running: Yes. When i insert info into one of the master's table, slave's show master status is: ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | slave-bin.001 | 79 | | | ++--+--+--+ master's show master status is: ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | master-bin.002 | 162 | | | ++--+--+--+ I re installed everything and still got the same error: 020820 18:05:20 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 020820 18:05:20 Error updating slave list: 020820 18:05:20 Slave I/O thread exiting, read up to log 'FIRST', position 4 also on the slave check /path/to/mysql/master.info this file gives you good info and I had to change it manually when I changed master server names in the my.cnf file my slave's master.info looks like: 4 master.idsk.com replicate replicatedb 3306 60 138206092 Anything wrong with it? also, add set-variable = slave_net_timeout=3600 set-variable = net_read_timeout=3600 I have added this one, thanks. Hmmm... When you set up the master database, did you copy all the *.frm, *.MYD, *.MYI to the slave database? I also noticed in your email the my.cnf file on the master had serveri-d instead of server-id Try removing bin-log from the my.cnf on the slave. If I remember correctly, there is a way to set up the slave to keep archives of the updates. The master.info file on the slave should look something like this Ignor stuff in quotes, I added that. walt-bin.950 log file on the master 73 position of the log file on the master walt.nea-fast.com master server name repl replication user name passwd replication password 3306 port 60 6 60 Let me know if any of this helps! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication Problem
Apparently I had list File permissions with my user. Thanks anyway. -James -Original Message- From: James Kelty [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:06 AM To: [EMAIL PROTECTED] Subject: Replication Problem Hello, I have this replication problem. I am getting this error on the slave: 020816 16:30:49 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020816 16:30:49 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 020816 16:30:49 Error reading packet from server: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (server_errno=1045) But I have verified through the command line that this user has the ability to contact the master, use the database, and that the password in /etc/my.cnf if correct. Any other reason that this is failing? -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication problem
On Thu, 27 Jun 2002 17:37:24 -0700 Patelli Paolo [EMAIL PROTECTED] wrote: mysql SHOW MASTER STATUS; ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | keynes-bin.023 | 399 | | | ++--+--+--+ On slave: mysql SHOW SLAVE STATUS; +-+-+-+---+--+-+---+-+-+++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +-+-+-+---+--+-+---+-+-+++--+ | keynes | repl| 3306| 60| | 31 | Yes | | | 0 | | 0| +-+-+-+---+--+-+---+-+-+++--+ go to your slave server ... and do this query slave stop;change master to master_log_file=keynes-bin.023;change master to master_log_pos=4;slave start; -- Let's call it an accidental feature. -- Larry Wall sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php