MySQL controls the number of user attempts to enter the wrong password

1. How to monitor MySQL deadlock in production environment and how to reduce the probability of deadlock occurrence

First of all, deadlock is not a "lock", but a deadlock is caused by two or more session lock waiting to generate loops.

(1) Deadlock monitoring and processing methods

For deadlock monitoring, each version provides innodb_print_all_deadlocks option. Turning on this option will output the deadlock log to the error log of MySQL, so the purpose of monitoring deadlock can be achieved by monitoring the error log. MariaDB is even simpler. MariaDB provides a counter for Innodb_deadlocks, which can monitor whether deadlocks occur by monitoring the growth of this counter.
If there are deadlocks on the line and the frequency is high, attention must be paid to them. Because the deadlock log only records the last two SQL that caused the deadlock, it cannot be located immediately through the deadlock log
To find out the causes of deadlock, we should cooperate to develop and simulate the deadlock process in time, analyze the causes of deadlock and modify the program logic.

(2) How to reduce the probability of deadlock

1. Try to use short transactions and avoid large transactions
2. When FOR UPDATE/LOCK IN SHARE MODE locks are added, it is better to reduce the transaction isolation level, for example, RC level can reduce the deadlock probability and lock granularity
3. When a transaction involves multiple tables or multiple rows of records, the operation order of each transaction should be kept at 1
4. Optimize the efficiency of SQL through index, reduce the deadlock probability, and avoid locking all data due to full table scanning
5. There should be transaction failure detection and automatic repeated submission mechanism in the program
6. In the high concurrency (spike) scenario, turn off the innodb_deadlock_detect option to reduce deadlock detection overhead and improve concurrency efficiency

2. What are the excellent features of MongoDB and what are the suitable scenarios

(1) Excellent characteristics

1. Practicality: Oriented to the rich document data model like json, it is naturally friendly to developers
2. Availability: Automatic high availability based on raft protocol, easily providing 99.999% availability
3. Scalability: Support for fragmented cluster provides friendly horizontal expansion for business
4. High performance: Nested model design support reduces discrete writes, fully utilizes physical memory, and avoids disk reads
5. Strong compression: WiredTiger engine provides a variety of data compression strategies, with a compression ratio of 2 ~ 7 times, which greatly saves disk resources

(2) Suitable Scenes

1. There is no requirement for multi-document transactions and multi-table associated queries
2. Industries with rapid business iteration and frequent changes in demand
3. Single cluster concurrency is too large to support business growth
4. Data volume growth is expected to be TB and above storage requirements
5. Expect 99.999% database high availability scenario

3. What are the advantages of GO over other programming languages? How to choose the actual production environment.

1. It naturally supports high concurrency and strong 1-language, with high development efficiency and stable and safe online operation
2. Garbage collection, regardless of memory allocation and collection
3. Powerful GMP model, asynchronous processing, supports high concurrency, and Xiaobai can easily write high concurrency code

In the actual production environment, it is suggested to consider from the following aspects:

1. Looking at the business scenario, e-commerce and big data processing have ready-made solutions, which are not suitable for use. In addition, cpu intensive mathematical operations are not needed.
2. GO is good at quickly producing business prototypes, with high iterative development efficiency and strong push by start-ups
3. Look at the technology stack developed by the company. If there is a big difference, it will be faster to get started if you choose GO, and the programming style can be unified.

4. 1 big business, there are many updates, which have been rolled back now, but they are in a hurry to shut down and restart. What should I do?

1. First, try to avoid executing large transactions in MySQL, because large transactions will bring problems such as master-slave replication delay
2. Large transactions are automatically rolled back by kill and MySQL. Transactions of ROLLING BACK can be seen through TRANSACTIONS of show engine ES90status, and corresponding row locks will still be held during rollback operation
3. If MySQL is shut down forcibly at this time, it will still scroll back after MySQL is started again
4. Therefore, in order to ensure data security, it is recommended to wait patiently for the rollback to complete before shutting down and restarting. Before shutting down and restarting, you can turn down innodb_max_dirty_pages_pct to refresh dirty pages as much as possible, and turn off innodb_fast_shutdown
5. If there is no way to shut down, kill-9 can shut down MySQL first, provided that double 1 needs to be set to ensure transaction security, otherwise more transaction data may be lost. Then innodb rolls back the previous transaction by itself crash recovery after restarting the instance

PS and kill-9 are high-risk operations, which may lead to unpredictable problems such as the failure to start MySQL. Please use them carefully

5. How to reduce the effect of writing the WHERE condition incorrectly or not writing the WHERE condition at all when UPDATE/DELETE

1. Try not to manually execute any SQL command online, which is easy to make mistakes. It is best to directly execute SQL command online with the help of the second inspector
2. It is best to execute SQL in the test environment before executing it in the production environment, or edit it in the local text environment in advance before executing it
3. It is recommended to turn on the sql_safe_updates option, and prohibit the UPDATE/DELETE command without WHERE condition, without LIMIT or without index condition. You can also add the--safe-updates option when connecting to the server with an mysql client, for example: mysql--safe-updates--h xx--u xx
4. When performing DML operation manually online, turn on the transaction mode first, and the misoperation of 10,000 can be rolled back. For example: mysql > begin; update xxx; rollback;
5. Execute DML operation through DB management platform, and add judgment on such dangerous SQL on the platform to directly reject the execution of dangerous SQL
6. Configure the delayed slave library, and quickly recover the data from the delayed slave library after discovering the mistaken deletion of data

6. How does MySQL control the number of user attempts to enter the wrong password?

(1) Plug-in assistance

Starting from the official MySQL5.7. 17, CONNECTION_CONTROL and CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plug-ins are provided, which in turn provide connection_control_failed_connections_threshold, connection_control_min_connection_delay, connection_control_max_connection_delay

1. connection_control_failed_connections_threshold

The meaning of this parameter is to control the number of login failures and then start the delayed login

2. connectioncontrolminconnectiondelay

This parameter respectively represents the minimum delay time of each reconnection after the number of failures is exceeded, and the delay calculation formula is (total number of current failures-failure threshold
Value) connectioncontrolminconnection_delay, so the more error attempts, the greater the latency

3. connection_control_max_connection_delay

Maximum delay time after which the client can reconnect

4. After installing the plug-in, you can monitor the status value of Connection_control_delay_generated and the table under INFORMATION_SCHEMA

CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS to monitor the number of false login attempts

(2) Error log monitoring

The number of account password errors can be captured by scanning the MySQL error log regularly. After reaching a certain threshold, the corresponding host ip can be shielded in the system firewall to achieve the purpose of shielding the account (the specific operation depends on the situation)
For example, the error log will show 2019-05-10T13: 04: 41.232259Z 5 [Note] Access denied for user 'xucl' @ '127.0. 0.1' (using password: YES)

(3) Other explanations

1. Some students mistakenly think that max_connection_errors can control the number of attempts of wrong passwords. In fact, this parameter can only prevent port detection such as telnet, that is, record the number of protocol handshake errors

2. Finally, in production environment 1, we must pay attention to the status of aborted_clients and aborted_connects, and pay attention to any abnormality in time

Summarize