Синхронизация БД с помощью триггеров (trigger MySQL)
На одном из проектов, над которым я работаю по-ночам )), стала необходимость в перестройке БД (она была денормализована), приведение её к нормальным формам, а также переписыванию скриптов. Останавливать работающий проект низя — он коммерческий.
Как же быть? Точнее — необходимо было держать новую БД актуальной, чтобы когда, все будет готово и оттестировано просто сделать переключение на новую БД и радоваться своей работе.
Я принял такое решение, создать копию БД продакшена, поработать с ней, привести к нормализованному виду и потом синхронизировать. Так я и сделал.
Теперь пришло время подумать как же я буду синхронизировать БД старую и новую? Ведь хочется, что бы при добавлении/редактировании/удалении контента в старой БД, такие же действия происходили с ним в новой БД.
На помощь пришли триггеры MySQL, краткий экскурс что это такое я дал на странице своего блог Работа trigger (триггерами) в MySQL.
И так, есть таблица новостей, в ней есть поля:
mysql> DESCRIBE lib_news; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | dt | int(11) | NO | | 0 | | | title | varchar(250) | NO | | | | | descr | text | NO | | NULL | | | text | text | NO | | NULL | | | source | varchar(250) | NO | | | | | source_url | varchar(250) | NO | | | | | keywords | text | NO | | NULL | | | img1 | varchar(20) | NO | | | | | img2 | varchar(20) | NO | | | | | img3 | varchar(20) | NO | | | | | img4 | varchar(20) | NO | | | | | img5 | varchar(20) | NO | | | | | first | tinyint(1) | NO | | 0 | | | fl | tinyint(1) | NO | MUL | 0 | | | kol | int(11) | NO | | 0 | | | f_yandex | tinyint(1) | NO | | 0 | | | f_lenta | tinyint(1) | YES | | 0 | | | f_lenta_email | tinyint(1) | YES | | 0 | | | comp_tp | int(11) | YES | | 0 | | +---------------+--------------+------+-----+---------+----------------+ 20 rows in set (0.02 sec)
А вот поля из аналогичной таблицы в новой БД, разница в 2-х полях.
поле дата `dt` в старой это INT(11), в новой TIMESTAMP;
поле ИД компании comp_tp INT(11), в новой id_comp_tp SMALLINT(4);
mysql> DESCRIBE urbanus_lib_news; +---------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | id_comp_tp | smallint(4) | NO | | 0 | | | dt | timestamp | NO | | CURRENT_TIMESTAMP | | | title | varchar(250) | YES | | NULL | | | descr | text | YES | | NULL | | | text | text | YES | | NULL | | | source | varchar(250) | YES | | NULL | | | source_url | varchar(250) | YES | | NULL | | | keywords | text | YES | | NULL | | | img1 | varchar(20) | YES | | NULL | | | img2 | varchar(20) | YES | | NULL | | | img3 | varchar(20) | YES | | NULL | | | img4 | varchar(20) | YES | | NULL | | | img5 | varchar(20) | YES | | NULL | | | first | tinyint(1) | NO | | 0 | | | fl | tinyint(1) | NO | MUL | 0 | | | kol | int(11) | NO | | 0 | | | f_yandex | tinyint(1) | NO | | 0 | | | f_lenta | tinyint(1) | NO | | 0 | | | f_lenta_email | tinyint(1) | NO | | 0 | | +---------------+--------------+------+-----+-------------------+----------------+ 20 rows in set (0.02 sec)
Получается, что при синхронизации таблиц, необходимо еще и приводить поля к нужному виду.
Ниже привожу триггеры которые и делают синхронизацию.
DROP TRIGGER IF EXISTS `lib_news_ai`;; CREATE TRIGGER `lib_news_ai` AFTER INSERT ON `lib_news` FOR EACH ROW BEGIN INSERT INTO `newdb`.`new_lib_news` SET `id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`, `descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`, `keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`, `img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`, `f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email` ON DUPLICATE KEY UPDATE `id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`, `descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`, `keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`, `img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`, `f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`; END;; DROP TRIGGER IF EXISTS `lib_news_au`;; CREATE TRIGGER `lib_news_au` AFTER UPDATE ON `lib_news` FOR EACH ROW BEGIN INSERT INTO `newbd`.`new_lib_news` SET `id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`, `descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`, `keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`, `img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`, `f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email` ON DUPLICATE KEY UPDATE `id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`, `descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`, `keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`, `img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`, `f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`; END;; DROP TRIGGER IF EXISTS `lib_news_ad`;; CREATE TRIGGER `lib_news_ad` AFTER DELETE ON `lib_news` FOR EACH ROW BEGIN DELETE FROM `newbd`.`new_lib_news` WHERE `id` = OLD.id; END;;
Как видно из св-в триггеров, любая новая запись в старой БД таблицы новостей будет записана в новую БД с изменениями которые необходимы. При редактировании также происходит обновление записей в 2-х БД. При удалении — удаление, соответственно.
Может возникнуть вопрос, почему я не использую простые запросы INSERT INTO или UPDATE. Ответ прост, вдруг по каким-то загадочным причинам в новой БД будет существовать запись с таким же ИД, который будет добавляться со старой БД, возникнет ошибка и ничего не будет, а используя конструкцию INSERT INTO ….. ON DUPLICATE KEY UPDATE (можно об этом почитать тут) я избегаю ошибки, даже если запись с таким же ИД есть, она будет обновлена. Или наоборот записи в новой БД нет, а в старой сделали исправление записи, следовательно эта запись добавится в новую БД.
Еще может возникнуть вопрос, а как же те записи, которые были добавлены в старую БД в момент реконструкции новой? Ну этот вопрос я не буду рассматривать в данной статье, об этом я еще не думал, но скорее всего этим займутся или EVENT или PROCEDURE.
Всем удачи.
`newdb`.`new_lib_news`
Статья просмотренна 54205 раз, зашло посетителей 17448
Хм, используя триггеры никогда не приходилось работать с несколькими базами. Интересное решение!
то есть таки можно триггером данные вставлять между двумя базами, это очень хорошо, сейчас попробую
Это актуально если БД с которой синхронизируешься находится на одинаковом ай-пи и связке логин/пароль