Combine MySQL databases ignoring keys

I would like to do something like this;

on server1; mysqldump mysql > db1.sql
on server2; mysqldump mysql > db2.sql

on server3 (new server);

mysql –force mysql < db1.sql
mysql –force mysql < db2.sql which should give me db1.sql users root etc but it should combination of the users from db1 + db2, so for instance; db1.sql => users{root,a,b,c}
db2.sql => users{root,a,d,e}

on server3 I want to see;

users{root,a,b,c,d,e}

Ofcourse that doesn’t work, as you get duplicate key messages on all double users.

So ofcourse I wrote something to fix that;

I first did;

mysql mysql < db1.sql
mysql mysqlold < db2.sql
PHP:


<?
mysql_connect("localhost", "", "");
mysql_select_db("mysqlold");

$q = mysql_query("select * from mysqlold.user");
while ($r = mysql_fetch_row($q)) {
$q1 = mysql_query("select * from mysql.user where User='".$r[1]."'");
if (mysql_num_rows($q1)) {
continue;
}
$s="";
foreach($r as $r1) {
if ($s) $s.=",";
$s.="'".$r1."'";
}
$qry = "insert into mysql.user values ($s)";
mysql_query($qry);
}
?>

Ofcourse, writing everything yourself was again much faster than searching the solution in the MySQL manual or on Google.

Be the first to leave a comment. Don’t be shy.

Join the Discussion

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>