Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 905
  • Last Modified:

Backup and restore a local/remote database

Hey,
I would like to backup and restore a MySQL database form my delphi program.
I have tried a few components, one which was recommended on About.com but I can't work out how to backup a database with it...it doesn't seem to have anything to do with MySQL.

What is the quickest, easiest and more secure way to backup a database?

I would like to give the user a button to backup either the complete database or just certain tables and also restore certain tables, or the full database.

I would also like to give the option to compress the database for faster download (Maybe upload if possible to upload a compressed sql dump)

Hope someone can help me out!

Thanks
John
0
BlackStorm
Asked:
BlackStorm
  • 8
  • 6
1 Solution
 
Eddie ShipmanAll-around developerCommented:
You can use ShellExecute on mysqldump.exe in the Bin directory yto backup your data
Here is the command line syntax: http://dev.mysql.com/doc/mysql/en/mysqldump.html

There are several comments on that page that also show how to make a batch file
to do it, including gzipping the result.
0
 
BlackStormAuthor Commented:
Hey,
How exactly would I run that on a remote computer/server from my local delphi program?
Mysql can be installed in a number of different directories so that would mean searching for the file in the remote server then executing it.

Isn't there a way to backup using just the delphi program itself?
You can backup/restore a database using PHP so is there a way to run a PHP/database query to create a dump and then download it to the local computer?
0
 
BlackStormAuthor Commented:
Should I add more points for this? Not sure how many points are needed for EE to be honest.

Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Eddie ShipmanAll-around developerCommented:
It doesn't matter if your connection is to the remote database.
0
 
BlackStormAuthor Commented:
But if I have to execute the program wouldnt I have to execute it on the remote computer?

If the database is on a remote server then wouldn't it need the mysqld to be on the remote server?
If it does, that would mean executing mysqld from the remote server.

I think you can backup a database without executing mysqld (I knew about execuing mysqld) but I would like to do it with just code in the program.
I can access everything in the database from my delphi program, so there should be a way to create a backup of the database.
Maybe get the program to download all tables and content to the local computer?

I know what I am looking to do in english (psuedo?), but I don't know the actual code to do it.
0
 
BlackStormAuthor Commented:
Anyone?
0
 
Eddie ShipmanAll-around developerCommented:
No, the program to do the backup can run on a local computer as long as your database is on another computer and
you have a remote connection to that database. Wouldn't mysqld be running on the remote computer, anyway?

If the remote computer has a share defined to the dir where the mysql data files are located, yes, you could transfer
them, register them with your mysqld and then run the backup.

Personally, I'd rather just have mysqld running on all the remotes and setup a remote connection
to them and run mysqldump. That is the easiest and foolproof way to do this.

I could not find ANYTHING that said that mySQL tables could be backed up without mysqld running. I
posted a question to the mySQL DEV forums about this, too.
0
 
BlackStormAuthor Commented:
Hey Eddie,
I would have to search the remote server for the mysqld server though, some servers have the mysql files in different folders, at least one of my servers doesn't have the mysql files in the default folder for that OS, and a number of OS have different locations for the default mysql install also.

If I have to access the remote mysqldump that would mean I need to know the file location wouldn't it?

There are a number of PHP scripts which backup a mysql database without running any programs external from the script, phpmyadmin for example, but there are a number of smaller, more simple scripts that do this also.

Is there anyway I can do what these scripts are doing without the need to run mysqldump?

Thanks
John
0
 
Eddie ShipmanAll-around developerCommented:
phpmyadmin actually logs in to the database by creating a connection to the DB so mysqld HAS to be running for it to work
otherwise you can't connect. I'm suggesting you do the same thing. The only way you'd have to know where the files
were located was if you were going to copy them.

Like I said, you can setup a mySQL DB on a remote system and connect to it from your system by setting up a connection to it.
It's not that difficult. Just enter the IP of the other machine and user and password. Then you can connect. Try doing it using
mySQL-Front or some other GUI that connects to mySQL.

BTW, no one has replied to my question on the mySQL Dev forums, yet.
0
 
Eddie ShipmanAll-around developerCommented:
Another option would be to use ZEOS DB Objects. It has the capability to conenct to remote mySQL DBs.
I wouldn't know how to backup a DB using them but I'm sure there's a way.
0
 
Eddie ShipmanAll-around developerCommented:
Or even TBatchMove with myODBC. Setou a myODBC connection to your local DB and the remote DB and use TBatchMove to
"copy" the table(s) and their data.
0
 
BlackStormAuthor Commented:
Hey Eddie,
Sorry I think I must have made a mistake earlier.
I meant without needing to run mysqldump, mysqld would have to be running to access mysql because that is the actual program for mysql itself.

I am connecting to a remote database with my program already using MyODBC, but I am not sure how to create a dump of the database and then download that to my local computer that I am making the connection from.

I didn't get a chance to test out the code you posted and it's 4am here now so I am about to go to bed, but I will try this tomorrow because I am working on the code tomorrow.

Thanks!
John
0
 
Eddie ShipmanAll-around developerCommented:
I gave you two other options, ZEOSDB and TBatchMove that should work for you.
0
 
Eddie ShipmanAll-around developerCommented:
Remember, with ZEOSDB Query, you can run mySQL commands.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now