---
name: "mysql-schema-diff"
section: "1p"
platform: "linux"
package: "libmysql-diff-perl"
version: "0.60-1"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/mysql-schema-diff"
---

# mysql-schema-diff(1p)

compare MySQL database schemas

## NAME

mysql-schema-diff - compare MySQL database schemas

## SYNOPSIS

```
    mysql-schema-diff [B<options>] B<database1> B<database2>
    mysql-schema-diff --help
```

## DESCRIPTION

*mysql-schema-diff* is a Perl script front-end to the CPAN \<http://www.perl.com/CPAN\> module MySQL::Diff \<http://search.cpan.org/search?module=MySQL::Diff\> which compares the data structures (i.e. schema / table definitions) of two MySQL \<http://www.mysql.com/\> databases, and returns the differences as a sequence of MySQL commands suitable for piping into *mysql* which will transform the structure of the first database to be identical to that of the second (*c.f.* *diff* and *patch*).

Database structures can be compared whether they are files containing table definitions or existing databases, local or remote.

**N.B.** The program makes *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 coldiff \<http://rossbeyer.net/software/mysql\_coldiff/\> which does this, and is based on an older program called *datadiff* which seems to have vanished off the 'net.

For PostgreSQL there are similar tools such as pgdiff \<http://pgdiff.sourceforge.net/\> and apgdiff \<http://apgdiff.startnet.biz/\>.

## EXAMPLES

```
  # compare table definitions in two files
  mysql-schema-diff db1.mysql db2.mysql
  # compare table definitions in a file 'db1.mysql' with a database 'db2'
  mysql-schema-diff db1.mysql db2
  # interactively upgrade schema of database 'db1' to be like the
  # schema described in the file 'db2.mysql'
  mysql-schema-diff -A db1 db2.mysql
  # compare table definitions in two databases on a remote machine
  mysql-schema-diff --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
  mysql-schema-diff --host2=remote.host.com --password=secret db:foo bar
```

## OPTIONS

- "-?, --help"

  show usage

- "-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

- "-c, --keep-old-columns"

  don't output DROP COLUMN 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 For more info see: http://dev.mysql.com/doc/refman/en/mysqldump.html#option\_mysqldump\_single-transaction

- "-h, --host=\[hostname\]"

  connect to host

- "-P, --port=\[port\]"

  use this port for connection

- "-u, --user=\[user\]"

  user for login if not current user

- "-p, --password\[=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=\[hostN\]"

  connect to host

- "--portN=\[portN\]"

  use this port for connection

- "--userN=\[userN\]"

  user for login if not current user

- "--passwordN\[=passwordN\]"

  password to use when connecting to server

- "--socketN=\[socketN\]"

  socket to use when connecting to server

## INTERNALS

For both of the database structures being compared, the following happens:

- •

  If the argument is a valid filename, the file is used to create a temporary database which `"mysqldump -d"` is run on to obtain the table definitions in canonicalised form.  The temporary database is then dropped.  (The temporary database is named `"test_mysqldiff_temp_something"` because default MySQL permissions allow anyone to create databases beginning with the prefix `"test_"`.)

- •

  If the argument is a database, `"mysqldump -d"` is run directly on it.

- •

  Where authentication is required, the hostname, username, and password given by the corresponding options are used (type `"mysql-schema-diff --help"` for more information).

- •

  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.

## BUGS, DEVELOPMENT, CONTRIBUTING

See \<http://adamspiers.org/computing/mysqldiff/\>.

## 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.

## SEE ALSO

MySQL::Diff, MySQL::Diff::Database, MySQL::Diff::Table, MySQL::Diff::Utils, mysql, mysqldump, mysqlshow

## AUTHOR

Adam Spiers \<mysqldiff@adamspiers.org\>