Shulou(Shulou.com)06/01 Report--

Brothers, have you ever thought about Lao Zhang? recently, Lao Zhang's talent has been limited by the busyness of his work, so there has been no time for more blogging. Today, we finally meet again a few days later (very happy)! Recently, there is a particularly popular court play. I don't know if you have seen it. It is called "Yanxi Strategy". It tells the story of a palace maid who goes through all the way, and finally becomes the story of the emperor's favorite imperial concubine. In addition, I love this kind of subject matter, so I am obsessed with it. It seems to expose the real reason why I am not more knowledgeable. Haha. Court dramas are all intrigues and intrigues between the imperial concubines in the harem, and the cruel fact that there are you without me and me without you. The idea that the winner is the king and the loser is the aggressor seems to have continued from ancient times to this day. We have to decide which one is good and who is bad.

There will also be such problems in the database field. Lao Zhang has been in the open source database circle for many years. MySQL database occupies the top position of open source database, while MongoDB occupies the first place of NoSQL database. Let's take a look at the overall ranking of the database.

Both are the first, and all are always compared. People will often ask such questions as MongoDB4.0 has come out, and support transactions, whether it can replace MySQL in the future. Which database is easy to use, MySQL or MongoDB? Today, Lao Zhang wants to interpret the difference between MySQL and MongoDB in an all-round way through this article. Let the confused brothers understand that there is no one to replace who, only which scene is more suitable for whom.

Let's clarify the difference between the two in turn from the following four directions. Only by getting to know each other better can we make better use of their functionality.

Part one: Overview of database

Let's first take a look at the MySQL database.

Let's learn the characteristics of MySQL database again.

After understanding MySQL, let's take a look at the introduction of MongoDB and its characteristics.

Introduction to MongoDB features:

After learning the first part, we have a certain understanding of both databases; next, we will test the difference between them from the point of view of operation and maintenance.

Part two: daily operation and maintenance management dimension 1. Differences in terms and concepts

As you can see, tables in relational databases are called collections in MongoDB. A line is called a document in MongoDB. So MongoDB is often called document database.

two。 Differences in storage data structures

When designing tables in a relational database, some information needs to be recorded by multiple tables.

In MongoDB, the above three tables can be implemented as the following code.

{_ id: "M416", name: "zhangsu", phone: [1234.5678],.}

Characteristics of MongoDB table design

Data aggregation data nested array structure 3. Startup configuration file format difference

The configuration of the MySQL database is called my.cnf. Let's take a look at how it is recorded.

[client] port = 3306socket = / data/mysql/ mysql.sock [MySQL] prompt= "\ u@db\ R:\ m:\ s [\ d] >" no-auto- Rehash [mysqld] user = mysqlport = 3306basedir = / usr/local/mysqldatadir = / data/mysql/socket = / data/mysql/mysql.sockpid-file = db.pidcharacter-set-server = utf8mb4skip_name_resolve = 1open_files_limit = 65535back_log = 1024max_connections = 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 = 32Mmax_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 files use Yaml format

4. Differences in addition, deletion, modification and search operations

5. Differences in transaction support

But with the advent of MongoDB 4.0, it will support multiple document transactions, and MongoDB will be the only database that can support speed, flexibility, the advantages of JSON document model and ACID data integrity guarantee at the same time.

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

Conclusion: with the increase of transaction support, MongoDB is functionally closer to relational database, but it is still essentially different from relational database: MySQL is a database based on relational model, and it is not as good as MongoDB for various scenarios with changeable data, such as the Internet of things or socialization. MongoDB's JSON model is dynamic and flexible, and the database can upgrade schema changes without going offline. In this scenario, it is especially appropriate to choose MongoDB.

6. Differences in 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 useful backup tool like xtrabackup so far. So generally speaking, logical backup is used to operate.

After we have a deeper understanding of them from the perspective of operation and maintenance, let's explore their deeper differences from the dimension of cluster architecture.

The third part: cluster architecture level 1. Differences at the level of cluster architecture

Let's start from the perspective of MySQL replication, and then introduce the MySQL high availability cluster architecture.

MySQL master-slave copy schematic diagram

Summary of MySQL replication types

Asynchronous replication:

It usually refers to asynchronism, that is, after the master database executes the Commit, it can successfully return to the client after the Binlog log is written to the master database. There is no need to wait for the Binlog log to be sent to the slave database. If the master database goes down, the log may be lost.

Semi-synchronous replication: the semi-synchronous replication function has been introduced after the MySQL5.5 version, and the master-slave server must install the semi-synchronous replication plug-in at the same time to enable this replication function. Under this function, make sure that the binlog content transferred from the main library has been written to your own relay log before notifying the waiting thread above the main library that the operation is complete. If the wait times out and exceeds the time set by the rpl_semi_sync_master_timeout parameter, semi-synchronous replication is turned off and automatically switched to asynchronous replication mode until at least one slave notifies the master library that binlog information has been received.

Multi-source replication:

The so-called multi-source replication is to synchronize the data of multiple master libraries to a slave server, and the slave library will create a pipeline to each master library. In previous versions of MySQL5.7, only one master and one slave, one master and multiple slaves or multi-master and multi-slave replication architecture could only be implemented. If you want to achieve multi-master and one-slave replication, you can only use MariaDB. MySQL version 5.7 has been able to achieve multi-master one-slave replication.

Parallel replication:

Use the parallel replication feature of MySQL5.7. The concept of parallelism has been around since version 5. 6, but parallel replication is based on the library level, or slave_parallel_type=database. However, in this mode, it is only based on the situation of more databases and fewer tables, and is not suitable for the real production environment. In MySQL version 5.7, parallel replication based on group commit is really realized, that is, the master library executes SQL statements in parallel, and the slave library can execute transactions committed by the master library in relay log concurrently through multiple workers threads. To enable parallel replication of MySQL5.7, you can set the parameter slave_parallel_workers > 0 in the slave library and set the newly added slave_parallel_type parameter in version 5.7 to LOGICAL_CLOCK. This parameter has two values, DATABASE and LOGICAL_CLOCK. MySQL5.6 defaults to database.

MySQL High availability Cluster Architecture Classification Chart

MHA:

The purpose of MHA is to maintain the high availability of the master library in MySQL Replication. Its biggest feature is that it can fix the difference logs between multiple slave, finally make all slave keep data consistent, and then choose one of them to act as the new master and point the other slave to it. When the master fails, you can read the position number of the main library binlog by comparing the slave O thread, and select the nearest slave as the alternative primary library (spare tire). Other slave libraries can generate differential relay logs by comparing them with alternative master libraries. Apply the binlog saved from the original master to the alternative master library, and promote the alternative master library to master. Finally, the corresponding differential relay log is applied to the other slave and replicated from the new master.

Double master + keepalived

When it comes to small and medium-sized sizes, it is the easiest to adopt this architecture.

The two nodes can adopt the simple one-master-one-slave mode, or double master mode, and placed in the same VLAN. After the failure of the master node, use the high availability mechanism of keepalived/heartbeat to quickly switch to the slave node.

PXC Cluster:

PXC is a MySQL highly available cluster architecture based on the Galera protocol. Galera products provide highly available cluster solutions for MySQL in the form of Galera Cluster. Galera Cluster is a MySQL cluster that integrates Galera plug-ins. Galera replication is a MySQL data synchronization scheme provided by Codership, which has high availability, easy to expand, and can achieve synchronous data replication, read and write among multiple MySQL nodes, which can ensure the high availability of database services and strong data consistency.

MGR architecture:

MySQL officially launched MySQL Group Replication (MGR) in version 5.7.17. Master1,master2,master3, all members complete their own affairs independently. When the client initiates an update transaction, the transaction is executed locally, and the commit operation of the transaction is initiated after the execution is completed. The resulting replication write set needs to be broadcast and copied to other members before it is actually committed. If conflict detection is successful, the group decides that the transaction can be committed and other members can apply, otherwise it will be rolled back. Ultimately, this means that all members of the group receive the same set of transactions in the same order. Therefore, the members of the group apply the same changes in the same order to ensure strong consistency of data within the group.

Next, let's introduce the replication of MongoDB.

MongoDB replication set:

The three-replica architecture is the most basic replication set architecture, one primary and two standby mode. The primary node accepts read and write requests from the outside world and synchronizes data with the standby node. When the primary node goes down, it will automatically switch to the standby node, which will not affect the online business and prevent a single point of failure.

Automatic switching of MongoDB replication sets

All members of the replica set can accept read operations. By default, however, the application points its read operation to primary.

A replica set can have at most one primary node. When a primary node goes down, the cluster triggers an election to select a new primary node.

In the following three-member node replica set architecture, an election was triggered after the primary outage, and a new secondary node was elected from the remaining two primary nodes.

MongoDB replication set read-write separation settings

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

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

Note when specifying the read preference option: because asynchronous replication is used, replication latency can cause the data on the secondary to be not up-to-date.

By default, all read requests for replication sets are sent to Primary,Driver to route read requests to other nodes by setting up Read Preference.

Primary: default rule, all read requests are sent to Primary

PrimaryPreferred: Primary first. If Primary is unreachable, request Secondary.

Secondary: all read requests are sent to secondary

SecondaryPreferred:Secondary priority. Request Primary when all Secondary is unreachable.

Nearest: the read request is sent to the nearest reachable node (the nearest node is detected by ping)

MongoDB sharding architecture

Sharding is a method of distributing data on multiple machines. MongoDB uses a sharding architecture to help you manage a very large number of datasets and clusters of high-throughput operations.

The business situation of large amount of data and high throughput is a great challenge for a single server. For example, a high query rate may deplete the server's CPU capacity. If the size of the working set exceeds the system memory, the pressure will be put on disk, which is not what we want for IO.

MongoDB supports horizontal scaling through shards.

Summary: there are many kinds of MySQL replication, and there are many cluster architectures in terms of selectivity. But in terms of scale-out ability, the sharding architecture of MongoDB is not as scalable.

In the last part, we will make a final summary of the two databases through the different application scenarios of MySQL and MongoDB.

Part IV: application scene Angle

As I said at the beginning of the introduction to MySQL, the coverage of MySQL is close to 100%. From large BAT, e-commerce platforms, game companies, and even many traditional industries are all moving closer to the direction of MySQL database, to achieve the trend of gradual monopoly. The application of MongoDB has also been applied to various fields, such as games, logistics, e-commerce, content management, social networking, Internet of things, live video, and so on.

Game field: game scene, using MongoDB to store game user information, user equipment, points and other directly embedded documents to facilitate query and update.

two。 Logistics scenario: MongoDB is used to store order information, and the order status is constantly updated in the form of an embedded array of MongoDB. All the changes of the order can be read in a single query.

3. Social scenarios: use MongoDB to store user information and moments information posted by users, and use geographic location index to achieve nearby people, places and other functions

4. Internet of things scenario: use MongoDB to store all connected smart device information, as well as log information reported by devices, and analyze these information in multi-dimensions

For me, when I first came into contact with MySQL in 2009, the first version 2.1 of MongoDB I came into contact with in 2012, for both databases, the palms and backs of the hands are full of meat. When I am lonely, they always accompany me, thanks to the simple happiness that technology brings to us. No matter how it develops in the future, it doesn't matter who will replace whom, just that they all have different characteristics, which makes it more appropriate for us to use them in different application scenarios. There is no court infighting, no intrigue, only the heart of the simplest technology, is the real version of the strategy!

For Lao Zhang, it is easy to write an article, but I really hope it can help those students who are just getting started or want to learn more about the database. The ability is limited, the level is average, where there is no introduction, but also hope everyone Haihan!

Colored egg

On the occasion of the 13th birthday of our favorite 51CTO, as a 51CTO expert blogger and database expert, I launched my own subscription column. Ten-year veterans teach you to practice a set of authentic MySQL dragon eighteen palms.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.