热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

mysql主从复制的脚本文件(mysql主从复制的脚本文件怎么删除)

本文目录一览:1、关于mysql主从复制的错误

本文目录一览:


  • 1、关于mysql 主从复制的错误


  • 2、如何在一台windows主机上实现MySQL的主从复制


  • 3、安全最重要!MySQL配置主从复制,主主复制


  • 4、MySQL 主从,5 分钟带你掌握


  • 5、如何在一台windows主机上搭建mysql主从复制

关于mysql 主从复制的错误

项目上 MySQL 还原 SQL 备份经常会碰到一个错误如下,且通常出现在导入视图、函数、存储过程、事件等对象时,其根本原因就是因为导入时所用账号并不具有SUPER 权限,所以无法创建其他账号的所属对象。ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation常见场景:1. 还原 RDS 时经常出现,因为 RDS 不提供 SUPER 权限;2. 由开发库还原到项目现场,账号权限等有所不同。

处理方式:

1. 在原库中批量修改对象所有者为导入账号或修改 SQL SECURITY 为 Invoker;2. 使用 mysqldump 导出备份,然后将 SQL 文件中的对象所有者替换为导入账号。

二、问题原因我们先来看下为啥会出现这个报错,那就得说下 MySQL 中一个很特别的权限控制机制,像视图、函数、存储过程、触发器等这些数据对象会存在一个 DEFINER 和一个 SQL SECURITY 的属性,如下所示:

--视图定义CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test

--函数定义CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER

--存储过程定义CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER

--触发器定义CREATE DEFINER=`root`@`%` trigger t_test

--事件定义CREATE DEFINER=`root`@`%` EVENT `e_test`

DEFINER:对象定义者,在创建对象时可以手动指定用户,不指定的话默认为当前连接用户;

SQL SECURITY:指明以谁的权限来执行该对象,有两个选项,一个为 DEFINER,一个为 INVOKER,默认情况下系统指定为 DEFINER;DEFINER:表示按定义者的权限来执行; INVOKER:表示按调用者的权限来执行。

如果导入账号具有 SUPER 权限,即使对象的所有者账号不存在,也可以导入成功,但是在查询对象时,如果对象的 SQL SECURITY 为 DEFINER,则会报账号不存在的报错。ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist

三、改写内容上述这个 DEFINER 问题,个人想到最简单的解决方式就是 mysqldump 导出时直接摘除掉相关属性,但是 mysqldump 本身并不提供对应参数,所以比较蛋疼,无论是原库走脚本变更或是备份后修改 SQL 文件都不是非常方便,尤其是触发器的 DEFINER,只能先 DROP 再 CREATE 才可以变更。只能看下是否可以从 mysqldump 源码中去掉 DEFINER 定义。本次 mysqldump 改写主要有 2 个目的:1. 摘取备份中视图、函数、存储过程、触发器等对象的 DEFINER 定义;2. 尝试加上比较简单的备份进度显示(原生 mysqldump 的 verbose 参数不是非常清晰,想要实现 navicate 备份时的那种行数显示)。

改写好处:1. 可以避免还原时遇到 DEFINER 报错相关问题;2. 根据输出信息知道备份是否正常进行,防止备份中遇到元数据锁无法获取然后一直卡住的情况。

如何在一台windows主机上实现MySQL的主从复制

1、首先要在本地建立两个mysql服务(参考这里),指定不同的端口。我这里一个主(3306),一个从(3307)。

2、然后修改主配置文件:

[mysqld]

server-id = 1

binlog-do-db=test #要同步的数据库

#binlog-ignore-db=mysql #不同步的数据库,如果指定了binlog-do-db这里应该可以不用指定的

log-bin=mysql-bin #要生成的二进制日记文件名称

修改从配置文件:

[mysqld]

server-id = 2

log-bin = mysql-bin

replicate-do-db=test

3、在主库添加一个用户 repl 并指定replication权限

create user 'repl'@'127.0.0.1' identified by 'asdf';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; -- --这里我指定数据库(test.*)时报错,而指定全库(*.*)时会成功。

4、保持主从mysql的test数据库初始状态一致。

一般是先将所有的表加读锁,然后copy磁盘上的数据库文件夹。我这里直接停止服务,然后将数据文件拷贝过去。

5、在主数据库里面运行show master status;记下file和position字段对应的参数。

mysql show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 107 | test | |

+------------------+----------+--------------+------------------+

安全最重要!MySQL配置主从复制,主主复制

为了保障数据的安全与稳定性,我们常用数据库的主从复制与主主复制来实现。主从复制为从机实时拷贝一份主机的数据,当主机有数据变化时,从机的数据会跟着变,当从机数据有变化时,主机数据不变;同样地,主主复制就是,多个主机之间,只要有一个主机的数据变化了,其它主机数据也会跟着变化。

添加以下内容

如果你是使用我之前那种方式启动的MySQL,那么你只需要去你相关联的宿主机的配置文件夹里面去建立一个 my.cnf 然后写入上面的类容就好了。

比如:我的启动命令如下(不应该换行的,这里为了方便查看,我给它分行了)

那么我只需要在 /docker/mysql_master/conf 这个目录下创建 my.cnf 文件就好了。

这个命令是需要在容器里面执行的

docker重启mysql会关闭容器,我们需要重启容器。

确保在主服务器上 skip_networking 选项处于 OFF 关闭状态, 这是默认值。 如果是启用的,则从站无法与主站通信,并且复制失败。

我的命令如下

在从服务器配置连接到主服务器的相关信息 (在容器里面的mysql执行)

上面代码的xxxxx你需要换成你的IP,docker 查看容器 IP 的命令如下:

启动的那个从服务器的线程

测试的话,你可以在主服务器里面,创建一个数据库,发现从服务器里面也有了,就成功了。

如果你还想要一个从服务器,那么你只需要按照上面配置从服务器再配置一个就行了,新建的从服务器,会自动保存主服务器之前的数据。(测试结果) 如果你上面的主从复制搞定了,那么这个主主复制就很简单了。我们把上面的从服务器也改成主服务器

1)、修改上面的从服务器的my.cnf文件,和主服务器的一样(注意这个server-id不能一样)然后重启服务器 2)、在从服务器里面创建一个复制用户创建命令一样(这里修改一下用户名可以改为 repl2) 3)、在之前的主服务器里面运行下面这个代码

上面主要是教你怎么搭建一个MySQL集群,但是这里面还有很多其它的问题。也是我在学习过程中思考的问题,可能有的小伙伴上来看到文章长篇大论的看不下去,只想去实现这样一直集群功能,所以我就把问题写在下面了。

1)、MySQL的replication和pxc MySQL的集群方案有replication和pxc两种,上面是基于replication实现的。

replication: 异步复制,速度快,无法保证数据的一致性。 pxc: 同步复制,速度慢,多个集群之间是事务提交的数据一致性强。

2)、MySQL的replication数据同步的原理 我们在配置的时候开启了它的二进制日志,每次操作数据库的时候都会更新到这个日志里面去。主从通过同步这个日志来保证数据的一致性。

3)、可否不同步全部的数据 可以配置,同步哪些数据库,甚至是哪些表。

4)、怎么关闭和开始同步

5)、我就我的理解画出了,主从、主从从、主主、复制的图。

往期推荐:

利用Docker仅花1分钟时间安装好MySQL服务

Linux下MySQL 5.7的离线与在线安装(图文)

Linux下安装MySQL8.0(收藏!)

MySQL 主从,5 分钟带你掌握

MySQL 主从一直是面试常客,里面的知识点虽然基础,但是能回答全的同学不多。

比如楼哥之前面试小米,就被问到过主从复制的原理,以及主从延迟的解决方案,因为回答的非常不错,给面试官留下非常好的印象。你之前面试,有遇到过哪些 MySQL 主从的问题呢?

所谓 MySQL 主从,就是建立两个完全一样的数据库,一个是主库,一个是从库, 主库对外提供读写的操作,从库对外提供读的操作 ,下面是一主一从模式:

对于数据库单机部署,在 4 核 8G 的机器上运行 MySQL 5.7 时,大概可以支撑 500 的 TPS 和 10000 的 QPS, 当遇到一些活动时,查询流量骤然,就需要进行主从分离。

大部分系统的访问模型是读多写少,读写请求量的差距可能达到几个数量级,所以我们可以通过一主多从的方式, 主库只负责写入和部分核心逻辑的查询,多个从库只负责查询,提升查询性能,降低主库压力。

MySQL 主从还能做到服务高可用,当主库宕机时,从库可以切成主库,保证服务的高可用,然后主库也可以做数据的容灾备份。

整体场景总结如下:

MySQL 的主从复制是依赖于 binlog 的,也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上二进制日志文件。

主从复制就是将 binlog 中的数据从主库传输到从库上,一般这个过程是异步的,即主库上的操作不会等待 binlog 同步的完成。

详细流程如下:

当主库和从库数据同步时,突然中断怎么办?因为主库与从库之间维持了一个长链接,主库内部有一个线程,专门服务于从库的这个长链接的。

对于下面的情况,假如主库执行如下 SQL,其中 a 和 create_time 都是索引:

我们知道,数据选择了 a 索引和选择 create_time 索引,最后 limit 1 出来的数据一般是不一样的。

所以就会存在这种情况:在 binlog = statement 格式时,主库在执行这条 SQL 时,使用的是索引 a,而从库在执行这条 SQL 时,使用了索引 create_time,最后主从数据不一致了。

那么我们改如何解决呢?

可以把 binlog 格式修改为 row,row 格式的 binlog 日志记录的不是 SQL 原文,而是两个 event:Table_map 和 Delete_rows。

Table_map event 说明要操作的表,Delete_rows event用于定义要删除的行为,记录删除的具体行数。 row 格式的 binlog 记录的就是要删除的主键 ID 信息,因此不会出现主从不一致的问题。

但是如果 SQL 删除 10 万行数据,使用 row 格式就会很占空间的,10 万条数据都在 binlog 里面,写 binlog 的时候也很耗 IO。但是 statement 格式的 binlog 可能会导致数据不一致。

设计 MySQL 的大叔想了一个折中的方案,mixed 格式的 binlog,其实就是 row 和 statement 格式混合使用, 当 MySQL 判断可能数据不一致时,就用 row 格式,否则使用就用 statement 格式。

有时候我们遇到从数据库中获取不到信息的诡异问题时,会纠结于代码中是否有一些逻辑会把之前写入的内容删除,但是你又会发现,过了一段时间再去查询时又可以读到数据了,这基本上就是主从延迟在作怪。

主从延迟,其实就是“从库回放” 完成的时间,与 “主库写 binlog” 完成时间的差值, 会导致从库查询的数据,和主库的不一致 。

谈到 MySQL 数据库主从同步延迟原理,得从 MySQL 的主从复制原理说起:

总结一下主从延迟的主要原因 :主从延迟主要是出现在 “relay log 回放” 这一步,当主库的 TPS 并发较高,产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围,那么延时就产生了,当然还有就是可能与从库的大型 query 语句产生了锁等待。

我们一般会把从库落后的时间作为一个重点的数据库指标做监控和报警,正常的时间是在毫秒级别,一旦落后的时间达到了秒级别就需要告警了。

解决该问题的方法,除了缩短主从延迟的时间,还有一些其它的方法,基本原理都是尽量不查询从库。

具体解决方案如下:

在实际应用场景中,对于一些非常核心的场景,比如库存,支付订单等,需要直接查询从库,其它非核心场景,就不要去查主库了。

两台机器 A 和 B,A 为主库,负责读写,B 为从库,负责读数据。

如果 A 库发生故障,B 库成为主库负责读写,修复故障后,A 成为从库,主库 B 同步数据到从库 A。

一台主库多台从库,A 为主库,负责读写,B、C、D为从库,负责读数据。

如果 A 库发生故障,B 库成为主库负责读写,C、D负责读,修复故障后,A 也成为从库,主库 B 同步数据到从库 A。

如何在一台windows主机上搭建mysql主从复制

1、首先要在本地建立两个mysql服务(参考这里),指定不同的端口。这里一个主(3306),一个从(3307)。

2、然后修改主配置文件:

[mysqld]

server-id = 1

binlog-do-db=test #要同步的数据库

#binlog-ignore-db=mysql #不同步的数据库,如果指定了binlog-do-db这里应该可以不用指定的

log-bin=mysql-bin #要生成的二进制日记文件名称

修改从配置文件:

[mysqld]

server-id = 2

log-bin = mysql-bin

replicate-do-db=test

3、在主库添加一个用户 repl 并指定replication权限

create user 'repl'@'127.0.0.1' identified by 'asdf';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; -- --这里指定数据库(test.*)时报错,而指定全库(*.*)时会成功。

4、保持主从mysql的test数据库初始状态一致。

一般是先将所有的表加读锁,然后copy磁盘上的数据库文件夹。这里直接停止服务,然后将数据文件拷贝过去。

5、在主数据库里面运行show master status;记下file和position字段对应的参数。

mysql show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 107 | test | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

6、在从库设置它的master:

mysql change master to master_host='127.0.0.1',master_port=3306,master_user='repl',master_password='asdf',master_log_file='mysql-bin.000001',master_log_pos=107;

Query OK, 0 rows affected (0.19 sec)

这里的master_log_file和master_log_pos对应刚才show master status记下的参数。

7、在从库开启从数据库复制功能。

slave start;

mysql slave start;

Query OK, 0 rows affected (0.00 sec)

在从库可以通过show slave status来查看一些参数。

8. 此时在主库创建表或插入数据,在从库就会很快也能看到了。

-- 主库

mysql create table tianyc_02(b int);

Query OK, 0 rows affected (0.16 sec)

mysql insert into tianyc_02 values(2013);

Query OK, 1 row affected (0.13 sec)

-- 从库

mysql show tables;

+----------------+

| Tables_in_test |

+----------------+

| tianyc_01 |

| tianyc_02 |

+----------------+

2 rows in set (0.00 sec)

mysql select * from tianyc_02;

+------+

| b |

+------+

| 2013 |

+------+

1 row in set (0.00 sec)

同理,可以搭建第二、第三个从节点。

备注:两个服务的serve_id必须不同,否则在开启复制功能时会提示错误

mysql slave start;

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

这里设置的主节点id为1,从节点id为2。也可以按照ip或端口进行设置,好用来区分。

注意:

* 主从间的数据库不是实时同步,就算网络连接正常,也存在瞬间,主从数据不一致。

* 如果主从的网络断开,从会在网络正常后,批量同步。

* 如果对从进行修改数据,那么很可能从在执行主的bin-log时出现错误而停止同步,这个是很危险的操作。所以一般情况下,非常小心的修改从上的数据。


推荐阅读
  • 在使用 SQL Server 时,连接故障是用户最常见的问题之一。通常,连接 SQL Server 的方法有两种:一种是通过 SQL Server 自带的客户端工具,例如 SQL Server Management Studio;另一种是通过第三方应用程序或开发工具进行连接。本文将详细分析导致连接故障的常见原因,并提供相应的解决策略,帮助用户有效排除连接问题。 ... [详细]
  • 尽管我们尽最大努力,任何软件开发过程中都难免会出现缺陷。为了更有效地提升对支持部门的协助与支撑,本文探讨了多种策略和最佳实践,旨在通过改进沟通、增强培训和支持流程来减少这些缺陷的影响,并提高整体服务质量和客户满意度。 ... [详细]
  • Amoeba 通过优化 MySQL 的读写分离功能显著提升了数据库性能。作为一款基于 MySQL 协议的代理工具,Amoeba 能够高效地处理应用程序的请求,并根据预设的规则将 SQL 请求智能地分配到不同的数据库实例,从而实现负载均衡和高可用性。该方案不仅提高了系统的并发处理能力,还有效减少了主数据库的负担,确保了数据的一致性和可靠性。 ... [详细]
  • 数字图书馆近期展出了一批精选的Linux经典著作,这些书籍虽然部分较为陈旧,但依然具有重要的参考价值。如需转载相关内容,请务必注明来源:小文论坛(http://www.xiaowenbbs.com)。 ... [详细]
  • Presto:高效即席查询引擎的深度解析与应用
    本文深入解析了Presto这一高效的即席查询引擎,详细探讨了其架构设计及其优缺点。Presto通过内存到内存的数据处理方式,显著提升了查询性能,相比传统的MapReduce查询,不仅减少了数据传输的延迟,还提高了查询的准确性和效率。然而,Presto在大规模数据处理和容错机制方面仍存在一定的局限性。本文还介绍了Presto在实际应用中的多种场景,展示了其在大数据分析领域的强大潜力。 ... [详细]
  • 本文探讨了 Kafka 集群的高效部署与优化策略。首先介绍了 Kafka 的下载与安装步骤,包括从官方网站获取最新版本的压缩包并进行解压。随后详细讨论了集群配置的最佳实践,涵盖节点选择、网络优化和性能调优等方面,旨在提升系统的稳定性和处理能力。此外,还提供了常见的故障排查方法和监控方案,帮助运维人员更好地管理和维护 Kafka 集群。 ... [详细]
  • 本指南详细介绍了在Linux环境中高效连接MySQL数据库的方法。用户可以通过安装并使用`mysql`客户端工具来实现本地连接,具体命令为:`mysql -u 用户名 -p 密码 -h 主机`。例如,使用管理员账户连接本地MySQL服务器的命令为:`mysql -u root -p pass`。此外,还提供了多种配置优化建议,以确保连接过程更加稳定和高效。 ... [详细]
  • 在CICS应用环境中,众多客户端通过网络与CICS服务器进行连接。系统管理员可以通过CICS系统交易CEMT查询当前连接的客户端信息。然而,在非客户端模式下,识别用户连接并解决信息获取错误的问题变得更为复杂。本文将探讨如何在CICS服务器端准确识别非客户端模式的用户连接,并提供有效的解决方案,以确保系统的稳定性和数据的准确性。此外,还将介绍一些常用的诊断工具和技术,帮助管理员快速定位和解决问题。 ... [详细]
  • 技术日志:使用 Ruby 爬虫抓取拉勾网职位数据并生成词云分析报告
    技术日志:使用 Ruby 爬虫抓取拉勾网职位数据并生成词云分析报告 ... [详细]
  • 今天我开始学习Flutter,并在Android Studio 3.5.3中创建了一个新的Flutter项目。然而,在首次尝试运行时遇到了问题,Gradle任务 `assembleDebug` 执行失败,退出状态码为1。经过初步排查,发现可能是由于依赖项配置不当或Gradle版本不兼容导致的。为了解决这个问题,我计划检查项目的 `build.gradle` 文件,确保所有依赖项和插件版本都符合要求,并尝试更新Gradle版本。此外,还将验证环境变量配置是否正确,以确保开发环境的稳定性。 ... [详细]
  • 在IIS上运行的WebApi应用程序在开发环境中能够正常进行文件的读写操作。然而,在尝试通过FTP访问实时服务器上的文件列表时,遇到了无法显示的问题,尽管服务器配置与开发环境相同。这可能涉及权限设置、FTP服务配置或网络连接等方面的问题。 ... [详细]
  • 本文详细介绍了一种利用 ESP8266 01S 模块构建 Web 服务器的成功实践方案。通过具体的代码示例和详细的步骤说明,帮助读者快速掌握该模块的使用方法。在疫情期间,作者重新审视并研究了这一未被充分利用的模块,最终成功实现了 Web 服务器的功能。本文不仅提供了完整的代码实现,还涵盖了调试过程中遇到的常见问题及其解决方法,为初学者提供了宝贵的参考。 ... [详细]
  • 在深入掌握Spring框架的事务管理之前,了解其背后的数据库事务基础至关重要。Spring的事务管理功能虽然强大且灵活,但其核心依赖于数据库自身的事务处理机制。因此,熟悉数据库事务的基本概念和特性是必不可少的。这包括事务的ACID属性、隔离级别以及常见的事务管理策略等。通过这些基础知识的学习,可以更好地理解和应用Spring中的事务管理配置。 ... [详细]
  • 本文探讨了如何在C#应用程序中通过选择ComboBox项从MySQL数据库中检索数据值。具体介绍了在事件处理方法 `comboBox2_SelectedIndexChanged` 中可能出现的常见错误,并提供了详细的解决方案和优化建议,以确保数据能够正确且高效地从数据库中读取并显示在界面上。此外,还讨论了连接字符串的配置、SQL查询语句的编写以及异常处理的最佳实践,帮助开发者避免常见的陷阱并提高代码的健壮性。 ... [详细]
  • 初探性能优化:入门指南与实践技巧
    在编程领域,常有“尚未精通编码便急于优化”的声音。为了从性能优化的角度提升代码质量,本文将带领读者初步探索性能优化的基本概念与实践技巧。即使程序看似运行良好,数据处理效率仍有待提高,通过系统学习性能优化,能够帮助开发者编写更加高效、稳定的代码。文章不仅介绍了性能优化的基础知识,还提供了实用的调优方法和工具,帮助读者在实际项目中应用这些技术。 ... [详细]
author-avatar
梦幻小螳螂
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有