----- On Mar 22, 2016, at 12:07 PM, william drescher will...@techservsys.com 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
