Can I synchronize my local db with my remote db?

[webvandals]

I want to have a mirror image of my remote db on my local machine.

My remote MySQL db is on my website which I have full root access to. The local one is also MySQL on my Linuxbox at home.

What I want to do is to have a php webpage on my local server that will (with the click of a button), go fetch the remote db and overwrite my local db with the remote one. Is this possible all through php?

Thanks!

[nemonoman]

Provided you have access to mysqldump installed on your remote server (probable), then the answer to your question is yes.

[bubblenut]

Check out Replication. It's a bit of work to set up but once it's going it requires almost no work. You do have to note that you cannot update (insert, alter, update etc) the slave, only the master. If you do replication on the offending table will fail and you'll have to do some rebuilding which can be a bitch. Sounds like this may be an issue as I imagine what you want this for is a fully functioning test environment.

Good luck and let us kow how you get on 🙂
Bubble

[nemonoman]

My guess is that what you mean to do is occasional updates, not on all te time master-slave replication. The environment you're described isn't really set up for replication.

The script you're describing is pretty simple:

Step 1...on your remote server:. exec ("mysqldump -uUsername -pPassword DatabaseName >sqldumpfile.sql")

If you wanted to you could gzip the file on the server, which compresses an average sqldump file by 70%

Step 2. on your local server: download the sqldumpfile.sql. Easiest would be to use: fopen(), fread(), fclose, but you could just create a browser link to the file and let the browser download it for your.

If it it gzipped, use exec to gunzip it.

Step 3: on your local server: exec(mysql -uUsername -pPassword DatabaseName <sqldumpfile.sql) to replace local db records with reccords dumped from remote db.

Step will take the longest depending on your internet connection speed and the size of your db. The other steps will be very fast.

[webvandals]

Thanks guys! I think nemnoman's suggestion is closest to what I want to do. But maybe I should be more specific about what I want to do:

1) I have an online DB which is the back-end to a shopping cart system and stores gpg encrypted cc numbers.

2) Obviously I don't want to store the gpg secret key on the server, but the admin section will need to be able to see the cc#.

My hairbrained solution is to have the admin section basically operating online, with a link to localhost like this:
http://127.0.0.1/store_admin/show_cc.php?cust_id=666

That way the secret key can be stored locally and used locally to decrypt the cc # as long as my local db has recently been synced with the live one. Surely there's an easier way to do this than my crazy idea, right?