MySQL管理实战指南

需积分: 3 48 浏览量更新于2024-07-29 收藏 10.45MB PDF 举报

"MySQL Admin Cookbook" 《MySQL Admin Cookbook》是一本专为MySQL数据库管理员设计的实用指南，由Daniel Schneller和Udo Schwedt合著，旨在帮助读者掌握MySQL的配置和管理技巧。这本书包含了99个精心挑选的“食谱”，即解决实际问题的步骤和策略，覆盖了MySQL管理中的各种常见和复杂场景。在本书中，读者可以学习到如何： 1. 安装与升级MySQL：了解如何在不同操作系统上安装MySQL，包括设置环境变量、配置网络连接和选择适合的存储引擎。 2. 数据库和表的管理：创建、修改和优化数据库结构，如设计高效的数据表，使用索引提升查询性能，以及备份和恢复数据。 3. 权限与安全：设置用户账户，管理权限，以及实施安全策略，确保数据库系统的安全运行。 4. 性能调优：监控MySQL的性能，分析慢查询，调整系统参数以优化服务器性能。 5. 复制与高可用性：配置MySQL的主从复制，实现数据同步，以及构建高可用性和故障转移解决方案。 6. 日志与监控：理解和利用MySQL的日志系统，如查询日志、二进制日志，以便进行故障排查和审计。 7. 备份与恢复：规划和执行数据库备份策略，学习如何快速恢复数据，包括热备份和增量备份。 8. 高级主题：探讨分区、触发器、存储过程等高级功能的使用，以及如何处理大数据和分布式环境。 9. 故障诊断与排除：学习如何识别和解决常见的MySQL问题，如内存管理、锁冲突和死锁。 10. 最佳实践：提供关于数据库设计、性能监控、资源管理和维护的最佳实践建议，帮助读者在日常工作中提升效率。该书是针对MySQL管理员的专业参考书，无论你是新手还是经验丰富的DBA，都可以从中获得宝贵的实战经验和技巧。虽然出版于2010年，但许多核心概念和最佳实践仍然适用于当前版本的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页未读，继续阅读

wzc1981

粉丝: 2
资源: 11

MySQL管理实战指南

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

MySQL.Cookbook.2nd.Edition

MySQL Admin Cookbook

mysql admin cookbook

MySQL Admin Cookbook - Daniel Schneller.pdf

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

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

PostgreSQL 9 High Availability Cookbook 无水印pdf

MySQL管理实战指南

MySQL管理烹饪书：2010版

最新资源