Mysql主从同步基本概念与配置方法

菠萝油侠

已于 2022-11-11 17:06:59 修改

阅读量443

点赞数 2

分类专栏： Mysql数据库文章标签： mysql 数据库服务器

于 2022-11-11 15:51:48 首次发布

原文链接：https://blog.csdn.net/agonie201218/article/details/121499881

版权

Mysql数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

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

注意上面的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;

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

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

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

排错过程：（主服务器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

上面的配置中：