mysql的主从复制如何配置

共计 6048 个字符，预计需要花费 16 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家分享的是有关 mysql 的主从复制如何配置的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考。一起跟随丸趣 TV 小编过来看看吧。

数据库复制对于系统高可用、高性能的提升扮演者很重要的角色。

1 主库配置 1.1 my.cnf 配置：

在主库配置文件 my.cnf 中进行如下基本配置：

log-bin = mysql-bin // 二进制日志文件名称主体
log-bin-index = mysql-bin.index // 二进制日志文件索引文件
server-id = 1 // 唯一的服务器 ID，为了保持唯一性，可以去 ip 的尾部
binlog-format = mixed // 控制复制基于的方式，有基于语句(statement)，基于行(row)，混合(mixed)，** 主从库需要保持一致 **
#sync_binlog=1 // 推荐配置，开启该选项，mysql 每次在事务提交前会将二进制日志同步到磁盘上，保证在服务器崩溃时不会丢失事件。

默认复制全部数据库，如果需要指定数据库，请参照第 7 节（复制过滤）。

比如说要指定 db1 和 db2 两个数据库进行主从复制：binlog-do-db = db1
binlog-do-db = db2

1.2 添加复制账户：

复制账户添加以及权限设置：

mysql grant replication slave, replicatin client on \*.\* to repl@ 172.16.226.192 identified by repl123456 // 其中 repl 是用户名，repl123456 为账户密码，172.16.226.168 为备库的地址。mysql flush privileges; // 在不重启 mysql 服务的情况下完成权限的更新

2 备库配置

在备库配置文件 my.cnf 中进行如下基本配置：

relay-log = slave-relay-bin // 中继日志文件名称主体
relay-log-index = slave-relay-bin.index // 中继日志文件索引文件
server-id = 2 // 唯一的服务器 ID，必须要异于主库
#read_only = 1 // 限制备库为只读，可选
log_slave_updates = 1 // 控制是否在中继日志执行之后，将同步过来的数据添加到自己的 binlog 中去，1 代表是
skip_slave_start // 该选项能够阻止备库在崩溃后自动启动复制，建议开启
即使开启了建议的选项，备库仍然可能在崩溃后被中断，因为 master.info 和中级日志文件都不是崩溃安全的，所以建议开启一下选项：sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1

同样可以过滤待同步的数据库，或者表，参考复制过滤一节。

3 数据库远程备份

数据库远程备份可以选择 mysqldump（逻辑备份）进行热备，但对于数据量较大时会比较慢，Xtrabackup（物理备份）也可以对 mysql 数据库进行热备（这里使用 innobackupex-1.5.1），Xtrabackup 可以实现 innoDB 等数据库的在线备份，速度较快且不影响正常读写。这里对全库进行备份。

3.1 创建备份账户

在主服务器创建用户 backup（使用最小权限），用于数据库备份。

mysql grant reload, lock tables, replication client on \*.\* to backup@ % identified by backup123 
mysql flush privileges; // 在不重启 mysql 服务的情况下完成权限的更新

3.2 数据库完全备份

完全备份和恢复准备两个步骤都是在主库服务器完成。

innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=backup --password=backup123 /mysqlbackup
--defaults-file：选择默认的配置文件
--user 和 --password：分别为进行备份的用户名和密码
/mysqlbackup：目标目录

3.3 恢复准备

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
innobakupex 命令的 –apply-log 选项可用于实现上述功能。如下面的命令：

innobackupex-1.5.1 --apply-log --user=backup --password=backup123 /mysqlbackup/2017-01-11_21-20-57
如果执行正确，其最后输出的几行信息通常如下：xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120407 9:01:36 InnoDB: Starting shutdown...
120407 9:01:40 InnoDB: Shutdown completed; log sequence number 92036620
120407 09:01:40 innobackupex: completed OK!

在实现“准备”的过程中，innobackupex 通常还可以使用 –use-memory 选项来指定其可以使用的内存的大小，默认通常为 100M。如果有足够的内存可用，可以多划分一些内存给 prepare 的过程，以提高其完成速度。

3.4 数据拷贝

将主服务器上准备好的数据库拷贝到从服务器。(当然也可以打包后再拷贝)

scp -r /mysqlbackup/ copyer@192.168.1.192:/data/

3.5 数据恢复

在数据恢复之前首先关闭从服务器 mysql 服务，并从备份文件夹中的 xtrabackup_binlog_info 文件中获取当前正在使用的二进制日志文件，以及备份这一刻为止二进制日志事件的位置。如果 datadir 目录不为空，还需要清空 datadir 目录。
innobackupex 命令的 –copy-back 选项用于执行恢复操作，其通过复制所有数据相关的文件至 mysql 服务器 datadir 目录中来执行恢复过程。innobackupex 通过 backup-my.cnf 来获取 datadir 目录的相关信息（也可以通过 –defaults-file 指定 my.cnf 目录，还要确保 datadir 路径为空）

innobackupex-1.5.1 --copy-back /mysqlbackup
如果执行正确，其输出信息的最后几行通常如下：innobackupex: Starting to copy InnoDB log files
innobackupex: in /backup/2012-04-07_08-17-03 
innobackupex: back to original InnoDB log directory /mydata/data 
innobackupex: Finished copying back files.
120407 09:36:10 innobackupex: completed OK!

请确保如上信息的最后一行出现“innobackupex: completed OK!”。

当数据恢复至 datadir 目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如 mysql，否则，在启动 mysqld 之前还需要事先修改数据文件的属主和属组。如：

chown -R mysql:mysql /var/lib/mysql/

4 主从连接 4.1 开启从数据库

service mysql start

如果开启 mysql 失败，可以通过查看错误日志寻找失败原因。

4.2 建立主从连接

从库通过复制账户连接到主库：（slave 必须处于 stop 状态才能使以下连接生效）

mysql change master to master_host= 192.168.1.208，master_user= repl，master_password= repl123456，master_log_file= mysql-bin.000001（备份时得到的活动日志）,master_log_pos=0（备份时得到的活动日志中事件的位置）;

注：如果这里在进行主从连接的时候一直连不上 master，有一个可能的原因是 my.cnf 配置文件中绑定了本机，即 bind-address = 127.0.0.1，我们要做的就是将其注释掉，否则外部机器是访问不了的。

开启 slave：

mysql start slave;

查看 slave 状态，可以发现 IO 线程和 SQL 线程已处于开启状态，有非常多表征从库连接状态的变量（这些变量同样可以用于设置主从监控），在这里不一一做介绍。

mysql show slave status;
Slave_IO_Running: Yes // 表示 IO 线程运行正常
Slave_SQL_Running: Yes // 表示 SQL 线程运行正常
Seconds_Behind_Master: 0 // 表示在网络条件较好的情况下，从库能够及时同步上主库

4.3 常用监控命令

mysql show processlist\G; // 查看数据库服务线程情况
mysql show master/slave status\G; // 查看主备库状态
mysql flush logs; // 强制轮换 (rotate) 二进制日志，从而得到一个完整的二进制日志文件
mysql show binlog events in 指定二进制日志文件名称 from（从指定位置开始显示）limit（需要显示的事件数量）\G; // 查看 binlog 中事件
mysql show binary logs; // 显示所有的 binlogs
mysql reset master; // 删除所有二进制日志文件并清空索引文件
mysql reset slave; // 删除 slave 上复制用的所有文件重新开始
mysql show slave hosts; // 查看主库所拥有的从库信息

5 从库延迟较大

如果发现从库延迟较大，就需要找到延迟大的原因。参数 innodb_flush_log_at_trx_commit 对 mysql 的写入效率影响较大，有三个取值：

0：每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；1：每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；2：每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；

取 1 时的 IO 耗费最大，虽然一致性和完整性方面效果最好，但是写入效率最低，而这也是导致从库延迟较大的原因（如果服务器配置较高或许会好些）。取 0 时 mysql 写入性能很好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。取 2 时的写入性能也很好，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。

6 混合模式复制

正常情况下使用使用基于语句的复制，而对不安全的语句则切换到基于行的复制。主要有以下几种情况：

该语句调用了：UUID 函数用户自定义函数 CURRENT_USER 或 USER 函数 LOAD_FILE 函数一个语句同时更新了两个或者两个以上含有 AUTO_INCREMENT 列的表语句使用了服务器变量存储引擎不允许使用基于语句的复制，例如，mysql cluster 引擎 7 复制过滤

有时候我们不需要对数据库中所有的库进行复制，或者不想对指定库中的某些表进行复制操作，那么我们就需要对复制进行一定的过滤配置，以达到更合理的复制效果。

1. 基于 master

**binlog-do-db=mysql**：主库只是将指定库 (mysql) 发生的变化记录到二进制日志中。**binlog-ignore-db=mysql**：主库取消将指定库 (mysql) 发生的变化记录到二进制日志中。

2. 基于 slave

针对数据库进行的过滤：

**replicate-do-db=mysql**：从库只是将指定库 (mysql) 发生的变化进行重现。**replicate-ignore-db=mysql**：从库取消将指定库 (mysql) 发生的变化进行重现。针对表进行的过滤：**replicate-wild_do-table=mysql.learn**：从库只是将指定库 (mysql) 中指定表 (learn) 发生的变化进行重现。**replicate-wild_ignore-table=mysql.learn**：从库取消将指定库 (mysql) 中指定表 (learn) 发生的变化进行重现。

以上复制过滤方式乍一看没有问题，其实还是有需要注意的地方。因为这些过滤方式的效果与复制方式有关系。如果是基于语句的复制，binlog-do-db、binlog-ignore-db、replicate-do-db、replicate-ignore-db 与跨库 (如 use 库内和 use 外) 有关系，这一点需要注意。

8 日志清理暴力清理：（没有主从复制的情况下）

1、重启 mysql 服务器即可关闭 bin 日志的记录
2、通过 reset master 命令进行清理

条件清理

如果存在主从复制关系，则应当使用 purge 的方式来清理 bin 日志，语法如下：

purge {master|binary} logs to log_name 
purge {master|binary} logs before date

用户删除列于在指定的日志或日期之前的日志索引中的所有二进制日志，同时这些日志也会从日志索引文件的清单中删除。

eg.
purge master logs to mysql-bin.000005 
purge master logs before 2014-08-30 00:00:00 // 清除指定日期之前的日志
purge master logs before date_sub(now(),Interval 3 day); 清除三天前的日志

定时清理

参数：expire_logs_days
说明：二进制日志自动删除 / 过期的天数。默认值为 0，即没有过期的
示例：expire_logs_days = 5，代表日志的有效时间为 5 天
什么时候会删除过期日志？

每次进行 log flush 的时候会自动删除过期的日志

什么时候会触发 log flush？

1、重启
2、binlog 文件的大小达到了最大限制
3、手动执行 flush logs 命令

感谢各位的阅读！关于 mysql 的主从复制如何配置就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到吧！

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合！