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

Transferred from

"Ip Man" is an interactive column newly designed by Zhi Ju Tang, which provides you with tips on technical knowledge from time to time, regardless of form, questions or discussions, and publishes answers on the same day, so that you can easily use fragment time to learn the most practical knowledge points.

Zhi Ju Tang-the most reliable and quality training brand http://www.3wedu.net/

IP Man album https://mp.weixin.qq.com/mp/homepage?__biz=MzI1OTU2MDA4NQ%3D%3D&hid=15&sn=8a530aa309c1fe6e4d99b3a0d49a9695

Sunday, June 10, 2018

What are the reasons for the inconsistency of MySQL master-slave replication, and how to prevent and solve it?

First, the main reasons for the inconsistency of the principal are:

The data of slave database is inconsistent with that of master database due to human reasons (written from slave library)

In the process of master-slave replication, the master library is down abnormally.

Set rules such as ignore/do/rewrite, replication, etc.

Binlog non-row format

Asynchronous replication itself is not guaranteed, semi-synchronous has the problem of commit read, enhanced semi-synchronous is more perfect. However, for abnormal restart (Replication Crash Safe), the prevention of writing data from the library (GTID) also needs a strategy to ensure.

The slave library has been interrupted for a long time, and the binlog application is not continuous. Monitor and repair the master and slave in time.

Stored procedures are enabled from the library, stored procedures are disabled from the library, etc.

Database size version / branch version causes data inconsistency? Master and slave versions are unified

No parameters such as mysqldump-- master-data=2 are specified during backup

Inconsistency between master and slave SQL _ mode

The server id of one master and two slaves is consistent.

MySQL self-increasing column principal is not consistent

The master-slave information is saved in the file, and the refresh of the file itself is non-transactional, resulting in that the execution point from the restart of the library is greater than the actual execution point.

If the after_commit is used in semi-synchronization, the master database may be out of sync, depending on whether the binlog is passed to the slave database.

Enhanced semi-synchronous enabled (5.7in after_sync mode), but automatically switched from library latency timeout to asynchronous replication

2. the prevention and solutions are as follows:

Master:innodb_flush_log_at_trx_commit=1&sync_binlog=1

Slave:master_info_repository= "TABLE" & relay_log_info_repository= "TABLE" & relay_log_recovery=1

Set from library to read-only mode

5.7 enhanced semi-synchronization can be used to avoid data loss, etc.

Binlog row format

A regular data verification mechanism must be cited.

When using deferred replication, the master-slave data is also inconsistent (as planned), but in the switching, do not promote the delay from the master database to the master database.

In the process of master-slave switching in mha, master inconsistency may occur due to the downtime of the master library system (the mechanism of mha itself causes this problem)

Monday, June 11, 2018

Why did you decide to divide the database and table, what problems do you encounter in the process, and how to solve them?

First, why did you decide to carry out sub-database and sub-table?

Select and judge whether to use sub-database and sub-table according to the type of business and the evaluation of business capacity.

The ability of the current database, the evaluation of stress

Physical isolation of databases, such as reducing lock contention, resource consumption and isolation, etc.

There are many hotspot tables and a large amount of data, which may lead to lock contention and performance degradation.

The high concurrency of the database and the excessive read and write pressure on the database may lead to database or system downtime.

Excessive number of database (below MySQL5.7) connections will increase the pressure on the system

A large amount of data in a single table, such as improper use of SQL, will lead to a high proportion of random read and write of io. The query is slow (the B + tree on the large table is too large, the scan is too slow, and may even require 4 layers of B + tree)

Backup and recovery time is long

Second, what are the problems?

The conflict detection of global competition (primary key and unique index) is not accurate, and the global self-increasing primary key support is not good enough.

The selection of sharding keys. If there is no good choice, the efficiency of SQL execution may be affected.

Distributed transaction, mid-price product support for distributed transaction

For developers, the business needs to be split.

For developers, some SQL incompatibility requires code refactoring and workload assessment.

For developers, cross-library join, cross-library query

Third, how to solve the problem?

Use the global semicolon. Or use globally unique id, (apply build order unique int type as global primary key)

The application layer determines the unique index

Cooperate with the application to select the appropriate sharding key and add the index

Cooperate with the application and development to rectify the incompatible SQL.

Tuesday, June 12, 2018

What should be considered in the MySQL High availability Architecture? How do you think it should be designed?

1. What should be considered in the MySQL high availability architecture?

To understand the business, we need to consider the sensitivity of the business to database consistency requirements, and whether any transactions will be lost in the switching process.

To understand the infrastructure, you need to understand the highly available architecture of the infrastructure. For example, single network cable, single power supply, etc.

For database failure time mastery, the business side can tolerate the time range at most, because the application unavailable time caused by highly available handover

You need to understand the mainstream high-availability pros and cons: for example, MHA/PXC/MGR, etc.

Considering multi-IDC and multi-copy distribution, business can be cut to another computer room after all IDC-level nodes are offline.

Second, how do you think it should be designed?

The basic layer cooperates with the basic operation and maintenance department to understand and avoid whether a single point of failure will occur in the network / hard disk / power supply.

The application layer cooperates with the application developer to record SQL logs in critical business. Even if the transaction is switched and the transaction is lost, the data consistency can be ensured by manual patching. For example, the transactional business introduces state machine, transaction state, and transaction redo after database switching.

The business layer understands its own applications and makes reasonable high-availability strategies according to different applications.

Stand-alone multi-instance environment and virtual machine or container-based design cannot be distributed on the same physical machine.

In the end, the big trick is not available in the database. You can first store the mentioned transactions in a queue or other location, and wait for the database to be restored and reapplied.

Wednesday, June 13, 2018

MySQL backup, when using xtrabackup to back up all instance data, will it cause lock waiting? What if you use mysqldump for backup?

Will xtrabackup and mysqldump cause lock waiting?

Xtrabackup, which produces a temporary global read lock FTWL (flush table with read lock) during backup, is used to copy files such as frm/MYD/MYI and record binlog information. If the amount of data in the MyISAM table is very large, the longer it takes to copy and lock the table.

Mysqldump might. If you just add the-- single-transacton option to ensure backup data consistency, the FTWL lock will not be generated. But usually, in order to keep the backup file consistent with binlog, we usually set the-master-data option to get the current binlog information, which is also temporarily locked.

If the amount of data is very large, it is recommended to give priority to the use of xtrabackup to improve the backup / recovery speed. If the amount of data is not too large or want to back up a single table, it is recommended to use mysqldump to facilitate logical recovery. Each has its own advantages and disadvantages, pay attention to its applicable scenarios

Second, xtrabackup cold knowledge

Xtrabackup developed based on MySQL version 5.6generates an intercom file suspend file during backup, which is used for communication between xtrabackup and innobackupex. The file is deleted after the backup. The default file location is / tmp/xtrabackup_suspended.

If the access permission of / tmp or the permission of the file is modified during the backup, the two programs cannot communicate directly, which will cause xtrabackup hang to live, and the table being backed up will not be able to release the lock normally, which will cause lock waiting. In this case, you need to force kill to drop the xtrabackup process.

Friday, June 15, 2018

MySQL 5.7began to support JSON, so is it necessary to use MongoDB to store JSON? Please list your opinions / reasons.

1. Opinion A: support MySQL storage JSON

1.MongoDB does not support transactions, while MySQL supports transactions

Compared with MongoDB, the stability of 2.MySQL is better than MongoDB.

3.MySQL supports multiple storage engines

II. Opinion B: support MongoDB storage JSON

1. From the point of view of performance, MongoDB is better than MySQL in reading and writing efficiency of JSON.

Compared with MySQL, 2.MongoDB has better scalability than MySQL.

3.MongoDB supports more JSON functions

III. Summary

1. If the application has no transaction requirements, the structure of the storage data table is complex and is often modified. For example, MongoDB is more suitable for scenarios such as in-game equipment.

two。 If the application has transaction requirements, the "tables" that store data are related to each other. For example, MySQL is more suitable for scenarios such as an order system.

3. On the whole, we are relatively optimistic about the JSON function of MySQL, and the JSON function of MySQL will have a chance to surpass MongoDB under the official efforts in the future.

Sunday, June 17, 2018

When the data is mistakenly deleted / misoperated, the data is lost. What measures have you tried to save the data / loss?

I. premise

1. When the data is mistakenly deleted / misoperated, the database should be shut down as soon as possible. The business side needs to hang up the shutdown notice urgently to avoid secondary data pollution and to protect the consistency of the data.

2.BINLOG format is ROW format, and BINLOG of other formats is not discussed.

Second, what means can be used to recover data loss caused by data misoperation (update/delete/drop)?

1.BINLOG recovery: you can use the reverse parsing BINLOG tool to restore. For example: binlog2SQL, etc.

two。 Deferred slave library: data recovery can be achieved by releasing the delayed slave library and specifying the BINLOG end location point.

Third, data is mistakenly deleted (rm/ physical files are corrupted) resulting in data loss, what means can be used to recover?

1. If there is a backup, you can restore mysqldump/xtrabackup + binlog through backup to achieve full + incremental recovery.

two。 If there is no backup but there is a slave library, you can promote the slave database to the master library by switching between master and slave, so as to achieve data recovery.

3. If there is no backup and no slave library, but MySQL is not restarted, you can try to restore by copying the files in / proc/$pid/fd

4. If there is no backup and no slave library, but MySQL is restarted, you can restore it through extundelete or undrop-for-innodb

Tuesday, June 19, 2018

How to choose the replication architecture of MySQL 5.7in production with asynchronous replication, semi-synchronous, enhanced semi-synchronous, MGR, etc.?

1. In the production environment:

Several replication scenarios have the value of existence. The following are described separately:

Choose in terms of maturity, recommended: asynchronous replication (GTID+ROW)

Choose from data security and higher performance: enhanced semi-synchronization (under this structure innodb_flush_log_trx_commit can also be adjusted to non-1 for better performance)

For scenarios where master-slave switching control is difficult to manage and requires high data consistency, MGR can be used.

2. Reasons:

Asynchronous replication is relatively mature and easy to use for environmental operation and maintenance.

Enhanced semi-synchronous replication can safely ensure that data is transferred to the slave database, and there is no need to be too strict on the configuration of a single node, especially from the library, and there is a higher improvement in consistency and performance, but there are certain requirements for operation and maintenance.

MGR group replication. Compared with enhanced semi-synchronous replication, MGR is more able to ensure data consistency, and transaction commits must be resolved and approved by most nodes in the group. MGR architecture is more difficult for operation and maintenance, but it is also more perfect.

Generally speaking, in terms of technical implementation: MGR > enhanced semi-synchronous > asynchronous replication.

In the future, we may see more MGR used in production, and the requirements for the operation and maintenance of MySQL will also be climbed to another tall building.

Wednesday, June 20, 2018

Why pt-osc may cause master-slave delay, is there any good way to solve or avoid it?

If binlog is in row format in replication, and using pt-osc to copy data from old table to temporary table for large tables, a large amount of binlog will be generated during this period, resulting in latency

In the process of moving data in pt-osc, insert...select has row locks, which will reduce the degree of transaction parallelism, and the binlog generated in the process of moving data in pt-osc is not parallel, so it cannot be played back in parallel in slave.

You can control the data size of each copy by setting parameters-- chunk-size,-- chunk-time, or-- max-log, check-interval, check-slave-lag and other parameters to control the delay of master-slave replication (but this may cause pt-osc work to take too long and need to be weighed)

Thursday, June 21, 2018

What causes the delay of MySQL asynchronous replication?

There are mostly concurrent transactions on master and single-thread playback on salve. (since MySQL 5.7, real parallel playback is supported and alleviated)

Asynchronous replication, there is a certain delay (otherwise it is not called asynchronous, you can change it to semi-synchronous replication if you mind)

The performance of slave machines is generally weaker than that of master (this is a common misunderstanding, in fact, slave does not have low requirements for machine performance)

Sometimes multiple instances are run on slave to save machine resources

The design of the table structure is unreasonable, especially when there is no primary key before MySQL 5.6. it will cause almost all updates to be scanned throughout the table, which is very inefficient.

Run a large number of read-only inefficient SQL on slave

A large number of large transactions will also cause slave to fail to play back in parallel.

Delays caused by business design defects or network delays

Friday, June 22, 2018

How much binlog does MySQL produce every day? can you find out with SQL statements?

First of all, this is a hypothetical proposition (another fishing question).

This requirement can be quickly accomplished through system-level commands in conjunction with the "FLUSH BINARY LOGS" in MySQL.

Running the SHOW MASTER/BINARY LOGS command allows you to see a full list of binlog, but there is no way to tell which ones were generated during the day.

Saturday, June 23, 2018

What method can be used to prevent accidental deletion of data?

The following measures can prevent accidental deletion of data, as follows:

In the production environment, the business code should try not to keep the password information of the database connection account in explicit text.

Important DML and DDL are automatically implemented through platform tools to reduce manual operation.

Deployment delay replication slave library, used for data rollback in case of erroneous deletion, and slave database is set to read-only

Confirm that the backup system is timely and effective

Enable SQL audit function and form a good SQL habit

Enable the sql_safe_updates option, do not allow updates / deletions without WHERE conditions

Change the rm of the system layer to mv

No physical deletion online, but logical deletion instead (mark row data as unavailable)

Activate the fortress machine to block high-risk SQL

Lower the permission level of ordinary accounts in the database

Be sure to turn on binlog

Sunday, June 24, 2018

What are the replication improvements of MySQL 8.0 over 5.7?

Mr. Song Libing: this proposition is also discussed in the open class "replication and improvement of MySQL 8.0 vs. 5.7". There are two main parts in a brief summary:

I. improvement of ordinary replication function

Add WRITESET parallel replication mode to improve parallelism and reduce latency

In multi-source replication, the filter rule of each channel can be dynamically modified online and can be viewed / monitored in packs

Binary Log stores more diverse data and supports millisecond latency monitoring

JSON Documents replication is more efficient.

Support for DDL Crashsafe

Add caching_sha2_password security policy to improve replication security

Second, the function of MGR is improved.

Support to set the node weight, and the online node with the largest weight will be elected.

More status information is stored in each node, such as version, role, etc.

Flow control can be automated according to the transaction status of the slave node.

Servers that leave the cluster are automatically set to read only to avoid misoperation to update data.

Can monitor the memory usage of MGR

Monday, June 25, 2018

Running truncate table, 400 million pieces of data will cause long-term table lock? Is there a better way?

It is best to create the new table, then cross-rename exchange, and then drop/truncate table or other ways to clear the data.

1. Operable steps:

Create a new tmp table, and exchange the formal table with the tmp table name (note that it is done in a SQL and lock the table)

Create a hard link ln tmp.ibd tmp.ibd.hdlk to the tmp table

Delete table tmp from mysql (both truncate and drop)

Then find a time when the business is not busy to delete the data file or use coreutils's truncate to do it slowly.

2. With regard to truncate table, the official file explains:

Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements

When a table is truncated, it is dropped and re-created in a new .ibd file, and the freed space is returned to the operating system

Tuesday, June 26, 2018

Obviously, there is an index "feeling" that should be selected, and it is also available in possible_keys during explain, but it is not selected in the end. What are the possible reasons?

1. The implementation plan is as follows:

Desc select * from T1 where c2 > = 2

Key: NULL

Key_len: NULL

Rows: 14

Filtered: 92.86

Extra: Using where

2. The possible reasons are as follows:

Implicit conversion

Table fragmentation because the fragmentation rate of the table is too high

The data read according to the index accounts for more than 30% of the data in the whole table.

Statistics are not updated in a timely manner

3. The results of the above implementation plan are:

The expected number of rows scanned is 14, and the value of filtered (which is the percentage of rows that return results as a percentage of rows that need to be read) is 92%.

The filtered value of 92% in the current execution plan indicates that the results obtained according to the index query account for 92% of the whole table. In MySQL, the results of the index query account for 30% of the data of the whole table, so the index will not be taken.

In addition, it is also possible that the fragmentation rate of the table is too high or implicit conversion.

Wednesday, June 27, 2018

The master-slave replication thread is normal (Yes, also reported correctly). The binlog of Master has gone to binlog.000100, but the Master_Log_File on slave only goes to binlog.000090. What are the possible reasons?

First of all, note that this is Master_Log_File IO thread latency, not Relay_Master_Log_File SQL thread latency.

1. The possible reasons are as follows:

Because the sync_relay_ log value is too low, Slave refreshes relay_log files frequently, which consumes too much hard disk resources of Slave, so SlaveIO Thread is very slow.

Due to the excessive pressure of Master/Slave, the Slave IO Thread can not respond in time and can not obtain the event of Master in time.

There is a serious packet loss on the network. Small packets can connect and stay connected, but large packets cannot be sent. It may be caused by the inconsistent setting of TCP MTU values between Master and Slave.

The Master and Slave web links have been disconnected. However, the slave_net_ timeout value is equal to 0 (indicating that heartbeat is completely disabled) or slave_net_timeout and Slave_heartbeat_period are very large (indicating the time to detect the master-slave heartbeat).

The binlog of Master is so large that the file of the io thread has been reading the same one for a long time.

II. Summary

This case is in the main library for stress testing, in the process of stress testing, because of the great pressure of Master itself, it is too late for Master to send binlog to Slave. So there appears to be no delay on the surface, but in fact there has been a delay.

.

Wednesday, July 4, 2018

How to optimize the Linux operating system for MySQL environments?

I. Primary play

1. Turn off NUMA at the BIOS and kernel level

two。 Set the maximum performance mode for CPU and memory at the BIOS level

3. Turn off CPU energy saving mode at the BIOS level

4. Change IO Scheduler to deadline or noop

5. Using the xfs file system, mount options noatime, nodiratime, nobarrier

6. Set up vm.swappiness at the kernel level

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.