MySql: Select database and perform mysql dump

[rennis]

I currently have a batch file created using shell which runs mysqldump and saves the information to a file.

However, I was wondering if there would be a way to use a recordset instead to select the database and initiate the mysql dump.

Ideally this is what i would like to have:
rs.Open "Use database mysqldump > C:\", conn, 3, 3

Does anyone know if this is possible?

 

[hvass]

I think you are asking can you get this utility to return some sort of in memory result set

My guess would be no, my understanding is that it is a command line utilty. I stand to be corrected I have never used it.

What you probably want to do is use one of the api interfaces, perl, php, c etc.. or connectors odbc, .net, visual studio, J etc.. which will return a record set depending on your language of choice

 

[rennis]

hvass, thank you for your reply.

I understand that mysqldump is a command line utility, but i was wondering if there was anything similar i could use in place of it by using a recordset. for example, to select information from a table it would be:
recordset.open "Select * from tablename",connection, 3,3.

I have a current system running with vb front end and mysql back end, with the odbc 3.51.12 connector.

So i was just wondering if there was anyway to instead of selecting the table, select the database, and process the same command that mysqldump would.

Thanks again

 

[rennis]

Is anyone aware of a way that this could be accomplished?

 

[hvass]

I dont know VB so can only give you an idea of the structure of the code

One option would be do a show tables from databasename and then run for each tablename returned a mysqldump

But for larger tables and databases my suggestion is that for each table in the database you dump the table to a file and at the same time create a sql script file that you put a whole series of create table and load data statements in. This should produce a series of data files and a single sql script file. To restore the database you would simply run the sql script.

open database connection
open SQLSCRIPT for output

#get a list of the tables in your database and for each table
$resultset=execute "show tables from mydatabase"  
foreach $tablename(in the resultset){
  #do a show create table and write result into script file
  $resultcreatetable=execute "show create table $tablename"
  print SQLSCRIPT $resultcreatetable
  #now dump the data to a file using select into outfile
  open $tablename.txt for output
  execute "select * into outfile $tablename.txt fields terminated by '\t' lines terminated by '\n";
  close $tablename.txt
  #prepare the load data statement that will read the data back in
  print SQLSCRIPT "load data infile '$tablename.txt' into $tablename fields terminated by '\t' lines terminated by '\n"
}
close SQLSCRIPT

 

[rennis]

Thanks hvass for your assistance.Its greatly apprecaited