MySQL 主从复制，双机热备

1、主从复制

主从复制原理:

mysql数据库提供了一种主从备份的机制，其实就是把主数据库的所有的数据同时写到备份的数据库中。实现mysql数据库的热备份。

要想实现双机的热备，首先要了解主从数据库服务器的版本的需求。要实现热备mysql的版本都高于3.2。还有一个基本的原则就是作为从数据库的数据版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。

当然要实现mysql双机热备，除了mysql本身自带的REPLICATION功能可以实现外，也可以用Heartbeat这个开源软件来实现主从复制的根本操作就是把从主服务器上所执行过的sql在从机上再执行一遍，只要两个机器的数据库初态（数据库结构，数据，配置）是一样的，那么我们开启主从复制之后就能保证他们一直都是一样的状态。这些都是mysql自己实现的，我们就配置一下就可以了。

从图中我们可以看出来了，主服务器需要做的事情非常简单的，就只是把执行的sql语句存储到二进制文件binary-log中，而从服务器需要做的事情比较多，而且还得使用两个线程进行整个事件的监听和处理。

我们的主从复制一共分为三步：

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events。 slave将master的binary log events拷贝到它的中继日志（relay log）。 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的。基本原则

由于主从复制的一些特性，为了保证数据一一致，所以这里有些原则需要遵守：

每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个salve

1.1、环境准备：

A服务器（主服务器Master）：192.0.0.131
B服务器（从服务器Slave）：192.0.0.188
主从服务器的Mysql版本皆为5.6
这里需要注意防火墙的3306端口要开放
两台服务器数据库的数据表结构，配置都必须是一样的。

1.2、主服务器设置：

登陆到主服务器的mysql，并执行如下语句创建用户，授予权限：

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON *.* TO slave@"%" IDENTIFIED BY "123456";

修改my.cnf配置文件

log-bin=mysql-bin
server-id=1

说明：

1. log-bin:开启二进制日志，该日志是在事务提交时写日志文件的。默认大小是1G，后面加001,002这样的后缀顺加。
1. server-id，唯一标识主机，mysql主从每个mysql实例配置都不一样就行。这个值默认是0，如果是0，主服务器拒绝任何从服务器的连接。

其他配置（不是必须配置的）：

1、binlog-do-db=db_001（主数据库配置） #指定mysql的binlog日志记录哪个db，配置需要同步的数据库，可以配置多个，如果没有此配置项则同步全部。
2、binlog-ignore-db=mysql（主数据库配置） #配置不同步的数据库，可以配置多个。
3、binlog_format = mixed #配置binlog的格式
4、read-only = 0 #配置是否只读 0代表不只读，1代表只读
5、auto-increament-increment = 10 #用于设定双主情况下自增列的ID冲突使用的，主要用来设置自增步长
6、auto-increment-offset = 1 #表示这台服务器的序号，从1开始，不超过auto-increament-increment

重启数据库

systemctl restart mysqld.service

1.3、从服务器设置：

配置my.cnf

server-id=2
replicate-do-db=test
skip-slave-start=true

注意：如果你使用的是虚拟机，而且从机是从master主机克隆出来的，那么你就需要进行这一步的操作了。

进入我们mysql的数据库目录下：

#这是默认的地址，根据自己配置的datadir即可
cd /var/lib/mysql

vim auto.cnf

修改里面的uuid为一个不同的即可。

重启数据库

systemctl restart mysqld.service

1.4、配置从服务器认主

获取binlog的信息

我们先到主（master）服务器上获取binlog的信息，在mysql的命令界面输入：

show master status;

这里展示的就是我们当前主服务器使用的binlog的文件名，其中position是文件中偏移量，我们之后配置slave需要用到这些信息，这个文件在每次服务器状态变化后都不同

进入最关键的一步了，在进入从服务器mysql操作界面后，输入如下指令：

stop slave;          //先停步slave服务线程，这个是很重要的，如果不这样做会造成以下操作不成功，第一次设计主从的话忽略。

change master to 
master_host='192.0.0.131',
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000002', 
master_log_pos=1472;

这里user和password就是我们第一步在主服务器上创建的用户名和密码，然后MASTER_LOG_FILE 就是我们上一步查看到的master在使用的binlog文件（这个文件在每次主服务器状态变化后都不同），MASTER_LOG_POS 就是binlog的偏移量，用于同步扫描使用。master_log_file对应File, master_log_pos对应Position。Mysql 5.x以上版本已经不支持在配置文件中指定主服务器相关选项。

执行完抛警告的话没关系，没有异常就可以。

开启从服务器slave线程

start slave;

查看（Slave）状态

show slave status\G;

看到图中的两个yes，就表示我们的从服务器已经在完全运行了。

现在我们可以在主服务器的数据库上做一个修改数据看看是否同步到从服务器的数据库上来证明可用性。

1.5、主从验证

因为之前数据库的数据都是一样的，现在就都新建个test 库，并且在test 库新建表 user_test.

这里需要注意两个数据库都要执行

#创建新库
CREATE DATABASE test;

#指定编码
CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;

#创建新表
create table user_test( id int comment'ID',name VARCHAR(20) comment'名称', create_time timestamp DEFAULT now() comment'创建时间' );

准备条件完成，进入测试

主库查询：

从库查询：

现在我们在主库手动插入一条数据

use test;

insert INTO user_test value(1,"张三",NOW());

直接查询从库看有没有数据同步

结果很明显，数据已经同步，说明我们的主从复制的配置Ok了。

2、双机热备

实现双机热备，原理其实就是做两个机器的互相主从，我们把上述步骤主从对调，然后做一遍就能实现了双机热备了

还是使用上面的两台服务器，具体步骤就不写了，直接贴一下两个服务器的配置文件 my.cnf

服务器A配置：

log-bin=mysql-bin
server-id=1

# 双机热备需要添加
log-slave-updates
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2

服务器B配置

log-bin=mysql-bin
server-id=2

# 双机热备需要添加
log-slave-updates
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2

开始测试---------------------------------------------------->

服务器A 删除一条，查看服务器B的数据

服务器B 增加一条，删除一条，查看服务器A的数据

结果可以看到互为主从的双机热备也成功了，以上只是一个实现思路，具体的配置还得根据自己的实际情况，下面贴一些会用到的配置参数：

Server-id：ID值唯一的标识了复制群集中的主从服务器，因此它们必须各不相同。Master_id必须为1到232－1之间的一个正整数值，slave_id值必须为2到232－1之间的一个正整数值。
Log-bin：表示打开binlog，打开该选项才可以通过I/O写到Slave的relay-log，也是可以进行replication的前提。
Binlog-do-db：表示需要记录二进制日志的数据库。如果有多个数据可以用逗号分隔，或者使用多个binlog-do-dg选项。
Binglog-ingore-db：表示不需要记录二进制日志的数据库，如果有多个数据库可用逗号分隔，或者使用多binglog-ignore-db选项。
Replicate-do-db（从数据库配置）：表示需要同步的数据库，如果有多个数据可用逗号分隔，或者使用多个replicate-do-db选项。
Replicate-ignore-db（从数据库配置）：表示不需要同步的数据库，如果有多个数据库可用逗号分隔，或者使用多个replicate-ignore-db选项。
Master-connect-retry：master-connect-retry=n表示从服务器与主服务器的连接没有成功，则等待n秒（s）后再进行管理方式（默认设置是60s）。如果从服务器存在mater.info文件，它将忽略些选项。
Log-slave-updates：配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步。
skip-slave-start 从机服务器重启后slave 不会自动开始，要手动执行，防止出错

3、常见问题

mysql 二进制日志文件写满磁盘，服务不可用，报错如下

Disk is full writing './mysql-bin.000032' (Errcode: 15753600 - No space left on device). Waiting for someone to free space...

3.1、可以通过如下设置，自动删除日志文件

vim /etc/my.cnf  //修改expire_logs_days,x是自动删除的天数，一般将x设置为短点，如10
expire_logs_days = x  //二进制日志自动删除的天数。默认值为0,表示“没有自动删除”

此方法需要重启mysql

当然也可以不重启mysql,开启mysql主从，直接在mysql里设置expire_logs_days

show binary logs;
show variables like '%log%';
set global expire_logs_days = 10;

3.2、手动清除binlog文件

首先登陆MySQL

PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);   //删除10天前的MySQL binlog日志,附录2有关于PURGE MASTER LOGS手动删除用法及示例
show master logs;

也可以重置master，删除所有binlog文件：

reset master;  //附录3有清除binlog时，对从mysql的影响说明