CODEWITHSUNDEEP
javascriptpythonmysqllinuxshell
anika
anika

Reputation: 63

How to compare two MySql database

I have to compare two MySql database data, I want to compare two MySql schema and find out the difference between both schema.

I have created two variables Old_Release_DB and New_Release_DB. In Old_Release_DB I have stored old release schema than after some modification like I deleted some column, Added some column, Renamed some column, changed column property like increase datatype size (ex: varchar(10) to varchar(50)). Than it became new release schema that I have stored in New_Release_DB.

Now I want to Table Name, list of column name which has changed in New_Release_DB, and changes along with column name.

Example,

Table_A Column_Name Add(if it is added), Table_A Column_Name Delete(if it is deleted), Table_A Column_Name Change(if its property has changed)

I am trying it in Shell script in Linux, But I am not getting it. Please let me know If I can use other script like python or java.

Upvotes: 1

Views: 5529

Answers (3)

JanC
JanC

Reputation: 335

Have a look at mysqldiff

It can also produce the nessecerary SQL statements to convert from one scheme to the other (or vice versa)

The documentation from the above link is rather good. And getting started is as easy as: 

mysqldiff --server1=user@host1 --server2=user@host2 test:test

Upvotes: 1

Maciej
Maciej

Reputation: 181

I use mysql Workbench which has the schema synchronization utility. Very handy when trying to apply changes from development server to a production server.

Upvotes: 0

Sergey Brodin
Sergey Brodin

Reputation: 361

You can compare two databases by creating database dumps: mysqldump -u your-database-user your-database-name > database-dump-file.sql - if you're using a password to connect to a database, also add -p option to a mysqldump command.

And then compare them with diff: diff new-database-dump-file.sql old-database-dump-file.sql Optionally, you can save the results of diff execution to a file with STDOUT redirecting by adding > databases_diff to a previous command.

However, that kind of comparison would require some eye work - you will get literally a difference between two files.

Upvotes: 0

Related Questions