MySQL does not limit the number of slaves that you can connect to the master server in a replication topology. However, as the number of slaves increases, they will have a toll on the master resources because the binary logs will need to be served to different slaves working at different speeds. If the data churn on the master is high, the serving of binary logs alone could saturate the network interface of the master.

A classic solution for this problem is to deploy a binlog server – an intermediate proxy server that sits between the master and its slaves. The binlog server is set up as a slave to the master, and in turn, acts as a master to the original set of slaves. It receives binary log events from the master, does not apply these events, but serves them to all the other slaves. This way, the load on the master is tremendously reduced, and at the same time, the binlog server serves …

There are times when you may be rebuilding a DB server by replaying the binary logs using the mysqlbinlog utility. Extracting CRUD statements and DDL is relatively straightforward, but not for statements like LOAD DATA INFILE. The actual data file is embedded within the binary log, and not very visible to the naked eye. But there is an easy way to decipher the binary log and extract the file to load manually.

As an example, I have taken a simple text file of numbers and loaded it into a fictitious table abc using the LOAD DATA LOCAL INFILE command. To see where in the binary log that command would reside, the mysqlbinlog utility is used:

$ mysqlbinlog mysqld-bin.000003 | grep -i -B7 "load data"

# at 174
#100921 21:42:10 server id 1136902037  end_log_pos 218
#Begin_load_query: file_id: 1  block_len: 21
# at 218
#100921 21:42:10 …