MySQL 实验室 因为专注,所以专业。

  • 首页
  • 博客
  • 下载
  • 文档
  • 工具
  • 知识库
  • 培训及服务
  • MySQL 5.1 Reference Manual :: 16 Replication
    • MySQL 5.1 Reference Manual
    • Preface, Notes, Licenses
    • 1 General Information
    • 2 Installing and Upgrading MySQL
    • 3 Tutorial
    • 4 MySQL Programs
    • 5 MySQL Server Administration
    • 6 Backup and Recovery
    • 7 Optimization
    • 8 Language Structure
    • 9 Internationalization and Localization
    • 10 Data Types
    • 11 Functions and Operators
    • 12 SQL Statement Syntax
    • 13 Storage Engines
    • 14 High Availability and Scalability
    • 15 MySQL Enterprise Monitor
    • 16 Replication
    • 17 MySQL Cluster NDB 6.X/7.X
    • 18 Partitioning
    • 19 Stored Programs and Views
    • 20 INFORMATION_SCHEMA Tables
    • 21 Connectors and APIs
    • 22 Extending MySQL
    • A MySQL 5.1 Frequently Asked Questions
    • B Errors, Error Codes, and Common Problems
    • C MySQL Change History
    • D Restrictions and Limits
    • Index
    • Standard Index
    • C Function Index
    • Command Index
    • Function Index
    • INFORMATION_SCHEMA Index
    • Transaction Isolation Level Index
    • JOIN Types Index
    • Operator Index
    • Option Index
    • Privileges Index
    • SQL Modes Index
    • Status Variable Index
    • Statement/Syntax Index
    • System Variable Index

    Chapter 16. Replication

    Table of Contents     [+/-]

    16.1. Replication Configuration     [+/-]
    16.1.1. How to Set Up Replication
    16.1.2. Replication Formats
    16.1.3. Replication and Binary Logging Options and Variables
    16.1.4. Common Replication Administration Tasks
    16.2. Replication Implementation     [+/-]
    16.2.1. Replication Implementation Details
    16.2.2. Replication Relay and Status Files
    16.2.3. How Servers Evaluate Replication Filtering Rules
    16.3. Replication Solutions     [+/-]
    16.3.1. Using Replication for Backups
    16.3.2. Using Replication with Different Master and Slave Storage Engines
    16.3.3. Using Replication for Scale-Out
    16.3.4. Replicating Different Databases to Different Slaves
    16.3.5. Improving Replication Performance
    16.3.6. Switching Masters During Failover
    16.3.7. Setting Up Replication Using SSL
    16.4. Replication Notes and Tips     [+/-]
    16.4.1. Replication Features and Issues
    16.4.2. Replication Compatibility Between MySQL Versions
    16.4.3. Upgrading a Replication Setup
    16.4.4. Replication FAQ
    16.4.5. Troubleshooting Replication
    16.4.6. How to Report Replication Bugs or Problems

    Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not to connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

    The target uses for replication in MySQL include:

    • Scale-out solutions - spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.

    • Data security - because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.

    • Analytics - live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.

    • Long-distance data distribution - if a branch office would like to work with a copy of your main data, you can use replication to create a local copy of the data for their use without requiring permanent access to the master.

    Replication in MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster (see Chapter 17, MySQL Cluster NDB 6.X/7.X).

    There are a number of solutions available for setting up replication between two servers, but the best method to use depends on the presence of data and the engine types you are using. For more information on the available options, see Section 16.1.1, “How to Set Up Replication”.

    There are two core types of replication format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You may also use a third variety, Mixed Based Replication (MBR). For more information on the different replication formats, see Section 16.1.2, “Replication Formats”. From MySQL 5.1.12 to MySQL 5.1.28, mixed format is the default. Beginning with MySQL 5.1.29, statement-based format is the default.

    Replication is controlled through a number of different options and variables. These control the core operation of the replication, timeouts, and the databases and filters that can be applied on databases and tables. For more information on the available options, see Section 16.1.3, “Replication and Binary Logging Options and Variables”.

    You can use replication to solve a number of different problems, including problems with performance, supporting the backup of different databases, and as part of a larger solution to alleviate system failures. For information on how to address these issues, see Section 16.3, “Replication Solutions”.

    For notes and tips on how different data types and statements are treated during replication, including details of replication features, version compatibility, upgrades, and problems and their resolution, including an FAQ, see Section 16.4, “Replication Notes and Tips”.

    For detailed information on the implementation of replication, how replication works, the process and contents of the binary log, background threads and the rules used to decide how statements are recorded and replication, see Section 16.2, “Replication Implementation”.

    MySQL Enterprise.  The MySQL Enterprise Monitor provides numerous advisors that provide immediate feedback about replication-related problems. For more information, see http://www.mysql.com/products/enterprise/advisors.html.