The client is asking for backups of their data, however we would not like to share our database design as it may lead to a security risk...

inb4: Security by Obscurity is not a valid security control method.

I have thought about dumping only pertinent tables with their data in csv or excel xml form. What is the correct and most acceptable way without giving our whole schema away?

  • There is no correct way. If it cannot be used to restore your database into a consistent state, it is not a backup. What we are talking about here (and the customer probably wants) is a dump or an export. – Twinkles Dec 11 '13 at 7:35
  • Is this the first time you have received this type of request from a client? – user507 Dec 11 '13 at 7:45
  • Well I don't want the client getting the whole schema and database design...@shawn yes – stephen Dec 11 '13 at 8:08
  • I usually do mysql -e "some query" > abc.log. This file can be opened in MS-Excel (Text to Column option) and my operations team like to play in excel always. – georgecj11 Dec 11 '13 at 19:09

Dumping the data into CSV would be the best way. This way, the data is "most usable" to the client - as opposed to an XML file or even an SQL dump. They could conceivably take that data and use it in another system, for example.

If the data is going to be backed up by the client with the intention of having you (or indeed another party) "restore" or otherwise import that data at a later date, your data dump will need to include Primary and Foreign Keys so you can match the data up again.

For example, you might export a "Category" CSV file which contains CategoryID and CategoryName, a "Customer" CSV file which contains CustomerID, Name, Email etc and a CustomerCategory CSV file which simply contains a list of CustomerID and CategoryID values. You would then be able to use the three CSV files to rebuild the client's Customer and Category tables if required.

You can export the data using outfile as in the following example:

select col1,col2 into outfile '/path/file1.(txt/sql/csv)' fields 
terminated by ',' lines terminated by '\n' from tab ;
| improve this answer | |

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