MySQL Master-slave replication Rollup

Binlog Log

Binary log format

Statement -based statement

Row rows based

Mixed Mode mixed

Binary Log Events

Location-based postion based on time Datetime-timestamp

The replication process has a very important limitation--replication is serialized on slave, meaning that concurrent update operations on Master cannot operate concurrently on slave. So the data on the slave is generally slower than the data on master. That is, the data between master and slave will be out of sync for a certain amount of time.

synchronous, asynchronous, semi-synchronous replication

Synchronous

Synchronous replication can be defined as data being submitted to one or more machines at the same time usually this is done through a well-known "two-phase commit".

Asynchronous

The main library does not Tabeku the progress after performing some transactions. If the repository is unfortunate and more unfortunate is that the main library is now crash such as downtime then the data in the repository is incomplete. In short, we cannot use a standby to continue to provide data-consistent services in the event of a failure of the main library.

Semi-synchronous

There should be at least one slave to turn on the function of the semi-synchronous copy on master.

At this point, a thread commits a transaction on master that will be blocked until it learns that a slave that has been turned on for semi-synchronous replication has received all events for this transaction or waits for a timeout.

MySQL Semi-synchronous replication

Show global variables like ' have_dynamic_loading ';

INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

Show global variables like ' plugin_dir ';

SELECT * from Information_schema.plugins; or show plugins;

SET GLOBAL rpl_semi_sync_master_enabled = 1;

SET GLOBAL rpl_semi_sync_master_timeout = 60;

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

Start slave;

show slave status;

Multi-source Replication

STOP SLAVE;

SET GLOBAL master_info_repository = ' TABLE ';

SET GLOBAL relay_log_info_repository = ' TABLE ';

Slave > Change Master to master_host= "127.0.0.1", master_port=20121,

Master_user= "Multi", master_password= "multi" for CHANNEL ' Master1 ';

Slave > Change Master to master_host= "127.0.0.1", master_port=20122,

Master_user= "Multi", master_password= "multi" for CHANNEL ' Master2 ';

Slave > Change Master to master_host= "127.0.0.1", master_port=20123,

Master_user= "Multi", master_password= "multi" for CHANNEL ' Master3 ';

Start slave for channel ' Master1 ';

Start slave for channel ' Master2 ';

Show slave status for Channel ' Master1 ';

Show slave status for Channel ' Master2 ';

GTID

Gtid is the global transaction ID (transaction identifier)

GTID = source_id:transaction_id

Eg:3e11fa47-71ca-11e1-9e33-c80aa9429562:50

Each of the GTID represents a database transaction

Open Gtid

Set global enforce_gtid_consistency=on;

Set global log_slave_updates=on;

Set global gtid_mode=off_permissive;

Set global gtid_mode=on_permissive;

Set global gtid_mode=on;

Using Gtid to establish Master-slave

mysql> Change Master to master_host= ' 1.1.1.1 ', Master_port =3306,master_user= ' Gtid ', master_password= ' Gtid ', master _auto_position=1;

MySQL parallel replication

This article is from "Allen's personal blog" blog, please be sure to keep this source http://allen2288.blog.51cto.com/1184612/1767068

MySQL Master-slave replication Rollup