成都创新互联网站制作重庆分公司

mysql主从复制架构及实现

MySQL主从复制架构及实现

mysql主从复制配置

  • 主节点：

    创新互联是专业的广安网站建设公司，广安接单;提供成都网站建设、成都做网站,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行广安网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!

    • 启动二进制日志

    • 为当前节点设置一个全局唯一的ID号（server-id）

    • 创建有复制权限的用户账号（replication slave，replication client）

  • 从节点：

    • 启动中继日志

    • 为当前节点设置一个全局唯一的ID号

    • 使用有复制权限的用户账号连接至主服务器，并启动复制线程

具体步骤

主节点（192.168.182.130）

  • 编辑配置文件/etc/my.cnf，添加以下内容

    [mysqld]
log_bin=1
server_id=1
innodb_file_per_table=on
  • 创建有复制权限的用户账号 
    MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.132' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
从节点（192.168.182.132）

  • 编辑配置文件/etc/my.cnf，添加以下内容

    [mysqld]
relay_log=relay_log
relay_log_index=relay_log.index
server_id=7
skip_name_resolve=1

  • 使用有复制权限的用户账号连接至主服务器

    MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000075',MASTER_LOG_POS=245;

  • 启动复制线程

    MariaDB [(none)]> START SLAVE;
  • 查看slave的状态 
    MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.182.130
              Master_User: repluser
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000075
      Read_Master_Log_Pos: 245
           Relay_Log_File: 1.000002
            Relay_Log_Pos: 529
    Relay_Master_Log_File: mysql-bin.000075
         Slave_IO_Running: Yes   ##io线程启动了
        Slave_SQL_Running: Yes   ## sql线程启动了
          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: 245
          Relay_Log_Space: 809
          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

mysql主从复制中应该要注意的问题

  • 限制从服务器为只读

    • 限制从服务器为只读，在从服务器上设置read_only=on，想要永久有效，就写在配置文件中，但是此限制对拥有SUPER权限的用户均无效

      MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
MariaDB [(none)]> SET GLOBAL  read_only=1;
    • 如果想要限制所有用户不能够进行写操作，那么启动一个连接加读锁，并且不要退出该连接 
      MariaDB [(none)]> FLUSH TABLE WITH READ LOCK;

  • 如何保证主从复制的事务安全？

    • 在master节点启用参数

      sync_binlog=

    • 如果用到的是InnoDB存储引擎

      innodb_flush_log_at_trx_commit
innodb_support_xa
    • 在slave节点上，以下参数表示在从服务器启动的时候，是否自动启动复制线程，on表示不用自动启动，使用手动启动的方式 
      skip_slave_start=on

  • slave节点上的两个文件

    • master.info文件中保存了slave连接至master时的相关信息，例如主服务器的ip地址，复制使用的用户，密码，端口，以及当前同步的二进制日志文件和位置

    • relay-log.info文件中保存了当前slave节点已经复制的二进制日志文件和位置，复制的内容保存到了哪个中继日志的哪个位置 
      [root@backserver data]# cat relay-log.info
./1.000002
693
mysql-bin.000075
409

  • 复制的监控和维护：

    • 清理日志：使用PURGE命令，清理之前确保数据已经备份过了

    • 复制监控

      MariaDB [(none)]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       483 |
| mysql-bin.000002 |       264 |
| mysql-bin.000003 |       264 |
| mysql-bin.000004 |       264 |
| mysql-bin.000005 |       990 |
| mysql-bin.000006 |       514 |
| mysql-bin.000007 |       264 |
| mysql-bin.000008 |       245 |
| mysql-bin.000009 |       245 |
+------------------+-----------+
MariaDB [(none)]> SHOW BINLOG EVENTS;
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                           |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         245 | Server ver: 5.5.45-MariaDB-log, Binlog ver: 4                  |
| mysql-bin.000001 | 245 | Query       |         1 |         315 | BEGIN                                                          |
| mysql-bin.000001 | 315 | Intvar      |         1 |         343 | INSERT_ID=9                                                    |
| mysql-bin.000001 | 343 | Query       |         1 |         456 | use `S_SC_C`; INSERT INTO S (sname,sdept) VALUES ('xiao','MA') |
| mysql-bin.000001 | 456 | Xid         |         1 |         483 | COMMIT /* xid=43 */                                            |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |      245 |              |                  |
+------------------+----------+--------------+------------------+
MariaDB [(none)]> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             | Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|  5 | root        | localhost | NULL | Query   |    0 | NULL                                                                        | SHOW PROCESSLIST |    0.000 |
|  6 | system user |           | NULL | Connect | 3850 | Waiting for master to send event                                            | NULL             |    0.000 |
|  7 | system user |           | NULL | Connect | 3806 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
MariaDB [(none)]> SHOW SLAVE STATUS\G

    • 从服务器是否落后与主服务器，在从服务器的salve有Seconds_Behind_Master可以查看

      MariaDB [(none)]> SHOW SLAVE STATUS
-> \G
*************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
          Master_Host: 192.168.182.130
          Master_User: repluser
          Master_Port: 3306
        Connect_Retry: 60
      Master_Log_File: mysql-bin.000079
  Read_Master_Log_Pos: 334
       Relay_Log_File: 1.000006
        Relay_Log_Pos: 618
Relay_Master_Log_File: mysql-bin.000079
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_Do_DB: MYDB
  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: 334
      Relay_Log_Space: 1182
      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

    • 确定主从节点数据是否一致，使用一些工具，例如percona-tools

    • 如果数据不一致，那么就要将从节点的所有数据删除，将主节点的数据备份一份导入到从节点上，重新复制

  • 在主节点上有一个参数，设置为1表示每一次dump事件到从节点的时候，本地的master.info信息要立即同步到磁盘上，让从节点的master.info及时得到更新，

    MariaDB [(none)]> SET GLOBAL sync_master_info=1;
MariaDB [(none)]> SHOW  GLOBAL VARIABLES LIKE 'sync_master_info';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sync_master_info | 1     |
+------------------+-------+
  • 在从节点上有关于relay_log的参数，例如sync_relay_log_info，表示每一次写操作立即同步到磁盘文件上，还有sync_relay_log

双主模型

  • 遇到的问题：

    • 数据不一致，这时，我们可能就需要将数据可靠性比较高的服务器留下来，根据保留的mysql服务器重新再做一个从服务器

    • 对于自动增长的字段，如果一个主节点是1，2，3，...，另一个主节点也是1，2，3，...，那么合并的时候就会出现问题，所以，我们可以让一个节点的自动增长的字段使用偶数id，另一个主节点使用奇数id，这样合并的时候就不会出现问题

    • 奇数id的设置

      auto_increment_offset=1 #表示从1开始
auto_increment_increment=2 #表示一次增长2个
    • 偶数id的设置 
      auto_increment_offset=2
auto_increment_increment=2

配置步骤

  • 各节点使用一个唯一的server_id

  • 都启动binary log和relay log

  • 创建拥有复制权限的用户账号

  • 定义自动增长id字段的数值范围为奇偶

具体步骤

  • 其中一个节点为:

    • 编辑配置文件

      log_bin=1
server_id=1
innodb_file_per_table=on
relay_log=relay-log
relay_log_index=relay-log.index
auto_increment_offset=1
auto_increment_increment=2

    • 创建用户账号

      MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.132' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
    • 使用有复制权限的用户账号连接至主服务器并开启slave线程 
      MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=245;
MariaDB [(none)]> START SLAVE;

  • 在另一个主节点上的操作：

    • 编辑配置文件

      [mysqld]
bin_log=1
relay_log=1
relay_log_index=relay-log.index
server_id=7
auto_increment_offset=2
auto_increment_increment=2

    • 创建具有复制权限的用户账号

      MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.130' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
    • 使用具有复制权限的用户账号连接至主节点并启用slave线程 
      MariaDB [(none)]>  CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000075',MASTER_LOG_POS=665;
MariaDB [(none)]> START SLAVE;

半同步复制

  • 需要用到一个插件，如果是rpm包安装的插件路径在/usrl/lib64/mysql/plugin，我使用二进制格式安装的插件路径是/usr/local/mysql/lib/plugin

  • 主节点上的操作

    • 编辑配置文件

      [mysqld]
log_bin=1
server_id=1
innodb_file_per_table=on
plugin_dir=/usr/local/mysql/lib/plugin

    • 添加具有复制权限的用户账号

      MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.132' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;

    • 安装插件

      MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

    • 查看有哪些插件

      MariaDB [(none)]> SHOW PLUGINS;

    • 查看半同步相关的变量

      MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

    • rpl_semi_sync_master_enabled：为OFF表示禁用为半同步复制的主节点

    • rpl_semi_sync_master_timeout：表示等待从服务器应答的超时时长，默认是10s，如果超过这个时间从服务器没有给主服务器应答，那么就降级为异步方式运行，不再等待

    • rpl_semi_sync_master_trace_level：表示跟踪级别

    • rpl_semi_sync_master_wait_no_slave：表示在没有从节点的时候是否要等待，on为等待

    • 启用半同步 
      MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;

  • 从节点上的操作：

    • 编辑配置文件

      [mysqld]
relay_log=1
relay_log_index=relay-log.index
server_id=7
skip_name_resolve=1
plugin_dir=/usr/local/mysql/lib/plugin/

    • 使用具有复制权限的用户账号连接至主节点

      MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='rpluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000078',MASTER_LOG_POS=245;

    • 安装插件

      MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

    • 查看半同步相关的变量

      MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

    • 启用半同步

      MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
    • 启用slave线程 
      MariaDB [(none)]> START SLAVE;
  • 以上操作就可以完成一个半同步的复制，可以在主节点上查看相关的状态变量，查看是否有半同步的slave节点 
    MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
# Rpl_semi_sync_master_clients为0表示没有，为1表示有

复制过滤器

  • 让从节点仅复制指定的数据库，或指定数据库的指定表

  • 有两种实现方式:

    • 主服务器仅向二进制日志中记录与特定数据库（特定表）相关的事件，但是可能会导致时间点还原无法实现（毕竟二进制日志记录的事件是不完全的），不建议使用

      binlog_do_db  #指定只记录哪些数据库的相关修改操作到二进制日志文件中，可以指定一个列表，使用逗号隔开
binlog_ignore_db  #指定只忽略哪些不记录，其他都记录，可以指定一个列表，使用逗号隔开

    • 从服务器SQL_THREAD在replay中继日志中的事件时，仅读取与特定数据库（特定表）相关的事件并应用与本地；但是会造成网络及磁盘io浪费

      replicate_do_db=   #该处指定的数据库的相关事件都要进行复制
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table=  #在指定表的时候可以使用通配符
replcate_wild_ignore_table=
    • 示例：复制时只复制MYDB数据库的相关事件 
      #在从服务器上进行的操作
MariaDB [(none)]> SET GLOBAL replicate_do_db='MYDB';
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%replicate%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| replicate_annotate_row_events    | OFF       |
| replicate_do_db                  | MYDB      |
| replicate_do_table               |           |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db              |           |
| replicate_ignore_table           |           |
| replicate_wild_do_table          |           |
| replicate_wild_ignore_table      |           |
+----------------------------------+-----------+
MariaDB [(none)]> SHOW SLAVE STATUS\G;

当前文章：mysql主从复制架构及实现
文章来源：http://cxhlcq.com/article/gciiec.html

其他资讯

全国免费咨询:

400-028-6601

业务咨询：028-86922220 / 13518219792

节假值班：18980820575 / 13518219792

联系地址：成都市太升南路288号锦天国际A幢1002号

Copyright © 成都创新互联科技有限公司重庆分公司 渝ICP备2021005571号
友情链接:： 成都网站建设 重庆网站建设 四川网站建设 重庆建设网站 移动服务器托管 成都服务器托管 云服务器 广告设计制作 重庆网页设计 重庆做网站 重庆网站制作 重庆网站建设 重庆网站公司 渝中网站制作 重庆网站设计

在线咨询

微信咨询

电话咨询

028-86922220（工作日）

18980820575（7×24）

提交需求

返回顶部