mysql主从复制、双主双从、读写分离以及分库分表

2024-05-02 10:08
文章标签 分离 mysql 读写 主从复制 database 分库 分表 双主双

本文主要是介绍mysql主从复制、双主双从、读写分离以及分库分表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

这边已安装好mysql版本为5.7、jdk等

一、环境准备
1.1 准备2台服务器，一台为主一台为从
二、一主一丛
2.1 在主服务器节点上修改/etc/my.cnf的文件增加如下命令
**注意：**在我的环境中必须把增加的命令放在mysqld下方、否则会报异常、原因不详！！
在这里插入图片描述

#mysql服务唯一id，不同的mysql服务必须拥有全局唯一的id
server-id=1   //这里有个小插曲！后面描述
#启动二进制日志
log-bin=mysql-bin
#设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information-schema
#设置需要复制的数据库
binlog-do-db=msb
#设置binlog的格式
binlog_format=statement

2.2 在从服务器节点上修改/etc/my.cnf文件

#服务器唯一id
server-id=2
#启动中继日志
relay-log=mysql-relay

2.3 重新启动mysql服务 service mysql restart;

2.4 在主服务器上创建账户并授权slave(从服务器)

grant replication slave on *.* to 'root'@'%' identified by '666666';
--在进行授权的时候，如果提示密码的问题，把密码验证取消
set global validate_password_policy=0;
set global validate_password_length=1;

2.5 查看master的状态

show master status

在这里插入图片描述

2.6 在从服务器上配置需要复制的主机

CHANGE MASTER TO MASTER_HOST='10.0.0.130',master_port=33306,MASTER_USER='root',
MASTER_PASSWORD='666666',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=522;
参数介绍
master_host：主数据库ip
master_port：主数据库端口
master_user：连接主数据的用户
master_password：连接主数据的密码
master_log_file：主数据的日志文件
master_log_pos：主数据的日志文件，当前记录的位置

2.7 启动从服务器复制功能

start slave;

2.8 查看从服务器状态

show slave status\G

在这里插入图片描述
果然一切注定不会那么顺利、出问题了。。。。我这完全是照着文档的步骤走的怎么会出问题呢、心里狠狠地说了句 卧槽。。
这个Slave_IO_Running: No是怎么回事，在mysql安装目录下

show variables like 'datadir';   登陆mysql输入这个命令可以获取到安装目录

查看到错误日志是这样的
在这里插入图片描述
英文不太好、不过大概可以猜到是id重复了、可是这怎么会重复呢？当时主服务器server-id=1、从服务器server-id=2明明是这样配置的咋会重复呢？马上在百度上搜了下找到了这篇文章
排查错误
1、分别在主从数据库中执行如下sql, 并必读两边的server_id 是否一样，一样则修改my.cnf设置成不一样：

    show variables like '%server_id%';

2、分别在主从数据库中执行如下sql, 并必读两边的server_uuid 是否一样，如果一样则删除从数据库的数据目录下的auto.cnf：

  show variables like '%uuid%';

3、确认主从数据库的数据host是否一样，如果主从数据库的host一样也会报这个错误.

经过排查果然主服务器和从服务器的server-id竟然是一样、原因my.cnf本身就有了server-id=1这个配置了、然后我在配置主服务器和从服务器时又给它加了一个所以导致重复，修改完毕后再次查看主服务器mysql状态

show master status 
如果状态发生改变从服务器配置时需要同步更改！

登陆从服务器mysql输入命令

reset slave;  //重置从服务器的mysqlCHANGE MASTER TO MASTER_HOST='10.0.0.130',master_port=33306,MASTER_USER='root',
MASTER_PASSWORD='666666',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=522;  start slave;  //启动从节点
show slave status\G   //查看状态

在这里插入图片描述
当执行完成之后，会看到两个关键的属性Slave_IO_Running，Slave_SQL_Running，当这两个属性都是yes的时候，表示主从复制已经准备好了，可以进行具体的操作了

三、一主一从验证
3.1下面我们通过实际的操作来验证主从复制是否完成

--在主服务器mysql创建数据库
create database msb;
--在msb上创建具体的表
create table mytbl(id int,name varchar(20));
--在主服务器mysql上插入数据
insert into mytbl values(1,'zhangsan');
--在从服务器mysql上验证发现数据已经同步成功，表示主从复制完成

注意如果从服务器存在相同的id且是主键则会插入失败并报错、此时需要重置从服务器进行全量更新！

··读写分离
安装mycat 进入conf目录配置server.xml
vi server.xml进行如下配置

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");- you may not use this file except in compliance with the License. - Youmay obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0- - Unless required by applicable law or agreed to in writing, software -distributed under the License is distributed on an "AS IS" BASIS, - WITHOUTWARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See theLicense for the specific language governing permissions and - limitationsunder the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/"><user name="root">  //mysql用户名<property name="password">666666</property> //mysql密码<property name="schemas">TESTDB</property>  //mycat逻辑库<property name="defaultSchema">TESTDB</property> //默认逻辑库</user></mycat:server>

配置schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/"><schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="test" dataNode="dn1" /></schema><dataNode name="dn1" dataHost="localhost1" database="msb" /><dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="hostM1" url="10.0.0.130:33306" user="root"password="666666">
<readHost host="hostS1" url="10.0.0.131:33306" user="root" password="666666"></readHost></writeHost></dataHost>
</mycat:schema>

配置完成后进入bin目录启动mycat

./mycat console            //表示启动mycat并在控制台打印日志

连接mycat

mysql -uroot -p666666 -P 9066 -h 10.0.0.130

插入一条数据测试读写分离

insert into testvalues(2,@@hostname);

进行读测试
在这里插入图片描述
发现每次读出来的数据都可能不一样、这说明数据是从不同的服务器均衡的读出来，因为上面在schema.xml中配置了 balance=“2”，这个参数表明：所有读操作都随机的在writehost，readhost上分发。

        1、balance=0 :不开启读写分离机制，所有读操作都发送到当前可用的writehost上2、balance=1:全部的readhost和stand by writehost参与select 语句的负载均衡，简单的说，当双主双从模式下，其他的节点都参与select语句的负载均衡3、balance=2:所有读操作都随机的在writehost，readhost上分发4、balance=3:所有读请求随机的分发到readhost执行，writehost不负担读压力

双主双从

在此架构中，可以让一台主机用来处理所有写请求，此时，它的从机和备机，以及备机的从机复制所有读请求，当主机宕机之后，另一台主机负责写请求，两台主机互为备机。

准备四台服务器，分别命名为node1(主)、node2(从)、node3(主)、node4(从)

  1. 下面开始搭建双主双从
    修改node1上的/etc/my.cnf文件
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=msb
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候， 有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1， 取值范围是1 .. 65535
auto-increment-increment=2   //不是必须的
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1   //不是必须的

2、修改node3上的/etc/my.cnf文件

#主服务器唯一ID
server-id=3
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=msb
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2

3、修改node2上的/etc/my.cnf文件

#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

4、修改node4上的/etc/my.cnf文件

#从服务器唯一ID
server-id=4
#启用中继日志
relay-log=mysql-relay

5、所有主机重新启动mysql服务

6、在两台主机node1,node3上授权同步命令

GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY '666666';
//这个地方*.* 在生产环境中应为实际的主机ip

7、查看两台主机的状态

show master status;

8、在node2上执行要复制的主机

CHANGE MASTER TO MASTER_HOST='10.0.0.130',MASTER_USER='root',MASTER_PASSWORD='666666',MASTER_PORT=33306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=522;

9、在node4上执行要复制的主机

CHANGE MASTER TO MASTER_HOST='10.0.0.132',MASTER_USER='root',MASTER_PASSWORD='666666',MASTER_PORT=33306,MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=522;

10、启动两个从机的slave并且查看状态，当看到两个参数都是yes的时候表示成功

start slave;
show slave status;

11、完成node1跟node3的相互复制

--在node1上执行
CHANGE MASTER TO MASTER_HOST='192.168.85.113',MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=442;
--开启slave
start slave
--查看状态
show slave status\G
--在node3上执行
CHANGE MASTER TO MASTER_HOST='192.168.85.111',MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=442;
--开启slave
start slave
--查看状态
show slave status\G

双主双从验证
在node1上执行如下sql语句：

create database msb;
create table mytb2(id int,name varchar(20));
insert into mytb values(1,'zhangsan');

发现在node2、node3、node4都同步了数据
在这里插入图片描述

当上述操作完成之后，我们可以验证mycat的读写分离，此时我们需要进行重新的配置，修改schema.xml文件。

	在当前mysql架构中，我们使用的是双主双从的架构，因此可以将balance设置为1除此之外我们需要注意，还需要了解一些参数：参数writeType，表示写操作发送到哪台机器，此参数有两个值可以进行设置：writeType=0:所有写操作都发送到配置的第一个writeHost，第一个挂了切换到还生存的第二个writeType=1:所有写操作都随机的发送到配置的writehost中，1.5之后废弃，需要注意的是：writehost重新启动之后以切换后的为准，切换记录在配置文件dnindex.properties中参数switchType:表示如何进行切换：switchType=1:默认值，自动切换switchType=-1:表示不自动切换switchType=2：基于mysql主从同步的状态决定是否切换

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/"><schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema><dataNode name="dn1" dataHost="localhost1" database="msb" /><dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="hostM1" url="10.0.0.130:33306" user="root"password="666666">
<readHost host="hostS1" url="10.0.0.131:33306" user="root" password="666666"></readHost></writeHost>
<writeHost host="hostM2" url="10.0.0.132:33306" user="root"password="666666"><readHost host="hostS2" url="10.0.0.133:33306" user="root" password="666666"></readHost></writeHost></dataHost>
</mycat:schema>

下面开始进行读写分离的验证

--插入以下语句，使数据不一致
insert into mytb2 values(4,@@hostname);
--通过查询mycat表中的数据，发现查询到的结果在node2,node3,node4之间切换，符合正常情况
select * from mytb2;
--停止node1的mysql服务
service mysql stop
--重新插入语句
insert into mytb2 values(5,@@hostname);
--开启node1的mysql服务
service mysql start
--执行相同的查询语句，此时发现在noede1，node2,node4之间切换，符合情况

通过上述的验证，我们可以得到一个结论，node1,node3互做备机，负责写的宕机切换，其他机器充作读请求的响应。

分库分表(数据切分)有时间再更新

这篇关于mysql主从复制、双主双从、读写分离以及分库分表的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/954001。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
异构存储（冷热数据分离）

异构存储（冷热数据分离）

异构存储主要解决不同的数据，存储在不同类型的硬盘中，达到最佳性能的问题。 异构存储Shell操作 （1）查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies （2）为指定路径（数据存储目录）设置指定的存储策略 hdfs storagepolicies -setStoragePo
阅读更多...
10. 文件的读写

10. 文件的读写

10.1 文本文件 操作文件三大类： ofstream:写操作ifstream:读操作fstream:读写操作 打开方式解释ios::in为了读文件而打开文件ios::out为了写文件而打开文件，如果当前文件存在则清空当前文件在写入ios::app追加方式写文件ios::trunc如果文件存在先删除，在创建ios::ate打开文件之后令读写位置移至文件尾端ios::binary二进制方式
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...
计算机毕业设计 大学志愿填报系统 Java+SpringBoot+Vue 前后端分离 文档报告 代码讲解 安装调试

计算机毕业设计 大学志愿填报系统 Java+SpringBoot+Vue 前后端分离 文档报告 代码讲解 安装调试

🍊作者：计算机编程-吉哥 🍊简介：专业从事JavaWeb程序开发，微信小程序开发，定制化项目、 源码、代码讲解、文档撰写、ppt制作。做自己喜欢的事，生活就是快乐的。 🍊心愿：点赞 👍 收藏 ⭐评论 📝 🍅 文末获取源码联系 👇🏻 精彩专栏推荐订阅 👇🏻 不然下次找不到哟~Java毕业设计项目~热门选题推荐《1000套》 目录 1.技术选型 2.开发工具 3.功能
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2