[MySQL进阶之路][No.0001] MySQL的Replication基础

前记

距离上一次在segmentfault上发文章足足过了两年时间，自己也已经从在日本留学进入到了工作岗位。选择留在日本工作的理由其实自己也不是很清楚，只是无论身在哪里，都只想做一个技术人员的理想至少现在并没有改变。虽然目前为止日本的IT行业无论在规模还是技术层面都无法和国内相提并论，但是自己身边还是有很多大神的，自己在这段时间学到的东西无论如何也想和大家交流分享。还请大家多多指教。

关于这个系列

这个系列主要介绍自己工作上面关于MySQL的运用和研究。这个系列可能会偏向MySQL的底层和架构设计。对于开发方面的SQL语句设计以及数据表的设计等可能只会在介绍索引index的时候稍微提及。最后，本系列涉及到的MySQL版本将主要集中在5.7和8.0。存储引擎将只介绍Innodb。(主要Myasim等因为自己也没接触过-_-)

为什么要学MySQL

说到为什么要学MySQL，先得说为什么IT公司要用MySQL。MySQL是开源的，你可以在Github上随意的浏览它的源码，给MySQL开发者送bug report。最主要它是免费的，不管是自己买服务器搭架构，还是用云服务，MySQL都是很好的选择。虽然Oracle， SQL Server等在功能上可能更强大，但是对于中等规模的IT公司来说，MySQL往往已经足够够用了。

那可能就有人会说，现在谁还用关系型数据库呀。确实现在的数据库种类也是越来越多，NoSQL数据库不断提供着时髦的使用方法，对开发者来说也能更好的节省开发时间。Google的firestore（firebase）在最近也是被日本的开发者们视为掌中宝。但是MySQL在这么多年的企业级使用中，也性能调优方面，数据安全方面也变的不断成熟。虽然现在的NoSQL很方便，但是当涉及到一些敏感或者重要数据的时候，为了数据完整性和安全性，我会选择MySQL。学习MySQL，对于一个公司的发展或者对于一个技术人员的自我提升来说，其实都是一件很有逼格的事情。（虽然好像没什么说服力）

数据库的复制（Replication）

说了这么对废话，还是快点进入今天的正题。Replicaiton可能是学习数据库架构的最基础的东西了。Replication翻译过来是复制，那就是复制数据库，或者备份数据库呗。那为什么需要复制数据库呢？

想象一下下面一个场景，如果你只有一台数据库服务器，写数据和读数据全都通过这一个数据库来做，当你的流量大了以后，这台服务器的负载将越来越大，发生故障的机率也越来越大。最后当这台服务器挂掉以后，你的数据库将变的不可用，整个应用死掉，那可能你就要写好多故障报告了。

为了减少上面发生的概率，我们会使用replication，也就是主从架构。一台master(主)服务器底下挂着几台slave（从）服务器。slave数据库通过Replication和master数据库保持数据同步。这时候master数据库可以只用来写数据，读数据的流量就可以分散到slave数据库服务器上了。可之前相比，服务器的负载得到了分散。而且对于这个架构来说，容错性也得到了提高，当一台slave服务器死掉以后，其他或者的slave依然可以接受流量，应用也不会中断。master死掉以后，只要将一台slave升级成master就行了（故障损害虽然不是0，但也能尽可能的减少）。
图片描述

在上图的架构中，有一台slave没有读操作也没有写操作，这个服务器可以被用来定期获取数据库的snapshot。这样做的话就不会因为经常获取snapshot而对生产环境中的服务器造成影响。

MySQL的Replication原理

图片描述

如果在master服务器中设置binlog有效的话，对数据库有更新的操作都会被记录在binlog文件中。（binlog文件将在之后的文章中做详细介绍）
当slave连接到master服务器上时，master会创建一个binlog dump现成。而slave会创建一个IO线程和SQL线程。
具体的复制过程：

master出现数据库更新，在binlog中记录这个更新操作
binlog dump线程binlog中有更新，读取binlog并将它传到连接到的slave。
slave中的IO Thread接受这个binlog，将这个binlog记录在relay log文件中。
slave中的SQL线程从relay log中读取这个更新操作，通过SQL操作将这个更新反应到数据库中
通过上面的一系列操作，slave和master可以保持一致。

官方文档：https://dev.mysql.com/doc/ref...

实践

方便大家hands-on,可以使用我准备的这个库。（只要装了docker，就可以立马动手了）
https://github.com/leeif/mysq...

在docker中启动MySQL。

//启动container ： mysql57_master，mysql57_slave，mysql80_master，mysql80_slave
docker-compose up -d

//进入container里
docker-compose exec mysql57_master(mysql57_slave) bash

数据库用户root，密码为root。

master

要使用replication，master需要存储binlog。要存储binlog，需要在master中设置指定log-bin（binlog的名字和存储位置）。
※MySQL8.0开始，默认binlog是有效的，无需设置log-bin。

master的配置文件如下：

//mysql57_master

root@76e96aaae65d:/# cat /etc/mysql/conf.d/config-file.cnf
[mysqld]
server-id         = 0001
log-bin           = /var/log/mysql/mysql-bin.log
binlog_format     = statement
binlog_cache_size = 1M
max_binlog_size   = 200M
root@76e96aaae65d:/#

为了让slave识别master,server-id也是必须的。
这时候我们可以查看一下master的状况。

//mysql57_master

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

可以看到当前的binlog文件名是mysql-bin.000003，并且当前的binlog记录位置是154。
我们尝试在数据库写入一些数据。运行以下脚本。

//mysql57_master

root@76e96aaae65d:/# cat /mysql_etc/mysql_data_generator.sh
#!/bin/bash
mysql -uroot -P 3306 -proot -D mysql -e "create database replication_test;"
mysql -uroot -P 3306 -proot -D mysql -e "create table replication_test.test_table (id int not null auto_increment, name varchar(255), primary key (id));"

data=""
for i in {1..99}; do d="('name_$i'),"; data=$data$d; done
mysql -uroot -P 3306 -proot -D mysql -e "insert into replication_test.test_table (name) values $data('name_100');"
root@76e96aaae65d:/#

//mysql57_master
//写入了100条数据

mysql> select count(*) from replication_test.test_table;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     2163 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

可以看到binlog的参数发生了改变，说明数据库被更新了，并且更新内容被写入binlog文件里了。

slave

在slave服务器中，我们要让它和master实现同步。首先我们用change master语句让slave知道要从哪个master复制数据。

//mysql57_slave

mysql> change master to MASTER_HOST='10.1.0.100',
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='root',
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.12 sec)

mysql>

MASTER_LOG_POS设置成了写入数据之前master的binlog位置。
（这里我们用了root用户，在实际的运用场景中我们一般会在master创建一个只用于replication的用户，给它赋予只能replication的权限。）

启动slave

//mysql57_slave

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.0.100
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2163
               Relay_Log_File: ebd7cc002e88-relay-bin.000002
                Relay_Log_Pos: 2329
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2163
              Relay_Log_Space: 2543
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 50655a33-bda5-11e8-b007-02420a010064
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql>

...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
...

从上面的参数重，可以看到IO线程和SQL线程已经在运行，并且没有出现错误，说明replication被成功建立。

查看数据同步情况：

//mysql57_slave

mysql> select count(*) from replication_test.test_table;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.01 sec)

mysql>

可以看到master中的数据已经被同步了过来。

查看关于replicaiton的线程

master中运行的process

//mysql57_master

mysql> show processlist\G
*************************** 1. row *************************** //binlog dump线程
     Id: 7
   User: root
   Host: mysql_learning_hard_mysql57_slave_1.mysql_learning_hard_test:
     db: NULL
Command: Binlog Dump
   Time: 693
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 8
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
2 rows in set (0.00 sec)

mysql>

slave中运行的process

//mysql57_slave

mysql> show processlist\G
*************************** 1. row *************************** //IO线程
     Id: 3
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 790
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row *************************** //SQL线程
     Id: 4
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 92220
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
*************************** 3. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
3 rows in set (0.00 sec)

mysql>

结尾

关于MySQL replication的原理以及基本用法就先说到这。下篇准备具体介绍一下show slave status中的参数，通过这些参数我们可以实时把握当前主从复制的情况。

[MySQL进阶之路][No.0001] MySQL的Replication基础

前记

关于这个系列

为什么要学MySQL

数据库的复制（Replication）

MySQL的Replication原理

实践

master

slave

查看关于replicaiton的线程

结尾

leeif

引用和评论

[Golang] 聊一聊Go的那些处理命令行参数和配置文件的库

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 亿级数据平滑迁移实战

MySQL间隙锁，next-key锁

高效工程师的七个习惯

sqlmap注入语句学习，帮助你排查SQL注入漏洞