使用MySQL内建复制功能来最佳化可用性(一)

Home

Database

Mysql Tutorial

使用MySQL内建复制功能来最佳化可用性(一)_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 02:04 PM

mysqloneHostuseFunctionAvailablecopyoptimalserver

作者：Michael Tanoviceanu 翻译：limodou

在Soundbreak我们每天24小时不间断地播放实况音频和视频，所以对于MySQL的新增的复制特性，我们
不能做出很令人信服的测试。通过测试我们发现，可以使用这个特性来与备份数据库服务器保持数据同步，
这样当主服务器因为某种原因处理失效时，能够使用备份机处理所有的查询。对于这样的要求，配置两台服
务器并不困难。我将详细讨论整个处理过程，同时讨论一下当主服务器失效时，如何使用PHP来重定向查询。

MySQL内部复制功能是建立在两个或两个以上服务器之间，通过设定它们之间的主-从关系来实现的。其
中一个作为主服务器，其它的作为从服务器。我将详细讨论如何配置两台服务器，将一个设为主服务器，另
一个设为从服务器。并且描述一下在它们之间进行切换的处理过程。我是在MySQL的3.23.23版本上进行的配
置设置过程，并且也是在这个版本上进行的测试。MySQL开发人员建议最好使用最新版本，并且主-从服务器
均使用相同的版本。同时MySQL 3.23版本仍然是beta测试版，而且这个版本可能不能向下兼容。所以因为这
个原因，在实际的网站中，我现在还没有使用这个版本。拥有容错能力具有一个好处是，在不需中断任何查
询的情况下，对服务器进行升级。

第一步：配置主服务器
在这篇文章的剩下篇幅中，我将指定两台服务器。A（IP为10.1.1.1）作为主服务器（简称为主机）。B
（IP为10.1.1.2）作为后备服务器（简称为备机）。

MySQL的复制功能的实现过程为：备机(B)与主机(A)连接，然后读出主机的二进制更新日志，再将发生
的变化合并到自已的数据库中。备机需要一个用户帐号来与主机连接，所以在主机上创建一个帐号，并只给
它FILE权限，如下操作：

GRANT FILE ON *.* TO replicate@10.1.1.2 IDENTIFIED BY 'password';

为了备机能够与主机连接，要在主机上运行'FLUSH PRIVILEGES'，不过不要担心，因为我们将在下面的
步骤中停掉服务器。

现在我们需要主机数据库的一个快照，并且对主机进行配置，允许生成二进制的更新日志。首先编辑
'my.cnf'文件，以便允许二进制更新日志，所以在[mysqld]部分的下面某个地方增加一行：'log-bin'。在
下一次服务器启动时，主机将生成二进制更新日志(名为：<主机名>-bin.<增量序号#>)。为了让二进制更新
日志有效，关闭MySQL服务程序，然后将主机上的所有数据库目录到另一个目录中，接着重新启动mysqld。
请确定得到了所有数据库，否则在进行复制时，如果一个表在主机上存在但在备机上不存在，将因为出错而
退出。现在你已经得到了数据的快照，和一个从建立快照以来的二进制日志，上面记录着任何对数据库的修
改。请注意MySQL数据文件(*.MYD，*.MYI和*.frm)是依赖于文件系统的，所以你不能仅仅进行文件传输，如
从Solaris到Linux。如果你处于一个异种的服务器环境，你将不得不使用mysqldump实用程序或其它的定制
脚本来得到数据快照。

第二步：配置备机
让我们继续。停掉备机上的MySQL服务程序，并且把从主机上拷贝来的数据库目录移到备机上的data目
录下。请确认将目录的拥有者和属组改变为MySQL用户相应值，并且修改文件模式为660(只对拥有者和属组
可读、可写)，目录本身为770(只对拥有者和属组可读、可写和可执行)。

继续。在富掀舳疢ySQL服务程序，确认MySQL工作正常。运行几个select查询(不要update或insert
查询)，看一看在第一步中得到的数据快照是否成功。接着，在测试成功后关掉MySQL服务程序。

在备机上配置需要访问的主机，以便接收主机的更改。所以需要编辑务机上的'my.cnf'文件，在[mysqld]
部分中增加下面几行：

master-host=10.1.1.1
master-user=replicate
master-password=password

在启动备机服务程序后，备机服务程序将查看在'my.cnf'文件中所指定的主机，查看是否有改变，并且
将这些改变合并到自已的数据库中。备机保持了主机的更新记录，这些记录是从主机的'master.info'文件中
接收下来的。备机线程的状态可以通过sql命令'SHOW SLAVE-STATUS'看到。在备机上处理二进制日志中如果
发生错误，都将导致备机线程的退出，并且在*.err的日志文件中生成一条信息。然后错误可以被改正，接着
可以使用sql语句'SLAVE START'来重新启动备机线程。线程将从主机二进制日志处理中断的地方继续处理。

至此，在主机上所发生的数据改变应该已经复制到备机上了，要测试它，你可以在主机上插入或更新一
条记录，而在备机上选择这条记录。

现在我们拥有了从A机到B机的这种主-从关系，这样当A机可能当机的时候，允许我们将所有的查询重定
向到B机上去，但是当A机恢复时，我们没有办法将发生的改变恢复到A机中去。为了解决这个问题，我们创建
从B机到A机的主-从关系。

自：PHPBuilder.com

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AM

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

SQL Commands in MySQL: Practical ExamplesApr 14, 2025 am 12:09 AM

SQL commands in MySQL can be divided into categories such as DDL, DML, DQL, DCL, etc., and are used to create, modify, delete databases and tables, insert, update, delete data, and perform complex query operations. 1. Basic usage includes CREATETABLE creation table, INSERTINTO insert data, and SELECT query data. 2. Advanced usage involves JOIN for table joins, subqueries and GROUPBY for data aggregation. 3. Common errors such as syntax errors, data type mismatch and permission problems can be debugged through syntax checking, data type conversion and permission management. 4. Performance optimization suggestions include using indexes, avoiding full table scanning, optimizing JOIN operations and using transactions to ensure data consistency.

How does InnoDB handle ACID compliance?Apr 14, 2025 am 12:03 AM

InnoDB achieves atomicity through undolog, consistency and isolation through locking mechanism and MVCC, and persistence through redolog. 1) Atomicity: Use undolog to record the original data to ensure that the transaction can be rolled back. 2) Consistency: Ensure the data consistency through row-level locking and MVCC. 3) Isolation: Supports multiple isolation levels, and REPEATABLEREAD is used by default. 4) Persistence: Use redolog to record modifications to ensure that data is saved for a long time.

MySQL's Place: Databases and ProgrammingApr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

MySQL: From Small Businesses to Large EnterprisesApr 13, 2025 am 12:17 AM

MySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.

What are phantom reads and how does InnoDB prevent them (Next-Key Locking)?Apr 13, 2025 am 12:16 AM

InnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.

See all articles