Perhaps I am misreading this, but I am having difficulty understanding how your requirement is any different from built-in MySQL replication.
With MySQL replication, the master database writes changes to a binary log file. The slaves connect to the master, read the binary logs, write them to their local relay log, and play the statements back.
Please understand that realtime copy and backup are not the same thing. A real-time slave can help you recover from certain types of problems, but cannot resolve data-corruption issues (oops, I dropped that table!). For that, you need point-in-time backups. With a backup and a copy of the binary logs, you can recover from almost any type of failure by replaying the binary logs up to the point where the problem occurred.
Your First Question
why does a OPTIMIZE query block the replication catching up ?
The SQL thread in MySQL Replication processes SQL commands from the Relay Logs as a FIFO queue (i.e. first-come, first-server basis). Once the SQL thread see a command, it processes it to completion. Meanwhile, the Seconds_Behind_Master just keeps creeping up.
While the SQL thread on the Slave is running OPTIMIZE TABLE, once you run STOP SLAVE; it SQL thread has complete the running OPTIMIZE TABLE before the SQL thread can terminate. That's why STOP SLAVE; hangs. To verify this, open another mysql session and run SHOW PROCESSLIST;, You should see a line that has OPTIMIZE TABLE with the status Killing slave....
Your Second Question
can I prevent those OPTIMIZE queries ? I didn't put any cron to do that, and as far as I can see, nothing in /etc/cron.{d,daily,hourly,weekly} does this.
Someone or something is running the OPTIMIZE TABLE. You have to hunt it down in all your jobs or PHP apps. Use the mysqlbinlog utility to dumps all the binary logs into a text file. Then, grep the text file for OPTIMIZE TABLE or optimize table. The timestamp of when it was submitted into the binlogs will be a few lines about the command.
When you do find that OPTIMIZE TABLE command in your jobs or PHP code, simply run this:
SET SQL_LOG_BIN=0; OPTIMIZE TABLE tblname; SET SQL_LOG_BIN=1;
This will not register the OPTIMIZE TABLE in the binlogs, thus preventing a Slave from replicating it.
As was just pointed out in the comments below, You can also replace
OPTIMIZE TABLE tblname;
with one of the following:
OPTIMIZE NO_WRITE_TO_BINLOG TABLE tblname;
OPTIMIZE LOCAL TABLE tblname;
to restrict the OPTIMIZE TABLE command from landing in the binlogs and thus prevent replication of it.
Best Answer
It depends on the version.
Look for "multi-source" replication. This is where multiple Primaries can replicate to a single Replica. But this does not say how you would restore a single Primary that dies.
Consider using a single Primary for everything, then have one (or more) Replicas.
Or you could have multiple Primary-Replica pairs sitting in VMs or Dockers. The pair would, of course, have its parts on separate machines, preferably geographically separated (think storms, earthquakes, etc).