精华内容
下载资源
问答
  • 今天小编就为大家分享一篇关于MySQL主从同步延迟的原因及解决办法,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
  • mysql主从同步检查脚本 。主要运用于日常maysql运行主从同步状态检查。
  • mysql主从同步配置详解、一台服务器两个mysql,本地安装两个mysql实现主从同步
  • 超详细MySQL主从配置和主主同步总结。。。。。。。。。
  • C#开发的MySql主从监控程序,监控同步情况以及连接数。 可以监控多级塔式分发模式。 同步故障支持发送邮件。 VS2010开发,代码完整,可直接编译。
  • mysql主从同步原理和一些主从同步出现问题解决方案,干货多多
  • 实现监控LINUX系统下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 ...
  • 12.1: MySQL主从同步 、 主从同步模式 、 总结和答疑.docx
  • mysql主从同步(二)

    2021-01-07 06:18:14
    一:主mysql的my.ini配置 [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [mysqld] #设置3306端口 port = 3306 # 设置mysql的安装目录 basedir=”D:\mysql\mysql-5.7.27-winx64″ # 设置mysql...
  • Mysql主从同步的Last_IO_Errno:1236错误是什么原因呢,我们要如何来解决这个问题呢?下面和小编一起来看看关于此问题的记录与解决办法。 [removed]ec(2);[removed] 从服务器错误代码: Last_IO_Errno: 1236 Last...
  • MySQL主从同步与读写分离配置图文详解
  • 一、主机规划 服务器 IP zabbix-server ...mysql-master、zabbix-agent ...mysql-slave、zabbix-agent ...二、MySQL数据库主从搭建...2、删除系统自带mysql配置文件(主从库均需删除) [root@master ~]# yum remove -y ma
  • mysql主从同步+MyCat读写分离+分库分表+Haproxy高可用+Keepalived心跳机制+虚拟IP.rar
  • 主从服务器上的MySQL数据库版本同为5.1.34
  • Mysql主从同步

    千次阅读 2019-09-16 19:18:38
    一、Mysql主从同步概述 -实现数据自动同步的服务结构 -主服务器:接受客户服务连接 -从服务器:自动同步主服务器数据,并且在主服务器宕机时充当主服务器 二、主从同步原理 从这张图来看,主从同步分为两...

    一、Mysql主从同步概述

    -实现数据自动同步的服务结构

    -主服务器:接受客户服务连接

    -从服务器:自动同步主服务器数据,并且在主服务器宕机时充当主服务器

     

    二、主从同步原理

    从这张图来看,主从同步分为两个部分,一个是主服务器部分(Master),一个是从服务器部分(Slave)

    主服务器:

    1.Binary log :也就是binlog日志,主要用于记录sql操作[详解]https://blog.csdn.net/ck784101777/article/details/100766138

    从服务器:

    1.Relay log : 中继日志是连接mastert和slave的信息,它是复制的核心,I/O线程将来自master的事件存储到中继日志中,中继日志充当缓冲,这样master不必等待slave执行完成就可以发送下一个事件。

    2.I/O thread :从服务器上运行的一个线程,主要用于读写操作,即读取主服务器上的binlog日志,并且写到从服务器上的中继日志上.默认不开启,需手动打开

    3.Sql thread:Sql线程,从服务器上运行的一个线程,主要用于读取中继服务器上的二进制文件,并且将其转换为sql语句,并且在Mysql服务中执行

    既然了解了主从服务器上的工作的组件,我们来演练一下具体实现主从同步数据的过程

    主服务器上插入一条数据-->binlog日志发现并且记载-->从服务器上的I/O线程实时监控binlog日志,发现新记录并读取-->I/O线程将读取过来的数据写到中继日志中-->Sql线程监控到中继日志的变化-->Sql线程将新记录转换为Sql语句并且在mysql中执行(后台执行)

     

     

    三、主从同步的配置

     我们通过一个实验来看看如何配置主从同步,如图所示,我们准备两台Mysql服务器,一台作为Master服务器,一台作为Slave服务器,配置ip地址

    配置主服务器:

    -启用binlog日志,授权用户,查看binlog日志

    配置从服务器:

    -设置server_id

    -保证主从服务器数据一致

    -配置同步信息

    -启动slave程序,即启动IO和Sql线程

    -查看状态信息,是否连接成功,即线程是否启动

     

    主服务器配置:

    1)启用binlog日志

    1. ]# vim /etc/my.cnf
    2. [mysqld]
    3. server_id=51            //server_id
    4. log-bin=master51        //日志名
    5. :wq
    6. ]# systemctl restart mysqld

    2)用户授权

    用户名自定义、客户端地址使用% 或 只指定 从服务器的地址 都可以、只给复制数据的权限即可。

    1. ]# mysql -uroot -p密碼
    2. mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
    3.  
    4. mysql>quit;

    3)查看binlog日志信息

    查看日志文件名 和 偏移量位置。

    mysql> show master status\G;

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

    File: master51.000001 //日志名

    Position: 441 //偏移量

    Binlog_Do_DB:

    Binlog_Ignore_DB:

    Executed_Gtid_Set:

    1 row in set (0.00 sec)

    从服务器配置: 

    1)指定server_id

    Server_id值可以自定义,但不可以与主服务器相同。(取值范围1-255)

    1. ]# vim /etc/my.cnf
    2. [mysqld]
    3. server_id=52 //server_id值
    4. :wq
    5. ]# systemctl restart mysqld //重启服务

    2)确保与主服务器数据一致(如果是使用2台新部署的数据库服务器配置主从同步,此操作可以忽略)

        密码写你登录数据库的密码,导入的时候如果

    1. ]# mysqldump -uroot –p密码  -A > /allbak.sql //在主服务器上备份全部数据
    2. ]# scp /allbak.sql root@192.168.4.52:/root/ //将备份文件拷贝给从服务器
    3.  
    4. ]# mysql -uroot –p密码 数据库名 < /root/allbak.sql  //从服务器使用备份文件恢复数据
    5.  

    3)指定主服务器信息

    数据库管理员root本机登录,指定主服务器信息,其中日志文件名和偏移量 写allbak.sql文件记录的。

    指定主服务器信息后,会将主服务器信息保存在/var/lib/mysql/master.info文件下,若需要修改,则有两种途径,一种是在mysql下通过命令修改,而是修改上述文件.修改前必须停止slave线程,命令:stop slave 修改完后开启线程 : start slave

    1. ]# mysql -uroot –p密码 //管理员root 本机登录
    2. mysql> show slave status; //查看状态信息,还不是从服务器
    3. Empty set (0.00 sec)
    4.  
    5. mysql> change master to   //指定主服务器
    6. -> master_host=“192.168.4.51”,  //主服务器ip地址
    7. -> master_user=“repluser”,   //主服务器授权用户
    8. -> master_password=“123qqq…A”,  //主服务器授权用户密码
    9. -> master_log_file=“master51.000001 ”, //主服务器日志文件,以主服务器显示的为主
    10. -> master_log_pos=441;           //主服务器日志偏移量,切忌不可加双引号
    11. -> start slave;   //启动线程
    12. mysql> show slave status\G;    //查看状态信息,主要查看两个线程是否启动
    13. *************************** 1. row ***************************
    14. Slave_IO_State: Waiting for master to send event
    15. Master_Host: 192.168.4.51 //主服务器ip地址
    16. Master_User: repluser
    17. Master_Port: 3306
    18. Connect_Retry: 60
    19. Master_Log_File: master51.000001
    20. Read_Master_Log_Pos: 437
    21. Relay_Log_File: host52relay-bin.000002
    22. Relay_Log_Pos: 604
    23. Relay_Master_Log_File: master51.000001
    24. Slave_IO_Running: Yes        //IO线程yes状态
    25. Slave_SQL_Running: Yes        //SQL线程yes状态
    26. ...........................

    从服务相关文件信息:

    主从同步连接测试

    通过授权用户在客户端进行数据插入,插入数据后查看从服务器数据库是否同步数据

     

    1)在主服务器添加访问数据的连接用户

    授权用户对所有数据有增删改查的权限即可

    1. ]# mysql –uroot –p密码
    2. mysql> grant select,insert,update,delete on *.* to admin@"%" identified by "123qqq...A";
    3. Query OK, 0 rows affected, 1 warning (0.03 sec)
    4.  
    5. mysql> quit

    2)客户端连接主服务器访问数据

    在50主机 使用主服务器51的授权用户连接

    1. ]# mysql -h192.168.4.51-uadmin -p123qqq...A
    2. mysql> show grants;
    3. +------------------------------------------------------------+
    4. | Grants for admin@% |
    5. +------------------------------------------------------------+
    6. | GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'admin'@'%' |
    7. +------------------------------------------------------------+
    8. 1 row in set (0.00 sec)
    9.  
    10. mysql> insert into db3.user(name,uid) values("lili",288); //db3库和user表是主从同步之前主服务器已有的。
    11. Query OK, 1 row affected (0.05 sec)
    12.  
    13. mysql> insert into db3.user(name,uid) values("lili",288);
    14. Query OK, 1 row affected (0.28 sec)
    15.  
    16. mysql> insert into db3.user(name,uid) values("lili",288);
    17. Query OK, 1 row affected (0.05 sec)
    18.  
    19. mysql> select name,uid from db3.user where name="lili";
    20. +------+------+
    21. | name | uid |
    22. +------+------+
    23. | lili | 288 |
    24. | lili | 288 |
    25. | lili | 288 |
    26. +------+------+
    27. 3 rows in set (0.00 sec)

    3) 客户端连接从服务器访问数据

    客户端50主机使用授权用户连接从服务器可以看到和主服务器同样的数据

    1. ]# mysql -h192.168.4.52 –uadmin -p123qqq…A
    2. mysql> select name,uid from db3.user where name="lili";
    3. +------+------+
    4. | name | uid |
    5. +------+------+
    6. | lili | 288 |
    7. | lili | 288 |
    8. | lili | 288 |
    9. +------+------+
    10. 3 rows in set (0.00 sec)

     

    四、主从同步结构

    主从同步不仅仅只有一主多从的结构,有四种常用的结构

    1.一主一从,单向复制: 一主<--一从

    2.一主多从,中心化复制:一从-->一主<--一主

    3.一主多从,链式复制:一主<--一从<--一从

    4.多主模式,互为主从:一主<-->一主

    一主多从-中心化实验案例:

    准备3台Mysql服务器,一台作为主服务器,两台作为从服务器,配置内容与一主多从模式相同,参照上一个案例,如果你做了上一个案例,这里只需要配置第二台从服务即可,配置内容与第一台一致

    一主多从-链式复制案例:

      准备三台Mysql服务器,与上面实验不同的是,这里需要一台主服务器,一台即作为主服务器又作为从服务器,一台作为从服务器

      简单的介绍一下这种服务结构:首先你需要知道的是,我们在做一主一从的服务结构时,不可以在从服务器上插入数据(因为主服务器无法同步从服务器上的数据),在这里我们将数据插入到中间的服务器(这里是192.168.4.54的服务器)有和意义呢,想想一下这样一种情况,我们公司新增了一个部门,并且希望配置一台数据库服务器来存储这个部门的信息,但是它需要用到某个旧部门的数据信息(这里是192.168.4.53的数据库服务器),所有我们为它做了主从同步,将这一台新的服务器作为从服务器,并且同步旧部门的数据库信息,但是我们为了提高数据可靠性,希望有一台服务器最为这两台服务器的从服务器,此时我们就可以用到一主多从-链式复制的结构,这样就同步了两台数据库的信息(相当于取并集),这台从服务器从它的主服务器上同步信息,被同步的主服务器又可以通过自己的主服务器同步信息,此时两台服务器的数据都得到了同步.

      链式复制还有一个好处,那就是当上一级主服务器宕机后,当前服务器充当主服务器使用

    从服务器配置(192.168.4.54)

    这里省略了主服务器192.168.4.53的配置,如果做了我上面讲述的实验,这里假设你已经完成了这两台服务器的主从同步配置

    步骤一:环境准备

    为了在启用binlog日志及同步之前保持主、从库的一致性,主从同步未配置之前,要保证从库上要有主库上的数据,禁用selinux,关闭防火墙服务,保证物理连接正常

    1)关闭防火墙,禁用selinux,已关可忽略:

    1. ]# systemctl stop firewalld
    2. ]# setenforce 0

    步骤二:配置主服务器192.168.4.53

    2)用户授权

    1. ]# mysql -uroot -p123456
    2. mysql> grant replication slave on *.* to yaya@"%" identified by "123qqq…A“;
    3. Query OK, 0 rows affected, 1 warning (0.03 sec)

    3)启用binlog日志,修改/etc/my.cnf配置,重新启动MySQL服务程序

    指定服务器ID号、允许日志同步:

    1. ]# vim /etc/my.cnf
    2. [mysqld]
    3. log_bin=db53                     //启用binlog日志,并指定文件名前缀
    4. server_id=53                 //指定服务器ID号

    4)重启mysql服务:

    1. ]# systemctl restart mysqld

    5)确保/var/lib/mysql下面有两个文件:

    1. ]# ls /var/lib/mysql/db51.*
    2. /var/lib/mysql/db53.000001 /var/lib/mysql/db53.index

    6)查看主服务正在使用的日志信息

    查看主服务器状态,记录下当前的日志文件名、偏移的位置(下面SLAVE发起复制时需要用到):

    1. mysql> show master status;
    2. +-------------+----------+--------------+------------------+-------------------+
    3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    4. +-------------+----------+--------------+------------------+-------------------+
    5. | db53.000001 |437 | | | |
    6. +-------------+----------+--------------+------------------+-------------------+
    7. 1 row in set (0.00 sec)

    从服务器配置(192.168.4.55) 

    步骤一:配置从服务器192.168.4.55

    1)验证主库的授权用户

    1. ]# mysql -h192.168.4.54 -uuser54 -p654321
    2. mysql: [Warning] Using a password on the command line interface can be insecure.
    3. Welcome to the MySQL monitor. Commands end with ; or \g.
    4. Your MySQL connection id is 7
    5. Server version: 5.7.17-log MySQL Community Server (GPL)
    6.  
    7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    8.  
    9. Oracle is a registered trademark of Oracle Corporation and/or its
    10. affiliates. Other names may be trademarks of their respective
    11. owners.
    12.  
    13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    14.  
    15. mysql> //验证成功

    2)指定server_id

    1. [mysqld]
    2. server_id=55

    3)重新启动服务

    1. ]# systemctl restart mysqld

    4)管理员登录指定主库信息

    1. ]# mysql -uroot -p123456
    2. mysql> change master to
    3. -> master_host="192.168.4.54”;
    4. -> master_user="user55”;
    5. -> master_password="654321",
    6. -> master_log_file=" db54.000001”;
    7. -> master_log_pos=154;
    8. Query OK, 0 rows affected, 2 warnings (0.37 sec)

    5)启动slave进程

    1. mysql> start slave;
    2. Query OK, 0 rows affected (0.04 sec)

    6)查看进程状态信息

    1. mysql> show slave status\G
    2. *************************** 1. row ***************************
    3. Slave_IO_State: Waiting for master to send event
    4. Master_Host: 192.168.4.54
    5. Master_User: user55
    6. Master_Port: 3306
    7. Connect_Retry: 60
    8. Master_Log_File: db54.000001
    9. Read_Master_Log_Pos: 154
    10. Relay_Log_File: db55-relay-bin.000001
    11. Relay_Log_Pos: 315
    12. Relay_Master_Log_File: db54.000001
    13. Slave_IO_Running: Yes
    14. Slave_SQL_Running: Yes

    互为主从-多主模式

     要想配置多主模式,你必须了解主从同步的复制模式,并将复制模式更改为半同步复制模式.

    [如何更改复制模式]https://blog.csdn.net/ck784101777/article/details/100918876 

     这里假设你已经更改完,那么配置起来就很容易了,你只需要在两台Mysql服务器上配置指定对方的主机即可,通过 change master to命令,参考上文配置格式.

     多主模式的好处就是任意一台服务宕机,剩下的一台都可以作为主服务器,多主模式也是数据库集群架构的基础,缺点是使用两倍的硬件资源,但是存储相同的数据,多少有点浪费资源

     

    展开全文
  • 当我们搭建MySQL集群时,自然需要完成数据库的主从同步来保证数据一致性。而主从同步的方式也分很多种,一主多从、链式主从、多主多从,根据你的需要来进行设置。但只要你需要主从同步,就一定要注意server-id的配置...
  • 什么是mysql主从同步

    2019-12-25 10:32:09
    什么是MySQL主从同步 数据是一个应用至关重要的一部分。从目的出发,主从同步有那么点备份的意思,主库(Master)将自己库中的写入同时同步给自己的从库(Slave),当主库发生某些不可预知的状况,导致整个服务器...

    什么是MySQL主从同步

    数据是一个应用至关重要的一部分。从目的出发,主从同步有那么点备份的意思,主库(Master)将自己库中的写入同时同步给自己的从库(Slave),当主库发生某些不可预知的状况,导致整个服务器无法使用时,由于从库中也有一份数据,所以数据可以做到快速恢复,不造成或者减少造成数据的损失。

    当然,这只是第一个层面,如果主从库的作用仅限于此,那么我个人认为没有必要分为两个数据库,只需要定期将数据库内容作为快照发送到另一台服务器,或者每次写入时将写入内容实时发送到另一台服务器不就好了吗,这样不但可以节约资源,也可以起到容灾备份的目的。

    当然主从同步的作用绝不可能仅限于此,一旦我们配置了主从结构,我们通常不会让从节点仅仅只作为备份数据库,我们应该还会相应地配置上读写分离(可以使用MyCat或者其它中间件,可以自己了解一下,关于MyCat我在下一篇博客中会说这个,篇幅可能会有点长,所以就再写一篇吧)。

    在实际环境下,对于数据库的读操作数目远大于对数据库的写操作,所以我们可以让Master只提供写的功能,然后将所有的读操作都移到从库,这就是我们平时常说的读写分离,这样不但可以减轻Master的压力,还可以做容灾备份,一举两得

    MySQL主从同步的原理

    说完了主从同步的概念,下面来说说主从同步的原理,其实原理也非常简单,没有Redis集群那么多的概念。

    实际上当我们在MySQL中配置了主从之后,只要我们对Master节点进行了写操作,这个操作将会被保存到MySQL的binary-log(bin-log)日志当中,当slave连接到master的时候,master机器会为slave开启binlog dump线程。当master 的 binlog发生变化的时候,Master的dump线程会通知slave,并将相应的binlog内容发送给Slave。而Slave节点在主从同步开启的时候,会创建两个线程,一个I/O线程,一个SQL线程,这在我们后面的搭建中可以亲眼看到。

    • I/0线程:该线程链接到master机器,master机器的binlog发送到slave的时候,IO线程会将该日志内容写在本地的中继日志(Relay log)中。

    • SQL线程:该线程读取中继日志中的内容,并且根据中继日志中的内容对Slave数据库做相应的操作。

    • 可能造成的问题:在写请求相当多的情况下,可能会造成Slave数据和Master数据不一致的情况,这是因为日志传输过程中的短暂延迟、或者写命令较多,系统速度不匹配造成的。

    这大致就是MySQL主从同步的原理,真正在其中起到作用的实际上就是这两个日志文件,binlog和中继日志。

    手动搭建MySQL主从同步

    环境准备

    本次搭建主从同步的环境:CentOS 7 ,MySQL 8.0.18(使用二进制包安装)。

    场景介绍

    本次将会搭建MySQL的主从同步,其中一台Master,两台Slave。

    Master:IP :192.168.43.201 Port:3306
    Slave1:IP:192.168.43.202 Port:3306
    Slave2:IP:192.168.43.203 Port:3306
    

    开始搭建

    修改配置文件

    当我们安装好MySQL之后,在/etc/目录下会有一个my.cnf文件,打开文件,加入如下内容(别忘了修改之前做好备份):

    x

    #该配置为Master的配置
    server-id=201 #Server id 每台MySQL的必须不同
    log-bin=/var/lib/mysql/mysql-bin.log #代表开启binlog日志
    expire_logs_days=10 #日志过期时间
    max_binlog_size=200M #日志最大容量
    binlog_ignore_db=mysql #忽略mysql库,表示不同步此库
    

    y

    #该配置为Slave的配置,第二台Slave也是这么配置,不过要修改一下server-id
    server-id=202
    expire_logs_days=10 #日志的缓存时间
    max_binlog_size=200M #日志的最大大小
    replicate_ignore_db=mysql #忽略同步的数据库
    

    新增Slave用户

    打开Master节点的客户端 ,mysql -u root -p 密码

    创建用户 create user 'Slave'@'%' identified by '123456';

    给新创建的用户赋权:grant replication slave on '*.*' to 'Slave'@'%';

    查看Master节点状态

    以上操作都没有问题后,我们在客户端中输入show master status查看master的binlog日志。

    配置两个Slave节点

    打开两个Slave节点客户端,在我们的另外两个Slave节点中输入如下命令:

    change master to master_user='Slave',master_password='123456',master_host='192.168.43.201',master_log_file='mysql-bin.000005',master_log_pos=155,get_master_public_key=1;
    #注意,这里的master_log_file,就是binlog的文件名,输入上图中的mysql-bin.000005,每个人的都可能不一样。
    #注意,这里的master_log_pos是binlog偏移量,输入上图中的155,每个人的都可能不一样。
    

    配置完成后,输入start slave;开启从节点,然后输入show slave status\G;查看从节点状态

    可以看到,在两台Slave的状态中,我们能亲眼看到IO线程和SQL线程的运行状态,这两个线程必须都是yes,才算配置搭建完成。

    搭建完成

    通过上述步骤,就完成了MySQL主从同步的搭建,相对Redis而言MySQL配置相当简单。下面我们可以进行测试。

    先看看三个MySQL的数据库状态:SHOW DATABASES;

    可以看到现在数据库都是初始默认状态,没有任何额外的库。

    在Master节点中创建一个数据库,库名可以自己设置。

    CREATE DATABASE testcluster;

    可以看到,在Slave中也出现了Master中创建的数据库,说明我们的配置没有问题,主从搭建成功。这里就不再创建表了,大家可以自己试试,创建表再往表中插入数据,也是没有任何问题的。

    注意事项

    如果出现IO线程一直在Connecting状态,可以看看是不是三台机器无法相互连接,如果可以相互连接,那么有可能是Slave账号密码写错了,重新关闭Slave然后输入上面的配置命令再打开Slave即可。

    如果出现SQL线程为NO状态,那么有可能是从数据库和主数据库的数据不一致造成的,或者事务回滚,如果是后者,先关闭Slave,然后先查看master的binlog和position,然后输入配置命令,再输入set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;,再重新start slave;即可,如通过是前者,那么就排查一下是不是存在哪张表没有被同步,是否存在主库存在而从库不存在的表,自己同步一下再重新配置一遍即可。

    结语

    在写这篇文章之前自己也被一些计算机领域的“名词”吓到过,相信有不少同学都有一样的体会,碰上某些高大上的名词总是先被吓到,例如像“分布式”、“集群”等等等等,甚至在没接触过nginx之前,连”负载均衡“、”反向代理“这样的词都让人觉得,这么高达上的词,肯定很难吧,但其实自己了解了nginx、ribbon等之后才发现,其实也就那么回事吧,没有想象中的那么难。

    所以写这篇文章的初衷是想让大家对集群化或者分布式或者其他的一些技术或者解决方案不要有一种望而却步的感觉(感觉计算机领域的词都有这么一种特点,词汇高大上,但是其实思想是比较好理解的),其实自己手动配置出一个简单的集群并没有那么难。

    如果学会docker之后再来配置就更加简单了,但是更希望不要只局限于会配置,配置出来的东西只能说你会配置了,但是在这层配置底下是前人做了相当多的工作,才能使我们通过简单配置就能实现一些功能,应该要深入底层,了解配置下面的工作原理,这个才是最重要的,也是体现一个程序员水平的地方。

    展开全文
  • MySQL 主从同步 为什么要实现主从同步 高并发阶段,数据库压力会非常大。然而实际上大部分的网站、 app,其实都是读多写少。针对这个情况,可以维持一个主库(数据写入),主库挂多个从库(数据读取),主库会自动把...

    MySQL 主从同步

    一、为什么要实现主从同步

    高并发阶段,数据库压力会非常大。然而实际上大部分的网站、 app,其实都是读多写少。针对这个情况,可以维持一个主库(数据写入),主库挂多个从库(数据读取),主库会自动把数据给同步到从库上去,一写多读,减少数据库的查询压力,从而提高并发能力。

    二、MySQL 主从复制原理

    主库将变更写入 binlog 日志,然后从库连接到主库之后,从库有一个 IO 线程,将主库的 binlog 日志拷贝到自己本地,写入一个 relay 中继日志中。接着从库中有一个 SQL 线程会从中继日志读取 binlog,然后执行 binlog 日志中的内容,也就是在自己本地再次执行一遍 SQL,这样就可以保证自己跟主库的数据是一样的。
    mysql主从复制原理

    1. 从库同步主库数据的过程是串行化的,也就是说主库上并行的操作,在从库上会串行执行。由于从库从主库拷贝日志以及串行执行 SQL 的特点,在高并发场景下,从库的数据一定会比主库慢一些,是有延时的。所以经常出现,刚写入主库的数据可能是读不到的,要过几十毫秒,甚至几百毫秒才能读取到。

    2. 如果主库突然宕机,然后恰好数据还没同步到从库,那么有些数据可能在从库上是没有的,有些数据可能就丢失了。

    所以 MySQL 实际上在这一块有两个机制,一个是半同步复制,用来解决主库数据丢失问题;一个是并行复制,用来解决主从同步延时问题。

    (1) 半同步复制,也叫 semi-sync 复制,指的就是主库写入 binlog 日志之后,就会将强制此时立即将数据同步到从库,从库将日志写入自己本地的 relay log 之后,接着会返回一个 ack 给主库,主库接收到至少一个从库的 ack 之后才会认为写操作完成了。

    (2)并行复制,指的是从库开启多个线程,并行读取 relay log 中不同库的日志,然后并行重放不同库的日志,这是库级别的并行。

    MySQL 主从同步延时 show status可以查看 Seconds_Behind_Master,可以看到从库复制主库的数据落后了几ms。

    一般来说,如果主从延迟较为严重,有以下解决方案:

    1. 分库,将一个主库拆分为多个主库,每个主库的写并发就减少了几倍,此时主从延迟可以忽略不计。
    2. 打开 MySQL 支持的并行复制,多个库并行复制。如果说某个库的写入并发就是特别高,单库写并发达到了 2000/s,并行复制还是没意义。
    3. 重写代码,写代码的同学,要慎重,插入数据时立马查询可能查不到。
      如果确实是存在必须先插入,立马要求就查询到,然后立马就要反过来执行一些操作,对这个查询设置直连主库。

    三、MySQL主从复制实践

    MySQL主从复制一般情况下我们会设置需要同步的数据库,使用参数配置选项:binlog-do-db,可以在master上指定需要同步的数据库;replicate-do-db,在从数据看上指定需要同步的数据库。(一般只设定master上的binlog-do-db即可,不需要两个同时设定。以防万一,在slave也可以加上replicate-ignore-db)。

    3.1 环境准备

    准备好两个数据库,手动将主库数据同步到从库。

    (1)备份数据: mysqldump -uroot -p123456 oyc > /home/data/oyc.sql
    (2)导入到从库
    

    3.2 主库配置

    (1)配置主库配置文件

    vim /etc/my.cnf
    配置:
    log-bin=mysql-bin #开启二进制日志
    server-id=1 #设置server-id
    

    (2)创建同步用户并授权

    mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';#创建用户 
    
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';#分配权限 
    
    mysql> flush privileges;   #刷新权限
    

    (3)重启MySQL并查看主库状态

    systemctl restart mysqld

    mysql> show master status;
    ±-----------------±---------±-------------±-----------------±------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    ±-----------------±---------±-------------±-----------------±------------------+
    | mysql-bin.000001 | 154 | | | |
    ±-----------------±---------±-------------±-----------------±------------------+
    1 row in set (0.00 sec)

    3.3 主库配置

    (1) 配置从库配置文件

    vim /etc/my.cnf
     
    server-id=2                  #设置server-id,必须唯一
    replicate-do-db=oyc
    replicate-do-table=oyc.user
    
    (# 只同步哪些数据库,除此之外,其他不同步  
    binlog-do-db = oycblog  
    # 不同步哪些数据库  
    binlog-ignore-db = mysql  
    binlog-ignore-db = test  
    binlog-ignore-db = information_schema  )
    

    (2) 重启MySQL并配置主从关系

    systemctl restart mysqld


    执行mysql指令:
    CHANGE MASTER TO
    MASTER_HOST=‘146.56.192.87’,
    MASTER_USER=‘repl’,
    MASTER_PASSWORD=‘123456’,
    MASTER_LOG_FILE=‘mysql-bin.000001’,
    MASTER_LOG_POS=154;

    mysql> start slave;
    
    mysql> show slave status\G;
    
    mysql> stop slave;
    

    在这里插入图片描述

    3.4 其他配置

    忽略错误

    mysql> stop slave;
    mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ;       #跳过一个事务
    mysql> start slave
    
    展开全文
  • 主要介绍了MySQL主从同步原理介绍,本文讲解了主从同步概述、主从同步需求、主从同步过程等内容,需要的朋友可以参考下
  • 一、MySQL数据库主从同步延迟产生的原因 MySQL的主从复制都是单线程的操作,主库对所有DDL和DML产生的日志写进binlog,由于binlog是顺序写,所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中...

    一、MySQL数据库主从同步延迟产生的原因

    MySQL的主从复制都是单线程的操作,主库对所有DDL和DML产生的日志写进binlog,由于binlog是顺序写,所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的,不是顺序的,成本高很多。另一方面,由于SQL Thread也是单线程的,当主库的并发较高时,产生的DML数量超过slave的SQL Thread所能处理的速度,或者当slave中有大型query语句产生了锁等待那么延时就产生了。

    常见原因:Master负载过高、Slave负载过高、网络延迟、机器性能太低、MySQL配置不合理。
    
    关于DDL和DML
    
    SQL语言共分为以下几大类:查询语言DQL,控制语言DCL,操纵语言DML,定义语言DDL。事务控制TCL.
    
    DQL(Data QUERY Languages)语句:即数据库定义语句,用来查询SELECT子句,FROM子句,WHERE子句组成的查询块,比如:select–from–where–grouop by–having–order by–limit
    
    DDL(Data Definition Languages)语句:即数据库定义语句,用来创建数据库中的表、索引、视图、存储过程、触发器等,常用的语句关键字有:CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增删改表的结构
    
    DML(Data Manipulation Language)语句:即数据操纵语句,用来查询、添加、更新、删除等,常用的语句关键字有:SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。增删改表的数据
    
    DCL(Data Control Language)语句:即数据控制语句,用于授权/撤销数据库及其字段的权限(DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.)。常用的语句关键字有:GRANT,REVOKE。
    
    TCL(Transaction Control Language)语句:事务控制语句,用于控制事务,常用的语句关键字有:COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。
    

    二、主从延时排查方法

    通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断:

    NULL,表示io_thread或是sql_thread有任何一个发生故障;

    0,该值为零,表示主从复制良好;

    正值,表示主从已经出现延时,数字越大表示从库延迟越严重
    在这里插入图片描述

    三、解决方案

    解决数据丢失的问题:

    1. 半同步复制

    从MySQL5.5开始,MySQL已经支持半同步复制了,半同步复制介于异步复制和同步复制之间,主库在执行完事务后不立刻返回结果给客户端,需要等待至少一个从库接收到并写到relay log中才返回结果给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一个TCP/IP往返耗时的延迟。

    1. 主库配置sync_binlog=1,innodb_flush_log_at_trx_commit=1

    sync_binlog的默认值是0,MySQL不会将binlog同步到磁盘,其值表示每写多少binlog同步一次磁盘。

    innodb_flush_log_at_trx_commit为1表示每一次事务提交或事务外的指令都需要把日志flush到磁盘。

    注意:将以上两个值同时设置为1时,写入性能会受到一定限制,只有对数据安全性要求很高的场景才建议使用,比如涉及到钱的订单支付业务,而且系统I/O能力必须可以支撑!
    

    1.解决从库复制延迟的问题:

    1)、架构方面

    1.业务的持久化层的实现采用分库架构,mysql服务可平行扩展,分散压力。

    2.单个库读写分离,一主多从,主写从读,分散压力。这样从库压力比主库高,保护主库。

    3.服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。

    4.不同业务的mysql物理上放在不同机器,分散压力。

    5.使用比主库更好的硬件设备作为slave,mysql压力小,延迟自然会变小。

    2)、硬件方面

    1.采用好服务器,比如4u比2u性能明显好,2u比1u性能明显好。

    2.存储用ssd或者盘阵或者san,提升随机写的性能。

    3.主从间保证处在同一个交换机下面,并且是万兆环境。

    总结,硬件强劲,延迟自然会变小。一句话,缩小延迟的解决方案就是花钱和花时间。

    3)、mysql主从同步加速

    1、sync_binlog在slave端设置为0

    2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

    3、直接禁用slave端的binlog

    4、slave端,如果使用的存储引擎是innodb,innodb_flush_log_at_trx_commit =2

    4)、从文件系统本身属性角度优化
    master端修改linux、Unix文件系统中文件的etime属性, 由于每当读文件时OS都会将读取操作发生的时间回写到磁盘上,对于读操作频繁的数据库文件来说这是没必要的,只会增加磁盘系统的负担影响I/O性能。可以通过设置文件系统的mount属性,组织操作系统写atime信息,在linux上的操作为:打开/etc/fstab,加上noatime参数/dev/sdb1 /data reiserfs noatime 1 2然后重新mount文件系统#mount -oremount /data

    5)、同步参数调整主库是写,对数据安全性较高,比如sync_binlog=1,innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而slave则不需要这么高的数据安全,完全可以讲sync_binlog设置为0或者关闭binlog,innodb_flushlog也可以设置为0来提高sql的执行效率

    2.MySql数据库从库同步其他问题及解决方案

    1)、mysql主从复制存在的问题:
    ● 主库宕机后,数据可能丢失
    ● 从库只有一个sql Thread,主库写压力大,复制很可能延时

    2)、解决方法:
    ● 半同步复制—解决数据丢失的问题
    ● 并行复制----解决从库复制延迟的问题

    3)、半同步复制mysql semi-sync(半同步复制)半同步复制:
    ● 5.5集成到mysql,以插件的形式存在,需要单独安装
    ● 确保事务提交后binlog至少传输到一个从库
    ● 不保证从库应用完这个事务的binlog
    ● 性能有一定的降低,响应时间会更长
    ● 网络异常或从库宕机,卡主主库,直到超时或从库恢复

    4)、主从复制–异步复制原理、半同步复制和并行复制原理比较
    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • 关于mysql主从同步,相信大家都不陌生,随着系统应用访问量逐渐增大,单台数据库读写访问压力也随之增大,当读写访问达到一定瓶颈时,将数据库的读写效率骤然下降,甚至不可用;为了解决此类问题,通常会采用mysql...
  • 在异步或半同步的复制结构中,从库出现延迟是一件十分正常的事。 虽出现延迟正常,但是否需要关注,则一般是由业务来评估。 如:从库上有需要较高一致性的读业务,并且要求延迟小于某个值,那么则需要关注。 简单...
  • DBA06 - MySQL主从同步、主从同步模式

    千次阅读 2018-07-20 22:43:23
    一、什么是mysql主从同步 主:正在被客户端访问的数据库服务器,被称作主库服务器。 从:自动同步主库上的数据的数据库服务器,被称作从库服务器。 二、配置mysql主从同步 2.1 拓扑图 数据库服务器 192.168....
  • Mysql主从同步 两个核心线程 IO / SQL 1>概述:实现数据自动同步的服务结构 2>原理: Master:启用binlog日志 Slave: a: slave_IO:复制master主机binlog日志中SQL命令到本机relay-log文件 b:slave_SQL:执行本机...
  • mysql主从同步架构设计方案,可以配置应用最广泛的一主拖多从,也可以多主多从,方案中有监控方案,7 * 24 小时自动监控。
  • 启动mysql主从同步功能主实例搭建运行mysql主实例在mysql的配置文件夹/mydata/mysql-master/conf中创建一个配置文件my.cnf修改完配置后重启实例:进入mysql-master容器中:在容器中使用mysql的登录命令连接到客户端...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 88,195
精华内容 35,278
关键字:

mysql主从同步

mysql 订阅