#!/usr/bin/perl -w
=head1 NAME
mysqldiff - compare MySQL database schemas
=head1 SYNOPSIS
mysqldiff [B<options>] B<database1> B<database2>
mysqldiff --help
=head1 DESCRIPTION
F<mysqldiff> is a Perl script front-end to the
L<CPAN|http://www.perl.com/CPAN> module
L<MySQL::Diff|http://search.cpan.org/search?module=MySQL::Diff> which
compares the data structures (i.e. schema / table definitions) of two
L<MySQL|http://www.mysql.com/> databases, and returns the differences
as a sequence of MySQL commands suitable for piping into F<mysql>
which will transform the structure of the first database to be
identical to that of the second (I<c.f.> F<diff> and F<patch>).
Database structures can be compared whether they are files containing
table definitions or existing databases, local or remote.
B<N.B.> The program makes I<no> attempt to compare any of the data
which may be stored in the databases. It is purely for comparing the
table definitions. I have no plans to implement data comparison; it
is a complex problem and I have no need of such functionality anyway.
However there is another program
L<coldiff|http://rossbeyer.net/software/mysql_coldiff/>
which does this, and is based on an older program called
F<datadiff> which seems to have vanished off the 'net.
For PostgreSQL there are similar tools such as
L<pgdiff|http://pgdiff.sourceforge.net/> and
L<apgdiff|http://apgdiff.startnet.biz/>.
=head1 EXAMPLES
# compare table definitions in two files
mysqldiff db1.mysql db2.mysql
# compare table definitions in a file 'db1.mysql' with a database 'db2'
mysqldiff db1.mysql db2
# interactively upgrade schema of database 'db1' to be like the
# schema described in the file 'db2.mysql'
mysqldiff -A db1 db2.mysql
# compare table definitions in two databases on a remote machine
mysqldiff --host=remote.host.com --user=myaccount db1 db2
# compare table definitions in a local database 'foo' with a
# database 'bar' on a remote machine, when a file foo already
# exists in the current directory
mysqldiff --host2=remote.host.com --password=secret db:foo bar
=head1 OPTIONS
=over 4
=item C<-?, --help>
show usage
=item C<-A, --apply>
interactively patch database1 to match database2
=item C<-B, --batch-apply>
non-interactively patch database1 to match database2
=item C<-d, --debug[=N]>
enable debugging [level N, default 1]
=item C<-l, --list-tables>
output the list off all used tables
=item C<-o, --only-both>
only output changes for tables in both databases
=item C<-k, --keep-old-tables>
don't output DROP TABLE commands
=item C<-n, --no-old-defs>
suppress comments describing old definitions
=item C<-t, --table-re=REGEXP>
restrict comparisons to tables matching REGEXP
=item C<-i, --tolerant>
ignore DEFAULT, AUTO_INCREMENT, COLLATE, and formatting changes
=item C<-S, --single-transaction>
perform DB dump in transaction
For more info see: http://dev.mysql.com/doc/refman/en/mysqldump.html#option_mysqldump_single-transaction
=item C<-h, --host=[hostname]>
connect to host
=item C<-P, --port=[port]>
use this port for connection
=item C<-u, --user=[user]>
user for login if not current user
=item C<-p, --password[=password]>
password to use when connecting to server
=item C<-s, --socket=...>
socket to use when connecting to server
=back
=head2 For <databaseN> only, where N == 1 or 2
=over 4
=item C<--hostN=[hostN]>
connect to host
=item C<--portN=[portN]>
use this port for connection
=item C<--userN=[userN]>
user for login if not current user
=item C<--passwordN[=passwordN]>
password to use when connecting to server
=item C<--socketN=[socketN]>
socket to use when connecting to server
=back
=head1 INTERNALS
For both of the database structures being compared, the following
happens:
=over 4
=item
If the argument is a valid filename, the file is used to create a
temporary database which C<mysqldump -d> is run on to obtain the table
definitions in canonicalised form. The temporary database is then
dropped. (The temporary database is named
C<test_mysqldiff_temp_something> because default MySQL permissions
allow anyone to create databases beginning with the prefix C<test_>.)
=item
If the argument is a database, C<mysqldump -d> is run directly on it.
=item
Where authentication is required, the hostname, username, and password
given by the corresponding options are used (type C<mysqldiff --help>
for more information).
=item
Each set of table definitions is now parsed into tables, and fields
and index keys within those tables; these are compared, and the
differences outputted in the form of MySQL statements.
=back
=cut
use strict;
use 5.006; # due to 'our' and qr//
use FindBin qw($RealBin $Script);
use lib $RealBin;
use Getopt::Long qw(:config no_ignore_case);
use IO::File;
use String::ShellQuote qw(shell_quote);
use MySQL::Diff;
my %opts = ();
GetOptions(\%opts, "help|?", "debug|d:i", "apply|A", "batch-apply|B",
"keep-old-tables|k", "no-old-defs|n", "only-both|o", "table-re|t=s",
"host|h=s", "port|P=s", "socket|s=s", "user|u=s", "password|p:s",
"host1=s", "port1=s", "socket1=s", "user1=s", "password1:s",
"host2=s", "port2=s", "socket2=s", "user2=s", "password2:s",
"tolerant|i", "single-transaction|S", "list-tables|l"
) or usage();
usage() if (@ARGV != 2 or $opts{help});
$opts{debug} ||= 0;
my $md = MySQL::Diff->new(%opts);
for my $num (1, 2) {
my $new_db = $md->register_db($ARGV[$num-1], $num);
usage($new_db) unless ref $new_db;
}
$| = 1;
my $diffs = $md->diff();
print $diffs;
apply($md, $diffs) if $opts{apply} || $opts{'batch-apply'};
exit 0;
##############################################################################
sub usage {
print STDERR @_, "\n" if @_;
die <<EOF;
Usage: $Script [ options ] <database1> <database2>
Options:
-?, --help show this help
-A, --apply interactively patch database1 to match database2
-B, --batch-apply non-interactively patch database1 to match database2
-d, --debug[=N] enable debugging [level N, default 1]
-l, --list-tables output the list off all used tables
-o, --only-both only output changes for tables in both databases
-k, --keep-old-tables don't output DROP TABLE commands
-n, --no-old-defs suppress comments describing old definitions
-t, --table-re=REGEXP restrict comparisons to tables matching REGEXP
-i, --tolerant ignore DEFAULT, AUTO_INCREMENT, COLLATE, and formatting changes
-S, --single-transaction perform DB dump in transaction
-h, --host=... connect to host
-P, --port=... use this port for connection
-u, --user=... user for login if not current user
-p, --password[=...] password to use when connecting to server
-s, --socket=... socket to use when connecting to server
for <databaseN> only, where N == 1 or 2,
--hostN=... connect to host
--portN=... use this port for connection
--userN=... user for login if not current user
--passwordN[=...] password to use when connecting to server
--socketN=... socket to use when connecting to server
Databases can be either files or database names.
If there is an ambiguity, the file will be preferred;
to prevent this prefix the database argument with `db:'.
EOF
}
sub apply {
my ($md, $diffs) = @_;
if (! $diffs) {
print "No differences to apply.\n";
return;
}
my $db0 = $md->db1->name;
if ($md->db1->source_type ne 'db') {
die "$db0 is not a database; cannot apply changes.\n";
}
unless ($opts{'batch-apply'}) {
print "\nApply above changes to $db0 [y/N] ? ";
print "\n(CAUTION! Changes contain DROP TABLE commands.) "
if $diffs =~ /\bDROP TABLE\b/i;
my $reply = <STDIN>;
return unless $reply =~ /^y(es)?$/i;
}
print "Applying changes ... ";
my $args_ref = $md->db1->auth_args;
unshift @$args_ref, q{mysql};
push @$args_ref, $db0;
my $pipe = shell_quote @$args_ref;
my $fh = IO::File->new("|$pipe") or die "Couldn't open pipe to '$pipe': $!\n";
print $fh $diffs;
$fh->close or die "Couldn't close pipe: $!\n";
print "done.\n";
}
=head1 BUGS, DEVELOPMENT, CONTRIBUTING
See L<http://adamspiers.org/computing/mysqldiff/>.
=head1 COPYRIGHT AND LICENSE
Copyright (c) 2000-2016 Adam Spiers. All rights reserved. This
program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.
=head1 SEE ALSO
L<MySQL::Diff>, L<MySQL::Diff::Database>, L<MySQL::Diff::Table>, L<MySQL::Diff::Utils>,
L<mysql>, L<mysqldump>, L<mysqlshow>
=head1 AUTHOR
Adam Spiers <mysqldiff@adamspiers.org>
=cut