共计 7414 个字符，预计需要花费 19 分钟才能阅读完成。
前记
距离上一次在 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 中的参数，通过这些参数我们可以实时把握当前主从复制的情况。
