MySQL, get SQL statements of difference between two tables to update the latest

General Discussion
1

Hello everyone,

I would like to get the difference between two tables from databases located on different servers and get SQL statements to update the oldest database. That includes the data, and the structure. It is MySQL database.
At the end, I will do a difference between two schemas, procedures,…, not just the tables.

I have struggled to arrive at this point : I can generate a changelog from the two database, but only about the structure, unfortunately without the data change.

  1. java -jar liquibase.jar --driver=com.mysql.jdbc.Driver </li>
  2.       --classpath=mysql-connector-java-5.1.17-bin.jar </li>
  3.       --changeLogFile=db.ExAmPlechangelog.xml </li>
  4.       --url=“jdbc:mysql://801.010.150:87” </li>
  5.       --defaultSchemaName=test1 </li>
  6.       --username=root </li>
  7.       --password=password </li>
  8.       diffChangeLog </li>
  9.       --referenceUrl=“jdbc:mysql://localhost:87” </li>
  10.       --referenceUsername=root </li>
  11.       --referencePassword=password </li>
  12.       --referenceDefaultSchemaName=test2

So, what is the parameter to also have the data change ?

And what is the command to translate the changelog into SQL queries ?

Thanks in advance,
Regards,
Cedric

2

Hi!

Just use the command updateSQL (with the same properties). It will use the (generated) changelog to create SQL statements

Greetings,

Heiner

3

If the question is not accurate enough, if there is something missing, please let me know.

If there is a place you think I have overlooked and should dig in, please refer to it (even if I have had a look in several place of the documentation of liquibase without getting an answer to all these questions).

Cedric

4

Hi
As Heiner has said, and as documented, it is quite straightforward. Here is a more verbose version, in case you don’t understand.
You need to execute the above command (diffChangeLog) once, it will generate the change log, aka the difference between the two databases.
Then, execute the same script, but replace diffChangeLog with updateSQL.
You will see the SQL statements appearing on your screen.

Thanks Heiner for the help.