Тема: Mysql - синхронизируем записи с разеых серверов.

Может комуто пригадится, система управления сервером PLESK...

Проблема: на серверах стоит qmail поэтому решил поставить перед ними postfix, но есть нюансы, серверов много, иногда они загружены smile надо для постфикса отдельно базу данных делать, но тут опять проблема, пользователей тысячи, надо синхронизировать...

решение вижу в FEDERATED таблицах. Пробовал поставить Mysql 5,1 но там по какойто причине поддержка этого типа таблиц выкинута из порта, нехочу щас вникать в чем дело, поставил 5,0.

Создаем таблицу которая покажет мне в моем постфиксовом мускуле данные с удаленного серврера:

CREATE TABLE `accounts` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `type` varchar(32) character set ascii NOT NULL default 'plain',
  `password` text character set ascii collate ascii_bin,
  PRIMARY KEY  (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://user:pass@host/psa/accounts';
CREATE TABLE `domains` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `cr_date` date default NULL,
  `name` varchar(255) character set ascii NOT NULL,
  `displayName` varchar(255) character set utf8 NOT NULL,
  `dns_zone_id` int(10) unsigned NOT NULL,
  `status` bigint(20) unsigned NOT NULL default '0',
  `htype` enum('none','vrt_hst','std_fwd','frm_fwd') NOT NULL,
  `real_size` bigint(20) unsigned default '0',
  `cl_id` int(10) unsigned NOT NULL,
  `cert_rep_id` int(10) unsigned default NULL,
  `limits_id` int(10) unsigned default NULL,
  `params_id` int(10) unsigned default NULL,
  `guid` varchar(36) character set ascii collate ascii_bin NOT NULL default '00000000-0000-0000-0000-000000000000',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `displayName` (`displayName`),
  KEY `cl_id` (`cl_id`),
  KEY `cert_rep_id` (`cert_rep_id`),
  KEY `limits_id` (`limits_id`),
  KEY `params_id` (`params_id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://user:pass@host:3306/psa/domains';
CREATE TABLE `mail` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `mail_name` varchar(245) character set ascii NOT NULL,
  `perm_id` int(10) unsigned NOT NULL,
  `postbox` enum('false','true') NOT NULL default 'false',
  `account_id` int(10) unsigned NOT NULL,
  `redirect` enum('false','true') NOT NULL default 'false',
  `redir_addr` varchar(255) character set utf8 default NULL,
  `mail_group` enum('false','true') NOT NULL default 'false',
  `autoresponder` enum('false','true') NOT NULL default 'false',
  `spamfilter` enum('false','true') NOT NULL default 'false',
  `virusfilter` enum('none','incoming','outgoing','any') NOT NULL default 'none',
  `mbox_quota` bigint(20) NOT NULL default '-1',
  `dom_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `dom_id` (`dom_id`,`mail_name`),
  KEY `account_id` (`account_id`),
  KEY `perm_id` (`perm_id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://user:pass@host:3306/psa/mail';

теперь у меня есть все что мне нужно. Можно делать select из любой таблици и вытаскивать записи, первая занимает некоторое время, остальный быстро, так как удаленный сервер все кеширует. надо сформировать вывод понятный постфиксу, это mail:pass, для того чтобы не использовать километровый запрос создает VIEW

create view view_users as select
 concat_ws('@',mail.mail_name,domains.name) as mailbox,
 accounts.password as password
from
 domains,
 mail,
 accounts
where
 domains.id=mail.dom_id and
 accounts.id=mail.account_id;

Вот, теперь делая select * from view_users; получаю список всех майлов и паролей, постфикс их примит для SMTP авторизации через SASL.

НО! при моей нагрузке в час пик при работе нескольких серверов (количество растет постоянно) у меня чтонибудь точно загнется. Поэтому делаем локальную копию пользователей:

Копия view
create table users as select * from view_users where 1=0;
Для того чтобы записи не дублировались
create UNIQUE index users_unique on users (mailbox);
добавляем и синхронизирует записи
replace into users select * from view_users;

и переодически, раз в 10 минут можно пускать процедурку, для синхронизации данных

delimiter //
drop procedure if exists update_from_plesk //
create procedure update_from_plesk()
deterministic
begin
    declare x int;
    declare plesk_alive boolean default true;
    declare curx cursor for select id from plesk.mail;
    declare continue handler for SQLSTATE 'HY000' set plesk_alive = false;
    open curx;
    -- fetch curx into x;
    if (plesk_alive) then
        replace into plesk.users select * from plesk.view_users;
    end if;
end //
delimiter;

Но опять проблема, replace не удаляет записи, только обновляет и добавляет, не хорошо, дропнуть данные нельзя так как получается время когда таблица недоступна, можно конечно и LOCK READ сделвть, но таймаут в 1-2 минуты мало кому понравится, поетому каждую ноч надо пускать еще одно действие, lock read, drop все нафиг и call update_from_plesk;

идеи/предлажения/дополнения принимаются...

2

Re: Mysql - синхронизируем записи с разеых серверов.

все это мне не очень нравится... Во первых сложно, во вторых без нужных знаний отдельного админа не обойтись.
Если все так загружено, у постфикса есть возможность распределения нагрузки через дополнительные postfix-сервера. Или в крайнем случае, поднять базу отдельно а постфикс отдельно на хороших машинах. решения всегда есть.

3

Re: Mysql - синхронизируем записи с разеых серверов.

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

А вообще есть и спец по постфиксу и по мускулу... конечные сервера нельзя перенастраивать, вот где проблема, вот и выкручиваемся как можем. А сложного особо ничего нет, надо тока въехать в принцип.

Задача простая, альтернативный SMTP для всех клиентов, само сабой с авторизацией... Предлажи лучше систему, рассмотрим. Есть только 2 маленьких условия:

1. DNS зоны менять нельзя (на доменах клиентов, доменов около 10000)
2. Сервера перенастраивать нельзя (там Qmail)

4

Re: Mysql - синхронизируем записи с разеых серверов.

ак у вас все жестоко smile

5

Re: Mysql - синхронизируем записи с разеых серверов.

В MySql есть NDB Engine (тип таблиц). Я настраивал ее, и работал даже но в последствии купил более мощный сервер и перенес все на один. Но есть недостатки, на которые я сам напоролся и даже загасил базу. Хорошо что всегд делаются копии...
Нужно очень конкретно понимать что и как все происходит между кластерами. Так как одно неверное движение, и базу можно просто потерять. Для этого нужно держать ответственного человека, чтобы он только и делал что следил за базами и не отвелкался от этого. Иначе там не  выйдет так, как мы привыкли говорить "Настроил и забыл".
Была еще идея - создать отдельный массив и загружаться с него остальным серверам(MySql) к примеру через тот же ISCSI или NFS монтируя систему. Вот если так получится, то это было бы не плохо. Но нужно большое вложение денег. Я до этого не дорос, но возможно на следующий год начну думать об этом серьезней. Самому нужно для MyIsam таблиц такую вещь как Кластер.

6

Re: Mysql - синхронизируем записи с разеых серверов.

Нет, нфс в базе не хорошо... Щас смотрю как альтернатива в сторону имплиментаций и сравниваю скорости. Разница в том, что система с федеральными таблицами не держет постоянного конекта с базами, а из за этого данные немного отстают, в моем случае 10 минут, а при созданий имплиментаций постоянно весит конект и при изменении сообщается что поменялось, если конект разрывается - используется последняя ссинхронизированая копия.

Попожа напишу как настраивается. Еще раз скажу что в моем случае максимум что я могу, это создать пользователя на удаленном сервере, никакой костомизации, а то софт потом необновить, он и так глючный а если еще и поковырять его вообще загибается sad