MySQL query to show difference between development and production schema

I would like to have a query using the schema database in MySQL

that shows the difference between the columns, triggers and stored procedures between two database schema's: production and development.

Query, not tools

I've seen 开发者_开发问答Compare two MySQL databases

Which lists the tools that can perform this task, but I would like to know is if there is a query that can perform this task.

Please only suggest queries, I really do not want to know about tools, command line hacks or such.

I am looking to see if the production database and development database are out of sync.

And which fields, procedures etc where added or changed, so I can update the production database if I roll out a new update of the client software that uses the database.

I'm using MySQL 5.1 latest version.

Johan, try to run this script. Specify two databases you want to compare in variables at the begining of the script. Query returns data-set, and sets statuses for table/view columns.

Status 'Only in source' - object exists only in db1; Status 'Only in target' - object exists only in db2; Status 'In both schemas' - object exists in db1 and in db2, but details can be different; for example: value 'varchar(255)/int(11)' says that source field type is 'varchar(255)' and target is 'int(11)', value 'null' says that details are equal;

SET @source_db = 'db1';
SET @target_db = 'db2';

SELECT 
  'Only in source' exist_type,
  c1.table_schema, c1.table_name, c1.column_name, c1.ordinal_position, c1.column_default, c1.is_nullable, c1.numeric_precision, c1.numeric_scale, c1.character_set_name, c1.collation_name, c1.column_type, c1.column_key, c1.extra, c1.column_comment
FROM
  (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @source_db) c1
  LEFT JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @target_db) c2
    ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name
WHERE c2.column_name is null

UNION ALL

SELECT
  'Only in target' exist_type,
  c2.table_schema, c2.table_name, c2.column_name, c2.ordinal_position, c2.column_default, c2.is_nullable, c2.numeric_precision, c2.numeric_scale, c2.character_set_name, c2.collation_name, c2.column_type, c2.column_key, c2.extra, c2.column_comment
FROM
  (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @source_db) c1
  RIGHT JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @target_db) c2
    ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name
WHERE c1.column_name is null

UNION ALL

SELECT 
  'In both schemas' exist_type,
  CONCAT(c1.table_schema, '/', c2.table_schema),
  c1.table_name, c1.column_name,
  IF(c1.ordinal_position = c2.ordinal_position OR c1.ordinal_position IS NULL AND c2.ordinal_position IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.ordinal_position, ''), IFNULL(c2.ordinal_position, ''))),
  IF(c1.column_default = c2.column_default OR c1.column_default IS NULL AND c2.column_default IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_default, ''), IFNULL(c2.column_default, ''))),
  IF(c1.is_nullable = c2.is_nullable OR c1.is_nullable IS NULL AND c2.is_nullable IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.is_nullable, ''), IFNULL(c2.is_nullable, ''))),
  IF(c1.numeric_precision = c2.numeric_precision OR c1.numeric_precision IS NULL AND c2.numeric_precision IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.numeric_precision, ''), IFNULL(c2.numeric_precision, ''))),
  IF(c1.numeric_scale = c2.numeric_scale OR c1.numeric_scale IS NULL AND c2.numeric_scale IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.numeric_scale, ''), IFNULL(c2.numeric_scale, ''))),
  IF(c1.character_set_name = c2.character_set_name OR c1.character_set_name IS NULL AND c2.character_set_name IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.character_set_name, ''), IFNULL(c2.character_set_name, ''))),
  IF(c1.collation_name = c2.collation_name OR c1.collation_name IS NULL AND c2.collation_name IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.collation_name, ''), IFNULL(c2.collation_name, ''))),
  IF(c1.column_type = c2.column_type OR c1.column_type IS NULL AND c2.column_type IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_type, ''), IFNULL(c2.column_type, ''))),
  IF(c1.column_key = c2.column_key OR c1.column_key IS NULL AND c2.column_key IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_key, ''), IFNULL(c2.column_key, ''))),
  IF(c1.extra = c2.extra OR c1.extra IS NULL AND c2.extra IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.extra, ''), IFNULL(c2.extra, ''))),
  IF(c1.column_comment = c2.column_comment OR c1.column_comment IS NULL AND c2.column_comment IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_comment, ''), IFNULL(c2.column_comment, '')))
FROM
  (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @source_db) c1
  JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @target_db) c2
    ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name;

This script can be modified to find differences between triggers and routines.

All of the data that you're after should be in the tables in the information_schema database.

You might be able to do the comparison with some kind of a join that only shows you the differences but trying to do it in queries or a single query seems like an overly complicated way of approaching the problem, I think you're shooting yourself in the foot.

The quick and easy solution would be to to diff either the contents of a mysqldump --no-data of each database or to pull out the data from the information schema and diff that.

t1 compare to t2

select 
 (case t1.table_name=t2.table_name when 1 then concat(t1.table_name,"==",t2.table_name) else concat(t1.table_name,"!=",t2.table_name) end) as table_name,
 (case t1.column_name=t2.column_name when 1 then concat(t1.column_name,"==", t2.column_name) else concat(t1.column_name,"!=", t2.column_name) end) as column_name,
 (case t1.ORDINAL_POSITION=t2.ORDINAL_POSITION when 1 then concat(t1.ORDINAL_POSITION,"==", t2.ORDINAL_POSITION) else concat(t1.ORDINAL_POSITION,"!=", t2.ORDINAL_POSITION) end) as ORDINAL_POSITION
......--columns in information_schema
from columns t1 left join (select * from columns where table_schema='t2') t2 on t2.table_name=t1.table_name and t2.column_name=t1.column_name where t1.table_schema='t1'; 

hope this help!!

Johan you have already narrowed you answer domain by saying you want a "Query" to compare databases :)

However my suggestion for you is to think about "Binary Logging" . I have used it successfully for a similar purposes.

       a) enable logs 
       b) Go through all binary logs files 
       c) grep desired statements
       d) at then end purge/reset binary logs  ie. RESET MASTER 

Obviously you will do this on production db.

Other ways might be : How to synchronize development and production database

This is an oldy, but it works. Building on devart's example I went ahead and built the comparison for procedures and functions:

SET @source_db = 'qls_projects_for_comparison';
SET @target_db = 'qls_projects';

-- Pick one and comment out the other
-- SET @routine_type = 'FUNCTION';
SET @routine_type = 'PROCEDURE';

-- Get the ones only in the source
SELECT
  'Only in SOURCE' exist_type, C1.ROUTINE_NAME, C1.ROUTINE_SCHEMA,         
C1.ROUTINE_TYPE, C1.LAST_ALTERED, C1.DEFINER as 'Source Definer', C2.DEFINER 
as     'Target Definer', def_compare as 'Compare Definitions'  
FROM (    
(SELECT *,'' as def_compare FROM INFORMATION_SCHEMA.ROUTINES WHERE 
ROUTINE_TYPE = @routine_type AND ROUTINE_SCHEMA = @source_db) C1
LEFT JOIN (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @target_db) C2
ON C1.ROUTINE_NAME = C2.ROUTINE_NAME
)
WHERE C2.ROUTINE_NAME IS NULL

UNION ALL

-- Get the ones only in the target
SELECT
  'Only in TARGET' exist_type, C2.ROUTINE_NAME, C2.ROUTINE_SCHEMA,             
C2.ROUTINE_TYPE, C2.LAST_ALTERED, C1.DEFINER as 'Source Definer', C2.DEFINER 
as 'Target Definer', def_compare as 'Compare Definitions'  
FROM (    
(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @source_db) C1
RIGHT JOIN (SELECT *,'' as def_compare FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = @routine_type AND ROUTINE_SCHEMA = @target_db) C2
ON C1.ROUTINE_NAME = C2.ROUTINE_NAME
)
WHERE C1.ROUTINE_NAME IS NULL

UNION ALL

-- Get the ones in both and compare the bodies of the routines 

SELECT 
'In both schemas' exist_type
, C2.ROUTINE_NAME
, C2.ROUTINE_SCHEMA
, C2.ROUTINE_TYPE
, C2.LAST_ALTERED
, C1.DEFINER as 'Source Definer'
, C2.DEFINER as 'Target Definer', 
IF(C1.ROUTINE_DEFINITION=C2.ROUTINE_DEFINITION, 'Matches','Does Not Match') 
as 'Compare Definitions'
FROM (    
   (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @source_db) C1
  INNER JOIN (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @target_db) C2
    ON C1.ROUTINE_NAME = C2.ROUTINE_NAME
)

I have developed a tool that can be used to compare two Databases. This Tool only works with MySQL. This tool generates SQL for Target Database to sync the database. This is a web application tool built in CakePHP 2, you have to download code put into xammp->htdocs in case of windows, and make a virtual domain before using it. For more information goto below Link

https://github.com/hardeepvicky/MySql-Schema-Compare