Web lists-archives.com

Re: need help from the list admin





----- On Mar 22, 2016, at 12:07 PM, william drescher william@xxxxxxxxxxxxxxx wrote:

> sent for Bernd, and to see if it works from another sender
> ----------------------------------------------
>  Lentes, Bernd wrote:
> Hi,
> 
> i know that there is a list dedicated to replication, but when
> you have a look in the archive it's nearly complete empty. Really
> not busy.
> So i hope it's ok if i ask here.
> we have a web app which runs a MySQL DB and dynamic webpages with
> perl and apache httpd. Webpages serve reading and writing into
> the db. The db is important for our own work flow, so i'd like to
> make it HA. I have two HP servers and will use SLES 11 SP4 64bit
> as OS. MySQL is 5.5.47. For HA i'd like to use pacemaker, which
> is available in SLES High Availibility Extension. I have
> experience in linux, but i'm not a database administrator nor
> developer. HA is important for us, we don't have performance
> problems.
> My first idea was to run the web app and the db in a virtual
> machine on the host and in case of a failure of one host
> pacemaker would run the vm on the other host. VM would be stored
> on a FC SAN. I stopped following this idea. I have bought a book
> about HA: "..." from Oliver Liebel. It's only available in
> german. But i can recommend it, it's very detailed and well
> explained.
> He proposed to have two hosts, and on each is running a MySQL
> instance as master AND slave. But it's not a "real multi master
> solution", because pacemaker takes care that the IP for the web
> app just points to one master. So i don't have the multi-master
> problems with concurrent inserts (i believe).
> His idea is that host A is master for the slave on host B, and
> host B is the master for the slave on host A. OK ?
> Let's imagining that the IP to the web app points to host A,
> inserts are done to the master on host A and replicated to the
> slave on host B. Now host A has problems, pacemaker redirects the
> IP to host B, and everything should be fine.
> What do you think about this setup ? Where is the advantage to a
> "classical Master/Slave Replication" ? How should i configure
> log-slave-updates in this scenario ?
> Let's imagine i have two hosts again: Host A is master, host B is
> slave. Nothing else. No real or pseudo "Multi-Master". IP points
> to host A. Host A has problems, pacemaker recognizes it, promotes
> B to master and pivot the IP. Everything should be fine. Where is
> the disadvantage of this setup compared to the "Multi-Master
> Replication" in the book ? The OCF ressource agent for mysql
> should be able to handle the mysql stuff and the RA for the IP
> pivots the IP.
> 
> Now some dedicated questions to replication. I read a lot in the
> official documentation, but some things are not clear to me.
> In our db we have MyISAM and InnoDB tables.
> 
> From what i read i'd prefer row based replication. The doc says
> is the safest approach. But there seems to be still some problems:
> 
> The doc says: "For tables using the MYISAM storage engine, a
> stronger lock is required on the slave for INSERT statements when
> applying them as row-based events to the binary log than when
> applying them as statements. This means that concurrent inserts
> on MyISAM tables are not supported when using row-based
> replication."
> What does this exactly mean ? Concurrent inserts in MyISAM-tables
> are not possible if using RBL ? Or unsafe in the meaning they
> create inconsistencies ?
> 
> "RBL (Row Based Logging) and synchronization of nontransactional
> tables. When many rows are affected, the set of changes is split
> into several events; when the statement commits, all of these
> events are written to the binary log. When executing on the
> slave, a table lock is taken on all tables involved, and then
> the rows are applied in batch mode. (This may or may not be
> effective, depending on the engine used for the slave抯 copy of
> the table.)"
> What does that mean ? Effective ? Is it creating inconsistencies
> ? Or just not effective in the sense of slow or inconvinient ?
> 
> Or should i prefer MIXED for binlog_format ?
> 
> The doc says: " If a statement is logged by row and the session
> that executed the statement has any temporary tables, logging by
> row is used for all subsequent statements (except for those
> accessing temporary tables) until all temporary tables in use by
> that session are dropped.
> This is true whether or not any temporary tables are actually
> logged. Temporary tables cannot be logged using row-based format;
> thus, once row-based logging is used, all subsequent statements
> using that table are unsafe. The server approximates this
> condition by treating all statements executed during the session
> as unsafe until the session no longer holds any temporary tables."
> What does that mean ? Unsafe ? Causing inconsistencies ? Problem
> with SBL or RBL ?
> 
> The doc says: "Due to concurrency issues, a slave can become
> inconsistent when a transaction contains updates to both
> transactional and nontransactional tables. MySQL tries to
> preserve causality among these statements by writing
> nontransactional statements to the transaction cache, which is
> flushed upon commit. However, problems arise when modifications
> done to nontransactional tables on behalf of a transaction become
> immediately visible to other connections because these changes
> may not be written immediately into the binary log.
> Beginning with MySQL 5.5.2, the
> binlog_direct_non_transactional_updates variable offers one
> possible workaround to this issue. By default, this variable is
> disabled. Enabling binlog_direct_non_transactional_updates causes
> updates to nontransactional tables to be written directly to the
> binary log, rather than to the transaction cache.
> binlog_direct_non_transactional_updates works only for statements
> that are replicated using the statement-based binary logging
> format; that is, it works only when the value of binlog_format is
> STATEMENT, or when binlog_format is MIXED and a given statement
> is being replicated using the statement-based format. This
> variable has no effect when the binary log format is ROW, or when
> binlog_format is set to MIXED and a given statement is replicated
> using the row-based format.
> Important:
> Before enabling this variable, you must make certain that there
> are no dependencies between transactional and nontransactional
> tables; an example of such a dependency would be the statement
> INSERT INTO myisam_table SELECT * FROM innodb_table. Otherwise,
> such statements are likely to cause the slave to diverge from the
> master."
> Does that mean that "Due to concurrency issues, a slave can
> become inconsistent when a transaction contains updates to both
> transactional and nontransactional tables" is also a problem for
> RBL ?
> "... you must make certain that there are no dependencies between
> transactional and nontransactional tables; an example of such a
> dependency would be the statement INSERT INTO myisam_table SELECT
> * FROM innodb_table. Otherwise, such statements are likely to
> cause the slave to diverge from the master."
> Are these statements problems when using RBL ?
> 
> Thanks for any answer.
> 
> 
> Bernd
> 

Hi William,

thanks for the try. Good idea !
Did you change anything ?


Bernd


Helmholtz Zentrum Muenchen
Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH)
Ingolstaedter Landstr. 1
85764 Neuherberg
www.helmholtz-muenchen.de
Aufsichtsratsvorsitzende: MinDir'in Baerbel Brumme-Bothe
Geschaeftsfuehrer: Prof. Dr. Guenther Wess, Dr. Alfons Enhsen, Renate Schlusen (komm.)
Registergericht: Amtsgericht Muenchen HRB 6466
USt-IdNr: DE 129521671


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql