Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
Keeping in mind the risks that VSarkiss has already explained, there are two possibilities.
Short (cheating) answer, without using Perl :-)
$ mysqldump --add-drop-table -h host1 db1 | mysql -h host2 db2
If you want to add one column without erasing the data, you can modify your master database and then do like this:
# copies the target data without the structure. Creates a # complete SQL statement including all the field names. $ mysqldump -t -c -h host1 db2 > db2.sql # copies the structure from db1 to db2, without data $ mysqldump -d -h host1 db1 | mysql -h host2 db2 # reinserts the data $ mysql -h host2 db2 < db2.sql
Notice that this code does not work if you want to remove a column. If you just add or modify a column, it should work fine.

The (long) Perl answer is more flexible, exploiting some DBI features to check the differences between two databases and eventually fix them.
This node shows how to do a basic comparison between two databases.
For something more structure oriented, the following script can achieve most of what you want. It can determine if one table is in one database and missing from the other one, and warn you if there are differences.
Since you want to have a copy of the same database in many locations, I assume that you you want all the "satellites" to be like the central one. If this is the case, it should be easy to modify the check_diff sub to drop the offending table and re-create it.
To create an "ALTER TABLE" statement, you should loop inside the table structure. An example of how to do it is in the code of DBSChema::Normalizer.
If your goal is to have the same table structure in both databases, you can just run this script twice:
$ ./dbdiff host1 db1 host2 db2 $ ./dbdiff host2 db2 host1 db1
This way, tables that did not exist in host 1 will be created in host 2 and vice versa.
Notice that the script does not modify your databases, but only creates the necessary statements that you should execute.
It is not a complete solution, but it should be a good start.
HTH
#!/usr/bin/perl -w use strict; use DBI; sub help { print STDERR "syntax dbidiff host1 db1 host2 db2\n"; print STDERR "reverse the order of hosts and DBs for", "a complete synch\n"; exit; } my $host1 = shift || help(); my $db1 = shift || help(); my $host2 = shift || help(); my $db2 = shift || help(); #change connection parameters to suit your needs. #username and password are taken from the configuration #file in the user's home dir my $dbh1 = DBI->connect("DBI:mysql:$db1;host=$host1;" ."mysql_read_default_file=$ENV{HOME}/.my.cnf", undef,undef,{RaiseError => 1}); my $dbh2 = DBI->connect("DBI:mysql:$db2;host=$host2;" ."mysql_read_default_file=$ENV{HOME}/.my.cnf", undef,undef,{RaiseError => 1}); # reads the table list from both databases my %tables1 = map {$_,1} $dbh1->tables(); my %tables2 = map {$_, 1} $dbh2->tables(); sub get_table_struct { my $host_num = shift; my $dbname = shift; my $tablename = shift; my $dbh = $host_num == 1 ? $dbh1 : $dbh2; my $sth = $dbh->prepare(qq{SHOW CREATE TABLE $dbname.$tablenam +e}); $sth->execute(); my ($dummy, $table_creation) = $sth->fetchrow_array(); $sth->finish(); return $table_creation; } sub copy_table { my $tablename = shift; print "# New table to create on $host2 - $db2\n"; print "#\n# CREATING TABLE $tablename\n#\n\n"; print get_table_struct(1, $db1,$tablename), "\n"; } sub check_diff { my $tablename = shift; my $tc1 = get_table_struct(1, $db1, $tablename); my $tc2 = get_table_struct(2, $db2, $tablename); if ($tc1 ne $tc2) { print "# `$tablename` is different\n"; #uncomment the next two lines to fix the differences "brute f +orce" #print "DROP TABLE $tablename;\n"; #print "$tc1\n" } } # # The main loop. Here the two databases get compared. # for (keys %tables1) { if(exists $tables2{$_}) { check_diff($_); } else { copy_table ($_); } } $dbh1->disconnect(); $dbh2->disconnect();
update Jan 22, 2003 I found an utility on CPAN, MySQL::Diff , which does exactly what is requested.
_ _ _ _ (_|| | |(_|>< _|

In reply to Re: DBI -- Synchronize table diffs? by gmax
in thread DBI -- Synchronize table diffs? by twerq

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Domain Nodelet?
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this?Last hourOther CB clients
    Other Users?
    Others making s'mores by the fire in the courtyard of the Monastery: (3)
    As of 2024-07-13 07:36 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found

      Notices?
      erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.