mysql 主从复制

MySQL复制概述

简单来说就是保证主服务器（Master）和从服务器（Slave）的数据是一致性的，向Master插入数据后，Slave会自动从Master把修改的数据同步过来（有一定的延迟），通过这种方式来保证数据的一致性，就是Mysql复制

Mysql 复制能解决什么问题

一、高可用和故障切换

复制能够帮避免MySql单点失败，因为数据都是相同的，所以当Master挂掉后，可以指定一台Slave充当Master继续保证服务运行，因为数据是一致性的（如果当插入Master就挂掉，可能不一致，因为同步也需要时间），当然这种配置不是简单的把一台Slave充当Master，毕竟还要考虑后续的Salve同步Master

二、负载均衡

因为读写分离也算是负载均衡的一种，所以就不单独写了，因为一般都是有多台Slave的，所以可以将读操作指定到Slave服务器上（需要代码控制），然后再用负载均衡来选择那台Slave来提供服务，同时也可以吧一些大量计算的查询指定到某台Slave，这样就不会影响Master的写入以及其他查询

三、数据备份

一般我们都会做数据备份，可能是写定时任务，一些特殊行业可能还需要手动备份，有些行业要求备份和原数据不能在同一个地方，所以主从就能很好的解决这个问题，不仅备份及时，而且还可以多地备份，保证数据的安全

四、业务模块化

可以一个业务模块读取一个Slave，再针对不同的业务场景进行数据库的索引创建和根据业务选择MySQL存储引擎， 不同的slave可以根据不同需求设置不同索引和存储引擎

主从配置需要注意的点

(1)主从服务器操作系统版本和位数一致；

(2) Master和Slave数据库的版本要一致；

(3) Master和Slave数据库中的数据要一致；

(4) Master开启二进制日志，Master和Slave的server_id在局域网内必须唯一；

复制如何工作

复制有三个步骤：

1、Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events)

2、Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)

3、Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)

第一步Master记录二进制日志，每次提交事务完成数据更新前mysql备份，Master将数据更新的时间记录到二进制日志中，MySql会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。再记录二进制日志后，主库会告诉存储引擎可以提交事务了。

第二步，Slave将Master的二进制日志复制到本地的中继日志中，首先，Slave会启动一个工作线程，成为I/O线程， I/O线程跟Master建立一个普通的客户端链接，然后再Master上启动一个特殊的二进制转储（binlog dump）线程（该线程没有对应的SQL命令），这个二进制转储线程会读取主库上的二进制日志中的事件。从库I/O线程将接受到时间记录到中继日志中。

第三步从库的SQL线程执行最后异步，该线程的从中继日志中读取事件并在从库执行，从而实现从库数据更新。

这种复制架构实现了获取事件和重放事件的解偶，允许这两个过程异步进行。也就是说I/O线程能够独立于SQL线程之外工作。但这种架构页限制了复制的过程，其中最重要的一点是主库上并发运行的查询再从库只能串行化执行，因为只有一个SQL线程重放中继日志中的事件。这是很多工作负载的性能瓶颈所在。因为始终受限于单线程。

复制类型

1、基于语句的复制

在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制

2、基于行的复制

把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持

3、混合类型的复制

默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制

相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。

启动多个Mysql实例

要配置主从复制，我们在本机开多个Mysql实例来操作就可以了，让他们监听不同端口

多开实例可以看我另一篇教程：同一台Ubuntu 启动多个mysql

主从复制配置

现在我们两个实例Mysql

server1 : 127.0.0.1 3306 master

server2 : 127.0.0.1 3301 slave

配置master

在主库创建一个复制帐号，这个帐号是给从库的IO线程建立连接到主库时用的，从库会用这个帐号连接主库并读取主库的二进制日志：

grant replication slave, replication client on *.* to 'repl'@'localhost' identified by '123456';

主库添加配置：

# 设置server_id，一般设置为IP, 要独一无二的

server-id = 10

# 开启二进制日志功能，最好是绝对路径

log_bin = /var/log/mysql/mysql-bin.log

# 主从复制的格式（mixed,statement,row，默认格式是statement）

binlog_format=mixed

# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

expire_logs_days=7

# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存

binlog_cache_size=1M

# 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）

binlog-ignore-db=mysql

启用二进制日志后，重启后， show master status; 可以看到二进制相关信息

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000008 | 107 | | |

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

添加从库配置

# 设置server_id，一般设置为IP, 要独一无二的

server-id = 10

log_bin = /var/log/mysql/mysql-bin.log

# 中继日志路径

relay_log = /home/mysql/3301/mysql-relay-bin

# 允许从库将其重放的事件也记录到自身的二进制日志中

log_slave_updates = 1

read_only = 1

从库开启复制

mysql> CHANGE MASTER TO

MASTER_HOST='$host',

MASTER_USER='repl',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='msyql-bin.00001',

MASTER_LOG_POS=0;

MASTER_LOG_POS设为0，是从日志开头开始复制，MASTER_LOG_FILE是master的二进制文件

# 启动复制

mysql> start slave;

# 查看复制状态

mysql> show slave status;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 107

Relay_Log_File: mysql-relay-bin.000020

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

Master_Server_Id: 10

# Slave_IO_Running: Yes，Slave_SQL_Running: Yes 说明同步正常进行

# Seconds_Behind_Master: 0 就是完全同步了

这时就完成了主从复制的配置，当主服务器有更新，从库也会更新。

我们还可以从线程列表看出复制线程，主库上可以看到由从库I/O线程向主库发起的连接。

mysql> show processlist \G

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

Id: 44

User: repl

Host: localhost:32866

db: NULL

Command: Binlog Dump

Time: 73032

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

同样，我们看看从库的线程，有两个，一个I/O线程，一个SQL线程：

这两个线程都是再system user 帐号下运行，I/O线程是写日志到中继日志的线程， SQL线程是重放SQL的线程。

从已经运行已久的服务器开始复制

那么，至此我们已经完成了Mysql的主从配置。

但是上面是配置两台刚好安装号的服务器，数据相同，并且知道当前主库二进制日志。

更典型的案例是，一个运行已经一段时间的主库，要用一台新安装的从库与之同步，此时这台从库还没有数据。

所以我们得想办法，线初始化从库： 从主库复制数据、使用最近依次备份来启动从库。

这需要三个条件来让主库和从库保持同步：

复制数据到从库

mysqldump --single-transaction --all-databases --master-data=1 -uroot -p123456|mysql -S /home/mysql/33

备份

(1)备份计划

视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。

100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。

(2)备份恢复时间

物理备份恢复快，逻辑备份恢复慢 这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考 20G的2分钟（mysqldump） 80G的30分钟(mysqldump) 111G的30分钟（mysqldump) 288G的3小时（xtra) 3T的4小时（xtra) 逻辑导入时间一般是备份时间的5倍以上

(3)备份恢复失败如何处理

首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。

数据库备份

1、使用mysqldump命令备份

Mysqldump是MySQL提供的一个非常有用的数据备份工具，执行MySQLdump命令时，可以将数据备份成一个文本文件。

对整个数据库备份语法为：mysqldump -u username -p dbname >filename.sql

例如：mysqldump -u root -p feng_test > d:feng.sql

对某张表备份语法为：mysqldump -u username -p dbname tablename > filename.sql

备份多个数据库：mysqldump -u username -p --databases booksdb test > filename.sql（备份booksdb和test数据库）

另外--all-databases参数可以备份系统中所有的数据库

还原备份：mysql -u username -p dbname < filename.sql

如果已经登录了就可以使用：source filaname.sql

2、直接复制数据文件

3、Mysqlhotcopy快速备份与恢复

该方法是备份数据库或单个表的最快途径，但只能备份MYISAM类型的表！

如 将test数据库备份到/user/backup目录下：

Mysqlhotcopy -u root -p test /user/backup

恢复数据：

如 cp -r /usr/backup/test usr/local/mysql/data

mysqldump

mysqldump 属于逻辑备份。加入—single-transaction 选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)， 之后显式开启一个事务(START TRANSACTION /!40100 WITH CONSISTENTSNAPSHOT /)，这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。 如果加上—master-data=1 的话，在刚开始的时候还会加一个数据库的读锁 (FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(showmaster status)，马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务

Xtrabackup

xtrabackup 属于物理备份，直接拷贝表空间文件，同时不断扫描产生的 redo 日志并保存下来。最后完成 innodb 的备份后，会做一个 flush engine logs 的操作(老版本在有 bug，在5.6 上不做此操作会丢数据)，确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交 概念，因为 xtrabackup 并不拷贝 binlog，所以必须保证所有的 redo log 都落盘，否则可能会丢最后一组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点，数据文件虽然不是一致性的，但是有这段时间的 redo 就可以让数据文件达到一致性(恢复的时候做的事 情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，备份完后解锁。 这样就做到了完美的热备。

（编辑：源码门户网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!