Shulou(Shulou.com)06/01 Report--

The following is about common problems and effective solutions to MySQL master-slave replication. The secret of the text lies in being close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on common problems and effective solutions of MySQL master-slave replication.

First, replication problems and solutions:

(1) data corruption or loss:

Corruption on a slave: replay binary logs on slave, resulting in data inconsistencies for a variety of reasons. For example, hardware failure

/ / restore the dataset backed up on other master on the new slave, and copy from the master from the location of the backup.

Re-copy / / offline failed slave, restore and then copy again

Master data is corrupted or lost:

/ / find the latest salve on each slave according to its gtid, and then upgrade it to master

/ / MHA+semi repl / / MHA and a node semi-synchronous replication. Upgrade semi-synchronous node to a new master.

/ / final proposal:

Binary log analysis, and then restore

Single malfunction: deserved it!

(2) mixed use of storage engines; / / resolutely put an end to

MyISAM does not support transactions / / cannot be rolled back

InnoDB supports 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 server-id sent by master

Modify server id and copy again

(4) replication delay

Lag behind master from node

Write your own script and constantly monitor it and find that the lag time is not equal to zero. After that, you can restart the io thread of slave.

Additional monitoring tools are needed to assist with the implementation.

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

Therefore, slave is also single-threaded in local applications.

There are multiple libraries on master, and the transactions of each library can be parallel, because the lock on library A has no effect on library B, but there must be a sequence reflected in the binary log.

Therefore, there is no way to avoid the backwardness of slave, so multithreading has been introduced since mysql 5.5.

Support for one-slave, multi-master and multi-thread replication

Multithreaded replication / / enable only one thread per database

/ / but the main database 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, SQL threads on Slave

Metrics of database services:

Qps:query per second

Tps: transaction per second

Database stress testing tools:

Sysbench

2. Galera Cluster: mostly used for data replication in MySQL

Http://galeracluster.com/

A lower-level replication mechanism

The implementation needs to be compiled separately.

Installation mode

Msyql official or galera / / official version that supports galera

Percona-cluster / / integrated mysql version

Mariadb-cluster / / dedicated replication

Neither base nor epel provides a source for galera-cluster

The default version of mariadb does not support 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

/ / at least 3 node are required

1. Premise: hosts files and time synchronization

Node1:192.168.1.67

Node2:192.168.1.68

Node3:192.168.1.69

Node1:

It is recommended to configure the yum source locally

Yum remove mariadb / / Uninstall client and server

Yum install MairaDB-Galera-server

Node2:

It is recommended to configure the yum source locally

Yum remove mariadb / / Uninstall client and server

Yum install MairaDB-Galera-server

Node3:

It is recommended to configure the yum source locally

Yum remove mariadb / / Uninstall client and server

Yum install MairaDB-Galera-server

two。 Profile 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

...

Start for the first time: 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 nodes 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: same as node3: configuration file

Node1: any node will do

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

Node2:

Service mysql start

Node3:

Service mysql start

3. test

Mysql > any node creation data

You can see 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 | H2

4 | hello

> insert into tb2 (name) values ('to'), (' from')

> select * from tb2

1 hi

4 hello

5 to

8 from

Use the global id generator

R _ foreign separator:

Or write it yourself.

Or use open source tools

Or configure the implementation at the application layer / / manually modify or use vrrp in case of failure

Personal test method:

1. Copy all rpm files

2.createrepot / testrepo

3. Just modify the yum file to point to the yum source.

4. Use yum for installation

3. Multithreaded replication of MySQL 5.6and GTID

Note: any service or host should be monitored

The reason why mysql lags behind: single-thread replication

5.5 semi-synchronous

5.6 GTID, multithreaded replication

MariaDB is added to the mysqld option group of the configuration file:

Slave-parallel-threads

MASTER: my.cnf adds the following parameters

Binlog_format = row

Gtid_mode = ON

Enforce-gtid-consistency = ON

Slave_parallel_workers=4-enable multithreaded replication based on libraries. Default 0 is not enabled.

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 adds 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-- Master event check

Slave_sql_verify_checksum = 1-- check from event

Slave_allow_batching = 1

Log-slave-updates=true

Is there anything you don't understand about the common problems and effective solutions of the above MySQL master-slave replication? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.