mysql集群技术：主从复制，读写分离

快来打我* 2022-06-04 02:12 263阅读 0赞

*  服务器端读写分离的具体技术
    
     *  1 数据库集群技术
        
         *   *  集群由 3 个概念
             *  2 数据库复制 replication
                
                 *  数据库复制 replication 的实现原理
                 *  实现步骤 
                 *  具体实施
     *  配置主服务器
     *  配置从服务器
        
         *   *   *  常用语句 
                 *  主服务器的日志格式用哪种好 
 *  调试
    
     *  方法一 重置slave
     *  方法二
     *  方法三 重设同步日志件及位置
        
         *   *  1查看slave状态
             *  2暂停slave
             *  3到主服务器上查看主机状态
             *  4修改从服务同步日志文件及位置
             *  4启动slave
 *  复制过滤
    
     *  主库过滤
     *  从库过滤
     *  问题

# 服务器端读写分离的具体技术 #

## 1: 数据库集群技术 ##

#### 集群由 3 个概念 ####

1.  sql 节点 sql node
2.  数据节点 data node
3.  管理节点 ndb managerment

Sql 语句发送 “1sql 节点 “, “1sql” 节点发往 “2 数据节点 “, 再由 3 管理节点完成数据节点的之间的同步 .

集群技术相对复杂 , 至少有 3 种节点 ,4 台服务器才能完成 .

#### 2: 数据库复制 replication ####

Created with Raphaël 2.1.0主库168主库168从库166从库166嘿，哥们，我这边有更新，你那边也更新一下吧

##### 数据库复制 replication 的实现原理 #####

1.  主服务器凡运行语句 , 都产生一个二进制日志 binlog
2.  从服务器不断读取主服务器的 binlog
3.  从主服务读取到的 binlog, 转换为自身可执行的 relaylog,
4.  执行 relaylog

##### 实现步骤 : #####

1.  首先确保主服务器打开二进制日志功能 .这样 , 主服务器一旦有数据变化 , 立即产生二进制日志 .
2.  从服务器也需要开启二进制日志和 relay 日志功能 .这样可以从主服务器读取 binlog, 并产生 relaylog
3.  在主服务器建立一个从服务器的账号 , 并授予数得上权限 .
4.  指定从服务对应的主服务器 , 开启从服务器 .

##### 具体实施 #####

本人虚拟机下有两台 linux,IP 为 168

**1. 166 做从服务器**

**2. 168 做为主服务器 .**

**3. 保证主从 3306 端口互通 .**

## 配置主服务器 ##

**4. 配置主服务器 168, 打开 `binlog`**

> 查看mysql主服务器状态 是否配置

MariaDB [mysql]> show master status;
    Empty set (.00 sec)

vim /etc/my.cnf
    ...
    ...
    ...
    # 给服务器起一个唯一的 id
    server-id=168
     # 开启二进制日志
    log-bin=mysql-bin
    # 指定日志格式 有mixed|row|statement 推荐mixed
    binlog-format=mixed

**重启 mysql**

[root@localhost ~]# service mysqld restart

> 查看mysql主服务器状态 是否配置 结果已经能够充当 master 服务器

**5. 在主服务器上创建相应的复制账号**

MariaDB [mysql]> grant replication slave,replication client on *.* to repl@'192.168.%.%' identified by '123456';
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

## 配置从服务器 ##

**6. 配置从服务器打开 binlog 和 relaylog**

> 查看mysql从服务器状态 是否配置

MariaDB [mysql]> show slave status;
    Empty set (.00 sec)

vim /etc/my.cnf
    ...
    ...
    ...
    # 给服务器起一个唯一的 id
    server-id=166
    # 开启二进制日志
    log-bin=mysql-bin
    # 指定中继日志的位置和命名
    relay_log=mysql-relay
    # 允许从库将其重要的事件也记录到自身的二进制日志中
    log_slave_updates=1
    # 指定只读
    read-only=1

**重启 mysql**

[root@localhost ~]# service mysqld restart

**7. 在从服务器通过语句指定要复制的主服务器 ( 注意 , 可以一主多从 , 不可一从多主 ).**

> master\_log\_file对应主服务器 show master status中的File  
> master\_log\_pos对应主服务器 … status中的position

MariaDB [mysql]> change master to
    master_host='192.168.1.168',
    master_user='repl',
    master_password='123456',
    master_log_file='mysql-bin.000001',
    master_log_pos=0;
    Query OK, 0 rows affected (0.00 sec)

**8. 启动从服务器功能**

MariaDB [mysql]> start slave;

**9. 查看从库状态**

MariaDB [mysql]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.166
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 0
                   Relay_Log_File: mysql-relay.000001
                    Relay_Log_Pos: 0
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                ...           
                ... 
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
    1 row in set (0.00 sec)

**9. 测试 .**

##### 常用语句 : #####

#查看 master 的状态 , 尤其是当前的日志及位置
    show master status ;
    
    #查看 slave 的状态 .
    show slave status;
    
    #重置 slave 状态 .
    reset slave ;
    
    #启动 slave 状态 ( 开始监听 msater 的变化 )
    start slave ;
    
    #暂停 slave 状态 ;
    stop slave;

##### 主服务器的日志格式用哪种好 ? #####

有 statement,row, mixed 3种 , 其中 mixed 是指前 2 种的混合 。

1.  以 `insert into xxtable values (x,y,z)` 为例 ,  
    影响 : 1 行 , 且为新增 1 行 , 对于其他行没有影响 .  
    这个情况 , 用 row 格式 , 直接复制磁盘上 1 行的新增变化 。
2.  以 `update xxtable set age=21 where name='sss';`  
    这个情况 , 一般也只是影响 1 行 . 用 row 也比较合适 。
3.  以过年发红包 , 全公司的人 , 都涨薪 100 元 .  
    `update xxtable set salary=salary+100;`  
    这个语句带来的影响 , 是针对每一行的 , 因此磁盘上很多 row 都发生了变化 。  
    此处 , 适合就 statment 格式的日志 。

> 总结  
> 2 种日志 , 各有各的高效的地方 ,mysql 提供了 mixed 类型 。  
> 可以根据语句的不同 , 而自动选择适合的日志格式 .

# 调试 #

配置**mysql**的master/slave时，经常会遇到`Slave_IO_Running: No`

这时可通过以下方法来处理：

## 方法一： （重置slave） ##

slave stop;
    
    reset slave;
    
    slave start;

## 方法二 ##

MariaDB [mysql]> slave stop;
    MariaDB [mysql]> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    MariaDB [mysql]> slave start;

## 方法三： （重设同步日志件及位置） ##

#### 1、查看slave状态 ####

show slave status\G
    
                Master_Log_File: mysql-bin.000040
            Read_Master_Log_Pos: 4
                 Relay_Log_File: mysqld-relay-bin.000001
                  Relay_Log_Pos: 98
          Relay_Master_Log_File: mysql-bin.000040
               Slave_IO_Running: No
              Slave_SQL_Running: Yes

> 目前已同步到mysql-bin.000040这个日志文件，98的位置；

#### 2、暂停slave ####

slave stop;

#### 3、到主服务器上查看主机状态： ####

#### 4、修改从服务同步日志文件及位置 ####

日志文件及位置都改小；（之前是同步正常，最近才出现问题的，一般改小一个值就好）  
如：

change master to master_log_file='mysql-bin.000004', master_log_pos=1872;

#### 4、启动slave ####

slave start;

5、再次确认slave状态

show slave status\G

> 这时会发现：  
> Slave\_IO\_Running: Yes  
> 且下述两个值一直在不断变化中：  
> Relay\_Log\_Pos: 998875  
> Relay\_Master\_Log\_File: mysql-bin.000048

**方法一、二不一定会有效果.方法三是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删除记录同步也会有一些错误信息,不会影响使用.**

# 复制过滤 #

## 主库过滤 ##

在主库上使用选项 `binlog_do_db`和`binlog_ignore_db`来控制过滤二进制日志。

# 只记录test库的二进制日志，可以配置多个
    binlog_do_db = 'test'

# 不记录mysql库的二进制日志
    binlog_ignore_db = 'mysql'

> inlog\_do\_db和Binlog\_ignore\_db不仅可能会破坏复制，还可能会导致从某个时间点的备份进行数据恢复时失败。在大多数据情况下都不应该使用这些参数。

## 从库过滤 ##

#要只想复制某个库
    replicate_do_db = 'db_name'
    
    #要只想复制某张表
    replicate_do_table = 'table_name'
    
    #忽略复制某个库
    replicate_ignore_db
    #忽略复制某张表
    replicate_ignore_table
    
    #复制 通配符 匹配指定库的所有表
    replicate_wild_do_table = db_name[%_]
    
    #复制 除通配符 匹配指定库外的所有表
    replicate_wild_ignore_table = db_name[%_]
    
    
    #告诉从服务器如果默认数据库(由USE所选择)为主服务器上的 from_name ，则翻译为 to_name 。只影响含有表
    的语句(不是类似CREATE DATABASE、DROP DATABASE和ALTER DATABASE的语句)，并且只有 from_name 为
    主服务器上的默认数据库时。该选项不可以跨数据库进行更新。
    replicate_rewrite_db = from_name->to_name

> 从服务器设置完成，重启mysql服务器，然后进入交互式模式，执行`mysql> SHOW SLAVE STATUS\G`  
> 查看Replicate\_Do\_DB这一字段，即可验证配置是否生效

## 问题 ##

但实际上它不会根据目标数据库名过滤，过滤的是当前的默认数据库**（如果使用的是基于语句的复制，就会有这样的问题，但基于行的复制方式则不会）**

Mysql>USE test;
    Mysql>DELETE FROM sakila.file

> \*\_do\_db和\*\_ignore\_db都会在数据库test上过滤DELETE语句，而不是在sakila上。这通常不是想要的结果，可能会导致执行或忽略错误的语句。

**总结：Statement-based跟当前use的库有关，Row-based更直接，只关心指定的库‘做或不做’。**

**除replicate-wild-do-table=/replicate-wild-ignore-table=外，其他过滤规则会受到“binlog\_format”以及“当前所在库”的影响（即所谓的跨库问题）**