?
Solved

Backup and restore a local/remote database

Posted on 2005-03-24
16
Medium Priority
?
805 Views
Last Modified: 2010-04-05
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
Comment
Question by:BlackStorm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
16 Comments
 
LVL 26

Accepted Solution

by:
EddieShipman earned 400 total points
ID: 13630402
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
 

Author Comment

by:BlackStorm
ID: 13636996
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
 

Author Comment

by:BlackStorm
ID: 13643042
Should I add more points for this? Not sure how many points are needed for EE to be honest.

Thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 26

Expert Comment

by:EddieShipman
ID: 13643556
It doesn't matter if your connection is to the remote database.
0
 

Author Comment

by:BlackStorm
ID: 13643670
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
 

Author Comment

by:BlackStorm
ID: 13667002
Anyone?
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 13671953
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
 

Author Comment

by:BlackStorm
ID: 13677819
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 13681428
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 13681438
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 13681445
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
 

Author Comment

by:BlackStorm
ID: 13690284
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 13698175
I gave you two other options, ZEOSDB and TBatchMove that should work for you.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 13698181
Remember, with ZEOSDB Query, you can run mySQL commands.
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question