mysql-主从同步与读写分离

一、mysql主从同步原理

mysql主从是用于数据灾备。也可以缓解服务器压力(读写分离),即为主数据库服务器增加一个备服务器,
两个服务器之间通过mysql主从复制进行同步,这样一台服务器有问题的情况下可以切换到另一台服务器继续使用。
如何想实现mysql主从自动切换,需要增加高可用,比如MHA

注意:
1、主库挂了,从库可以继续对外提供服务(需要研发把连接主库的ip改为从库)
2、主库起来后主从环境会自动恢复,如果从库写入了数据不会同步到主库,也不会影响主从同步但是会导致主从数据不一致,所以从库最好只读。
3、主从同步是主库往从库单项同步的。
4、如果想把从库升级为主库,需要再配置一遍主从同步的过程并把旧的从库的同步功能关闭stop slave(实测)
5、主从和主备其实是一个东西,唯一区别就是主备环境中备机不对外提供服务只做备份, 主从环境中主挂了备机可以对外提供读或读写服务。
6、当后期出现不同步的时候只需要锁主库并记住master当前pos值,然后从库再执行一遍同步命令再解锁从库即可,实在不行再备份/还原数据。

在这里插入图片描述
从库生成两个线程,一个I/O线程,一个SQL线程;
i/o线程去请求主库 的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中;
主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog;
SQL 线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致;

主从同步原理:

主库写入数据到binlog日志,从库通过IO线程将主库的binlog日志读取到从库的中级日志(relaylog)中,从库再通过自己的SQL线程将中级日志中的数据写入到数据库,来完成主从同步。

在从库使用show slave status;查看Read_Master_Log_Pos: 和 Exec_Master_Log_Pos: 的值如果不一样说明数据同步不一致
Seconds_Behind_Master: 0 #和主库比同步延迟的秒数

二、配置mysql主库

  1. MySQL主服务器配置
1.编辑配置文件/etc/my.cnf                       #其余参数保持默认
[mysqld]                                        #必须在【mysqld】这个模块下
server-id=1                                     #主从服务器的server-id 不能相同
log-bin=mysql-bin                               #开启二进制同步日志
expire_logs_days = 7                      # binlog保持7天
#binlog-do-db=test2                             #设置需要同步的数据库
binlog-ignore-db=performacen_schema,mysql       #设置不需要同步的数据库
------------------------
mysql5.7.8以后expire_logs_days已经被弃用,改为binlog_expire_logs_seconds = 30240,单位秒

2.建立用户

mysql>  grant replication slave on *.* to rep@'192.168.1.121' identified by 'Clouddeep@8890';
允许rep用户通过192.168.1.121服务器登录并读取本地mysql的权限
mysql>flush privileges;           #刷新权限

 // 可在Slave上做连接测试: mysql -h 192.168.1.200  -u  wenqiang  –p

mysql8改为这样了
mysql> create user 'test'@'172.17.0.3' identified by '123456';  # 创建用户
ERROR 1396 (HY000): Operation CREATE USER failed for    # 这个报错是之前这个用户创建过可能之前的用户没删干净导致的
mysql> grant replication slave on *.* to 'test'@'172.17.0.3';  # 授权用户主从复制权限
mysql> alter user 'slave'@'172.17.0.3' identified with mysql_native_password by '123456';  # 把加密方式也改了防止后期出现Last_IO_Errno: 2061问题。
mysql> grant all privileges on *.* to 'root'@'192.168.1.%'; # 授权所有与权限

3.锁主库表(锁定数据库中所有表)

mysql> FLUSH TABLES WITH READ LOCK;
 mysql> flush logs;

4.显示主库信息(记下来)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      371 | test2        | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5.另开一个终端,备份(导出)需要复制的数据库,这一步是为了防止两边数据不一致导致同步不成功。同步时必须保证两边数据一致。

# mysqldump -u root --opt --default-character-set=utf8 -p --events --all-databases >/server/backup/master.sql
#用mysqldump命令导出字符集为(utf8)的所有数据库,导出到目录/tmp下的master.sql


主从同步时主库为什么要锁库?
同步数据时mysql-bin.xxxx和Position的值必须和当前数据量能对的上,mysql-bin.xxx和Postion相当于当前数据量内容的标签,所以同步之前先锁主库并记住mysql-bin和postition值以及备份出与之对应的数据量内容,然后再把备份的数据还原到从库中,从库再根据当前的数据量内容和对应的2个标签(mysql-bin.xxx和Position)进行同步。
比如:
mysql-bin.00007 132   10M数据量内容
mysql-bin.00028 155   30M数据量内容

6、解锁主库表,此时主库可以运行了

mysql> UNLOCK TABLES;

三、MySQL从服务器配置

1.、将主数据库的master.sql传输到从服务器的 /tmp目录下

# yum -y install openssh-clients                      #安装scp
 #scp -pr /tmp/master.sql 192.168.1.121:/tmp

2、导入数据库

# mysql  -u root  -p --default-character-set=utf8   < /server/backup/master.sql

3、编辑从库配置

#vi /etc/my.cnf(其余参数保持默认)

[mysqld]                                                 #必须在【mysqld】这个模块下
server-id=2                                              #不能与master的id相同
log-bin=mysql-bin                                        #开启二进制日志

/etc/init.d/mysql restat                            重启从库

4、在SLAVE上设置同步

mysql> stop slave;                                       #关闭slave 同步服务
mysql> reset slave all;                                  #清除之前同步的残留数据
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.200',MASTER_USER='rep',MASTER_PASSWORD='Clouddeep@8890',MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=371;
-----------------------
从库通过用户rep去同步192.168.1.200上的数据,并根据mysql-bin.000007和position值完成同步。

5、启动SLAVE服务

mysql>start slave;

6、查看SLAVE状态

mysql> SHOW SLAVE STATUS\G;
其中 Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes",表明 Slave 的 I/O 和 SQL 线程都在正常运行

7、如果需要的话设置从库对普通用户只读(不影响主从同步)

# 设置只读模式
mysql> set global read_only=1;    # 1只读,0读写,对super用户无效
mysql> flush privileges;

# 也可以通过配置文件添加
[mysqld]
read_only=ON

# 查看是否开启只读
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+

# 查看哪些用户具有super权限
mysql> select user,host,Super_priv from mysql.user;

# mysql5创建普通用户
mysql> grant select,insert,update,delete on s18.* to 'test'@'127.0.0.1' identifi
ed by '123456';
# mysql8创建普通用户
create user "wen"@"%" identified by '123456';  # 创建用户
grant insert,update on *.* to "wen"@"%";       # 授权
alter user "wenqiang2"@"%"  identified with mysql_native_password by '123456';  # 修改加密方式

# 查看用户权限
mysql> show grants for "wen"@"%";
+----------------------------------------------------------------+
| Grants for wen@%                                         |
+----------------------------------------------------------------+
| GRANT INSERT, UPDATE ON *.* TO `wen`@`%` |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

一主多从,多个从的server-id 不能设置一样

状态参数解释:

2. Master_Host: 10.1.8.62
       Master_User: rep_user
       Master_Port: 3306
       这3条信息,显示了slave连接master时,使用的master的主机---master_host、连接master用的用户---master_user、连接master的端口---master_port。
   3. Connect_Retry: 10
      连接中断后,重新尝试连接的时间间隔。默认值是60秒。
   4. Master_Log_File: binlog.000026
       Read_Master_Log_Pos: 446
       这两条信息,显示了与master相关的日志的信息。master_log_file:当前I/O线程正在读取的master 二进制日志的文件名;read_master_log_pos:当前I/O线程正在读取的二进制日志的位置(主库Position值)
   5.  Relay_Log_File: relay.000008
        Relay_Log_Pos: 589
        Relay_Master_Log_File: binlog.000026
        这3条信息,显示了与relay log相关的信息。relay_log_file:当前SQL线程正在读取并执行的relay log的文件名;relay_log_pos:当前SQL线程正在读取并执行的relay log文件的位置;relay_master_log_file:master 二进制日志的文件名。该文件包含当前SQL执行的事物
   6.  Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
        显示了当前I/O线程和SQL线程的状态
   7.  Replicate_Do_DB: 
        Replicate_Ignore_DB: 
        Replicate_Do_Table: 
        Replicate_Ignore_Table: 
        Replicate_Wild_Do_Table: 
        Replicate_Wild_Ignore_Table:
        这部分显示的是关于复制DB和table的信息。
   8.  Last_Errno: 0
        Last_Error: 
        laster_errno和laster_error是 Last_SQL_Errno和Last_SQL_Error的同义词。
  9.  Skip_Counter: 0
       系统参数sql_slave_skip_counter的值。sql_slave_skip_counter:slave应该跳过的事件数
  10. Exec_Master_Log_Pos: 446
       sql线程当前执行的事件,在master 二进制日志中的position(从库的Position值)
  11. Relay_Log_Space: 878
        所有存在relay log的大小
  12. Seconds_Behind_Master: 0
       这个值是时间戳的差值。是slave当前的时间戳和master记录该事件时的时间戳的差值
  13. Replicate_Ignore_Server_Ids: 
       slave当前会跳过的事件号
  14. Master_Server_Id: 2211
       master的server-id;如果master和slave的server-id相同,在启动slave时,会报错



在从库使用show slave status\;查看Read_Master_Log_Pos:   和  Exec_Master_Log_Pos:  的值如果不一样说明数据同步不一致

相关命令

(1)查看主从同步状态
show slave status\G;
(2)启动从库开始同步数据
start slave;

四、mysql读写分离

(1)mysql读写分离必须依赖mysql主从同步,开发将数据写入mysql主库,然后mysql主库的数据会自动同步到mysql从库,当需要读取数据的时候从mysql从库读取数据,一般主库负责写,从库负责读,


(2)mysql读写分离多由开发控制,当然也可以交由运维实现:

常见现象
  运维工作中会经常维护MySQL主从服务器,当然Slave我们只是用于读操作。
一般权限开通也只授权只读账号,但是有时候维护工作可能不是一个人在做,你不能保证其他同事都按照这个标准操作。
有同事可能会授权Slave库MySQL账号为all或者select,update,insert,delete。还有一种情况是主从做了对所有数据的同步(包括用户信息),在Master库上面授权的账号也同步到了Slave库上面,当然Master账号中肯定会有select,update,insert,delete权限。

存在的问题
  那么问题来了,当运维人员或者开发人员程序错误的连接了Mysql把Slave当成了Master等情况,那么就悲催了所有的数据修改就到Slave了,也会直接影响到主从的同步。

为了避免上述问题,我们需要给MySQL的Slave设置为只读模式,当然不会影响到主从同步,从库只读对super账户无效。

解决方法
演示如下:
mysql> set global read_only=1; 
Query OK, 0 rows affected (0.00 sec)
#set global read_only=0读写模式,1只读模式

# 查看哪些用户具有super权限
mysql> select user,host,Super_priv from mysql.user;
+------------------+------------+------------+
| user             | host       | Super_priv |
+------------------+------------+------------+
| root             | %          | Y          |
| wenqiang         | %          | Y          |
| root             | 127.0.0.1  | Y          |
| slave2           | 172.17.0.2 | N          |
| slave            | 172.17.0.3 | N          |
| mysql.infoschema | localhost  | N          |
| mysql.session    | localhost  | Y          |
| mysql.sys        | localhost  | N          |
+------------------+------------+------------+
8 rows in set (0.00 sec)

授权普通MySQL测试账号,创建普通用户不能用 grant all privileges 
mysql> grant select,insert,update,delete on s18.* to 'test'@'127.0.0.1' identifi
ed by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
用测试账号登陆进行删除等操作,会提示--read-only错误
复制代码
复制代码
mysql> delete from student where sid=14;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so i
t cannot execute this statement

mysql> insert class values(5,三年级十班);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so i
t cannot execute this statement
复制代码
复制代码
注意:set global read_only=1 对拥有super权限的账号是不生效的,所以在授权账号的时候尽量避免添加super权限

那么我们在做数据迁移的时候不想发生任何数据的修改,包括super权限修改也要限制。
可以用锁表:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.18 sec)
使用root账号测试:
mysql>  delete from student where sid=13;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read
lock
解锁测试:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>  delete from student where sid=13;
Query OK, 0 rows affected (0.00 sec)

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/941228.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

在Java虚拟机(JVM)中,方法可以分为虚方法和非虚方法。

在Java虚拟机(JVM)中,方法可以分为虚方法和非虚方法。以下是关于这两种方法的详细解释: 一、虚方法(Virtual Method) 定义:虚方法是指在运行时由实例的实际类型决定的方法。在Java中,所有的非私有、非静态、非final方法都是虚方法。当调用一个虚方法时,JVM会根据实…

【RabbitMQ】RabbitMQ保证消息不丢失的N种策略的思想总结

文章目录 生产者端&#xff08;消息发布端&#xff09;保证机制RabbitMQ服务器端保证机制消费者端&#xff08;消息接收端&#xff09;保证机制除了MQ自带的机制&#xff0c;还能做的操作持久化的原理ACK思想 更多相关内容可查看 消息从发送&#xff0c;到消费者接收&#xff0…

重拾设计模式--外观模式

文章目录 外观模式&#xff08;Facade Pattern&#xff09;概述定义 外观模式UML图作用 外观模式的结构C 代码示例1C代码示例2总结 外观模式&#xff08;Facade Pattern&#xff09;概述 定义 外观模式是一种结构型设计模式&#xff0c;它为子系统中的一组接口提供了一个统一…

新版国标GB28181设备端Android版EasyGBD支持国标GB28181-2022,支持语音对讲,支持位置上报,开源在Github

经过近3个月的迭代开发&#xff0c;新版本的国标GB28181设备端EasyGBD安卓Android版终于在昨天发布到Github了&#xff0c;最新的EasyGBD支持了国标GB28181-2022版&#xff0c;还支持了语音对讲、位置上报、本地录像等功能&#xff0c;比原有GB28181-2016版的EasyGBD更加高效、…

element-puls封装表单验证

项目场景&#xff1a; 提示&#xff1a;这里简述项目相关背景&#xff1a; 在做项目中会有一些简单的表单非空验证&#xff0c;这些验证比较简单&#xff0c;就是代码看着有点多&#xff0c;做起来浪费时间&#xff0c;所以我们可以将这个方法封装起来&#xff0c;然后挂载全…

Unity命令行传递自定义参数 命令行打包

命令行参数增加位置 -executeMethod 某脚本.某方法 参数1 参数2 参数3 ... 例如执行EditorTest.GetCommandLineArgs方法 增加两个命令行参数 Version=125 CDNVersion=100 -executeMethod EditorTest.GetCommandLineArgs Version=125 CDNVersion=100 Unity测试脚本 需要放在…

【Java基础面试题033】Java泛型的作用是什么?

Java的基础语法可以看尚硅谷的这个PDF&#xff1a;尚硅谷JavaSE基础/《Java从入门到精通(JDK17版)》_尚硅谷电子书.pdf Autism_Btkrsr/Blog_md_to_pdf - 码云 - 开源中国 (gitee.com) 回答重点 Java泛型的作用是通过在编译时检查类型安全&#xff0c;允许程序员编写更通用和…

Flutter环境搭建

1.Flutter 简介 1.1 Flutter 是什么 &#xff1f; Flutter 是一个 UI SDK&#xff08;Software Development Kit&#xff09;跨平台解决方案&#xff1a;可以实现一套代码发布移动端&#xff08;iOS、Android、HarmonyOS&#xff09;、Web端、桌面端目前很多公司都在用它&…

COMSOL with Matlab

文章目录 基本介绍COMSOL with MatlabCOMSOL主Matlab辅Matlab为主Comsol为辅 操作步骤常用指令mphopenmphgeommghmeshmphmeshstatsmphnavigatormphplot常用指令mphsavemphlaunchModelUtil.clear 实例教学自动另存新档**把语法套用到边界条件**把语法套用到另存新档 函数及其微分…

AlipayHK支付宝HK接入-商户收款(PHP)

一打开支付宝国际版 二、点开商户服务 三、下载源码

设计模式之 abstract factory

适用场景 一个系统要独立于它的产品的创建、组合和表示时。一个系统要由多个产品系列中的一个来配置时。当你要强调一系列相关的产品对象的设计以便进行联合使用时。当你提供一个产品类库&#xff0c;而只想显示它们的接口而不是实现时 架构演示 首先client这个东西可以接触到…

华为IPD流程6大阶段370个流程活动详解_第一阶段:概念阶段 — 81个活动

华为IPD流程涵盖了产品从概念到上市的完整过程,各阶段活动明确且相互衔接。在概念启动阶段,产品经理和项目经理分析可行性,PAC评审后成立PDT。概念阶段则包括产品描述、市场定位、投资期望等内容的确定,同时组建PDT核心组并准备项目环境。团队培训涵盖团队建设、流程、业务…

每天40分玩转Django:Django部署

Django部署 一、今日学习内容概述 学习模块重要程度主要内容生产环境配置⭐⭐⭐⭐⭐settings配置、环境变量WSGI服务器⭐⭐⭐⭐⭐Gunicorn配置、性能优化Nginx配置⭐⭐⭐⭐反向代理、静态文件安全设置⭐⭐⭐⭐⭐SSL证书、安全选项 二、生产环境配置 2.1 项目结构调整 mypr…

主要是使用#includenlohmannjson.hpp时显示找不到文件,但是我文件已正确导入visual studio配置,也保证文件正确存在

问题&#xff1a; 主要是在项目配置中包括了C/C配置中文件位置&#xff0c;但是没有把nlohmann上一级的目录包括进去&#xff0c;导致#include"nlohmann/json.hpp"找不到文件位置 解决&#xff1a; 加上上一级目录到附加包含目录 596513661)] 总结&#xff1a; 找不…

tslib(触摸屏输入设备的轻量级库)的学习、编译及测试记录

目录 tslib的简介tslib的源码和make及make install后得到的文件下载tslib的主要功能tslib的工作原理tslib的核心组成部分tslib的框架和核心函数分析tslib的框架tslib的核心函数ts_setup()的分析(对如何获取设备名和数据处理流程的分析)函数ts_setup()自身的主要代码ts_setup()对…

Unity DOTS中的share component

Unity DOTS中的share component 内存管理创建流程修改流程销毁流程Reference share component是DOTS中一类比较特殊的component&#xff0c;顾名思义&#xff0c;它是全局共享的component&#xff0c;所有具有相同component值的entity&#xff0c;共享一个component&#xff0c…

EfficienetAD异常值检测之瓷砖表面缺陷检测(免费下载测试数据集和模型)

背景 当今制造业蓬勃发展&#xff0c;产品质量把控至关重要。从精密电子元件到大型工业板材&#xff0c;表面缺陷哪怕细微&#xff0c;都可能引发性能故障或外观瑕疵。人工目视检测耗时费力且易漏检&#xff0c;已无法适应高速生产线节奏。在此背景下&#xff0c;表面缺陷异常…

【从零开始入门unity游戏开发之——C#篇21】C#面向对象的封装——`this`扩展方法、运算符重载、内部类、`partial` 定义分部类

文章目录 一、this扩展方法1、扩展方法的基本语法2、使用扩展方法3、扩展方法的注意事项5、扩展方法的限制6、总结 二、运算符重载1、C# 运算符重载2、运算符重载的基本语法3. 示例&#xff1a;重载加法运算符 ()4、使用重载的运算符5、支持重载的运算符6、不能重载的运算符7、…

vscode 快速切换cangjie版本

前言 目前阶段cangjie经常更新&#xff0c;这就导致我们可能会需要经常在不同的版本之间切换。 在参加训练营时从张老师那学到了如何使用 vscode 的配置文件来快速进行cangjie版本的切换。 推荐一下张老师的兴趣组 SIGCANGJIE / 仓颉兴趣组 这里以 windows 下&#xff0c;配置…

RCE总结

文章目录 常见漏洞执行函数&#xff1a;1.系统命令执行函数2.代码执行函数 命令拼接符读取文件命令绕过&#xff1a;空格过滤绕过关键字绕过长度过滤绕过无参数命令执行绕过无字母数字绕过利用%0A截断利用回溯绕过利用create_function()代码注入无回显RCE1.反弹shell2.dnslog外…