两个不同远程机器上的两个MySQL数据库之间的区别(mysqldiff使用说明) [英] Difference between two MySQL databases which are on two different remote machines (mysqldiff usage clarification)
问题描述
我只想了解两个不同远程机器上的两个mysql数据库之间的区别,我知道 mysqldiff 可以用于这种目的,我使用它。但问题是,我可以找到我的(或本地)机器上的数据库与远程数据库之间的区别,但是,如果两个数据库,我们想要找到的差异是在两个不同的远程机器上,这是不可能的(或我不知道如何使用它)。
I just want to find out the difference between two mysql databases which are on two different remote machines, I know mysqldiff can be used for this kind of purpose, and I am using it. But the problem is, I can find the difference between the db on my (or local) machine with a remote database, But, If both databases , for which we want to find the difference are on two different remote machines, it is not possible (or I dont know how to use it). can anybody tell me how to achieve this.
提前感谢
推荐答案
p>这里是我用来比较两个不同的MySQL数据库的脚本。要比较两个远程数据库,您只需要将 $ db1_host
从 localhost
更改为其他远程的IP地址
Here's a script I use to compare two different MySQL databases. To compare two remote databases you'll just need to change $db1_host
from localhost
to the IP address of the other remote server.
<?php
//------------------------------------------------------------------------------
// Define the variables we'll be using.
//------------------------------------------------------------------------------
$db1_con = NULL;
$db1_constraints = array();
$db1_dbname = 'db1';
$db1_host = 'localhost';
$db1_password = 'password1';
$db1_tables = array();
$db1_username = 'username1';
$db2_con = NULL;
$db2_constraints = array();
$db2_dbname = 'db2';
$db2_host = '123.123.123.123';
$db2_password = 'password2';
$db2_tables = array();
$db2_username = 'username2';
//------------------------------------------------------------------------------
// Connect to the databases.
//------------------------------------------------------------------------------
try{
$db1_con = new PDO("mysql:host=$db1_host;dbname=information_schema", $db1_username, $db1_password);
$db1_con->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); // Try to use the driver's native prepared statements.
$db1_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Let's use exceptions so we can try/catch errors.
}catch(PDOException $e){
echo "<p>Connection failed for $db1_host: " . $e->getMessage() . '</p>';
exit;
}
try{
$db2_con = new PDO("mysql:host=$db2_host;dbname=information_schema", $db2_username, $db2_password);
$db2_con->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); // Try to use the driver's native prepared statements.
$db2_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Let's use exceptions so we can try/catch errors.
}catch(PDOException $e){
echo "<p>Connection failed for $db2_host: " . $e->getMessage() . '</p>';
exit;
}
if (NULL !== $db1_con && NULL !== $db2_con){
echo "<h2>Column Analysis</h2>";
$sql = 'SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME, ORDINAL_POSITION';
$statement1 = $db1_con->prepare($sql);
$statement1->bindValue(1, $db1_dbname);
$statement2 = $db2_con->prepare($sql);
$statement2->bindValue(1, $db2_dbname);
if (TRUE === $statement1->execute()){
while ($row = $statement1->fetch(PDO::FETCH_ASSOC)){
$db1_tables[$row['TABLE_NAME']][$row['COLUMN_NAME']] = array();
foreach ($row AS $key => $value){
$db1_tables[$row['TABLE_NAME']][$row['COLUMN_NAME']][$key] = $value;
}
}
}
if (TRUE === $statement2->execute()){
while ($row = $statement2->fetch(PDO::FETCH_ASSOC)){
$db2_tables[$row['TABLE_NAME']][$row['COLUMN_NAME']] = array();
foreach ($row AS $key => $value){
$db2_tables[$row['TABLE_NAME']][$row['COLUMN_NAME']][$key] = $value;
}
}
}
foreach ($db1_tables AS $table => $info){
if (!isset($db2_tables[$table])){
echo "<p>Table <strong>$table</strong> does not exist in the SECOND database!</p>";
}else{
foreach ($info AS $column => $data){
if (!isset($db2_tables[$table][$column])){
echo "<p>Column <strong>$column</strong> does not exist in table <strong>$table</strong> in the SECOND database!</p>";
}else{
if (count($data)){
foreach ($data AS $key => $value){
if ($db1_tables[$table][$column][$key] !== $db2_tables[$table][$column][$key]){
echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> has differing characteristics for <strong>$key</strong> (". $db1_tables[$table][$column][$key] ." vs. ". $db2_tables[$table][$column][$key] .")</p>";
}
}
}
}
}
}
}
foreach ($db2_tables AS $table => $info){
if (!isset($db1_tables[$table])){
echo "<p>Table <strong>$table</strong> does not exist in the FIRST database!</p>";
}else{
foreach ($info AS $column => $data){
if (!isset($db1_tables[$table][$column])){
echo "<p>Column <strong>$column</strong> does not exist in table <strong>$table</strong> in the FIRST database!</p>";
}else{
if (count($data)){
foreach ($data AS $key => $value){
if ($db2_tables[$table][$column][$key] !== $db1_tables[$table][$column][$key]){
echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> has differing characteristics for <strong>$key</strong> (". $db2_tables[$table][$column][$key] ." vs. ". $db1_tables[$table][$column][$key] .")</p>";
}
}
}
}
}
}
}
echo "<h2>Constraint Analysis</h2>";
$sql = 'SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME, ORDINAL_POSITION';
$statement1 = $db1_con->prepare($sql);
$statement1->bindValue(1, $db1_dbname);
$statement2 = $db2_con->prepare($sql);
$statement2->bindValue(1, $db2_dbname);
if (TRUE === $statement1->execute()){
while ($row = $statement1->fetch(PDO::FETCH_ASSOC)){
foreach ($row AS $key => $value){
$db1_constraints[$row['TABLE_NAME']][$row['COLUMN_NAME']][$key] = $value;
}
}
}
if (TRUE === $statement2->execute()){
while ($row = $statement2->fetch(PDO::FETCH_ASSOC)){
foreach ($row AS $key => $value){
$db2_constraints[$row['TABLE_NAME']][$row['COLUMN_NAME']][$key] = $value;
}
}
}
foreach ($db1_constraints AS $table => $info){
foreach ($info AS $column => $data){
if (isset($db2_constraints[$table][$column])){
if (count($data)){
foreach ($data AS $key => $value){
if ('CONSTRAINT_NAME' !== $key && $db1_constraints[$table][$column][$key] !== $db2_constraints[$table][$column][$key]){
echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> has differing characteristics for <strong>$key</strong> (". $db1_constraints[$table][$column][$key] ." vs. ". $db2_constraints[$table][$column][$key] .")</p>";
}
}
}
}else{
echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> is missing a constraint in the SECOND database!</p>";
}
}
}
foreach ($db2_constraints AS $table => $info){
foreach ($info AS $column => $data){
if (isset($db1_constraints[$table][$column])){
if (count($data)){
foreach ($data AS $key => $value){
if ('CONSTRAINT_NAME' !== $key && $db2_constraints[$table][$column][$key] !== $db1_constraints[$table][$column][$key]){
echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> has differing characteristics for <strong>$key</strong> (". $db2_constraints[$table][$column][$key] ." vs. ". $db1_constraints[$table][$column][$key] .")</p>";
}
}
}
}else{
echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> is missing a constraint in the FIRST database!</p>";
}
}
}
}
?>
这篇关于两个不同远程机器上的两个MySQL数据库之间的区别(mysqldiff使用说明)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!