1

I currently make a backup of my 2.5GB (and growing) MySQL Database every day. I have over 100 tables that are backed up.

I use this command:

mysqldump --user=user --password=pass --host=localhost db_name | gzip > backup.sql.gz

Works great but when I need to quickly restore data to a single table, it's a horrible process. I have to download the backup, extract the ZIP file, wait forever for the editor to load the SQL file so that I can remove the other tables I don't require. When I need this done fast, I'm pulling my hair out

Can anyone recommend a better way to store MySQL backups? Is there a command to split all the tables into their own sql files?

Appreciate your help!

Ben Sinclair
  • 3,896
  • 7
  • 54
  • 94
  • 2
    Use the [`--tab`](http://dev.mysql.com/doc/en/mysqldump.html#option_mysqldump_tab) option? – eggyal May 26 '13 at 08:01
  • Or else, use [Point-in-Time (Incremental) Recovery Using the Binary Log](http://dev.mysql.com/doc/en/point-in-time-recovery.html)? – eggyal May 26 '13 at 08:09
  • It is possible to restore a single table by single command, even without extracting the whole backup to disk (by piping): [Can I restore a single table from a full mysql mysqldump file?](http://stackoverflow.com/q/1013852/1323641) – Olexa May 26 '13 at 08:17
  • @eggyal Thanks for the suggestion. Looks like that might be what I need. I've tried this `mysqldump --user=user --password=pass --host=localhost --tab=backups/ db_name` but it only exports the schema of the first table. Sorry, 'm a bit of a noob at the mysqldump command. – Ben Sinclair May 26 '13 at 08:19
  • @Olexa That's super helpful thanks! But most of the time I don't want to do an entire table restore. I might just want to pick out certain columns and rows from the table. – Ben Sinclair May 26 '13 at 08:19
  • When you say "*it only exports the schema of the first table*", do you mean that you get `.txt` files containing the data from all tables but an `.sql` file containing the schema for only one? – eggyal May 26 '13 at 08:20
  • @eggyal A single .sql file containing the schema for only one table. – Ben Sinclair May 26 '13 at 08:23
  • Well, each `.sql` file should now contain the schema for only one table... but there should be one for each table! Did it abort with an error? I suspect it was, as noted in the manual, because "*You must have the [`FILE`](http://dev.mysql.com/doc/en/privileges-provided.html#priv_file) privilege, and the server must have permission to write files in the directory that you specify.*" – eggyal May 26 '13 at 08:26
  • Ah yes, seems to be a permissions thing. Weird how it wrote one file though... I'll contact my host and see if I can get this permission sorted – Ben Sinclair May 26 '13 at 08:41
  • @BenSinclair: Chances are that the `mysqldump` client writes the `.sql`, then requests that the server write the `.txt`. It seems that the server failed, and `mysqldump` aborted. – eggyal May 26 '13 at 11:58

0 Answers0