MySQL backup using ASP 1

[altisdesign]

I wonder if any of the more experienced members of the board could help me out with this problem I'm having. I'm coming to the conclusion it isn't possible, because I can't find any reference to it anywhere on the web after a good deal of searching. Let me explain my position, I'm writing an admin area which will access and update a large MySQL database. When the asp code runs the update (about once a week), there will be a high potential for things to go wrong, and as a precautionary measure I wanted to perform a database backup before it ran the database updates.

What I wanted to do was similar to the PHPMYADMIN "Export" function, but in ASP, and save the file on the server for me to download at a later date if needed. If I could create the file, I figured that I could save it to a directory with write priveledges on the server. The problem is actually creating a dump of the structure of all of the tables using an ASP script. I have no idea how PHPMYADMIN manages to do it, but I don't know any PHP whatsoever, and I would far prefer to make it work automatically through the admin area I am scripting in ASP. All the references I could find in the MySQL manual were to creating the backup using telnet access, which we don't have at my host for security reasons, so I wondered if anyone else had come accross this problem before and had a possible solution that could help me [], or whether anyone can say that it definitely is not possible.

Many Thanks in advance
-Altis Design

 

[shaddow]

What platform are you using? ASP on Linux or ASP on Windows?

________
George, M

 

[altisdesign]

ASP on Windows

 

[shaddow]

it is MySql or MS SQL?
and if it's MySql you need MySql ODBC drivers to access the database, after it should be easy.

________
George, M

 

[altisdesign]

Its MySQL. Sounds interesting - thanks, what code would I use then ?

 

[shaddow]

You have to go to and get the ODBC driver from

http://www.mysql.com/products/myodbc/index.html

and then use it with Server.CreateObject

________
George, M

 

[altisdesign]

Its on a hosted server, so its got all the ODBC drivers installed. What code would I use once I'd created the server object to list out the table schema and do the backup?

 

[Genimuse]

I've not used MySQL, but taking a gander at the documentation online at

http://www.mysql.com/doc/en/BACKUP_TABLE.html

it would appear that you can send this SQL command (via a standard ASP ADO.Execute):

BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'

You'll have to back the tables up one at a time, but that shouldn't be too bad.

Based on the comments on the bottom of that page you'd need to use the FSO to delete or rename the existing backup files first, or use a unique naming system (that, for example, includes the date) because it won't write overwrite existing files.

The only way I saw (including a Googe search) to backup more than one table at a time requires the command line, which means WSH would have to be installed on your shared server, something that's unlikely (even if it is it's unlikely you have system permissions to use it).

 

[Genimuse]

Oops, sorry, it's obvious based on the syntax that you can backup all of the tables at the same time; you just have to specify them in the command.

 

[altisdesign]

Thanks, I've just tried it... and guess what? I get access denied - my host must have disabled it I can't figure why, I even tried saving it into one of the folders that have write permissions, but I've written to them about it, hopefully they'll give me one folder that I can write to! Thanks for the suggestions, yes that does look a good way of doing it. If they don't allow me to do it "for security reasons" I could remain stuck though. Good call on the FSO filenaming, I probably would name them based on the date, that looks to be the best way of doing it.

 

[Genimuse]

I noticed in the notes on that link (or perhaps the main backup page) that the command seems to base its security not on the permissions of the client (which in this case is ASP/IUSR) but instead on the server, MySQL. That means that the MySQL app/process may need to have permissions to write files in that directory, something your host should be able to fix.

 

[altisdesign]

Thanks for this, I've sent in the request by mail, I hope they'll accept it Thanks for your help - I'll let you know!