1
Peekay
Import multiple SQL table files - how do?
  • 2006/3/25 17:39

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


I'm test-driving a piece of MySQL backup software that first downloads all the individual tables from a MySQL database as separate text files and thereafter only downloads those tables that have changed.

Each text file looks similar to this:

#-- deleting current 'profession' table

drop table if exists profession;

#-- export table structure for 'profession'

CREATE TABLE `profession` (
  `
prof_idint(4NOT NULL auto_increment,
  `
prof_namevarchar(100NOT NULL default '',
  
PRIMARY KEY  (`prof_id`)
TYPE=MyISAM;


The software manages backups and restores to the original on-line database, but I also want to be able to populate a local MySQL database from the latest backup, in case I need to do any substantial work on the site off-line.

My problem is I can't see a way to import 30 or so separate SQL table files in PHPMyAdmin, other than doing so one at a time.

Does anyone know if there's a utility that would concatenate them into one SQL file?
A thread is for life. Not just for Christmas.

2
Peekay
Re: Import multiple SQL table files - how do?
  • 2006/3/27 8:01

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


Bump, anyone?

I did download SQLyog, but this doesn't appear to support batch import of SQL text files.
A thread is for life. Not just for Christmas.

3
tripmon
Re: Import multiple SQL table files - how do?
  • 2006/3/27 8:06

  • tripmon

  • Module Developer

  • Posts: 462

  • Since: 2004/2/28


if it's just structure, you can cut and paste in any text editor... I know that doesn't provide automated concatonation, but it would make for a single phpma operation...

There is also the phpma import directory which you can set up... I've never used it, but I have seen something about it... have you got the latest phpma version? maybe check to see if it can grab everything in the folder in a single operation (otherwise I don't see much use in having to configure the directory or going to the trouble to include it as a feature of phpma)

Probably not much help, but at least I tried ;)

4
Peekay
Re: Import multiple SQL table files - how do?
  • 2006/3/28 9:28

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


I found that BigDump and a small utility called doSQL will present you with a list if you have more than one SQL file to import, but you still have to click on every filename in the list.

Hopefully I won't have to do it that often.
A thread is for life. Not just for Christmas.

5
jdseymour
Re: Import multiple SQL table files - how do?

Are these actually *.txt files, or of another extension like *.myi or similar?

6
Peekay
Re: Import multiple SQL table files - how do?
  • 2006/3/28 10:30

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


Hi jdseymour.

They are text files, but with no file extension.

To provide the full story... I have been researching some options following comments in this thread about backing up dynamic sites. I had downloaded FTP-Sync and was looking for a MySQL backup utility when I found a piece of software called Site-Vault.

This appeals to me because it performs synchronised FTP file and MySQL database backups in one go. You configure the FTP and database connections as a 'site'. Once a full backup is done, backing up the site downloads both the MySQL data and any changed files. The current version doesn't feature scheduled backups, but apparently the next release will.

The demo works really well, but for some reason, the database tables are downloaded as separate text files, rather than as one SQL file. I was hoping to be able to use the table files to populate a local database if I needed to do any off-line development on a site. It turns out that I can do this, I was just surprised that I had to do it one file at a time.
A thread is for life. Not just for Christmas.

7
jdseymour
Re: Import multiple SQL table files - how do?

Sounds like it just downloads the MySQL data directory. Look at the files in your data directory on the server. You will probably see files with the same structure, however, with an extension. Seems that it would be easier if the program did not change the extension of the file, then you could just restore the directory (in a clean database of the same name.

8
Peekay
Re: Import multiple SQL table files - how do?
  • 2006/3/28 10:49

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


Yes, you're probably right. Also, in my original post I mentioned that the data backups were differential - i.e. only changed tables, however it turns out that *all* the data is downloaded each backup. Under those circumstances, the software might just as well export a single SQL file really.

I'll put it in their suggestion box.
A thread is for life. Not just for Christmas.

Login

Who's Online

454 user(s) are online (335 user(s) are browsing Support Forums)


Members: 0


Guests: 454


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits