Mysql主从同步配置

1. mysql主从同步定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

使用主从同步的好处：

• 1.通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
• 2.提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
• 3.在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

1.2 什么是 Binlog

MySQL 的二进制日志可以说 MySQL 最重要的日志了，它记录了所有的 DDL 和 DML(除 了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。

一般来说开启二进制日志大概会有 1%的性能损耗。二进制有两个最重要的使用场景:

• 其一：MySQL Replication 在 Master 端开启 Binlog，Master 把它的二进制日志传递给 Slaves 来达到 Master-Slave 数据一致的目的。

• 其二：自然就是数据恢复了，通过使用 MySQL Binlog 工具来使恢复数据。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有 的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的 DDL 和 DML(除 了数据查询语句)语句事件。

1.2 Binlog 的分类

MySQL Binlog 的格式有三种，分别是 STATEMENT,MIXED,ROW。在配置文件中可以选择配 置 binlog_format= statement|mixed|row。三种格式的区别：

1）statement：语句级，binlog 会记录每次一执行写操作的语句。相对 row 模式节省空间，但是可能产生不一致性，比如“update tt set create_date=now()”，如果用 binlog 日志 进行恢复，由于执行时间不同可能产生的数据就不同。

• 优点：节省空间。
• 缺点：有可能造成数据不一致。

2）row：行级， binlog 会记录每次操作后每行记录的变化。

• 优点：保持数据的绝对一致性。因为不管 sql 是什么，引用了什么函数，他只记录 执行后的效果。

• 缺点：占用较大空间。

3）mixed：statement 的升级版，一定程度上解决了，因为一些情况而造成的 statement 模式不一致问题，默认还是 statement，在某些情况下譬如：当函数中包含 UUID() 时；包含 AUTO_INCREMENT 字段的表被更新时；执行 INSERT DELAYED 语句时；用 UDF 时；会按照 ROW 的方式进行处理

• 优点：节省空间，同时兼顾了一定的一致性。

• 缺点：还有些极个别情况依旧会造成不一致，另外 statement 和 mixed 对于需要对 binlog 的监控的情况都不方便。

1.3 主从同步机制

• 1、slave 服务器执行 start slave，开启主从复制开关， slave 服务器的 IO 线程请求从 master 服务器读取 binlog（如果该线程追赶上了主库，会进入睡眠状态）。
• 2、master 服务器的更新SQL(update、insert、delete)被写到binlog, 主库的 binlog dump thread，把 bin log 的内容发送到从库。
• 3、从库启动之后，创建一个 I/O 线程，读取主库传过来的 bin log 内容并写到 relay log（会记录位置信息，以便下次继续读取）。
• 3、slave 服务器的 SQL 线程会实时检测 relay log 中新增的日志内容，把 relay log解析成 SQL 语句，并执行。

2. 配置主从同步

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

1．在主服务器上，必须开启二进制日志机制和配置一个独立的ID

2．在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号

3．在开始复制进程前，在主服务器上记录二进制文件的位置信息

4．如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）

5．配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置

2.1 配置主服务器

• 1.更改配置文件，首先检查你的主服务器上的my.cnf文件中是否已经在[mysqld]模块下配置了log-bin和server-id

[mysqld]
log-bin=mysql-bin
server-id=1
## 下面可选
binlog_format=ROW          #复制模式
max_binlog_size=100M         #超过max_binlog_size或超过6小时会切换到下一序号文件
binlog_cache_size       = 16M
log_bin=/var/lib/mysql/mysql-bin.log   #默认路径可修改
expire_logs_days= 7                           #日志过期时间，设置为0则永不过期
binlog_cache_size=16M           #二进制日志缓冲大小,通过show status like 'binlog_%';查看调整写入磁盘的次数，写入磁盘为0最好max_binlog_cache_size   = 256M
relay_log_recovery  = 1            #当slave从库宕机后，假如relay-log损坏了，
#导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，
#并且重新从master上获取日志，这样就保证了relay-log的完整性。
sync_binlog= 1            #二进制日志（binary log）同步到磁盘的频率
innodb_flush_log_at_trx_commit = 1     #每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。

注意上面的log-bin和server-id的值都是可以改为其他值的，如果没有上面的配置，首先关闭mysql服务器，然后添加上去，接着重启服务器

• 2.创建用户，每一个从服务器都需要用到一个账户名和密码来连接主服务器，可以为每一个从服务器都创建一个账户，也可以让全部服务器使用同一个账户。下面就为同一个ip网段的所有从服务器创建一个只能进行主从同步的账户。

首先登陆mysql，然后创建一个用户名为rep，密码为123456的账户，该账户可以被192.168.253网段下的所有ip地址使用，且该账户只能进行主从同步

mysql > grant replication slave on *.* to ‘rep’@‘192.168.253.%’ identified by ‘123456’;
mysql> FLUSH PRIVILEGES;

3.获取二进制日志的信息并导出数据库，步骤：

首先登陆数据库，然后刷新所有的表，同时给数据库加上一把锁，阻止对数据库进行任何的写操作

mysql > flush tables with read lock;

然后执行下面的语句获取二进制日志的信息

mysql > show master status;

File的值是当前使用的二进制日志的文件名，Position是该日志里面的位置信息（不需要纠结这个究竟代表什么），记住这两个值，会在下面配置从服务器时用到。

注意：如果之前的服务器并没有配置使用二进制日志，那么使用上面的sql语句会显示空，在锁表之后，再导出数据库里的数据（如果数据库里没有数据，可以忽略这一步）

[root@localhost backup]# mysqldump -uroot -p'123456' -S /data/3306/data/mysql.sock --all-databases > /server/backup/mysql_bak.$(date +%F).sql

如果数据量很大，可以在导出时就压缩为原来的大概三分之一

[root@localhost backup]# mysqldump -uroot -p'123456' -S /data/3306/data/mysql.sock --all-databases | gzip > /server/backup/mysql_bak.$(date +%F).sql.gz

这时可以对数据库解锁，恢复对主数据库的操作

mysql > unlock tables;

2024.5.9 修改

mysqldump 命令会锁表，所以不用一开始就 锁全数据库
命令：

mysqldump -uroot -p123 -A -B -F --master-data=2  >/tmp/full.sql

–master-data=2 会保存 Position 到文件

参数	参数说明
-A	备份全库
-B	备某一个数据库下的所有表
-R, --routines	备份存储过程和函数数据
–triggers	备份触发器数据
–master-data={1|2}	告诉你备份后时刻的binlog位置如果等于1，则将其打印为CHANGE MASTER命令; 如果等于2，那么该命令将以注释符号为前缀。
–single-transaction	对innodb引擎进行热备
-F, --flush-logs	刷新binlog日志
-x, --lock-all-tables	锁定所有数据库的所有表。这是通过在整个转储期间采用全局读锁来实现的。
-l, --lock-tables	锁定所有表以供读取
-d	仅表结构
-t	仅数据
–compact	减少无用数据输出(调试)

参考： Mysql 备份恢复 mysqldump与xtrabackup备份

2.2 配置从服务器

首先检查从服务器上的my.cnf文件中是否已经在[mysqld]模块下配置leserver-id

[mysqld]

server-id=2
## 忽略表
replicate-wild-ignore-table=mysql.*
replicate-wild-ignore-table=sys.*

注意上面的server-id的值都是可以改为其他值的（建议更改为ip地址的最后一个字段），如果没有上面的配置，首先关闭mysql服务器，然后添加上去，接着重启服务器

如果有多个从服务器上，那么每个服务器上配置的server-id都必须不一致。从服务器上没必要配置log-bin，当然也可以配置log-bin选项，因为可以在从服务器上进行数据备份和灾难恢复，或者某一天让这个从服务器变成一个主服务器

如果主服务器导出了数据，下面就导入该文件，如果主服务器没有数据，就忽略这一步

[root@localhost ~]# mysql -uroot -p'123456' -S /data/3306/data/mysql.sock < /server/backup/mysql_bak.2015-07-01.sql

如果从主服务器上拿过来的是压缩文件，就先解压再导入

配置同步参数，登陆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='POS',

如图所示：

启动主从同步进程

mysql > start slave;

检查状态

mysql > show slave status \G

上面的两个进程都显示YES则表示配置成功

3 使用主从同步来备份

把主服务器的数据复制到从服务器上，然后备份从服务器的数据，在数据量不是很大的时候使用mysqldump命令，对于很大的数据库，就直接备份数据文件。

3.1 使用mysqldump来备份

步骤：（以下的所有操作都在从服务器上进行）

1.首先暂停从服务器的复制进程

shell > mysqladmin stop-slave

或者只是暂停SQL进程（从服务器仍然能接收二进制日志的事件，但不会执行这些事件，这样能在重启SQL进程时加快复制进度）

shell > mysql -e ‘stop slave sql_thread;’

2.使用mysqldump导出全部或部分的数据库

shell > mysqldump --all-databases > fulldb.dump

3.在导出数据库后，重启复制进程

shell > mysqladmin start-slave

3.2 备份原始文件

为了保证数据文件的完整性，在备份之前首先关闭从服务器，步骤：

1.关闭从服务器：

shell > mysqladmin shutdown

2.复制数据文件，可以使用压缩命令，假如当前目录就是数据库的数据目录（在my.cnf文件中的配置项datadir的值就是该目录的位置）

shell > tar cf /tmp/dbbackup.tar ./data

3.然后再启动mysql服务器

4 主从同步的小技巧

主服务器第一次导入数据，如果你从其他地方拿来了要导入到主服务器中的数据，此时只要在主服务器中导入一次即可，因为这些数据会自动发送到从服务器中，在主服务器上使用命令

shell > mysql -h master < other_data.sql

增加从服务器，本来已经至少有一个从服务器时（暂时命名为slave1），决定再添加其余的从服务器（slave2），此时就不需要像上面那样去操作主服务器，只要复制一个已经存在的从服务器就可以了

5.排错

Slave_IO_Running: NO

这是一个很常见的错误（我也曾对这个错误咬牙切齿），总结起来就三个原因：

1. 主服务器的网络不通，或者主服务器的防火墙拒绝了外部连接3306端口
2. 在配置从服务器时，输错了ip地址和密码，或者主服务器在创建用户时写错了用户名和密码
3. 在配置从服务器时，输错了主服务器的二进制日志信息

排错过程：（主服务器ip：192.168.1.139，从服务器ip：192.168.1.204）

第0步就是检查错误日志，如果不能快速排错，可以按我的步骤试试：

1．首先在从服务器上执行ping程序，确定能ping通主服务器

在从服务器上执行mysq的远程连接

[root@slave204 log]# mysql -urep -p -h 192.168.1.139 -P3306

如果显示ERROR 1045 (28000): Access denied for user ‘test’@‘192.168.1.204’ (using password: YES)则跳转到第3

2．登陆主服务器的mysql，查看所有的用户

mysql > select user,host from mysql.user;

上图就是我的错误根源，可以看到用户名完全写错了，先删除错误的用户：

mysql > drop user “rep@192.168.1.%”@”%”;

再重新创建用户：

mysql > grant replication slave on *.* to ‘rep’@‘192.168.1.%’ identified by ‘123456’;

mysql > flush privileges;

3．假如用户名没有错，那么如何排除是否是输入的密码错误呢？

额，我也想知道方法。最好就是多输入几遍，或者重新创建用户名和密码来测试。问题还没有解决，转到4

4．在你的防火墙中添加3306端口

[root@localhost mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent

[root@localhost mysql]# firewall-cmd --reload

再关闭selinux

[root@slave204 log]# vi /etc/sysconfig/selinux

把SELINUX=enforcing改为SELINUX=disabled

[root@slave204 log]# source /etc/sysconfig/selinux

登录主服务器，查看服务器状态

mysql > show master status \G

然后重新配置一次从服务器，在配置之前首先关闭主从同步进程

mysql > stop slave;

Slave_SQL_Running: No

把上面的Slave_IO_Running调试成YES后，就轮到这个小样了。我根据这个博客的内容来解决的：http://kerry.blog.51cto.com/172631/277414

MySQL复制类型及应用选项

MySQL 复制可以分为以下几种类型：

默认的复制是异步复制，而很多新同学因为不了解 MySQL 除了异步复制还有其他复制的类型，所以错误地在业务中使用了异步复制。为了解决这个问题，我们一起详细了解一下每种复制类型，以及它们在业务中的选型，方便你在业务做正确的选型。

异步复制

在异步复制（async replication）中，Master 不用关心 Slave 是否接收到二进制日志，所以 Master 与 Slave 没有任何的依赖关系。你可以认为 Master 和 Slave 是分别独自工作的两台服务器，数据最终会通过二进制日志达到一致。

异步复制的性能最好，因为它对数据库本身几乎没有任何开销，除非主从延迟非常大，Dump Thread 需要读取大量二进制日志文件。

如果业务对于数据一致性要求不高，当发生故障时，能容忍数据的丢失，甚至大量的丢失，推荐用异步复制，这样性能最好（比如像微博这样的业务，虽然它对性能的要求极高，但对于数据丢失，通常可以容忍）。但往往核心业务系统最关心的就是数据安全，比如监控业务、告警系统。

半同步复制

半同步复制要求 Master 事务提交过程中，至少有 N 个 Slave 接收到二进制日志，这样就能保证当 Master 发生宕机，至少有 N 台 Slave 服务器中的数据是完整的。

半同步复制并不是 MySQL 内置的功能，而是要安装半同步插件，并启用半同步复制功能，设置 N 个 Slave 接受二进制日志成功，比如：

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"rpl-semi-sync-master-enabled = 1rpl-semi-sync-slave-enabled = 1rpl_semi_sync_master_wait_no_slave = 1

上面的配置中：

• 第 1 行要求数据库启动时安装半同步插件；
• 第 2、3 行表示分别启用半同步 Master 和半同步 Slave 插件；
• 第 4 行表示半同步复制过程中，提交的事务必须至少有一个 Slave 接收到二进制日志。

在半同步复制中，有损半同步复制是 MySQL 5.7 版本前的半同步复制机制，这种半同步复制在Master 发生宕机时，Slave 会丢失最后一批提交的数据，若这时 Slave 提升（Failover）为Master，可能会发生已经提交的事情不见了，发生了回滚的情况。

有损半同步复制原理如下图所示：

可以看到，有损半同步是在 Master 事务提交后，即步骤 4 后，等待 Slave 返回 ACK，表示至少有 Slave 接收到了二进制日志，如果这时二进制日志还未发送到 Slave，Master 就发生宕机，则此时 Slave 就会丢失 Master 已经提交的数据。

而 MySQL 5.7 的无损半同步复制解决了这个问题，其原理如下图所示：

从上图可以看到，无损半同步复制 WAIT ACK 发生在事务提交之前，这样即便 Slave 没有收到二进制日志，但是 Master 宕机了，由于最后一个事务还没有提交，所以本身这个数据对外也不可见，不存在丢失的问题。

所以，对于任何有数据一致性要求的业务，如电商的核心订单业务、银行、保险、证券等与资金密切相关的业务，务必使用无损半同步复制。这样数据才是安全的、有保障的、即使发生宕机，从机也有一份完整的数据。

多源复制

无论是异步复制还是半同步复制，都是 1 个 Master 对应 N 个 Slave。其实 MySQL 也支持 N 个 Master 对应 1 个 Slave，这种架构就称之为多源复制。

多源复制允许在不同 MySQL 实例上的数据同步到 1 台 MySQL 实例上，方便在 1 台 Slave 服务器上进行一些统计查询，如常见的 OLAP 业务查询。

多源复制的架构如下所示：

上图显示了订单库、库存库、供应商库，通过多源复制同步到了一台 MySQL 实例上，接着就可以通过 MySQL 8.0 提供的复杂 SQL 能力，对业务进行深度的数据分析和挖掘。

延迟复制

前面介绍的复制架构，Slave 在接收二进制日志后会尽可能快地回放日志，这样是为了避免主从之间出现延迟。而延迟复制却允许Slave 延迟回放接收到的二进制日志，为了避免主服务器上的误操作，马上又同步到了从服务器，导致数据完全丢失。

我们可以通过以下命令设置延迟复制：

CHANGE MASTER TO master_delay = 3600

这样就人为设置了 Slave 落后 Master 服务器1个小时。

延迟复制在数据库的备份架构设计中非常常见，比如可以设置一个延迟一天的延迟备机，这样本质上说，用户可以有 1 份 24 小时前的快照。

那么当线上发生误操作，如 DROP TABLE、DROP DATABASE 这样灾难性的命令时，用户有一个 24 小时前的快照，数据可以快速恢复。

对金融行业来说，延迟复制是你备份设计中，必须考虑的一个架构部分。

总结

看到这里的同学，你一定会对 MySQL 复制技术有一个清晰的了解，认识到复制是数据同步的基础，而二进制日志就是复制的基石。总结一下今天的重点：

1. 二进制日志记录了所有对于 MySQL 变更的操作；
2. 可以通过命令 SHOW BINLOG EVENTS IN ... FROM ... 查看二进制日志的基本信息；
3. 可以通过工具 mysqlbinlog 查看二进制日志的详细内容；
4. 复制搭建虽然简单，但别忘记配置 crash safe 相关参数，否则可能导致主从数据不一致；
5. 异步复制用于非核心业务场景，不要求数据一致性；
6. 无损半同步复制用于核心业务场景，如银行、保险、证券等核心业务，需要严格保障数据一致性；
7. 多源复制可将多个 Master 数据汇总到一个数据库示例进行分析；
8. 延迟复制主要用于误操作防范，金融行业要特别考虑这样的场景。