9

I would like to make a hash / signature of MySQL database schema (without data), in order to have a checksum of it, to ensure it is not modified by others.

How can I achieve it ?

Raptor
  • 53,206
  • 45
  • 230
  • 366

2 Answers2

10

You need table checksum as far as I understand your question:

checksum table `table`

so, just make a checksum of an empty table, I guess

Nemoden
  • 8,816
  • 6
  • 41
  • 65
  • how about the whole schema ( all tables )? – Raptor Oct 11 '12 at 03:56
  • I doubt you can get a checksum of a whole database using one command. So, I'd go with collecting all checksums of all tables in a database, then concatenating it together and evaluating `md5` of this string. – Nemoden Oct 11 '12 at 04:04
  • okay, understand it can't be done in a simple way. However, the CHECKSUM TABLE query will also include data in the table to calculate the checksum. Is there any method to exclude the data? – Raptor Oct 12 '12 at 03:01
  • 1
    Yes, it includes data. You need a copy of an empty table. What I'd do is `mysqldump -d database` - this will dump a whole database, then create test database, create all tables (they will be empty because of `-d` flag - `--no-data`) and do checksums. And yes, I don't think there is a simple way to do what you want. – Nemoden Oct 12 '12 at 05:52
  • 1
    Sorry I didn't understand the question. You wanted to check whether nobody altered tables. In this case I'd probably checked all tables with `show create table 'table'` and I'd have initial structures for all tables to compare. This is a bit simpler for me then making a dump and then checking all tables with `checksum` – Nemoden Oct 12 '12 at 05:55
  • I think my final approach is to dump the schema using `mysqldump` and do a file-based checksum, which allows me to make a single checksum for all table schema in one shot – Raptor Oct 12 '12 at 06:08
  • 4
    Hmmm... i didn't think of it :) This approach is okay, but `mysqldump` includes information about it's version and version onf mysql server. If you upgrade/downgrade mysql server or mysql client (this will cause upgrade/downgrade of mysqldump), your resulting file's signature will, obviously, differ although schema is not changed. To avoid this, use `--compact` option of `mysqldump` like so: `mysqldump --compact -u root --password=root --host=127.0.0.1 test | md5sum`. – Nemoden Oct 12 '12 at 06:37
  • BTW, it was an interesting task for me too. `mysqldump` of a single database without data should be fast enough. I don't see any drawbacks. Nice question. – Nemoden Oct 12 '12 at 06:41
  • Nice idea. I tried it and got bitten because `mysqldump` outputs the auto increment values (which change when new rows are added obviously); but this [other answer](http://stackoverflow.com/a/15656501/1387519) gives a solution for that part. Thanks for the initial lead. – rixo Jan 21 '14 at 14:19
  • 1
    Sorry, but how this answer can be correct if `checksum table 'table'` for an **empty** table is always **0**, no matter what the table structure is? (MySQL 5.5) – iloo Jan 19 '17 at 07:33
3
SELECT HEX( CRC32 ( SUM( CRC32( CONCAT(
   COALESCE(TABLE_NAME,''),
   COALESCE(COLUMN_NAME,''),
   COALESCE(ORDINAL_POSITION,''),
   COALESCE(COLUMN_DEFAULT,''),
   COALESCE(IS_NULLABLE,''),
   COALESCE(DATA_TYPE,''),
   COALESCE(CHARACTER_MAXIMUM_LENGTH,''),
   COALESCE(CHARACTER_OCTET_LENGTH,''),
   COALESCE(NUMERIC_PRECISION,''),
   COALESCE(NUMERIC_SCALE,''),
   COALESCE(CHARACTER_SET_NAME,''),
   COALESCE(COLLATION_NAME,''),
   COALESCE(COLLATION_NAME,''),
   COALESCE(COLUMN_TYPE,''),
   COALESCE(COLUMN_KEY,'')
  ) ) ) ) ) AS 'hash'
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'database_name';
Евгений Кочетков
  • 31
  • 2