; mysql主从架构/模式 | Linux运维部落

mysql主从架构/模式

MySQL Replication:

Master/Slave

Master: write/read

Slaves: read

为什么?

冗余:promte(提升为主),异地灾备

人工

工具程序

负载均衡:转移一部分“读”请求;

支援安全的备份操作:






主/从架构:

异步制:master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志,是否完成操作,不用关心。MYSQL的默认设置。


半同步复制:master只保证slaves中的一个操作成功,就返回,其他slave不管。这个功能,是由google为MYSQL引入的。


一主多从;


一从一主;


                级联复制;分层次,每一级服务器上一级服务器同步数据,并传递给下一级的服务器


循环复制;


双主复制;

一从多主(不建议):

每个主服务器提供不同的数据库;可以使用多线程复制,但是每个主服务器必须是提供不同的数据库


==========================================================================



主从复制配置:(异步复制) 

 

 


从服务器需要启动两个重要的线程:

            第一个:从主服务器不断同步时间到本地的中继日志文件中   — >  IO线程

            第二个:从本地中继日志读取时间进行重放(replay)    — >   SQL线程




单主模型:

配置前准备

时间同步:可以编辑 /etc/chrony.conf 文件添加合适的服务器时间源,然后重启服务

复制的开始位置:

从0开始;

从主服务器备份中恢复到从节点后启动的复制,指示比从零开始多了一步(从主服务器上备份数据库到从服务器上恢复,然后加上binlog做时间点恢复);

主从服务器mysqld程序版本不一致?

                        mysql版本只能是主服务器版本低于从服务器,或者与从服务器一致;但是从服务器版本不能低于主服务器

                —————————————————————————————————————————————————

主服务器:

配置文件my.cnf

server_id=1(不能与其他节点相同)

log-bin=master-log

                        innodb_file_per_table = ON

                        skip_name_resolve = ON

启动服务:

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';   

                        #为了安全起见,只需要授予最小权限REPLICATION SLAVE,REPLICATION CLIENT就可以了,而且需要注意,创建账户的时候二进制日志是否开启,开启了也会记录创建用户语句,视情况而定时候需要跳过;

mysql> FLUSH PRIVILEGES;

                                            

                                —————————————————————————–



从服务器:

配置文件my.cnf

server_id=2(不能与其他节点相同)

relay_log=relay-log 

innodb_file_per_table = ON

                        skip_name_resolve = ON

                        #注意:从服务器没有必要的话,可以不启动二进制日志



启动服务:

mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;


mysql> START SLAVE [IO_THREAD|SQL_THREAD];

mysql> SHOW SLAVE STATUS;

                              mysql> SET @@global.read_only=1     #开启只读功能,但是对于root用户以及super权限的用户都无效

                            

                              mysql> show slave hosts;       #查看slave的连接状态

                                       

 

                               —————————————————————————–

                               —————————————————————————–



双主模型:


       要点

时间同步:可以编辑 /etc/chrony.conf 文件添加合适的服务器时间源,然后重启服务

双方版本:需要一致

                    中继日志:双方均要启用中继日志(relay-log)

                二进制日志:双方都要启动二进制日志

                自动增长字段:一方使用奇数,另一方使用偶数,避免重叠


                               —————————————————————————–

                               —————————————————————————–


主服务器(server_id=1):

配置文件my.cnf

server_id=1(不能与其他节点相同)

log-bin=master-log

                        relay-log=relay-log

                        innodb_file_per_table = ON

                        skip_name_resolve = ON

        auto_increment_offset = 1 (设置字段增长以1开始,使用奇数)

                                auto_increment_increment = 2  (设置字段每次增长2)


启动服务:(双方均要创建可用于复制表的用户,只需要最低权限,以及互相添加为主服务器)

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';   

                        #为了安全起见,只需要授予最小权限REPLICATION SLAVE,REPLICATION CLIENT就可以了,而且需要注意,创建账户的时候二进制日志是否开启,开启了也会记录创建用户语句,视情况而定时候需要跳过;

mysql> FLUSH PRIVILEGES;


mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;


mysql> START SLAVE [IO_THREAD|SQL_THREAD];

mysql> SHOW SLAVE STATUS;

                              mysql> SET @@global.read_only=1     #开启只读功能,但是对于root用户以及super权限的用户都无效


                               —————————————————————————–

                               —————————————————————————–


主服务器(server_id=2):

配置文件my.cnf

server_id=2(不能与其他节点相同)

log-bin=master-log

                        relay-log=relay-log

                        innodb_file_per_table = ON

                        skip_name_resolve = ON

        auto_increment_offset = 2 (设置字段增长以2开始,使用偶数)

                                auto_increment_increment = 2  (设置字段每次增长2)


启动服务:(双方均要创建可用于复制表的用户,只需要最低权限,以及互相添加为主服务器)

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';   

                        #为了安全起见,只需要授予最小权限REPLICATION SLAVE,REPLICATION CLIENT就可以了,而且需要注意,创建账户的时候二进制日志是否开启,开启了也会记录创建用户语句,视情况而定时候需要跳过;

mysql> FLUSH PRIVILEGES;


                                mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;


mysql> START SLAVE [IO_THREAD|SQL_THREAD];

mysql> SHOW SLAVE STATUS;




         —————————————————————————–        




        MariaDB [(none)]> show slave status\G    #开启从服务器后的状态参数解析

*************************** 1. row ***************************

               Slave_IO_State: 

                  Master_Host: 10.1.35.1

                  Master_User: slaveuser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-log.000001

          Read_Master_Log_Pos: 1078

               Relay_Log_File: relay-log.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: master-log.000001

             Slave_IO_Running: No

            Slave_SQL_Running: No  由于还没有启动复制功能,所以这两项为NO

              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: 1078

              Relay_Log_Space: 245

              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:          #基于ssl复制的功能选项

        Seconds_Behind_Master: NULL      #检查落后服务器多少秒,如果落后的秒数比价大,可以重启IO线程(先STOP SLAVE IO_THREAD;然后再START SLAVE IO_THREAD)

Master_SSL_Verify_Server_Cert: No         #是否启用基于ssl功能进行复制

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error:      #线程的错误号

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 0



                               —————————————————————————–

                               —————————————————————————–

双主模型弊端

        

        在第二个节点创建数据库

MariaDB [(none)]> create database mydb;

     在第一个节点创建一个边,并定义自动增长的行

MariaDB [mydb]> create table tbl1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30));

 

        返回第二节点,并查看在第二节点上创建的table,并插入数据(到此可以看出双主互相复制)

MariaDB [(none)]> use mydb

MariaDB [mydb]> desc tbl1;

MariaDB [mydb]> insert into tbl1 (name) values ('stu1');

由于定义了第二节点auto_increment_offset = 2 , 所以有关自动增长字段的数据都是从二开始,并每次使用都自增加2

 在第一节点查看并插入数据

 

 

 上图就是设置双主关于增长字段的跳跃性的弊端,只要不冲突,应该不会有问题。所以设置双主模型最好有一个全局序列符号生成器,需要增长字段的时候,由序列符号生成器提供,而不是服务器自己定义。


                               —————————————————————————–

                               —————————————————————————–




以下为用到的命令解析

START SLAVE     #启动从服务器的复制功能

MariaDB [(none)]> HELP START SLAVE

Name: 'START SLAVE'

Description:

Syntax:

START SLAVE [thread_types]

START SLAVE [SQL_THREAD] UNTIL

    MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

START SLAVE [SQL_THREAD] UNTIL

    RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos

thread_types:

    [thread_type [, thread_type] … ]

thread_type: IO_THREAD | SQL_THREAD

                               —————————————————————————–

                               —————————————————————————–

            

             CHANGE MASTER TO            #在从服务器上设置主服务器信息的命令

         MariaDB [(none)]> HELP CHANGE MASTER TO

Name: 'CHANGE MASTER TO'

Description:

Syntax:

CHANGE MASTER TO option [, option] …

option:

    MASTER_BIND = 'interface_name'

  | MASTER_HOST = 'host_name'

  | MASTER_USER = 'user_name'

  | MASTER_PASSWORD = 'password'

  | MASTER_PORT = port_num

  | MASTER_CONNECT_RETRY = interval       #重试的时间间隔,一般使用默认值

  | MASTER_HEARTBEAT_PERIOD = interval   #探测主服务器的时间间隔,一般使用默认值

  | MASTER_LOG_FILE = 'master_log_name'      #从主服务器的哪个二进制日志文件开始复制

  | MASTER_LOG_POS = master_log_pos          #从主服务器的二进制日志文件的哪个pos开始复制

  | RELAY_LOG_FILE = 'relay_log_name'            #复制到从服务器的哪个中继日志文件,一般使用默认值

  | RELAY_LOG_POS = relay_log_pos                #复制到从服务器中继日志文件的哪个,一般使用默认值pos位置

  | MASTER_SSL = {0|1}

  | MASTER_SSL_CA = 'ca_file_name'

  | MASTER_SSL_CAPATH = 'ca_directory_name'

  | MASTER_SSL_CERT = 'cert_file_name'

  | MASTER_SSL_KEY = 'key_file_name'

  | MASTER_SSL_CIPHER = 'cipher_list'

  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}

  | IGNORE_SERVER_IDS = (server_id_list)   #一从多住的选项,一般不建议一从多主;

server_id_list:

    [server_id [, server_id] … ]

 



                               —————————————————————————–

                               —————————————————————————–

       

                        STOP SLAVE     #停止从服务器的复制功能


MariaDB [hellodb]> HELP STOP

Name: 'STOP SLAVE'

Description:

Syntax:

STOP SLAVE [thread_types]

thread_types:

   [thread_type [, thread_type] … ]

thread_type: IO_THREAD | SQL_THREAD


                               —————————————————————————–

                               —————————————————————————–


    总结:

复制时应该注意的问题:

1、从服务设定为“只读”;(双主模式无需设置)

在从服务器启动read_only,但仅对非SUPER权限的用户有效;

阻止所有用户:

mysql> FLUSH TABLES WITH READ LOCK;



2、尽量确保复制时的事务安全

在master节点启用参数:

sync_binlog = ON    #每次事务提交的时候,都立即将二进制日志时间都重内存同步到磁盘中,能确保从服务器能立即得到事件,而且能保护本地数据安全

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

innodb_flush_logs_at_trx_commit=ON      #在事务提交时,立即刷写事务日志从内存到磁盘上

innodb_support_xa=ON    #支持分布式事务

#这两项启动起来,能在一定程度上确保从服务器能够立即得到主服务器的最新事件



3、从服务器意外中止时尽量避免自动启动复制线程

由于数据是重要的,服务器能意外终止的时间都是奇异的,所以,终止后不要让从服务器自动启动复制线程,等人工手动排查后才从新开启复制线程;有可能复制线程复制到一半的时候出现意外,复制线程或许没有这种功能—–不知道时候需要再次复制上次中断的事件               

4、从节点:设置参数

每一个从服务器都会保存一个文件 relay-log-info,是记录在主服务器上复制二进制日志的位置,以及本地中继日志的位置,为避免产生繁忙的IO操作,这些参数都是先保存在内存上的,到一定程度才会同步到磁盘上的,所以不安全

sync_master_info=ON

sync_relay_log_info=ON

                        以上两项为立即刷写数据从内存到磁盘




===================================================================
===================================================================










===================================================================
===================================================================

半同步复制

      每次有时间提交的时候,主服务器完成本地的所有写入操作(事务日志,二进制日志),还要确保这个事件已经写入到从服务器的中继日志中以及完成重放,并报告主服务器已经完成写入操作了,最后主服务器才能告诉客户端数据已经写入,虽然影响性能,但是数据安全性提高了。

            半同步复制需要安装Google的开源插件

     主服务器需要的插件 –>  semisync_master.so

从服务器需要的插件 –>  semisync_slave.so



               支持多种插件的位置:/usr/lib64/mysql/plugins/

需要安装方可使用:

mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name'; 


 —————————————————————————–

在主从复制的基础上开始做:


主节点:

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';   #安装模块

SHOW PLUGIN;    #查看安装模块的情况


MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';

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   

MariaDB [mydb]> SET GLOBAL rpl_semi_sync_master_enabled=ON;


从节点:

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';                        

 rpl_semi_sync_slave_enabled          OFF   

 rpl_semi_sync_slave_trace_level      32   

MariaDB [mydb]> STOP SLAVE IO_THREAD;

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';

MariaDB [mydb]> START SLAVE IO_THREAD;

判断方法:

主节点:

MariaDB [mydb]> SELECT @@global.rpl_semi_sync_master_clients;

                                或者:

                                MariaDB [mydb]> SHOW STATUS LIKE ’%semi%‘;

          | Rpl_semi_sync_master_clients                             半同步复制的连接个数

| Rpl_semi_sync_master_net_avg_wait_time          主节点网络等待平均时长

| Rpl_semi_sync_master_net_wait_time                  主节点网络等待的累积时长

| Rpl_semi_sync_master_net_waits                         主节点网络等待的累积次数

| Rpl_semi_sync_master_no_times                          主节点关闭半同步复制的次数

| Rpl_semi_sync_master_no_tx                                               主节点等待超时的次数

| Rpl_semi_sync_master_status                                             是否为半同步复制状态

| Rpl_semi_sync_master_timefunc_failures                  时间函数未正常工作的次数

| Rpl_semi_sync_master_tx_avg_wait_time               主节点等待事务提交的平均时长

| Rpl_semi_sync_master_tx_wait_time                      主节点等待事务提交的总时长

| Rpl_semi_sync_master_tx_waits                             主节点等待事务提交的次数

| Rpl_semi_sync_master_wait_pos_backtraverse         改变当前等待最小二进制日志的次数

| Rpl_semi_sync_master_wait_sessions                           当前有多少个session 因为slave 的回复而造成等待

| Rpl_semi_sync_master_yes_tx                                             主节点收到从节点回复的次数





===================================================================
===================================================================






复制过滤器:



 

仅复制有限一个或几个数据库相关的数据,而非所有;由复制过滤器进行;

有两种实现思路:

(1) 主服务器过滤

主服务器仅向二进制日志中记录有关特定数据库相关的写操作;

         坏处:其它库的point-recovery将无从实现; 

                                好处:节约网络IO以及磁盘IO

binlog_do_db=                           #白名单控制

binlog_ignore_db=                     #黑名单控制


如果基于上述控制,及在主节点用二进制日志文件控制,则其他库的二进制日志信息无法得到保存,当需要数据恢复的时候,是无法恢复没有保存二进制日志信息的数据库的信息,慎用!


(2) 从服务器过滤

从服务器的SQL THREAD仅重放关注数据库或表相关的事件,并将其应用于本地;

坏处:网络IO和磁盘IO;

好处:数据安全性更高

                                

                    MariaDB [mydb]> show  slave status\G

Replicate_Do_DB=

Replicate_Ignore_DB=

Replicate_Do_Table=

Replicate_Ignore_Table=

Replicate_Wild_Do_Table=               #wild为允许使用通配符

Replicate_Wild_Ignore_Table=   #wild为允许使用通配符

                                      由于是GLOBAL参数,只有root用户才有权限修改,但是建议写在配置文件中/etc/my.cnf



                                                  #通常不会在表级别过滤数据


===================================================================
===================================================================










基于SSL复制

前提:启用SSL功能;

===================================================================
===================================================================





复制的监控和维护:



(1) 清理日志:PURGE 

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };


                        binary:二进制日志文件

                        master:日志文件


                        清除前先备份,或者复制文件出来,再清理,过一段时间确实无用,再作左后的删除


              ————————————————————————————–



(2) 复制监控

MASTER:

SHOW MASTER STATUS;

SHOW BINLOG EVENTS;

SHOW BINARY LOGS;

SLAVE:

SHOW SLAVE STATUS;

判断从服务器是否落后于主服务器:

Seconds_Behind_Master: 0       如果主从服务器均不繁忙,但是从服务器依然落后于主服务器,可以重启从服务器的  IO_THREAD  (先stop slave IO_THREAD 然后 start slave IO_THREAD)



              ————————————————————————————–


(3) 如何确定主从节点数据是否一致?

通过表的CHECKSUM检查;


   使用percona-tools中pt-table-checksum;


              ————————————————————————————–




(4) 主人数据不一致时的修复方法?

重新复制;

数据集过大,可以使用单表导入导出;



===================================================================
===================================================================

原创文章,作者:hunter,如若转载,请注明出处:/60958

联系我们

400-080-6560

在线咨询:点击这里给我发消息

邮件:1660809109@qq.com

工作时间:周一至周五,9:30-18:30,节假日同时也值班

友情链接:万达娱乐  万达娱乐招商QQ  万达主管QQ  万达娱乐注册  万达娱乐主管  万达娱乐平台  万达直属QQ  万达直属  万达招商  万达开户