in reply to mirroring mySQL database

Well, this doesn't look like an entirely Perl related problem, and I have a non-entirely-Perl-related answer. :-)
If your tables are not timestamped, an alternative solution could be reached through log-update files.
In your my.cnf, you can have the following:
[mysqld] log-update log-long-format
It will produce a log with a copy of all the commands issued to the server. The long-format business means that before each query there is a comment with information about user, IP, date and time of the query.
This log is a SQL script that you can present to MySQL and replicate all the changes made since the start of the log. (Whith the exception of LOAD DATA queries: for these ones you would need the original input files as well).
Here, finally, enters Perl, which you can use to parse this log and send the lines you want to the second server.
It would help if you saved the log on a regular basis (it grows really BIG!).
The resulting format of the log-file is something like
# Time: 011206 9:47:01 # User@Host: john[john] @ [10.10.10.112] INSERT INTO official_holidays (OHDate,OHName) VALUES ( "2000/01/01" , +"New Years Day");
Parsing this format in Perl is not straightforward but not extremely difficult either.
For example, to get all the records after a given date and time, you could use:
#!/usr/bin/perl -w use strict; my $start_copy = 0; while (<>) { next if /^\s+$/; if ((!$start_copy) and (/^# Time: (\d+)\s+(\d+):(\d\d):(\d\d)/)) { my $date = $1; my $hour = $2; # check if date and time are what you need if ((substr($1,4,2) eq "06") and ($hour gt "09")) { $start_copy = 1; } } print if $start_copy; }
The regex should be fortified a bit, but I am sure you got the idea.

One more thing: within your applications, you can decide not to update the logs temporarily, by setting the SQL_LOG_OFF variable to "1". More on that in the manual.
Cheers
gmax

Replies are listed 'Best First'.
Re: Re: mirroring mySQL database
by gmax (Abbot) on Dec 06, 2001 at 16:20 UTC
    update
    Sorry, it was SQL_LOG_UPDATE=0
    The previous one (SQL_LOG_OFF=1) will only affect the general log file. And, in addition, they have different semantics. :-(
    summary:
    my $query = qq{ SET SQL_LOG_OFF=1; # no logs to the general log SET SQL_LOG_UPDATE=0; # no logs to the update log. UPDATE $table SET bigField = $BLOB_value WHERE ID=1; SET SQL_LOG_OFF=0; # logs to the general log SET SQL_LOG_UPDATE=1; # logs to the update log. UPDATE $another SET smallfield = $smallvalue WHERE ID =3; }; $dbh->do($query);