Linux script to get schema difference using mysqldbcompare in MySQL

Complete script to get missing structural changes between two schemas in MySQL


The goal of this blog is to provide .sql files that we can import directly to remove the missing structural changes of a schema.

We will get queries for creating missing tables and columns.

We are going to use mysqldbcompare utility for taking the basic difference file(.sql).

There are many bugs if you are planning to use mysqldbcompare alone for taking structural difference

  • Wrong queries for alters containing add index,change index,add foreign_key constraints.
  • For missing tables we will just get the names.

Setup description:

We are having both the perfect schema and the dirty schema on the same server.

We will use the perfect schema as a template schema to compare the dirty schema with to get the missing changes.

We are not comparing data consistencies so we are taking¬† the structure dump of both schema and importing it into our testing server as we don’t want the mysqldbcompare slow down our business servers.

Getting into the script: Read More

Upgrade MySQL 5.6 to 5.7

Complete steps to upgrade MySQL 5.6 to MySQL 5.7

Description of current setup:

We are currently using MySQL 5.6.34  on Linux Centos 7.

The upgrade version we have chosen is MySQL 5.7.21

Step 1:

Take dump of mysql schema and other important ones. (in case u need to downgrade to current version)

Step 2:

Stop MySQL service:

service mysql stop;

Step 3:

Remove your current MySQL rpms:

First select all installed MySQL rpms

yum list installed|grep -i "mysql"

Then you can remove all rpms having string 5.6 in their names Read More