How to compare schema of two databases in MySQL

-
If you are responsible for maintaining database objects structure and want to know what objects are added or dropped from previous application version to the next version, then you have come to the right place.

You may need to know the schema difference for any number of purposes like for Application upgrade, Product documentation etc.

In this tutorial we will mention how to compare schema of two databases in MySQL. You will need below mentioned tools to do this.

  • MySQL built-in utility: mysqldump
  • Text Editor supporting Regular Expression: Notepad++
  • File comparison tool: Chose as per your preference, I used Beyond Compare
    •

1. Take the backup of schema for the databases

For the purpose of this tutorial we will assume that we need to compare DB schema for two databases appVer1 and appVer2. Take the backup as follows: (Provide password when prompted and press Enter key.)

For appVer1: mysqldump -h your_db_host -u your_db_user -p --single-transaction --skip-comments --no-data --routines --triggers --events appVer1 > appVer1_schema.sql

For appVer2: mysqldump -h your_db_host -u your_db_user -p --single-transaction --skip-comments --no-data --routines --triggers --events appVer2 > appVer2_schema.sql

NOTE:
  1. Replace your_db_host with actual HOST and your_db_user with actual USER of your MySQL server.
  2. Make sure that the USER has proper privilege on the database whose schema you are fetching in this activity.

2. Use Notepad++ to format the schema dump

The mysqldump output will also contain auto increment clause, if the tables are created with the same. It will show difference in DB schema when you will use a compare tool, where in actual it may not matter for you.

How to resolve this?
 
Use Notepad++
 
Open your mysqldump file in Notepad++. Then Search and Replace using Regular Expression as follows:
 
Regular Expression: AUTO_INCREMENT=[0-9]+<space>
 
Replace <space> with actual space character.

 

Find what: AUTO_INCREMENT=[0-9]+<space> (Add a actual space character after + character)
Replace with: Make sure there is no character inside this box as you need to replace the matching pattern with blank.
 
Choose Replace ALL.

Apply similar process for other clauses as well like DEFINER etc., if present and creating unnecessary difference.

3. Compare the two schema files

Use a file comparison tool to compare the two schema files after they have been formatted.

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can be on disk or
Read more