How to: Synchronize 2 MySQL server

[Noip]

Hi I need to backup my current remote MySQL server on a linux 7.3 box which is also running MySQL. My Linux box is not reachable from Internet. I would like to import from the remote server and have a synchro. triggered since the remote server is continuously being updated. Is it possible? I'm also new to linux...

Any help would be appreciated...

 

[piti]

how do you want to do it, if one of the servers is not on the net?
u need to somehow transfer the data ;-)

 

[Noip]

Sorry, when I was saying not 'Reachable', I mean my linux box have no global IP. It still can access the Net i.e it is behind a router.

Meanwhile, Output for
mysqlaccess -h 'remoteserverip' -u 'username' -p 'password' -d * --debug=0

gives
Could not open outputfile ~/mysqlaccess.log for debugging-info

I have then place a txt file called mysqlaccess.log at root
But still same problem
Any idea

 

[piti]

i can tell you how i do it:

remote box:
cron job packing selected databases into one backup file

local box:
cron job downloading this package, stopping mysql server, unpacking into the datadir, starting mysql server

but i have set another one on my other box:

on the local box using lynx open a php script on the remote box which puts the relevant data into special download tables, another script creates comma separated plain text files from this tables, downloads this files and with a reversed procedure loads the data into the database
this way i can download only the changed data

maybe none of those will suite your needs, it depends on the situation

 

[Noip]

I forget to tell...

I'm a newbie and unless I have a detail explanation, I won't be able to try your suggestions

 

[piti]

newbie to what? mysql? linux? computers?

that cron method requires only the knowledge of tar and it's switches, and some downloading util that is able to run noninteractively
to learn more - man tar, man crontab

and the php method needs you to create a script that dumps/restores database into/from a plain text file and again that downloading util of your choice

 

[nigelcohen]

There is a clumsy (but effective) way to get the data from an internet database to a local machine:

1. Backup the internet database - on the Linux command line, type:

mysqldump -u (yourusername) -p(password) -all-databases > (/path/filenameforbackup)

2. Retrieve the file to your local machine

3. Restore the internet database by:
a) Edit the retrieved file to remove any databases you do not want to restore. Note that restoring MySQL database may cause problems since this permissions database will be overwritten with the internet version. Just delete all references to that table in the retrieved file
b) Go into MySQL in the normal way
c) Drop the databases you are about to import (it is a good idea to ensure you have a backup of the dropping databases before you do this!!)
d) Import the retrieved databases using the command:
source (/path/fileretrieved);

Good luck