每日MySQL之007：MySQL复制之Binary Log File Position Based Replication

MySQL中的复制有Binary Log File Position Based Replication和Replication with Global Transaction Identifiers，这篇只讲述前者

这种模式下，master将更新操作写到binary log中，而slave读取并执行这些从主机发过来的binary log，从而更新本地数据库。每个slave都会从主机获取一份完整的binary log，但具体执行binary log中的哪些事件，是slave自己决定的，默认情况下slave会执行binary log所有的事件，如果有需要的话，可以设置只执行部分数据库或者部分表的事件。

这种模式下，每个master和slave都要配置一个唯一的server ID。而slave要读取master的日志，所以slave要知道master的主机名、binary log文件名和binary log中的位置。

假定有两台机器，db2a已经有数据，要做master，db2b做slave，目标是db2b中数据与db2a中保持一致（跟DB2中的HADR类似）。

过程如下：
1. Master启用binary log并分配一个唯一的server ID
2. Slave分配一个唯一的server ID，不能与master相同
3. 可选，为replication专门创建一个用户，用来读取日志
4. 在master上获取binary log的当前位置，slave需要从这个当前位置开始读取
5. 如果master上已经有数据，则需要获取数据的快照并拷贝到slave上
6. 配置slave到master的连接，包括master主机名、密码、binary log文件名及当前位置（第4步中获取）
7. 开始复制

1. Master启用binary log并分配一个唯一的server ID 1
root@db2a:~# cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep -iv -e '^#' -e '^$'
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
log-bin = /var/log/mysql/binerr.log
symbolic-links=0

root@db2a:~# mysqld --user=mysql  --server_id=1 &
[1] 27792

2. Slave分配一个唯一的server ID 2，Slave并不需要开启binary log:
root@db2b:~# cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep -iv -e '^#' -e '^$'
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address    = 127.0.0.1
symbolic-links=0

root@db2b:~#  mysqld --user=mysql --server_id=2 --skip-slave-start &
[1] 3201

3. Master上为复制专门创建一个用户repl，只赋予REPLICATION SLAVE权限：
mysql> use mysql
mysql> CREATE USER 'repl'@'db2b' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'db2b';

4. 在master上获取binary log的当前位置，可以看到文件名为binerr.000008，位置为611：
mysql> FLUSH TABLES WITH READ LOCK;
mysql>  SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binerr.000008 |      611 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5. Master上，获取数据库的快照，需要把文件sample.test1.db发送到Slave上
root@db2a:~# mysqldump -u root -pqingsong --databases sample test1 --master-data > sample.test1.db

mysql> UNLOCK TABLES;

6. Slave上配置到master的连接，需要指定master的host，连接到master的用户名、密码，master上binary log的名称及位置：
root@db2b:~# mysql -u root -pqingsong

mysql>  CHANGE MASTER TO
    -> MASTER_HOST='db2a',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='binerr.000008',
    -> MASTER_LOG_POS=611;

7. Slave上先把数据恢复，然后使用START SLAVE开始复制，可以看到，数据已经复制过来：
root@db2b:~# mysql -u root -pqingsong < sample.test1.db 

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> use test1;
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|   10 | aaa  |
|   13 | ddd  |
+------+------+
2 rows in set (0.00 sec)

再次验证，在master上新增一个表，在slave上马上就可以查询到：
db2a上：
mysql> use test1;
mysql> create table t2 like t1;
mysql> insert into t2 select * from t1;
mysql> insert into t2 values(100,'newValueAfterRep');

db2b上：
root@db2b:~# mysql -u root -pqingsong
mysql> show tables;
mysql> select * from t2;
+------+------------------+
| id   | name             |
+------+------------------+
|   10 | aaa              |
|   13 | ddd              |
|  100 | newValueAfterRep |
+------+------------------+
3 rows in set (0.00 sec)

测试中发现，slave是可写的，也就是说可以直接在db2b上做修改操作。

https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html