0

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.

  • 2
    show show us what have you tried so far ? or at least ask clear question, it's unclear. show some data, file or etc. – Rahul May 2 '16 at 6:48
  • I have two database one is "old-tables" and one is "new-tables". We have made some changes in "old-tables" database. then updated "old-tables" database we are calling it "new-tables" database. – user168519 May 2 '16 at 7:07
  • Sounds like something that should be done in SQL, not shell scripting. – jordanm May 2 '16 at 7:21
  • If you want to compare the descriptions (or some scripts), use e.g. diff(1) – vonbrand Aug 17 at 15:59
0

To compare the differences between schemas of two mysql databases from the command line you can use mysqldiff

example usage from the documentation

shell> mysqldiff --server1=root@localhost \
          employees.salaries:emp1.salaries --differ

# server1 on localhost: ... connected.
# Comparing employees.salaries to emp1.salaries                    [FAIL]
# Object definitions are not the same:
  CREATE TABLE `salaries` (
    `emp_no` int(11) NOT NULL,
    `salary` int(11) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`emp_no`,`from_date`),
    KEY `emp_no` (`emp_no`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
?           ^^^^^
+ ) ENGINE=MyISAM DEFAULT CHARSET=latin1
?          ++ ^^^
Compare failed. One or more differences found.

If the two datatabases are not the same the utility should return a non-zero exit code which you could incorporate into a shell script.
To me the long term solution to this problem is that this DB schema should really have its own independent versioning system.

0

You can certainly use Toad software which is a freeware and it has excellent options available to compare the databases, tables and schema.

Check here https://www.toadworld.com/products/toad-for-sql-server

I found this very handy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.