MySQL DB Syncing, Remote->Local

Test

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.

1. <p>    //update the remote database
   
2.    
   
3.     //print a message
   
4.     function msg($msg) {
   
5.         print "$msg <br /><hr>";
   
6.     }
   
7.    
   
8.     msg("Connecting to databases");
   
9.    
   
10.     //open connections to both DB's
    
11.     include("php/db.php");
    
12.     $locdb = new DB(true);
    
13.     $remdb = new DB();
    
14.     $remdb->connect("server", "user", "pass", "database_name");
    
15.    
    
16.     if ($locdb->error) {
    
17.         msg($locdb->err);
    
18.         exit;
    
19.     }
    
20.     if ($remdb->error) {
    
21.         msg($remdb->err);
    
22.         exit;
    
23.     }
    
24.     msg("Connected!");
    
25.    
    
26.     //file path info
    
27.     //where you want the dump files to go
    
28.     $filedump = 'c:\web\summit_new\dumps\\';
    
29.     //where your mysql bin is (not needed for *nix)
    
30.     $mysqlbin = 'c:\wamp\mysql\bin\\';
    
31.     //where your putty is (not needed for *nix, use openssh)
    
32.     $putty = 'c:\Program Files\PuTTy\\';</p><p>
    
33.     msg("Backing up both databases");
    
34.    
    
35.     //array of system commands
    
36.     $system = array(
    
37.         "{$mysqlbin}mysqldump -u root --skip-add-locks --add-drop-table --create-options --extended-insert --compatible=mysql40  database_name tables > {$filedump}local_dump." .time() .".sql",
    
38.         ""{$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"
    
39.     );
    
40.    
    
41.    
    
42.     foreach($system as $cmd) {
    
43.         msg("System Call:<br />$cmd");
    
44.         system($cmd);
    
45.     }
    
46.         
    
47.       msg("Backup complete. Dump files at $filedump");
    
48.         </p><p>    //array of tables to sync
    
49.     $tables = array("page_info", "page_content");
    
50.    
    
51.     foreach ($tables as $table) {
    
52.    
    
53.         msg("Using table `$table`");
    
54.         
    
55.         $locdb->table = $table;
    
56.         $remdb->table = $table;
    
57.         if (mysql_num_fields($locdb->select()) != mysql_num_fields($remdb->select())) {
    
58.             msg("Column mismatch in table `$table`.  Use dump files to import data");
    
59.             break;
    
60.         }
    
61.                
    
62.         msg("Emptying remote table `$table`");
    
63.         $remdb->remove();
    
64.         if ($remdb->error) {
    
65.             msg($remdb->error);
    
66.         }
    
67.         
    
68.         msg("Importing local db content into remote db");
    
69.         while ($locrow = mysql_fetch_assoc($locdb->result)) {
    
70.             $rows = "";
    
71.             $vals = array();
    
72.             
    
73.             foreach ($locrow as $key => $value) {
    
74.                 $rows .= ",$key";
    
75.                 $vals[] = $value;
    
76.             }
    
77.             $rows = preg_replace("/^,/", "", $rows);
    
78.             
    
79.             $remdb->insert($rows, $vals);
    
80.             if ($remdb->error) {
    
81.                 msg($remdb->error);
    
82.             }
    
83.         }
    
84.         
    
85.     }
    
86.     msg("Remote database updated");
    
87.     exit; </p>

复制代码

still having problems with this line though:

1. ""{$putty}plink" -pw ssh_pass <a href="mailto:ssh_user@server">ssh_user@server</a> 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"

复制代码

try another approach (using mysqldump remotely) rather than instantiating a new SSH session, provided of course the -h parameter will work in this situation.           

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