Comparing two databases on MySQL-MariaDB

Posted by in MariaDB, MySQL

Last week I needed to compare two databases on a MySQL server that I think they seem to be identical. Because I was on a hurry, I create a simple query to verify it, with this sintax using information_schema:
SELECT table_name, engine, row_format
, SUM( CASE WHEN table_schema = "base_datos_1" THEN table_rows ELSE NULL END) AS "base_datos_1"
, SUM( CASE WHEN table_schema = "
base_datos_2" THEN table_rows ELSE NULL END) AS "base_datos_2"
FROM information_schema.tables
WHERE table_schema = "base_datos_1" OR table_schema = "base_datos_2"
GROUP BY table_name
ORDER BY table_name DESC;

The results I got:

Comparando 2 bases de datos

Comparando 2 bases de datos

Practicaly, they are equals. I say “practicaly”, because I got a table with an important difference between one database and the other (the table was datosmensual). Because of this, I don’t recommend to rely on information_schema statistics, at least, with related to record count! So, next step is to create a new query to test if the record number of both were the same. I use the classic query select count::

SELECT COUNT(*) FROM tabla;

The result was:

Comparando bases de datos

Comparando bases de datos

With this example, I would like to warn you about record count of a table!

Have a nice day!