MySQL主从复制技术（纯干货）

来源：互联网发布：销售管理论文数据编辑：程序博客网时间：2024/04/28 17:16

1.复制配置

主机一定要开启二进制日志（这里建议配置RBR）

每个主机和每个从机一定要配置一个位移的id，即server-id

每个从机配置一定要包含主机名称，日志名称，和位置号，在5.5以后已经只允许使用session动态配置。

1.1 怎样配置主从复制

复制主机配置

[mysqld]

log-bin=mysql-bin

server-id=1 范围 1-2^32-1 如果设置成0 那么拒绝所有从服务器连接

innodb_flush_log_at_trx_commit=1

sync_binlog=1

复制从机配置

[mysqld]

server-id=2

1.2 在主机上创建复制账户

mysql>CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

1.3已经存在数据时候搭建主从复制集群，使用mysqldump 创建数据快照

mysql>mysqldump --all-databases --master-data > dbdump.db

单一数据库

mysql> mysqldump -B database_name --master-data > database_name.sql

如果没有master-data 那么就要使用FLUSH TABLES WITH READ LOCK 锁住所有表

1.4 5.5版本在从机上面配置主机要使用CHANGE MASTER TO 语句：

mysql>CHANGE MASTER TO

-> MASTER_HOST='master_host_name',

-> MASTER_USER='replication_user_name',

-> MASTER_PASSWORD='replication_password',

-> MASTER_LOG_FILE='recorded_log_file_name',

-> MASTER_LOG_POS=recorded_log_position;

注释：以下为复制指定数据库选项

--replicate-do-db, --replicateignore-db, and --replicate-rewrite-db

1.5 GTID模式的研究与维护

同步过程中修改停机维护行为步骤，或者添加GTIDs 功能

step 1 mysql> SET @@global.read_only = ON; 确保已经完全同步然后进行步骤2

step 2 shell mysqladmin --uusername -p shutdown

step 3 (使用GTIDs)shell> mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency & 后面的可以在配置文件中配置

注入一个空事务

SET GTID_NEXT='aaa-bbb-ccc-ddd:N';

BEGIN;

COMMIT;

SET GTID_NEXT='AUTOMATIC';

一旦已经事务标识正常以后要在从机上面刷新并清理日志 N 是当前正在使用的二进制文件

FLUSH LOGS;（刷新日志会强制生成一个新的二进制文件，下面的N便是指这个文件）

PURGE BINARY LOGS TO 'master-bin.00000N';（注：清理的二进制文件并不包括当前N这个文件）

GTIDs的限制：（重点）

因为GTID复制是基于事务的，一些mysql本身特点在使用GTID时候可能不支持。

1.涉及到关于非事务存储引擎的更新操作，因为混合使用可能导致同一个事物出现多个GTIDs值

解决：也就只使用Innodb就可以

2.CREATE TABLE ... SELECT 语句，这个语句在GTID模式下是被禁止使用的，因为这对于GTID是不安全的，当使用基于行的复制时，这个语句实际是是两个事件，一个创建表的事件，一个是从原表插入行，实际上是两个事件，但是在从机会使用一个GTID 去接收，也就是只接收了创建表的命令，而丢失了导入数据，因此这个语句是不被支持的

3.临时表 CREATE TEMPORARY TABLE 和 DROP TEMPORARY TABLE 在事务内部不被支持，只能在事务外部并且autocommit=1 才可以

如果开启了--enforce-gtid-consistency 选项那么在GTID模式下任何时候都不可以

4.开启--enforce-gtid-consistency 就可以防止以上错误语句的执行，会以错误形式返回。

GTID 由于临时表的限制不可取。使用一般复制

Command-Line Format --read-only

Name read_only

Variable

Scope

Global

System Variable

Dynamic

Variable

Yes

Permitted Values Type boolean

Default false

Cause the slave to permit no updates except from slave threads or from users having the SUPER

privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its

master server and not from clients. This variable does not apply to TEMPORARY tables.

--read-only 建议用在从机配置上，非主机和不具有super权限的用户无法更新从机

1.6 检查复制状态

SHOW SLAVE STATUS

mysql> SHOW SLAVE STATUS\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: master1

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 931

Relay_Log_File: slave1-relay-bin.000056

Relay_Log_Pos: 950

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes # IO线程正常

Slave_SQL_Running: Yes # SQL线程正常知道原理就知道这两个的作用了

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 931

Relay_Log_Space: 1365

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 # 这个不能用来作为主从延迟的判断标准，不准确的， 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: 0

1.7 暂停主从复制：STOP SLAVE;

STOP SLAVE IO_THREAD;

STOP SLAVE SQL_THREAD;

START SLAVE;

START SLAVE IO_THREAD;

START SLAVE SQL_THREAD;

作用：单停止SQL线程这样可以方便的做备份，备份结束后可以继续开启SQL线程，这样从机也可以很容易追上主机

如果主机有修改等动作，暂停IO线程，然后再CHAGNE MASTER TO 新的动作

1.8 复制有三个线程主机有一个，从机有两个，主机是Binlog dump thread 当从机连接到主机后用来给从机发送而二进制日志，会锁住每个要读的事件，从机是Slave I/O thread 和 Slave SQL thread 前者当START SLAVE 开始后如果向主机索取那些有变动的行从二进制日志，复制这些内容到自己的中继日志。后者从IO线程写入的中继日志读取和执行每个事件，目前截止5.6版本，执行对于单库来说是单线程，对于多库是多线程，也就是库级并发。

1.9对于数据库级别的复制，指定复制数据库，和忽略数据库本身只需要配置一个

--replicate-do-db, --replicate-ignore-db 这两个选项则选择顺序为先过滤前者，如果前者满足配置则不会判断后者，否则继续判断后者。

和这两个参数是类似的 --binlog-dodb --binlog-ignore-db ，区别在于主机和从机上面控制

表级别控制暂时忽略

2.0. 半同步复制：

mysql 复制默认是异步的，半同步复制简单来说就是主机提交事务至少要确定有一个从机全部接收完毕，否则将会等待，如果从机没有反应，则直到超时。

如果超时那么半同步复制将会自动转换为异步复制，当至少一个半同步从机追赶上后，主机将切换会半同步复制。

半同步复制至少要在主机和一台从机开启，否则将使用异步复制

• rpl_semi_sync_master_enabled

控制是否半同步复制开启，默认是0 不开启

• rpl_semi_sync_master_timeout

控制等待从机相应的超时切换异步复制的时间。默认是10000ms （10S）

• rpl_semi_sync_slave_enabled

从机控制是否开启半同步复制

半同步复制监控的几个状态值

• Rpl_semi_sync_master_clients

半同步从机的数量

• Rpl_semi_sync_master_status

半同步在主机上运行，如果正常半同步复制为1，如果插件没有启用，或者因为超时导致切换异步则为0。

• Rpl_semi_sync_master_no_tx

从机认为不成功的事务数量

• Rpl_semi_sync_master_yes_tx

从机认为成功的事务数量

• Rpl_semi_sync_slave_status

是否半同步复制在从机上正常运行，1表示正常，0表示异常

半同步的安装和配置：

要求mysql5.5 以上版本

have_dynamic_loading 系统参数为YES

二进制分发版支持动态加载

半同步复制插件包含在二进制分发版中

找到插件

二进制分发版插件位置

./lib/plugin/semisync_slave.so

./lib/plugin/semisync_master.so

将主和从对应的插件放到相应的插件目录

show global variables like 'plugin_dir' 可以查看目录位置

然后进入mysql，安装插件

主机上面：

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从机上面：

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

如下错误可能发生，需要安装install libimf:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

ERROR 1126 (HY000): Can't open shared library

'/usr/local/mysql/lib/plugin/semisync_master.so' (errno: 22 libimf.so: cannot open

shared object file: No such file or directory)

http://dev.mysql.com/downloads/os-linux.html. 参考安装链接

使用SHOW PLUGINS 查看当前安装的插件信息

开启半同步的参数是动态的，既可以在配置文件中配置也可以动态开启

如下

运行时，主机上面：

mysql> SET GLOBAL rpl_semi_sync_master_enabled = {0|1};

mysql> SET GLOBAL rpl_semi_sync_master_timeout = N;

从机上面：

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = {0|1};

配置文件中

主机：

[mysqld]

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000 # 1 second

从机:

[mysqld]

rpl_semi_sync_slave_enabled=1

监控状态要具体知道状态值对应的意义

关键几个如下：

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';

具体参考文档参数内容

延迟复制：

CHANGE MASTER TO MASTER_DELAY = N; 单位S

具体参考官方文档

复制已知存在的问题：

基于语句的复制 AUTO_INCREMENT, LAST_INSERT_ID(), 和 TIMESTAMP 在除了下面这些情况下是正确的

1. mysql 5.6.10 以前的版本，自增列在同一个表一定匹配的是同样的自增列。即不能一个不是auto_increment 另一个是auto_increment ，列的名称一样。

2.执行触发器或者函数去更新一个auto_increment 列，复制会不正确。在mysql5.6中这样的语句是不安全的

3.插入一个存在联合主键，并且主键包含AUTO_INCREMENT列，但是它不是第一个列的表，基于语句的复制是不安全的（这个问题INNODB存储引擎不受影响）

CREATE TABLE .... SELECT 语句对于没有GTID的RBR格式是安全的，对于SBR 和MIX 有可能出问题的。

问题很多都是关于SBR和MIX的，但是基于RBR的复制基本上不会出现这里没有记录的问题

复制的过滤规则：

主机会把所有的记录二进制语句发送给从机，由从机判断筛选和过滤。

实施步骤：

两台主机安装mysql （略）

注: 主从配置基于主机和从机内存为4G的配置

主机配置/etc/my.cnf (master)

[mysqld]

################## base ##################

basedir = /usr/local/mysql

datadir = /mysql/data

port = 3306

server_id = 239

socket = /var/lib/mysql/mysql.sock

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER

user = mysql

##########################################

################# general ################

bulk_insert_buffer_size = 16M

character_set_server = utf8

event_scheduler = 1

explicit_defaults_for_timestamp = 1

join_buffer_size = 16M

interactive_timeout = 1800

lock_wait_timeout = 60

max_allowed_packet = 64M

max_connections = 256

max_heap_table_size = 64M

skip_name_resolve = 1

sort_buffer_size = 16M

table_open_cache = 3000

tmp_table_size = 32M

transaction-isolation = READ-COMMITTED

wait_timeout = 1800

symbolic-links = 0

##########################################

################# myisam #################

key_buffer_size = 16M

myisam_sort_buffer_size = 32M

##########################################

################# innodb #################

innodb_buffer_pool_size = 2G

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_checksum_algorithm = crc32

innodb_checksums = 1

innodb_file_per_table = 1

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_flush_method = O_DIRECT

innodb_lock_wait_timeout = 30

innodb_log_buffer_size = 32M

innodb_log_file_size = 256M

innodb_sort_buffer_size = 32M

##########################################

################### log ##################

log_bin = /mysql/log/mysql_master.bin

binlog_format = ROW

expire_logs_days = 10

log_bin_trust_function_creators = 1

log-error = /mysql/log/mysql_master-err.log

slow_query_log = 1

long_query_time = 3

slow_query_log_file = /mysql/log/mysql_master-slow.log

sync_binlog = 1

##########################################

从机配置/etc/my.cnf (slave)

[mysqld]

################## base ##################

basedir = /usr/local/mysql

datadir = /mysql/data

port = 3306

server_id = 1

socket = /var/lib/mysql/mysql.sock

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER

user = mysql

##########################################

################# general ################

bulk_insert_buffer_size = 16M

character_set_server = utf8

event_scheduler = 1

explicit_defaults_for_timestamp = 1

join_buffer_size = 16M

interactive_timeout = 1800

lock_wait_timeout = 60

max_allowed_packet = 64M

max_connections = 256

max_heap_table_size = 64M

skip_name_resolve = 1

sort_buffer_size = 16M

table_open_cache = 3000

tmp_table_size = 32M

transaction-isolation = READ-COMMITTED

wait_timeout = 1800

symbolic-links = 0

##########################################

################# myisam #################

key_buffer_size = 16M

myisam_sort_buffer_size = 32M

##########################################

################# innodb #################

innodb_buffer_pool_size = 2G

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_checksum_algorithm = crc32

innodb_checksums = 1

innodb_file_per_table = 1

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_flush_method = O_DIRECT

innodb_lock_wait_timeout = 30

innodb_log_buffer_size = 32M

innodb_log_file_size = 256M

innodb_sort_buffer_size = 32M

##########################################

################### log ##################

log_bin = /mysql/log/mysql_slave.bin

binlog_format = ROW

expire_logs_days = 10

log_bin_trust_function_creators = 1

log-error = /mysql/log/mysql_slave-err.log

slow_query_log = 1

long_query_time = 3

slow_query_log_file = /mysql/log/mysql_slave-slow.log

##########################################

##########SLAVE###########################

replicate-do-db=my_database #只复制的数据库

log-slave-updates=ON #本地也写二进制日志

relay_log_recovery = 1 #崩溃后放弃已经复制的中继日志，从主机重新获取，防止损坏的中继日志被执行

read-only = 1 #除了从主机复制的更新和具有super权限用户的更新外其他用户不能修改数据

skip_slave_start = 1 #从机崩溃或者重启后复制不自动重启

#slave-skip-errors=all

master_info_repository = TABLE

relay_log_info_repository = TABLE

relay_log_purge = 1

relay_log = /mysql/log/mysql_slave_relay.log

#sync_binlog = 1

##########################################

2.01 同步一台数据库my_database 操作。

一、先使用mysqldump备份my_database 数据库

#mysqldump -E -v -R -B my_database > my_database_bak.sql

二、恢复到主机的服务器上

#mysql --comment my_database < my_database_bak.sql

三、从机数据部署问题，保证数据一致性使用 --single-transaction

#mysqldump --single-transaction --master-data=2 -E -v -R -B my_database > my_database_bak.sql

注：因为my_database里面使用的都是innodb存储引擎，所以使用--single-transaction 和 master-data。

master-data 和 --lock-table 是冲突的，因为它会自动打开 --lock-all-tables ,如果加上--single-transaction

那么就能保证锁很短的时间，并保证数据一致性。对于--master-data 默认值为1 表示恢复备份会影响二进制的位置

如果等于，那么在备份文件头change master to master_log = '' , master_position=''；语句会以注释的形式存在。

如下：

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_master.000003', MASTER_LOG_POS=33879657;

-- Current Database: `my_database`

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `my_database` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `my_database`;

以此位置配置从机

首先恢复备份到从机服务器，然后先在主机创建复制账号：

#mysql> grant replication slave on *.* to username@'IP' identified by 'password';

在从机配置主机连接：

CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='username',MASTER_PASSWORD='password' MASTER_LOG_FILE='mysql_master.000003', MASTER_LOG_POS=33879657;

START SLAVE;

这样从机就会开始追赶被主机落下的部分。简单双机主从搭建完毕

可能出现的问题：

IO 显示 CONNECTING

检查防火墙端口，尝试使用客户端连接验证连接性

可能的故障问题维护：

在复制期间从机发生错误

如果主机和从机同时发生同样的错误，那么错误会被记录，并且从机继续正常执行。

如果主机和从机发生不同的错误，那么需要使用SHOW SLAVE STATUS 确定错误原因，

根据错误原因可以选择--slave-skip-errors 等方式解决错误

如果一条语句在主机执行而没有在从机执行导致从机停机，查看错误，如果可以通过跳过错误的方式解决则使用下面语句：

mysql> STOP SLAVE;

mysql> SET GLOBAL sql_slave_skip_counter = N;

mysql> START SLAVE;

注：这个语句是指从机跳过N个事件，一般为1，指跳过导致从机复制失败的事件，使用这个语句一定要从机停止，否则会报错。

使用这个语句，要理解二进制日志实际上是以一个组序列作为事件组，每个事件组由一个时间序列组成，

对于事务表，每个组相当于一个事务，对于非事务表，每个事件组相当于一个sql语句

所有使用这个语句跳过的单位实际上是事件组。一定会跳过一个完整的事件，而不可能出现跳过一半。

综合手册来看，解决主从复制错误有两种方式：

一、重新搭建从节点

二、跳过当前错误

如果一切配置没有问题那么按常理来说是不应该出现错误的，如果出现错误，那么很大可能是可跳过的，这个要根据实际错误内容判断。

主从配置主机上的参数参考：

1 、server_id 在主从上范围1 to 2^32 − 1. 0为默认值，表示拒绝主（在从配置），或者拒绝从（在主配置），要求主从server_id 要唯一

2、server_uuid 一般在data_dir/auto.cnf 目录记录，安装mysql一般会存在

3、enforce-gtid-consistency GTID模式下，禁止不安全语句的执行，譬如create table .....select

4、auto_increment_increment 自增id的步长

5、auto_increment_offset 自增id的起始值 auto_increment_offset + N × auto_increment_increment

主从配置从机上的参数参考：

1、--log-slave-updates 从机在本地记录二进制日志，如果从机作为下一个从节点的主机时这个是必要的。

并且本身也要配置二进制相关参数

2、log_slow_slave_statements 当慢查询日志开启，这个参数是记录在slave上查询时间超过query_long_time的语句

3、--master-info-file=file_name 用于从机记录主机信息的文件

4、--master-retry-count=count 从机试图连接到主机的次数默认86400次

5、--max-relay-log-size=size 每个中继日志的大小，如果非0，则按照配置大小来切换产生新的中继日志文件，默认值是0，按照max_binlog_size的大小来控制中继日志每个文件的大小

6、--read-only （重要）只允许具有super权限和复制账号来更新从数据库，其他账户均不允许更改。保证从机只读

7、--relay-log=file_name 中继日志的配置，默认数据路径，可以指定路径和名称

8、--relay-log-index=file_name 中继日志索引文件名称

9、--relay-log-info-file=file_name 从机记录有关中继日志的信息的文件名称。

10、--relay-log-purge={0|1} 一旦他们不再需要，禁用或启用自动清除中继日志

11、--relay-log-recovery={0|1}跟随服务器启动立即启用自动中继日志恢复。恢复过程将创建一个新的中继日志文件、初始化SQL线程位置在新的中继日志，并初始化I/O线程到SQL线程位置。从主然后继续的中继日志的阅读。这应该是在复制从机的崩溃后使用，以确保没有可能损坏的继电器日志被处理。默认值为0（禁用）。

此选项应该启用（设置为1），--relay-log-inforepository必须设置TABLE，并必须启用relay-log-recovery。启用relay-log-recovery选项时而不启用--relay-log-purge，存在从不被清除的文件读取中继日志，导致数据的不一致性，因此是不安全。所以应该 --relay-log-recovery=1， --relay-log-inforepository = TABLE ，--relay-log-purge =1 同时启用

12、--relay-log-space-limit=size 此选项将限制在从机所有中继日志的总大小的上限。默认0 表示“没有限制”，从机的磁盘大小有限时候比较有用。当达到极限时，I/O线程停止读取二进制日志事件从主服务器，直到SQL线程已经赶上并删除一些不用的中继日志。注意，这个限制不是绝对的：在有些情况下，SQL线程需要更多的事件才可以删除中继日志。在这种情况下，I/O线程超过限制直到它SQL线程执行后允许删除一些中继日志，因为不这样做会导致死锁。你不应该设置--relay-log-space-limit 小于--max-relay-log-size的两倍大小。(或者 --max-binlog-size 如果--max-relaylog-size 是 0).

13、--replicate-do-db=db_name

重点：这个和二进制格式息息相关，这里只讨论RBR

在RBR 模式下只需要知道在replicate-do-db=db1的情况下对db1有影响的都会被更新到从机，无论use db切换到什么数据库

譬如 use db2； update db1.table set id = N where name = N; RBR会更新从机，SBR不会更新从机

14、--replicate-ignore-db=db_name 理解同上，具体SBR看官方文档。受USE DB影响较大

15、--replicate-ignore-table=db_name.tbl_name

16、--replicate-do-table=db_name.tbl_name

17、--replicate-rewrite-db=from_name->to_name

18、--replicate-same-server-id 默认为0

19、--replicate-wild-do-table=db_name.tbl_name

20、 --replicate-wild-ignore-table=db_name.tbl_name

以下这些信息报告给主机，在主机上SHOW SLAVE HOSTS;可以查看

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

21、--report-host=host_name 把从机的主机名报告给主机，大体上是应该用主机名代替ip注册主机

22、--report-password=password

23、--report-port=slave_port_num

24、--report-user=user_name

25、--show-slave-auth-info

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

26、--show-slave-auth-info

27、--slave-checkpoint-group=#

28、--slave-checkpoint-period=#

29、--slave-parallel-workers 多线程复制，在多库情况下并发，单库还是单线程

30、--slave-pending-jobs-size-max=#

31、--skip-slave-start 从机服务器重启后slave 不会自动开始，要手动执行，防止出错

32、--slave_compressed_protocol={0|1} 对于从机和主机协议压缩，暂时用不到

33、--slave-load-tmpdir=file_name

34、slave-max-allowed-packet=bytes

35、--slave-net-timeout=seconds 当从机超过这个时间没有获取到数据则认为已经断开连接，并尝试重新连接

36、slave-rows-search-algorithms=list

37、--slave-skip-errors=[err_code1,err_code2,...|all|ddl_exist_errors] 从机跳过指定类型的错误，一般不建议配置，如果没问题，不配置就可以正常，如果有问题，那么就要知道排错后手动跳过