Without court infighting, the database community's extension of the Jubilee strategy

You old iron, you have not thought Lao Zhang, recently Lao Zhang's talent is the busy limit of work, so has no time more bo, today a few days after we finally meet again (very happy)! Recently, there is a special fire Palace play, do not know whether you have seen, the play name is called "Extension of the plan", is a maid, the way through, and finally become the emperor's most favorite to make the story of the chaise. And I love this kind of theme, so I am very obsessed. (seems to have exposed himself to no more Bo's real reason haha). Court class of drama, are the harem concubines between the specter of my cheat, intrigue, have you without me, I do not have your cruel facts. The winner is the King, and the loser is the idea that seems to have lasted from ancient times to today. Do not want to separate a winner, who is good, who is bad to give up.

In the database field will have this kind of problem, Lao Zhang I am in the Open source database circle for many years. The MySQL database occupies the top spot of the open source database, and MongoDB occupies the first place in the NoSQL database. Let's look at the overall ranking of the database;

Both are first, and all are always compared. Will often be asked about such problems MongoDB4.0 has been published, and support the business, is not the future can replace MySQL. MySQL and MongoDB which database is very useful ah. Today Lao Zhang wants to pass this article, take everybody omni-directional interpretation MySQL and MongoDB difference. Let the confused old iron understand that there is no substitute for who, only which scene more suitable for whom.

We clarify the difference between the two in the following four directions. Only a better understanding of each other can make better use of their functionality.

Part I: Database overview

Let's take a look at MySQL database first.

Then learn the characteristics of MySQL database;

With MySQL, let's look at the introduction of MongoDB and its features in the same vein.

Description of MongoDB Features:

After studying the first part, we have a certain understanding of both databases, then we will examine the difference from the angle of operation and maintenance.

The second part: The daily operation and maintenance Management Dimension 1. Differences in terminology and concepts

The conclusion can be seen that the tables in the relational database are called collections in MongoDB. The line is called a document in MongoDB. So often MongoDB is called a document database.

2. Differences in storage data structures

Tables are designed in a relational database, and some information requires multiple table records.
In MongoDB, the above three tables, it becomes the following this piece of code can be achieved.

{_id:"M416",name:"zhangsu",phone:[1234,5678],.....}

Features of MongoDB table design

1. Data aggregation
2. Data nesting
3. Array structure

3. Startup configuration file Format differences

The configuration of MySQL database is called my.cnf, we look at its record way;

[Client]port = 3306socket =/data/mysql/mysql.sock[mysql]prompt= "\[email protected] \r:\m:\s [\d]>" No-auto-r Ehash[mysqld]user = Mysqlport = 3306basedir =/usr/local/mysqldatadir =/data/mysql/socket =/data/mysql/mysql.sock Pid-file = Db.pidcharacter-set-server = Utf8mb4skip_name_resolve = 1open_files_limit = 65535back_log = 1024max_connecti ONS = 512max_connect_errors = 1000000table_open_cache = 1024table_definition_cache = 1024table_open_cache_instances = 64thread_stack = 512kexternal-locking = Falsemax_allowed_packet = 32msort_buffer_size = 4Mjoin_buffer_size = 4Mthread_ Cache_size = 768#query_cache_size = 0#query_cache_type = 0interactive_timeout = 600wait_timeout = 600tmp_table_size = 32Mm Ax_heap_table_size = 32mslow_query_log = 1slow_query_log_file =/data/mysql/slow.loglog-error =/data/mysql/ Error.loglong_query_time = 0.1server-id = 3306101log-bin =/data/mysql/mybinlogsync_binlog = 1binlog_cache_size = 4Mmax_ Binlog_cache_size = 1gmax_binlog_size = 1gexpire_Logs_days = 7master_info_repository = Tablerelay_log_info_repository = Tablegtid_mode = Onenforce_gtid_consistency = 1log_slave_updates=1binlog_format = Rowrelay_log_recovery = 1relay-log-purge = 1key_buffer_size = 32Mread_buffer_size = 8mread_rnd_buffer_size = 4mbulk_insert_buffer_size = 64m#myisam_sort_buffer_size = 128m#myisam_max_sort_file_size = 10g#myisam_repair_threads = 1lock_wait_timeout = 3600explicit_defaults_for_timestamp = 1innodb_thread_concurrency = 0innodb_sync_spin_loops = 100innodb_spin_wait_delay = 30secure_file_priv= "super_read_only=0transaction_isolation = Repeatable-read#innodb_additional_mem_pool_size = 16minnodb_buffer_pool_size = 1024minnodb_buffer_pool_instances = 8innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_data_file_path = Ibdata1:100M: Autoextendinnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 32minnodb_log_file_size = 2Ginnodb_log_files_in _group = 2innodb_max_undo_log_size = 4ginnodb_io_capacity = 4000innodB_io_capacity_max = 8000innodb_flush_neighbors = 0innodb_write_io_threads = 8innodb_read_io_threads = 8innodb_purge_ Threads = 4innodb_page_cleaners = 4innodb_open_files = 65535innodb_max_dirty_pages_pct = 50innodb_flush_method = O_ directinnodb_lru_scan_depth = 4000innodb_checksum_algorithm = Crc32#innodb_file_format = Barracuda#innodb_file_ Format_max = Barracudainnodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_ file_per_table = 1innodb_online_alter_log_max_size = 4ginternal_tmp_disk_storage_engine = InnoDBinnodb_stats_on_ metadata = 0innodb_status_file = 1[mysqldump]quickmax_allowed_packet = 32M

MongoDB configuration file using Yaml format

4. Differences in the operation of adding or deleting changes

5. Differences in transaction support

But with the advent of MongoDB 4.0, it will support multi-document transactions, when MongoDB will be the only database capable of simultaneously supporting speed, flexibility, JSON document model advantages, and acid data integrity guarantees.

The so-called multi-document transaction can be understood as a multi-line transaction of a relational database. In relational transaction support, there is almost no exception to support the atomicity of operations within the same transaction, that is, either commit all or roll them all back. There can be multiple operations within the same transaction, for multiple tables, or for multiple rows of data within the same table.

Summary: With the increase in transaction support, MongoDB is more closely related to relational database, but with the relationship is still a fundamental difference: MySQL is based on the relational model of the database, a variety of data-changeable scenarios such as Internet of things or social and no mongodb support good. MongoDB JSON model is dynamic and flexible, the database does not have to go offline mode changes to upgrade, in this scenario, the choice of MongoDB will be particularly appropriate.

6. Differences on Backup

MySQL Backup method:

MongoDB Backup method:
Logical Backup and Recovery
1.mongodump
2.mongorestore
3.mongoexport
4.mongoimport

Note: MongoDB does not have a handy backup tool like Xtrabackup so far. So, in general, it's all done using logical backup methods.

From the operational perspective, we have a deeper understanding of them, and we'll start with the dimensions of the cluster architecture to explore their deeper differences.

Part III: Cluster architecture Level 1. Differences at the cluster architecture level

Let's start with the MySQL replication perspective, and then introduce MySQL's highly available cluster architecture

MySQL master-slave replication schematic diagram

MySQL replication kind summary;

Asynchronous replication:
It is usually not stated that asynchronous, that is, after the main library executes a commit, after the main library writes the Binlog log can be successfully returned to the client, without waiting for the Binlog log to send to the slave library, once the main library down, it is possible to lose the log.

Semi-synchronous replication: The semi-synchronous replication feature was introduced after the MySQL5.5 version, and the master and slave servers had to install the semi-synchronous replication plug-in to enable the replication function. Under this function, make sure that the Binlog content that is passed from the library to the main library is written to its own relay log, and the waiting thread on the main library is notified before the operation is complete. If the wait timeout exceeds the time set by the Rpl_semi_sync_master_timeout parameter, the semi-synchronous replication is turned off and automatically converted to asynchronous replication mode until at least one of the libraries has been notified that the main library has received the Binlog information.

Multi-source replication:
The so-called multi-source replication, is to synchronize the data of multiple master libraries to a single slave server, from the library will create a pipeline to each main library. In versions prior to MySQL5.7, only one master-slave, one master-multiple-slave or multiple-master-slave replication architecture could be implemented, and if you wanted to implement multiple master-to-one replication, you could only use MARIADB. MySQL version 5.7 has been able to achieve multiple master one from the replication.

Parallel replication:
Use MySQL5.7 's parallel replication feature. There is a parallel concept in version 5.6, but the parallel replication is based on the library level, which is slave_parallel_type=database. However, in this mode, it is only based on the situation of the multi-Library and less table, and not in the real production environment. In MySQL version 5.7, we really implemented parallel replication based on group submission, which is simply that the main library executes the SQL statement in parallel, and the transaction submitted by the main library in relay log can be executed concurrently from the library through multiple workers threads. To turn on MySQL5.7 parallel replication, you can set the parameter slave_parallel_workers>0 from the library and set the newly added Slave_parallel_type parameter in version 5.7 to Logical_clock. This parameter has database and Logical_clock two values. MySQL5.6 default is database.

MySQL High-availability cluster architecture classification diagram;

MHA:

The purpose of the MHA is to maintain the high availability of the master library in MySQL replication, which is best characterized by the ability to repair the difference logs between multiple slave, eventually keeping all slave data consistent, and then choosing a new master from which to act. and point the other slave to it. When Master fails, you can read the position number of the main library Binlog by comparing the I/O thread between slave and select the closest slave as the alternate main library (spare tire). The other from the library can be compared to the alternate main library to generate differences in the trunk log. Apply the Binlog saved from the original master on the alternate main library and promote the alternate main library to master. Finally, apply the corresponding differential relay log on the other slave and start copying from the new master.

Dual Master +keepalived

This architecture is the easiest to use when it comes to small and medium size.
Two nodes can be used in a simple one-to-one slave mode, or dual-master mode, and placed in the same VLAN, after the master node failure, the use of keepalived/heartbeat high-availability mechanism to quickly switch to the slave node.

PXC cluster:

PXC is a MySQL high-availability cluster architecture based on the Galera protocol. The Galera product is a high-availability clustering solution for MySQL in the Galera cluster way. Galera cluster is a MySQL cluster with integrated Galera plugins. Galera replication is a MySQL data synchronization scheme provided by Codership, which is highly available, easy to expand, and can realize data synchronous replication and read/write between multiple MySQL nodes, which can guarantee high availability of database service and strong data consistency.

MGR Architecture:

MySQL officially launched the group replication in 5.7.17 (MySQL group Replication, referred to as MGR). Master1,master2,master3, all members do their own business independently. When the client initiates an update transaction, the transaction is executed locally, and a commit operation is initiated on the transaction after the execution is completed. It is necessary to broadcast the resulting copy write set to other members before it is actually committed. If the conflict detection succeeds, the group decides that the transaction can be committed, other members can apply, or rollback. Ultimately, this means that all members in the group receive the same set of transactions in the same order. Therefore, members of the group apply the same changes in the same order, ensuring strong consistency of data within the group.

Next we introduce the replication of MongoDB;
MongoDB Replica set:

The three-copy architecture is the most basic replica set of the schema, one master two-standby mode. The master node accepts external read/write requests and synchronizes data to the standby node. When the primary node goes down, it automatically switches to the standby node, without affecting the online service and preventing a single point of failure.

MongoDB replica set auto switch

All members of the replica set can accept read operations. However, by default, the application points its read operation to primary.
The replica set can have up to one primary node, and after the primary node is down, the cluster will trigger an election to elect a new primary node
In the following three member node replica set schema, after the primary outage, an election was triggered, and a new primary node was elected from the remaining two secondary nodes.

MongoDB replica set read/write detach settings

Read preference determines which node the MongoDB client reads data from.

By default, the application points its read operation to the primary node in the replica set.

Note When specifying the Read Preference option: Because of the use of asynchronous replication, replication latency can cause data on secondary to be not up-to-date.

By default, all read requests to a replica set are sent to Primary,driver to route read requests to other nodes by setting up reading preference.

Primary: Default rule, all read requests sent to primary
Primarypreferred:primary priority, if primary unreachable, request secondary
Secondary: All read requests are sent to secondary
Secondarypreferred:secondary priority, request primary when all secondary are unreachable
Nearest: The read request is sent to the nearest available node (the closest node is detected by pinging)

MongoDB Shard Architecture

Sharding is a method of allocating data on multiple machines. MongoDB uses a shard architecture to help you manage clusters of very large numbers of datasets and high throughput operations.
Large data volumes and high-throughput business situations are challenging for a single server. For example, a high query rate might deplete the server's CPU capacity. The working set size exceeds the system memory, then the pressure is given to the disk, which is not what we want to see for IO.
MongoDB supports horizontal scaling through shards.

Summary: There are many types of MySQL replication, and the cluster architecture is more selective. But on the scale, the Shard architecture without MongoDB has strong expansion capability.

In the last part, we will make a final summary of the two databases by using different scenarios of MySQL and MongoDB;

Part IV: Application Scenario Angle

As the opening introduction to MySQL features, MySQL uses nearly 100% coverage. From the large bat, e-commerce platform, game companies, and even many traditional industries are all in the direction of the MySQL database, to achieve a gradual monopoly trend. For MongoDB applications have also been xxx to various fields, such as games, logistics, e-commerce, content management, social, internet of Things, video broadcast and so on.

1. Game field: The game scene, the use of MongoDB storage game user information, user equipment, points, etc. directly embedded in the form of documents stored, convenient to query, update.

2. Logistics scene: Using MongoDB to store order information, order status in the shipping process will be constantly updated, in the form of MongoDB embedded array to store, one query will be able to read all the changes to the order.

3. Social scene: Using MongoDB to store user information, as well as user-published Friends Circle information, through the location index to achieve nearby people, places and other functions

4. IoT scenario: Use MongoDB to store all connected smart device information, as well as the log information reported by the device, and to analyze the information in multiple dimensions

For me, 2009 began to contact MySQL, I contacted in 2012 the first version of MongoDB 2.1, for these two databases is really the palm of the hand is meat. When I am lonely, they are always with me, thanks to the simple joy of technology. No matter what the future of development, there is no so-called who will replace who, just say they each have different characteristics, prompting in different scenarios, we use who is more appropriate. There is no court inside the fight, no intrigues, only the most simple to do the heart of technology, is a realistic version of the extension of the Millennium Raiders!

For Lao Zhang, writing an article is very simple, but really hope to help those who have just started or want to learn more about the database of the students. Limited capacity, the level of general, where there is no introduction to the place, but also hope that everyone Haihan!

Eggs

On the occasion of our favorite 51CTO 13 birthday, as a 51CTO expert blogger, database expert, I have launched my own subscription column ten years veteran teach you to practice a set of authentic MySQL Dragon 18 Palm, from now on, 51CTO Community Anniversary Event (2018.8.31 deadline), subscribe to 2 columns, free to receive hundred-yuan book-based >>> book optional portal

Without court infighting, the database community's extension of the Jubilee strategy