Loading ...
Sorry, an error occurred while loading the content.

Backup MXs and databases

Expand Messages
  • Titanus Eramius
    Hello good folks I have recently brought my very first mailserver online, and have been testing it for the past month or so. Since the setup needs to be
    Message 1 of 8 , Sep 3, 2012
    • 0 Attachment
      Hello good folks

      I have recently brought my very first mailserver online, and have been
      testing it for the past month or so. Since the setup needs to be
      redundant, I have also brought a secondary mailserver online on it's own
      domain, and everything seems to run smoothly.

      It's a Debian, Postfix, Dovecot, postfixAdmin, SQLGrey and
      Squrriel Mail setup, with virtual users only, and MySQL as the central
      component.

      The database is what my question is about. As far as I
      know, the best way to fight of spam and backscatter, is if the backup
      MX uses the same database as the primary.

      I've been reading up on the subject, but seems to lack the experince to
      take a dissicion on what way to keep the backup MX database updated. It
      does not need to be real-time, or anywhere close to it. For this setup
      twice a day will probably be fine.

      So, I guess my question is: How do you, good and experienced folks,
      keep your backup MXs updated?

      I've looked at two solutions so far:
      MySQL Replication, which seems a bit dodgy, with the risk of silent
      data corruption.

      Bash scipting with rsync could do the job, but seems like a less
      clean solution.

      Thank you for your time
    • Reindl Harald
      ... where do you see a risk of silent data corruption? if htis would be the case it would be simply impossible have a omplete dbmail-database running on a
      Message 2 of 8 , Sep 3, 2012
      • 0 Attachment
        Am 03.09.2012 23:56, schrieb Titanus Eramius:
        > Hello good folks
        >
        > I have recently brought my very first mailserver online, and have been
        > testing it for the past month or so. Since the setup needs to be
        > redundant, I have also brought a secondary mailserver online on it's own
        > domain, and everything seems to run smoothly.
        >
        > MySQL Replication, which seems a bit dodgy, with the risk of silent
        > data corruption.

        where do you see a risk of silent data corruption?

        if htis would be the case it would be simply
        impossible have a omplete dbmail-database running
        on a replication salve over 3 years with a lot of
        foreign constraints and a major scheme update

        there is BO silent corruption
        please do not post FUD
      • Andrew Beverley
        ... +1 for mysql replication for backup mail servers. It s been pretty reliable for me. As long as you run some sort of regular process to check that the
        Message 3 of 8 , Sep 3, 2012
        • 0 Attachment
          On Mon, 2012-09-03 at 23:56 +0200, Titanus Eramius wrote:
          > So, I guess my question is: How do you, good and experienced folks,
          > keep your backup MXs updated?
          >
          > I've looked at two solutions so far:
          > MySQL Replication

          +1 for mysql replication for backup mail servers. It's been pretty
          reliable for me. As long as you run some sort of regular process to
          check that the databases are in sync then you'll be fine (see maatkit).

          Andy
        • Titanus Eramius
          On Tue, 04 Sep 2012 00:39:08 +0200 ... I m somewhat sorry for being unable to ask my question in a manner that can t be misunderstood, but I suppose it s
          Message 4 of 8 , Sep 3, 2012
          • 0 Attachment
            On Tue, 04 Sep 2012 00:39:08 +0200
            Reindl Harald <h.reindl@...> wrote:

            > Am 03.09.2012 23:56, schrieb Titanus Eramius:
            > >
            > > MySQL Replication, which seems a bit dodgy, with the risk of silent
            > > data corruption.
            >
            > where do you see a risk of silent data corruption?
            >
            > if htis would be the case it would be simply
            > impossible have a omplete dbmail-database running
            > on a replication salve over 3 years with a lot of
            > foreign constraints and a major scheme update
            >
            > there is BO silent corruption
            > please do not post FUD

            I'm somewhat sorry for being unable to ask my question in a manner that
            can't be misunderstood, but I suppose it's always possible to find
            someting negative if you are looking for it.

            Like I said "I've been reading up on the subject, but seems to lack the
            experince ..." which should be understood as I don't know anything
            about the subject besides what I have read.

            Like this
            http://www.iheavy.com/2012/04/26/bulletproofing-mysql-replications-with-checksums/

            And this
            http://www.xaprb.com/blog/2007/11/08/how-mysql-replication-got-out-of-sync/
            (which I found here http://forums.mysql.com/read.php?27,216438,216438 )

            And so on
            http://www.pythian.com/news/1273/mysql-replication-failures/

            So I'm sorry, I don't see the FUD, and because I know next to nothing
            about databases, I simply can not see these replication errors as
            anything else than corruption*. But please enlighten me, that was why I
            posted to the list.

            ---

            * "Data corruption refers to errors in computer data that occur during
            writing, reading, storage, transmission, or processing, which
            introduce unintended changes to the original data."
            https://en.wikipedia.org/wiki/Data_corruption
          • Reindl Harald
            ... that is not the point ... * mixed transactional and non-transactional tables not relevant in this context why would someone mix innodb/myisam a database
            Message 5 of 8 , Sep 3, 2012
            • 0 Attachment
              Am 04.09.2012 01:15, schrieb Titanus Eramius:
              >> where do you see a risk of silent data corruption?
              >>
              >> if this would be the case it would be simply
              >> impossible have a omplete dbmail-database running
              >> on a replication salve over 3 years with a lot of
              >> foreign constraints and a major scheme update
              >>
              >> there is NO silent corruption
              >> please do not post FUD
              >
              > I'm somewhat sorry for being unable to ask my question in a manner that
              > can't be misunderstood, but I suppose it's always possible to find
              > someting negative if you are looking for it.

              that is not the point

              > Like I said "I've been reading up on the subject, but seems to lack the
              > experince ..." which should be understood as I don't know anything
              > about the subject besides what I have read.
              >
              > Like this
              > http://www.iheavy.com/2012/04/26/bulletproofing-mysql-replications-with-checksums/

              * mixed transactional and non-transactional tables
              not relevant in this context
              why would someone mix innodb/myisam a database and transaction?

              * use of non-deterministic functions such as uuid()
              not relevant in this context

              * stored procedures and functions
              not relevant in this context

              * update with LIMIT clause
              not relevant in this context
              even if, combined with a clear "order by" no problem

              > So I'm sorry, I don't see the FUD, and because I know next to nothing
              > about databases, I simply can not see these replication errors as
              > anything else than corruption*. But please enlighten me, that was why I
              > posted to the list.

              for postfix lookup tables you have usually a very simple
              database scheme with very few changes and 99.9% of all
              queries are readonly because postfix does even not need
              any write permissions to the database (and does not have
              it in any of my setups)

              so you have a simple webinterface for updates or if
              you have only a few domains/users maybe phpMyAdmin or
              terminal would be enough

              so there is virtually zero danger for get out of sync
            • Titanus Eramius
              On Tue, 04 Sep 2012 01:50:42 +0200 ... Thank you very much for the answer, it covers all my questions in full detail. Why someone would use something like
              Message 6 of 8 , Sep 4, 2012
              • 0 Attachment
                On Tue, 04 Sep 2012 01:50:42 +0200
                Reindl Harald <h.reindl@...> wrote:

                > > Like this
                > > http://www.iheavy.com/2012/04/26/bulletproofing-mysql-replications-with-checksums/
                >
                > * mixed transactional and non-transactional tables
                > not relevant in this context
                > why would someone mix innodb/myisam a database and transaction?
                >
                > * use of non-deterministic functions such as uuid()
                > not relevant in this context
                >
                > * stored procedures and functions
                > not relevant in this context
                >
                > * update with LIMIT clause
                > not relevant in this context
                > even if, combined with a clear "order by" no problem
                >
                > for postfix lookup tables you have usually a very simple
                > database scheme with very few changes and 99.9% of all
                > queries are readonly because postfix does even not need
                > any write permissions to the database (and does not have
                > it in any of my setups)
                >
                > so you have a simple webinterface for updates or if
                > you have only a few domains/users maybe phpMyAdmin or
                > terminal would be enough
                >
                > so there is virtually zero danger for get out of sync

                Thank you very much for the answer, it covers all my questions in full
                detail.

                Why someone would use something like uuid() on the primary database and
                then call it an error when it does not work on the slave, I can not
                understand. Thank you for clearing that up to.
              • Reindl Harald
                ... with binlog-format = ROW i can not imagine how even this could be a problem http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html with
                Message 7 of 8 , Sep 4, 2012
                • 0 Attachment
                  Am 04.09.2012 14:07, schrieb Titanus Eramius:
                  >> for postfix lookup tables you have usually a very simple
                  >> database scheme with very few changes and 99.9% of all
                  >> queries are readonly because postfix does even not need
                  >> any write permissions to the database (and does not have
                  >> it in any of my setups)
                  >>
                  >> so you have a simple webinterface for updates or if
                  >> you have only a few domains/users maybe phpMyAdmin or
                  >> terminal would be enough
                  >>
                  >> so there is virtually zero danger for get out of sync
                  >
                  > Thank you very much for the answer, it covers all my questions in full
                  > detail.
                  >
                  > Why someone would use something like uuid() on the primary database and
                  > then call it an error when it does not work on the slave, I can not
                  > understand. Thank you for clearing that up to.

                  with "binlog-format = ROW" i can not imagine how even this
                  could be a problem

                  http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html

                  with statement-based replication it is clear that many things
                  are dangerous, but who is using this really in production?
                • Marcio Merlone
                  ... Even better with a replicated LDAP. Regular sync check also applies. Regards. -- *Marcio Merlone*
                  Message 8 of 8 , Sep 4, 2012
                  • 0 Attachment
                    Em 03-09-2012 20:06, Andrew Beverley escreveu:
                    On Mon, 2012-09-03 at 23:56 +0200, Titanus Eramius wrote:
                    
                    So, I guess my question is: How do you, good and experienced folks,
                    keep your backup MXs updated?
                    
                    I've looked at two solutions so far:
                    MySQL Replication
                    
                    +1 for mysql replication for backup mail servers. It's been pretty
                    reliable for me. As long as you run some sort of regular process to
                    check that the databases are in sync then you'll be fine (see maatkit).
                    
                    Even better with a replicated LDAP. Regular sync check also applies.

                    Regards.

                    --
                    Marcio Merlone
                  Your message has been successfully submitted and would be delivered to recipients shortly.