2

So I would like to update my dev version of my database from my live database, I've exported the live database's schema and when I try and import it into the dev database, it is giving errors saying the tables already exist. The tables have data in them I don't want to lose and doing a full data dump would take too long and is not need. I just need the newer columns that are in the live database to go into my dev database, is there a way to mass import all the newer columns into the dev database tables?

ImANobody
  • 29
  • 4
  • AFAIK this is not possible through dumps. Not sure though. – Jorge Campos Mar 06 '18 at 20:23
  • That is what I thought, just wanted to make sure before I start going through and comparing the columns in the tables – ImANobody Mar 06 '18 at 20:28
  • Something to consider is column order. Appended columns are added after the last column, which may or may not be desirable for your use case(s). – The Head Rush Dec 22 '20 at 20:38
  • Can you export just the data, not the schema? PgAdmin and Sql Server Management Studio have this option. Then you would be able to restore just the data. Another option would be to just create a new database and restore the whole schema + data into the new db. Just be wary of production data in a non-production environment. That could be asking for trouble in terms of data theft, data tracking, etc. – ps2goat Dec 22 '20 at 20:40
  • You can checkout this ,if it helps at all. [Liquibase](https://www.liquibase.org/) ? – Dev-vruper Dec 22 '20 at 20:44
  • Liquibase has a concepts of 'changelog' and 'changesets' which you can manually modify for your need and try running it on your existing DB schema. – Dev-vruper Dec 22 '20 at 20:52
  • 2
    Another problem with added columns -- What about the new data that might be in them. – Rick James Dec 22 '20 at 22:02
  • Yeah, I misread the question (or stopped reading too early) :facepalm: You need a schema compare tool like others have said, and also a better approach to updating your live database. You should have scripts that get run in lower environments first (preferably), then move them up to production. The goal is to maintain consistency in each environment. If you can't do that, you should at least store your update scripts that you ran in prd some where so that you can find and apply them as updates in lower environments. I'm using flyway, which is similar to liquibase. – ps2goat Dec 22 '20 at 23:50
  • 1
    It looks like the [MySQL Workbench](https://dev.mysql.com/doc/workbench/en/wb-design-schema.html) has schema compare functionality built into it. – ps2goat Dec 22 '20 at 23:51
  • @ps2goat, unfortunate that option isn't available in the Ubuntu 18.04 & 20.04 versions (the 20.04 is the community version). I'll have to try the MS-Windows version, although that makes it convoluted... – Alexis Wilke Dec 26 '20 at 21:34

1 Answers1

1

I would recommend to take a look at mysqldiff utility. It's a tool which "which compares the data structures (i.e. schema / table definitions) of two MySQL databases, and returns the differences as a sequence of MySQL commands".

The source code (perl) of this tool is hosted on Github. There are already Ubuntu and Debian packages for this tool.

There are good examples in the manpage:

# compare table definitions in two files
mysqldiff db1.mysql db2.mysql

# compare table definitions in a file 'db1.mysql' with a database 'db2'
mysqldiff db1.mysql db2

Note that this tool is only dealing with the data structure, not the data itself.

Similar utilities exist for other SQL databases (for instance pgdiff for Postgresql).

Raphael Medaer
  • 2,528
  • 12
  • 18
  • Excellent! Well, it always wants to reset the indexes, it looks like, but it detects missing tables and missing/updated columns. Exactly what I need. – Alexis Wilke Dec 27 '20 at 20:50