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?

closed as primarily opinion-based by Shawn Melton, Mark Storey-Smith, Philᵀᴹ, Paul White, Taryn Dec 11 '13 at 11:05

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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? – Shawn Melton Dec 11 '13 at 7:45
  • Well I don't want the client getting the whole schema and database design...@shawn yes – Stephen H 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 ;

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