ITPub博客

首页 > 数据库 > MySQL > MySQL主从复制简单介绍、重建、中断处理-----给开发、测试人员的培训

MySQL主从复制简单介绍、重建、中断处理-----给开发、测试人员的培训

MySQL 作者:萧忆勋 时间:2015-12-17 10:22:27 0 删除 编辑
        前几天给开发做了MySQL主从复制相关的培训,主要是让他们了解一下相关的情况,对中断进行简单的处理。
本文主要对MySQL主从复制进行简单的介绍,包括原理简介、搭建步骤、状态检查、同步中断及排查、备库重建。

欢迎转载,请注明作者、出处。

作者:张正
blog:http://space.itpub.net/26355921 
QQ:176036317
如有疑问,欢迎联系。



MySQL主从复制

MySQL主从复制、搭建、状态检查、中断排查及备库重做

2014/8/27

 

 

 

本文档主要对MySQL主从复制进行简单的介绍,包括原理简介、搭建步骤、状态检查、同步中断及排查、备库重建。

 


 TOC \o "1-3" \h \z \u 一、MySQL主从复制概述... 

1、主从复制简介... 

2、主从复制原理、机制... 

3、主从复制原理图... 

二、MySQL主从复制搭建... 

1Master端配置部署... 

2Slave端配置部署... 

3、建立主从同步... 

三、主从复制状态检查及异常处理... 

1、主从复制状态检查... 

2IO_thread异常... 

3sql_thread异常... 

4、主从复制延迟... 

 

 

 


 

一、MySQL主从复制概述

1、主从复制简介

MySQL主从复制就是将一个MySQL实例(Master)中的数据实时复制到另一个MySQL实例(slave)中,而且这个复制是一个异步复制的过程。

实现整个复制操作主要由三个进程完成的,其中两个进程在Slavesql_threadIO_thread),另外一个进程在 MasterIO进程)上。

2、主从复制原理、机制

要实施复制,首先必须打开Master端的binary logbin-log)功能,否则无法实现。因为整个复制过程实际上就是SlaveMaster端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。

复制的基本过程如下:

1)、Slave上面的IO_thread连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

2)、Master接收到来自SlaveIO_thread的请求后,通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息,返回给Slave IO_thread。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log file的以及bin-log pos

3)、SlaveIO_thread接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪 个位置开始往后的日志内容,请发给我”;

4)、SlaveSql_thread检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容,并在本数据库中执行。

 

 

3、主从复制原理图


 


 

 

二、MySQL主从复制搭建

MySQL主从复制搭建主要步骤有:Master端配置部署、Slave端配置部署、建立主从同步

 

1、Master端配置部署

         a、 配置参数:

[mysqld]

server-id=101  # 这个要保证一个主从复制环境中,不要有相同的server-id

log-bin=/data/mysql6001/binlog/mysql-bin.log

log-bin-index=/data/mysql6001/binlog/mysql-bin.index

expire_logs_days=30

         b、 创建用户,并赋予权限:

         GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '******';

       

2、Slave端配置部署

         a、 配置参数:

[mysqld]

server-id=102 

relay-log=/data/mysql6001/relaylog/mysql-relay-bin.log

relay-log-index=/data/mysql6001/relaylog/mysql-relay-bin.index

relay_log_purge=on

 

3、建立主从同步

(重建备库也是使用该方法)

 

         建立主从同步可以从主库上导出数据,也可以从已有的从库上导出数据,然后再导入到新的从库中,change master to建立同步。

3.1 、导出数据

在主库上导出数据:

mysqldump -u*** -p*** -S /data/mysql6001/mysql.sock --default-character-set=utf8 -q --single-transaction --master-data -A  >  /tmp/all_database.sql

(或者)在从库上导出数据:

mysqldump -u*** -p*** -S /data/mysql6001/mysql.sock --default-character-set=utf8 -q --single-transaction --dump-slave -A  >  /tmp/all_database.sql

NOTES

--master-data--dump-slave导出的备份中,会包含master_log_filemaster_log_pos信息。

3.2 、从库导入数据

mysql -u*** -p*** --default-character-set=utf8 < all_database.sql

NOTES

此处导入脚本,就已经在从库中执行了以下操作:

change_master_to

master_log_file=' mysql-bin.000xxx',

master_log_pos=xxxxxx;

3.3 、从库与主机建立同步

以下为建立主从同步最基本的6个项:

change master to

master_host='xxx.xxx.xxx.xxx',     # 主库IP

master_port=6001,              # 主库mysqld的端口

master_user='repl',              # 主库中创建的有REPLICATION SLAVE 权限的用户

master_password='xxxxxxxx',      # 该用户的密码

master_log_file=' mysql-bin.000xxx', # 已在导入时指定了

master_log_pos=xxxxxx;          #已在导入时指定了

指定与主库同步的基本信息后,就可以启动slave进程了:(IO_threadsql_thread

start slave;

三、主从复制状态检查及异常处理

1、主从复制状态检查

主库查看binlog情况:

show master status\G

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

            File: mysql-bin.000303

        Position: 18711563

    Binlog_Do_DB:

Binlog_Ignore_DB:

 

在从库上主要是使用以下命令查看从库与主库的同步状态:

show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.43.128    #主库IP

                  Master_User: repl             #主库复制的用户

                  Master_Port: 6001             #主库mysqld端口

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000303   #io_thread读取主库master_log_file

          Read_Master_Log_Pos: 18711563        # io_thread读取主库master_log_pos

               Relay_Log_File: mysql-relay-bin.000900

                Relay_Log_Pos: 18711709

        Relay_Master_Log_File: mysql-bin.000303  #sql_thread执行主库的master_log_file

             Slave_IO_Running: Yes             #关键的,io_thread是否running

            Slave_SQL_Running: Yes             #关键的,sql_thread是否running

              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: 18711563       #sql_thread执行主库的master_log_pos

              Relay_Log_Space: 18711908

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

1 row in set (0.00 sec)

 

2、IO_thread异常

         IO_thread异常,状态往往是Slave_IO_Running: Connecting NO

         IO_thread是向Master发送请求读取master binlog,如果处于Connecting状态,说明无法正确地与Master进行连接,可能的原因有:

         a网络不通(是否打开防火墙)

         b复制用户的密码不对

         c指定的master_port端口不对

         dmaster上的mysql-bin.xxxxxx被误删

         e主库磁盘空间满了

         通过show slave status\G可以看到相关错误信息,例如:

show slave status\G

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'repl@192.168.43.128:3306' - retry-time: 60      

            retries: 86400

或者通过错误日志看到相关信息,如:

140828 15:47:20 [ERROR] Slave I/O: error connecting to master 'repl@192.168.43.128:3306' -  

              retry-time: 60  retries: 86400, Error_code: 2003

140828 15:47:21 [Note] Event Scheduler: Loaded 0 events

140828 15:47:21 [Note] /home/mysql/mysql/bin/mysqld: ready for connections.

3、sql_thread异常

         sql_thread发生异常,状态就会变为Slave_SQL_Running: NO

         sql_thread发生异常的情况非常多,发生异常后,需要通过以下方法排查和解决:

         a、对比主库和从库的二进制日志的情况:

主库:

show master status\G

            File: mysql-bin.000303

        Position: 18711563

从库:

show slave status\G

               Master_Log_File: mysql-bin.000303   --IO_thread

          Read_Master_Log_Pos: 18711563          --IO_thread

          Relay_Master_Log_File: mysql-bin.000303   --sql_thread

           Exec_Master_Log_Pos: 18711163          --sql_thread

 

         b、通过show slave status\G查看错误信息:

show slave status\G

Last_SQL_Errno: 1062

Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database:

             'test'. Query: 'insert into test values(1,2,3,4,5,6)'

 

         c通过错误日志查看错误信息:

140828 16:27:51 [ERROR] Slave SQL: Error 'Duplicate entry '1' for key 'PRIMARY'' on query.

              Default database: 'test'. Query: 'insert into test values(1,2,3,4,5,6)',

              Error_code: 1062

140828 16:27:51 [Warning] Slave: Duplicate entry '1' for key 'PRIMARY' Error_code: 1062

140828 16:27:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem,

              and restart the slave SQL thread with "SLAVE START". We stopped at log

              'mysql-bin.000303' position 18711163

 

         根据这些报错信息,往往就能够定位到发生异常的原因。如果我们了解产生异常的具体事件,而且能够掌控,可以通过设置sql_slave_skip_counter参数来跳过当前错误。

set global sql_slave_skip_counter=1;

         或者使用slave_skip_errors参数(read only variable),指定跳过某种类型的错误:

参数文件中设置:

slave_skip_errors=1062     #跳过1062错误

         遇到错误时,不要一通百度后,然后根据看起来很类似的操作直接来进行操作。因为网上大部分解决sql_thread异常的方法是:

         a直接set global sql_slave_skip_counter=n; n设置很大的值,即:跳过所有错误),

         b设置slave_skip_errors=all;  跳过所有类型的错误

         c直接查看主库的binlog,然后在从库上直接执行change master to

         这些方法都会导致主从数据不一致。

 

         如果发现从库与主库差异太大,无法通过手动操作数据修改重新建立同步。可以参考上述"MySQL主从复制搭建" 重新搭建从库。

 

4、主从复制延迟

主从复制延迟,可能的原因有:

a主从同步延迟与系统时间的关系,查看主从两台机器间系统时间差

b主从同步延迟与压力、网络、机器性能的关系,查看从库的iocpumem及网络           

c主从同步延迟与lock锁的关系(myisam表读时会堵塞写),尽量避免使用myisam      表。         一个实例里面尽量减少数据库的数量。

d、主从复制发生异常而中断,过很久之后才发现复制异常。可通过查看masterslavestatus估算相差的日志。如果相差太大,则可以考虑重做从库。

 

 


最后附上本文的完整文档,需要忽悠开发或测试人员的,赶紧get了:
MySQL主从复制.docx


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30640692/viewspace-1873753/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-12-17

  • 博文量
    1
  • 访问量
    2156