Linux Mysql master-slave replication principle Introduction and steps

大家好，我是霸王卸甲，今天我给大家带来的是linux数据库中的主从复制的简单介绍和步骤详解。主从复制

MySQL master-slave replication flexibility

一主一从主主复制一主多从---扩展系统读取的性能，因为读是在从库读取的；多主一从---5.7开始支持联级复制---

Use and Conditions

MySQL master-slave replication uses

实时灾备，用于故障切换读写分离，提供查询服务备份，避免影响业务

Prerequisites for Master-Slave deployment:

主库开启binlog日志（设置log-bin参数）主从server-id不同从库服务器能连通主库

Master principle
MySQL master-slave replication principle

Generate two threads from the library, one I/O thread, and one SQL thread;

The I/O thread requests the binlog of the main library and writes the resulting binlog log to the relay log (trunk log) file;
The main library generates a log dump thread to path Binlog from the library I/O line;

SQL thread, will read the log in the relay log file, and parse into concrete operation, to achieve the master-slave operation consistent, and the final data consistent;

Problems and Solutions

Problems with MySQL master-slave replication:

主库宕机后，数据可能丢失从库只有一个sql Thread，主库写压力大，复制很可能延时

Workaround:

半同步复制---解决数据丢失的问题并行复制----解决从库复制延迟的问题

Semi-synchronous replication

MySQL Semi-sync (semi-synchronous replication)
Semi-synchronous replication:

5.5集成到mysql，以插件的形式存在，需要单独安装确保事务提交后binlog至少传输到一个从库不保证从库应用完这个事务的binlog性能有一定的降低，响应时间会更长网络异常或从库宕机，卡主主库，直到超时或从库恢复

Master-Slave replication-the principle of asynchronous replication

The principle of semi-synchronous replication:

Parallel replication

MySQL parallel replication

社区版5.6中新增并行是指从库多线程apply binlog库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)

Set up

Set global slave_parallel_workers=10;

Set the number of SQL threads to 10

Other

Partial data replication
Main Library Add Parameters:

Binlog_do_db=db1
Binlog_ignore_db=db1
Binlog_ignore_db=db2

Or add parameters from the library

Replicate_do_db=db1
Replicate_ignore_db=db1
Replicate_do_table=db1.t1
replicate_wild_do_table=db%.%
replicate_wild_ignore_table=db1.%

cascading replication (Common)
A->b->c
Add parameters in B:

Log_slave_updates
B will record A's binlog into its own binlog log.

Monitoring of replication:

Show slave status \g

Copy error handling
Common: 1062 (primary key conflict), 1032 (record does not exist)
Solve:

手动处理跳过复制错误：set global sql_slave_skip_counter=1

Summarize

主从形式    一主一从    一主多从--扩展系统读取性能    多主一从--5.7开始支持    主主复制    联级复制用途：实时灾备的故障切换，读写分离，备份原理    主：log dump线程传binlog；    从        i/o线程接受读取binlog，并写入relay log文件        sql线程从relay log 文件中读取binlog并持久化问题及解决    主库宕机后，数据丢失        半同步复制    主库写压力大，因从库只有一个sql 线程来持久化，复制可能延迟        并行复制半同步复制：    原理        事务在主库写完binlog后需要从库返回一个已接受，才放回给客户端；    5.5集成到mysql，以插件的形式存在，需要单独安装    确保事务提交后binlog至少传输到一个从库    不保证从库应用完成这个事务的binlog    性能有一定的降低    网络异常或从库宕机，卡主库，直到超时或从库恢复并行复制    原理：从库多线程apply binlog    在社区5.6中新增    库级别并行应用binlog，同一个库数据更改还是串行的    5.7版本并行复制基于事务组部分数据复制联级复制（常用）    A->B->C    B中添加参数log_slave_updates    B将把A的binlog记录到自己的binlog日志中复制的监控    show slave status复制出错处理    常见：1062（主键冲突），1032（记录不存在）    解决：        手动处理        跳过复制错误：set global sql_slave_skip_counter=1mysql主从复制是mysql高可用性，高性能（负载均衡）的基础简单，灵活，部署方式多样，可以根据不同业务场景部署不同复制结构复制过程中应该时刻监控复制状态，复制出错或延时可能给系统造成影响mysql主从复制目前也存在一些问题，可以根据需要部署复制增强功能

One Master one from
Step parsing:

Prepare 4 machines:
10.18.42.116 KVM4
10.18.42.79 KVM5
10.18.42.171 KVM6
10.18.42.69 KVM7
Parsing in/etc/hosts

Host
1. Open the binary log in KVM4, server ID Gtid
Vim/etc/my.cof
Log-bin
Server-id=1
Gtid_mode=on
Enfore_gtid_consistency=1
Restart:
Systemctl Restart Mysqld

2. Authorized Replication User Rep
Grant replication Slave,replication Client on . to ' rep ' @ ' 10.18.42.% ' identified by ' [email protected] ';
Refresh:
Flush privileges;

3. Backing Up Database data
Mysqldump-p ' [email protected] '--all-databases--single-transaction--master-data=2--flush-logs > date +%F - Mysql-all.sql
Use SCP to transfer data synchronization from the machine
SCP 2018-1-1-mysql-all.sql kvm5:/tmp

4. Analog data changes can be used as a non-invasive creation of the library

Slave machine
1. Test if the rep user is available
Mysql-h kvm4-urep-p ' [email protected] ' (note that the firewall should be off)

2 Start binary log, server Id,gtid
Vim/etc/my.cnf
Log-bin
server-id=2
Gtid_mode=on
Enforce_gtid_consistency=1
Restart database
Systemctl Restart MYSQLD Test configuration If there is a problem, if startup fails. Please check the configuration.

3. Restore Manual Sync Data
Ls/tmp/2018-1-1-mysql-all.sql
Into the vault.
Set sql_log_bin=0;
Source/tmp/2018-1-1-mysql-all.sql
See if data is synchronized or re-operate

4. Setting up the primary server
Change Master tobr/>master_host= ' Kvm4 ',
Master_user= ' rep ',
master_password= '[email protected]',
Start slave;
show slave status\g;
View
Slave_io_running:yes
Slave_sql_running:yes
Success

5 returns the primary server (KVM4) Update data and observes the synchronization from the server (KVM5).

Dual master double Slave (MM-SS)
Because the top has been completed KVM4 Master KVM5 from
So then make KVM5 master kvm4 from

1. Set the primary server KVM5 to KVM4
Into the KVM5.
Change Master tobr/>master_host= ' Kvm4 ',
Master_user= ' rep ',
master_password= '[email protected]',
Restart
Start slave;
show slave status\g;
If the status is not good, restart Master2 's mysqld

Test to create data synchronization with each other

2. Synchronizing existing Databases
KVM4 mysqldump-p '[email protected]'--all-databases--single-transaction--master-data=2--flush-logs > C2/>-mysql-all.sql

Use SCP to KVM6 KVM7
Scp-r 2018-1-1-mysql-all.sql kvm6:/tmp
Scp-r 2018-1-1-mysql-all.sql kvm7:/tmp

KVM6 mysql-p '[email protected]' </tmp/2018-1-1-mysql-all.sql
KVM7 mysql-p '[email protected]' </tmp/2018-1-1-mysql-all.sql

3. Start KVM6 kvm7 ID gtid main information repository = Table Relay log information repository = table
Kvm6

Vim/etc/my.cnf
Server-id=3
Gtid_mode=on
Enforce_gtid_consistency=1
Master-info-repository=table
Relay-log-info-repository=table

Systemctl Restart Mysqld

Kvm7

Vim/etc/my.cnf
Server-id=4
Gtid_mode=on
Enforce_gtid_consistency=1
Master-info-repository=table
Relay-log-info-repository=table

Systemctl Restart Mysqld

4. Setting up the master server in KVM6 kvm7
KVM6:
Change Master tobr/>master_host= ' Kvm4 ',
Master_user= ' rep ',
master_password= '[email protected]',
< p="">

Change Master tobr/>master_host= ' KVM5 ',
Master_user= ' rep ',
master_password= '[email protected]',
< p="">

Start slave;
show slave status\g;

KVM7:
Change Master tobr/>master_host= ' Kvm4 ',
Master_user= ' rep ',
master_password= '[email protected]',
< p="">

Change Master tobr/>master_host= ' KVM5 ',
Master_user= ' rep ',
master_password= '[email protected]',
< p="">

Start slave;
show slave status\g;

Test data is synchronized

Summary, points of error and solutions
Slave_io_running:no
Slave_sql_running:yes

Enter the database
Stop slave;
Reset slave;
Re-configuring the Master and Slave
Start slave;
show slave status\g;

Slave_io_running:yes
Slave_sql_running:no

Enter to show slave status\g;
last_sql_errno:1008 Error 1008
Go to config file to skip this error
Vim/etc/my.cnf
slave-skip-errors=1008
Restart database
Enter the database
Start Slavr;
To view is show slave status\g;
This will skip this error
Go out and comment out slave-skip-errors=1008.
You can do it.

1236 is caused by some reason data is out of sync
In this case, manual synchronization
Re-backup on the main machine to the slave machine
Manual update
Restart database
Into the database
Start slave;
Start slave;
(played two times)
Double Yes

The above is today's full content, mainly a master one from and double master double from, in addition to the above mentioned problems also encountered new problems can be a private messages to me, I will reply to you in the first time, thank you.

Linux Mysql master-slave replication principle Introduction and steps