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
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
With this example, I would like to warn you about record count of a table!
Have a nice day!