MYSQLDIFF(1p)

💖 MYSQLDIFF (1p) — the manual, but make it a group chat 💖

okay bestie, this is a page from User Contributed Perl Documentation (iykyk, that's the manual section it lives in), and it's literally the whole tea on mysql-schema-diff. sit down, grab a boba, we're doing this. 🧋✨

---

🌸 NAME 🌸

mysql-schema-diff — compare MySQL database schemas. that's it, that's the vibe. two schemas walk in, one diff walks out. 💅

---

✨ SYNOPSIS ✨

mysql-schema-diff [options] database1 database2

mysql-schema-diff --help

like. that's the whole energy right there. two databases, and the tool tells you what's different. 🦋

---

🎀 DESCRIPTION 🎀

okay so mysql-schema-diff is a Perl script, and it's basically a front-end (a cute little face, if you will) for the CPAN module MySQL::Diff — you can find CPAN at http://www.perl.com/CPAN, and the module itself at http://search.cpan.org/search?module=MySQL::Diff. no cap. 👑

what does it DO though. it compares the data structures (that's the schema / table definitions, not the actual rows, bestie, don't get it twisted) of two MySQL databases — MySQL lives at http://www.mysql.com/ — and it hands you back the differences as a sequence of MySQL commands. and those commands are designed to be piped straight into mysql, so they'll transform the structure of the first database until it's identical to the second one. it's giving diff and patch, but for your tables. periodt. 💖

and the way it's so flexible?? you can compare database structures whether they're files containing table definitions or existing databases, and whether those are local or remote. slay.

N.B. bestie, listen. this program makes no attempt to compare any of the data that might be stored in the databases. none. zero. it is purely for comparing the table definitions. the author has no plans to implement data comparison, because it's a complex problem and honestly they have no need of such functionality anyway. 🙈

BUT. if that's your thing, there's another program called coldiff (http://rossbeyer.net/software/mysql_coldiff/) which does exactly that — and it's based on an older program called datadiff which seems to have vanished off the 'net. gone. deleted. rip. 💀

and for PostgreSQL girlies, there are similar tools like pgdiff (http://pgdiff.sourceforge.net/) and apgdiff (http://apgdiff.startnet.biz/). you're so welcome. 🌈

---

🌟 EXAMPLES 🌟

these are literally the moments. copy them, love them:

# 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

see that last one?? db:foo?? that's how you tell it "no bestie, I mean the database foo, not the file foo that's sitting right there in my directory." iconic behavior honestly. 🔥

---

💅 OPTIONS 💅

every single one, no skips, we don't gatekeep here:

• -?, --help — show usage 🫶
• -A, --apply — interactively patch database1 to match database2
• -B, --batch-apply — non-interactively patch database1 to match database2 (no questions asked, it just goes)
• -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 (so chill of it)
• -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 🦋

yes bestie, you get per-database versions of the connection stuff. one for each side. balance. ✨

• --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 🔍

so like, what's actually happening under the hood?? for both of the database structures being compared, here's the whole sequence:

• 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_.) — sneaky but smart, we love a loophole. 💀
• 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. and THAT'S the moment. 🌟

---

🐛 BUGS, DEVELOPMENT, CONTRIBUTING 🐛

See http://adamspiers.org/computing/mysqldiff/. that's the whole section, bestie, go look. 👀

---

📜 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. 🫶 (so yes, you can share it, just keep it under Perl's terms, we respect the rules here.)

---

👑 SEE ALSO 👑

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

go read those next, they're the supporting cast and they ate. 💖

---

🌸 AUTHOR 🌸

Adam Spiers <mysqldiff@adamspiers.org> — the main character who made all of this happen. we stan. 💅✨

---

and that's the whole page bestie!! this was written for perl v5.26.2, dated 2018-07-20, and it's the MYSQLDIFF(1p) page — so go diff those schemas, you've got this. 🎀🦋💜