One good way to pull off something of that nature is to set up Master-Master Replication or Circular Replication. This is not to be confused with MultiMaster Replciation.
Setting up Circular Replication is actaully very easy if you have setup Master-Slave Replication. Here is what you need to do in order to configure it.
For this example, we will assume Master-Slave Replication is active but you will experience a bit of downtime (1-2 minutes):
Step 1) Add this line to /etc/my.cnf on the Master.
log-slave-updates
Step 2) Add these line to /etc/my.cnf on the Slave:
log-bin=mysql-bin (or have whatever the master has for this)
log-slave-updates
WARNING : Here is the brief moment of downtime !!!
Step 3) On the Slave, service mysql restart
This will activate binary logs on the Slave
Step 4) On the Master, service mysql stop
Step 5) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master.
WARNING : Here is the longer moment of downtime !!!
Step 6) On the Slave, service mysql stop
Step 7) On the Slave, find out the last binary log
Step 8) On the Slave, find out the filesize of the last binary log
Step 9) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master. This should be a faster copy.
Step 10) On the Master, edit
Line 2 of master.info with the last binary log of the Slave.
Line 3 of master.info with the filesize of the last binary log of the Slave.
Line 4 of master.info with the IP of the Slave.
Line 5 is the userid of replication user (DO NOT TOUCH)
Line 6 is the password of replication user (DO NOT TOUCH)
Step 11) Delete all binary logs and binary log index file of the Master.
Step 12) On the Slave, service mysql start, wait 15 seconds
Step 13) On the Master, service mysql start
Step 14) On the Master, run STOP SLAVE; SHOW MASTER STATUS;
Step 15) On the Slave, run CHANGE MASTER TO MASTER_HOST='IP of Slave',MASTER_USER='userid of replication user from Step10',MASTER_PASSWORD='password of replication user from Step10',MASTER_LOG_FILE='binary log from Step14',MASTER_LOG_POS=LogPos from Step14.
Step 16) On the Slave, run START SLAVE;
Step 17) On the Master, run START SLAVE;
I performed steps similar to this for another StackExchange question I answered.
Give it a Try !!!
There's a stack of system related questions here. We don't know what the hardware looks like for your master or slave. Maybe you're lacking RAM on the slave, or any number of other differences.
The master is probably receiving bursts of updates in parallel from many client connections, and for whatever reason, the slave can't keep up since all replication is done in serial.
If you have a lot of InnoDB activity on the master, you can probably disable InnoDB on the slave and gain some speed that way. That option and a few others are given here:
16.4.4.7: How can I use replication to improve performance of my system?
http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html#qandaitem-16-4-4-1-7
Or there could be configuration tweaks in my.cnf
that could help you. I'd start by looking at your memory usage on the slave, and looking up buffer and memory related options for innodb and/or myisam depending on which of those engines you're using (or others).
You can also look through your binlogs with the mysqlbinlog
tool to see what database and tables have the most activity. Then start working with your application developers and start chipping away at the problem.
Also check your mysqld error log on the slave. There might be clues in there.
Best Answer
Only the master would need a fixed IP address, since its the slaves that pulls data. You say UPDATE the master in your question, but it is not possible for a slave to update a master, unless you have a MASTER to MASTER replication setup. As you are using the word slave, then that assumes that the Slave server only pulls data but doesn't update the Master.
As for security, when it comes to grants, you would need to give your replication user access to the Master using a wild card.
On the Master MySQL server
Where percent represents 0 to 254. You could also just put % like this
So that the replication user can access the master server from anywhere
Here is a post I answered on setting up replication.
If the slave will not always be online, there is a threshold of transactions that will be available. If you wait past that threshold, it will no longer be possible to synchronize.
On the master MySQL server, in the my.cnf file, you might find an entry like this:
Which means that only 24 hours of binary logs will be conserved on the master server.
By default, MySQL does not automatically remove binary log files. Considering the amount of space they could take up, it is unlikely that there is no automatic removal.