设为首页收藏本站

BC Morning Updates

 找回密码
 注册

QQ登录

只需一步,快速开始

查看: 370|回复: 0

MySQL DB Syncing, Remote->Local

[复制链接]
发表于 2015-3-16 10:06:27 | 显示全部楼层 |阅读模式
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.  

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|BC Morning Website ( Best Deal Inc. 001 )  

GMT-8, 2019-9-21 09:24 , Processed in 0.124835 second(s), 19 queries .

Supported by Bestdeal Online

© 2008-2019 BestDeal Online Inc.

快速回复 返回顶部 返回列表