Mysql Delayed Replication or Salespeople with Mysql-slave

Recently, I was given the following task: There are many traveling salesmen with laptops who are traveling around the country and are selling something to someone.
Since they need up-to-date data on product availability and prices, from time to time they connect to the central server via the Internet and merge their updated data.

Conditions for the task:
- The frequency and frequency of contacting traveling salesmen is unknown, just like the duration.
- Everything should work as reliably as possible, because salespeople are such “salespeople”.
- The solution should be based on Mysql master-slave replication.

Conclusions from the condition:
')
- For reliability, on the client side (slave) a minimum of settings, no scripts on the crown, everything must be inside mysql.
- Since it is not known when and how often the salesman will connect to the master base, the binlog on the master must be stored for as long as all the slaves do not download it themselves.

Decision:
- Inform the master, which slave and how many have already “downloaded”. Or rather, in what position is the “laziest” slave.
- All other binlog can be safely removed.



The task has been reduced to the fact that the slave, as the master must inform, in which place the slave has already caught up with him.

More precisely, we have tasks:
1. On the slave, find out in which position we are now using binlog.
2. Report it as a master.
3. On the wizard, delete unnecessary binlog files.

Since no external scripts can be run (under the terms of the task). Remains stored procedures.
No fields from show slave status are available in stored procedures ... what to do ...

We create a table on the master in the replicated database and once a minute we write there a timestamp.
These changes are written to binlog, binlog sooner or later arrive at the slave and commit. On the slave, after reading the value from this table, we know how long we caught up with the master :)



So, create on the master (!!! in the replicated table) and insert 1 record:

CREATE TABLE `master_tick` ( `id` mediumint(9) NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ); INSERT INTO master_tick SET time=NOW(); 

and hang an event on it (correct database name):

 CREATE EVENT master_tick ON SCHEDULE EVERY 1 MINUTE DO UPDATE my_db.master_tick set time=NOW(); 

All now every minute the master_tick field in the table will be updated and it will be recorded in the binlog.

yes do not forget to add in my.cnf

 event_scheduler=1 

but not to be overloaded:

 SET GLOBAL event_scheduler=ON; 

Well, now on the slave we know how much we caught up with the master and we need to somehow inform the master about this.

From the slave to the master to write, using MYSQL without using external scripts is possible only through the “local” table ENGINE = FEDERATE;

The easiest way to write an event on a slave is to write a table in the FEDERATE which is located on the master.



Create a master table in a NOT replicable database,

 CREATE TABLE `slaves` ( `server_id` int(11) NOT NULL, `master_tick_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `server_id` (`server_id`) ) 

On the slave, connect to it:

  CREATE TABLE `slaves` ( `slave_server_id` int(11) NOT NULL, `master_tick_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `server_id` (`server_id`) ) ENGINE=FEDERATED CONNECTION='mysql://user:passwd@mysql_master.mydomain.com:3306/test/slaves' 

On the slave, we will create a time copying procedure from the master_tick table in the slaves; let's not forget to specify server_id, so that the wizard understands who the slave is in which position.

 CREATE PROCEDURE `copy_master_tick_time`() BEGIN DECLARE master_tick_time timestamp; SET @master_tick_time = (select time from my_db.master_tick); DELETE FROM test.slaves where server_id=@@server_id; INSERT INTO test.slaves set server_id=@@server_id, master_tick_time=@master_tick_time; END 

Add EVENT to the slave :

 CREATE EVENT `copy_master_tick` ON SCHEDULE EVERY 30 SECOND STARTS '2013-01-01 00:00:00' DO call copy_master_tick_time() 

Everything.

It remains only the last, on the master to delete unnecessary logs.
Run the cron Bash script:

 #!/bin/bash a=`mysql -e "select MIN(master_tick_time) from slaves;"` mysql -e "PURGE BINARY LOGS BEFORE $a" 

PS Perhaps this will only work if binlog_format = 'ROW';