backing up tables on different machines

Anon

Hello,

I need to backup a table from a MySQL database on one machine to our MySQL server. I need help with the PHP scripting. How do I copy a table on a remote machine to a table on our local machine, using PHP? Any help would be greatly appreciated.

Regards,
Tony

[deleted]

You don't need/want PHP for that.
Read the MySQL manual about mysqldump instead.

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

I want to use PHP. The purpose of this script is so that the user can go to a web page, click a button that says backup, and the script will then get their IP, connect to the database on their machine (the script will know the database name already) and then copy the database (or a table) to an identical database (or table) on the server the php script is running on. I want to use php so they can back up their data, not me.

[deleted]

You should still read the mysqldump manual pages. You can run mysqldump and the mysql commandline tool from PHP to make the dumps.

It would make no sense to try to program the mysqldump functionality in PHP.

BTW, you do not want to allow remote database connections from/to your server.
Instead, offer your clients a download of an ASCII dump, and let them upload that dump again for a restore. The last thing you want is to have your database hang on a failing connection to some client's server.

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

Thanks for the input...

I'll have to think about this. I wanted to have my clients backup their database on our servers. I'm not quite sure I follow what your saying, but I'll look up the mysqldump man. The jist of it is, (in theory) they will have a Java program talk to the database on their machine, add stuff and such. Then at the end of the day, they can back that data up on our machine.

Maybe I'm looking at this wrong? I'll have to think. Thank you, you are certainly a G.U.R.U at this. Thanks.

[deleted]

"I'm not quite sure I follow what your saying,"

That's probably because I did not get that part about taht you want to make a backup of your client's servers, instead of having your clients make a backup of your server.

You can still use mysqldump though, it let's you connect to remote database servers and copy data into a file, which is really what you want.

To restore you can use the mysql commandline too also, but it does require that your clients setup security to allow your server to be 'SQL-God', letting it drop and create databases (eeek!)

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

While I saw your topic that is simiarily to what I am doing:
I use the following command to backup the database, however the created file contain nothing.
exec("mysqldump --opt [databasename]>[path]/[filename].dump -u root -p");
exec("[password]");
If not set password in mysql
just use the first line command that is ok
So I think may be the problem caused by when call the mysql command it can't receive the password
if anybody has experience in this aspect would you let me know
thanks

[deleted]

You can add the password in the mysqldump command:

exec("mysqldump --opt [databasename]>[path]/[filename].dump -u root -pYOURPASSWORDHERE");

but remember, the password is stored in your PHP code! anyone who can read your PHP code can read the password!!

Anon

Thanks a lot for your reply, and that is what I want to do!!
🙂

Anon

After previous time, I find an other method for remote node to backup the database by the following command. I think this command is more directly backup the database and at the same time place the backup file to remote node.However, while I use this command it can't do as I expect. And I directly type this command at server, it display error 'Access denied for user: root@REMOTE_IP '.does this mean that the root is just support locate host for use mysqldump command.

exec("mysqldump -h REMOTE_IP -u root -pPASSWORD --opt DATABASE>REMOTE_PATH.dump ");

if any one has any experience on this, would you please tell me
thanks