精华内容
下载资源
问答
  • mysql主从同步
    2021-01-18 22:57:46

    84fe720d0c55497f3d7880d600c4d4a9.png

    启动mysql服务 登录相关权限的用户  进行操作

    以下整理来源于:https://blog.csdn.net/mycwq/article/details/17136001

    MYSQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力。

    MySQL主从同步的机制

    MYSQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的,通过设置在Master MySQL上的binlog(使其处于打开状态),Slave MySQL上通过一个I/O线程从Master MySQL上读取binlog,然后传输到Slave MySQL的中继日志中,然后Slave MySQL的SQL线程从中继日志中读取中继日志,然后应用到Slave MySQL的数据库中。这样实现了主从数据同步功能。

    69ab7f7db90170edbf28fef625e5b687.png

    74d1ada0db1780c2035cc38f5a61d3f3.png

    MySQL主从同步的作用

    1、可以作为一种备份机制,相当于热备份

    2、可以用来做读写分离,均衡数据库负载

    MySQL主从同步的步骤

    一、准备操作

    1、主从数据库版本一致,建议版本5.5以上

    2、主从数据库数据一致

    二、主数据库master修改

    1、修改MySQL配置:

    # 日志文件名

    log-bin = mysql-bin

    # 主数据库端ID号

    server-id = 1

    2、重启mysql,创建用于同步的账户:

    # 创建slave帐号slave_account,密码123456

    mysql>grant replication slave on *.* to 'slave_account'@'%' identified by '123456';

    # 更新数据库权限

    mysql>flush privileges;

    3、查询master的状态

    mysql> show master status;

    +------------------+----------+--------------+------------------+

    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

    +------------------+----------+--------------+------------------+

    | mysql-bin.000009 |      196 |              |                  |

    +------------------+----------+--------------+------------------+

    1 row in set

    注:执行完这个步骤后不要再操作主数据库了,防止主数据库状态值变化

    三、从数据库slave修改

    1、修改MySQL配置:

    # 从数据库端ID号

    server-id =2

    2、执行同步命令

    # 执行同步命令,设置主数据库ip,同步帐号密码,同步位置

    mysql>change master to master_host='192.168.1.2',master_user='slave_account',master_password='123456',master_log_file='mysql-bin.000009',master_log_pos=196;

    # 开启同步功能

    mysql>start slave;

    3、检查从数据库状态:

    mysql> show slave status\G;

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

    Slave_IO_State: Waiting for master to send event

    Master_Host: 192.168.1.2

    Master_User: slave_account

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: mysql-bin.000009

    Read_Master_Log_Pos: 196

    Relay_Log_File: vicky-relay-bin.000002

    Relay_Log_Pos: 253

    Relay_Master_Log_File: mysql-bin.000009

    Slave_IO_Running: Yes

    Slave_SQL_Running: Yes

    Replicate_Do_DB:

    Replicate_Ignore_DB:

    ...

    注:Slave_IO_Running及Slave_SQL_Running进程必须正常运行,即YES状态,否则说明同步失败。

    到这里,主从数据库设置工作已经完成,自己可以新建数据库和表,插入和修改数据,测试一下是否成功

    四、其他可能用到的相关参数

    1、master端:

    # 不同步哪些数据库

    binlog-ignore-db = mysql

    binlog-ignore-db = test

    binlog-ignore-db = information_schema

    # 只同步哪些数据库,除此之外,其他不同步

    binlog-do-db = game

    # 日志保留时间

    expire_logs_days = 10

    # 控制binlog的写入频率。每执行多少次事务写入一次

    # 这个参数性能消耗很大,但可减小MySQL崩溃造成的损失

    sync_binlog = 5

    # 日志格式,建议mixed

    # statement 保存SQL语句

    # row 保存影响记录数据

    # mixed 前面两种的结合

    binlog_format = mixed

    2、slave端:

    # 停止主从同步

    mysql> stop slave;

    # 连接断开时,重新连接超时时间

    mysql> change master to master_connect_retry=50;

    # 开启主从同步

    mysql> start slave;

    以上连接超时设置,类似方式可用于设置主数据库ip,同步帐号密码,同步位置

    d99995690225820ebe6557f67cf4267d.png

    33f27e4cc02bdf1234f1882154a6f9d0.png

    更多相关内容
  • 今天小编就为大家分享一篇关于MySQL主从同步延迟的原因及解决办法,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
  • MySQL主从同步

    2020-12-14 15:31:49
    主从同步一、主从同步概述1.1、MySQL主从同步1.2、主从同步工作原理1.2.1、必要条件1.2.2、具体过程1.3、结构类型二、构建一主一从2.1、拓扑结构2.2、配置主服务器2.2.1、启用binlog日志2.2.2、授权用户2.2.3、查看...
  • 1、什么是mysql主从同步? 当master(主)库的数据发生变化的时候,变化会实时的同步到slave(从)库。 2、主从同步有什么好处? 水平扩展数据库的负载能力。 容错,高可用。Failover(失败切换)/High Availability ...
  • 作为一名DBA,在工作中会经常遇到一些MySQL主从同步延迟的问题,这些同步慢的问题,其实原因非常多,可能是因为主从的网络问题导致,可能是因为网络带宽问题导致,可能是因为大事务导致,也可能是因为单线程复制导致...
  • 主要给大家介绍了关于Centos7系统下Mysql主从同步配置的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • mysql主从同步及canal配置详解.docx
  • 主要介绍了Shell脚本实现监控MySQL主从同步,此脚本应该能适应各种各样不同的内外网环境,并同时监控MySQL运行状态,需要的朋友可以参考下
  • C#开发的MySql主从监控程序,监控同步情况以及连接数。 可以监控多级塔式分发模式。 同步故障支持发送邮件。 VS2010开发,代码完整,可直接编译。
  • mysql主从同步搭建修改优化版.pdf
  • 详细描述MySQL主从同步配置过程
  • mysql主从同步搭建

    千次阅读 2022-02-03 10:55:57
    环境说明 win10(mysql版本为mysql-5.5.48)做主库,在本机安装了一台虚拟机,里面装了linux(centos7)系统,mysql版本为mysql-5.5.48作为从库。 下面分为三个部分:1、介绍mysql在Linux的... 3、主从同步需要的...

    环境说明

            win10(mysql版本为mysql-5.5.48)做主库,在本机安装了一台虚拟机,里面装了linux(centos7)系统,mysql版本为mysql-5.5.48作为从库。

            下面分为三个部分:1、介绍mysql在Linux的安装和配置
                                            2、介绍mysql在window的安装和配置
                                            3、主从同步需要的操作和配置。(如果本地自己装好了,可以直接看这里就行)

    Linux安装中mysql

    1.  下载两个rpm文件,MySQL-client-5.5.48-1.linux2.6.x86_64.rpm和MySQL-server-5.5.48-1.linux2.6.x86_64.rpm。
    2. 放到Linux的/root/Downloads目录下
    3. //首先安装server,输入下面的指令
      rpm -ivh MySQL-server-5.5.48-1.linux2.6.x86_64.rpm
      如果提示“GPD keys...”安装失败,解决方案:rpm -ivh MySQL-server-5.5.48-1.linux2.6.x86_64.rpm --force --nodoeps(原因是有可能rpm安装的时候安装到旧版的GPD keys)

      如果出现了冲突

      先卸载原来的mariadb-libs-1:5.5.68-1.el7.x86_64,
      //删除原来的mariadb-libs-1:5.5.68-1.el7开头的库
      yum -y remove mariadb-libs-1:5.5.68-1.el7*
      卸载完后在重新安装一次

      这次就正常了。
    4. 服务端安装完成后,安客户端
    5. 查看MySQL版本
      mysqladmin --version
      开启MySQL服务
      service mysql start
      关闭mysql服务
      service mysql stop
      重启MySQL服务
      service mysql restart
      


      启动服务:1.设置开机自启 chkconfig mysql on(打开), chkconfig mysql off(关闭)
      检查开机是否自动启动:ntsysv
    6. 安装完成后,直接输入mysql就可以登录了,我们现在要给mysql设置下密码,需要用到安装完成后提示的那两个指令
      给mysql设置登录密码,用户名root,密码root
      /usr/bin/mysqladmin -u root password root

     MySQL的一些信息 

    数据库目录:--datadir=/var/lib/mysql。
    pid文件目录:--pid-file=/var/lib/mysql/lxx02.pid。

    MySQL核心目录:
                    
    /var/lib/mysql:MySQL安装目录。
                    /usr/share/mysql:mysql配置文件目录。
                            my-huge.cnf:高端服务器配置。
                            my-large.cnf:中等服务器配置
                            my-medium.cnf:一般
                            my-small.cnf:较小
                            以上配置文件mysql默认不能识别,默认只识别/etc/my.cnf。
                            我们把my-huge.cnf复制一份到/etc/文件夹下,并改名为my.cnf。
                    /usr/bin:命令目录(mysqladmin,mysqldump等命令)

    mysql配置:

    查看mysql的字符编码
    show variables like '%char%';
    
    在/etc/my.cnf文件中[mysqld]标签下添加下面这些配置
    character_set_server=utf8
    character_set_client=utf8
    collation_server=utf8_general_ci #排序方式
    server-id=2    #设置从节点的id,不能跟别的mysql一样,主从同步要用
    log-bin=mysql-bin    #开启binlong
    
    #在mysql客户端执行
    #授权远程返回,这个命令有可能授权不了,如果错了在百度下别的命令,之前在安装遇到过这个问题
    grant all privileges on *.* to 'root'@'192.168.1.%' identified by 'root' with grant option ;
    #授权后刷新让立刻生效。
    flush privileges;
    
    上面改完重启mysql
    service mysql restart
    
    关闭Linux防火墙
    service iptables stop;

    重启后就可以在navicat上连接登录了

     

     window系统mysql下载与安装

    下载地址:MySQL :: Download MySQL Community Server (Archived Versions)

     

     

    选择自定义安装

     

    选择自己想要的安装路径

     

     

    直接启动MySQL配置,没有这个选项的话可以在mysql安装的bin目录下打开MySQLInstanceConfig.exe,下面配置的东西,一般都会放到:C:\ProgramData\MySQL\MySQL Server 5.5\my.ini这个文件中

     

     

     

     

     

     

     

     

     

     

     

     

     

     主从同步

    先看下mysql主从同步原理

            master将改变的数据记录到binary log中,然后会有专门的线程同步给slave,slave的io线程会把同步过来的数据记录到relay log日志中,relay log在从数据中做指令回放,从而达到主从一致。
           

    异步复制与半同步复制

    异步复制:MySQL主从集群默认采用的是一种异步复制的机制。主服务在执行用户提交的事 务后,写入binlog日志,然后就给客户端返回一个成功的响应了。

     半同步复制:半同步复制机制是一种介于异步复制和全同步复制之前的机制。主库在执行完客 户端提交的事务后,并不是立即返回客户端响应,而是等待至少一个从库接收并写 到relay log中,才会返回给客户端。MySQL在等待确认时,默认会等10秒,如果超 过10秒没有收到ack,就会降级成为异步复制。

     

     MySQL binlog有三种模式:Row、Statement 和 Mixed 。

    主库的配置

            windows:(mysql:my.ini)文件
            linux:(mysql:my.cnf)文件

    配置前记得检查权限,比如远程访问权限,和防火墙处理好。

     my.ini文件配置

    #在[mysqld]节点下添加
    #master的id
    server-id=1
    #binary log,开启binlong文件,并指定文件路径
    log-bin="C:/Program Files/MySQL/data/mysql-bin"
    #异常信息的日志
    log-error="C:/Program Files/MySQL/data/mysql-error"
    
    #Path to installation directory. All paths are usually resolved relative to this.
    #MySQL根路径
    basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
    #指定需要同步哪些数据库(默认全部数据库)
    #binlog-do-bo=mydb01
    
    #指定需要 不同步哪些数据库(排除)
    #binlog-ignore-db=mysql

     授权从数据库

    #授权IP为192.168.1开头的账号为root,密码为root作为我的从计算机
    GRANT REPLICATION slave,reload,super ON *.* to 'root'@' 192.168.1.%' identified by 'root';
    #刷新使其立刻生效
    flush privileges;

     查看主数据库的状态
            file:同步的二进制文件
            position:二进制文件记录的位置
            binlog_ignore_db:忽略的数据库
            binlog_do_db:需要同步的数据库

     

     从库的配置

            my.cnf文件配置

    #从库的id,需要在集群中唯一,代表身份
    server-id=2
    #开启binlong文件
    log-bin=mysql-bin
    #需要同步的数据库
    replicate-do-db=mydb01

    在从库中指定主库

    
    #在mysql客户端执行,查看server_id的值。
    show variables like '%server_id%';
    
    #配置server_id的值(有时候配置文件设置的server_id会不生效需要手动配置)
    set global server_id=3;

    下面的操作是为了确保server_id有被修改到,下面所有的start slave和stop slave都不要执行,这是我在实验的时候多操作了。

    change master to 
    #主库的ip
    master_host='192.168.1.5',
    #主库的用户名
    master_user='root',
    #主库的密码
    master_password='root',
    master_port=3306,
    #主库的binlog日志
    master_log_file='mysql-bin.000001',
    #主库的binlog日志的记录位置
    master_log_pos=13061;
    
    #执行完上面这些在开启slave服务
    #开启从服务
    start slave;
    
    #查看主从同步信息
    show slave status  \G

     

     

     

     

     

    展开全文
  • mysql主从同步架构设计方案,可以配置应用最广泛的一主拖多从,也可以多主多从,方案中有监控方案,7 * 24 小时自动监控。
  • MySQL主从同步原理

    千次阅读 2022-02-14 22:37:25
    主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库;主数据库一般是准实时的业务数据库。 原理: 数据库有个bin-log二进制文件,记录了所有sql语句。 我们的目标就是把主数据库的bin-log...

    主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库;主数据库一般是准实时的业务数据库。

    原理:

    1. 数据库有个bin-log二进制文件,记录了所有sql语句。

    2. 我们的目标就是把主数据库的bin-log文件的sql语句复制过来。

    3. 让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。

    4. 具体需要三个线程来操作:
      4.1 binlog输出线程:每当有从库连接到主库的时候,主库都会创建一个线程然后发送binlog内容到从库。在从库里,当复制开始的时候,从库就会创建两个线程进行处理:
      4.2 从库I/O线程:当START SLAVE语句在从库开始执行之后,从库创建一个I/O线程,该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件,其中包括relay log文件。
      4.3 从库的SQL线程:从库创建一个SQL线程,这个线程读取从库I/O线程写到relay log的更新事件并执行。
      可以知道,对于每一个主从复制的连接,都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程,每一个从库都有它自己的I/O线程和SQL线程。

    用途:

    1、实施灾备,故障切换

    2、读写分离

    3、备份、避免数据丢失

    条件:

    1、主库开启binlog日志

    2、主从服务器server-id不同

    3、从库服务器能连通主库

    方式:

    1、同步复制

    同步复制,意思是master的变化,必须等待slave-1,slave-2,…,slave-n完成后才能返回。

    这样,显然不可取,也不是MYSQL复制的默认设置。比如,在WEB前端页面上,用户增加了条记录,需要等待很长时间。

    2、异步复制

    如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志,是否完成操作,不用关心。MYSQL的默认设置。

    3、半同步复制

    master只保证slaves中的一个操作成功,就返回,其他slave不管。

    这个功能,是由google为MYSQL引入的。

    在这里插入图片描述

    在这里插入图片描述

    原理步骤:
    步骤一:主库db的更新事件(update、insert、delete)被写到binlog

    步骤二:从库发起连接,连接到主库

    步骤三:此时主库创建一个binlog dump thread线程,把binlog的内容发送到从库

    步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log.

    步骤五:还会创建一个SQL线程,从relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db.

    关于MySQL主从同步的一些问题

    • master的写操作,slaves被动的进行一样的操作,保持数据一致性,那么slave是否可以主动的进行写操作?

    假设slave可以主动的进行写操作,slave又无法通知master,这样就导致了master和slave数据不一致了。因此slave不应该进行写操作,至少是slave上涉及到复制的数据库不可以写。实际上,这里已经揭示了读写分离的概念。

    • 主从复制中,可以有N个slave,可是这些slave又不能进行写操作,要他们干嘛?

    可以实现数据备份。

    类似于高可用的功能,一旦master挂了,可以让slave顶上去,同时slave提升为master。

    异地容灾,比如master在北京,地震挂了,那么在上海的slave还可以继续。

    主要用于实现scale out,分担负载,可以将读的任务分散到slaves上

    • 主从复制中有master,slave1,slave2,…等等这么多MYSQL数据库,那比如一个JAVA WEB应用到底应该连接哪个数据库?

    我们在应用程序中可以这样,insert/delete/update这些更新数据库的操作,用connection(for master)进行操作,select用connection(for slaves)进行操作。那我们的应用程序还要完成怎么从slaves选择一个来执行select,例如简单的轮循算法。

    这样的话,相当于应用程序完成了SQL语句的路由,而且与MYSQL的主从复制架构非常关联,一旦master挂了,某些slave挂了,那么应用程序就要修改了

    • 能不能让应用程序与MYSQL的主从复制架构没有什么太多关系呢?

    找一个组件,application program只需要与它打交道,用它来完成MYSQL的代理,实现SQL语句的路由。

    mysql proxy并不负责,怎么从众多的slaves挑一个?可以交给另一个组件(比如haproxy)来完成。

    这就是所谓的MYSQL READ WRITE SPLITE,MYSQL的读写分离。

    • 如果mysql proxy , direct , master他们中的某些挂了怎么办?

    总统一般都会弄个副总统,以防不测。同样的,可以给这些关键的节点来个备份。

    • 当master的二进制日志每产生一个事件,都需要发往slave,如果我们有N个slave,那是发N次,还是只发一次?

    如果只发一次,发给了slave-1,那slave-2,slave-3,…它们怎么办?

    显 然,应该发N次。实际上,在MYSQL master内部,维护N个线程,每一个线程负责将二进制日志文件发往对应的slave。master既要负责写操作,还的维护N个线程,负担会很重。可 以这样,slave-1是master的从,slave-1又是slave-2,slave-3,…的主,同时slave-1不再负责select。 slave-1将master的复制线程的负担,转移到自己的身上。这就是所谓的多级复制的概念。

    • 当一个select发往mysql proxy,可能这次由slave-2响应,下次由slave-3响应,这样的话,就无法利用查询缓存了。

    应该找一个共享式的缓存,比如memcache来解决。将slave-2,slave-3,…这些查询的结果都缓存至mamcache中。

    • 随着应用的日益增长,读操作很多,我们可以扩展slave,但是如果master满足不了写操作了,怎么办呢?

    scale on ?更好的服务器? 没有最好的,只有更好的,太贵了。。。

    scale out ? 主从复制架构已经满足不了。

    • 从数据库的读的延迟问题了解吗?如何解决?

    原因:主库TPS并发高,DDL数量超过slave一个sql线程承受的范围,还有可能与大型的查询造成了所等待,还有网络延迟。(谈到MySQL数据库主从同步延迟原理,得从mysql的数据库主从复制原理说起,mysql的主从复制都是单线程的操作,主库对所有DDL和DML产生binlog,binlog是顺序写,所以效率很高;slave的Slave_IO_Running线程会到主库取日志,效率会比较高,slave的Slave_SQL_Running线程将主库的DDL和DML操作都在slave实施。DML和DDL的IO操作是随机的,不是顺序的,因此成本会很高,还可能是slave上的其他查询产生lock争用,由于Slave_SQL_Running也是单线程的,所以一个DDL卡主了,需要执行10分钟,那么所有之后的DDL会等待这个DDL执行完才会继续执行,这就导致了延时。有朋友会问:“主库上那个相同的DDL也需要执行10分,为什么slave会延时?”,答案是master可以并发,Slave_SQL_Running线程却不可以。)

    **解决方法一:**最简单的减少slave同步延时的方案就是在架构上做优化,尽量让主库的DDL快速执行。还有就是主库是写,对数据安全性较高,比如sync_binlog=1,innodb_flush_log_at_trx_commit = 1 之类的设置,而slave则不需要这么高的数据安全,完全可以讲sync_binlog设置为0或者关闭binlog,innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。

    解决方法二:数据放入缓存中,更新数据库后,在预期可能马上用到的情况下,主动刷新缓存。

    解决办法三:对于比较重要且必须实时的数据,比如用户刚换密码(密码写入 Master),然后用新密码登录(从 Slaves 读取密码),会造成密码不一致,导致用户短时间内登录出错。所以在这种需要读取实时数据的时候最好从 Master 直接读取,避免 Slaves 数据滞后现象发生。

    • 做主从后主服务器挂了怎么办?

    假设发生了突发事件,master宕机,现在的需求是要将192.168.1.102提升为主库,另外一个为从库

    步骤:

    1.确保所有的relay log全部更新完毕,在每个从库上执行stop slave io_thread; show processlist;直到看到Has read all relay log,则表示从库更新都执行完毕了

    2.登陆所有从库,查看master.info文件,对比选择pos最大的作为新的主库,这里我们选择192.168.1.102为新的主库

    3.登陆192.168.1.102,执行stop slave; 并进入数据库目录,删除master.info和relay-log.info文件, 配置my.cnf文件,开启log-bin,如果有

    log-slaves-updates和read-only则要注释掉,执行reset master

    4.创建用于同步的用户并授权slave,同第五大步骤

    5.登录另外一台从库,执行stop slave停止同步

    6.根据第七大步骤连接到新的主库

    7.执行start slave;

    8.修改新的master数据,测试slave是否同步更新

    • 读写分离实现方法:

    为了减轻数据库的压力,一般会进行数据库的读写分离,实现方法一是通过分析sql语句是insert/select/update/delete中的哪一种,从而对应选择主从,二是通过拦截方法名称的方式来决定主从的,如:save*()、insert*() 形式的方法使用master库,select()开头的使用slave库。

    虽然大多数都是从程序里直接实现读写分离的,但对于分布式的部署和水平和垂直分割,一些代理的类似中间件的软件还是挺实用的,如 MySQL Proxy比较。mysql proxy根本没有配置文件, lua脚本就是它的全部,当然lua是相当方便的。

    相关配置:

    innodb_flush_log_at_trx_commit 和 sync_binlog 是 MySQL 的两个配置参数。它们的配置对于 MySQL 的性能有很大影响(一般为了保证数据的不丢失,会设置为双1,该情形下数据库的性能也是最低的)。

    • 1、innodb_flush_log_at_trx_commit

    innodb_flush_log_at_trx_commit:是 InnoDB 引擎特有的,ib_logfile的刷新方式( ib_logfile:记录的是redo log和undo log的信息)

    取值:0/1/2

    innodb_flush_log_at_trx_commit=0,表示每隔一秒把log buffer刷到文件系统中(os buffer)去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区,也就是内存上,如果机器宕掉,可能丢失1秒的事务数据。

    innodb_flush_log_at_trx_commit=1,表示在每次事务提交的时候,都把log buffer刷到文件系统中(os buffer)去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话,数据库对IO的要求就非常高了,如果底层的硬件提供的IOPS比较差,那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。

    innodb_flush_log_at_trx_commit=2,表示在每次事务提交的时候会把log buffer刷到文件系统中去,但并不会立即刷写到磁盘。如果只是MySQL数据库挂掉了,由于文件系统没有问题,那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下,数据库的事务数据可能丢失1秒之类的事务数据。这样的好处,减少了事务数据丢失的概率,而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中,一般只是从log buffer的内存转移的文件系统的内存缓存中,对底层IO没有压力)。

    • 2、sync_binlog

    sync_binlog:是MySQL 的二进制日志(binary log)同步到磁盘的频率。

    取值:0-N

    sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。这个是性能最好的。

    sync_binlog=1,当每进行1次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

    sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

    注:

    大多数情况下,对数据的一致性并没有很严格的要求,所以并不会把 sync_binlog 配置成 1. 为了追求高并发,提升性能,可以设置为 100 或直接用 0.

    而和 innodb_flush_log_at_trx_commit 一样,对于支付服务这样的应用,还是比较推荐 sync_binlog = 1.

    在这里插入图片描述
    JVM内存泄漏和内存溢出的原因
    JVM常用监控工具解释以及使用
    Redis 常见面试题(一)
    ClickHouse之MaterializeMySQL引擎(十)
    三种实现分布式锁的实现与区别
    线程池的理解以及使用

    号外!号外!

    最近面试BAT,整理一份面试资料,覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。想获取吗?如果你想提升自己,并且想和优秀的人一起进步,感兴趣的朋友,可以在扫码关注下方公众号。资料在公众号里静静的躺着呢。。。

    • 喜欢就收藏
    • 认同就点赞
    • 支持就关注
    • 疑问就评论

    一键四连,你的offer也四连

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

    本文作者:Java技术债务
    原文链接:https://www.cuizb.top/myblog/article/1644849971
    版权声明: 本博客所有文章除特别声明外,均采用 CC BY 3.0 CN协议进行许可。转载请署名作者且注明文章出处。

    展开全文
  • 一、主机规划 服务器 IP zabbix-server ...mysql-master、zabbix-agent ...mysql-slave、zabbix-agent ...二、MySQL数据库主从搭建...2、删除系统自带mysql配置文件(主从库均需删除) [root@master ~]# yum remove -y ma
  • mysql主从同步检查脚本 。主要运用于日常maysql运行主从同步状态检查。
  • Mysql主从同步配置

    万次阅读 2021-11-23 18:43:39
    mysql主从同步定义 主从同步使得数据可以从一个数据库服务器复制到其他服务器上,在复制数据时,一个服务器充当主服务器(master),其余的服务器充当从服务器(slave)。因为复制是异步进行的,所以从服务器不需要...

    1. mysql主从同步定义

    主从同步使得数据可以从一个数据库服务器复制到其他服务器上,在复制数据时,一个服务器充当主服务器(master),其余的服务器充当从服务器(slave)。因为复制是异步进行的,所以从服务器不需要一直连接着主服务器,从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件,可以指定复制所有的数据库,某个数据库,甚至是某个数据库上的某个表。

    使用主从同步的好处:

    • 1.通过增加从服务器来提高数据库的性能,在主服务器上执行写入和更新,在从服务器上向外提供读功能,可以动态地调整从服务器的数量,从而调整整个数据库的性能。
    • 2.提高数据安全-因为数据已复制到从服务器,从服务器可以终止复制进程,所以,可以在从服务器上备份而不破坏主服务器相应数据
    • 3.在主服务器上生成实时数据,而在从服务器上分析这些数据,从而提高主服务器的性能

    1.2 什么是 Binlog

    MySQL 的二进制日志可以说 MySQL 最重要的日志了,它记录了所有的 DDL 和 DML(除 了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL 的二进制日志是事务安全型的。

    一般来说开启二进制日志大概会有 1%的性能损耗。二进制有两个最重要的使用场景:

    • 其一:MySQL Replication 在 Master 端开启 Binlog,Master 把它的二进制日志传递给 Slaves 来达到 Master-Slave 数据一致的目的。

    • 其二:自然就是数据恢复了,通过使用 MySQL Binlog 工具来使恢复数据。

    二进制日志包括两类文件:二进制日志索引文件(文件名后缀为.index)用于记录所有 的二进制文件,二进制日志文件(文件名后缀为.00000*)记录数据库所有的 DDL 和 DML(除 了数据查询语句)语句事件。

    1.2 Binlog 的分类

    MySQL Binlog 的格式有三种,分别是 STATEMENT,MIXED,ROW。在配置文件中可以选择配 置 binlog_format= statement|mixed|row。三种格式的区别:

    1)statement:语句级,binlog 会记录每次一执行写操作的语句。相对 row 模式节省空间,但是可能产生不一致性,比如“update tt set create_date=now()”,如果用 binlog 日志 进行恢复,由于执行时间不同可能产生的数据就不同。

    • 优点:节省空间。
    • 缺点:有可能造成数据不一致。

    2)row:行级, binlog 会记录每次操作后每行记录的变化。

    • 优点:保持数据的绝对一致性。因为不管 sql 是什么,引用了什么函数,他只记录 执行后的效果。

    • 缺点:占用较大空间。

    3)mixed:statement 的升级版,一定程度上解决了,因为一些情况而造成的 statement 模式不一致问题,默认还是 statement,在某些情况下譬如:当函数中包含 UUID() 时;包含 AUTO_INCREMENT 字段的表被更新时;执行 INSERT DELAYED 语句时;用 UDF 时;会按照 ROW 的方式进行处理

    • 优点:节省空间,同时兼顾了一定的一致性。

    • 缺点:还有些极个别情况依旧会造成不一致,另外 statement 和 mixed 对于需要对 binlog 的监控的情况都不方便。

    1.3 主从同步机制

    img

    • 1、slave 服务器执行 start slave,开启主从复制开关, slave 服务器的 IO 线程请求从 master 服务器读取 binlog(如果该线程追赶上了主库,会进入睡眠状态)。
    • 2、master 服务器的更新SQL(update、insert、delete)被写到binlog, 主库的 binlog dump thread,把 bin log 的内容发送到从库。
    • 3、从库启动之后,创建一个 I/O 线程,读取主库传过来的 bin log 内容并写到 relay log(会记录位置信息,以便下次继续读取)。
    • 3、slave 服务器的 SQL 线程会实时检测 relay log 中新增的日志内容,把 relay log解析成 SQL 语句,并执行。

    2. 配置主从同步

    有很多种配置主从同步的方法,可以总结为如下的步骤:

    1.在主服务器上,必须开启二进制日志机制和配置一个独立的ID

    2.在每一个从服务器上,配置一个唯一的ID,创建一个用来专门复制主服务器数据的账号

    3.在开始复制进程前,在主服务器上记录二进制文件的位置信息

    4.如果在开始复制之前,数据库中已经有数据,就必须先创建一个数据快照(可以使用mysqldump导出数据库,或者直接复制数据文件)

    5.配置从服务器要连接的主服务器的IP地址和登陆授权,二进制日志文件名和位置

    2.1 配置主服务器

    • 1.更改配置文件,首先检查你的主服务器上的my.cnf文件中是否已经在[mysqld]模块下配置了log-bin和server-id
    [mysqld]
    log-bin=mysql-bin
    server-id=1
    

    注意上面的log-bin和server-id的值都是可以改为其他值的,如果没有上面的配置,首先关闭mysql服务器,然后添加上去,接着重启服务器

    • 2.创建用户,每一个从服务器都需要用到一个账户名和密码来连接主服务器,可以为每一个从服务器都创建一个账户,也可以让全部服务器使用同一个账户。下面就为同一个ip网段的所有从服务器创建一个只能进行主从同步的账户。

    首先登陆mysql,然后创建一个用户名为rep,密码为123456的账户,该账户可以被192.168.253网段下的所有ip地址使用,且该账户只能进行主从同步

    mysql > grant replication slave on *.* to ‘rep’@‘192.168.253.%’ identified by ‘123456’;
    mysql> FLUSH PRIVILEGES;
    

    3.获取二进制日志的信息并导出数据库,步骤:

    首先登陆数据库,然后刷新所有的表,同时给数据库加上一把锁,阻止对数据库进行任何的写操作

    mysql > flush tables with read lock;
    

    然后执行下面的语句获取二进制日志的信息

    mysql > show master status;
    

    图片1
    File的值是当前使用的二进制日志的文件名,Position是该日志里面的位置信息(不需要纠结这个究竟代表什么),记住这两个值,会在下面配置从服务器时用到。

    注意:如果之前的服务器并没有配置使用二进制日志,那么使用上面的sql语句会显示空,在锁表之后,再导出数据库里的数据(如果数据库里没有数据,可以忽略这一步)

    [root@localhost backup]# mysqldump -uroot -p'123456' -S /data/3306/data/mysql.sock --all-databases > /server/backup/mysql_bak.$(date +%F).sql
    

    如果数据量很大,可以在导出时就压缩为原来的大概三分之一

    [root@localhost backup]# mysqldump -uroot -p'123456' -S /data/3306/data/mysql.sock --all-databases | gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
    

    这时可以对数据库解锁,恢复对主数据库的操作

    mysql > unlock tables;
    

    2.2 配置从服务器

    首先检查从服务器上的my.cnf文件中是否已经在[mysqld]模块下配置leserver-id

    [mysqld]
    
    server-id=2
    ## 忽略表
    replicate-wild-ignore-table=mysql.*
    replicate-wild-ignore-table=sys.*
    

    注意上面的server-id的值都是可以改为其他值的(建议更改为ip地址的最后一个字段),如果没有上面的配置,首先关闭mysql服务器,然后添加上去,接着重启服务器

    如果有多个从服务器上,那么每个服务器上配置的server-id都必须不一致。从服务器上没必要配置log-bin,当然也可以配置log-bin选项,因为可以在从服务器上进行数据备份和灾难恢复,或者某一天让这个从服务器变成一个主服务器

    如果主服务器导出了数据,下面就导入该文件,如果主服务器没有数据,就忽略这一步

    [root@localhost ~]# mysql -uroot -p'123456' -S /data/3306/data/mysql.sock < /server/backup/mysql_bak.2015-07-01.sql
    

    如果从主服务器上拿过来的是压缩文件,就先解压再导入

    配置同步参数,登陆mysql,输入如下信息:

    CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='replication_user_name', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS='POS',
    

    如图所示:
    图片2
    启动主从同步进程

    mysql > start slave;
    

    检查状态

    mysql > show slave status \G
    

    图片3
    上面的两个进程都显示YES则表示配置成功

    3 使用主从同步来备份

    把主服务器的数据复制到从服务器上,然后备份从服务器的数据,在数据量不是很大的时候使用mysqldump命令,对于很大的数据库,就直接备份数据文件。

    3.1 使用mysqldump来备份

    步骤:(以下的所有操作都在从服务器上进行)

    1.首先暂停从服务器的复制进程

    shell > mysqladmin stop-slave
    

    或者只是暂停SQL进程(从服务器仍然能接收二进制日志的事件,但不会执行这些事件,这样能在重启SQL进程时加快复制进度)

    shell > mysql -e ‘stop slave sql_thread;’
    

    2.使用mysqldump导出全部或部分的数据库

    shell > mysqldump --all-databases > fulldb.dump
    

    3.在导出数据库后,重启复制进程

    shell > mysqladmin start-slave
    

    3.2 备份原始文件

    为了保证数据文件的完整性,在备份之前首先关闭从服务器,步骤:

    1.关闭从服务器:

    shell > mysqladmin shutdown
    

    2.复制数据文件,可以使用压缩命令,假如当前目录就是数据库的数据目录(在my.cnf文件中的配置项datadir的值就是该目录的位置)

    shell > tar cf /tmp/dbbackup.tar ./data
    

    3.然后再启动mysql服务器

    4 主从同步的小技巧

    主服务器第一次导入数据,如果你从其他地方拿来了要导入到主服务器中的数据,此时只要在主服务器中导入一次即可,因为这些数据会自动发送到从服务器中,在主服务器上使用命令

    shell > mysql -h master < other_data.sql
    

    增加从服务器,本来已经至少有一个从服务器时(暂时命名为slave1),决定再添加其余的从服务器(slave2),此时就不需要像上面那样去操作主服务器,只要复制一个已经存在的从服务器就可以了

    5.排错

    Slave_IO_Running: NO

    这是一个很常见的错误(我也曾对这个错误咬牙切齿),总结起来就三个原因:

    1. 主服务器的网络不通,或者主服务器的防火墙拒绝了外部连接3306端口
    2. 在配置从服务器时,输错了ip地址和密码,或者主服务器在创建用户时写错了用户名和密码
    3. 在配置从服务器时,输错了主服务器的二进制日志信息

    排错过程:(主服务器ip:192.168.1.139,从服务器ip:192.168.1.204)

    第0步就是检查错误日志,如果不能快速排错,可以按我的步骤试试:

    1.首先在从服务器上执行ping程序,确定能ping通主服务器

    在从服务器上执行mysq的远程连接

    [root@slave204 log]# mysql -urep -p -h 192.168.1.139 -P3306
    

    如果显示ERROR 1045 (28000): Access denied for user ‘test’@‘192.168.1.204’ (using password: YES)则跳转到第3

    2.登陆主服务器的mysql,查看所有的用户

    mysql > select user,host from mysql.user;
    

    图片4
    上图就是我的错误根源,可以看到用户名完全写错了,先删除错误的用户:

    mysql > drop user “rep@192.168.1.%”@”%”;
    

    再重新创建用户:

    mysql > grant replication slave on *.* to ‘rep’@‘192.168.1.%’ identified by ‘123456’;
    
    mysql > flush privileges;
    

    3.假如用户名没有错,那么如何排除是否是输入的密码错误呢?

    额,我也想知道方法。最好就是多输入几遍,或者重新创建用户名和密码来测试。问题还没有解决,转到4

    4.在你的防火墙中添加3306端口

    [root@localhost mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
    
    [root@localhost mysql]# firewall-cmd --reload
    

    再关闭selinux

    [root@slave204 log]# vi /etc/sysconfig/selinux
    

    把SELINUX=enforcing改为SELINUX=disabled

    [root@slave204 log]# source /etc/sysconfig/selinux
    

    登录主服务器,查看服务器状态

    mysql > show master status \G
    

    然后重新配置一次从服务器,在配置之前首先关闭主从同步进程

    mysql > stop slave;
    

    Slave_SQL_Running: No

    把上面的Slave_IO_Running调试成YES后,就轮到这个小样了。我根据这个博客的内容来解决的:http://kerry.blog.51cto.com/172631/277414

    MySQL复制类型及应用选项

    MySQL 复制可以分为以下几种类型:

    图片

    默认的复制是异步复制,而很多新同学因为不了解 MySQL 除了异步复制还有其他复制的类型,所以错误地在业务中使用了异步复制。为了解决这个问题,我们一起详细了解一下每种复制类型,以及它们在业务中的选型,方便你在业务做正确的选型。

    异步复制

    在异步复制(async replication)中,Master 不用关心 Slave 是否接收到二进制日志,所以 Master 与 Slave 没有任何的依赖关系。你可以认为 Master 和 Slave 是分别独自工作的两台服务器,数据最终会通过二进制日志达到一致。

    异步复制的性能最好,因为它对数据库本身几乎没有任何开销,除非主从延迟非常大,Dump Thread 需要读取大量二进制日志文件。

    如果业务对于数据一致性要求不高,当发生故障时,能容忍数据的丢失,甚至大量的丢失,推荐用异步复制,这样性能最好(比如像微博这样的业务,虽然它对性能的要求极高,但对于数据丢失,通常可以容忍)。但往往核心业务系统最关心的就是数据安全,比如监控业务、告警系统。

    半同步复制

    半同步复制要求 Master 事务提交过程中,至少有 N 个 Slave 接收到二进制日志,这样就能保证当 Master 发生宕机,至少有 N 台 Slave 服务器中的数据是完整的。

    半同步复制并不是 MySQL 内置的功能,而是要安装半同步插件,并启用半同步复制功能,设置 N 个 Slave 接受二进制日志成功,比如:

    plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"rpl-semi-sync-master-enabled = 1rpl-semi-sync-slave-enabled = 1rpl_semi_sync_master_wait_no_slave = 1
    

    上面的配置中:

    • 第 1 行要求数据库启动时安装半同步插件;
    • 第 2、3 行表示分别启用半同步 Master 和半同步 Slave 插件;
    • 第 4 行表示半同步复制过程中,提交的事务必须至少有一个 Slave 接收到二进制日志。

    在半同步复制中,有损半同步复制是 MySQL 5.7 版本前的半同步复制机制,这种半同步复制在Master 发生宕机时,Slave 会丢失最后一批提交的数据,若这时 Slave 提升(Failover)为Master,可能会发生已经提交的事情不见了,发生了回滚的情况。

    有损半同步复制原理如下图所示:

    图片

    可以看到,有损半同步是在 Master 事务提交后,即步骤 4 后,等待 Slave 返回 ACK,表示至少有 Slave 接收到了二进制日志,如果这时二进制日志还未发送到 Slave,Master 就发生宕机,则此时 Slave 就会丢失 Master 已经提交的数据。

    而 MySQL 5.7 的无损半同步复制解决了这个问题,其原理如下图所示:

    图片

    从上图可以看到,无损半同步复制 WAIT ACK 发生在事务提交之前,这样即便 Slave 没有收到二进制日志,但是 Master 宕机了,由于最后一个事务还没有提交,所以本身这个数据对外也不可见,不存在丢失的问题。

    所以,对于任何有数据一致性要求的业务,如电商的核心订单业务、银行、保险、证券等与资金密切相关的业务,务必使用无损半同步复制。这样数据才是安全的、有保障的、即使发生宕机,从机也有一份完整的数据。

    多源复制

    无论是异步复制还是半同步复制,都是 1 个 Master 对应 N 个 Slave。其实 MySQL 也支持 N 个 Master 对应 1 个 Slave,这种架构就称之为多源复制。

    多源复制允许在不同 MySQL 实例上的数据同步到 1 台 MySQL 实例上,方便在 1 台 Slave 服务器上进行一些统计查询,如常见的 OLAP 业务查询。

    多源复制的架构如下所示:

    图片

    上图显示了订单库、库存库、供应商库,通过多源复制同步到了一台 MySQL 实例上,接着就可以通过 MySQL 8.0 提供的复杂 SQL 能力,对业务进行深度的数据分析和挖掘。

    延迟复制

    前面介绍的复制架构,Slave 在接收二进制日志后会尽可能快地回放日志,这样是为了避免主从之间出现延迟。而延迟复制却允许Slave 延迟回放接收到的二进制日志,为了避免主服务器上的误操作,马上又同步到了从服务器,导致数据完全丢失。

    我们可以通过以下命令设置延迟复制:

    CHANGE MASTER TO master_delay = 3600
    

    这样就人为设置了 Slave 落后 Master 服务器1个小时。

    延迟复制在数据库的备份架构设计中非常常见,比如可以设置一个延迟一天的延迟备机,这样本质上说,用户可以有 1 份 24 小时前的快照。

    那么当线上发生误操作,如 DROP TABLE、DROP DATABASE 这样灾难性的命令时,用户有一个 24 小时前的快照,数据可以快速恢复。

    对金融行业来说,延迟复制是你备份设计中,必须考虑的一个架构部分。

    总结

    看到这里的同学,你一定会对 MySQL 复制技术有一个清晰的了解,认识到复制是数据同步的基础,而二进制日志就是复制的基石。总结一下今天的重点:

    1. 二进制日志记录了所有对于 MySQL 变更的操作;
    2. 可以通过命令 SHOW BINLOG EVENTS IN ... FROM ... 查看二进制日志的基本信息;
    3. 可以通过工具 mysqlbinlog 查看二进制日志的详细内容;
    4. 复制搭建虽然简单,但别忘记配置 crash safe 相关参数,否则可能导致主从数据不一致;
    5. 异步复制用于非核心业务场景,不要求数据一致性;
    6. 无损半同步复制用于核心业务场景,如银行、保险、证券等核心业务,需要严格保障数据一致性;
    7. 多源复制可将多个 Master 数据汇总到一个数据库示例进行分析;
    8. 延迟复制主要用于误操作防范,金融行业要特别考虑这样的场景。
    展开全文
  • Linux搭建mysql主从同步

    千次阅读 2022-03-16 14:37:11
    Linux搭建mysql主从同步
  • MySQL主从同步配置

    2021-03-15 04:18:52
    MySQL主从同步1. 主从同步的定义主从同步使得数据可以从一个数据库服务器复制到其他服务器上,在复制数据时,一个服务器充当主服务器(master),其余的服务器充当从服务器(slave)。因为复制是异步进行的,所以从...
  • 一个不规范操作导致MySQL主从同步中断(GTID模式) 数据量激增,导致MySQL主从同步延迟 If you have any questions or any bugs are found, please feel free to contact me. Your comments and suggestions are ...
  • MySQL主从同步(一)——原理详解

    千次阅读 2021-12-26 12:52:44
    一、MySQL主从同步简介 MySQL主从同步,即MySQL Replication,可以实现将数据从一台数据库服务器同步到多台数据库服务器。MySQL数据库自带主从同步功能,经过配置,可以实现基于库、表结构的多种方案的主从同步。 ...
  • MYSQL 主从同步详解

    2021-01-25 23:48:39
    关于MYSQL主从同步什么是MYSQL的主从复制主从复制是指将一个服务器作为主服务器,所有的数据更新操作都在主服务器进行,并且将主服务器的数据同步到一个或多个从服务器,保证从服务器和主服务器的数据一致。...
  • MySQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力。 MySQL主从同步的机制: MySQL同步的流程大致如下:  1、主...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 113,495
精华内容 45,398
关键字:

mysql主从同步

mysql 订阅