MySQL master-slave replication Galera

First, master-slave replication problems and solutions

Second, Galera Cluster

Third, MySQL 5.6 Multi-threaded replication, GTID

MySQL Replication:

Master ha or multi-master node model

Mmm:multi Master MySQL

Mha:master HA

Monitoring of the master node enables automatic failover to other slave nodes, by raising a slave node to a new master node

Galera Cluster:wresp

Replicate globally through the WRESP protocol: any node can read and write

Read/write Separation://is usually developed by oneself

Mysql-proxy: no stable stable version

Mysql-proxy--> atals (Qihoo)

Amoeba: At present, the level of activity is not high

I. Replication Problems and solutions:

(1) Data corruption or loss:

Corruption on one slave: Replay binary logs on slave for various reasons, resulting in inconsistent data. such as hardware failure

Use the backup data set on the other master, and restore on the new slave, starting from the location of the backup and copying from master.

re-copy can//offline failure of the slave, restore and then re-copy can

Master Data corruption or loss:

//On each slave, find the latest salve based on their gtid, then upgrade to master

//mha+semi repl//MHA and a node semi-synchronous replication, promote the semi-synchronous node to the new master

Final Solution:

binary Log parsing, and then restore

Single fault: deserved it!

(2) Mixed use of storage engine;//resolutely eliminate

MyISAM does not support transactions//cannot be rolled back

InnoDB Support transactions

(3) non-unique server ID

The entire replication cluster can lead to confusion.

///For example, if the server ID of a slave is the same as the server ID of master, then he will not replay the master sent Server-id

Modify Server ID re-replication

(4) Replication delay

lag behind the master from node

Write your own script, constantly monitoring, found that the lag time is not equal to 0, after the slave can restart the IO thread

Additional monitoring tools are required to assist the implementation.

transactions on master can be concurrent, but writing to the same binary log is single-threaded

So slave is also single-threaded in local applications

Master has more than one library, each library transaction can be parallel, because the lock on a library, B library is not affected, but reflected in the binary log must have sequential order

So there is no way to avoid slave lag, so after MySQL 5.5 introduced multithreading mechanism.

supports one from multiple and multi-threaded replication

Multi-threaded replication//per database only one thread is enabled

//But the main library cannot be the same

multithreaded replication: still useful, dependent on Gtid

after//mysql 5.6.3, after MariaDB10.0.5

Replication Threads: Io threads on master and IO on slave, SQL threads,

Metrics for Database services:

Qps:query per second

TPS: Transaction per second

Database Stress Test Tool:

Sysbench

Second, Galera Cluster: Multiple data replication for MySQL

http://galeracluster.com/

A lower-level replication mechanism

The implementation needs to be compiled separately.

Installation method

MSYQL official or Galera//official support Galera version

Percona-cluster//Integrated MySQL version

Mariadb-cluster//Dedicated replication

Both base and Epel do not provide a source for Galera-cluster

The default MARIADB version is not supported for Galera

The package you need to use:

mariadb-5.5.46-centos7-x86_64-client.rpm

mariadb-5.5.46-centos7-x86_64-common.rpm

mariadb-galera-5.5.46-centos7-x86_64-common.rpm

Minimum of 3 node required

1. Prerequisites: Hosts file and time synchronization

node1:192.168.1.67

node2:192.168.1.68

node3:192.168.1.69

Node1:

We recommend that you configure the Yum source locally

Yum Remove MARIADB//Uninstall client and server side

Yum Install Mairadb-galera-server

Node2:

We recommend that you configure the Yum source locally

Yum Remove MARIADB//Uninstall client and server side

Yum Install Mairadb-galera-server

NODE3:

We recommend that you configure the Yum source locally

Yum Remove MARIADB//Uninstall client and server side

Yum Install Mairadb-galera-server

2. configuration file Description/etc/my.cnf.d/server.cnf

...

[Galera]

# manatory Settings//Mandatory configuration

#wresp_provider =/usr/lib64/galera/libgalera_smm.so

#wresp_cluster_address = "gcomm://192.168.1.67, 192.168.1.68, 192.168.1.69"

#wresp_cluster_name = "Mycluster"

#wresp_node_name = ' Node1 '

#wresp_node_address = ' 192.168.1.67 '//These two can be omitted

#binlog_format = row

#default_storage_engine =innodb

#bind-address=0.0.0.0

# Optional Setting

#wsrep_slave_threads =1

#innodb_flush_logs_at_trx_commit =0

...

First boot: You need to initialize the cluster and execute the following command on one of the node

/etc/init.d/mysql start--wsreq-new-cluster

Then start the other node normally

3. Modify the configuration file

Node1:

[Galera]

Wresp_provider=/usr/lib64/galera/libgalera_smm.so

Wresp_cluster_address= "gcomm://192.168.1.67, 192.168.1.68, 192.168.1.69"

Binlog_format=row

Default_storage_engine=innodb

innodb_autoinc_lock_mode=2

bind-address=0.0.0.0

Wresp_cluster_name= "Mycluster"

Node2: and Node3: Same configuration file

Node1: Any node can

/etc/rc.d/init.d/mysql Start--wsrep-new-cluster

Node2:

Service MySQL Start

NODE3:

Service MySQL Start

3. Testing

Mysql> any one node to create data

You can see the data on other node

Node1:

> Use mydb;

> CREATE table TB2 (id int unsigned auto_increment NOT null primary key,name char (30));

> INSERT into TB2 (name) VALUES (' Hi '), (' Hello ');

Node2:

> select * from TB2;

ID | Name

---------

1| H1

4| Hello

> INSERT into TB2 (name) values (' to '), (' from ');

> select * from TB2

1Hi

4Hello

5to

8from

Using the Global ID generator

R/W separators:

or write it yourself.

Or use an open source tool

Or in the application layer configuration implementation//Once the failure requires manual modification or use of VRRP

Pro-test method:

1. Copy all RPM files

2.createrepot/testrepo

3. Modify the Yum file to point to the Yum source

4. Install with Yum

Third, MySQL 5.6 Multi-threaded replication, GTID

Note: Any service or host should be monitored

Reasons for MySQL lag: single-threaded replication,

5.5 Semi-synchronous

5.6 GTID, multi-threaded replication,

MARIADB is added in the MYSQLD option group for the configuration file:

Slave-parallel-threads

MASTER:MY.CNF Add the following parameters

Binlog_format = row

Gtid_mode = On

Enforce-gtid-consistency = On

Slave_parallel_workers=4--Turn on library-based multi-threaded replication default 0 does not turn on

Binlog_cache_size = 8M

Max_binlog_size = 50M

Max_binlog_cache_size = 100M

Sync_binlog = 1

Expire_logs_days = 1

Log-slave-updates=true

SLAVE:MY.CNF Add the following parameters

Binlog_format = row

Gtid_mode = On

Enforce-gtid-consistency = On

Binlog_cache_size = 8M

Max_binlog_size = 50M

Max_binlog_cache_size = 100M

Sync_binlog = 1

Expire_logs_days = 1

Slave_parallel_workers=4

Max_relay_log_size = 50M

Relay_log_purge = 1

Relay_log_recovery = 1

Master_verify_checksum = 1--Main Event check

Slave_sql_verify_checksum = 1--From the event checksum

slave_allow_batching = 1

Log-slave-updates=true

Parallel replication of MySQL 5.6 and 5.7: Reference

Http://www.cnblogs.com/xiaotengyi/p/5532191.html

MySQL master-slave replication Galera