Procedure to make high availability for piler

[Rajesh]

Hi,

We are using piler for archiving of mails for more than 2 years , the piler has become critical application in our setup.

We want to setup redundant piler server to avoid data loss.

We followed the following steps

a. Installed Piler application on new server

b.Copied mysqldump from running server to new server

c. Copied (rsync) /var/piler/* directory to new server

d. copied /usr/local/piler/* to new server .

e. Logged on the new server and able to view the mails without issues.

Now when we tried replicating mysql server from old to new using master & slave config.

and rsync the /var/piler/* from old server to new server.

We are not able login to the new piler using [e-mail] but able to login using admin@local

When we checked the mysql slave status it throws the following error

=========================================================================

Last_SQL_Error: Could not execute Delete_rows_v1 event on table piler.sph_index; Can't find record in 'sph_index', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 17472032

===========================================================================

Kindly guide us the right procedure to make redundant server for piler.

Thanks in advance

Rajesh G.L

BEL

[Janos SUTO]

Hello. I think it’s able to achieve some sort of redundancy than HA. The simplest way is to setup two independent nodes, then configure your mail server copy the emails to both locations, ie. archive@node1 and archive@node2.

If you want to stick to the current layout which might work, then fix the replication setup to tolerate an empty sph_index table, or simply exclude this table from the replication.

Another solution might be to not replicating anything anymore, instead export the emails of the previous day on node1, copy it node2 where you import it. It allows much simpler setup having a replica at most 1 day lagging behind.

[Rajesh]

Hi Janos,

Thanks for the reply, We did the following method to bring up the redundant piler server

a. We ran rsync command from working piler server to redundant piler server for /var/piler/* directory.

b. mysqldump was copied from working piler server to redundant piler server.

c. Did rsync of /usr/local/etc/piler from working piler server to redundant piler server.

c. Started piler & searchd services on redundant server , all the mails were displayed correctly.

d. Next day, when we checked on redundant piler server , the /var/piler/sphinx/ size was decreased to 81 MB ( earlier the size was 41GB).

e. We wanted to know how the /var/piler/sphinx/ size was decreased to 81 MB, because of which the mails are not displayed on the redundant piler GUI.

f. Can we do rsync of /var/lib/mysql/piler from working piler server on to the redundant server , guide us any other method to take piler database backup from working piler server to the redundant piler server daily.

‌

[Janos SUTO]

It looks reasonable except f). You shouldn’t rsync raw mysql files while mysqld is running, I don’t think it’s a safe operation.

Regarding e) You must have a cron job other than piler’s running and messing the piler index data. Find it, kill it, then re-rsync the sphinx index files.

[Rajesh]

HI Janos,

Regarding point no f) kindly guide us the best way to replicate the mysql database on redundant server daily.

‌

[Janos SUTO]

“If you want to stick to the current layout which might work, then fix the replication setup to tolerate an empty sph_index table, or simply exclude this table from the replication.”

Or simply use mysqldump and mysql import (as long as your database is not too large).

[Rajesh]

Hi, As suggested by you, we excluded sph_index table from the replication.

This was working fine for 4 days , now we are receiving following error in Mysql Slave server and replication is stopped

"Last_Error: Could not execute Write_rows_v1 event on table piler.audit; Duplicate entry '62817' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000002, end_log_pos 101538175 "

Is replication the right procedure for Mysql piler backup or should we use piler import export tool for backup.

Regards Rajesh G L

[Janos SUTO]

I think I described one possible way how to backup. This time the audit table seems to be corrupted. Try removing the duplicated record from the audit table, and try to resume the mysql replication.