mysql基于binlog的复制

背景

目前公司采用的是单机数据库，现在需要在基础上增加节点，实现主主复制，增加数据库的容灾能力，提高性能。通过对mysql官方文档的学习，在这里决定采用基于binlog的主从复制。

原理

master将改变作为事件写进binlog日志中，事件会以不同的日志形式被存储。slave读取master的binlog日志，并在本地重新执行binlog日志。
每个slave保存着一个binlog日志的坐标：binlog文件名称以及已经读取和执行过的位置。

从库生成两个线程，一个I/O线程，一个SQL线程；

i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；
主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；

SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；
注意点：

• 每个slave都会收到完整的binlog内容的拷贝。
• slave自行决定哪个事件需要执行。可以指定具体的数据库，表
• 不能配置master仅仅记录指定的事件

步骤

master开启binlog日志，设置唯一的server-id,重启master

基于binlog的主从复制是通过读取master的事件，然后重新在slave中执行来保持数据一致，因此，master开启binlog日志是先决条件。
server-id的范围是1～$2^{32}$之间，如果设置为0或者不设置，master拒绝任何slave的连接。这里的唯一server-id应该理解为在一个复制群组类，server-id不重复。
binlog日志的开启需要重新启动数据库实例，无法在线修改。

#以下两个参数是必填
log-bin=mysql-bin
server-id=1

## 只保留15天的binlog,防止日志过大占用磁盘
expire_logs_days = 15
#限制每个bin_log日志大小最大为100M。
max_binlog_size = 100M    

#skip-networking在master必须不可用，如果填为可用，那么任何tcp/ip连接将不可用，只能基于socket连接
skip-networking=off

#以下2个参数是可选的，最大保持复制过程的事物的持久性和一致性
innodb_flush_log_at_trx_commit=1
sync_binlog=1         

#每次增长2
auto-increment-increment = 2   
#设置自动增长的字段的偏移量，即初始值为1
auto-increment-offset = 1 

 #slave执行master的sql后，将sql记录在binlog日志中（默认是不记录的）
log-slave-updates=1       

创建用户

用于复制的用户需要具备 REPLICATION SLAVE权限,用于复制的帐号需要最小化权限，虽然这一步不是必须的，但是因为帐号密码信息在slave是明文存储的

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

获取master的binlog位置

获取master的binlog位置

• 开启一个session,刷新表并且锁住写操作

mysql> FLUSH TABLES WITH READ LOCK;

保持这个sesion,不要退出

• 再开启一个session,获取binlog的坐标，记录下这些值，在设置slave的时候会使用到

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

• 如果master之前没有开启binlog.这个时候使用show master status 是空的，那么file和position对应的是空字符串’'和4

备份master的数据

如果master以前存在数据，那么需要将数据迁移到slave中，在这里仅仅介绍mysqldump备份的方式

mysqldump -u username -p -S socket -A --single-transaction --master-data=2 > ./alldata.sql

• single-transaction会在备份数据的时候，生成一个快照，保持备份数据的一致性
• master-data将在备份文件的开头生成binlog的位置信息。

## --master-data=1生成的信息可以执行，2生成的信息被注释
CHANGE MASTER TO MASTER_LOG_FILE='on.000009', MASTER_LOG_POS=154;

配置slave

• 每个slave 需要具备唯一的server-id

slave的my.cnf的典型配置如下：

## 以下参数必须配置
server-id=2

# 每次增长2 设置自动增长的字段的偏移量，即初始值为2
auto-increment-increment = 2  
auto-increment-offset = 2 

## 以下参数是可选的，如果不指定，那么是全库复制
### 指定slave复制的数据库
replicate-do-db=
### 指定slave复制的表
replicate-do-table=
### 指定slave忽视的数据库
replicate-ignore-db=
### 指定slave忽视的表
replicate-ignore-table=

• CHANGE MASTER TO

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=recorded_log_position;

开启slave

• 执行start slave

mysql>start slave;

• 再执行show slave status\G;只要Slave_IO_Running和Slave_SQL_Running都是yes说明开启成功。