0

I'm trying to export a fairly complex mySQL database from the working local server to upload on to an online server for collaborators to READ ONLY the data within.

The database has a number of Foreign keys, and every table has a primary key. However, since NO DATA WILL BE ADDED to this dumb "shadow" copy, these are irrelevant, and frankly creating a headache trying to get them to import successfully.

So... IS there a way to export a MySQL databases' structure (and possibly data) withOUT any keys, keeping the autoincrement column, but just treating it like any other INT column, and removing the foreign key constraints?

Trees4theForest
  • 1,267
  • 2
  • 18
  • 48

1 Answers1

0

You can export a MySQL database without AUTO_INCREMENT options this way:

mysqldump --compatible no_field_options ...

I don't know of any way to omit the foreign key constraints. But if you use mysqldump, the export does SET FOREIGN_KEY_CHECKS=0; before creating any tables. This allows tables to be created out of order.

Re your comment:

If you really can't allow foreign key declarations in your table definitions, you'll have to edit them out of the MySQL export file. You can do this manually with any text editor, or else come up with a filter using sed or perl or a variety of other tools.

There's an example of a sed command in one of the answers to How do I dump MySQL file without Foreign Keys via command line?

It might be easier to drop the constraints in your tables before you export the database.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • The FK issue is more that the shared server I'm using doesn't allow the `REFERENCES` command which means it chokes on any FK definition, checked or not... – Trees4theForest May 11 '19 at 23:36