How can I setup mySQL replication in such a way that a slave on localhost with dynamic IP can update the master as well as get updates from the master which has a static IP.

The slave will not be always online.


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

TO 'replication'@'192.168.1.%' 
IDENTIFIED BY 'replicationuserpassword';

Where percent represents 0 to 254. You could also just put % like this

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replicationuserpassword';

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:

expire_logs_days    = 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.