Mysql Отложенная репликация или Коммивояжеры с Mysql-slave

Недавно мне поставили такую задачу: Есть много коммивояжеров с ноутбуками которые разъезжают по стране и что-то кому-то впаривают продают.
Т.к. им нужны актуальные данные о наличии товара и ценах, время от времени они подключаются к центральному серверу через интернет и сливают себе обновленные данные.

Условия к задаче:
— Частота и периодичность выхода на связь коммивояжеров неизвестна, ровно как и дилтельность.
— Должно все работать максимально надежно, потому как коммивояжеры они такие «коммивояжеры».
— Решение должно быть на базе Mysql master-slave replication.

Выводы из условия:

— Для надежности, на стороне клиента(slave) минимум настроек, никаких скриптов по крону, все должно быть внутри mysql.
— Т.к. неизвестно когда и с какой периодичностью будут подключаться к master базе коммивояжеры, binlog на мастере нужно хранить так долго пока все slave не скачают его себе.

Решение:
— Информировать мастер, какие slave и сколько уже «скачали». А точнее в какой позиции самый «ленивый» slave.
— Все остальные binlog можно смело удалять.

Задача свелась к тому, что slave как то должен информировать мастер, в каком месте slave его уже догнал.

Точнее имеем задачи:
1. На slave узнать а в какой позиции мы сейчас применяем binlog.
2. Cообщить об этом как то мастеру.
3. На мастере удалить ненужные файлы binlog.

Т.к. никакие внешние скрипты запускать нельзя(по условиям задачи). Остаются хранимые процедуры.
Никакие поля из «show slave status» недоступны в хранимых процедурах… что же делать…

Создаем на мастере в реплицируемой базе табличку и раз в минуту пишем туда timestamp.
Эти изменения пишутся в binlog, binlog рано или поздно доезжают до slave и комитися. На slvae прочитав из этой таблицы значение мы знаем до какого времени мы догнали мастер 🙂

Итак создаем на мастере (!!! в реплицируемой таблице) и вставим 1 запись:

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(); 

и вешаем на него событие(имя БД поправьте):

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

Все теперь каждую минуту поле master_tick в таблице будет обновляться и это будет записываться в binlog.

да не забываем добавить в my.cnf

event_scheduler=1 

а чтоб не перегружаться:

SET GLOBAL event_scheduler=ON; 

Хорошо, теперь на slave мы знаем насколько мы догнали мастер и надо как-то проинформировать об этом мастер.

Со slave на мастер писать, средствами MYSQL не используя внешние скрипты можно только через «локальную» таблицу ENGINE=FEDERATE;

Самый простой способ на slave по event писать в FEDERATE таблицу которая расположена на мастере.

Создадим на master таблицу в НЕ реплицируемой базе,

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

На slave подключимся к ней:

 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' 

На slave создадим процедуру копирования времени тз таблицы master_tick в slaves, не забудем указать server_id, чтоб на мастере понимать кто из slave в какой позиции.

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 

На slave добавим EVENT:

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

Все.

Остается только последнее, на master удалять ненужные логи.
Запускаем по крону Bash script:

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

P.S. Возможно это будет работать только если binlog_format = ‘ROW’;