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

Master-slave architecture: the slave node does not accept w operations, otherwise it may lead to data inconsistency.

First, the problems that should be paid attention to in the replication architecture:

1. Restrict slave to read-only mode

Can be set in the startup parameters.

> show global variables like 'read_only'

This restriction is not valid for users with SUPER privileges.

Block all users:

Mysql > flush tables with read lock; / / blocks all w operations.

/ / but the replay of the relay log is OK and will not be blocked.

two。 How to ensure the transaction security of master-slave replication?

Master should write to the transaction log immediately after executing the transaction.

Binary logs are buffered in memory. / / once the master goes down, slave still does not get the transaction, but the client is already commit and the data is inconsistent

Method: / / make sure that master saves the transaction to the binary log as soon as possible

1) launch parameters in master:

Sync_binlog=ON / / write the binary log immediately

If you use the innodb storage engine:

Innodb_flush_logs_at_trx_commit=ON

Innodb_support_xa=ON

1. Immediately write the data related to the transaction log in the transaction log buffer to the transaction log when the transaction commits.

2.xa: distributed transactions, based on 2-segment commit, execute distributed transactions.

2) on the slave node

Skip_slave_start=OFF

/ / whether to start the replication thread automatically when slave starts

/ / for transaction security: startup is not recommended. Joining directly may lead to errors.

/ / it is recommended to start manually. The thread

/ / refer to http://www.2cto.com/database/201307/230420.html

/ / Note: the security of data cannot be overemphasized.

Slave:

/ var/lib/mysql/

Master.info: master account password information, replication location, etc.

Relay-log.info records from the node itself, which position step is copied to which binary log.

Show global variables like'% relay_log%'

Relay_log_info_file / / saved file

Sync_relay_log

Sync_relay_log_info / /

Master

Show global varibales like'% master%'

Sync_master_info 0

/ / whether the information in master.info is synchronized to disk, so that slave can get the latest information.

/ / it is necessary to start.

Summary:

Master:

Sync_master_info

Slave:

Sync_relay_log

Sync_relay_log_info

Question 2: thinking

If master has been running for some time and has a lot of data

How to configure and start slave

Methods:

Restore data from backup to slave server

When replication starts at backup, the binary log file and its pos

Second, how to carry out master master replication:

An and B: both start relay-log,binary-log at the same time and are master and slave to each other.

Question:

1. The data are inconsistent, so use / / choose one carefully and delete the other

2. Autoincrement increment An and B are both growing automatically, and the merger will go wrong.

/ / Note mysql 5.5 and later. The problem of auto_increment in the main main model has been resolved and does not need to be set separately.

A: odd growth:

Auto_increment_offset=1

Auto_increment_increment=2

/ / starting from 1, increase by 2 at a time

B: even growth: auto_increment_offset=2

/ / what about the imbalance between the left and the right? There's no way.

Auto_increment_offset=2

Auto_increment_increment=2

/ / starting from 2, increase by 2 at a time

3. Circular replication

Configuration steps:

1. Use a unique server-id for each node

two。 Both start binary log and relay log

3. Create a user account with replication permission

4. Define the value of the auto-growing id field as parity

5. Each designate the other as the primary node and start the replication thread

Steps:

Master A:

Systemctl stop mariadb

Rm-rf / var/lib/mysql/*

Vim my.cnf

[mysqld]

Log-bin=master-bin

Lelay_log=relay-log

Server-id=1

Innodb_file_per_table=ON

Skip_name_resolve=ON

Auto_increment_offset=1

Auto_increment_increment=2

Systemctl start mariadb

Mysql > show global variables like'% log%' / / relay_log,log_bin

Msyql > grant replication slave,replication client on *. * to 'repluser'@'192.168.1.%' identified by' replipass'

Mysql > flush privileges

/ / now both are clean.

Show master status / / check the location of the other party

Master-bin.000003 506

Change master to master_host='192.168.1.68',master_user='repuser',master_password='replpass',master_log_file='master-bin.000003'

Master_log_pos=506; / / start from this position in master

Show slave status

Start slave

Master B:

Systemctl stop mariadb

Rm-rf / var/lib/mysql/*

Vim my.cnf

[mysqld]

Log-bin=master-bin

Lelay_log=relay-log

Server-id=5 / / different

Innodb_file_per_table=ON

Skip_name_resolve=ON

Auto_increment_offset=2 / / different

Auto_increment_increment=2

Systemctl start mariadb

Msyql > show global variables like'% log%'; / / relay-log,log-bin

Msyql > grant replication slave,replication client on *. * to 'repluser'@'192.168.1.%' identified by' replipass'

Mysql > flush privileges

Mysql > show master status / / check where you are

Mysql > show master status

Master-bin.000003 506 / / also 506

Change master to master_host='192.168.1.67',master_user='repuser',master_password='replpass',master_log_file='master-bin.000003'

Master_log_pos=506; / / start from this position in master

Show slave staus

Start slave

= =

Test:

A:create database mydb

Show slave status

B: use mydb

Create table tb1 (id int unsigned not null auto_increment primary key,name char (30))

Desc tb1

Show master status

A: check the show slave status. The location of the binary log has changed.

Insert into tb1 (name) values ('yang kang'), (' yang guo')

Select * from tb1

A: insert data

Insert into tb1 (name)

Finally:

Select * from tb1

There is a built-in function that saves the inserted id number.

You can reset insert id.

This way: there is no need to specify when mariadb-server starts

Auto_increment_increment=2,...

Third, semi-synchronous replication / / with the help of plug-ins

After version 5.5

Master can only wait for a slave to return the copy confirmation result / /

If no node returns a synchronization success message:?

Sets the timeout. Automatically downgraded to asynchronous mode after timeout

Based on mariadb plug-in

/ usr/lib64/mysql/plugin/

[root@localhost mysql] # ls / usr/lib64/mysql/plugin/sem*

/ usr/lib64/mysql/plugin/semisync_master.so

/ usr/lib64/mysql/plugin/semisync_slave.so

/ / one belongs to the master node and the other belongs to the slave node

A:systemctl stop mariadb

Rm-rf / var/lib/mysql/*

Vim my.cnf

Log-bin=master-bin

Server-id=1

Innodb_file_per_table=ON

Skip_name_resolve=ON

Systemctl start mariadb

Msyql > grant replication slave,replication client on *. * to 'repluser'@'192.168.1.%' identified by' replipass'

Mysql > flush privileges

Mysql > flush privileges / / slave is not required to create this user

Mysql-bin.00003 496

Install plugin rpl_semi_sync_master SONAME 'semisync_master.so'

Show plugins / / View plug-in

Show global variables like'% semi5'; / / added several variables

Show global status like'% semi%'

. . Client / / how many semi-synchronous node are there

Set global rpl_semi_sync_master_enabled=1

B:systemctl stop mariadb

Rm-rf / var/lib/mysql/*

Vim my.cnf

Relay_log=relay-log

Server-id=2

Innodb_file_per_tab=ON

Skip_name_resolve=ON

Systemctl start mariadb

Change master to master_host='192.168.1.67',master_user='repuser',master_password='replpass',master_log_file='master-bin.000003'

Master_log_pos=496; / / start from this position in master

Install plugin rpl_semi_sync_slave SONAME 'symisync_slave.so'

Set global rpl_semi_sync_slave_enabled=1 / / enable

Start slave

Show slave staus

/ / check on An again,. Client is 1. Master_status=ON

Note: help install

[mysqld]

Plugin_dir=/path/to/plugin/directory / / default / usr/lib64/mysql/plugin

Test:

A:master

Msyql > create table tb1 (id int,name char (30))

Msyql > show global status like'% semi%'

There will be a lot of status waiting time.

Summary:

It is recommended to configure only one and a half synchronization.

Master:

Mysql > install plugin rpl_semi_sync_master SONAME 'semisync_master.so'

Mysql > set global variables rpl_semi_sync_master_enabled=1

Mysql > show gloabl variables like'% semi%'

Mysql > show global status like'% semi%'

Slave:

Msyql > install plugin rpm_semi_sync_slave SONAME 'semisync_slave.so'

Msyql > set global variables rpl_semi_sync_slave_enabled=1

4. Copy filter:

Msyql can replicate one or more databases

Replication filters can be done in master and slave node

On master: it will cause the binary log of master to be incomplete and not record the data of all databases.

Let the slave node copy only the specified database, or the specified table / / of the specified database, but master sends all binary data to slave, and it is up to slave to choose partial replication.

Achieve:

(1) the primary service records only events related to a specific database (specific table) in the binary log.

Problem: time restore cannot be implemented and is not recommended

Binlog_do_db / / database whitelist list

Binlog_ignore_db / / database blacklist list, do not use these two at the same time

(2) when slave sql_thread relays events in the replay log, only events related to a specific database (specific table) are read and applied locally.

Problem: it will bring network and disk IO waste.

Replicate_do_db / / whitelist separated by commas

Replicate_ignore_db / / ignored database

Implementation: / / on the original master-slave architecture

Mysql > show global variables like 'replicate%'

Mysql > set global replicate_do_db='mydb'

/ / filter on slave

Mysql > show slave status\ G

/ / Test to create other databases on master

/ / you can't see it on slave, but master inserts data on mydb and can see it on slave

Note: show slave status\ G

Replicate_ignore_table

Replicate_do_table; / / restrictions can be made on a single table

Replicate_do_db:

Replicate_ignore_db

Replicate_wild_do_table; / / wildcard

Replicate_wild_ignore_table; / / wildcard

SSL-based replication

Help change master / /

Change master master_ssl / / specifies to use ssl for replication

Check to see if it supports:

Mysql > show global variables like'% ssl%'; / / use ssl between front-end applications and mysql for security reasons

Prerequisite: support for SSL

(1) master configures the private key of the certificate; and to create a replication account that requires a private SSL connection

Help grant

Grant... With ssl_option ssl_option

/ / generally, slave verifies the status of master

(2) specify ssl-related options when using the change master to command on the slave side

Files related to the replication function:

Master.info: used to store information about slave connecting to master, such as account number, password, server address, etc.

Relay-log.info: the correspondence between the replicated binary log and the local relay log log saved on the current slave node

Monitoring and maintenance of MySQL replication:

(1) Clean up logs:

Purge binary logs to 'mysql-bin.010'

Purge binary logs before '2008-04-01 10 0012'

Show binary logs

(2) copy monitoring

Show master status

Show binlog events

Show binary logs

Show slave status

Show process list; / / View replication thread

(3) whether slave server lags behind master service

Slave:

Show slave status

How long does Second_Behind_master;// lag behind master?

(4) how to determine whether the master-slave nodes are consistent or not

Percona-tools / / A tool can detect

(5) how to fix the data inconsistency

1.slave data deletion, re-backup on master

two。 Multiple slave, grayscale mode, online and offline one by one

Re-copy.

VI. Read-write separator

/ / Open source implementation, some companies develop their own or implement read-write separation in front-end applications

Mysql-proxy / / there are many pits, which are no longer used.

Http://www.cnblogs.com/phpstudy2015-6/p/6687480.html#_label1

Alibaba's cobar

Atlas of 360th

Kingshard of golang

Http://blog.csdn.net/hu_wen/article/details/53635976

Mysql-router: officially provided, read-write separation is not supported

It implements failover and failover, and the tool has its own ip and port to achieve high availability.

Algorithm: rotation training, weighted rotation training, etc.

How to add a new salve

1 dump the data of the main library master, stop slave.

Mysqldump-uroot-p-- all-databases > all.sql

Show master status

Mysql-bin.000002 652

2 is passed to the slave library slave, and then restored on the slave library slave.

Mysql > stop slave

# msyql

< all.sql; change master to master_host='192.168.1.106',master_user='repluser',master_password='replpass' ,master_log_file='mysql-bin.000002',master_log_pos=652,master_connect_retry=5; msyql>

Start slave

Remarks: reset slave / / reset slave

Attachment: how to switch slave to master

Note:

1.mysqlhotcopy can only hot standby the MyISAM table.

two。 Principle: first add a read lock to the database that needs to be backed up

Then use FLUSH TABLES to write the data in memory back to the database on the hard disk.

Finally, copy the database files that need to be backed up to the target directory

Mysqlhotcopy [option] dbname1 dbname2 backupDir/

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.