MySQL DB Syncing, Remote->Local

[TJ111]

I was just wondering if anyone has used any DB syncing scripts/programs with MySQL. Basically I just want to have a script I can run as a cron.daily or something that will dump a remote database and update my local version of it. There's alot of tools/programs/scripts turned up in google results, so just wondering if you guys have any recommendations. I don't want anything fancy, just very basic and easy to customize (ie a bash or perl script, or similar).

[chazzy]

why not use replication?

[TJ111]

It's more then I need. I don't have control over the remote database, for starters, plus changes happening will happen on the scale of only 10-20 changes per day. It's for a testing environment only, and just want my local testing environment to match the remote one.

[chazzy]

what if you just use mysqldump on the remote system, and just drop and recreate the database locally? i don't think there's any need to do a diff, is there?

[TJ111]

I might write a little bash script to do it automatically, just seeing if anyone else had seen/used anything real basic to do this before. Also another quick question, the remote server is MySQL 4.1 and locally I have 5.0. Will this effect the queries at all? Ie things like this end up in the mysqldump file:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
.....

/*!40000 ALTER TABLE `page_info` DISABLE KEYS */;
LOCK TABLES `page_info` WRITE;
INSERT INTO ....etc

When doing it manually I just delete those things as I'm not sure if it'd cause issues with 5.0.

[chazzy]

definitely. MySQL doesn't know what backwards compatibility is.

[TJ111]

Ugh, looks like I'm stuck with using ye olde manual mode. I could write a script for it, but it'd have to be riddled with regular expressions and the like and would be more complicated then its probably worth.

[TJ111]

In case anyone googles this or something, here is the solution I came up with. It uses putty + ssh and the system() function to make sql dumps before performing the script, but if you are gutsy you can just omit that. It also uses my database class, but it would be pretty easy to just swap them out for generic mysql statements.

//update the remote database

//print a message
function msg($msg) {
print "$msg <br /><hr>";
}

msg("Connecting to databases");

//open connections to both DB's
include("php/db.php");
$locdb = new DB(true);
$remdb = new DB();
$remdb->connect("server", "user", "pass", "database_name");

if ($locdb->error) {
msg($locdb->err);
exit;
}
if ($remdb->error) {
msg($remdb->err);
exit;
}
msg("Connected!");

//file path info
//where you want the dump files to go
$filedump = 'c:\web\summit_new\dumps\\';
//where your mysql bin is (not needed for *nix)
$mysqlbin = 'c:\wamp\mysql\bin\\';
//where your putty is (not needed for *nix, use openssh)
$putty = 'c:\Program Files\PuTTy\\';


msg("Backing up both databases");

//array of system commands
$system = array(
"{$mysqlbin}mysqldump -u root --skip-add-locks --add-drop-table --create-options --extended-insert --compatible=mysql40 database_name tables > {$filedump}local_dump." .time() .".sql",
"\"{$putty}plink\" -pw ssh_pass ssh_user@server mysqldump -u db_user -pdb_pass --skip-lock-tables --skip-add-locks --add-drop-table --create-options --extended-insert db_name db_tables > {$filedump}remote_dump.".time().".sql"
);


foreach($system as $cmd) {
msg("System Call:<br />$cmd");
system($cmd);
}

msg("Backup complete. Dump files at $filedump");


//array of tables to sync
$tables = array("page_info", "page_content");

foreach ($tables as $table) {

msg("Using table `$table`");

$locdb->table = $table;
$remdb->table = $table;
if (mysql_num_fields($locdb->select()) != mysql_num_fields($remdb->select())) {
msg("Column mismatch in table `$table`. Use dump files to import data");
break;
}

msg("Emptying remote table `$table`");
$remdb->remove();
if ($remdb->error) {
msg($remdb->error);
}

msg("Importing local db content into remote db");
while ($locrow = mysql_fetch_assoc($locdb->result)) {
$rows = "";
$vals = array();

foreach ($locrow as $key => $value) {
$rows .= ",$key";
$vals[] = $value;
}
$rows = preg_replace("/^,/", "", $rows);

$remdb->insert($rows, $vals);
if ($remdb->error) {
msg($remdb->error);
}
}

}
msg("Remote database updated");
exit;


I'm still having problems with this line though:

"\"{$putty}plink\" -pw ssh_pass ssh_user@server mysqldump -u db_user -pdb_pass --skip-lock-tables --skip-add-locks --add-drop-table --create-options --extended-insert db_name db_tables > {$filedump}remote_dump.".time().".sql"
Which is odd because when I print the output of that and copy and paste it into cmd it works perfectly. But right now when called from php it is creating an empty sql file.

[chazzy]

ok, just like i said earlier, why not just use mysqldump? you can use it on particular tables if you want. do it once to create a .sql file w/ the data, and then execute that sql script using a connection to the other db.

[TJ111]

I am using mysqldump, but only for backup reasons here. I have very limited privileges on the remote server, and was having issues with sql being output by my db (even with the --compatible=mysql40 flag) due to the limited privileges. I have to go in and manually edit the sql and then copy+paste it into a mysql shell. So to avoid having to due that daily for the next 2-3 months (on ~15 decently large tables), I just wrote this little script (which does work). The only issue I have is with the remote sqldump outputting a blank sql file when called from within PHP (but not if I copy+paste into cmd.exe).

[chazzy]

Are you able to do a standard mysql connect to the remote machine? something like this


mysql -u someuser -psomepassword --host=IP_OR_NAME_OF_THE_OTHER_BOX


?

If so, maybe you could just use the -h/--host option on mysqldump to connect.

[TJ111]

I can already connect to the server and perform a mysqldump, my only problem is that its outputting a blank file.

$system = array(
/*.....*/,
"\"{$putty}plink\" -pw ssh_pass ssh_user@server mysqldump -u db_user -pdb_pass --skip-lock-tables --skip-add-locks --add-drop-table --create-options --extended-insert db_name db_tables > {$filedump}remote_dump.".time().".sql"
);


foreach($system as $cmd) {
msg("System Call:<br />$cmd");
system($cmd);
}

The msg function prints the following:

System Call:
"c:\Program Files\PuTTy\plink" -pw ***** ****@***** mysqldump -u ***** -p***** --skip-lock-tables --skip-add-locks --add-drop-table --create-options --extended-insert tj****_db page_content page_info > c:\web\summit_new\dumps\remote_dump.1204919258.sql
When I copy+paste that into a windows command prompt, it outputs the full sqldump to the correct folder. However, for whatever reason, when called within PHP puts out a blank file. Quite confounding lol. Thanks again for the help.

[chazzy]

you may be able to connect already, but you haven't been able to generate the file properly. my thought is to try another approach (using mysqldump remotely) rather than instantiating a new SSH session, provided of course the -h parameter will work in this situation.

[TJ111]

Wow, you sir are a genious. I spent all day yesterday staring at the mysqldump documentation (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html), and if you look it doesn't mention the -h or --host flags anywhere on its table of parameters (but does mention down in the long list of definitions, so I must of scanned right over it). I've always just performed dumps from the shell, or a script on the host machine at least, so I was just doing it the only way I know.

Works great now with the "-h server" flag set, and is much easier on the eyes. Plus it eliminates the middle man all together. Thanks.