0

I've configured a master-slave structure for my database by following this tutorial. It works well and all is fine. I mean, any new change that happens in the master, will be applied to the slave in a few secs.

Just I have 5 questions about how it works.

  1. In the master server MySQL config file, I replaced the bind-address value from 127.0.0.1 to the server IP itself. Why? The localhost and the server's IP aren't the same? Don't get me wrong, I'm saying the IP of the server itself, not the IP of the slave server.

  2. I want to know, either does the master send new logs to the slave (using a cron job or something) in a specific interval or the slave is keep calling the master (using a cron job or something) to see if there is any new change in the binlog file to apply on the replicated database?

  3. What's the mysql-relay-bin file? I saw it on the slave server. I know there is a mysql-bin file on the master that contains the database-write-changes and the slave server uses it to update itself. But, what's the usage of mysql-relay-bin?

  4. There are lots of mysql-bin files created also in the slave server. Why? I think just those files should be in the master server. Can I delete them all from the slave server? Also, the last file is mysql-bin.000043 on the slave and the last file is mysql-bin.000004 on the master server. So, why 43 binlog files on the slave and just 4 binlog files on the master?

  5. Is there any approach to delete binlog files from the master server that have been applied to the slave server? Since they have been successfully replicated/copied to the slave server and generally we don't need them anymore.

4
  • That tutorial seems not complete to me. There is no need to add binlog_do_db on the master side. Master should log all changes it is the slave task to take what needed. If you add another schema on the future with your configuration you need to restart the master. Aug 4, 2023 at 15:11
  • "There is no need to add binlog_do_db on the master side", really? on no need it on the master? So, the slave should replicate the new changes based on what? @ErgestBasha
    – Martin AJ
    Aug 4, 2023 at 15:15
  • On the slave you configure which schema you want to replicate. For example in one of my master-slave configuration I have on the slave replicate_do_db=asterisk and replicate_do_db=realtime , in the master side those values are commented #replicate_do_db=asterisk and #replicate_do_db=realtime. That setup is up and running since October 2022 without any issue using MIXED format Aug 4, 2023 at 15:19
  • I see @ErgestBasha, good
    – Martin AJ
    Aug 4, 2023 at 15:42

1 Answer 1

1
  1. Bind address -- (I don't have the answer.) @ErgestBasha suggests https://superuser.com/questions/897699/what-is-the-difference-between-127-0-0-1-and-my-assigned-ipv4-address
  2. The Replica "pulls" the data from the "Primary". Well, actually, the Primary simultaneously sends stuff to both its binlog and to each Replica.
  3. The Replica writes the incoming data to its "relay log", from which it reads stuff to apply to its copy of the databases. "Cron" is not invloved.
  4. The Replica may have its own "binlog" -- this is in case it is a step toward further Replicas downstream. They are not identical files between any two servers in the replication topology. They may have the same data, but probably spread among the mysql-bin.* files differently.
  5. Use am expire_log_... setting to get the binlogs purged automatically, rather than building up forever. Set this separately on each server.
4
  • Thank you, Rick. You always answer my questions very well. I understood all items except the #2. The #2 has two sentences that contradict each other. Finally the master "sends" data for "slave"? Or the slave "pulls" changes from the master?
    – Martin AJ
    Aug 4, 2023 at 17:17
  • @Rick James. For the point 1 a good explanation can be found here. I'm guessin master and slave are on the same network so that the connection works. Aug 4, 2023 at 17:34
  • 1
    @MartinAJ - I noticed the contradiction as I wrote it. Perhaps, the Replica has initiated a read that the Primary then pushes into the connection.
    – Rick James
    Aug 4, 2023 at 20:46
  • @ErgestBasha - Thanks.
    – Rick James
    Aug 4, 2023 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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