MySQL Bugs: #3351: Import and export databases from/to zip files.

Bug #3351	Import and export databases from/to zip files.
Submitted:	31 Mar 2004 17:22	Modified:	1 Apr 2004 7:55
Reporter:	Francisco Reinaldo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.0	OS:	Windows (Windows + UNIX.)
Assigned to:		CPU Architecture:	Any

Description:
It would be great if databases could be exported to zip files through a command like ZIP DATABASE <databasename> <full_file_path> and also being able to import databases with UNZIP DATABASE <databasename> <full_file_path>.

I believe that is a great feature to import/export databases without having to use the export and import features through the LOAD command.

How to repeat:
N/A

Hi,
We have had this in our TODO for long. Here is a workaround for now; it's to use the free "zip" and "unzip" utilities which are available here (and are included in most Linux by default) for many Unix and Windows versions:
http://www.info-zip.org/pub/infozip/Zip.html
(I have used them in the past for backups, they work and are compatible with WinZip for example).
Then to do the equivalent of ZIP DATABASE <databasename> <full_file_path> do:
mysqldump --databases <databasename> | zip <full_file_path>.zip -
(don't forget the final "-"),
and for UNZIP DATABASE of this zip, do:
unzip -p <full_file_path>.zip | mysql
On Unix systems both commands will be very efficient: pipes "|" are handled in such a way that the command after the "|" starts as soon as it receives some output from the command before the "|". So if your database is 100G, it will not create a 100G temporary file before starting compression.
On Windows unfortunately (unless they improved it very recently?) pipes are not working this way, they are rather creating a 100G temporary file.
I hope this tip can help you.
Guilhem

Even though the work around is fine, I would like to be more precise of how I think it should work to make sure that we are in the same page.

The ZIP and UNZIP functionality, should be a "safe" database operation like CREATE DATABASE. When I say "safe", what I really want to say is that needs to be consistent with other database commands such as SHOW DATABASES. Ideally if I am using ZIP mydatabase FROM /whateverpath/mydb.zip, SHOW DATABASES should not display that database until has been unzipped 100%.

The same concept applies for other commands such as USE, DROP, and so forth.

In another words, the database should be "hidden" while a ZIP or UNZIP operation is performed.

Ah sorry, I initially thought you were more insisting on the "compress data" functionality than on the "be atomic" functionality.
Atomicity of backup/restore, we don't have it currently; even leaving compression aside, if you do "mysqldump | mysql" then you will be able to see a table as soon as soon as it is created. For some people it's a feature, as it means data is available ASAP. For others (and I understand it) it's a concern.