Re: need help from the list admin

On 3/22/2016 7:49 AM, Lentes, Bernd wrote:
>
>
> ----- On Mar 22, 2016, at 12:07 PM, william drescher [email protected] 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

Yes, in the original document there were some characters that 
were put on the screen as asian pictograph characters.  I 
replaced them with periods:
I have bought a book
about HA: "..." from Oliver Liebel

And I found the same characters in your sig and removed them.

bill



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