Linux script to get schema difference using mysqldbcompare in MySQL

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

Description:

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:

mysqldbcompare command:

We can use multiple options with mysqldbcompare:

–server1 : is the server on which both schema are there and we need to give username:[email protected]:port

(as we have both the schema on the same server that’s why i have mentioned server1)

(make sure the user you are using have the required privileges to perform the activity)

–difftype : as we want the difference in sql format

–run-all-tests : to check for everything as data is not there we are not skipping other  checks

You can check other options here.

$1:$2 :

  • $1 is the command line argument(schema name) that we are passing to the script and should be the same schema for which we want the difference(dirty_schema).
  • $2 is the command line argument(schema name) that we are passing to the script and should be the template schema that we need for the comparison(perfect_schema).
mysqldbcompare --server1=root:${passwd}@localhost:3306 --difftype=sql --run-all-tests $1:$2 > $1_diff.sql

Selecting the alters from the output file of above command:

We are removing comments and auto_increments from the queries.

cat $1_diff.sql|sed "s/COMMENT[=]'.*'//"|sed -n '/ALTER TABLE/,/;/p'|sed '/,$/{$!{N;s/,\nAUTO_INCREMENT[=][0-9]*//;ty;P;D;:y}}'|sed 's/AUTO_INCREMENT[=][0-9]*//' > $1_alt_diff.sql

Removing erroneous parts of the alter queries:

We will remove all the parts related to index,drop foreign key ,add constraint,collation,warnings.

egrep -rv 'DROP FOREIGN KEY|ADD CONSTRAINT|INDEX' $1_alt_diff.sql | tr -d ';' | sed -e 's/ALTER TABLE/;ALTER TABLE/g' | tr '\n' ' ' | sed -e 's/\;/\n\;/g' | sed 's/^;//g' | sed 's/.$/;/g'|sed 's/COLLATE[=][a-zA-Z0-9_,]*//'|sed "s/# WARNING:[#.a-zA-Z0-9_' ]*//"|sed "s/[, ]*;/;/"|sed "s/[ ]*;/ ;/"|tr -s ' ' > $1_alters.sql

Removing all empty alters:

After removing all the erroneous parts of the the queries we may get some empty queries like:

alter table table_name;

To remove this kind of queries we will use:

while read line
do
a=$(echo "${line}"|awk '{print $4}')
if [[ $a != ";" ]]
then
echo ${line} >> $1_diff_alters.sql
fi
done < $1_alters.sql

Adding command to skip errors relating constraint violations:

To skip all the errors related to constraint violations as we are going to alter tables we need to add

set foreign_key_checks=0;

in the beginning of alter file.

sed -i '1s/^/set foreign_key_checks=0;\n/' $1_diff_alters.sql

Done

$1_diff_alters.sql is the final alter file.

Selecting missing table names from the diff file we got after using the mysqldbcompare command:

cat $1_diff.sql|sed -n "/Objects in server1.$2/,/----------/p"|grep "TABLE"|cut -d':' -f 2|awk '{$1=$1;print}' > test.txt

Generating create table queries for the missing tables:

From the create queries we need to remove DEFAULT CHARSET and AUTO_INCREMEMT.

while read line
do
mysql -uroot -p${passwd} -e "show create table $2.${line}\G"|sed -n '/CREATE/,$p'|sed 's/Create Table: //'|sed 's/AUTO_INCREMENT[=][0-9]*//'|sed 's/DEFAULT CHARSET[=][a-zA-Z0-9_]*//' >> $1_create.sql
echo ";" >> $1_create.sql
done < test.txt

Skipping foreign_key checks:

Same as in alter file we are going to use

sed -i '1s/^/set foreign_key_checks=0;\n/' $1_create.sql

to skip the constraint errors

$1_create.sql is the final create query file for the missing tables.

All Done ! We got both the alter and create files.

Leave a Reply

Your email address will not be published. Required fields are marked *