Backup, Restore procedure. .sql, .txt and InnoDB

Forum Moderators: open

Message Too Old, No Replies

Backup, Restore procedure. .sql, .txt and InnoDB

MySQL, Backup, Restore Commands for InnoDB

i_dvlp

6:56 pm on Nov 21, 2008 (gmt 0)

I'm curious to get the input from an expert.
I don't remember the exact MySQL command I ran to backup my InnoDB database. I do remember having to specify a path. What's curious is, most of the MySQL examples show redirection to a dump file and I have a .sql and .txt (delimited data) file for each table that was in the database.

I may have used a method that was appropriate only for ISAM and somehow it worked for InnoDB, also.
I may have used the SQLyog tool, and by mistake did not use the Dump to SQL (for InnoDB), and used the main backup, which claims to use 'MySQL Backup Table' method.

As I said, I have separate .sql and .txt for every table.
How can I restore these tables given the structure and data?
TIA for your suggestions.

physics

3:24 pm on Nov 24, 2008 (gmt 0)

Try using the same tool you used to back it up.

i_dvlp

4:07 pm on Nov 24, 2008 (gmt 0)

It looks like it would be a combination of running these 2 commands:
mysql -uUSER -p DATABSE < table_ddl.sql
LOAD DATA INFILE 'delim_table_data.txt'

But I wondered if it can be done en-masse, as a batch run.
With 15 tables running these commands gets a little monotonous and tedious. Any wildcard options ? Any suggestions ?

physics

4:46 am on Nov 25, 2008 (gmt 0)

The command line is your friend


for x in `ls *.sql`; do mysql -uUSER -pPASS DATABSE < $x; done for x in `ls`; do echo LOAD DATA INFILE \'$x\' >> batch.sql ; done 
mysql -uUSER -pPASS DATABASE < batch.sql

((provided without warranty of any kind))