MySQL管理实战指南

需积分: 10 191 浏览量更新于2024-07-20 收藏 8.26MB PDF 举报

"MySQL Admin Cookbook 是一本专注于MySQL管理和配置的实战指南，包含了从基础到高级的全面知识。书中提供的99个实用配方均基于作者在企业环境中的真实经验，旨在帮助读者掌握MySQL的管理和优化技巧。" 《MySQL Admin Cookbook》这本书深入浅出地探讨了MySQL的管理与配置主题，它涵盖了以下关键知识点： 1. **MySQL安装与配置**：书中会详细介绍如何在不同的操作系统上安装MySQL服务器，并进行基本的配置，包括设置用户权限、安全选项和系统变量。 2. **数据库设计与管理**：学习如何创建、修改和删除数据库，以及如何设计高效的数据表结构，理解索引的原理和使用，以及优化查询性能。 3. **SQL查询与优化**：涵盖复杂的SQL查询语法，包括联接、子查询和窗口函数，以及如何利用EXPLAIN分析查询执行计划，进行SQL性能调优。 4. **备份与恢复**：了解备份策略的重要性，包括全量备份、增量备份和热备份，以及如何在数据丢失时快速恢复。 5. **复制与高可用性**：学习MySQL的主从复制，实现数据同步和高可用性架构，包括配置复制、故障切换和监控。 6. **性能监控与调优**：掌握如何使用工具如MySQL Performance Schema、SHOW STATUS命令和日志分析来监控数据库性能，以及如何调整配置参数提升性能。 7. **安全性管理**：理解MySQL的安全机制，如GRANT和REVOKE语句，以及如何设置用户认证和访问控制，确保数据安全。 8. **存储引擎选择**：探讨不同存储引擎（如InnoDB、MyISAM、Memory等）的特点和适用场景，帮助读者根据需求选择最适合的引擎。 9. **云环境下的MySQL管理**：讨论在云计算平台（如AWS、Google Cloud或Azure）上部署和管理MySQL的注意事项和最佳实践。 10. **故障排查与问题解决**：提供应对常见错误和问题的解决方案，帮助读者快速定位和解决问题。此书不仅提供了详细的步骤指南，还解释了背后的原理，使得读者能够在实际工作中灵活应用。无论你是MySQL的新手还是经验丰富的管理员，都能从这本实践中受益，提升你的MySQL管理技能。

Preface

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes

do happen. If you nd a mistake in one of our books—maybe a mistake in the text or the

code—we would be grateful if you would report this to us. By doing so, you can save other

readers from frustration and help us improve subsequent versions of this book. If you nd

any errata, please report them by visiting http://www.packtpub.com/support,

selecting your book, clicking on the let us know link, and entering the details of your

errata. Once your errata are veried, your submission will be accepted and the errata

will be uploaded on our website, or added to any list of existing errata, under the Errata

section of that title. Any existing errata can be viewed by selecting your title from

http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt,

we take the protection of our copyright and licenses very seriously. If you come across any

illegal copies of our works, in any form, on the Internet, please provide us with the location

address or website name immediately so that we can pursue a remedy.

Please contact us at copyright@packtpub.com with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at questions@packtpub.com if you are having a problem with any

aspect of the book, and we will do our best to address it.

Download at Wow! eBook

WWW.WOWEBOOK.COM

Replication

The basic idea behind replication is to reect the contents of one database server (this

can include all databases, only some of them, or even just a few tables) to more than one

instance. Usually, those instances will be running on separate machines, even though this is

not technically necessary.

Traditionally, MySQL replication is based on the surprisingly simple idea of repeating the

execution of all statements issued that can modify data—not SELECT—against a single master

machine on other machines as well. Provided all secondary slave machines had identical data

contents when the replication process began, they should automatically remain in sync. This

is called Statement Based Replication (SBR).

With MySQL 5.1, Row Based Replication (RBR) was added as an alternative method for

replication, targeting some of the deciencies SBR brings with it. While at rst glance it may

seem superior (and more reliable), it is not a silver bullet—the pain points of RBR are simply

different from those of SBR.

Even though there are certain use cases for RBR, all recipes in this chapter will be using

Statement Based Replication.

While MySQL makes replication generally easy to use, it is still important to understand

what happens internally to be able to know the limitations and consequences of the actions

and decisions you will have to make. We assume you already have a basic understanding of

replication in general, but we will still go into a few important details.

Statement Based Replication

SBR is based on a simple but effective principle: if two or more machines have the same set

of data to begin with, they will remain identical if all of them execute the exact same SQL

statements in the same order.

Executing all statements manually on multiple machines would be extremely tedious and

impractical. SBR automates this process. In simple terms, it takes care of sending all the

SQL statements that change data on one server (the master) to any number of additional

instances (the slaves) over the network.

The slaves receiving this stream of modication statements execute them automatically,

thereby effectively reproducing the changes the master machine made to its data originally.

That way they will keep their local data les in sync with the master's.

One thing worth noting here is that the network connection between the master and its

slave(s) need not be permanent. In case the link between a slave and its master fails, the

slave will remember up to which point it had read the data last time and will continue from

there once the network becomes available again.

Download at Wow! eBook

WWW.WOWEBOOK.COM

Chapter 1

In order to minimize the dependency on the network link, the slaves will retrieve the binary

logs (binlogs) from the master as quickly as they can, storing them on their local disk in

les called relay logs. This way, the connection, which might be some sort of dial-up link,

can be terminated much sooner while executing the statements from the local relay-log

asynchronously. The relay log is just a copy of the master's binlog.

The following image shows the overall architecture:

...

binlog

INSERT (1)

UPDATE (2)

DELETE (3)

UPDATE (4)

...

[mysqld]

"only do (1) +

(4)"

INSERT (1)

UPDATE (2)

DELETE (3)

UPDATE (4)

...

[mysqld]

"only do (2) +

(3)

The relay log is

written to disk as

statements

arrive. It is as big

as the master's

binlog.

The slaves can

be set up to

only apply a

subset of the

relay log.

INSERT (1)

UPDATE (2)

DELETE (3)

UPDATE (4)

...

[mysqld]

.....

......

The binlog

contains all

statements that

modify data on

the master.

The binlog is sent to

all slaves over the

network

individually.

Network Links to the master

may go down temporarily.

Affected slaves will fall behind,

but catch up automatically

when the connection is re-

established.

The over all amount

of data sent is the

binlog size times the

number of slaves.

Statement Based Replication

Master Server

Master DB

Files

INSERT (1)

UPDATE (2)

DELETE (3)

UPDATE (4)

NetWork

Slave Server 1

Slave Server 2

Slave Server n

relay-Log

my.cnf

Slave DB 1

Files

Slave DB 2

Files

Slave DB n

Files

Filtering

In the image you can see that each slave may have its individual conguration on whether it

executes all the statements coming in from the master, or just a selection of those. This can

be helpful when you have some slaves dedicated to special tasks, where they might not need

all the information from the master.

All of the binary logs have to be sent to each slave, even though it might then decide to

throw away most of them. Depending on the size of the binlogs, the number of slaves and

the bandwidth of the connections in between, this can be a heavy burden on the network,

especially if you are replicating via wide area networks.

Download at Wow! eBook

WWW.WOWEBOOK.COM

剩余375页未读，继续阅读

windowsxpwyd

粉丝: 21
资源: 4

MySQL管理实战指南

mysql admin cookbook

MySQL Admin Cookbook - Daniel Schneller.pdf

精通MySQL配置与管理：MySQL Admin Cookbook

MySQL.Admin.Cookbook

Packtpub.MySQL.Admin.Cookbook.Mar.2010.rar

prestashop-cookbook:用于安装 Prestashop 基于 Web 的电子商务解决方案的 Chef Cookbook

PostgreSQL 9 High Availability Cookbook 无水印pdf

MySQL管理实战指南

MySQL管理烹饪书：2010版

MySQL管理员必读：99个实战技巧

最新资源