精华内容
下载资源
问答
  • MySQL 复制

    千次阅读 2020-01-23 17:35:39
    本文对 MySQL 复制功能和用法做了整理和介绍。

    本文对 MySQL 复制功能和用法做了整理和介绍。

    复制(Replication)解决的问题
    1. 数据分布
    2. 负载均衡和读写分离
    3. 备份
    4. 高可用性和故障切换
    5. 大表 DDL 避免锁表
    6. MySQL 升级测试
    复制的工作原理
    1. 主库将数据更改记录到 binlog 中
    2. 备库将主库上的日志复制到自己的 relaylog 中
    3. 备库读取 relaylog 中的事件,将其在备库上重放
    4. 实现方式是:备库会启动一个 I/O 线程,I/O 线程会跟主库建立一个客户端连接,然后在主库上启动一个 binlog dump 线程,该线程不断把新的事件传送给备库
    5. 使用复制,需要在主库上增加一个配库使用的账号,该账号需要有REPLICATION SLAVE,REPLICATION CLIENT权限
    复制的使用
    1. 配置 master
    # 1) my.cnf 配置文件
    [mysqld]
    log-bin=mysql-bin
    server-id=1
    # 2) 获取主库 binlog 位点
    mysql> SHOW MASTER STATUS;
    File: mysql-bin.000001
    Position:98
    
    1. 配置 slave 并开启复制
    # 1) my.cnf 配置文件
    [mysqld]
    log_bin           = mysql-bin
    server_id         = 2
    relay_log         = mysql-relay-bin
    log_slave_updates = 1
    read_only         = 1
    # 2) 设置主库和位点
    mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1',
        -> MASTER_PORT=3306
        -> MASTER_USER='repl',
        -> MASTER_PASSWORD='p4ssword',
        -> MASTER_LOG_FILE='mysql-bin.000001',
    	-> MASTER_LOG_POS=98;
    # 3) 检查位点是否设置正确
    mysql> SHOW SLAVE STATUS\G
    # 4) 开启复制
    mysql> START SLAVE ;
    
    1. SHOW SLAVE STATUS 中各个位点信息的含义
    Master_Log_File			# 目前拉取的主库文件
    Read_Master_Log_Pos		# 目前拉取的主库文件位置
    Relay_Log_File			# 当前执行的本库 relay log 文件
    Relay_Log_Pos			# 当前执行的 relay log 文件位点
    Relay_Master_Log_File	# 当前执行到的对应的主库文件的位置
    
    1. 暂时关闭 binlog: set sql_log_bin=0;
    2. 安全删除 binlog
    PURGE MASTER LOGS TO 'mysql-bin.007379';		# 按文件名删除
    PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26'; # 按时间删除
    
    复制的类型
    • 基于语句的复制
      1. 复制快,节约 binlog 空间
      2. 复杂场景下(使用函数或存储过程等)存在各种异常和 bug
    • 基于行的复制
      1. 简单灵活适应性强
      2. 数据有冗余,出错方便修复
      3. DDL 还是语句的复制
    • 混合性复制
      1. 适应性最强
      2. DDL 也是基于行的复制,binlog 会超级大
      3. 可以理解为,基于行复制的扩展
    • 基于 GTID 的复制
      1. 简单灵活,不用记录位点,会根据全局事务ID自动寻点
      2. 因为是基于事务的复制(也是基于行的),所以不支持非事务的存储引擎
      3. 可以理解为,基于行赋值的升级
    复制异常
    • 写冲突,insert 的主键/唯一键已存在,处理的方式往往是:
      1. 记录下错误发生的表和对应主键
      2. 使用SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;跳过这次冲突
      3. 检查主备库中对应主键的记录是否一致
    • 写异常,update 前镜像不一致,导致数据更新失败:
      1. 使用基于行的复制时,update 操作在备库上会匹配前镜像,也就是带有 where 条件(见下节 binlog 文件结构)
      2. 当前镜像不匹配时,update 中最新的记录不会更新到备库
      3. 这种情况往往发生在位点错误或 binlog 丢失的情况下
    binlog
    • 把 binlog 解析成可读文件
    mysqlbinlog binlog.000001 --database=name --start-datetime=name  \
    	--stop-datetime=name --start-position 245770410  \
    	--stop-position 245793673 -f -v \
    	--base64-output=decode-rows > tmpfile 
    
    • binlog 解析后的文件结构(update 操作)
    BEGIN
    /*!*/;
    # at 92105505
    # at 92105582
    #160525 19:44:50 server id 101717547  end_log_pos 92105582      Table_map: `im_user_data_0005`.`im_user_cfg_tbl` mapped to number 1450
    #160525 19:44:50 server id 101717547  end_log_pos 92106296      Update_rows: table id 1450 flags: STMT_END_F
    BINLOG '
    MpBFVxMrFhAGTQAAAG5rfQUAAKoFAAAAAAEAEWltX3VzZXJfZGF0YV8wMDA1AA9pbV91c2VyX2Nm
    Z190YmwACAMP/AMBAQwMA0AAA8Q=
    (二进制信息略)
    '/*!*/;
    ### UPDATE im_user_data_0005.im_user_cfg_tbl
    ### WHERE
    ###   @1=222 /* INT meta=0 nullable=0 is_null=0 */
    ###   @2='xxxxx' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
    ###   @3='xxxxx' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
    ###   @4=222 /* INT meta=0 nullable=0 is_null=0 */
    ###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
    ###   @6=1 /* TINYINT meta=0 nullable=0 is_null=0 */
    ###   @7=2012-07-12 11:22:13 /* DATETIME meta=0 nullable=1 is_null=0 */
    ###   @8=2016-05-24 20:23:03 /* DATETIME meta=0 nullable=1 is_null=0 */
    ### SET
    ###   @1=1111 /* INT meta=0 nullable=0 is_null=0 */
    ###   @2='xxxxx' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
    ###   @3='xxxxx' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
    ###   @4=1111 /* INT meta=0 nullable=0 is_null=0 */
    ###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
    ###   @6=1 /* TINYINT meta=0 nullable=0 is_null=0 */
    ###   @7=2012-07-12 11:22:13 /* DATETIME meta=0 nullable=1 is_null=0 */
    ###   @8=2016-05-25 19:44:50 /* DATETIME meta=0 nullable=1 is_null=0 */
    # at 92106296
    #160525 19:44:50 server id 101717547  end_log_pos 92106323      Xid = 3286612943
    COMMIT/*!*/;
    
    常见的拓扑结构

      每一个 MySQL 实例都可以即做主库由做备库,一个主库可以有多个备库,一个备库只能有一个主库。
      理论上 MySQL 可以支持各种复杂(网状)的复制拓扑结构,但是不同的业务场景会限制复制拓扑的设计。
    在这里插入图片描述

    • 主主结构
      1. 两个互为主库,但是应用只写其中一个,另一个可以作为热备份,或者作为只读库分担读流量
      2. 使用灵活,运维方便(因为切换方便)
        在这里插入图片描述
    • 一主多备
      1. 在读流量极大的情况下,需要这种一个主库(提供数据更新服务)多个备库(提供数据查询服务)的结构
      2. 这种结构运维的难点是主库 Crash 后,需要重新选择一个数据最完整的备库作为新的主库
    主备一致性保证
    • semi-sync (半同步)
      1. 半同步介于异步和全同步(sync_binlog 打开)之间
      2. 主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可
      3. 半同步保证了主备的最终一致性,同时降低了主库事务 commit 的等待时间
    参考资料

    《高性能MySQL(第3版)》

    展开全文
  • mysql复制--主从复制配置

    万次阅读 多人点赞 2018-04-12 00:04:36
    当单台MYSQL服务器无法满足当前网站流量时的优化方案。需要搭建mysql集群技术。 复制功能: 数据分布 负载均衡(读) 备份 高可用和故障切换 MySQL升级测试 复制方式: 主–从复制 ...

    当单台 MYSQL 服务器无法满足当前网站流量时的优化方案。需要搭建 mysql 集群技术。

    复制功能:

    • 数据分布
    • 负载均衡(读)
    • 备份
    • 高可用和故障切换
    • MySQL升级测试

    复制方式:

    1. 主–从复制
    2. 主–主复制
    3. 半同步复制

    复制原理:

    Mysql 中有一种日志叫做 bin 日志(二进制日志)。这个日志会记录下所有修改了数据库的SQL 语句(insert,update,delete,create/alter/drop table, grant 等等)。
    主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍,这样从服务器上的数据就和主服务器上的数据相同了。

    复制流程图:

    这里写图片描述

    复制过程:

    1. 主节点必须启用二进制日志,记录任何修改了数据库数据的事件。
    2. 从节点开启一个线程(I/O Thread)把自己扮演成 mysql 的客户端,通过 mysql 协议,请求主节点的二进制日志文件中的事件
    3. 主节点启动一个线程(dump Thread),检查自己二进制日志中的事件,跟对方请求的位置对比,如果不带请求位置参数,则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
    4. 从节点接收到主节点发送过来的数据把它放置到中继日志(Relay log)文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置(主节点中的二进制文件会有多个,在后面详细讲解)。
    5. 从节点启动另外一个线程(sql Thread ),把 Relay log 中的事件读取出来,并在本地再执行一次。

    复制中线程的作用:

    从节点:

    • I/O Thread: 从 Master 节点请求二进制日志事件,并保存于中继日志中。
    • Sql Thread: 从Relay log 中读取日志事件并在本地完成重放。

    主节点:

    • Dump Thread:为每个 Slave 的 I/O Thread 启动一个 dump 线程,用于向从节点发送二进制事件。

    **思考:**从节点需要建立二进制日志文件吗?
    看情况,如果从节点需要作为其他节点的主节点时,是需要开启二进制日志文件的。这种情况叫做级联复制。如果只是作为从节点,则不需要创建二进制文件。

    Mysql复制特点:

    1. 异步复制:主节点中一个用户请求一个写操作时,主节点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成,再响应用户。主节点只需要把写入操作在本地完成,就响应用户。但是,从节点中的数据有可能会落后主节点,可以使用(很多软件来检查是否落后)
    2. 主从数据不一致。

    主从复制配置过程:

    主节点:

    1. 启用二进制日志。
    2. 为当前节点设置一个全局唯一的server_id。
    3. 创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

    从节点:

    1. 启动中继日志。
    2. 为当前节点设置一个全局唯一的server_id。
    3. 使用有复制权限的用户账号连接至主节点,并启动复制线程。

    配置演示:

    测试环境:
    主节点:192.168.0.196
    从节点:192.168.0.198
    mysql版本:mysql-5.5.17
    linux:centos6.5

    1:编辑主节点配置文件
    这里写图片描述
    2:启用二进制日志文件

    • 添加:log-bin = mysql-bin
    • 添加 :server-id =1
    • 添加:innodb-file-per-table =ON
    • 添加:skip_name_resolve=ON
      这里写图片描述

    3:启动mysql服务,并连接mysql
    这里写图片描述
    4:查看日志信息
    1:)查看二进制日志是否开启
    这里写图片描述
    2:)查看主节点二进制日志列表
    这里写图片描述
    3:)查看主节点的server id
    这里写图片描述
    5:在主节点上创建有复制权限的用户。REPLIACTION SLAVE ,REOPLIATION CLIENT
    这里写图片描述
    6:刷新
    这里写图片描述
    主服务器配置完成。
    注意:确保主服务器的iptables没有阻断3306的访问端口。

    7:编辑从节点配置文件
    这里写图片描述
    8:开启中继日志

    • 添加:relay-log=relay-log
    • 添加:relay-log-index=relay-log.index
    • 添加:server-id=2
    • 添加:innodb_file_per_table=ON
    • 添加:skip_name_resolve=ON
      这里写图片描述
      9:启动从节点mysql服务,并登陆mysql
      这里写图片描述
      10:查看从节点日志信息
      1:)查看中继日志是否开启
      这里写图片描述
      2:)产看server 相关信息
      这里写图片描述
      11:在从节点配置访问主节点的参数信息
      添加 主节点主机,访问主节点的用户名及密码,主节点二进制文件信息。
      注意:主节点的二进制文件一定要是二进制列表中的最后一个二进制文件。
      这里写图片描述
    CHANGE MASTER TO MASTER_HOST='192.168.0.196',MASTER_USER='repluser',MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000042',MASTER_LOG_POS=362;
    

    这里写图片描述
    12:查看从节点的状态信息
    因为没有启动 从节点的复制线程,IO线程 和 SQL 线程都为NO.
    这里写图片描述

    13:启动复制线程
    START SLAVE 可以指定线程类型:IO_THREAD ,SQL_THREAD, 如果不指定,则两个都启动。
    这里写图片描述
    14:再次查看从节点状态信息
    这里写图片描述

    至此,mysql的主----从复制配置完成。
    15:测试
    1:)在主节点创建数据库,并查看主节点二进制日志信息
    这里写图片描述
    2:)在从节点查找二进制日志信息,并查看mydb数据库是否复制成功
    这里写图片描述
    这里写图片描述

    主从复制架构中应注意的问题:

    从节点要设置某些限定使得它不能进行写操作,才能保证复制当中的数据一致。

    1:限制从服务器为只读
    在从服务器上设置:
    read_only = ON,但是此限制对拥有SUPER权限 的用户均无效。
    阻止所有用户:
    mysq>FLUSH TABLES WITH READ LOCK;

    2:如何保证主从复制时的事物安全?
    1:在主节点设置参数
    sync_binlog=1: Mysql开启bin-log日志使用bin-log时,默认情况下,并不是每次执行写入就与硬盘同步,这样在服务器崩溃时,就可能导致bin-log最后的语句丢失。可以通过这个参数来调节,sync_binlog=N,使执行N次写入后,与硬盘同步。1是最安全的,但是也是最慢的。
    如果用到innode 存储引擎:
    innodb_flush_logs_at_trx_commit=ON(刷写日志:在事务提交时,要将内存中跟事务相关的数据立即刷写到事务日志中去。)
    innodb_support_xa=ON (分布式事务:基于它来做两段式提交功能)
    sync_master_info=1:每次给从节点dump一些事件信息之后,主节点的master info 信息会立即同步到磁盘上。让从服务器中的 master_info 及时更新。
    这里写图片描述
    2:在每个slave节点
    skip_slave_start =ON (跳过自动启动,使用手动启动。)
    relay_log也会在内从中先缓存,然后在同步到relay_log中去,可以使用下面参数使其立即同步。
    sync_relay_log =1 ,默认为10000,即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新,交由OS的cache控制。
    sync_relay_log_info=1每间隔多少事务刷新relay-log.info,如果是table(innodb)设置无效,每个事务都会更新

    注: 在从节点中 master.info是记录在主节点复制位置的文件。
    这里写图片描述
    relylog_info: 本地将来至于主节点的哪一个二进制文件中position并且保存文本地哪一个中继日志中的哪一个postion. 从节点启动时也需要根据relay-log.info定位本地relay-log.
    这里写图片描述

    展开全文
  • MySQL 复制滞后与延时复制

    千次阅读 2015-11-24 22:04:55
    MySQL复制被普遍认为是十分有效的,主服务器进行更改后,从服务器可在几秒内做出相应的改动。但如果发生两者之间同步缓慢的问题, 那么主要有以下两个原因: 从结点磁盘问题: 复制操作对每个数据库都是由一个线程...

    1:MySQL 复制滞后解决
    MySQL复制被普遍认为是十分有效的,主服务器进行更改后,从服务器可在几秒内做出相应的改动。但如果发生两者之间同步缓慢的问题, 那么主要有以下两个原因:
    从结点磁盘问题: 复制操作对每个数据库都是由一个线程来完成,通常执行变更时的滞后是由磁盘延迟引起的。在这种情况下,您应该考虑使用SSD加速这个过程。
    带宽低/网络延迟高: 如果两个服务器位于远程位置(高延迟的情况下)或服务器之间的存在带宽较低的问题,我们应使用下面的方法之一或者两者结合使用,以最大限度地减少服务器间通信量。
    使用基于语句的复制:基于行的复制会为数据库中每一行的变更创建一个SQL 语句。基于语句的复制是应用程序发送的实际SQL语句的记录。通常基于语句的复制在记录大小方面更为有效。然而,你应该意识到,当你使用UPDATE … LIMIT1时,基于语句的复制可能并不十分有效
    压缩通信量: MySQL支持使用 slave_compressed_protocol参数进行日志压缩复制。这种方法将减少高达80%的服务器之间的通信。然而,压缩是计算密集型的,所以你应该意识到这样会产生一些额外的CPU利用率(这通常不属于数据库中的问题)。这个参数应该在两个服务器上都启用:
    动态的从MySQL命令行输入:SET GLOBALslave_compressed_protocol = 1;
    在MySQL配置文件中进行配置:

    compress master-slave communication

    slave_compressed_protocol = 1
    最起码,要理解你的复制行为为何滞后,然后了解如何使用正确的方法来解决滞后问题。是的,它就是这么容易,且十分有效。
    来自:http://www.searchdatabase.com.cn/showcontent_87714.htm

    2:MySQL 延时复制设置
    Mysql (需5.6以上版本)延迟复制配置,通过设置Slave上的MASTER TO MASTER_DELAY参数实现:
    CHANGE MASTER TO MASTER_DELAY = N;
    N为多少秒,该语句设置从数据库延时N秒后,再与主数据库进行数据同步复制
    具体操作:
    登陆到Slave数据库服务器
    mysql>stop slave;
    mysql>CHANGE MASTER TO MASTER_DELAY = 600;
    mysql>start slave;
    mysql>show slave status \G;
    查看SQL_Delay的值为600,表示设置成功。
    注释:
    SQL_Delay:一个非负整数,表示秒数,Slave滞后多少秒于master。
    SQL_Remaining_Delay:当 Slave_SQL_Running_State 等待,直到MASTER_DELAY秒后,Master执行的事件,
    此字段包含一个整数,表示有多少秒左右的延迟。在其他时候,这个字段是0。
    来自:http://my.oschina.net/emolee/blog/197916

    3:可以查看mysql的官网
    MySQL 5.6 supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time. The default delay is 0 seconds. Use the MASTER_DELAY option for CHANGE MASTER TO to set the delay to N seconds:
    CHANGE MASTER TO MASTER_DELAY = N;
    An event received from the master is not executed until at least N seconds later than its execution on the master. The exceptions are that there is no delay for format description events or log file rotation events, which affect only the internal state of the SQL thread.
    Delayed replication can be used for several purposes:
    重点:To protect against user mistakes on the master. A DBA can roll back a delayed slave to the time just before the disaster.
    To test how the system behaves when there is a lag. For example, in an application, a lag might be caused by a heavy load on the slave. However, it can be difficult to generate this load level. Delayed replication can simulate the lag without having to simulate the load. It can also be used to debug conditions related to a lagging slave.
    To inspect what the database looked like long ago, without having to reload a backup. For example, if the delay is one week and the DBA needs to see what the database looked like before the last few days’ worth of development, the delayed slave can be inspected.
    START SLAVE and STOP SLAVE take effect immediately and ignore any delay. RESET SLAVE resets the delay to 0.
    SHOW SLAVE STATUS has three fields that provide information about the delay:
    SQL_Delay: A nonnegative integer indicating the number of seconds that the slave must lag the master.
    SQL_Remaining_Delay: When Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master executed event, this field contains an integer indicating the number of seconds left of the delay. At other times, this field is NULL.
    Slave_SQL_Running_State: A string indicating the state of the SQL thread (analogous to Slave_IO_State). The value is identical to the State value of the SQL thread as displayed by SHOW PROCESSLIST.
    When the slave SQL thread is waiting for the delay to elapse before executing an event, SHOW PROCESSLIST displays its State value as Waiting until MASTER_DELAY seconds after master executed event.
    The relay-log.info file now contains the delay value, so the file format has changed. See Section 17.2.2.2, “Slave Status Logs”. In particular, the first line of the file now indicates how many lines are in the file. If you downgrade a slave server to a version older than MySQL 5.6, the older server will not read the file correctly. To address this, modify the file in a text editor to delete the initial line containing the number of lines.
    from:http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html

    展开全文
  • 理解MySQL复制(Replication)

    千次阅读 2016-04-30 10:19:26
    | 理解MySQL复制(Replication) 1、复制概述 1.1、复制解决的问题 数据复制技术有以下一些特点: (1) 数据分布 (2) 负载平衡(load balancing) (3) 备份 (4) 高可用性(high availability)和容错 1.2、复制如何工作 ...

    | 理解MySQL复制(Replication)

     

    1、复制概述

    1.1、复制解决的问题
    数据复制技术有以下一些特点:
    (1)    数据分布
    (2)    负载平衡(load balancing)
    (3)    备份
    (4)    高可用性(high availability)和容错

    1.2、复制如何工作
    从高层来看,复制分成三步:
    (1)    master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);
    (2)    slave将master的binary log events拷贝到它的中继日志(relay log);
    (3)    slave重做中继日志中的事件,将改变反映它自己的数据。

    下图描述了这一过程:

     

    该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前,master在二日志记录这些改变。MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master通知存储引擎提交事务。
    下一步就是slave将master的binary log拷贝到它自己的中继日志。首先,slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接,然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件,如果已经跟上master,它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
    SQL slave thread处理该过程的最后一步。SQL线程从中继日志读取事件,更新slave的数据,使其与master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。
    此外,在master中也有一个工作线程:和其它MySQL的连接一样,slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的,也就是说master上的并行更新操作不能在slave上并行操作。

     

    2、体验MySQL复制
    MySQL开始复制是很简单的过程,不过,根据特定的应用场景,都会在基本的步骤上有一些变化。最简单的场景就是一个新安装的master和slave,从高层来看,整个过程如下:
    (1)在每个服务器上创建一个复制帐号;
    (2)配置master和slave;
    (3)Slave连接master开始复制。

    2.1、创建复制帐号
    每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中。假如,你想创建repl用户,如下:
    mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.140.%' IDENTIFIED BY 'repl';

    2.2、配置master
    接下来对master进行配置,包括打开二进制日志,指定唯一的servr ID。例如,在配置文件加入如下值:
    [mysqld]
    log-bin=mysql-bin
    server-id=1
    重启master,运行SHOW MASTER STATUS,输出如下:

     

    2.3、配置slave
    Slave的配置与master类似,你同样需要重启slave的MySQL。如下:
    log_bin           = mysql-bin
    server_id         = 2
    relay_log         = mysql-relay-bin
    log_slave_updates = 1
    read_only         = 1
    server_id是必须的,而且唯一。slave没有必要开启二进制日志,但是在一些情况下,必须设置,例如,如果slave为其它slave的master,必须设置bin_log。在这里,我们开启了二进制日志,而且显示的命名(默认名称为hostname,但是,如果hostname改变则会出现问题)。
    relay_log配置中继日志,log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。
    有些人开启了slave的二进制日志,却没有设置log_slave_updates,然后查看slave的数据是否改变,这是一种错误的配置。所以,尽量使用read_only,它防止改变数据(除了特殊的线程)。但是,read_only并是很实用,特别是那些需要在slave上创建表的应用。

     

    2.4、启动slave

    接下来就是让slave连接master,并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作,而应该使用CHANGE MASTER TO语句,该语句可以完全取代对配置文件的修改,而且它可以为slave指定不同的master,而不需要停止服务器。如下:

    mysql> CHANGE MASTER TO MASTER_HOST='192.168.140.129',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;

    MASTER_LOG_POS的值为0,因为它是日志的开始位置。然后,你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确:

    mysql> SHOW SLAVE STATUS\G

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

                 Slave_IO_State:

                    Master_Host: 192.168.140.129

                    Master_User: repl

                    Master_Port: 3306

                  Connect_Retry: 60

                Master_Log_File: mysql-bin.000001

            Read_Master_Log_Pos: 4

                 Relay_Log_File: mysql-relay-bin.000001

                  Relay_Log_Pos: 4

          Relay_Master_Log_File: mysql-bin.000001

               Slave_IO_Running: No

              Slave_SQL_Running: No

                                 ...omitted...

          Seconds_Behind_Master: NULL

    Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running表明slave还没有开始复制过程。日志的位置为4而不是0,这是因为0只是日志文件的开始位置,并不是日志位置。实际上,MySQL知道的第一个事件的位置是4。

    为了开始复制,你可以运行:

    mysql> START SLAVE;

    运行SHOW SLAVE STATUS查看输出结果:

    mysql> SHOW SLAVE STATUS\G

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

                 Slave_IO_State: Waiting for master to send event

                    Master_Host: 192.168.140.129

                    Master_User: repl

                    Master_Port: 3306

                  Connect_Retry: 60

                Master_Log_File: mysql-bin.000001

            Read_Master_Log_Pos: 164

                 Relay_Log_File: mysql-relay-bin.000001

                  Relay_Log_Pos: 164

          Relay_Master_Log_File: mysql-bin.000001

               Slave_IO_Running: Yes

              Slave_SQL_Running: Yes

                                 ...omitted...

          Seconds_Behind_Master: 0

    注意,slave的I/O和SQL线程都已经开始运行,而且Seconds_Behind_Master不再是NULL。日志的位置增加了,意味着一些事件被获取并执行了。如果你在master上进行修改,你可以在slave上看到各种日志文件的位置的变化,同样,你也可以看到数据库中数据的变化。

    你可查看master和slave上线程的状态。在master上,你可以看到slave的I/O线程创建的连接:

     

    mysql> show processlist \G

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

         Id: 1

       User: root

       Host: localhost:2096

         db: test

    Command: Query

       Time: 0

     State: NULL

       Info: show processlist

    *************************** 2. row ***************************

         Id: 2

       User: repl

       Host: localhost:2144

         db: NULL

    Command: Binlog Dump

       Time: 1838

     State: Has sent all binlog to slave; waiting for binlog to be updated

       Info: NULL

    2 rows in set (0.00 sec)

     行2为处理slave的I/O线程的连接。
    在slave上运行该语句:

    mysql> show processlist \G

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

         Id: 1

       User: system user

       Host:

         db: NULL

    Command: Connect

       Time: 2291

     State: Waiting for master to send event

       Info: NULL

    *************************** 2. row ***************************

         Id: 2

       User: system user

       Host:

         db: NULL

    Command: Connect

       Time: 1852

     State: Has read all relay log; waiting for the slave I/O thread to update it

       Info: NULL

    *************************** 3. row ***************************

         Id: 5

       User: root

       Host: localhost:2152

         db: test

    Command: Query

       Time: 0

     State: NULL

       Info: show processlist

    3 rows in set (0.00 sec)


    行1为I/O线程状态,行2为SQL线程状态。

    2.5、从另一个master初始化slave
    前面讨论的假设你是新安装的master和slave,所以,slave与master有相同的数据。但是,大多数情况却不是这样的,例如,你的master可能已经运行很久了,而你想对新安装的slave进行数据同步,甚至它没有master的数据。
    此时,有几种方法可以使slave从另一个服务开始,例如,从master拷贝数据,从另一个slave克隆,从最近的备份开始一个slave。Slave与master同步时,需要三样东西:
    (1)master的某个时刻的数据快照;
    (2)master当前的日志文件、以及生成快照时的字节偏移。这两个值可以叫做日志文件坐标(log file coordinate),因为它们确定了一个二进制日志的位置,你可以用SHOW MASTER STATUS命令找到日志文件的坐标;
    (3)master的二进制日志文件。

    可以通过以下几中方法来克隆一个slave:
    (1)    冷拷贝(cold copy)
    停止master,将master的文件拷贝到slave;然后重启master。缺点很明显。
    (2)    热拷贝(warm copy)
    如果你仅使用MyISAM表,你可以使用mysqlhotcopy拷贝,即使服务器正在运行。
    (3)    使用mysqldump
    使用mysqldump来得到一个数据快照可分为以下几步:
    <1>锁表:如果你还没有锁表,你应该对表加锁,防止其它连接修改数据库,否则,你得到的数据可以是不一致的。如下:
    mysql> FLUSH TABLES WITH READ LOCK;
    <2>在另一个连接用mysqldump创建一个你想进行复制的数据库的转储:
    shell> mysqldump --all-databases --lock-all-tables >dbdump.db
    <3>对表释放锁。
    mysql> UNLOCK TABLES;

    3、深入复制
    已经讨论了关于复制的一些基本东西,下面深入讨论一下复制。

    3.1、基于语句的复制(Statement-Based Replication)
    MySQL 5.0及之前的版本仅支持基于语句的复制(也叫做逻辑复制,logical replication),这在数据库并不常见。master记录下改变数据的查询,然后,slave从中继日志中读取事件,并执行它,这些SQL语句与master执行的语句一样。
    这种方式的优点就是实现简单。此外,基于语句的复制的二进制日志可以很好的进行压缩,而且日志的数据量也较小,占用带宽少——例如,一个更新GB的数据的查询仅需要几十个字节的二进制日志。而mysqlbinlog对于基于语句的日志处理十分方便。
     
    但是,基于语句的复制并不是像它看起来那么简单,因为一些查询语句依赖于master的特定条件,例如,master与slave可能有不同的时间。所以,MySQL的二进制日志的格式不仅仅是查询语句,还包括一些元数据信息,例如,当前的时间戳。即使如此,还是有一些语句,比如,CURRENT USER函数,不能正确的进行复制。此外,存储过程和触发器也是一个问题。
    另外一个问题就是基于语句的复制必须是串行化的。这要求大量特殊的代码,配置,例如InnoDB的next-key锁等。并不是所有的存储引擎都支持基于语句的复制。

    3.2、基于记录的复制(Row-Based Replication)
    MySQL增加基于记录的复制,在二进制日志中记录下实际数据的改变,这与其它一些DBMS的实现方式类似。这种方式有优点,也有缺点。优点就是可以对任何语句都能正确工作,一些语句的效率更高。主要的缺点就是二进制日志可能会很大,而且不直观,所以,你不能使用mysqlbinlog来查看二进制日志。
    对于一些语句,基于记录的复制能够更有效的工作,如:
    mysql> INSERT INTO summary_table(col1, col2, sum_col3) SELECT col1, col2, sum(col3) FROM enormous_table GROUP BY col1, col2;
    假设,只有三种唯一的col1和col2的组合,但是,该查询会扫描原表的许多行,却仅返回三条记录。此时,基于记录的复制效率更高。
    另一方面,下面的语句,基于语句的复制更有效:
    mysql> UPDATE enormous_table SET col1 = 0;
    此时使用基于记录的复制代价会非常高。由于两种方式不能对所有情况都能很好的处理,所以,MySQL 5.1支持在基于语句的复制和基于记录的复制之前动态交换。你可以通过设置session变量binlog_format来进行控制。

    3.3、复制相关的文件
    除了二进制日志和中继日志文件外,还有其它一些与复制相关的文件。如下:
    (1)mysql-bin.index
    服务器一旦开启二进制日志,会产生一个与二日志文件同名,但是以.index结尾的文件。它用于跟踪磁盘上存在哪些二进制日志文件。MySQL用它来定位二进制日志文件。

     (2)mysql-relay-bin.index
    该文件的功能与mysql-bin.index类似,但是它是针对中继日志,而不是二进制日志。内容如下:
    .\mysql-02-relay-bin.000017
    .\mysql-02-relay-bin.000018
    (3)master.info
    保存master的相关信息。不要删除它,否则,slave重启后不能连接master。
     

     I/O线程更新master.info文件

     

     (4)relay-log.info  

    包含slave中当前二进制日志和中继日志的信息。

     

     3.4、发送复制事件到其它slave
    当设置log_slave_updates时,你可以让slave扮演其它slave的master。此时,slave把SQL线程执行的事件写进行自己的二进制日志(binary log),然后,它的slave可以获取这些事件并执行它。如下:

     

    3.5、复制过滤(Replication Filters)
    复制过滤可以让你只复制服务器中的一部分数据,有两种复制过滤:在master上过滤二进制日志中的事件;在slave上过滤中继日志中的事件。如下:

     

    4、复制的常用拓扑结构
    复制的体系结构有以下一些基本原则:
    (1)    每个slave只能有一个master;
    (2)    每个slave只能有一个唯一的服务器ID;
    (3)    每个master可以有很多slave;
    (4)    如果你设置log_slave_updates,slave可以是其它slave的master,从而扩散master的更新。

    MySQL不支持多主服务器复制(Multimaster Replication)——即一个slave可以有多个master。但是,通过一些简单的组合,我们却可以建立灵活而强大的复制体系结构。

    4.1、单一master和多slave
    由一个master和一个slave组成复制系统是最简单的情况。Slave之间并不相互通信,只能与master进行通信。如下:

     如果写操作较少,而读操作很时,可以采取这种结构。你可以将读操作分布到其它的slave,从而减小master的压力。但是,当slave增加到一定数量时,slave对master的负载以及网络带宽都会成为一个严重的问题。
    这种结构虽然简单,但是,它却非常灵活,足够满足大多数应用需求。一些建议:
    (1)    不同的slave扮演不同的作用(例如使用不同的索引,或者不同的存储引擎);
    (2)    用一个slave作为备用master,只进行复制;
    (3)    用一个远程的slave,用于灾难恢复;
    4.2、主动模式的Master-Master(Master-Master in Active-Active Mode)
    Master-Master复制的两台服务器,既是master,又是另一台服务器的slave。如图:

    主动的Master-Master复制有一些特殊的用处。例如,地理上分布的两个部分都需要自己的可写的数据副本。这种结构最大的问题就是更新冲突。假设一个表只有一行(一列)的数据,其值为1,如果两个服务器分别同时执行如下语句:
    在第一个服务器上执行:
    mysql> UPDATE tbl SET col=col + 1;
    在第二个服务器上执行:
    mysql> UPDATE tbl SET col=col * 2;
    那么结果是多少呢?一台服务器是4,另一个服务器是3,但是,这并不会产生错误。
    实际上,MySQL并不支持其它一些DBMS支持的多主服务器复制(Multimaster Replication),这是MySQL的复制功能很大的一个限制(多主服务器的难点在于解决更新冲突),但是,如果你实在有这种需求,你可以采用MySQL Cluster,以及将Cluster和Replication结合起来,可以建立强大的高性能的数据库平台。但是,可以通过其它一些方式来模拟这种多主服务器的复制。

    4.3、主动-被动模式的Master-Master(Master-Master in Active-Passive Mode)
    这是master-master结构变化而来的,它避免了M-M的缺点,实际上,这是一种具有容错和高可用性的系统。它的不同点在于其中一个服务只能进行只读操作。如图:

     4.4、带从服务器的Master-Master结构(Master-Master with Slaves)
    这种结构的优点就是提供了冗余。在地理上分布的复制结构,它不存在单一节点故障问题,而且还可以将读密集型的请求放到slave上。

     

     

    关注公众号「Python专栏」,后台回复「腾讯架构资源1」获取由腾讯架构师提供的大数据资源1份~

    <div align="center"><img src="https://img-blog.csdnimg.cn/20190818220647999.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIzNjU4Mjg=,size_16,color_FFFFFF,t_70" width="30%"/></div>
     

     

    展开全文
  • MYSQL复制原理及其流程-详

    千次阅读 2018-05-22 11:06:07
    MySQL复制概述保证主服务器(Master)和从服务器(Slave)的数据是一致性的,向Master插入数据后,Slave会自动从Master把修改的数据同步过来(有一定的延迟),通过这种方式来保证数据的一致性,就是Mysql复制延时性...
  • MySql复制一列到另一列

    千次阅读 2018-12-14 11:43:31
    mysql复制一列到另一列 UPDATE 表名 SET B列名=A列名 异表复制 需求一:把一个表某个字段内容复制到另一张表的某个字段 实现1: update B set extra = A.extra from A join B on (A.id = B.id); 实现2: ...
  • MySQL的复制策略是异步的,单向的,Master->Slave,下面是MySQL复制的整体逻辑: 1 所有对Master的更新操作比如insert,update,delete等都会被写到binary log里。 2 Slave上的IO线程向Master上的IO线程索要bin log,...
  • mysql复制表的两种方式

    万次阅读 2018-10-11 11:54:03
    mysql复制表的两种方式。 第一、只复制表结构到新表 create table 新表 select * from 旧表 where 1=2 或者 create table 新表 like 旧表  第二、复制表结构及数据到新表 create table新表 select * from 旧...
  • MySQL复制表结构 表数据

    千次阅读 2017-03-10 11:53:06
    MySQL复制表结构 表数据 1、复制表结构及数据到新表CREATE TABLE 新表 SELECT * FROM 旧表这种方法会将oldtable中所有的内容都拷贝过来,当然我们可以用delete from newtable;来删除。不过这种方法的一个最不好的...
  • MySQL复制

    千次阅读 2019-01-12 17:37:52
    创建表的CREATE TABLE命令还有另外一种语法结构,在一张已经存在的数据表的基础上创建一份该表的拷贝,也就是复制表。 方式一:仅复制表结构。 CREATE TABLE [IF NOT EXISTS] 数据表名 {LIKE 源数据表名 | (LIKE...
  • MySQL复制的三种架构

    千次阅读 2016-01-30 16:24:41
    MySQL复制相对比较简单,就是将主数据库的DDL和DML操作通过二进制日志传到复制服务器(也叫从库)上,然后在从库 上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。其他主要商业关系型数据库则...
  • MYSQL复制的几种模式

    千次阅读 2012-05-21 09:28:31
    MYSQL复制的几种模式 MySQL 5.1 中,在复制方面的改进就是引进了新的复制技术:基于行的复制。 MYSQL复制的几种模式 MySQL 5.1 中,在复制方面的改进就是引进了新的复制技术:基于行的复制。 简言之,这种新技术...
  • MySQL 复制

    千次阅读 2018-04-28 09:19:12
    复制表结构 只复制表结构到新表: -- 语法: create table 新表 select * from 旧表 where 1=2 CREATE TABLE bb SELECT * FROM article WHERE 1=2; 或者 -- 语法: create table 新表 like 旧表 CREATE ...
  • mysql复制-mysql双主热备实现

    千次阅读 2018-09-19 16:09:50
    在配置双主热备前 需要先了解 mysql主从复制原理 :https://blog.csdn.net/qq_16399991/article/details/82749333 和 mysql搭建 主从复制:https://blog.csdn.net/qq_16399991/article/details/82740881 。...
  • MySQL复制表的三种方式

    万次阅读 多人点赞 2020-06-08 00:36:09
    文章目录复制表结构及其数据只复制表结构只复制表数据 复制表结构及其数据 下面这个语句会拷贝数据到新表中。 注意:这个语句其实只是把select语句的结果建一个表,所以新表不会有主键,索引。 create table table_...
  • MySQL复制记录和复制表

    千次阅读 2019-06-03 15:30:28
    mysql> create table user (id int primary key,name varchar(20));创建表,同时设置表的结构 mysql> desc user;查询表的结构 +-------+-------------+------+-----+---------+-------+ | Field | Type | N...
  • mysql复制表以及复制数据库

    万次阅读 2017-08-09 11:46:40
    (一)将旧表复制到新表 1、CREATE TABLE新表 SELECT* FROM旧表; 该语句只是复制表结构以及数据,它不会复制与表关联的其他数据库对象,如索引,主键约束,外键约束,触发器等。 CREATE TABLE if not exists ...
  • Mysql复制表结构、表数据

    千次阅读 2012-08-01 10:24:24
    顺便转一下Mysql复制表结构、表数据的方法: 1、复制表结构及数据到新表 CREATE TABLE 新表 SELECT * FROM 旧表 这种方法会将oldtable中所有的内容都拷贝过来,当然我们可以用delete from newtable;来删除...
  • mysql复制--复制过滤

    千次阅读 2018-04-17 10:47:00
    复制过滤: 让从节点仅仅复制指定的数据库,或指定数据库的指定数据表。主服务器有10个数据库,而从节点只需要同步其中的一两个数据库。这个时候就需要复制过滤。复制过滤器可以在主节点中实现,也可以在从节点中...
  • MySQL复制表数据到新表的方法

    千次阅读 2014-10-16 15:28:31
    MySQL复制表数据操作相信大家都不会太陌生,下面就为您详细介绍MySQL复制表数据到新表的步骤,希望对您会有所启迪。 1.MySQL复制表结构及数据到新表 CREATE TABLE 新表 SELECT * FROM 旧表 2.只复制...
  • mysql 复制表结构或创建新表

    千次阅读 2016-11-18 10:02:50
    mysql 复制表结构或创建新表  1、复制表结构及数据到新表 CREATE TABLE 新表SELECT * FROM 旧表 这种方法会将oldtable中所有的内容都拷贝过来,当然我们可以用delete from newtable;来删除。 不过这种方法的一...
  • MySQL复制之gtid_purged与gtid_executed

    千次阅读 2016-03-31 11:09:38
    MySQL复制之gtid_purged与gtid_executed
  • mysql复制整个库

    万次阅读 2018-03-12 14:11:27
    新建一个iot_telecommysql&gt; create database iot_telecom; Query OK, 1 row affected (0.08 sec)把iot的所有东西,复制到iot_telecom中,注意...mysqldump iot -uroot -pass --add-drop-table | mysql iot_t...
  • mysql复制表语句

    千次阅读 2018-12-12 23:48:40
    1. 仅复制表结构 create table my_table_copy2 like my_table; 2. 复制表结构和数据 create table my_table_copy1 select * from my_table; 注意:该语句只是按select语句执行的结果新建表,并不会复制表的主键、...
  • MySQL复制表结构

    千次阅读 2019-03-06 19:44:10
    1.同库中复制表: 1)方法1:SELECT条件查询 CREATE TABLE CONTACTS_BK AS SELECT * FROM CONTACTS WHERE 1=2; 2)方法2:LIKE关键字 CREATE TABLE CONTACTS_BK LIKE CONTACTS; 2.复制表到另一个数据库 表名...
  • 若原有日志表:trxn_detail_log_201806 , 现在像创建以后每个月的表,可以使用以下语句复制表数据。 CREATE TABLE 表名 LIKE SELECT * FROM 模板表名; CREATE TABLE trxn_detail_log_201807 LIKE SELECT * ...
  • 以下关于mysql复制关系,描述错误的一项是? 正确答案: A 你的答案: D (错误) mysql支持master-slave复制,也支持master-master复制 一般情况下,异步复制的性能比半同步复制好,但后者相对更为...
  • MySQL 复制表的操作

    千次阅读 2018-10-13 22:56:26
    复制表格有两种方法: 方法一:在create table语句的末尾添加like子句,可以将源表的表...mysql&gt; desc FirTable; //原表的结构 +-------+-------------+------+-----+---------+-------+ | Field | Type | ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 75,422
精华内容 30,168
关键字:

mysql复制

mysql 订阅