Need some Mysql help

[fantom1979]

Using SQLyog, I have to process each batch file individually. This takes quite a bit of user interaction (54 batch files). Is there a way to combine the batch files to load them all at once or is there a way to create a new batch file that automatically uploads all of the other batch files. Sorry, I am a bit of a newb here.

Thanks

[DaveHorn]

Do you have PHP on your server?

This will load all tables for you at once. There are others out there as well.

[fantom1979]

Dave,

I see your thread about the importer, but I get a tad lost with the PEAR stuff. I am running a home server on Windows 2000 with Apache, Mysql, and PHP. I created it just to make website and a message board. I have done some simple PHP scripts, but for the most part I am still new at this. I use SQLyog and PHPmyAdmin to connect to MySQL. SQLyog allows me to import a batch file, but only one at a time. If there was just a way to create a batch file that would load all of the other batch files, that would be the ideal solution for me.

[fantom1979]

Ok, i think i might be on to something here. I downloaded Text Magician (freeware http://www.david-pye.com/ ) and combined all of the .sql files into one big .sql file. I will run a test of the actual import tonight and see if it works.

[DaveHorn]

If you installed your win install using one of the packages that install Apache/MySQL/PHP all in one shot, it may of already contained the Pear packages. At the very least it intalled the Pear files for you. Then all you need to do is type the following from your php installed directory

go-pear
pear install DB


But simply merging all files into one and then loading it will work as well. Just more steps to perform everytime you load tables.

[fantom1979]

Originally Posted by DaveHorn

If you installed your win install using one of the packages that install Apache/MySQL/PHP all in one shot, it may of already contained the Pear packages. At the very least it intalled the Pear files for you. Then all you need to do is type the following from your php installed directory

go-pear
pear install DB


But simply merging all files into one and then loading it will work as well. Just more steps to perform everytime you load tables.

Thanks for your help Dave, I will check out PEAR more in the next couple of weeks, I am very interested.

[fantom1979]

Originally Posted by fantom1979

Ok, i think i might be on to something here. I downloaded Text Magician (freeware http://www.david-pye.com/ ) and combined all of the .sql files into one big .sql file. I will run a test of the actual import tonight and see if it works.

This worked for me, it takes some time to combine the files and then to import the now huge batch file, but now I can import the file in two steps instead of the 54 steps that was required before.

[Andreas Raht]

If you're having shell access to the server, you might also try this:

mysql -uusername -ppassword -t database_name < path_to_file/filename.sql

This will execute all the commands from the .sql file "directly". Should be very fast, and doesn't require any 3rd party lib.

Unix geeks will easily tell us how to import all sql files this way with a single line command using find and pipe or however that's called

You could also write a Perl script which creates a list of sql files and makes system calls for each file. I'll post such a script here ASAP.

[fantom1979]

Thanks Andreas,

I have had no problem loading the sql files by themselves using a 3rd party or batch file. I just didnt want to have to sit in front of my computer to load each of the 54 batch files. By combining them using a text editor I only have to touch my computer to merge the files and to load the one large .sql batch file. A Perl script would be great as well

[Andreas Raht]

Here is the promised Perl code to import the sql files on a server with Perl and MySQL available.
This code has not been tested and we do not offer any support for it. Use it at your own risk!

It just reads a directory into a list, then skips to the list, looking for files with the wanted file name extension, then calls mysql for each file which it finds. BTW you could also add another line to unlink (delete) the file after importing it, then add the script to your crontab to be executed every 30 minutes, which will automate the process a little bit. Just an idea.

#!/usr/bin/perl
use strict;
use CGI::Carp qw(fatalsToBrowser);

my $path="/usr/local/data/whatever/";
opendir(DIRHANDLE, $path) || die "Cannot opendir /some/path: $path!";
my @dirlist=readdir(DIRHANDLE);
my $name;
foreach $name(@dirlist) 
{
 if (index($name,".mysql.sql") > 0)
 {
  print "Importing ".$path.$name."...\n";
  system("mysql -uusername -ppassword -t database_name < ".$path.$name);
  
 }
 
}
closedir(DIRHANDLE);

[fantom1979]

Originally Posted by Andreas Raht

Here is the promised Perl code to import the sql files on a server with Perl and MySQL available.
This code has not been tested and we do not offer any support for it. Use it at your own risk!

It just reads a directory into a list, then skips to the list, looking for files with the wanted file name extension, then calls mysql for each file which it finds. BTW you could also add another line to unlink (delete) the file after importing it, then add the script to your crontab to be executed every 30 minutes, which will automate the process a little bit. Just an idea.

#!/usr/bin/perl
use strict;
use CGI::Carp qw(fatalsToBrowser);

my $path="/usr/local/data/whatever/";
opendir(DIRHANDLE, $path) || die "Cannot opendir /some/path: $path!";
my @dirlist=readdir(DIRHANDLE);
my $name;
foreach $name(@dirlist) 
{
 if (index($name,".mysql.sql") > 0)
 {
  print "Importing ".$path.$name."...\n";
  system("mysql -uusername -pp***word -t database_name < ".$path.$name);
  
 }
 
}
closedir(DIRHANDLE);

Thank you