Search code examples
mysqlsqldatabaseubuntu-16.04ubuntu-server

Import two different mysql dump file with the same schema (database name) on the same server

Me and my team is working on correcting things that our predecessor did, which is ruining the structure of our servers. Our predecessor puts a lot of servers with the same database on it (database name, tables etc), and each server contains data which is not available to the other.

Example:

Server 1 contains customer information but no customer transaction

Server 2 has no customer information but has customer transaction

and so on...

Now, We are working on correcting these things. Put all the data from 2 servers to one server only.

On hand, we have two different mysql dump and one server.

Question: Is it OK to import those two mysql dump with the same database name, tables, etc, but contains different data on one server that already have the said database?

I have already imported the big one of mysql dump, so I am waiting for your confirmation on this.

My server is Ubuntu Server 16.04.3 (with minimal gui)

Mysql Server 5.7 with Mysql Workbench

Thanks a lot...

UPDATE

Since the mysql dump contains only the CREATE TABLE tablename and INSERT INTO part and NO DROP TABLE IF IT EXIST tablename. I just proceed the execution of the mysql dump with the OPTION to ignore the errors (but not advisable, of course , that depends on your situation), since my only concern is to just add the missing data from the two mysql dump, the only errors I encountered was the "Duplicate entry", and that's perfect for my case.

Thanks.

Solution

  • A dump file, by default, is nothing but a bunch of SQL statements like this:

    --
-- Table structure for table `foo`
--

DROP TABLE IF EXISTS `foo`;

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ...other columns...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `foo`
--

INSERT INTO `foo` VALUES (...lots of rows of data...);

    Notice that the first thing it will do for each table is DROP TABLE, then re-CREATE TABLE.

    Don't do this!

    If your tables have the same names, then it will wipe out the data you've loaded so far.

    I suggest you create a new database and load the dump file into that. Then even if the tables have the same names, you'll have access to all the data in one MySQL instance. You can then use SQL to run cross-database queries to merge the data in whatever way you want (like with multi-table UPDATE statements joining each table of the same name in each database).

    I wonder why your former database developer separated the data... could it have had something to do with Sarbanes-Oxley compliance? You might want to look into that before you merge the data.