Compare database schema pre/post release

Author: Colum Foskin

• The first thing to do is to find out where to source the correct database schema - for pre and post release (Ask a dev to clarify this)

• Dump the databases for each application - Ensure to use the flag –no-data

mysqldump -u root -p --no-data "database" > "dump file name" 

• Do this on each server for pre/post comparison

• Use ftp to take them down to your local machine:
  • Ftp: sftp “server name” and navigate to the dir and use “get dump file name”

• Create 2 databases for each application locally:
  • eg. Ticketer, create the ticketer_before && ticketer_after databases

• Important! before you proceed ensure that you have a ticketer and an snmp manager database locally - these are not used for the comparing, but to ensure no errors when loading the databases.

• If you do not have them then you will need to create them (the dump files will reference ticket.table and snmp_manager.table and your created ones will have different names so you will have errors)

• Then load the database dump file for each application to your created databases for before and after release eg:
  • For ticketer: mysql -u root -p ticketer_before < ticketer_before_dump.sql

• Bring up the database for each application on both the before and after, then do a show tables to be sure the tables have loaded correctly locally (verify tables are correct)

• Install the application DBSOLO to do the comparing - the scrum laptop has it installed if needed (and mysql)
  • Set up your connection to local dbs
  • Click tools → compare schema → on the src select your before schema, on the dest click you after schema
  • Click next until compare is done
  • Click the icon under the src window to show sql script which would update the old schema with the new one and save to file.
  • Repeat for each of the 4 applications, these are your scripts!