mysql工作原理流程图 mysql原理及应用

MySQL主从复制|读写分离应用场景及原理测试全面分析

• 一、MySQL主从复制|应用场景及原理

• 1.1、MySQL主从复制应用场景及原理

• 1.1.1、为什么需要主从复制？

• 1.1.2、主从复制提供的优势
• 1.1.3、主从复制的劣势
• 1.1.4、MySQL主从复制的原理

• 1.2、MySQL主从复制配置相关

• 1.2.1、测试环境

• 1.2.2、MySQL的部署
• 1.2.3、MySQL主从复制的基本流程
• 1.2.4、MySQL主从复制实现

• 1.2.4.1、Master与slave binlog日志开启

• 1.2.4.2、确认matser与slave serverid不一致
• 1.2.4.3、创建一个用于slave和master通信的用户账号
• 1.2.4.4、查看master正在使用的二进制以及位置编号
• 1.2.4.5、slave数据库进行连接操作
• 1.2.4.6、slave数据库启动同步
• 1.2.4.7、验证数据同步

• 1.3、MySQL主从复制测试

• 1.3.1、测试主master宕机

• 1.3.2、测试master主服务器网络连接断开
• 1.3.3、测试slave数据库宕机，master数据库正常写入，slave还原场景
• 1.3.4、测试master&slave同时宕机
• 1.3.5、MySQL主从复制的致命！操作！

• 1.4、主从复制并不能减轻对数据库的压力

• 二、MySQL读写分离|应用场景及原理

• 2.1、应用场景

• 2.2、MySQL-proxy
• 2.3、MySQL-proxy部署安装

• 2.3.1、下载mysql-proxy

• 2.3.2、解压mysql-proxy压缩包
• 2.3.3、配置mysqlproxy
• 2.3.4、测试mysqlproxy读写分离测试

• 2.4、再见

一、MySQL主从复制|应用场景及原理

---

1.1、MySQL主从复制应用场景及原理

1.1.1、为什么需要主从复制？

单个数据库一旦发生爆炸，宕机，硬盘或系统损坏丢失则应用将全部瘫痪

当硬盘被小偷偷走，数据库直接发生爆炸，或者系统瘫痪导致业务无法正常运行，此时加一台从服务器解决这样的问题

---

当加入一台从数据库后与从进行实时同步

主从同步后当主mysql爆炸，从mysql数据依然保留可以接管主mysql的任务提供正常的应用服务

1.1.2、主从复制提供的优势

• 1、可以进行数据分布，确保数据在不同的数据库中存放
• 2、当主mysql出现严重的问题时，从mysql可以接管主mysql
• 3、当主数据库意外发生数据丢失情况，从mysql起到了备份的作用

1.1.3、主从复制的劣势

• 1、当然主mysql发生高数据量高并发情况从服务器-主服务器之间的传输问题导致主mysql性能下降的问题
• 2、主从复制的技术并不能给主mysql带来效率上的提高，反而在某种情况拖延主数据库的效率问题

唯一的好处是主mysql的数据不仅仅是一份，起到了备份的作用以及防备主宕机的后续保障。

1.1.4、MySQL主从复制的原理

  按图中流程首先用户通过互联网访问到web，新建了一个用户操作，这个操作发送到数据库，数据库执行insert into table userxxx 插入数据。同时将此insert into table userxxx操作记录存入到自己的binlog日志当中，slave端启动IO线程对master发送请求（请求内容：master的二进制文件以及二进制的POS节点位置）。 master收到slave请求后根据slave请求的信息，读取指定的binlog日志以及从哪个节点开始的日志信息返回给slave的IO线程，IO线程收到master返回的信息后将binlog日志信息以及POS节点信息依次写入到relay log日志也就是中继日志当中，再此同时也将master返回的binlog日志名称以及pos节点位置存放在master-info文件当中。此时SQL线程依次读取relay log日志并将读取内容解析成sql语句进行执行操作，从而实现从主数据一致。

为了简明过程如下：

1. master将sql执行的操作存入到binlog二进制日志当中
2. slave启动IO线程向master服务器进行请求（binlog日志/pos节点）
3. master对slave的请求的binlog/pos节点进行响应
4. slave收到响应后将信息存入到中继日志当中，并且将master的binlog/pos存入到master-info文件当中
5. SQL线程读取中继日志内容进行解析SQL操作写入到数据库

master-info文件作用

master-info（该文件存在slave端）文件中，以便在下一次读取的时候能够清楚的告诉master，我需要从哪个binlog文件的哪个pos节点位置开始，请把此节点以后的日志内容发给我

master-info文件在mysql目录下存在如下：

mysql dump Thread

当slave向master发送请求后，master会为每一个IO线程创建一个dump Thread线程用来发送二进制日志到slave/IO线程。最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。Mysql dump Thread线程与IO线程

主库收到从库发送的注册指令后会获取到从库的server-id，report-host，report-user，report-password，prot等相关的数据，最终这些数据会存放到slave_list变量中，如果slave-list这个表中已经存在slave-server-id的信息则会删除掉这个信息，在将新的加入。（不能有相同的server-id出现，否则会直接删除）

master收到slave IO发送的请求后，获取slave发送的binlog相关信息，server-id，名称，POS，binlog大小等等，检查是否已经存在与该从库关联的binlog dump线程，例如，slave IO线程出了问题，此时master的binlog dump线程一直等待主库写入数据操作. 如果这时从库io线程重连到主库，就会发现主库已经存在与该从库对应的dump线程。所以主库在处理从库binlog dump请求时，先检查是否已经存在dump线程。

binlog dump内部调用的是多个while嵌套循环，依次等待并读取binlog文件中的event，随后将event发送到从库，如果此时从库envet已经在使用，则忽略该event，当读到新的binlog时如果event都已经发送完成，则 binlogdump线程会等待binlog更新事件。

IO线程请求拉取binlog的命令发送完成之后，io线程在一个while循环中，不断调用read_event(mysql, mi, &suppress_warnings) 来读取主库发送的binlog数据，并写入到relay log文件中。

IO线程以及master的log dump线程都使用循环的方式一个在等待主master写入数据并发送广播传输，当主库发送二进制日志后通过循环方式不断并写入到relay log文件当中。

1.2、MySQL主从复制配置相关

1.2.1、测试环境

主MySQL	192.168.1.8
从MySQL	192.168.1.9
MySQL	version：5.7

1.2.2、MySQL的部署

MySQL部署略过建议下载oneinstall一键下载工具部署mysql，非常好用！

1.2.3、MySQL主从复制的基本流程

1. 首先确定配置master 和 slave其中mysql主配置文件的 ‘server-id’
2. 创建一个用于slave和master通信的用户账号
3. 查看matser的状态，show matser status;需要记录：File的名称以及Postsion的数字
4. 主mysql数据给从数据授权之后需要通过从数据库进行连接操作，连接的时候需要matser的file名称以及postsion这两个数据
5. 从mysql数据库连接成功后，启动同步操作：start slave;
6. 最后检查连接的状态：（show slave status；）

1.2.4、MySQL主从复制实现

主从复制实现还是非常简单的，按照2.1.3的说明一步步部署就可以了，过程如下：

1.2.4.1、Master与slave binlog日志开启

第一步确定master和slave的mysql开启了binlog二进制文件的操作，可以通过以下命令查看：

MySQL [(none)]> show variables like 'log\_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

确定log_bin为on的状态，mysql二进制日志mysql-bin.000001,mysql-bin.000002 这样的方式出现，通常出现在mysql目录下。mysql二进制日志中记录了mysql中sql语句执行的所有日志操作. 有一款mysql的工具是通过mysql二进制日志中的内容从而进行数据库的恢复操作. 如果mysql重启一次就会产生新的mysql-bin二进制日志。

1.2.4.2、确认matser与slave serverid不一致

server_id是不可以重复的，server_id 在每一个正在同步中的slave在master上都会对应一个matser线程，这个线程是通过slave的serverid来标识的，如果存在两台从服务器的serverid一致，只能一个链接成功，因为slave在master端最多只有一个线程，通过serverid来标识slave是哪台，如果发现slave两台id都一致，则会导致另外一台不同步的问题. 以下方式配置server id；

master:

[root@master ~]# vi /etc/my.cnf
[root@master ~]# cat /etc/my.cnf | grep server-id
server-id = 1   `这是配置文件中matser的id号`
[root@master ~]#

slave：

[root@slave ~]# vi /etc/my.cnf
[root@slave ~]# cat /etc/my.cnf | grep server-id
server-id = 2   `这是配置文件中slave的id号`
[root@slave ~]#

配置matser 和 slave serverid后进行重启mysql操作使其生效！

1.2.4.3、创建一个用于slave和master通信的用户账号

mstser数据库操作：

MySQL [(none)]> grant replication slave on *.* to  'myslave'@'192.168.1.9' identified by 'pwd123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]>

这条sql是授权1.9这台主机，也就是slave，通过myslave这个用户，通过密码pwd123来链接主mysql的操作

1.2.4.4、查看master正在使用的二进制以及位置编号

MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      602 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

file表示mysql目前正在使用的二进制日志的文件，position记录了当前执行二进制日志位置。 这两个值需要做保留，从服务器会通过链接到主mysql，与主的二进制文件的日志进行同步，同时从哪里开始同步是由 Position来决定。

1.2.4.5、slave数据库进行连接操作

change master to
master_host='192.168.1.8',
master_user='myslave',
master_password='pwd123',
master_port=3306,
master_log_file='mysql-bin.000007',
master_log_pos=602
;

• change master to 连接matser数据库
• master_host master数据库的地址
• master_user master创建slave与master连接的用户名
• master_password master创建slave与master连接用户名的密码
• master_port master 数据库的端口
• master_log_file matser数据库正在使用的二进制文件
• master_log_pos master数据库执行二进制日志位置

操作：

MySQL [(none)]> change master to
    -> master_host='192.168.1.8',
    -> master_user='myslave',
    -> master_password='pwd123',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000007',
    -> master_log_pos=602;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

MySQL [(none)]>

1.2.4.6、slave数据库启动同步

start slave;命令

MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.8
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 602
               Relay_Log_File: 192-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000007
             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: 602
              Relay_Log_Space: 1230
              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: 691ede37-7085-11ec-bbac-000c298a92c4
             Master_Info_File: /data/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)

Slave_IO_Running: Yes ： IO线程开启成功
Slave_SQL_Running: Yes ： SQL线程开启成功

1.2.4.7、验证数据同步

到此刻matser数据库创建，删除，增加，改动，从数据库都会进行实时同步数据. 到此mysql主从复制已实现。

1.3、MySQL主从复制测试

1.3.1、测试主master宕机

主数据库发生停止的情况下，slave的IO线程向master发出请求未能链接成功报错信息如下：

当matser宕机后slave 状态显示error reconnecting to master 'myslave@192.168.1.8:3306' - retry-time: 60 retries: 1重新连接到主服务器时出错myslave@192.168.1.8：3306’-重试时间：60

1.3.2、测试master主服务器网络连接断开

断开前：

断开后：

查看错误信息：

一旦发生IO发生：error reconnecting to master 'myslave@192.168.1.8:3306' - retry-time: 60 retries: 1错误问题以下几个方式解决：

1. 网络不通，尝试ping测试，检查网络通信问题是否能到达master
2. 配置给与权限slave连接主配置的用户名密码错误问题
3. 主防火墙以及从数据库防火墙配置限制问题，检查端口出入配置

IO进程去连接主mysql发送请求，如果出现非yes的情况则检查双方服务器数据库的连接问题。包括配置问题，确保网络，配置无误。如果配置无误则检查网络问题，当网络恢复之后，slave IO会重新链接运行。

1.3.3、测试slave数据库宕机，master数据库正常写入，slave还原场景

例如：在一个数据量写入非常庞大的环境当中，主从复制正在运行，突然从数据库意外宕机关机，主数据库还在不断的写入数据。如下：

正常运行复制当中

IO SQL正常读写

slave强制关机

主服务器无法到达从

master使用批量插入10万行数据脚本

此脚本为创建了一个test_yanzan的库创建了一张tb1_db的表，在其中插入10万行数据.

# 批量插入：
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="123123"
DBNAME="test\_yanzan"
TABLENAME="tb1\_db"
#create database
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create\_db\_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e"${create\_db\_sql}"
#create table
create\_table\_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1) 
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${create\_table\_sql}"
#insert data to table
i="1"
while [ $i -le 100000 ]
do
insert\_sql="insert into ${TABLENAME} values($i,'zhangsan','1','21276387261874682','1999-10-10','2017-10-24','beijingchangpingqu')"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${insert\_sql}"
let i++
done
#select data
select\_sql="select count(\*)from${TABLENAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${select\_sql}"

总计插入时长：10分钟 配置低谨慎使用！启动slave服务器

slave数据库启动后自动连接到主sql，读取新增日志信息进行同步操作。

正常同步成功：

1.3.4、测试master&slave同时宕机

master & slave 启动后：

可完全正常链接。

1.3.5、MySQL主从复制的致命！操作！

slave开启IO线程后向master发出请求，master为IO线程开启一个dump 线程用来传输二进制日志，随后IO线程将收到的日志信息以及节点信息存放到中继日志当中，SQL线程读取中继日志进行解析写入实现主从同步。 但当slave数据库上例如有db_users的库文件，此时当主创建slave已存在的db_users库会发生什么如下：

查看从库slave状态

Error 'Can't create database 'db_users'; database exists' on query. Default database: 'db_users'. Query: 'create database db_users'错误“无法创建数据库”db_users”；查询中存在“数据库”。默认数据库：“db_用户”。查询：“创建数据库db\u用户”.以上直接中断SQL线程的结果，提示已经存在某数据库，当我们没有设置mysql主从复制异常脚本时，如果出现此类现象没有任何报警，那么主数据库目前还正在写入数据，比如在此情况下继续在主数据库创建数据。

发现从数据库报错信息没有改变，并且没有同步新的数据，这是因为刚刚主master创建的数据库与slave的数据库相互冲突的结果。 流程是：slave已经存在db_users的库，当master创建db_users库，通过传输最后到中继日志，随后sql线程读取中继日志中的create database db_users发现已经存在此库，于是报出已经存在的结果，这样的结果导致后续中继日志中的语句不断增加，因为IO线程正常运行不断接收binlog日志写入中继日志，但sql线程断开从此无法同步数据。

解决办法：
以上情况sql线程在执行中继日志中的创建db_users的命令冲突，有一个指令是可以让sql线程跳过此冲突指令如下：
set GLOBAL sql_slave_skip_counter=1; 重新启动slave

或者也可以 stop slave 执行语句 然后开启slave即可。

同步成功

如果执行完之后还是没有解决，或者继续报错，那就一直执行这条语句就可以了，直到解决问题。

1.4、主从复制并不能减轻对数据库的压力

slave并不能提高数据库的效率问题，当并发量写入数据读取数据比较庞大千万级别所有的压力都在master服务器上,master数据库一边要处理写入的数据,一边还要处理读取的数据，还要将binlog日志节点发送到slave的IO线程。太忙了… 因此主从复制是为了读写分离提供服务，这样就可以大大提高数据库的效率问题。

二、MySQL读写分离|应用场景及原理

2.1、应用场景

当用户在应用层创建了一篇文章，通常是以insert插入形式，代表写入操作。调度中介服务器检测到用户是写入操作时，将用户的写入操作请求分发到master主数据库中，当用户要查询文章时，中介检测到查询操作，将查询操作分配到slave从数据库中运行。这样就实现了主从读写分离。

中间的分配分发的服务器软件可以通过mysql-proxy来实现。

2.2、MySQL-proxy

mysql-proxy是mysql官方提供的mysql中间件服务，上游可接入若干个mysql-client，后端可连接若干个mysql-server。它使用mysql协议，任何使用mysql-client的上游无需修改任何代码，即可迁移至mysql-proxy上。mysql-proxy最基本的用法，就是作为一个请求拦截，请求中转的中间层：将客户端的请求分发到不同的数据库当中。

2.3、MySQL-proxy部署安装

master	192.168.1.8
slave	192.168.1.7