List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:September 6 2004 5:34pm
Subject:Re: FW: Backup / Restore
View as plain text  
What do you do in the situation where your data has spaces in it?
Since select into outfile space seperates columns, any columns you
have with spaces in the data will not import correctly. Exampl:

mysql> create table t (name varchar(100), address varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t (name, address) values ('My Name', '1243 Street st.');
Query OK, 1 row affected (0.00 sec)

mysql> select * into outfile '/tmp/testdump' from t;
Query OK, 1 row affected (0.01 sec)

cat /tmp/testdump
My Name 1243 Street st.

How does your import script decide where to stop the name column and
begin the address column?


On Mon, 6 Sep 2004 10:22:48 -0500, Gordon <gordon@stripped> wrote:
> We have built an alternative save restore process. First we take all of the
> .frm files  and build
>  "select * into outfile '/path/tablename.txt' from tablename"
> statements. We also dump the structure only and put it in the same
> directory. This runs much faster than myysqldump and every table is in its
> own file. The real advantage for us is that for many user errors we can
> quickly load the few tables into a 2nd database on the server and then
> reconstruct. On our 2 processer Pentium server a 650,000 row table takes 7
> seconds to save or load. We have a mixture of MyISAM and INNODB tables and
> this process works for both. The primary reason we went to it was we were
> using mysqldump and encounterd a case where the output file grew to > 2GB.
> Restores worked fine until one day we had to do a restore and for some
> reason the file was corupted about half way through. The tables we were
> trying to get back happened to be at the end of the file and we could not
> find a tool to bypass the corupted data.
> Our database is ~ 3 GB  with data and indexes and the backup takes a few
> minutes to run in total.
> I would be iterested if anyone on the list sees any issues with this as a
> backup/restore methodology assuming we still do lock tables and use the
> binary log. This approach does take some additional admin effort if we add
> or drop tables.

Eric Bergen
FW: Backup / RestoreGordon6 Sep
  • Re: FW: Backup / RestoreEric Bergen6 Sep