程序员经验分享-hwhale

一、Mysql主从复制（一主多从）搭建

2023-05-16

  1. 介绍

    在实际生产中，数据的重要性不言而喻如果我们的数据库只有一台服务器，那么很容易产生单点故障的问题，比如这台服务器访问压力过大而没有响应或者奔溃，那么服务就不可用了，再比如这台服务器的硬盘坏了，那么整个数据库的数据就全部丢失了，这是重大的安全事故.为了避免服务的不可用以及保障数据的安全可靠性，我们至少需要部署两台或两台以上服务器来存储数据库数据，也就是我们需要将数据复制多份部署在多台不同的服务器上，即使有一台服务器出现故障了，其他服务器依然可以继续提供服务.
    MySQL提供了主从复制功能以提高服务的可用性与数据的安全可靠性.主从复制是指服务器分为主服务器和从服务器，主服务器负责读和写，从服务器只负责读，主从复制也叫 master/slave，master是主，slave是从，但是并没有强制，也就是说从也可以写，主也可以读，只不过一般我们不这么做。
    主从复制可以实现对数据库备份和读写分离

  2. 架构

    一主多从

    image-20200801153104597

    双主多从

    image-20200801153045598
    二、主从复制原理
image-20200801153214555

  • 当 master 主服务器上的数据发生改变时，则将其改变写入二进制事件日志文件中

  • salve 从服务器会在一定时间间隔内对 master 主服务器上的二进制日志进行探测，探测其是否发生过改变，如果探测到 master 主服务器的二进制事件日志发生了改变，则开始一个 I/O Thread 请求 master 二进制事件日志

  • 同时 master 主服务器为每个 I/O Thread 启动一个dump Thread，用于向其发送二进制事件日志

  • slave 从服务器将接收到的二进制事件日志保存至自己本地的中继日志文件中

  • salve 从服务器将启动 SQL Thread 从中继日志中读取二进制日志，在本地重放，使得其数据和主服务器保持一致；

  • 最后 I/O Thread 和 SQL Thread 将进入睡眠状态，等待下一次被唤醒

    注意：主从复制的过程会有很小的延迟，基本没有影响

二、一主多从搭建

  1. 节点规划

    系统centos7.3

    master node02 192.168.147.111

    slave1 node03 192.168.147.112

    slave2 node04 192.168.147.113

  2. 软件下载

    mysql下载地址

  3. 解压到本地

    tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.24

  4. 在node01，node02,node03分别建立存放数据的目录

    cd /usr/local/mysql-5.7.24/
mkdir /usr/local/mysql-5.7.24/data

  5. 在node01,node02,node03执行数据库初始化

    cd /usr/local/mysql-5.7.24/bin
./mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.24 --datadir=/usr/local/mysql-5.7.24/data/3306 --user=mysql

    initialize-insecure 表示不生成MySQL数据库root用户的随机密码，即root密码为空

  6. 修改node01,node02,node03的my.cnf文件

    node01

    vi /usr/local/mysql-5.7.24/data/3306/my.cnf

[client]
port=3306
socket=/usr/local/mysql-5.7.24/data/3306/mysql.sock
default-character-set=utf8

[mysqld]
port=3306
socket=/usr/local/mysql-5.7.24/data/3306/mysql.sock
datadir=/usr/local/mysql-5.7.24/data/3306
log-error=/usr/local/mysql-5.7.24/data/3306/error.log
pid-file=/usr/local/mysql-5.7.24/data/3306/mysql.pid

character-set-server=utf8
lower_case_table_names=1
autocommit=1
log-bin=mysql-bin
server-id=1

    node02

    [client]
port=3306
socket=/usr/local/mysql-5.7.24/data/3306/mysql.sock
default-character-set=utf8

[mysqld]
port=3306
socket=/usr/local/mysql-5.7.24/data/3306/mysql.sock
datadir=/usr/local/mysql-5.7.24/data/3306
log-error=/usr/local/mysql-5.7.24/data/3306/error.log
pid-file=/usr/local/mysql-5.7.24/data/3306/mysql.pid

character-set-server=utf8
lower_case_table_names=1
autocommit=1
server-id=2

    node03

    [client]
port=3306
socket=/usr/local/mysql-5.7.24/data/3306/mysql.sock
default-character-set=utf8

[mysqld]
port=3306
socket=/usr/local/mysql-5.7.24/data/3306/mysql.sock
datadir=/usr/local/mysql-5.7.24/data/3306
log-error=/usr/local/mysql-5.7.24/data/3306/error.log
pid-file=/usr/local/mysql-5.7.24/data/3306/mysql.pid

character-set-server=utf8
lower_case_table_names=1
autocommit=1

server-id=3

  7. node01,ode02,node03切换到/usr/local/mysql-5.7.24/bin目录下，使用 msyqld_safe 命令指定配置文件并启动MySQL服务：

    ./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3306/my.cnf &

  8. 登录每个节点node01,node02,node03都执行

    方式一：使用用端口、主机登录（推荐使用该方式）

    ./mysql -uroot -p -P3306 -h127.0.0.1

    方式二：使用socket文件

    ./mysql -uroot -p -S /usr/local/mysql-5.7.24/data/3306/mysql.sock

    -p 是指定密码，如果没有密码则可以不写 -p，
    -S是指定sock文件，mysql.sock文件是服务器与本机客户端进行通信的ip与端口文件

  9. 修改密码node01,node02,node03都执行

    alter user 'root'@'localhost' identified by 'mysql@!QAZ';

  10. 授权远程访问（这样远程客户端才能访问）node01,node02,node03都执行

    grant all privileges on *.* to root@'%' identified by 'mysql@!QAZ';

    其中*.* 的第一个表示所有数据库名，第二个表示所有的数据库表
    root@’%’ 中的root表示用户名
    %表示所有ip地址，%也可以指定具体的ip地址，比如root@localhost，root@192.168.10.129

    刷新配置

    flush privileges;
三、主从复制配置

  1. 登录主节点node01，在主服务器上创建复制数据的账号并授权

    grant replication slave on *.* to 'copy'@'%' identified by 'mysql@!QAZ';
flush privileges;

    注意：该语句可完成授权、创建用户、修改密码操作

  2. 查看主服务器状态

    show master status;

    mysql主服务器默认初始值：

    image-20200802151909712

    如果不是初始值需要进行初始化

    reset master;

  3. 登录从节点node02,node03查看状态

    show slave status;

    初始状态：Empty set ，如果不是建议重置

    stop slave; 	#停止复制，相当于终止从服务器上的IO和SQL线程
reset slave;

  4. 设置从服务器的master

    在从服务器客户端执行node02,node03

    change master to master_host='192.168.147.111',master_user='copy',
master_port=3306,master_password='mysql@!QAZ',
master_log_file='mysql-bin.000001',master_log_pos=154;

  5. 在从机器上执行开始复制命令

    start slave;
四、验证

  1. 检查从服务器复制功能状态

    show slave status \G

如果 Slave_IO_Running 和 Slave_SQL_Running 均为 YES，则表示主从关系正常

image-20200802192849961

2.在主服务器上创建数据库、表、数据，然后在从服务器上查看是否已经复制

五、关闭集群
  1. 方式一：使用端口、主机关闭 （推荐）
 ./mysqladmin -uroot -p -P3306 -h127.0.0.1 shutdown
  1. 方式二:使用socket文件
 ./mysqladmin -uroot -p -S /usr/local/mysql-5.7.24/data/3307/mysql.sock shutdown
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

主从复制

一主多从

一、Mysql主从复制（一主多从）搭建 的相关文章

  • mysql语句中的*星号是什么意思？

    Ex mysql query SELECT FROM members WHERE id id 这意味着选择表中的所有列

  • C#：SQL 查询生成器类

    在哪里可以找到好的 SQL 查询构建器类 我只需要一个简单的类来构建 SQL 字符串 仅此而已 我需要它用于 C 和 MySql 我真的不需要像 Linq 或 NHibernate 这样的东西 谢谢 由于 Google 将我引导至此页面 我

  • 在docker中使用MySQL数据库设置aspnetcore

    我正在尝试设置一个 docker compose 文件 其中包含 asp net core mysql 数据库和 phpmyadmin 的容器 设置我的 mysql 服务器没有问题 我可以使用 phpmyadmin 访问它 我的 asp n

  • 查询中列的顺序重要吗？

    当从 MySQL 表中选择列时 与表中的顺序相比 选择列的顺序是否会影响性能 不考虑可能覆盖列的索引 例如 您有一个包含行 uid name bday 的表 并且有以下查询 SELECT uid name bday FROM table M

  • MYSQL - 查找最近的前一天

    我可以以某种方式 不使用 PHP 找到一周中最近的前一天日期吗 Like 最近的上一个星期二的日期是哪一天 CURDATE INTERVAL WEEKDAY CURDATE wday IF WEEKDAY CURDATE gt wday 0

  • 如何使用 MySQL 的 LOAD DATA LOCAL INFILE 在导入 CSV 时将字符串日期更改为 MySQL 日期格式

    我正在使用 MySQL 的 LOAD DATA LOCAL INFILE SQL 语句将数据从 CSV 文件加载到现有数据库表中 下面是一个 SQL 语句示例 LOAD DATA LOCAL INFILE file csv INTO TAB

  • PHP 绑定“bigint”数据类型（MySQLi 准备好的语句）

    studentId 57004542323382 companyOfferId 7 sql INSERT INTO studentPlacement companyOfferId studentId VALUES if stmt db gt

  • 截断 Mysql 表 Cron 作业？

    我在如何使用 cron 作业截断 Mysql 表时遇到了一些麻烦 无论我尝试什么 我似乎都无法让数据库清除表格 感谢您的帮助 mysql uderp example pexample hlocalhost Dexample e TRUNCA

  • Mysql 检索所有有限制的行

    我想检索特定用户的所有行 限制为 0 x 所以我只是想问是否有任何方法可以检索 mysql 中的所有行 而不调用返回 x 的 count id 的方法 而不重载现有函数 该函数在查询中根本没有限制 与我们的 string Relace 功能

  • 为什么我的 if 语句没有按我预期的方式工作？

    我正在尝试实现以下目标 我向我的 SQL 数据库询问使用SELECT FROM subjects 这样做之后我要求使用数组mysqli fetch assoc 在那之前一切都很好 现在的问题是 当我尝试在每个循环中修改 genero 的值

  • MySQL PHP邮政编码比较具体距离

    我试图找出比较一个邮政编码 用户提供的 和一大堆其他邮政编码 现在大约有 200 个邮政编码 之间的距离的最有效方法 相对于加载时间 但它会随着时间的推移而增加 我不需要任何精确的东西 只是在球场上 我下载了整个美国的邮政编码 csv 文件

  • MySQL MIN/MAX 所有行

    我有桌子Races与行ID Name and TotalCP 我选择分钟 TotalCP FROM Races 但是我想选择具有最小值的整行 我如何在单个查询中做到这一点 从聚合值获取整行的一般形式是 SELECT FROM Races W

  • 忽略重复条目并在 EF Core 中的 DbContext.SaveChanges() 上提交成功条目

    我有一个 ASP Net Core 2 2 Web API 在我的一个控制器操作中 我向 MySQL 数据库表添加了一堆行 我使用的是 Pomelo 例如 dbContext AddRange entities dbContext Save

  • 将古吉拉特语文本插入 MySQL 表会产生垃圾字符和不可读的文本

    我有三个 MySQL 表 我正在向其中插入古吉拉特语内容 当我插入两个表时 它们插入得很好并且可读 但在一个表中 它显示垃圾字符 不可读的文本 我怎样才能解决这个问题 MySQL 有每个表的字符集设置 http dev mysql com

  • MySQL 查询计算上个月

    我想计算上个月的订单总额 我收到了从当前日期获取当月数据的查询 SELECT SUM goods total AS Total Amount FROM orders WHERE order placed date gt date sub c

  • 将第三个表链接到多对多关联中的桥接表

    设计这个数据库的正确方法是什么 这是我设置表格的方式 我在名为 教师 的表和名为 仪器 的表之间存在多对多关系 然后我有一个连接两者的桥接表 我想将另一个表与 BRIDGE 表关联起来 意思是乐器 老师的组合 该表有 3 行 指定老师可以教

  • gem install mysql：无法构建 gem 本机扩展 (Mac Lion)

    我为 Mac OS X Lion 安装了 MySQL 5 5 27 来自 dmg 现在我尝试安装 mysql gem gem install mysql Building native extensions This could take

  • 如何使用 Perl 更改 mysql 密码

    我需要使用 Perl 脚本更改一些 mysql 密码 以下内容在更改数据库条目时有效 但是当我针对 mysql 用户更改修改它时 它将它们重置为空白密码 最后 刷新权限 也很好 但我还没有找到方法 usr bin perl use DBI

  • Java JDBC：更改表

    我希望对此表进行以下修改 添加 状态列 varchar 20 日期列 时间戳 我不确定该怎么做 String createTable Create table aircraft aircraftNumber int airLineCompa

  • 在 MySQL 中存储表情符号的编码问题：如何使用 Prisma ORM 在 NodeJS 中定义字符排序规则？

    亲爱的 Nodejs 专家和数据库专家 我们在 MySQL 数据库中存储表情符号和其他特殊字符时遇到问题 我们使用 Prisma 得到一个错误 这是我们使用的 ORM 参数无法从排序规则 utf8 general ci 转换为 utf8mb

随机推荐

热门标签