This commandline script will generate a structure diff of two input files and echo it to STDOUT.
It uses the "Database structure synchronizer" class from phpclasses.org with a simple commandline interface.
Connections can be made with SSH to remote servers, where mysqldump is executed to return the current schema structure. mysqldump can also be executed on the local machine the script runs on.
SSH connections require the PECL ssh2 library and libssh2, see here
Installation:
Download the mysql-compare.phar archive from the build directory. This can be placed in your /usr/bin.
Usage:
Usage: $ $argv[0] --from-[OPTIONS] --to-[OPTIONS]
Both `from` and `to` sources must be specified, and can be a
combination of either `file`, `db` or `ssh` sources.
`file` OPTIONS:
The `file` source reads a file from the local disk
--(from|to)-file The filename of the SQL dump to
use. Must be readable by the user
executing the script.
`db` OPTIONS:
The `db` source uses the mysqldump command executed on the current machine
--(from|to)-db-name (required) The name of the database
to use in the dump
--(from|to)-db-user The username to connect with
--(from|to)-db-password The password to connect with
--(from|to)-db-host The database host
`ssh` OPTIONS:
The `ssh` source extends the `db` source to use mysqldump over
an SSH connection to a remote server. Authentication must use
public keys. OPTIONS include the `db` commands above, plus:
--(from|to)-ssh-server (required) The server to connect to
A different port number can be specified
using colon notation.
--(from|to)-ssh-user (required) The username to connect as
--(from|to)-ssh-pubkey (required) The path to the public key
--(from|to)-ssh-privkey (required) The path to the private key
Examples:
# The following would diff two files on the local disk:
$ ./dbcompare.php --from-file=test/dev.sql --to-file=test/live.sql
# This would connect to a remote server and diff against a local file
$ ./dbcompare.php --from-file=test/dev.sql \
--to-ssh-server=server.example.com --to-ssh-user=root \
--to-ssh-pubkey=path/to/id_rsa.pub --to-ssh-privkey=path/to/id_rsa \
--to-db-name=db_live --to-db-user=root --to-db-password=password123
# Similarly, this example would use a local mysqldump against a remote server
$ ./dbcompare.php --from-db-name=db_dev --from-db-user=root --from-db-password=test123 \
--to-ssh-server=server.example.com --to-ssh-user=root \
--to-ssh-pubkey=path/to/id_rsa.pub --to-ssh-privkey=path/to/id_rsa \
--to-db-name=db_live --to-db-user=root --to-db-password=password123An example result (from two different files):
#
# Structure difference between 'dev.sql' and 'live.sql'
#
# Generated: Tue, 14 Aug 2012 14:54:35 +1200
#
# START DIFF;
CREATE TABLE IF NOT EXISTS `bar` (
`bar_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`foo_id` int(11) unsigned NOT NULL,
`default` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`bar_id`),
KEY `foo_id` (`foo_id`),
CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE `foo` MODIFY `name` varchar(256) NOT NULL
ALTER TABLE `foo` MODIFY `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
# END DIFF;