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

The new week, Lao Zhang (superZS) to see you again, we have to face the fast pace of life and disgusting work! Now most people choose to relax their way is to play games, the most prominent may be the mobile game "Arena of Valor".

It is said that this game is played by people in their 70s, down to primary school students, and Lao Zhang, too. The Rank is pitifully low (PS: just played recently), just Baiyin.

At that time, I also wanted others to take me, saying that as long as you give me how much money, you can quickly take you from stubborn bronze to the strongest king, but in the end, I chose to save money in the choice of pretending and saving money. I thought to myself, just play a game, but you play longer than me, skillful, experienced, as long as I spend a little more time, definitely better than you.

Having said that, Lao Zhang, I don't like to waste my time on games, but I like to take the time to write blog posts and share more knowledge with you. Because I think technology focuses on communication, communication, only learn more from each other, can progress faster! Since playing a game can be divided into stages, then what we work in the field of technology is even more hierarchical.

Although I can't teach you how to improve yourself in the game, I can share with you to improve yourself in the database field. Be a great god that everyone admires, a strongest king!

Exclusive New course launch > > in-depth Analysis of MySQL Architecture and practical DBA Video course

The knowledge context of MySQL database can be divided into four modules:

● MySQL architecture

● MySQL backup recovery

● MySQL High availability Cluster

● MySQL optimization.

From the four modules, seven parts are extracted for everyone's analysis.

Part one: stubborn Bronze

Rookies who have just come into contact with the MySQL database should first understand the common operation commands of MySQL and the characteristics of each version of MySQL. What functional and performance improvements have been experienced in the span of each release from official 5.1 to MySQL 5.7.

New features reference blog > > http://sumongodb.blog.51cto.com/4979448/1949800

Of course, at this stage, we also need to learn how to install the MySQL database and the use of some common commands.

Summary of common commands:

< backup.sql; 在这里举两个典型案例，MySQL 5.6 和 MySQL 5.7 在初始化数据时候的安装差异。 MySQL 5.6：初始化数据时需要进到家目录的 script 目录下 执行： /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql --defaults-file=/etc/my.cnf --user=mysql 此时数据库密码为空。 MySQL 5.7：初始化数据时需要进到家目录的 bin 目录下 执行： /usr/local/mysql/bin/mysqld --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql/ --initialize 已然已经废弃了使用 mysql_install_db 这个命令进行初始化数据的操作了。 注：--initialize 会自动生成密码在 error log 里面。如果加 --initialize-insecure 密码为空 第二部分：秩序白银篇 大概了解完 MySQL 的安装，我们来介绍下 MySQL 的体系结构。先看下官方版本的图：

We can see from the figure that the MySQL architecture is divided into two parts (mysql server layer + mysql storage engine layer)

The process of entering the database through a sql statement can be divided into eight small parts as shown in the following figure:

1-6 goes through the mysql-server layer part, and 7 is the storage engine layer part of our database. So it throws out the difference that we need to learn from each storage engine.

Only the differences between the two longest-used Innodb and Myisam are introduced here.

1. Transaction support is different (innodb supports transactions, myisam does not)

two。 Lock granularity (innodb row lock application, myisam table lock)

3. Storage space (innodb caches both index files and data files, myisam can only cache index files)

4. Storage structure

(myisam: the data file has the .MYD myData extension, and the index file has the .MYI myIndex extension)

(innodb: .Ibd) all tables are saved in the same data file.

5. Count the number of record rows

(myisam: the total number of rows that hold the table, which will be fetched directly by select count (*) from table;)

(innodb: if the total number of rows of the table is not saved, select count (*) from table; will traverse the entire table, consuming a lot.)

The third part: glory Gold.

If you want to learn database well, you must first learn architecture. The architecture is like the foundation of a house. If the foundation is unstable, it is impossible to build a tall building. Since there is little difference between the versions of the mysql server layer, I focus on the storage engine layer part. Let's take a look at the architecture diagram of Innodb:

We need to learn to divide this architecture into three main parts: memory composition, thread work, and disk storage.

You need to learn in the memory composition: the database memory module is made up of the main memory of data_buffer,index_buffer,insert buffer,redo log buffer,double writer buffer.

The three features of Innodb storage engine are: write twice, adaptive hash index, and insert buffer.

1. Double write (write twice) function: it can guarantee that after the page is damaged, a copy can be recovered directly.

2. Adaptive hash index (adaptive hash index) function: the Innodb storage engine monitors the lookup of indexes on the table, and establishes a hash index if it is observed that the establishment of a hash index can lead to a speed increase. The speed of reading and writing has also improved.

3. Insert buffer (insert buffer) function: for inserting ordinary indexes, random IO is changed into sequential IO, and then merged and inserted into disk.

-- main memory module-- > disk refresh mechanism:

A. Binlog cache--- > binlog file

Controlled by parameter sync_binlog

This parameter is very important for MySQL system. It not only affects the performance loss caused by Binlog to MySQL, but also affects the integrity of data in MySQL. The various settings for the "sync_binlog" parameter are described as follows:

● sync_binlog=0, when a transaction is committed, MySQL does not do disk synchronization instructions such as fsync to refresh the information in binlog_cache to disk, but let Filesystem decide when to synchronize, or synchronize to disk after the cache is full.

● sync_binlog=n, after every n transaction commits, MySQL will issue a disk synchronization instruction such as fsync to force the data in binlog_cache to be written to disk.

In MySQL, the default setting of the system is sync_binlog=0, that is, do not do any mandatory disk refresh instructions, this time the performance is the best, but the risk is also the greatest. Because once the system Crash, all binlog information in binlog_cache will be lost.

When set to "1", it is the safest setting with the greatest performance loss. Because when set to 1, even if the system Crash, at most one outstanding transaction in the binlog_cache is lost, without any material impact on the actual data.

From past experience and related tests, for systems with high concurrent transactions, the write performance gap between systems with "sync_binlog" set to 0 and 1 may be as high as 5 times or more.

B. redo log buffer--- > redo log

Controlled by parameter innodb_flush_log_at_trx_commit

There are three parameter values:

0:log buffer will be written to log file once a second, and the flush (brush to disk) operation of log file will take place at the same time. In this mode, writing to disk is not actively triggered when the transaction commits.

1: every time a transaction commits, mysql will write the data of log buffer to log file and flush (swipe to disk). This mode is the system default.

2: mysql writes log buffer data to log file each time the transaction is committed, but the flush (flush to disk) operation does not occur at the same time. In this mode, MySQL performs flush (flush to disk) operation once per second.

c. Dirty pages data_buffer---- > data files

1. Controlled by parameter innodb_max_dirty_pages_pct: it represents the proportion of dirty page refresh to buffer_pool; it is recommended to adjust it to 25-50%.

two。 Log switching will generate checkpoint checkpoint, which can induce refresh of dirty pages.

-- Thread work:

Four IO threads of Innodb: write thread,read thread,insert buffer thread,redo log thread

Master thread is the main thread of the database, with the highest priority, which contains 1s and 10s operations on the database.

Page cleaner thread: a thread that helps refresh dirty pages. Version 5. 7 can add more.

Purge thread: delete useless undo pages. Default is 1, and the maximum can be adjusted to 32.

The main data files are also what we need to learn:

Parameter files: MySQL version 5.6my.cnf and MySQL version 5.7my.cnf

Here are two templates: the parameters tested by Lao Zhang according to the production environment. Among them, you can adjust the innodb_buffer_pool size appropriately according to the real memory. (50-80% of physical memory is recommended)

[client] port = 3306socket = / tmp/mysql.sock#default-character-set=utf8 [mysql] # default-character-set= UTF8 [mysqld] port = 3306socket = / tmp/mysql.sockbasedir = / usr/local/mysqldatadir = / data/mysqlopen_files_limit = 3072back_log = 103max_connections = 512max_connect_errors = 100000table_open_cache = 512external-locking = FALSEmax_allowed_packet = 128Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 51query_cache_size = 32Mtmp _ table_size = 96Mmax_heap_table_size = 96Mslow_query_log = 1slow_query_log_file = / data/mysql/slow.loglog-error = / data/mysql/error.loglong_query_time = 0.05server-id = 1323306log-bin = / data/mysql/mysql-binsync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 128Mmax_binlog_size = 1024Mexpire_logs_days = 7key_buffer_size = 32Mread_buffer_size = 1Mread_rnd_buffer_size = 16mbulk _ Insert_buffer_size = 64Mcharacter-set-server=utf8default-storage-engine=InnoDBbinlog_format=row#gtid_mode=on#log_slave_updates=1#enforce_gtid_consistency=1interactive_timeout=100wait_timeout=100transaction_isolation = REPEATABLE-READinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 1434Minnodb_data_file_path = ibdata1:1024M:autoextendinnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 256Minnodb_log_files_in_group = 2innodb_max_dirty_pages_pct = 50innodb_file_per_table = 1innodb_locks_unsafe_for_binlog = 0 [mysqldump] quickmax_allowed_packet = 32m

Parameter file of MySQL version 5.7:

[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.sockcharacter-set-server = utf8mb4skip_name_resolve = 1open_files_limit = 65535back_log = 1024max_connections = 500max_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 = 768query_cache_size = 0query_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/mysql-binlogsync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 1Gmax_binlog_size = 1Gexpire_logs_days = 7gtid_mode = onenforce_gtid_consistency = 1log_slave_updatesbinlog_format = rowrelay_log_recovery = 1relay-log-purge = 1key_buffer_size = 32Mread_buffer_size = 8Mread_rnd_buffer_size = 4Mbulk_insert_buffer_size = 64Mlock_wait_timeout = 3600explicit_defaults_for_timestamp = 1innodbroomthread _ Concurrency = 0innodb_sync_spin_loops = 100innodb_spin_wait_delay = 30transaction_isolation = REPEATABLE-READinnodb_buffer_pool_size = 1024Minnodb_buffer_pool_instances = 8innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_data_file_path = ibdata1:1G:autoextendinnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 32Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 2innodbroommaxroomundo _ 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 = crc32innodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodbroomfileroomperper1 Table = 1innodb_online_alter_log_max_size = 4Ginternal_tmp_disk_storage_engine = InnoDBinnodb_stats_on_metadata = 0innodb_status_file = 1innodb_status_output = 0innodb_status_output_locks = 0performance_schema = 1performance_schema_instrument ='% = on' [mysqldump] quickmax_allowed_packet = 32m

-- Log file:

1. Error log error log: records the process of starting, running, and shutting down mysql.

two。 Full log general log: the query log records all requests to the mysql database, regardless of whether the requests were executed correctly or not.

3. Binary log binlog: records all operations that make changes to the database. But operations such as select and show are not included.

4. Relay log relay log: master-slave synchronization. The slave database needs to record the logs transferred from the master library to its own relay log.

5. Slow log slow log: all sql statements that run longer than a certain value are recorded in the slow log file.

-- you should also learn clearly the table design of the database.

For the selection of data types, please refer to the official documents:

Https://downloads.mysql.com/docs/licenses/mysqld-5.7-com-en.pdf

-- defragmentation of data

The cause of the debris:

1. This is mainly due to the deletion of large tables.

two。 Secondly, new data is inserted randomly, which may result in a large number of fragments in the secondary index.

The method of defragmentation:

1. Back up data tables, import and export, delete old tables

two。 Execute alter table table_name engine=innodb

-- collect statistics

Ensure the accuracy of statistical information in order to ensure the accuracy of our sql implementation plan. Collection method:

1. Restart the mysql service

two。 Traversing tables table

-- Learning partition tables

Types of partition tables:

1. Range

2. List

3. Hash

4. Key

-- learn to understand the index

It can be roughly divided into:

1. How to view indexes in a database: show index from table_name

two。 Learn to view the selectivity of database indexes: the higher the selectivity of select count (distinct C1) / count (*) from table_name;, the more suitable it is to create indexes.

3. In the process of creating an index, learn to view the execution plan. Internal heart method: first look at the type value, then look at key, then look at rows, and finally look at extra

Mysql > use test; Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > explain select * from sbtest +-- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -- + | 1 | SIMPLE | sbtest | ALL | NULL | 98712 | NULL | +-- -+

4. Understand the benefits of creating an index

a. Improve the efficiency of data retrieval

b. Improve the efficiency of aggregation function

c. Improve sorting efficiency

d. You can avoid returning to the table on an individual basis.

e. Reduce the number of rows scanned when multiple tables are associated

f. Primary key and unique index can be used as constraints

-- study of affairs

First, you need to know the four characteristics of a transaction (ACID):

a. Atomicity (Atomicity)

The atomicity of a transaction means that all operations contained in the transaction are either done or not done, ensuring that the database is consistent.

b. Consistency (Consistency)

Consistency means that the data in the database must meet the business rule constraints before and after the transaction operation.

c. Isolation (Isolation)

Isolation is the ability of a database to allow multiple concurrent transactions to read, write and modify data at the same time. Isolation can prevent data inconsistencies caused by cross execution when multiple transactions are executed concurrently.

d. Persistence (Durability)

After the transaction is finished, the modification of the data is permanent.

Be familiar with the four transaction isolation levels of the mysql database:

1. Read uncommitted (RU) read not submitted:

In a transaction, uncommitted changes from other transactions can be read

2. Read committed (RC) read submitted:

In a transaction, changes that have been committed by other transactions can be read

3. Repetable read, (RR) can be read repeatedly:

In a transaction, the data seen at the beginning of the transaction can be read repeatedly until the end of the transaction, and there will be no change.

4. Serializable (serial read):

Even if you need to acquire a table-level shared lock for each read and add a table-level exclusive lock for each write, the reads and writes between the two sessions will block each other.

Personal advice: for the transaction system of the site, we try to use a higher transaction level of RR; for some portal sites we use RC on it.

For Innodb locks, there are three locking algorithms by default:

1. Record

2. Gap lock

3. Next-key lock

The default locking algorithm is the next-key lock gap lock to ensure that there is no phantom reading.

Database character set

First learn to view the character set of the database:

[root@node3 ~] # mysql-uroot-proot123mysql > show variables like'% char%' +-- +-- + | Variable_name | Value | +-- -- + | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | / usr/local/mysql/share/charsets/ | +-| -+-+ 8 rows in set (0.00 sec)

If you want to ensure that Chinese garbled will not occur, you must meet the following three points:

1. Connection terminal must be UTF8

two。 The operating system must be UTF8

3. Database must be UTF8

If the three are unified, there will be no problem of garbled codes in Chinese.

-- Management of database permissions

1. The permission application process should be standardized and reasonable.

two。 Both testing and formal environments should strictly control the write permissions of the database, and it is forbidden to assign permissions such as create,alter to developers. And the read permission is separated from the external business service.

3. When the leader needs permission, ask for the purpose and send an email explanation. Try to be managed entirely by DBA.

4. The privileged account all privileges must be controlled by DBA personnel

5. For single library and single user, it is forbidden to give me one user account to manage multiple libraries.

6. Read-only account select, which can be used later with read_only in the master-slave architecture.

7. Prohibit root users from using as remotely connected users

Part IV: the noble platinum chapter

DBA staff, if you can not guarantee the integrity of the data, all operations are futile. So the importance of backup can be imagined. Although backup will not improve our business, it will increase our costs. However, without the integrity of the data, it is impossible to guarantee the normal operation of our online business. Is the last straw when the data is corrupted.

Backup is divided by method: cold backup and hot backup

Cold backup: the database is closed, affecting the business. System-level file copy (PS: now this is basically obsolete)

Hot backup: database online backup, does not affect the progress of existing business.

In the hot backup, it is divided into:

1. Logical backup

A. Mysqldump

B. Mydumper

C. Mysqlpump (mysql 5.7)

two。 Bare file backup

The physical bottom layer goes to the copy file, and the tool is percona-xtrabackup.

According to the content, it can be divided into full backup and incremental backup.

The two most commonly used methods in production:

1. Mysqldump

2. Xtrabackup

Detailed description of mysqldump parameters:

-- single-transaction is used to ensure the consistency of innodb backup data and is used with the RR isolation level. When a transaction is initiated, a snapshot version is read, and the data committed after the start of the transaction will not be read until the backup ends. (important)-Q,-quick plus the SQL_NO_CACHE flag to ensure that data in the cache is not read-- l-- lock-tables initiates a READ LOCAL LOCK lock that does not prevent reading or new data insertion-- master-data values 1 and 2, if the value is equal to 1, a CHANGE MASTER statement is added (later configuration builds master-slave architecture) if the value is equal to 2 Comments will be added before the CHANGE MASTER statement (later configuration builds master-slave architecture)-c,-- complete-insert Export the complete sql statement-djinomomuri data; do not export the data, but only guide the table structure-tmai Murphy create where=name; guide only the data, not the table structure-w,-- data; export the desired data according to conditions

Back up the database:

Back up a single database or a specified table in a single database: mysqldump [OPTIONS] database [tb1] [tb2]... Back up multiple databases: mysqldump [OPTIONS]-databases [OPTIONS] DB1 [DB2 DB3...] Backup all databases: mysqldump [OPTIONS]-all-databases [OPTIONS] use the mysql command to restore data: mysql-uroot-proot23 db_name < table_name.sqlxtrabackup backup principle Analysis: for Innodb, it is based on Innodb's crash recovery function for backup.

Introduction to the principle of database crash recovery: Innodb maintains a redo log, which records the real modification information of all data in Innodb. When the database is restarted, redo log will roll forward all committed transactions and roll back all uncommitted transactions to ensure data integrity at the moment of downtime.

XtraBackup does not lock the table when backing up, but copies the InnoDB data page by page. At the same time, XtraBackup has another thread monitoring the transactions log, copying the changed log pages away once the log changes. Stop copying logfile after all data files have been copied.

Common commands:

First of all, you need to create a backup directory: / opt/data/innobackupex-- no-timestamp-- defaults-file=/etc/my.cnf-- user root-- socket=/tmp/mysql.sock-- password root123 / opt/data/all-20170719-bak Note-- no-timestamp the meaning of this parameter: you do not need the system to create a time directory. You can name it yourself.

Analysis of the principle of additional equipment

There is a file, xtrabackup_checkpoints, in both the full and incremental backup files that records the LSN of the checkpoint when the backup completes. When making a new incremental backup, XtraBackup compares whether the LSN of each page in the tablespace is greater than the LSN completed by the last backup, and if so, backs up the page and records the LSN of the current checkpoint.

Additional information of July 20

[root@node3 all-20170720-incr] # cat xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 267719862to_lsn = 267720940last_lsn = 267720940compact = 0

Additional information of July 21

[root@node3 all-20170721-incr2] # cat xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 267720940to_lsn = 267721260last_lsn = 267721260compact = 0

You can see that the end of July 20 lsn (to_lsn) is the beginning of July 21 lsn (from_lsn).

Add common commands:

Incremental file of July 20th

. / innobackupex-- no-timestamp-- user root-- socket=/tmp/mysql.sock-- password root123-- defaults-file=/etc/my.cnf-- incremental--incremental-basedir=/opt/data/all-20170719-bak / data/xtrabackup/all-20170720-incr

Note #-incremental-basedir: used to identify where the current addition begins

Incremental file of July 21

. / innobackupex-- no-timestamp-- user root-- socket=/tmp/mysql.sock-- password root123-- defaults-file=/etc/my.cnf-- incremental--incremental-basedir=/data/xtrabackup/all-20170720-incr / data/xtrabackup/all-20170721-incr2

Full backup set = complete + additional 1 + additional 2

Restore operation:

Innobackupex-- user root-- socket=/tmp/mysql.sock-- password root123-- defaults-file=/etc/my.cnf-- apply-log-- redo-only + full innobackupex-- user root-- socket=/tmp/mysql.sock-- password root123-- defaults-file=/etc/my.cnf-- apply-log-- redo-only-- incremental-dir= additional 1innobackupex-- user root-- socket=/tmp/mysql.sock-- password root123-- defaults-file=/etc / my.cnf-apply-log-redo-only complete-incremental-dir= additional 2innobackupex-- user root-- socket=/tmp/mysql.sock-- password root123-- defaults-file=/etc/my.cnf-- apply-log + complete

Note #-redo-only represents only roll forward operations

#-apply-log application log to ensure data integrity

Part V: eternal Diamond

Let me introduce to you the most commonly used mainstream MySQL high availability architecture in the enterprise.

Introduction from two aspects

1. Based on master-slave replication

a. Dual-host Mmurm keepalived

B. MHA

two。 Based on Galera protocol

Mmurm M keepalived dual main architecture:

Generally, small and medium-sized companies use this architecture, which is relatively convenient and easy to build; master-slave or master-master mode can be used to quickly switch to master nodes by using keepalived high availability mechanism after the failure of slave nodes. The original library becomes the new master library.

However, in view of this framework, I personally suggest the following points:

1. Be sure to improve the switching script, and the switching mechanism of keepalived should be reasonable to avoid the phenomenon of unsuccessful switching.

two。 The configuration of the slave library should be the same as that of the master database as soon as possible, not too secondary; to avoid switching between the master library and the master database, the new master library (the original slave library) will affect the online business.

3. The problem of delay can not be avoided in this architecture. Enhanced semi-synchronous completion in mysql 5.7can be used. You can also change the architecture and use PXC to complete the synchronization function without delay.

4. Keepalived cannot solve the problem of brain fissure, so when judging service anomalies, we can modify our judgment script to decide whether to switch by supplementary detection of third-party nodes, which can reduce the risk of brain fissure.

5. Using the keepalived architecture, when setting the state of the two nodes, it should be set to the non-preemptive mode, both of which are backup state, and determine who is the main library by priority. Avoid brain fissure and conflict.

6. Install some dependency packages needed for mysql; it is recommended to configure the yum source and install keepalived with yum.

MHA architecture:

MySQL MHA architecture: it can be said that it is the most popular and most used architecture in the enterprise. Some students often ask me related questions.

Since MHA is so popular, what are its advantages?

1. During the failover, you can judge which slave database is closest to the data of the master database, and then switch to the above, which can reduce the data loss and ensure the consistency of the data.

two。 Support for binlog server can improve the efficiency of binlog transmission and further reduce the risk of data loss.

3. Enhanced semi-synchronization of mysql 5.7can be configured to ensure data synchronization at all times.

Of course, there will be some thorny shortcomings:

1. The automatic switching script is too simple and aging, so it is recommended to improve it gradually in the later stage.

two。 To build MHA architecture, it is necessary to turn on the mutual trust protocol of linux system, so it is not a small test for system security.

PXC architecture:

It can realize synchronous data replication, read and write among multiple nodes, and ensure high service availability and data consistency of the database.

PXC basically belongs to the most perfect set of architectural design concepts:

1. Master-slave synchronization, basically no delay

two。 Fully compatible with MySQL

3. Adding new nodes to the cluster is easy to deploy.

4. High service availability can be guaranteed, and data consistency is more stringent

Part VI: the strongest Masters

Entering the last Rank, where the knowledge building has been basically built, what we need to do is some advanced optimization operations.

Optimization can be considered from four parts: programming perspective, system dimension, database, and hardware direction.

Refer to Lao Zhang's blog article "the eighteen palms of database optimization":

Http://sumongodb.blog.51cto.com/4979448/1949024

Today, Lao Zhang introduced MySQL to all the brothers from shallow to deep. I really hope that you can take the time to read it carefully. I write every article very attentively. As a teacher, I mainly transfer knowledge and experience to those who are confused. Or waste most of their time playing games.

I hope this knowledge will be helpful to you. If you have any opinions, we can discuss them together and make progress together. Make our life fuller, let us love technology more! (the end of Arena of Valor of superZS Lao Zhang)

In the later stage, there will be a MySQL High availability Architecture Trilogy series. I hope you will pay attention to it then!

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.