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

Java基础

浏览数:194

2019-3-17

AD:资源代下载服务

前记

距离上一次在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线程。
具体的复制过程:

  1. master出现数据库更新,在binlog中记录这个更新操作
  2. binlog dump线程binlog中有更新,读取binlog并将它传到连接到的slave。
  3. slave中的IO Thread接受这个binlog,将这个binlog记录在relay log文件中。
  4. slave中的SQL线程从relay log中读取这个更新操作,通过SQL操作将这个更新反应到数据库中
  5. 通过上面的一系列操作,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中的参数, 通过这些参数我们可以实时把握当前主从复制的情况。