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