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

需积分: 10 197 浏览量更新于2024-08-01 收藏 9.61MB PDF 举报

"MySQLAdmin Cookbook" 《MySQL Admin Cookbook》是一本专为MySQL管理员量身打造的指南，涵盖了99个实用的技巧和解决方案，旨在帮助读者掌握MySQL的配置和管理。作者Daniel Schneller和Udo Schwedt凭借他们的专业知识，提供了一系列深入浅出的指导，以帮助读者提升在MySQL管理领域的技能。本书内容广泛，包括但不限于以下几个核心知识点： 1. **MySQL安装与配置**：书中详细介绍了如何在不同操作系统上安装MySQL，包括设置环境变量、配置文件的修改以及优化服务器设置等步骤。 2. **数据库和表管理**：涵盖创建、修改和删除数据库及表的方法，还包括了关于数据类型选择、索引优化和存储引擎的讨论。 3. **数据备份与恢复**：讲解了各种备份策略，如全量备份、增量备份和差异备份，以及如何在紧急情况下恢复数据。 4. **性能监控与优化**：提供了监控MySQL性能的工具和技巧，包括查询优化、内存管理、日志分析和调整系统参数以提高性能。 5. **用户权限与安全**：讨论了如何创建和管理用户账户，设置权限，以及实现安全的数据库访问控制。 6. **复制与集群**：详述了MySQL的主从复制机制，以及如何搭建高可用性和负载均衡的MySQL集群。 7. **故障排查**：分享了诊断和解决常见问题的方法，如错误处理、性能瓶颈定位以及日志分析技巧。 8. **MySQL最新版本特性**：可能包括对MySQL新版本特性的介绍，如InnoDB存储引擎的改进、新的SQL语法或性能增强功能。 9. **自动化与脚本化**：教授如何利用shell脚本或Perl、Python等语言进行MySQL的自动化操作。 10. **最佳实践**：书中还会包含一些实践经验，如数据库设计原则、数据迁移策略以及定期维护的最佳做法。通过阅读这本书，读者不仅可以学到如何有效地管理和维护MySQL数据库，还能了解如何在实际工作中应用这些知识，从而提升系统的稳定性和效率。无论你是初级的数据库管理员还是经验丰富的专业人士，这本《MySQL Admin Cookbook》都能成为你宝贵的参考资料。

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页未读，继续阅读

ls01c

粉丝: 0
资源: 1

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

MySQL Admin Cookbook

mysql cookbook

my sql cookbook

MySQL Admin Cookbook - Daniel Schneller.pdf

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版

最新资源