Skip to content


Cursor限制国内用户使用Claude模型?国内免费使用Claude Code

这两天,很多使用Cursor进行AI编程的小伙伴突然发现一个醒目的提示

Model not available

This model provider doesn’t serve your region. Visit https://docs.cursor.com/account/regions for more information.

从官方文档来看,这次限制主要是由于A社更新了其服务区域政策,导致中国大陆地区无法再使用部分模型。

在国内社交媒体平台上,以“Cursor不能用了”为关键词搜索,可以看到开发者们已经炸锅了,相关帖子引发热议。本次断供的模型包含开发者编程时常用的主力模型,如Claude 4 Sonnet、Claude 3.5 Sonnet等性能优异的模型。有网友吐槽,自己是Cursor长期的订阅用户,却因为一纸通知而失去了当时付费购买的核心服务。

感觉自推出Claude Code后,其他对其产生严重依赖的AI IDE的使用体验也大大下降,部分原因可能是算力供应不足、A社要优先满足其自己的AI编程工具。 考虑到Cursor让人迷惑的定价和各种策略,以及Claude Code更强大的编程能力,直接使用Claude Code或许是一个更好的选择

Claude Code是什么?

Claude Code 是由 Anthropic 公司推出的一款智能编程工具,基于 Claude Sonnet 模型开发。这款工具最大的亮点是能够通过自然语言指令直接在终端操作,无需复杂的配置,就能完成代码编辑、Bug 修复、代码搜索等任务。它支持多种编程语言,包括Java、Python、go 等,并且可以直接集成到 VS Code、Cursor 等开发环境中,让开发者体验更流畅. https://www.anthropic.com/claude-code

国内使用 Claude Code 的难点

尽管 Claude Code 功能强大,但在国内使用却并不容易,主要原因包括:

网络限制:由于官方需要稳定的外网环境,国内用户往往需要借助“魔法网络”才能访问。 费用高昂:Claude Code 的订阅费用不低,尤其是 Max 计划,每月 200 美元的价格让许多开发者望而却步。 注册门槛:需要海外邮箱和手机号进行注册,国内用户难以满足这些条件。 那么,有没有办法绕过这些限制,让我们在国内也能免费使用呢?答案是肯定的!接下来,给大家分享,如何白嫖 Claude Code,国内也能免费使用

Any Router

Any Router 是一个API聚合、转发、统计和管理平台,适合需要高效调用和管理多种API资源的开发者和团队。我们可以在控制台查看账户余额、API调用统计、消耗分布等信息,并进行相关管理操作。 为什么选择AnyRouter接入Claude?

零成本启动:新用户注册即赠  $50额度,足够体验数百次Claude 3.7高级模型调用1 绕过风控难题:直接注册Claude账号需海外手机/IP,且封号率高(网友实测“比OpenAI更严”)34 模型自由选:支持全系列Claude模型(含最新3.7 Sonnet),性能对标GPT-4甚至部分场景反超2

注册AnyRouter账号

步骤1:

首先打开平台链接 https://anyrouter.top/login https://anyrouter.top/register?aff=2sDt

步骤2: 使用 GitHub 账号登录

进入到Any Router首页,使用 GitHub 账号登录(如果没有 GitHub,需先注册一个,此处需要魔法)

步骤3: 100 美元

登录后,你会自动进入数据看板,并免费获得 100 美元的初始额度。

步骤4: 注册成功后,如果你觉得100美元的初始额度不够用,还可以将分享邀请链接 (可选)

发送给你的好友,这样您和好友都可获得额外 $50 Claude Code 额度的奖励。 https://anyrouter.top/register?aff=2sDt

步骤5: 添加API令牌,

进入“API 令牌”页面,点击“添加令牌”。 在名称栏填写易记的名称,例如“Claude Code Key” 在“模型限制”中选择 claude-sonnet-4-20250514,并勾选“设为无限额度”,其他保持默认设置即可。 点击“提交”,复制生成的 API Key,以备后续使用(以 sk- 开头,令牌后面要用)

步骤6:安装 Node.js(已安装可跳过)

确保 Node.js 版本 ≥ 18.0 https://nodejs.org/en/download/

# Ubuntu / Debian 用户
curl -fsSL https://deb.nodesource.com/setup_lts.x | sudo bash -
sudo apt-get install -y nodejs
node --version

# macOS 用户
sudo xcode-select --install
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
brew install node
node --version

# rocky
# LTS
curl -o- https://fnm.vercel.app/install | bash
# current
curl -fsSL https://rpm.nodesource.com/setup_current.x | sudo bash -
dnf install nodejs -y
node -v
v24.4.0

npm -v
11.4.2
 glibc二进制版适合centos7
https://unofficial-builds.nodejs.org/download/release/v18.20.8/node-v18.20.8-linux-x64-glibc-217.tar.gz
tar zxvf node-v18.20.8-linux-x64-glibc-217.tar.gz   -C /usr/local
ln -s /usr/local/node-v18.20.8-linux-x64-glibc-217/bin/node /usr/bin/node
ln -s /usr/local/node-v18.20.8-linux-x64-glibc-217/bin/npm  /usr/bin/npm 

将/usr/local/node-v18.20.8-linux-x64-glibc-217/bin/claude
加入到环境变量中
export PATH=$PATH:/usr/local/node-v18.20.8-linux-x64-glibc-217/bin/claude

步骤7:安装 Claude Code

 npm config get prefix
/usr

#全局npm包目录
/usr/lib/node_modules
npm install -g @anthropic-ai/claude-code
claude --version

如果是使用 Windows 系统,需要通过 WSL(Windows 子系统)安装。

步骤8: 配置环境变量,为避免每次重复输入,将环境变量写入 bash_profile 和 bashrc,将sk-xxx替换为前面获取的API令牌。

为避免每次重复输入,可将环境变量写入 bash_profile 和 bashrc:

# 对于 Bash 用户 (macOS 默认,部分 Linux)
echo -e '\n export ANTHROPIC_AUTH_TOKEN=sk-...' >> ~/.bash_profile
echo -e '\n export ANTHROPIC_BASE_URL=https://anyrouter.top' >> ~/.bash_profile
source ~/.bash_profile

echo -e '\n export ANTHROPIC_AUTH_TOKEN=sk-...' >> ~/.bashrc
echo -e '\n export ANTHROPIC_BASE_URL=https://anyrouter.top' >> ~/.bashrc
# 对于 Zsh 用户 (较新版 macOS 默认)
echo -e '\n export ANTHROPIC_AUTH_TOKEN=sk-...' >> ~/.zshrc
echo -e '\n export ANTHROPIC_BASE_URL=https://anyrouter.top' >> ~/.zshrc

步骤9: 使用 Claude Code

重启终端后,直接使用:

cd your-project-folder
claude

1.0.48 (Claude Code)

运行后 选择你喜欢的主题 + Enter 确认安全须知 + Enter 使用默认 Terminal 配置 + Enter 信任工作目录 + Enter

开始在终端里和你的 AI 编程搭档一起写代码吧!🚀

选择模型:/model, 默认的模型是Claude 4 Sonnet

对于日常任务,推荐使用 Claude 4 Sonnet,费率仅为 Claude 4 Opus 的 1/5;对于有较复杂的任务,可以使用更强大的 Claude 4 Opus.

Posted in AI, 技术.

Tagged with , .


Git CLI高危任意文件写入漏洞曝光,PoC已公开

Git CLI存在一个高危漏洞,攻击者可利用该漏洞在Linux和macOS系统上实现任意文件写入。目前该漏洞的概念验证(PoC)利用代码已公开。 该漏洞编号为CVE-2025-48384,CVSS严重性评分为8.1分。当用户执行

git clone –recursive命令克隆恶意仓库时,攻击者可借此实现远程代码执行。

一个看似无害的回车符(Carriage Return),竟然能让 Git 的子模块克隆逻辑彻底“失控”,甚至引发远程代码执行(RCE)!近日,研究人员 David Leadbeater 披露了一个严重漏洞(CVE-2025-48384),攻击者可以通过精心构造的 .gitmodules 文件,在类 Unix 系统上实现任意文件写入,最终控制用户系统。这一漏洞利用的是 Git 配置解析中对 \r 字符处理的不一致性,看似微小的逻辑差异,却构成了实质性安全威胁。

该研究者表示:“在类 Unix 平台上,如果你对不可信的仓库执行了 git clone –recursive 操作,极有可能会导致远程代码执行(RCE)。请尽快更新 Git 及其他嵌入 Git 的软件(包括 GitHub Desktop)到修复版本。”

原文链接:https://dgl.cx/2025/07/git-clone-submodule-cve-2025-48384#_

该漏洞特别影响macOS和Linux平台上未打补丁的Git CLI版本(v2.43.7、v2.44.4、v2.45.4、v2.46.4、v2.47.3、v2.48.2、v2.49.1和v2.50.1之前的版本)。值得注意的是,由于非UNIX系统在控制字符处理上的根本差异,Windows系统不受此漏洞影响。

企业必须立即通过各自的包管理器将受影响的Git CLI升级至已修复版本(v2.43.7、v2.44.4、v2.45.4、v2.46.4、v2.47.3、v2.48.2、v2.49.1或v2.50.1)。用户可通过执行git –version命令验证当前Git版本,并与存在漏洞的版本范围(包括v2.50.0、v2.49.0、v2.48.0-v2.48.1、v2.47.0-v2.47.2、v2.46.0-v2.46.3、v2.45.0-v2.45.3、v2.44.0-v2.44.3以及v2.43.6及更早版本)进行比对。

macOS平台的GitHub Desktop用户应暂时避免使用该客户端进行Git操作,转而使用已打补丁的Git CLI进行仓库管理,直至官方发布修复补丁。 安全团队可实施检测机制,使用自定义代理规则监控针对外部仓库的可疑

git clone –recursive操作,同时在克隆不受信任的仓库前审计.gitmodules文件内容。

Posted in 安全通告.

Tagged with , .


WordPress插件exlink-safe-filter上架

exlink-safe-filter – External Link Security

使用Trae AI辅助开发了wordpress外链打码插件, 现已通过wordpress审核,上线市场。可以从多个渠道获取。

=== exlink-safe-filter – External Link Security === Plugin Name: exlink-safe-filter Contributors: C1G Donate link: https://blog.c1gstudio.com/ Tags: external links, security, link filtering, whitelist, blacklist Requires at least: 5.0 Tested up to: 6.8 Stable tag: 2.0.4 License: GPLv2 or later License URI: https://www.gnu.org/licenses/gpl-2.0.html

高级外部链接安全过滤插件,提供白名单、灰名单、黑名单分类管理及多种安全防护选项。

== 描述 ==

exlink-safe-filter是一款企业级外部链接安全管理插件,专为WordPress网站设计。它通过先进的链接分类系统和灵活的安全策略,有效保护您的网站用户免受恶意链接的侵害,同时提供良好的用户体验。

主要功能:

  • 多层次链接分类系统

    • 白名单:完全信任的域名,直接显示原始链接
    • 灰名单:经过中转页但自动跳转
    • 黑名单:完全阻止访问,显示警告信息
    • 未知域名:可配置处理方式(3秒自动跳转、显示URL、显示编码URL或阻止访问)
  • 全面的内容处理范围

    • 内容类型:文章、页面、评论、产品(支持WooCommerce)
    • 元素类型:HTML链接(a标签)、纯文本URL、电子邮件地址、图片资源、其他资源(脚本、iframe等)
  • 灵活的处理选项

    • 处理时间:显示时处理(推荐)或发布/编辑时处理
    • 审计模式:保留原始URL在data-original-url属性中,便于审计
    • URL加密:支持Base64加密或明文显示
    • 自定义重定向地址:默认为/exlink-safe-redirect/
  • 高级安全特性

    • 域名掩码功能:替换主体域名中间部分,保留首尾字符(如将www.domain.com.cn显示为www.d***n.com.cn)
    • 全面的域名验证系统
    • 自定义警告和安全消息
    • 多语言支持:中文(简体)和英文
    • 自定义CSS样式:可定制中转页和警告信息样式
  • 用户友好的界面

    • 直观的设置页面
    • 详细的选项说明
    • 响应式设计,适配各种设备

== 安装 ==

  1. 从WordPress插件目录下载并安装插件,或上传exlink-safe-filter文件夹到/wp-content/plugins/目录
  2. 在WordPress后台激活插件
  3. 进入【设置】→【exlink-safe-filter Security】配置插件选项
  4. 根据需求设置白名单、灰名单和黑名单
  5. 配置安全策略和显示选项
  6. 保存设置后插件自动生效

== 截图 ==

  1. 安全中转页面示例
  2. 过滤后的显示页面
  3. 设置页面

== 常见问题 ==

= 如何添加域名到白名单? = 在设置页面的”域名列表”选项卡中,将域名添加到白名单文本框,每行一个域名(*.example.com)。

= 如何添加域名到灰名单? = 在设置页面的”域名列表”选项卡中,将域名添加到灰名单文本框,每行一个域名(*.example.com)。

= 如何添加域名到黑名单? = 在设置页面的”域名列表”选项卡中,将域名添加到黑名单文本框,每行一个域名(*.example.com)。

= 如何自定义中转页样式? = 在”自定义CSS”选项卡中,添加您的自定义CSS代码,插件会自动将其应用到中转页和警告信息。

= 插件支持哪些语言? = 当前支持中文(简体)和英文,可在设置页面的”常规设置”中切换。

= 如何配置域名掩码功能? = 在”安全设置”中,将”域名转码方式”设置为”打码”,系统会自动替换域名主体部分的中间字符为星号。

== 升级日志 ==

= 2.0.4 =

  • 增强所有用户输入输出的安全转义处理

= 2.0.3 =

  • 移除rot13加密方式
  • 增强所有用户输入输出的安全转义处理
  • 新增内容安全策略(CSP)保护
  • 强化XSS防御机制
  • 优化前端资源加载方式(WP 6.3+特性)

= 2.0.1 =

  • 新增恢复默认设置功能(带二次确认对话框)
  • 优化设置页面布局和提示文案
  • 优化默认安装配置
    • 默认不启用插件
    • 内容范围默认仅选中文章
    • 元素范围默认仅选中HTML链接

Posted in WordPress.

Tagged with , , , .


MySQL 8.0 for Database Administrators OCP 课程习题14

14. Skill Check Achieving High Availability with MySQL InnoDB Cluster

Which are true about a MySQL InnoDB cluster? (Choose two)

It provides a confgurable capability for the administration of a group of server instances in a cluster.

It uses AdminAPI to work with relational and document data in the MySQL Document Store.

It provides real-time access with data consistency across partitioned and distributed datasets.

It relies on MySQL Router to interconnect replicated servers in a group.

It uses MySQL Group Replication to replicate data between all servers in a group.

How does MySQL Shell (mysqlsh) support the administration of a cluster? (Choose two)

It enables replicated server updates using binary log position and global transaction identifiers (GTIDs).

It manages failover by automatically routing server connections.

It provides scripting capabilities using JavaScript, Python, and SQL commands.

It implements a pluggable architecture and allows developers to create customized plug-ins.

It enables access to MySQL features via APIs

You have a live cluster with three servers. Executing the dba.getCluster() function returns the Cluster value “cluster”. Which methods must be used, all of which are required, to add a fourth server to the cluster? (Choose three)

cluster.rescan()

cluster.rejoinInstance()

dba.configureLocalInstance()

cluster.addInstance()

cluster.dissolve()

dba.configureInstance()

Which are functions of Group Replication? (Choose two)

assigning replication servers to groups of three to nine servers

in multi-primary mode, enabling group members to certify the order of transactions within the global sequence of transactions

enabling automatic avoidance of “split-brain” situations among group members

in multi-primary mode, enabling automatic election of a new primary when the primary server fails

in single-primary mode, enabling all servers to accept transactions but only the primary decides the order to commit

Examine this mysqlsh statement and output: mysql-js> cluster.checkInstanceState(“root@server4:3314”) … … { “reason”: “recoverable”, “state”: “ok” } What does the output signify?

The instance has not executed any GTID transactions; therefore, it cannot conflict with the GTIDs executed by the cluster.

The instance has more executed GTIDs than the executed GTIDs of the cluster seed instances.

The instance has executed GTIDs, which diverge with the executed GTIDs of the cluster seed instances.

The instance has executed GTIDs, which do not conflict with the executed GTIDs of the cluster seed instances.

myCluster is an InnoDB cluster. Examine this mysqlsh statement and output: mysql-js > cluster.status() { “clusterName”: “myCluster”, “defaultReplicaSet”: { “name”: “default”, “primary”: “server2:3312”, “status”: “OK_NO_TOLERANCE”, … What does this status signify? (Choose two)

There are enough members online to form a quorum.

One or more members may be online but their status is not ONLINE or RECOVERING.

One or more members are online but cannot form a quorum.

There is no redundancy in the cluster, and the failure of a member will result in database outage.

There is sufficient redundancy available to tolerate at least one failure.


14. Skill Check Achieving High Availability with MySQL InnoDB Cluster

Which are true about a MySQL InnoDB cluster? (Choose two)

It provides a confgurable capability for the administration of a group of server instances in a cluster.

It uses AdminAPI to work with relational and document data in the MySQL Document Store.

It provides real-time access with data consistency across partitioned and distributed datasets.

It relies on MySQL Router to interconnect replicated servers in a group.

It uses MySQL Group Replication to replicate data between all servers in a group.

答案: It provides a confgurable capability for the administration of a group of server instances in a cluster. It uses MySQL Group Replication to replicate data between all servers in a group.

说明: What Is MySQL InnoDB Cluster? • Provides a complete and scalable high availability solution for MySQL – Easy to configure and administer a group of server instances in a cluster • Uses MySQL Group Replication to replicate data between all servers in the group – The AdminAPI removes the need to work with group replication directly. – You work with the AdminAPI via MySQL Shell using your choice of Python or JavaScript language. • Manages failover automatically – If a server in the group goes down, the cluster reconfigures itself. – It requires at least three servers for the group to be fault tolerant • Enables clients to connect to the group transparently – Clients connect to the group via MySQL Router and do not have to know the details of the individual instances within the group.

How does MySQL Shell (mysqlsh) support the administration of a cluster? (Choose two)

It enables replicated server updates using binary log position and global transaction identifiers (GTIDs).

It manages failover by automatically routing server connections.

It provides scripting capabilities using JavaScript, Python, and SQL commands.

It implements a pluggable architecture and allows developers to create customized plug-ins.

It enables access to MySQL features via APIs

答案: It provides scripting capabilities using JavaScript, Python, and SQL commands.

It enables access to MySQL features via APIs.

说明: MySQL Shell (mysqlsh) • Is an advanced client and code editor for MySQL • Provides scripting capabilities by using JavaScript, Python, or SQL commands – You can enter commands interactively or execute them in batches. – You can switch between languages by entering \js, \py, or \sql, respectively. • Enables access to MySQL features via APIs – XDevAPI: Communicate with a MySQL server running the X Plugin to work with both relational and document data in the MySQL Document Store. – AdminAPI: Configure and administer a MySQL InnoDB cluster. • Supports output in tab delimited, table, and JSON (JavaScript Object Notation) formats • Interacts with a MySQL server via a global Session object – If using Python and JavaScript, you create the Session object by calling the getSession() method on the mysqlx module. – If using SQL, the Session object is created when the client connects.

You have a live cluster with three servers. Executing the dba.getCluster() function returns the Cluster value “cluster”. Which methods must be used, all of which are required, to add a fourth server to the cluster? (Choose three)

cluster.rescan()

cluster.rejoinInstance()

dba.configureLocalInstance()

cluster.addInstance()

cluster.dissolve()

dba.configureInstance()

答案: cluster.rescan() cluster.addInstance() dba.configureInstance()

Which are functions of Group Replication? (Choose two)

assigning replication servers to groups of three to nine servers

in multi-primary mode, enabling group members to certify the order of transactions within the global sequence of transactions

enabling automatic avoidance of “split-brain” situations among group members

in multi-primary mode, enabling automatic election of a new primary when the primary server fails

in single-primary mode, enabling all servers to accept transactions but only the primary decides the order to commit

答案: assigning replication servers to groups of three to nine servers in multi-primary mode, enabling group members to certify the order of transactions within the global sequence of transactions

说明: How Group Replication Works • Servers belong to a replication group. – A replication group can contain up to nine servers. — At least three servers are required to be fault tolerant. – Group Replication uses global transaction identifiers (GTIDs). – The group is defined by a UUID. • Group membership is managed automatically. – A server that joins or rejoins the group will automatically synchronize with the others. – Servers can leave and join the group at any time. • Replication groups operate in one of two modes: – Single-primary: One server in the group accepts updates. – Multi-primary: All servers in the group accept updates. • Changes are replicated to all members of the group.

Examine this mysqlsh statement and output: mysql-js> cluster.checkInstanceState(“root@server4:3314”) … … { “reason”: “recoverable”, “state”: “ok” } What does the output signify?

The instance has not executed any GTID transactions; therefore, it cannot conflict with the GTIDs executed by the cluster.

The instance has more executed GTIDs than the executed GTIDs of the cluster seed instances.

The instance has executed GTIDs, which diverge with the executed GTIDs of the cluster seed instances.

The instance has executed GTIDs, which do not conflict with the executed GTIDs of the cluster seed instances.

答案: The instance has executed GTIDs, which do not conflict with the executed GTIDs of the cluster seed instances.

说明: Checking the State of an Instance • Execute cluster.checkInstanceState(instance) to verify the instance GTID state in relation to the cluster. – Analyzes the instance executed GTIDs with the executed/purged GTIDs on the cluster to determine if the instance is valid for the cluster. • The output of this method is one of the following: – OK new: The instance has not executed any GTID transactions; therefore, it cannot conflict with the GTIDs executed by the cluster. – OK recoverable: The instance has executed GTIDs, which do not conflict with the executed GTIDs of the cluster seed instances. – ERROR diverged: The instance has executed GTIDs, which diverge with the executed GTIDs of the cluster seed instances. – ERROR lost_transactions: The instance has more executed GTIDs than the executed GTIDs of the cluster seed instances.

myCluster is an InnoDB cluster. Examine this mysqlsh statement and output: mysql-js > cluster.status() { “clusterName”: “myCluster”, “defaultReplicaSet”: { “name”: “default”, “primary”: “server2:3312”, “status”: “OK_NO_TOLERANCE”, … What does this status signify? (Choose two)

There are enough members online to form a quorum.

One or more members may be online but their status is not ONLINE or RECOVERING.

One or more members are online but cannot form a quorum.

There is no redundancy in the cluster, and the failure of a member will result in database outage.

There is sufficient redundancy available to tolerate at least one failure.

答案: There is no redundancy in the cluster, and the failure of a member will result in database outage. There are enough members online to form a quorum.

说明: OK_NO_TOLERANCE = 当前服务正常(有 quorum) + 无故障缓冲(单点即崩)。需尽快修复离线节点或扩容!

节点有哪状态 ONLINE – 节点状态正常。 OFFLINE – 实例在运行,但没有加入任何Cluster。 RECOVERING – 实例已加入Cluster,正在同步数据。 ERROR – 同步数据发生异常。 UNREACHABLE – 与其他节点通讯中断,可能是网络问题,可能是节点crash。 MISSING 节点已加入集群,但未启动group replication

集群有哪些状态 OK – 所有节点处于online状态,有冗余节点。 OK_PARTIAL – 有节点不可用,但仍有冗余节点。 OK_NO_TOLERANCE – 有足够的online节点,但没有冗余,例如:两个节点的Cluster,其中一个挂了,集群就不可用了。 NO_QUORUM – 有节点处于online状态,但达不到法定节点数,此状态下Cluster无法写入,只能读取。 UNKNOWN – 不是online或recovering状态,尝试连接其他实例查看状态。 UNAVAILABLE – 组内节点全是offline状态,但实例在运行,可能实例刚重启还没加入Cluster。

Posted in Mysql.

Tagged with , , , .


MySQL 8.0 for Database Administrators OCP 课程习题13

13.Skill Check Administering a Replication Topology

Which are functions of a slave SQL thread? (Choose two)

reading events from the binary log and sending them to the slave I/O thread

distributing relay log events between worker threads on a multithreaded slave

allowing transactions updating different databases to run in parallel on a slave

connecting with the master’s binlog dump thread

copying events from the master relay log to the slave’s relay log

Examine this parameter setting: SLAVE_PARALLEL_TYPE = DATABASE Which are true? (Choose two)

transactions that are part of the same binary log group commit on a master can to run in parallel on a slave

transactions that update different databases can run in parallel on a slave

master-slave data consistency is ensured when there are cross-database constraints

MASTER_AUTO_POSITION must be set to 1 for this setting to take effect

SLAVE_PARALLEL_WORKERS must be greater than zero for this setting to take effect

Which are required when troubleshooting a MySQL replication error? (Choose two)

Verifying that the slave I/O thread serializes all event updates in the relay log.

Verifying that the Seconds_Behind_Master column displayed by the SHOW SLAVE STATUS command is zero.

Reviewing the events in the master binary log file.

Reviewing the Last_IO_Error and Last_SQL_Error columns displayed by the SHOW SLAVE STATUS command.

Verifing that both master and slave have unique nonzero server ID values.

Querying the I/O thread status of a replication slave displays this status message: Waiting for the slave SQL thread to free enough relay log space What does this status signify?

The value of the relay_log_space_limit parameter is zero.

The combined size of the relay logs exceeds the value of relay_log_space_limit.

The I/O thread is executing a LOAD DATA statement and appending the data to a temporary file which will be processed by the slave.

The I/O thread is copying an event to the relay log for processing by the SQL thread.

Which replication table in a performance schema contains the status of the SQL thread in a multithreaded slave?

replication_applier_status

replication_applier_status_by_coordinator

replication_applier_configuration

replication_applier_status_by_worker

You used the SHOW SLAVE STATUS command on a slave and received this output: … Master_Log_File: server1-bin.000004 Read_Master_Log_Pos: 275 Relay_Log_File: server2-relay-bin.000011 Relay_Log_Pos: 453 Relay_Master_Log_File: server1-bin.000002 Exec_Master_Log_Pos: 1307 … What does this status signify? (Choose two)

The binary log was re-sent as the slave failed to acknowledge completion

Log events are being copied faster than they are being executed on the slave.

There is latency in the SQL thread rather than the I/O thread.

There is network latency when transferring events between master and slave.

The slave is still executing previous binary log events but has received another binary log from the master.


13.Skill Check Administering a Replication Topology

Which are functions of a slave SQL thread? (Choose two)

reading events from the binary log and sending them to the slave I/O thread

distributing relay log events between worker threads on a multithreaded slave

allowing transactions updating different databases to run in parallel on a slave

connecting with the master’s binlog dump thread

copying events from the master relay log to the slave’s relay log

答案: allowing transactions updating different databases to run in parallel on a slave

distributing relay log events between worker threads on a multithreaded slave

Examine this parameter setting: SLAVE_PARALLEL_TYPE = DATABASE Which are true? (Choose two)

transactions that are part of the same binary log group commit on a master can to run in parallel on a slave

transactions that update different databases can run in parallel on a slave

master-slave data consistency is ensured when there are cross-database constraints

MASTER_AUTO_POSITION must be set to 1 for this setting to take effect

SLAVE_PARALLEL_WORKERS must be greater than zero for this setting to take effect

答案: SLAVE_PARALLEL_WORKERS must be greater than zero for this setting to take effect transactions that update different databases can run in parallel on a slave

说明: Multithreaded Slaves • Use multithreaded slaves to reduce slave lag. • Set the slave_parallel_workers variable to a value greater than zero to create that number of worker threads. – With multiple worker threads, the slave SQL thread does not apply events directly, but delegates responsibility to worker threads. • Set the slave_parallel_type variable to specify the parallelization policy. – DATABASE (default): Transactions that update different databases are applied in parallel. – LOGICAL_CLOCK: Transactions that are part of the same binary log group commit on a master are applied in parallel on a slave. — The master server can configure the binary log to record commit timestamps or write sets using the binlog_transaction_dependency_tracking variable. — Set slave_preserve_commit_order variable to ON to preserve the commit order.

Which are required when troubleshooting a MySQL replication error? (Choose two)

Verifying that the slave I/O thread serializes all event updates in the relay log.

Verifying that the Seconds_Behind_Master column displayed by the SHOW SLAVE STATUS command is zero.

Reviewing the events in the master binary log file.

Reviewing the Last_IO_Error and Last_SQL_Error columns displayed by the SHOW SLAVE STATUS command.

Verifing that both master and slave have unique nonzero server ID values. 答案: Verifing that both master and slave have unique nonzero server ID values. Reviewing the Last_IO_Error and Last_SQL_Error columns displayed by the SHOW SLAVE STATUS command.

说明: Troubleshooting MySQL Replication • View the error log. – The error log can provide you with enough information to identify and correct problems in replication. • Issue a SHOW MASTER STATUS statement on the master. – Logging is enabled if the position value is non-zero. • Verify that both the master and slave have a unique non-zero server ID value. – The master and the slave must have different server IDs. • Issue a SHOW SLAVE STATUS command on the slave or query the replication tables in Performance Schema. – Slave_IO_Running and Slave_SQL_Running display Yes when the slave is functioning correctly. – Last_IO Error and Last

Querying the I/O thread status of a replication slave displays this status message: Waiting for the slave SQL thread to free enough relay log space What does this status signify?

The value of the relay_log_space_limit parameter is zero.

The combined size of the relay logs exceeds the value of relay_log_space_limit.

The I/O thread is executing a LOAD DATA statement and appending the data to a temporary file which will be processed by the slave.

The I/O thread is copying an event to the relay log for processing by the SQL thread.

答案:

The combined size of the relay logs exceeds the value of relay_log_space_limit.

说明: Replication Slave I/O Thread States • Reconnecting after a failed master event read – The thread is trying to reconnect to the master. – When the thread reconnects, the state becomes Waiting for master to send event. • Waiting for the slave SQL thread to free enough relay log space – The combined size of the relay logs exceeds the value of relay_log_space_limit. — Only if non-zero. A zero value means there is no limit imposed on the size of the relay logs. – The I/O thread is waiting until the SQL thread frees enough space in the relay logs by processing and then deleting their contents.

Which replication table in a performance schema contains the status of the SQL thread in a multithreaded slave?

replication_applier_status

replication_applier_status_by_coordinator

replication_applier_configuration

replication_applier_status_by_worker

答案:

replication_applier_status_by_coordinator

说明: Replication Tables in Performance Schema Table Name Contains replication_connection_configuration Configuration parameters for connecting to the master replication_connection_status Status of the current connection to the master replication_applier_configuration Configuration parameters for the transaction applier on the slave replication_applier_status Current status of the transaction applier on the slave replication_applier_status_by_coordinator Status of the coordinator thread in a multithreaded slave replication_applier_status_by_worker Status of applier thread in a single-threaded slave or worker threads in a multithreaded slave replication_applier_filters Information about the replication filters configured on specific replication channels replication_applier_global_filters Information about global replication filters (all channels) replication_group_members Network and status information for group members replication_group_member_stats Statistical information about group members and the transactions they participate in

You used the SHOW SLAVE STATUS command on a slave and received this output: … Master_Log_File: server1-bin.000004 Read_Master_Log_Pos: 275 Relay_Log_File: server2-relay-bin.000011 Relay_Log_Pos: 453 Relay_Master_Log_File: server1-bin.000002 Exec_Master_Log_Pos: 1307 … What does this status signify? (Choose two)

The binary log was re-sent as the slave failed to acknowledge completion

Log events are being copied faster than they are being executed on the slave.

There is latency in the SQL thread rather than the I/O thread.

There is network latency when transferring events between master and slave.

The slave is still executing previous binary log events but has received another binary log from the master.

答案:

Log events are being copied faster than they are being executed on the slave. There is latency in the SQL thread rather than the I/O thread.

Posted in Mysql.

Tagged with , , , .


MySQL 8.0 for Database Administrators OCP 课程习题12

12.Skill Check Configuring a Replication Topology

 Which are true for semisynchronous replication? (Choose two)

It improves query execution performance of the master and at least one slave.

It restricts masters and slaves to use only statement-based binary logging.

It ensures data integrity between the master and at least one slave.

If the master is configured as semisynchronous, then all slaves must be configured as semisynchronous.

After committing a transaction, the master blocks until at least one slave acknowledges the transaction.

 Which are advantages of MySQL replication? (Choose two)

Another server, either master or slave, can take over the duties of a crashed server to prevent application outages.

Applications can scale-out SQL queries to multiple servers to reduce the response time.

Clients can run queries on different servers simultaneously to prevent data conflicts.

Same-level slave servers can share updates in case of the master’s failure to enable data coherency.

When the master is down, a slave server can be configured to automatically become the master to prevent database inconsistencies.

Which are requirements for enabling multisource replication? (Choose two)

The slave must create a replication channel for each master.

The slave must use FILE-based repositories.

All masters and slaves must be in a star topology.

All masters and slaves must be configured to prevent data conflicts.

The slave must use TABLE-based repositories.

Which steps are required to configure Server2 as slave of Server1? (Choose two)

Execute this command on Server2: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server1-bin.000001′, -> MASTER_LOG_POS=;

Add these settings to the Server1 my.sql file: [mysqld] log-bin=server1-bin

Add these settings to the Server2 my.sql file: [mysqld] log-bin=server2-bin

Execute this command on Server1: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server2-bin.000001′, -> MASTER_LOG_POS=;

Create a user on Server2 and grant them the REPLICATION SLAVE permission.

Which statement uses the global transaction identifier (GTID) replication protocol?

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3313, MASTER_AUTO_POSITION=1;

CHANGE MASTER TO RELAY_LOG_FILE=’slave-relay-bin.006′, RELAY_LOG_POS=4025;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3312, MASTER_LOG_FILE=’server2-bin.000001′, MASTER_LOG_POS=723074;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3311, MASTER_LOG_FILE=’server1-bin.000001′, MASTER_LOG_POS=155;

Which parameter settings enable and enforce logging based on global transaction identifier (GTID) for all transactions? (Choose two)

gtid-mode=OFF_PERMISSIVE enforce-gtid-consistency=OFF

gtid-mode=OFF enforce-gtid-consistency=WARN

gtid-mode=ON enforce-gtid-consistency=ON

gtid-mode=ON_PERMISSIVE enforce-gtid-consistency=ON

gtid-mode=ON enforce-gtid-consistency=WARN


12.Skill Check Configuring a Replication Topology

 Which are true for semisynchronous replication? (Choose two)

It improves query execution performance of the master and at least one slave.

It restricts masters and slaves to use only statement-based binary logging.

It ensures data integrity between the master and at least one slave.

If the master is configured as semisynchronous, then all slaves must be configured as semisynchronous.

After committing a transaction, the master blocks until at least one slave acknowledges the transaction.

答案: It ensures data integrity between the master and at least one slave. After committing a transaction, the master blocks until at least one slave acknowledges the transaction.

说明: Advantages and Disadvantages of Semisynchronous Replication Semisynchronous replication: • Ensures data integrity • Results in reduced performance – The master waits for the slave to respond before committing the transaction. — The timeout period is controlled by the rpl_semi_sync_master_timeout variable value. The default is 10,000 ms (10 seconds). — If no response is received, the master still commits the transaction but reverts to asynchronous mode. – The extra time taken by each transaction includes: — The TCP/IP roundtrip to send the commit to the slave — The slave recording the commit in its relay log — The master waiting for the slave’s acknowledgment of that commit • Is suitable primarily for servers that are physically co-located, communicating over fast networks

 Which are advantages of MySQL replication? (Choose two)

Another server, either master or slave, can take over the duties of a crashed server to prevent application outages.

Applications can scale-out SQL queries to multiple servers to reduce the response time.

Clients can run queries on different servers simultaneously to prevent data conflicts.

Same-level slave servers can share updates in case of the master’s failure to enable data coherency.

When the master is down, a slave server can be configured to automatically become the master to prevent database inconsistencies.

答案: Another server, either master or slave, can take over the duties of a crashed server to prevent application outages.

Applications can scale-out SQL queries to multiple servers to reduce the response time.

Which are requirements for enabling multisource replication? (Choose two)

The slave must create a replication channel for each master.

The slave must use FILE-based repositories.

All masters and slaves must be in a star topology.

All masters and slaves must be configured to prevent data conflicts.

The slave must use TABLE-based repositories.

答案: The slave must create a replication channel for each master. The slave must use TABLE-based repositories.

说明: Backing Up from Multiple Sources to a Single Server • Multisource replication can be used to: – Back up multiple servers to a single server – Merge table shards – Consolidate data from multiple servers to a single server • The slave creates a replication channel for each master from which it receives transactions. – Use the CHANGE MASTER TO … FOR CHANNEL channelname syntax: • Replicate from all channels concurrently or start and stop individual channels: CHANGE MASTER TO … MASTER_LOG_FILE=’binlog.000006′, MASTER_LOG_POS=143 FOR CHANNEL ‘shard-1’; START SLAVE IO_THREAD FOR CHANNEL ‘shard-1’;

Configuring Multi-Source Replication for a Binary Log Based Master

  1. Enable binary logging on the master by using –log-bin.
  2. Create a replication user.
  3. Note the current binary log file and position. – MASTER_LOG_FILE and MASTER_LOG_POSITION
  4. Verify that the slave is using TABLE-based replication repositories.
  5. Execute CHANGE MASTER TO… to add a new master to a channel by using a FOR CHANNEL clause. – Example: Add a new master with host name master1 using port 3451 to channel master-1. CHANGE MASTER TO MASTER_HOST=’master1′, MASTER_PORT=3451, MASTER_USER=’rpl’, MASTER_PASSWORD=’pass’, MASTER_LOG_FILE=’master1-bin.000003′, MASTER_LOG_POS=719, FOR CHANNEL ‘master-1’;

Which steps are required to configure Server2 as slave of Server1? (Choose two)

Execute this command on Server2: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server1-bin.000001′, -> MASTER_LOG_POS=;

Add these settings to the Server1 my.sql file: [mysqld] log-bin=server1-bin

Add these settings to the Server2 my.sql file: [mysqld] log-bin=server2-bin

Execute this command on Server1: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server2-bin.000001′, -> MASTER_LOG_POS=;

Create a user on Server2 and grant them the REPLICATION SLAVE permission.

答案: Execute this command on Server2: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server1-bin.000001′, -> MASTER_LOG_POS=;

Add these settings to the Server1 my.sql file: [mysqld] log-bin=server1-bin

Which statement uses the global transaction identifier (GTID) replication protocol?

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3313, MASTER_AUTO_POSITION=1;

CHANGE MASTER TO RELAY_LOG_FILE=’slave-relay-bin.006′, RELAY_LOG_POS=4025;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3312, MASTER_LOG_FILE=’server2-bin.000001′, MASTER_LOG_POS=723074;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3311, MASTER_LOG_FILE=’server1-bin.000001′, MASTER_LOG_POS=155;

答案: CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3313, MASTER_AUTO_POSITION=1;

说明:

Configuring Replication Slaves • Restore the backup from the master. – Verify that the gtid_purged variable is set if you are using GTIDs. • Issue a CHANGE MASTER TO statement on each slave with the: – Network location of the master — MASTER_HOST and MASTER_PORT values — Optionally, use MASTER_SSL and related options to encrypt network traffic between masters and slaves during replication. – Replication account username and password (with the REPLICATION SLAVE privilege) — MASTER_USER and MASTER_PASSWORD values – Binary log coordinates from which to start replicating (if you are not using GTIDs) — The MASTER_LOG_FILE and MASTER_LOG_POS values store the binary log position from which the slave starts replicating. — Specify MASTER_AUTO_POSITION=1 if you are using GTIDs.

Which parameter settings enable and enforce logging based on global transaction identifier (GTID) for all transactions? (Choose two)

gtid-mode=OFF_PERMISSIVE enforce-gtid-consistency=OFF

gtid-mode=OFF enforce-gtid-consistency=WARN

gtid-mode=ON enforce-gtid-consistency=ON

gtid-mode=ON_PERMISSIVE enforce-gtid-consistency=ON

gtid-mode=ON enforce-gtid-consistency=WARN

答案: gtid-mode=ON enforce-gtid-consistency=ON

gtid-mode=ON_PERMISSIVE enforce-gtid-consistency=ON

说明: Global Transaction Identifiers (GTIDs) Global Transaction Identifiers (GTIDs) uniquely identify each transaction in a replication topology. • Each GTID is of the form :. For example: • A GTID set contains a range of GTIDs: • Enable GTID mode with the following options: – gtid-mode=ON: Logs a unique GTID along with each transaction – enforce-gtid-consistency: Disallows events that cannot be logged in a transactionally safe way – log-slave-updates: Records replicated events to the slave’s binary log

GTID_MOD OFF :不产生GTID,Slave只接受不带GTID的事务 OFF_PERMISSIVE :不产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务 ON_PERMISSIVE :产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务 ON :产生GTID,Slave只能接受带GTID的事务。

Posted in Mysql.

Tagged with , , , .


选择 MySQL 8.0 for Database Administrators OCP 课程习题11

11.Skill Check Performing Backups

How does the – – master-data option of the mysqldump command ensure data consistency during a database backup? (Choose two)

It starts a new binary log.

It acquires a global lock at the beginning of the backup operation.

It records the binlog position in the backup file.

It creates database and table structures but does not dump the data.

It locks tables during backup.

Which are features of the mysqlpump utility? (Choose three)

dumping InnoDB CREATE TABLESPACE statements

restoring databases using dumped data files

parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

taking physical backups of databases

faster reloading of secondary indexes for InnoDB tables

Which command forces the current binary log to close and the next incremental binary log to open?

mysql> GRANT RELOAD ON . TO ‘backupuser’@’localhost’;

mysql> FLUSH LOGS;

mysql> PURGE BINARY LOGS TO ‘binlog.000048’;

mysql> FLUSH BINARY LOGS;

Which are features or capabilities of multisource replication? (Choose three)

consolidating data from multiple servers to a single server

preventing slaves from making backups

backing up files not requiring additional storage or processing on the master

merging table shards

adding processing load on the master

backing up multiple servers to a single server

Which privileges does a user need to execute the mysqldump command? (Choose three)

SELECT for dumped tables

CREATE on each of the dumped objects

TRIGGER for dumped triggers

SHOW VIEW for dumped views

RELOAD on all databases and tables

ALTER on the database

Which commands back up the “employees” database to a single SQL script? (Choose two)

# mysql -uemployee -p employees < /labs/firewall_training.sql

# mysqldump -uroot -p –tab=/backups employees

# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

# mysqlimport -uroot -p emps2 /backups/dept_manager.txt


11.Skill Check Performing Backups

How does the – – master-data option of the mysqldump command ensure data consistency during a database backup? (Choose two)

It starts a new binary log.

It acquires a global lock at the beginning of the backup operation.

It records the binlog position in the backup file.

It creates database and table structures but does not dump the data.

It locks tables during backup.

答案:

It records the binlog position in the backup file.

It locks tables during backup.

说明: Ensuring Data Consistency with mysqldump Ensuring consistency: • –master-data option alone – Locks all tables during backup with FLUSH TABLES WITH READ LOCK – Records the binlog position as a CHANGE MASTER TO statement in the backup file — –master-data=2 records the position as a comment • –master-data and –single-transaction options used together – Does not lock tables – only InnoDB table consistency is guaranteed – Acquires a global lock at the beginning of the backup operation to obtain a consistent binary log position • –lock-all-tables – Satisfies consistency by locking all tables for the duration of the whole dump • –flush-logs – Starts a new binary log

Which are features of the mysqlpump utility? (Choose three)

dumping InnoDB CREATE TABLESPACE statements

restoring databases using dumped data files

parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

taking physical backups of databases

faster reloading of secondary indexes for InnoDB tables

答案: parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

faster reloading of secondary indexes for InnoDB tables

说明: mysqlpump mysqlpump is very similar to mysqldump, but with the following enhancements: • Provides better performance than mysqldump by extracting data in parallel threads – mysqlpump divides the dump process into several subtasks and then adds these subtasks to a multithreaded queue. – This queue is then processed by N threads (two by default). • Enables better control over which database objects to dump on each thread • Dumps users as CREATE USER/GRANT statements instead of as INSERTs into the mysql system database • Enables compressed output • Displays a progress indicator • Provides faster secondary index reloading for InnoDB tables

Which command forces the current binary log to close and the next incremental binary log to open?

mysql> GRANT RELOAD ON . TO ‘backupuser’@’localhost’;

mysql> FLUSH LOGS;

mysql> PURGE BINARY LOGS TO ‘binlog.000048’;

mysql> FLUSH BINARY LOGS;

答案: mysql> FLUSH LOGS;

Which are features or capabilities of multisource replication? (Choose three)

consolidating data from multiple servers to a single server

preventing slaves from making backups

backing up files not requiring additional storage or processing on the master

merging table shards

adding processing load on the master

backing up multiple servers to a single server

答案: consolidating data from multiple servers to a single server merging table shards backing up multiple servers to a single server

说明: Backing Up from Multiple Sources to a Single Server • Multisource replication can be used to: – Back up multiple servers to a single server – Merge table shards – Consolidate data from multiple servers to a single server • The slave creates a replication channel for each master from which it receives transactions. – Use the CHANGE MASTER TO … FOR CHANNEL channelname syntax: • Replicate from all channels concurrently or start and stop individual channels: CHANGE MASTER TO … MASTER_LOG_FILE=’binlog.000006′, MASTER_LOG_POS=143 FOR CHANNEL ‘shard-1’; START SLAVE IO_THREAD FOR CHANNEL ‘shard-1’;

Which privileges does a user need to execute the mysqldump command? (Choose three)

SELECT for dumped tables

CREATE on each of the dumped objects

TRIGGER for dumped triggers

SHOW VIEW for dumped views

RELOAD on all databases and tables

ALTER on the database

答案: SELECT for dumped tables

TRIGGER for dumped triggers

SHOW VIEW for dumped views

说明: Privileges Required for mysqldump You must have the following privileges to use mysqldump: • SELECT for dumped tables • SHOW VIEW for dumped views • TRIGGER for dumped triggers • LOCK TABLES (unless you use the –-single-transaction option) • Other options might require extra privileges. For example: – To use the –flush-logs or –master-data options, you must have the RELOAD privilege. – To create a tab-delimited output with the –tab option, you must have the FILE privilege. – To use the –routines option to back up stored functions and procedures, you must have the global SELECT privilege.

Which commands back up the “employees” database to a single SQL script? (Choose two)

# mysql -uemployee -p employees < /labs/firewall_training.sql

# mysqldump -uroot -p –tab=/backups employees

# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

# mysqlimport -uroot -p emps2 /backups/dept_manager.txt

答案: # mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

Posted in Mysql.

Tagged with , , , .


MySQL 8.0 for Database Administrators OCP 课程习题10

10.Skill Check Choosing a Backup Strategy

 Which techniques segregate data file backups from MySQL server data files? (Choose three)

replication

OS copy commands

binary logging

MySQL enterprise backup

distributed replicated block device

snapshots

Which are true about warm backups? (Choose two)

Updates are not possible during these backups.

When backups occurs, the server is in an inaccessible mode.

They do not impact system performance.

They permit applications to only read data.

They lock data using multiversion concurrency control (MVCC).

Which of these use full and binary log backups? (Choose two)

daily/hourly supplemental backups containing live data

backups using mysqldump with the –where option

weekly conditional backups of large transactional tables containing only fixed or historical data

multiple backups of data modifications taken each hour to minimize exposure during each day

backups using mysqlbackup from a replication slave

Which methods are used for logical backups? (Choose three)

MySQL enterprise backup

the mysqldump utility

the mysqlpump utility

snapshots

OS copy commands

SQL statements

Which are true about logical backups? (Choose three)

They use a snapshot capability that is external to MySQL.

They can be restored to MySQL databases on machines with different architectures than the one where the backup was created.

They can be taken to back up both local and remote MySQL servers.

They require the MySQL server to be running during the backups.

They generate SQL statements that can be used to recreate the orignal databases and tables.

They can identify objects that can cause damage and skip them during the backup.

Which are advantages of the RAID mirroring process? (Choose two)

It backs up both local and remote MySQL servers

It can reload a database on another server, running a different architecture

It recovers data in the event of a hardware failure

It enables the creation of live backups

It creates a SQL script that can be executed on a MySQL server


10.Skill Check Choosing a Backup Strategy

 Which techniques segregate data file backups from MySQL server data files? (Choose three)

replication

OS copy commands

binary logging

MySQL enterprise backup

distributed replicated block device

snapshots

答案: replication distributed replicated block device snapshots

说明: Physical Backup Conditions • The server must not modify data files during backup. – If you copy the live data files, prevent writes to those files: — For InnoDB: MySQL server shutdown is required. — For MyISAM: Lock tables to allow reads but not changes. • You can also minimize the effect to MySQL and applications by using techniques that separate the data files being backed up from the MySQL server: – Snapshots – Replication – DRBD or other methods that copy the whole filesystem

Which are true about warm backups? (Choose two)

Updates are not possible during these backups.

When backups occurs, the server is in an inaccessible mode.

They do not impact system performance.

They permit applications to only read data.

They lock data using multiversion concurrency control (MVCC).

答案: They permit applications to only read data. Updates are not possible during these backups.

说明: Warm Backups • Take place while the data is being accessed – In most cases, the data cannot be modified while the backup is taking place. • Have the advantage of not having to completely lock out end users – However, the disadvantage of not being able to modify the data while the backup is taking place can make this type of backup not suitable for certain applications. • Might result in performance issues because you cannot modify data during the backup. – Updating users may be blocked for a long duration of time.

Which of these use full and binary log backups? (Choose two)

daily/hourly supplemental backups containing live data

backups using mysqldump with the –where option

weekly conditional backups of large transactional tables containing only fixed or historical data

multiple backups of data modifications taken each hour to minimize exposure during each day

backups using mysqlbackup from a replication slave

答案: multiple backups of data modifications taken each hour to minimize exposure during each day

backups using mysqlbackup from a replication slave

说明: More Complex Strategies Combine multiple backup techniques to create more complex strategies: • Example using full and binary log backups: – Nightly backups using mysqlbackup from a replication slave – Multiple binary log backups each hour to minimize exposure during each day • Example using partial backups: – Technique: — mysqldump with –where option — SELECT INTO OUTFILE – Weekly conditional backups of large transactional tables containing only fixed or historical data — Data that does not change, for example, fulfilled orders that have passed the “return by” date – Daily/hourly supplemental backups containing live data

Which methods are used for logical backups? (Choose three)

MySQL enterprise backup

the mysqldump utility

the mysqlpump utility

snapshots

OS copy commands

SQL statements

答案: the mysqldump utility the mysqlpump utility SQL statements

说明: Logical Backups Perform a complete data dump by using SQL statements, mysqldump or mysqlpump. • These data dumps are based on a specific point in time but are the slowest of all the backup techniques. • Advantage: – The process creates a SQL script that you can execute on a MySQL server or data files that you can import into database tables. – You can use the script to reload the database on another host, running a different architecture or different version of MySQL Server. • Disadvantage: – By default (and always for non-InnoDB tables), mysqldump and mysqlpump lock tables during the dump, which prevents users from modifying data during the backup.

Which are true about logical backups? (Choose three)

They use a snapshot capability that is external to MySQL.

They can be restored to MySQL databases on machines with different architectures than the one where the backup was created.

They can be taken to back up both local and remote MySQL servers.

They require the MySQL server to be running during the backups.

They generate SQL statements that can be used to recreate the orignal databases and tables.

They can identify objects that can cause damage and skip them during the backup.

答案: They can be taken to back up both local and remote MySQL servers.

They require the MySQL server to be running during the backups.

They generate SQL statements that can be used to recreate the orignal databases and tables.

Which are advantages of the RAID mirroring process? (Choose two)

It backs up both local and remote MySQL servers

It can reload a database on another server, running a different architecture

It recovers data in the event of a hardware failure

It enables the creation of live backups

It creates a SQL script that can be executed on a MySQL server

答案: It recovers data in the event of a hardware failure

It enables the creation of live backups

Posted in Mysql.

Tagged with , , , .


MySQL 8.0 for Database Administrators OCP 课程习题9

9.Skill Check Optimizing Query Performance

What does the FORCE command do? (Choose three)

analyzes and stores key distribution statistics of a table

reorganizes data evenly across data pages

reclaims file system space used by empty pages

provides the optimizer with extra information about data distribution

forces an index rebuild even though the SQL statement does not change the table structure

produces multiple output rows when analyzing complex queries

Which logs contain queries that the MySQL server executes? (Choose two)

the Slow query log

the General query log

the Binary log

the Relay log

the Audit log

Examine this command which executes successfully: mysql> EXPLAIN SELECT emp_no, title FROM titles \G It generates this output: id: 1 select_type: SIMPLE table: titles partitions: NULL type: eq_ref … Which type of comparison plan is used to access rows?

matching a primary or unique key against a constant at the start of a query

matching one or more referenced values for equality

matching rows in a range that the named index supports

matching a single referenced value for equality

Which storage engines support the mysqlcheck client program? (Choose three)

MEMORY

ARCHIVE

MyISAM

InnoDB

MERGE

BLACKHOLE

Which materially improves overall system performance? (Choose two)

A query that executes once per minute has its response time reduced from 20 ms to 15 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 1 second.

A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 800 ms.

Which properties are true about Invisible Indexes? (Choose three)

They provide an approximation of data distribution in indexed columns.

They analyze and store key distribution statistics of a table.

They can test the effect of removing an index on performance without dropping the index.

They can be applied to primary keys.

They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

What does the EXPLAIN command do? (Choose two)

It generates a query execution plan.

It performs data modification in a query.

It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

It enables efficient access to rows.

It returns data from tables.


9.Skill Check Optimizing Query Performance

What does the FORCE command do? (Choose three)

analyzes and stores key distribution statistics of a table

reorganizes data evenly across data pages

reclaims file system space used by empty pages

provides the optimizer with extra information about data distribution

forces an index rebuild even though the SQL statement does not change the table structure

produces multiple output rows when analyzing complex queries

答案: reorganizes data evenly across data pages reclaims file system space used by empty pages forces an index rebuild even though the SQL statement does not change the table structure

说明: Rebuilding Indexes • Use FORCE: – Forces a rebuild even though the statement does not change the table structure – Reorganizes data more evenly across data pages – Reclaims file system space used by empty pages • Rebuild full text indexes by using OPTIMIZE TABLE: – By default, OPTIMIZE TABLE rebuilds the entire table. – The innodb_optimize_fulltext_only option ensures that OPTIMIZE TABLE rebuilds only the full text index

Which logs contain queries that the MySQL server executes? (Choose two)

the Slow query log

the General query log

the Binary log

the Relay log

the Audit log

答案: the Slow query log the General query log

说明: Identifying Slow Queries • Identify the queries that the server executes: – Use the general query log and slow query log. • Identify the queries that take a long time: – Use the slow query log. – Prioritize these over outlier queries or queries that you execute infrequently. • Find queries that execute many times: – Regularly execute SHOW PROCESSLIST or query the Performance Schema threads table to see currently executing statements and their durations and to identify emerging patterns. – Use sys.statement_analysis to view normalized statements with aggregated statistics. – Use the slow query log with a low threshold to record most statements

Examine this command which executes successfully: mysql> EXPLAIN SELECT emp_no, title FROM titles \G It generates this output: id: 1 select_type: SIMPLE table: titles partitions: NULL type: eq_ref … Which type of comparison plan is used to access rows?

matching a primary or unique key against a constant at the start of a query

matching one or more referenced values for equality

matching rows in a range that the named index supports

matching a single referenced value for equality

答案: matching a single referenced value for equality

说明:

Common type Values The type column indicates the type of comparison used by the optimizer to access rows. • ALL: Full table scan • index: Full index scan • const: Matching a primary or unique key against a constant at the start of a query • eq_ref: Matching a single referenced value (identified by the ref column) for equality • ref: Matching one or more referenced values for equality • range: Matching rows in a range that the named index (key) supports

Which storage engines support the mysqlcheck client program? (Choose three)

MEMORY

ARCHIVE

MyISAM

InnoDB

MERGE

BLACKHOLE

答案: MyISAM InnoDB ARCHIVE

说明: mysqlcheck Client Program • Can be more convenient than issuing SQL statements • Works with InnoDB, MyISAM, and ARCHIVE tables • Three levels of checking: – Table specific – Database specific – All databases • Some mysqlcheck maintenance options: – –analyze: Performs ANALYZE TABLE – –check: Performs CHECK TABLE (default) – –optimize: Performs OPTIMIZE TABLE • Examples: mysqlcheck -uroot -p employees employees salaries mysqlcheck –login-path=admin –analyze –all-databases

Which materially improves overall system performance? (Choose two)

A query that executes once per minute has its response time reduced from 20 ms to 15 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 1 second.

A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 800 ms.

答案: A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

Which properties are true about Invisible Indexes? (Choose three)

They provide an approximation of data distribution in indexed columns.

They analyze and store key distribution statistics of a table.

They can test the effect of removing an index on performance without dropping the index.

They can be applied to primary keys.

They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

答案: They can test the effect of removing an index on performance without dropping the index. They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

说明: Invisible Indexes • Enable you to “hide” indexes from the optimizer • Test the effect of removing indexes on query performance — Without making destructive changes to drop the index — Avoids expensive operation to re-create the index if it is found to be required • Continue to be updated by the server when data is modified by DML statements • Cannot be applied to primary keys • Should be marked as INVISIBLE in CREATE TABLE, ALTER TABLE, or CREATE INDEX statements – Can be “unhidden” by using the VISIBLE keyword

What does the EXPLAIN command do? (Choose two)

It generates a query execution plan.

It performs data modification in a query.

It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

It enables efficient access to rows.

It returns data from tables.

答案: It generates a query execution plan. It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

说明: EXPLAIN Command • Generates a query execution plan

Posted in Mysql.

Tagged with , , .


MySQL 8.0 for Database Administrators OCP 课程习题8

Skill Check Maintaining a Stable System

Which are true about MySQL behaviour when attempting to access a table for which an Exclusive (X) table-level lock is held? (Choose two)

It locks the table to allow shared row-level locking

it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

it allows other transactions to acquire shared locks on the table’s rows

it allows other sessions only to read rows.

Which actions are included in scaling out a database server? (Choose two)

increasing the network bandwidth

adding more servers to the environment

adding more CPU, storage, or main memory resources

writing software (application or storage engine) to use multiple locations

increasing the processing capacity of any single node

Examine this statement which executes successfully: “#mysqladmin –uroot –p kill 14” What does 14 represent in the output?

waiting_trx_id

blocking_trx_id

blocking_pid

waiting_pid

Which are server-level data locks? (Choose two)

Table locks

Storage engine data locks

Row-level locks

Locks that apply to internal resources

Metadata locks

You observe poor system performance. Which commands will display currently executing queries? (Choose two)

SHOW GLOBAL STATUS\G

SHOW PROCESSLIST\G

SHOW MASTER STATUS\G

SELECT * FROM sys.session\G

SELECT * FROM performance_schema.session_status;

Which conditions force an update to the performance baseline? (Choose two)

changes in server connection credentials

increasing data volumes

changes in application usage patterns

server migration to a new subnet

changes in the exploratory configuration


Skill Check Maintaining a Stable System

Which are true about MySQL behaviour when attempting to access a table for which an Exclusive (X) table-level lock is held? (Choose two)

It locks the table to allow shared row-level locking

it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

it allows other transactions to acquire shared locks on the table’s rows

it allows other sessions only to read rows.

答案: it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

Which actions are included in scaling out a database server? (Choose two)

increasing the network bandwidth

adding more servers to the environment

adding more CPU, storage, or main memory resources

writing software (application or storage engine) to use multiple locations

increasing the processing capacity of any single node

答案: adding more servers to the environment writing software (application or storage engine) to use multiple locations

说明: Scaling Up and Scaling Out • Scaling up: – Add more CPU, storage, or main memory resources to increase the processing capacity of any single node. – In general, scaling up is less complicated than scaling out because of the difficulty in writing software that performs well in parallel. • Scaling out: – Add more servers to the environment to enable more parallel processing. – Software (application or storage engine) needs to be written to use multiple locations. – Examples: — Sharded database — Replication for analytics or backups — InnoDB Cluster — NDB storage engine in MySQL Cluster

Examine this statement which executes successfully: “#mysqladmin –uroot –p kill 14” What does 14 represent in the output?

waiting_trx_id

blocking_trx_id

blocking_pid

waiting_pid

答案: blocking_pid

Which are server-level data locks? (Choose two)

Table locks

Storage engine data locks

Row-level locks

Locks that apply to internal resources

Metadata locks

答案: Table locks Metadata locks

说明: How MySQL Locks Rows MySQL locks resources in the following ways: • Server-level data locks: – Table locks – Metadata locks • Storage engine data locks: – Row-level locks – Handled at the InnoDB layer • Mutexes: – Lower-level locks that apply to internal resources rather than to data — Examples: Log files, AUTO_INCREMENT counters, and InnoDB buffer pool mutexes – Used for synchronizing low-level code operations, ensuring that only one thread can access each resource at a time

You observe poor system performance. Which commands will display currently executing queries? (Choose two)

SHOW GLOBAL STATUS\G

SHOW PROCESSLIST\G

SHOW MASTER STATUS\G

SELECT * FROM sys.session\G

SELECT * FROM performance_schema.session_status;

答案: SHOW PROCESSLIST\G SELECT * FROM sys.session\G

说明: Establishing a Baseline • Define what is normal: – The baseline is something to compare against when you encounter a problem. – Over time, changes in the baseline provide you with useful information for capacity planning. • Record operating system metrics: filesystem, memory, and CPU usage – top, iostat, vmstat , sysstat , sar on Linux- or UNIX-based systems – Resource Monitor and Performance Monitor on Windows • Record MySQL status and configuration: – SHOW PROCESSLIST or sys.session to see the running processes – mysqladmin extended-status to see status variables — Use -iseconds –relative to record value deltas. • Profile application use-case response times: – Log in, search, create, read, update, and delete.

Which conditions force an update to the performance baseline? (Choose two)

changes in server connection credentials

increasing data volumes

changes in application usage patterns

server migration to a new subnet

changes in the exploratory configuration

答案: increasing data volumes changes in application usage patterns

说明: Measuring What You Manage • Establish a performance baseline to measure the system’s normal variable values. • After every configuration change, measure the variables again and compare against your baseline. – Hardware and software upgrades – Exploratory configuration changes – Changes in the infrastructure • Measure variables regularly to update the baseline. – Changes in application usage patterns – Data growth over time • Whenever you encounter a problem, compare values with the baseline. – When you precisely define a problem, the solution often becomes obvious obvious

Posted in Mysql.

Tagged with , , .