MySQL High-availability scenario

Highly Available Scenarios MHA

1, master-slave replication scheme selection

Basic Scenario: Mysqldump method

Advanced Scenario: xbackup Backup and restore

2. Possible problems

Network latency, hard disk IO bottlenecks cause slow writes

=============xbackup master-Slave scheme ================

Process:

1, xbackup backup mysql in master Master

2. SCP Transfer backup folder to destination folder

3. Preprocess the backup folder on slave

Using--apply-log/Folder parameters

4. Copy or cut files to slave database data directory

--move-back or--copy-back

5. Set up copy account password and time point

Point in time view: Cat Xbackup_binlog_pos_innodb

Set the permissions account on master:

6. Set the change master configuration

Deferred replication uses:

1. Delete Recovery by mistake

2. For testing

Command: Change master to Master_delay=n; N is the second

The corresponding view parameters are:

The first one is the defined delay time

The second one is the countdown.

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

Synchronization scenarios:

Asynchronous replication (asynchronous replication)

MySQL default replication is asynchronous, the main library after executing the client committed transactions will immediately return the results to the client, do not care whether the library has been received and processed, so there is a problem, if the Lord crash off, at this time the master has committed the transaction may not be uploaded from the top, if at this time, Force will be promoted from the main, may lead to the new master's data is incomplete.

Full synchronous replication (Fully synchronous replication)

When the main library executes a transaction, all of the transactions executed from the library are returned to the client. Because it is necessary to wait for all the transactions from the library to be returned, the performance of full synchronous replication must be severely impacted.

Semi-synchronous replication (semisynchronous replication)

Between asynchronous replication and full synchronous replication, the main library does not immediately return to the client after executing a client-submitted transaction, but waits for at least one receipt from the library and written to the relay log to return to the client. Semi-synchronous replication improves data security relative to asynchronous replication, and it also causes a certain amount of latency, which is at least a TCP/IP round-trip time. Therefore, semi-synchronous replication is best used in low latency networks.

========== Semi-synchronous solution

http://www.cnblogs.com/ivictor/p/5735580.html Reference

MySQL High-availability scenario