您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

Mysql数据库实现主从同步,看这一篇就够了

时间:2020-12-18 10:03:44  来源:  作者:

环境介绍:

centos 7.5

MySQL 5.7.29

 Mysql主服务器:192.168.2.128

 Mysql从服务器:192.168.2.129

Mysql主从同步原理:

 当master服务器上的数据发生改变时(增、删、改),则将其改变写入二进制binlog日志中;slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变,则开启一个I/O 线程请求master二进制事件,同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至从库本地的中继日志中,从库(从节点)将启动SQL线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致,最后IO线程和SQL线程将进入睡眠状态,等待下一次被唤醒。

注意几点:

 1.master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)。

 2.slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和 master数据保持一致了。

 3.Mysql主从复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。

 4.Mysql主从复制最好确保master和slave服务器上的Mysql版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本)

 5.master和slave两节点间时间需同步。

Mysql数据库实现主从同步,看这一篇就够了

 


如上图所示:

 

  Mysql复制过程的第一部分就是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从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重放其中的事件而更新slave的数据,使其与master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。

  此外,在master中也有一个工作线程:和其它MySQL的连接一样,slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的,也就是说master上的并行更新操作不能在slave上并行操作。

构建主从同步:

主从同步介绍

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

  -主服务器: 接受客户端访问连接

  -从服务器: 自动同步主服务器数据

  • Master(主库)
      -启用binlog日志
  • Slave(从库)
      `-Slave_IO线程:复制master主机binlog日志文件里的SQL命令到本机的relay-log文件里。`
      `-Slave_SQL线程:执行本机relay-log文件里的SQL语句,实现与Master数据一致。`

构建思路

  • 配置主库
      -启用binlog日志、授权用户、查看 binlog日志信息
  • 配置从服务器
      -`确保与主服务器数据一致。`
      -`设置server_id、指定主库信息、启动slave程序`

配置Mysql主服务器

1.修改主配置文件

  -启用binlog日志并重启服务

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=db128      //启用binlog日志,指定日志名以db128开头
server_id=128     //指定服务器ID号
...

[root@localhost ~]# systemctl restart mysqld

[root@localhost ~]# ls /var/lib/mysql/db128.*
/var/lib/mysql/db128.000001  /var/lib/mysql/db128.index

2.授权用户

 -用户权限为 replication slave

 -用户名自定义

 -客户端地址允许从库连接

 -密码自定义

注意:
replication slave权限代表: 允许slave主机通过此用户连接master以便建立主从复制关系。

[root@localhost ~]# mysql -uroot -p123qqq...A
...
Server version: 5.7.29-log MySQL Community Server (GPL)
...
mysql> grant replication slave on *.* to mysqluser@"%" identified by "123qqq...A";
  //为主数据库授权用户mysqluser,权限为replication、slave,允许客户端地址为所有主机,允许访问所有库所有表*.*,授权用户密码为123qqq...A

3.查看日志信息

[root@localhost ~]# mysql -uroot -p123qqq...A
...
Server version: 5.7.29-log MySQL Community Server (GPL)
...
mysql> show master status;  //查看binlog记录日志信息的偏移量position
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| db128.000001 |   704787 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+

配置从服务器

1.修改主配置文件

 -指定server_id,不允许与主库的server_id值相同

[root@test2 ~]# vim /etc/my.cnf
[mysqld]
server_id=129
...
[root@test2 ~]# systemctl restart mysqld

2.确保与主库数据一致

 -在主库上备份数据,备份文件拷贝给从库

 -在从库上使用备份文件恢复数据

 -从库查看备份数据对应的binlog日志信息

主数据库操作:

--master-data: 在备份文件中添加这次备份的数据对应的binlog日志名以及备份后数据的节点编号(偏移量),以便从库同步数据时,可以知道从哪个节点开始同步数据,保证主库与从库数据完全一致.

[root@localhost ~]# mysqldump -uroot -p123qqq...A --master-data test > /root/test.sql
 //在主库上备份数据库test,并且记录当前备份数据对应的binlog日志信息,备份文件名为test.sql

[root@localhost ~]# ls test.sql
test.sql

[root@localhost ~]# scp test.sql root@192.168.2.129:/root/

从数据库操作:

[root@test2 ~]# ls test.sql
test.sql

[root@test2 ~]# mysql -uroot -p123qqq...A;
mysql> create database test;   //从库上必须有需要恢复的库,因为要恢复test库,所以先创建空库test

[root@test2 ~]# mysql -uroot -p123qqq...A test < /root/test.sql

[root@test2 ~]# mysql -uroot -p123qqq...A;
mysql> use test;
+----------------+
| Tables_in_test |
+----------------+
| lss            |
| money          |
+----------------+

3.指定主库信息

命令格式:
  change master to
  master_host="主库IP地址",
  master_user="用户名",
  master_password="密码",
  master_log_file="binlog日志文件名",
  master_log_poss=偏移量;

主数据库查看binlog日志名及偏移量:

[root@localhost ~]# mysql -uroot -p123qqq...A
...
mysql> show master status;  //主库查看binlog记录日志信息的日志名及偏移量
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| db128.000001 |  2261338 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+

从库指定主库信息:

[root@test2 ~]# mysql -uroot -p123qqq...A;
...
mysql> show slave status;
Empty set (0.00 sec)

mysql> change master to
    -> master_host="192.168.2.128",    //指定主库IP地址
    -> master_user="mysqluser",       //主库授权用户
    -> master_password="123qqq...A",    //授权用户的密码
    -> master_log_file="db128.000001",   //主库binlog日志文件名
    -> master_log_pos=2261338;       //备份文件的日志偏移量
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;      //启动slave进程

注意:

  1.指定的master信息会自动保存到/var/lib/mysql/master.info文件中。

  2.若要更改指定的主库信息,需先执行stop slave,修改完成后执行start slave。

4.查看slave状态

  -确定IO线程、SQL线程都是Yes状态

Mysql数据库实现主从同步,看这一篇就够了

 


报错解决:

 

 UUID问题报错解决:Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs…

mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.128    //主库IP地址
                  Master_User: mysqluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db128.000002
          Read_Master_Log_Pos: 452757169
               Relay_Log_File: test2-relay-bin.000002
                Relay_Log_Pos: 3205508
        Relay_Master_Log_File: db128.000002
             Slave_IO_Running: Yes    //IO线程已运行
            Slave_SQL_Running: Yes    //SQL线程已运行
              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: 452757169
              Relay_Log_Space: 3205715
              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: 128
                  Master_UUID: e46c9961-5780-11ea-bf2f-000c128a8b6b
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

5.相关文件

  -存放在从库数据库目录下

master.info                  //主库信息

relay-log.info               //中继日志信息

主机名-relay-bin.xxxx         //中继日志

主机名-relay-bin.index       //索引文件

查看从库服务器数据库目录下配置从库时生成的4种文件

[root@test2 ~]# ls /var/lib/mysql
...
test2-relay-bin.index   master.info    relay-log.info      
test2-relay-bin.000001  test2-relay-bin.000002

验证主从同步效果

主数据库操作:

 在主数据库服务器中创建一个新表test并赋值,再去从服务器上查看数据是否同步。

[root@localhost ~]# mysql -uroot -p123qqq...A
mysql: [Warning] Using a password on the comm
...
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| zabbix             |
+--------------------+

mysql> use test;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| money          |
+----------------+

mysql> create table test(name varchar(25),city varchar(30),age int);

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| money          |
| test           |
+----------------+

mysql> insert into test.test values("mysql","china",11);

mysql> select * from test.test;
+-------+-------+------+
| name  | city  | age  |
+-------+-------+------+
| mysql | china |   11 |
+-------+-------+------+

从数据库操作:

 查看主服务器上新增的数据是否已经同步到从服务器上。

[root@test2 ~]# mysql -uroot -p123qqq...A
...
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| zabbix             |
+--------------------+

mysql> use test;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| money          |
| test           |
+----------------+

mysql> select * from test.test;
+-------+-------+------+
| name  | city  | age  |
+-------+-------+------+
| mysql | china |   11 |
+-------+-------+------+

 可以看到我们在主数据库服务器上新增的数据已经成功同步到从服务器上了。

延伸:

MySql主从同步的延迟问题(如何产生):

 主库针对写操作,顺序写binlog日志,从库单线程去主库顺序读”写操作的binlog”,从库取到binlog在本地原样执行(随机写),来保证主从数据逻辑上一致。mysql的主从复制都是单线程的操作,主库对所有增、删、改产生binlog日志,binlog是顺序写,所以效率很高,slave的Slave_IO_Running线程到主库取日志,效率比较高,下一步,问题来了,slave的Slave_SQL_Running线程将主库的增、删、改操作在slave实施。增、删、改的IO操作是随机的,不是顺序的,成本高很多,还可能与slave上的其他查询操作产生lock争用,由于Slave_SQL_Running也是单线程的,所以一个增、删、改操作卡住了,需要执行10分钟,那么所有之后的增、删、改操作会等待这个增、删、改操作执行完才会继续执行,这就导致了延时。(主库是多进程,从库单进程(回放relaylog),所以在高并发时,会出现延迟。)

MySQL主从同步延迟怎么解决?

 把主从同步配置为异步模式 ,保证至少有1台数据库服务器与主服务器数据一致。

Mysql数据库实现主从同步,看这一篇就够了

 

如果你觉得这篇文章还不错,就请动动你的发财手为本文点赞-评论-转发吧,因为这将是我持续输出更多优质文章的最强动力,谢谢!



Tags:主从同步   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
目录 一、背景 二、步骤 0.理论支持 1、获取数据 2、结果 3、分析数据并评估大小 三、关于repl-backlog-size 一、背景 repl-backlog-size控制这个环形缓冲区. ​ 主从断...【详细内容】
2021-11-05  Tags: 主从同步  点击:(41)  评论:(0)  加入收藏
大型网站为了软解大量的并发访问,除了在网站实现分布式负载均衡,远远不够。到了数据业务层、数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器来处理如此多的数据库...【详细内容】
2021-06-09  Tags: 主从同步  点击:(77)  评论:(0)  加入收藏
标签:监控mysql主从同步状态是否异常阶段1:开发一个守护进程脚本每30秒实现检测一次。阶段2:如果同步出现如下错误号(1158,1159,1008,1007,1062),请跳过错误阶段3:请使用数组技术...【详细内容】
2021-05-19  Tags: 主从同步  点击:(153)  评论:(0)  加入收藏
在企业的Liunx运维中,经常需要应用到各种shell脚本,比如Mysql数据库备份Shell脚本和Mysql主从同步监控Shell脚本等等常用企业运维shell脚本。。下面分享两例,希望对大家有帮助...【详细内容】
2020-12-18  Tags: 主从同步  点击:(195)  评论:(0)  加入收藏
环境介绍:&emsp;CentOS 7.5&emsp;Mysql 5.7.29&emsp;Mysql主服务器:192.168.2.128&emsp;Mysql从服务器:192.168.2.129Mysql主从同步原理:&emsp;当master服务器上的数据发生改变...【详细内容】
2020-12-18  Tags: 主从同步  点击:(139)  评论:(0)  加入收藏
随着2013年5月17日,阿里集团最后一台IBM小机在支付宝下线后,毫无疑问,MySQL 已经是当今互联网公司使用最多的数据库了,Mysql开源免费,而且可以很方便的横向扩展来支撑高并发的流...【详细内容】
2020-04-14  Tags: 主从同步  点击:(94)  评论:(0)  加入收藏
主从同步意义?主从同步使得数据可以从一个数据库服务器复制到其他服务器上,在复制数据时,一个服务器充当主服务器(master),其余的服务器充当从服务器(slave)。因为复制是异步进行的,...【详细内容】
2020-04-01  Tags: 主从同步  点击:(55)  评论:(0)  加入收藏
高并发这个阶段,肯定是需要做读写分离的,啥意思?因为实际上大部分的互联网公司,一些网站,或者是 app,其实都是读多写少。所以针对这个情况,就是写一个主库,但是主库挂多个从库,然后从...【详细内容】
2019-12-26  Tags: 主从同步  点击:(97)  评论:(0)  加入收藏
准备两台服务器并安装mysql5.7master服务器192.168.254.132slave服务器192.168.254.136确保两台服务器安装的mysql版本一致,本测试使用的是mysql5.7,登录mysql数据库,使用如下...【详细内容】
2019-10-11  Tags: 主从同步  点击:(201)  评论:(0)  加入收藏
一、主从数据库的区别从数据库(Slave)是主数据库的备份,当主数据库(Master)变化时从数据库要更新,这些数据库软件可以设计更新周期。这是提高信息安全的手段。主从数据库服务...【详细内容】
2019-07-03  Tags: 主从同步  点击:(347)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(20)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条