MySQL Replicationation Advanced

Summary

Previous: MySQL Replication foundation next a MySQL REPLICATION-MHA

First, master replication

Second, semi-synchronous replication

Third, copy filter

Iv. Summary

V. Segmentation

Cond

First, master replication

　MySQL master replication by mutual primary never implemented, at this time load Balancer Scheduler is easier to implement, but because many servers are master, but there are serious problems, need to use cautiously:
1. Data inconsistency problem, can not be resolved, see Paxos algorithm description for details
2. Auto_increment ID Problem

How to solve? Take 2 units For example, one with an odd number, one with an even number

Configuration steps:

(1) Each node uses a unique server_id

(2) both binary and relay logs are started

(3) Create a user account with copy rights

(4) Define the auto-Grow ID field with a numeric range of odd and even numbers

(5) Mutual master from, both start the replication thread
　　

The demo is as follows:

　　(1) Master and slave Modify the configuration, mainly the following parameters
　　

log-bin=master-Binrelay_log=Relay_logauto_increment_offset=2Auto_increment_ Increment=2server_id=1

　　(2) both master and slave create accounts with copy rights

GRANT REPLICATION REPLICATION  on *. *  to ' Repl '@'192.168.1.%'by'111111' ;

Note: The permissions for replication are very large and cannot be used with the above wildcard character, preferably single IP authorization

　(3) Mutual master from

 to Master_host=' 192.168.1.202', master_user='repl ', Master_password='111111', master_log_file= ' master-bin.000003 ', master_pos=506; START SLAVE;

At this point, you can write on both servers, note that primary master replication has a lot of details, unless the data is not important, it is not recommended.

Second, semi-synchronous replication

Master must wait at least one policy that slave responds to

Plug-ins developed by Google, so to see whether semisync_master.so and semisync_slave.so are supported by the current database, the general mariadb is installed by default

Configuration steps:

　　　　(1) Each node uses a unique server_id

(2) Master initiates the binary log, slave the relay log

(3) master creates a user account with copy rights

(4) Connect master and slave in a semi-synchronous manner

Install plug-in mode: Help install

INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so '

INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so '

The demo is as follows:

(1) Master configuration, install plugin　　

Log -Bin=Master-binlog-bin-index= Master-bin-indexserver-ID       =1

Start MySQL:

' semisync_master.so ';

GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to ' repl ' @ ' 192.168.1.% ' identified by ' 111111 ';

(2) slave configuration, install plug-in

Relay-log-index=slave-relay-bin. Index Relay - Log =slave-relay-binserver-ID       =2

Start MySQL:

' semisync_slave.so '

(3) SHOW GLOBAL VARIABLES like '%semi% ';

On Master

set global rpl_semi_sync_master_enabled='on';

(4) SHOW GLOBAL STATUS like '%semi% ';

Slave on

set global rpl_semi_sync_slave_enabled='on';

(5) Connect SLAVE to MASTER

　

Third, copy filter

To copy a specified database from a node only, or specify a table in the specified database

Implementation method:

(1) The primary server only logs events related to a specific database to the binary log, and it is not recommended to use

Binlog_do_db:binlog white list, separated by commas

Binlog_ignore_db:binlog's blacklist

(2) from the server Sql_thread in the replay relay log event, only read the specific database (specific table) related events and apply to the local;

Problem: Synchronizing unnecessary data can result in a waste of network and disk IO

Replicate_do_db:replay White List

Blacklist when Replicate_ignore_db:replay

　　　　

Iv. Summary

MySQL's distributed system satisfies the CAP principle, and the APS compromise on a. Note that MySQL replication only shares the read operation, regardless of the master slave and the dual master model, there is no allocation of write operations.

Problem:

　　1. How can I restrict the server from being read-only?

(1) The first can be set on the slave read_only=on; However, this restriction is not valid for users with super privileges

(2) Block all users: Apply a global read lock

Mysql> FLUSH TABLES with READ LOCK;

　　2. How do I guarantee the security of transactions from replication?

Master: No memory buffer is used for some IO operations, and each operation is brushed to the hard disk.

Sync_binlog=on must

Innodb_flush_logs_at_trx_commit=on

Innodb_support_xa=on

Sync_master_info=on Optional

On slave:

Skip_slave_start=on

Sync_relay_log_info Optional

Sync_relay_log Optional

Files related to copy function:

Master.info: Used to save slave connection to master information, such as account number, password, server address, etc.

Relay_log.info: Saves the current binary log and local repay log log information that has been replicated on the current slave node

Maintain and monitor common commands:
(1) How to clean up the log?

Url:https://mariadb.com/kb/en/SQL-commands-purge- logs/BINARYto'mysql-bin.010'  BINARY'2008-04-02 22:46:26';

(2) Replication Monitoring

BINARY　　LOGS;　　SHOW BINLOG EVENTS; SHOW processlist;

(3) Whether the server is lagging behind the primary server

Field seconds_behind_master:0 information in SHOW SLAVE status

(4) How to determine whether the master-slave node data is consistent;

Special tools are needed, such as in Percona-tools.

(5) Inconsistent data?

Choose a re-copy, the data should have been lost, can only be manually judged.

V. Segmentation

http://haitian299.github.io/2016/05/26/mysql-partitioning/

MySQL Replicationation Advanced