mysql 主从维护_mysql主从复制

mysql主从复制

主从复制慨述

构建大型，高性能应用程序的基础

主服务器复制负责更新，且将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环

从服务器主动连接主库，通知主服务器从日志中读取的最后一次更新的位置(第一次的话就是开始位置)

从服务器接受从那时起发生的任何更新，任何封锁并等待主库通知新的更新

注意：任何数据更新都必须在主服务器上进行，主能读写，从只能读

使用主从的好处

提高数据库读的性能，主库专注写操作，从可以多个，分散读操作

提高数据安全，在从库做备份不会影响主库相关数据

能解决的问题

数据分布(data distribution)

负载均衡(load balancing)

备份(backups)

高可用性和容错行 High availability and failover，故障切换

mysql支持的复制方法

statement based replication(SBR) 基于SQL语句的复制: 主服务器上执行的sql,在从服务器上同样执行,5.6默认使用SBR,

row based replication(RBR) 基于行的复制: 把改变的内容复制过去,而不是把命令在从服务器上执行一遍,从5.0开始支持

mixed based replication(MBR) 混合型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确复制时,就采用基于行的复制

基于global transaction identifiers(GTIDs)来进行事务复制 5.6.5以后版本支持

mysql是异步复制，mysql cluster是同步复制。

mysql配置文件设置复制方法的变量为binlog_farmat,值为:

STATEMENT

ROW

MIXED

mysql主从复制模式：半同步模式(接近同步)，异步模式(可以配置延迟时间)，同步模式(延时小一点)

mysql主从架构：master --> slave, master --> slave --> slave, master master

几种复制的特点

SBR的特点

二进制日志记录的是sql语句

优点：

历史悠久

日志文件更小

记录了所有的语句，可以用来日后审计

缺点:

使用如下函数的语句不能被正确地复制：load_file(); uuid(), uuid_short(); user(); found_rows(); sysdate(); get_lock(); is_free_lock(); is_used_lock(); master_pos_wait(); rand(); release_lock(); sleep(); version();

在日志中出现如下警告信息的不能正确复制：[Warning] Statement is not safe to log in statement format.

或者在客户端中出现show warnings

Insert … select语句会执行大量的行级锁表

Update语句会执行大量的行级锁表来扫描整个表

RBR的特点

主服务器把表的行变化作为事件写入二进制日志

优点:

所有的数据变化都是被复制,这是最安全的复制方式

更少的行级锁

缺点:

日志会很大

不能通过查看日志来审计执行过的sql语句，不过可以通过mysqlbinlog --base64-output=decode-rows --verbose来查看数据的 变动

MBR

即使用SBR，又使用RBR，默认使用SBR

主从复制工作流程

slave端的SLAVE_IO_RUNNING进程连接上master端，提交主库二进制日志文件名及指定的位置(POS)

master端负责复制的IO进程根据文件名及起始位置读取起始位置及以后的数据，发送给slave端的IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在master端的binary log文件的名称以及在binary log中的位置

slave端的IO线程收到信息(二进制日志事件，binary log events)后，将接收到的日志内容依次写入到save端的relay log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的bin-log的文件名及位置记录到master-info(也可能是mysql.slave_master_info表)文件中

slave端的SLAVE_SQL_RUNNING进程检测到relay log 中新增加的内容后，会马上解析改log文件的内容,slave端重做中继日志中的事件，将改变反映它自己的数据。

下图描述了复制的过程：

Paste_Image.png

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

多级主从：

Paste_Image.png

主从复制参数

server-id = 11 #主从集群中不能重复,唯一ID

log_bin = mysql-bin #启用二进制日志

binlog_format = row #配置二进制日志格式

log_timestamps = SYSTEM #主要是控制 error log、genera log，等等记录日志的显示时间参数

slave-parallel-workers=16 # 从库启用多线程同步，默认为0,并行执行被禁止

slave-parallel-type=LOGICAL_CLOCK # 针对slave-parallel-workers参数，多进程同步时，默认是每个库同一时间只能有一个work进程来进行工作，如果只有一个库，设置LOGICAL_CLOCK来解除这个限制，默认是DATABASE

log_slave_updates=true #从库从主库更新操作也记录从的binlog日志(主 --> 从 --> 从)连级复制时中间的从库必须启用

binlog_checksum=CRC32 #binlog做认证

master_verify_checksum=1

slave_sql_verify_checksum=1

binlog_rows_query_log_events=1 #在RBS模式中，binlog日志及relay log日志中记录具体行的变化内容，也会记录原始的sql语句(注释掉)

master_info_repository=TABLE #是否将主状态和连接信息记录到 FILE(master.info)或a TABLE (mysql.slave_master_info)

relay_log_info_repository=TABLE #取决于服务器的 relay_log_info_repository 设置(FILE或 TABLE)

如下三个参数控制mysql.slave_master_info与mysql.slave_relay_log_info表的刷新频率

sync_master_info=1 #若master-info-repository为FILE，当设置为0，则每次sync_master_info事件都会刷新到磁盘，默认为10000次刷新到磁盘；若master-info-repository为TABLE，当设置为0，则表不做任何更新，设置为1，则每次事件会更新表 #默认为10000

sync_relay_log = 1 #默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制。

sync_relay_log_info = 1 #若relay_log_info_repository为FILE，当设置为0，交由OS刷新磁盘，默认为10000次刷新到磁盘；若relay_log_info_repository为TABLE，且为INNODB存储，则无论为任何值，则都每次evnet都会更新表。

relay_log_recovery=ON #当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的

binlog-row-image=minimal #针对row模式，参数默认值是FULL，为minimal：binlog记录的就只是影响后的行

server-id = ID

log_bin = mysql-bin

binlog_format = row

log_timestamps = SYSTEM

slave_parallel_workers=16

slave_parallel_type=LOGICAL_CLOCK

log_slave_updates=true

binlog_rows_query_log_events=1

master_info_repository=TABLE

relay_log_info_repository=TABLE

sync_master_info=1

sync_relay_log = 1

sync_relay_log_info = 1

relay_log_recovery=ON

binlog_row_image=minimal

复制过滤

两种思路，一种是在主库上过滤，一种是从库上过滤

Paste_Image.png

主库上面仅向二进制日志记录有特定相关数据库写操作；

问题：即时点还原将无法全面恢复

binlog_do_db=

binlog_ignore_db=

从库的SQL_THREAD仅在中继日志中读取特定数据相关的语句并应用到本地

问题： 会造成网络带宽及磁盘IO浪费

replicate_do_db=

replicate_ignre_db=

replicate_db_table=

replicate_ignore_table=

replicate_wild_do_table=

replicate_wild_ignore_table=

实例：

replicate-wild-do-table=xxx_db.%

replicate-wild-do-table=yxxx_db.%

replicate-wild-ignore-table =mysql.%

replicate-wild-ignore-table =sys.%

replicate-wild-ignore-table =information_schema.%

replicate-wild-ignore-table =performance_schema.%

主从复制配置

有很多种配置主从同步的方法，可以总结为如下的步骤：

在master端开启二进制日志和配置独立的ID

在每一个slave端配置一个唯一的ID，创建一个用来专门复制master端数据的账号

在开始复制前，在master端上记录二进制文件位置信息

如果在开始复制之前，master端中已经有数据，就必须先建一个数据快照

配置slave端要连接的master端的IP地址和登陆授权，二进制日志名称及位置

在master端建主从复制专用用户

CREATE USER 'userrepl'@'%' IDENTIFIED BY 'userpasswd';

GRANT REPLICATION SLAVE ON . TO 'userrepl'@'%';

基本配置参数

log-bin=mysql-bin

server-id=1 (每个主机的ID唯一)

slave-parallel-workers=16

slave-parallel-type=LOGICAL_CLOCK

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

binlog-row-image=minimal

传统方式

从上配置连接主库的信息

CHANGE MASTER TO

-> MASTER_HOST='Master_IP',

-> MASTER_USER='userrepl',

-> MASTER_PASSWORD='userpasswd',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=xxx;

GTID方式

gtid事物复制 5.6版本出现 主和从都开启还需要添加旧版本的配置

gtid_mode = on

enforce-gtid-consistency=true

change master to

-> master_host='192.168.1.102',

-> master_user='repuser',

-> master_password='reppasswd',

-> master_port=3306,

-> master_auto_position = 1,

-> master_delay=30; 配置同步延时

半同步方式

主节点：

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.05 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';

+------------------------------------+-------+

| Variable_name | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled | OFF |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

+------------------------------------+-------+

4 rows in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout=2000;

Query OK, 0 rows affected (0.00 sec)

从节点：

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Query OK, 0 rows affected (0.05 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

2 rows in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;

Query OK, 0 rows affected (0.00 sec)

如果从服务器已经启动，还需要重启IO_THREAD;

主从复制状态

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: xx.xx.xx.xx

Master_User: xxrepl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000061

Read_Master_Log_Pos: 48850622

Relay_Log_File: r430db02-relay-bin.000180

Relay_Log_Pos: 48850835

Relay_Master_Log_File: mysql-bin.000061

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: y_db.%,yi_db.%,b_db.%,u_cms.%

Replicate_Wild_Ignore_Table: mysql.%,sys.%,information_schema.%,performance_schema.%

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 48850622

Relay_Log_Space: 48851092

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 11

Master_UUID: a614d748-1cd6-11e6-a130-14187760f283

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 35e7d774-b8d1-11e5-948c-00163e0c1173:1-766221

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

GTID

MySQL [(none)]> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: xx.xx.xx.xx

Master_User: xxrepl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 12844624

Relay_Log_File: vm10-20-10-6-relay-bin.000002

Relay_Log_Pos: 12844837

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table: mysql.%,sys.%,information_schema.%,performance_schema.%

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 12844624

Relay_Log_Space: 12845051

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 68

Master_UUID: d5197ac0-5996-11e7-8f1d-fa163e7c7d02

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: d5197ac0-5996-11e7-8f1d-fa163e7c7d02:1-9459

Executed_Gtid_Set: d5197ac0-5996-11e7-8f1d-fa163e7c7d02:1-9459

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

复制的监控及维护

清理日志：PURGE

复制监控:

SHOW MASTER STATUS

SHOW BINLOG EVENTS

SHOW BINARY LOGS

SHOW SLAVE STATUS

多源复制slave启动和停止

启动所有

START SLAVEthread_types;

启动一个

start slave for channel "master50";

停止

STOP SLAVEthread_types;

stop slave for channel "master50";

查看

show slave status\G

show slave status for channel "master50"\G

重置

RESET SLAVE;

RESET SLAVE FOR CHANNELchannel;

如何判断slave是否落后于master

Seconds_Behind_Master: 0

如何确定主从节点数据是否一致？

通过表自身的CHECKSUM检查

使用percona-tools中pt-table-checksum

数据不一致的修复方法：重复复制；