索取营销诊断书
021-80394720QQ
    您的位置：上海毫米网络优化公司 > 网站优化分享 >
    相关推荐recommended
    Mysql之主从复制
    作者：mmseoamin日期：2024-02-04

    文章目录

    • 一.Mysql主从复制介绍
      • 1.Mysql主从复制原理
      • 2.Mysql的复制类型
      • 3.Mysql主从复制的工作过程
      • 二.搭建 Mysql主从复制
        • 1.首先关闭防火墙
        • 2.Mysql主从服务器时间同步
        • 3.主服务器
        • 4.从服务器
        • 三.验证主从复制效果
          • 1.主服务器
          • 2.从服务器
          • 四.实验中会发生的问题及解决方案
            • 1.实验中可能发生的问题（ IO 和 SQL 线程是NO状态情况）
            • 2.MySQL主从复制延迟
            • 3.解决方案

              一.Mysql主从复制介绍

              1.Mysql主从复制原理

              ① 基于二进制文件实现

              2.Mysql的复制类型

              ① 基于语句的复制（TSATEMENT,Mysql的默认类型）

              ② 基于行的复制（ROW）

              ③ 混合类型的复制（MIXED）

              3.Mysql主从复制的工作过程

              ① Master节点将数据的改变记录成二进制日志（bin log），当Master上的数据发生改变时，则将其改变写入二进制日志中

              ② slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O线程请求 Master的二进制事件

              ③ 同时Master节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至slave节点本地的中继日志（Relay log）中，slave节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，即解析成 sql 语句逐一执行，使得其数据和 Master节点的保持一致，最后I/O线程和SQL线程将进入睡眠状态，等待下一次被唤醒

              二.搭建 Mysql主从复制

              1.首先关闭防火墙

              systemctl stop firewalld
systemctl disable firewalld
setenforce 0

              2.Mysql主从服务器时间同步

              ① 主服务器设置

              yum install ntp -y
vim /etc/ntp.conf
--末尾添加--
  server 127.127.247.0							#设置本地是时钟源，注意修改网段
  fudge 127.127.247.0 stratum 8				#设置时间层级为8（限制在15内）
service ntpd start

              [root@www ~]# yum install ntp -y
已加载插件：fastestmirror, langpacks
file:///mnt/repodata/repomd.xml: [Errno 14] curl#37 - "Couldn't open file /mnt/repodata/repomd.xml"
正在尝试其它镜像。
Loading mirror speeds from cached hostfile
软件包 ntp-4.2.6p5-25.el7.centos.2.x86_64 已安装并且是最新版本
无须任何处理
[root@www ~]# vim /etc/ntp.conf
server 127.127.247.0							
  fudge 127.127.247.0 stratum 8	
[root@www ~]#  service ntpd start

              ②从服务器设置

              yum install ntp ntpdate -y
service ntpd start
/usr/sbin/ntpdate 192.168.247.131				#进行时间同步
crontab -e
  */30 * * * * /usr/sbin/ntpdate 192.168.247.131

              [root@localhost yum.repos.d]# yum install ntp ntpdate -y
已加载插件：fastestmirror, langpacks
Loading mirror speeds from cached hostfile
软件包 ntp-4.2.6p5-25.el7.centos.2.x86_64 已安装并且是最新版本
软件包 ntpdate-4.2.6p5-25.el7.centos.2.x86_64 已安装并且是最新版本
无须任何处理
[root@localhost yum.repos.d]# service ntpd start
Redirecting to /bin/systemctl start ntpd.service
[root@localhost yum.repos.d]# /usr/sbin/ntpdate 192.168.247.131
25 Jun 19:28:22 ntpdate[35364]: the NTP socket is in use, exiting
[root@localhost yum.repos.d]# crontab -e
no crontab for root - using an empty one
crontab: installing new crontab
  */30 * * * * /usr/sbin/ntpdate 192.168.247.131

              3.主服务器

              ① mysql配置文件

              vim /etc/my.cnf
  server-id=11
  log-bin=mysql-bin			#添加，主服务器开启二进制日志
  binlog_format=mixed

              [root@www ~]# vim /etc/my.cnf
  server-id=11
  log-bin=mysql-bin			
  binlog_format=mixed

              ② 选配项

               vim /etc/my.cnf
  expire_logs_days=7			#设置二进制日志文件过期时间，默认值为0，表示logs不过期
  max_binlog_size=500M		#设置二进制日志限制大小，如果超出给定值，日志就会发生滚动，默认值是1GB
  skip_slave_start=1			#阻止从库崩溃后自动启动复制，崩溃后再自动复制可能会导致数据不一致的
  innodb_flush_logs_at_trx_commit=1     #双1设置
  sync_binlog=1                         #双1设置

              ③ 实验操作

              systemctl restart mysqld
mysql -u root -pabc123
grant replication slave on *.* to 'myslave'@'192.168.80.%' identified by '123456   #给从服务器授权
flush privileges;
show master status;

              [root@www ~]# systemctl restart mysqld
[root@www ~]# mysql -u root -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.41-log Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
mysql> grant replication slave on *.* to 'myslave'@'192.168.247.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |      604 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

              4.从服务器

              ① mysql配置文件

              vim /etc/my.cnf
  server-id = 22							#修改，注意id与Master的不同，两个slave的id也要不同
  relay-log=relay-log-bin					#开启中继日志，从主服务器上同步日志文件记录到本地
  relay-log-index=relay-log-bin.index		#定义中继日志文件的位置和名称，一般和relay-log在同一目录

              [root@localhost yum.repos.d]# vim /etc/my.cnf
  server-id = 22							
  relay-log=relay-log-bin					
  relay-log-index=relay-log-bin.index

              ② 选配项

              innodb_buffer_pool_size=2048M		#用于缓存数据和索引的内存大小，让更多数据读写内存中完成，减少磁盘操作，可设置为服务器总可用内存的 70-80%
sync_binlog=0						#MySQL不做任何强制性的磁盘刷新指令，而是依赖操作系统来刷新数据到磁盘
innodb_flush_log_at_trx_commit=2	#每次事务log buffer会写入log file，但一秒一次刷新到磁盘
log-slave-updates=0					#slave 从 master 复制的数据会写入二进制日志文件里，从库做为其他从库的主库时设置为 1
relay_log_recovery=1				#当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log， 并且重新从 master 上获取日志，这样就保证了 relay-log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时， 可在 slave 从库上开启该功能，建议开启。

              ③ 实验操作

              systemctl restart mysqld
mysql -u root -pabc123
change master to master_host='192.168.80.10',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=604;		#配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致
start slave;						#启动同步，如有报错执行 reset slave;
show slave status\G					#查看 slave 状态
slave_IO_Running: Yes			#负责与主机的io通信，Yes代表同步正常
slave_SQL_Running: Yes			#负责自己的slave mysql进程，Yes代表同步正常

              [root@localhost yum.repos.d]# systemctl restart mysqld
[root@localhost yum.repos.d]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.41 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to 
    -> master_host='192.168.247.131',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=604;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.247.131
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 604
               Relay_Log_File: relay-log-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
       ......

              三.验证主从复制效果

              1.主服务器

              create database db_test;

              mysql>  create database db_test;
Query OK, 1 row affected (0.01 sec)

              2.从服务器

              show databases;

              mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

              四.实验中会发生的问题及解决方案

              1.实验中可能发生的问题（ IO 和 SQL 线程是NO状态情况）

              ① 网络不通

              ② my.cnf配置有问题

              ③ 密码、file文件名、pos偏移量不对

              ④ 防火墙没有关闭

              2.MySQL主从复制延迟

              ① master服务器高并发，形成大量事务

              ② 网络延迟

              ③ 主从硬件设备导致：cpu主频、内存io、硬盘io

              ④ 是同步复制、而不是异步复制

              3.解决方案

              ① 从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作

              ② 从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o方面性

              ③ 从库使用SSD磁盘

              ④ 网络优化，避免跨机房实现同步

               

    上一篇：基于微信小程序餐厅酒吧KTV预约订座系统设计与实现(PHP后台)可行性分析

    下一篇：PostgreSQL命令行工具psql常用命令，查询，导出等

    网站优化运营
    整站seo优化
    网络营销运营
    成功案例
    品牌创意网站
    营销型网站
    小程序
    微信小程序
    微信公众号
    百度熊掌号
    网站优化分享
    关于我们
    公司概况
    公司新闻
    公司文化
    结构体系
    企业资质
    加入我们
    关注我们
    扫描二维码
    获取更多信息
    精彩一手掌握
    Copyright © 2014-2019 seo,上海网站优化_网络制作_网站seo排名_网站建设推广_上海网络公司 版权所有 沪ICP备19022315号