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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
GRANT REPLICATION SLAVE ON *.* 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.