check data before-after mariadb upgrade

Question

I plan to upgrade from
10.1.2
to
10.11.6
and the upgrade with a test db shows success.

But i wanted to check for any differences and tried:

1. diff of mysqldump
2. diff of mysqldump --no-data (structure only)
3. table and index sizes
4. CHECKSUM TABLE

before and after the upgrade.

1. The mysqldump makes no sense. Too large and the dump format changes on higher versions (line breaks ect).

2. The structure only dump makes sense.
   I could see some changes that are nice to know:
   Examples:

-) ENGINE=InnoDB AUTO_INCREMENT=852701 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=852701 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-  `breakpoint` tinyint(1) NOT NULL DEFAULT '0',
+  `breakpoint` tinyint(1) NOT NULL DEFAULT 0,

3. The table and index size comparison seems to make sense and stays the same.
   The query i use:

SELECT table_schema as                                        `Database`,
       table_name   AS                                        `Table`,
       round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`,
       round((data_length / 1024 / 1024), 2)                  `Data in MB`,
       round((index_length / 1024 / 1024), 2)                 `Index in MB`
FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA = DATABASE()
ORDER BY (data_length + index_length) DESC, `Table` ASC;

4. The CHECKSUM TABLE works, but the checksum is different on some tables. I guess this because of structural changes - all those tables with structured changes have a different checksum.

That is why im here. How do i check the data after an upgrade.
And|or do|can i actually?

Answer 1

You could try the mariadb-dump of the newer MariaDB against the old data. Its untested but will be consistent with new lines and potentially less differences.

mariadb-check --extended is one way to ensure all tables are in a fully good state.

Another way is using pt-table-sync

pt-table-sync --execute --verbose --databases my_database_name h=my_host_1,u=my_user_1,p=my_pass_1 h=my_host_2,u=my_user_2,p=my_pass_2

Will ensure data consistency and display differences.

Note: a --dry-run as an argument won't actually perform comparison.