relay log file maintenance problem
I have a replication server that is running out of disk space because of old relay binary log files. Both it and the master server are version 4.0.20. I have a cron script that maintains a 3 day history of binary log files on the master server. Has someone figured out a way to manage the number relay log files so that someone doesn't have to check a replication server to see if the filesystem for the MySQL data directory hasn't been filled by old relay log files that haven't been accessed for some specified time? Thanks in advance, -bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table doesn't exist on query replication problem...
Hi, I have a mysql 4.0.21 master and a 4.0.18 slave. I am trying to maintain a replication of a DB named masterdb. My goal is to have multiple slaves with a copy of masterdb. On the master I specified in my.cnf binlog-do-db=masterdb. On the slave I specified replicate-do-db=masterdb in my.cnf. I took a snap shot of masterdb as per the user documentation. I loaded this on the slave server and set up the slave to point to the master using the changer master to ... following the proper syntax. The slave halts with the following messages in the .err file: 041006 12:06:06 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'aaa-bin.008' at position 79 ERROR: 1146 Table 'reporting.linkdata' doesn't exist 041006 12:06:37 Slave: Error 'Table 'reporting.linkdata' doesn't exist' on query 'replace into linkfilters (ip,sstatus,cstatus,dusers) select ip, sum(spam), sum(total), sum(dusers) from reporting.linkdata as a left join reporting.users as u on u.eid=a.eid where unix_timestamp(u.lastupdate) = 0 group by ip having sum(a.total)=15 and sum(a.dusers)=6'. Default database: 'masterdb', Error_code: 1146 041006 12:06:37 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'aaa-bin.007' position 73927 On the master server there is a database named reporting that does not exist on the slave and should not exist because it is used by the master server only. From my reading of the MySQL documentation on the binary log files works, (correct me if I am wrong. PLEASE!) the master binary log file records all transactions needed to rebuild the database(s) in the event of a catastrophe. In the context of replication, (from a high level view) the slave server reaches into the master and executes transactions to update itself (something like 'mysqlbinlog aaa-bin.007 |mysql -u rep -h slave'), correct? If this assumption about replication is correct then the query: 'replace into linkfilters (ip,sstatus,cstatus,dusers) select ip, sum(spam), sum(total), sum(dusers) from reporting.linkdata as a left join reporting.users as u on u.eid=a.eid where unix_timestamp(u.lastupdate) = 0 group by ip having sum(a.total)=15 and sum(a.dusers)=6' holds the key to my problem because reporting.linkdata and reporting.users do not exist on the slave since it has no concept of the database named reporting. Is there a way to rework this query so that either there are no references to the reporting database or to configure my.cnf on the master and slave to handle this query? Thanks in advance, -Bill Thomason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem with a cross database query
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. Thanks in advance, -Bill Thomason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
In replication, can you have a slave act as a master to a different slave?
Here is my scenario: I have 3 linux boxes - 2 with mysql 4.0.16 and 1 with 4.0.17. I have a 4.0.16 master (sys1) replicating a database (db1) that is roughly 1.5GB to a slave system (sys2) running 4.0.17. For the sake of a formal representation I'll say that (sys1,db1) -- (sys2,db1) represents this relationship. On sys2 we run an application that inspects the replication of db1 and maintains information in a different database (db2). I would like sys2 to replicate db2 to our third system (sys3) which happens to be running version 4.0.16. (sys2,db2) -- (sys3,db2). In this situation, can system sys2, who is a slave to sys1 for db1, be a master for db2 to a slave system sys3? If this is possible could someone help me by inspecting my my.cnf files and tell me why the second step (sys2,db2) -- (sys3,db2) does not replicate? Here are pieces of the my.cnf files that should be relevant. If someone can tell me what I'm doing wrong or has some suggestions I would greatly appreciate it. my.cnf from sys1: == [mysqld] log-bin bind-address = 192.168.1.2 server-id=1 log-bin=sys1-bin log-bin-index=sys-bin.index binlog-do-db=db1 set-variable = max_binlog_size=20M my.cnf from sys2: == [mysqld] log-bin bind-address = 192.168.1.3 server-id=2 master-host=192.168.1.2 master-user=sys2 master-password=sys2password master-port=3307 master-connect-retry=60 replicate-do-db=db1 log-warnings log-slave-updates report-host=192.168.1.3 report-port=3307 log-bin=sys2-bin log-bin-index=sys2-bin.index binlog-do-db=db2 set-variable=max_binlog_size=20M my.cnf from sys3: == bind-address=192.168.1.4 server-id=3 master-host=192.168.1.3 master-user=sys3 master-password=sys3password master-port=3307 replicate-do-db=db2 log-warnings log-slave-updates master-connect-retry=60 report-host=192.168.1.4 report-port=3307