Menu:
pluggable-storage-common-layer:: The Common Database Server Layer
The MySQL pluggable storage engine architecture enables a database professional to select a specialized storage engine for a particular application need while being completely shielded from the need to manage any specific application coding requirements. The MySQL server architecture isolates the application programmer and DBA from all of the low-level implementation details at the storage level, providing a consistent and easy application model and API. Thus, although there are different capabilities across different storage engines, the application is shielded from these differences.
The pluggable storage engine architecture provides a standard set of management and support services that are common among all underlying storage engines. The storage engines themselves are the components of the database server that actually perform actions on the underlying data that is maintained at the physical server level.
This efficient and modular architecture provides huge benefits for those wishing to specifically target a particular application need--such as data warehousing, transaction processing, or high availability situations--while enjoying the advantage of utilizing a set of interfaces and services that are independent of any one storage engine.
The application programmer and DBA interact with the MySQL database through Connector APIs and service layers that are above the storage engines. If application changes bring about requirements that demand the underlying storage engine change, or that one or more storage engines be added to support new needs, no significant coding or process changes are required to make things work. The MySQL server architecture shields the application from the underlying complexity of the storage engine by presenting a consistent and easy-to-use API that applies across storage engines.
File: manual.info.tmp, Node: pluggable-storage, Next: pluggable-storage-common-layer, Prev: pluggable-storage-overview, Up: pluggable-storage-overview
MySQL Server uses a pluggable storage engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server.
Plugging in a Storage Engine
Before a storage engine can be used, the storage engine plugin shared library must be loaded into MySQL using the *note 'INSTALL PLUGIN': install-plugin. statement. For example, if the 'EXAMPLE' engine plugin is named 'example' and the shared library is named 'ha_example.so', you load it with the following statement:
INSTALL PLUGIN example SONAME 'ha_example.so';To install a pluggable storage engine, the plugin file must be located in the MySQL plugin directory, and the user issuing the *note 'INSTALL PLUGIN': install-plugin. statement must have 'INSERT' privilege for the 'mysql.plugin' table.
The shared library must be located in the MySQL server plugin directory, the location of which is given by the 'plugin_dir' system variable.
Unplugging a Storage Engine
To unplug a storage engine, use the *note 'UNINSTALL PLUGIN': uninstall-plugin. statement:
UNINSTALL PLUGIN example;If you unplug a storage engine that is needed by existing tables, those tables become inaccessible, but are still present on disk (where applicable). Ensure that there are no tables using a storage engine before you unplug the storage engine.
File: manual.info.tmp, Node: pluggable-storage-common-layer, Prev: pluggable-storage, Up: pluggable-storage-overview
A MySQL pluggable storage engine is the component in the MySQL database server that is responsible for performing the actual data I/O operations for a database as well as enabling and enforcing certain feature sets that target a specific application need. A major benefit of using specific storage engines is that you are only delivered the features needed for a particular application, and therefore you have less system overhead in the database, with the end result being more efficient and higher database performance. This is one of the reasons that MySQL has always been known to have such high performance, matching or beating proprietary monolithic databases in industry standard benchmarks.
From a technical perspective, what are some of the unique supporting infrastructure components that are in a storage engine? Some of the key feature differentiations include:
Concurrency: Some applications have more granular lock requirements (such as row-level locks) than others. Choosing the right locking strategy can reduce overhead and therefore improve overall performance. This area also includes support for capabilities such as multi-version concurrency control or 'snapshot' read.
Transaction Support: Not every application needs transactions, but for those that do, there are very well defined requirements such as ACID compliance and more.
Referential Integrity: The need to have the server enforce relational database referential integrity through DDL defined foreign keys.
Physical Storage: This involves everything from the overall page size for tables and indexes as well as the format used for storing data to physical disk.
Index Support: Different application scenarios tend to benefit from different index strategies. Each storage engine generally has its own indexing methods, although some (such as B-tree indexes) are common to nearly all engines.
Memory Caches: Different applications respond better to some memory caching strategies than others, so although some memory caches are common to all storage engines (such as those used for user connections or MySQL's high-speed Query Cache), others are uniquely defined only when a particular storage engine is put in play.
Performance Aids: This includes multiple I/O threads for parallel operations, thread concurrency, database checkpointing, bulk insert handling, and more.
Miscellaneous Target Features: This may include support for geospatial operations, security restrictions for certain data manipulation operations, and other similar features.
Each set of the pluggable storage engine infrastructure components are designed to offer a selective set of benefits for a particular application. Conversely, avoiding a set of component features helps reduce unnecessary overhead. It stands to reason that understanding a particular application's set of requirements and selecting the proper MySQL storage engine can have a dramatic impact on overall system efficiency and performance.
File: manual.info.tmp, Node: replication, Next: group-replication, Prev: storage-engines, Up: Top
16 Replication **************
Menu:
replication-notes:: Replication Notes and Tips
Replication enables data from one MySQL database server (the source) to be copied to one or more MySQL database servers (the replicas). Replication is asynchronous by default; replicas do not need to be connected permanently to receive updates from the source. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
Advantages of replication in MySQL include:
Scale-out solutions - spreading the load among multiple replicas to improve performance. In this environment, all writes and updates must take place on the replication source server. Reads, however, may take place on one or more replicas. This model can improve the performance of writes (since the source is dedicated to updates), while dramatically increasing read speed across an increasing number of replicas.
Data security - because data is replicated to the replica, and the replica can pause the replication process, it is possible to run backup services on the replica without corrupting the corresponding source data.
Analytics - live data can be created on the source, while the analysis of the information can take place on the replica without affecting the performance of the source.
Long-distance data distribution - you can use replication to create a local copy of data for a remote site to use, without permanent access to the source.
For information on how to use replication in such scenarios, see *note replication-solutions::.
MySQL 5.7 supports different methods of replication. The traditional method is based on replicating events from the source's binary log, and requires the log files and positions in them to be synchronized between source and replica. The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between source and replica as long as all transactions committed on the source have also been applied on the replica. For more information about GTIDs and GTID-based replication in MySQL, see note replication-gtids::. For information on using binary log file position based replication, see note replication-configuration::.
Replication in MySQL supports different types of synchronization. The original type of synchronization is one-way, asynchronous replication, in which one server acts as the source, while one or more other servers act as replicas. This is in contrast to the synchronous replication which is a characteristic of NDB Cluster (see note mysql-cluster::). In MySQL 5.7, semisynchronous replication is supported in addition to the built-in asynchronous replication. With semisynchronous replication, a commit performed on the source blocks before returning to the session that performed the transaction until at least one replica acknowledges that it has received and logged the events for the transaction; see note replication-semisync::. MySQL 5.7 also supports delayed replication such that a replica deliberately lags behind the source by at least a specified amount of time; see note replication-delayed::. For scenarios where synchronous replication is required, use NDB Cluster (see note mysql-cluster::).
There are a number of solutions available for setting up replication between servers, and 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 *note replication-howto::.
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 can also use a third variety, Mixed Based Replication (MBR). For more information on the different replication formats, see *note replication-formats::.
Replication is controlled through a number of different options and variables. For more information, see *note replication-options::.
You can use replication to solve a number of different problems, including 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 *note 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 potential problems and their resolution, see note replication-notes::. For answers to some questions often asked by those who are new to MySQL Replication, see note faqs-replication::.
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 replicated, see *note replication-implementation::.
File: manual.info.tmp, Node: replication-configuration, Next: replication-implementation, Prev: replication, Up: replication