MySQL controls the number of attempts to enter incorrect passwords

1. How to monitor MySQL deadlocks in production environments and how to reduce the probability of deadlocks

First of all, deadlock is not "locked". Deadlock is caused by two or more session locks waiting for a loop.

1. Deadlock monitoring and handling methods

For deadlock monitoring, each version provides the innodb_print_all_deadlocks option. Turning on this option will output the deadlock log to the MySQL error log. Therefore, the purpose of monitoring deadlocks can be achieved by monitoring the error log. It is even simpler for MariaDB. MariaDB provides the Innodb_deadlocks counter, which can be used to monitor the growth of the counter to detect deadlocks.
If deadlock occurs online and the frequency is high, it must be taken seriously. Since the deadlock log only records the last two SQL statements that caused the deadlock, the cause of the deadlock cannot be immediately located through the deadlock log. We should promptly collaborate with developers to simulate the deadlock process, analyze the cause of the 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 adding FOR UPDATE/LOCK IN SHARE MODE locks, it is best to lower the transaction isolation level, such as RC level, to reduce the probability of deadlock and reduce the locking granularity.
3. When a transaction involves multiple tables or multiple rows of records, the operation order of each transaction must be consistent
4. Optimize SQL efficiency through indexes, reduce deadlock probability, and avoid locking all data due to full table scans
5. The program should have transaction failure detection and automatic resubmission mechanism
6. In high-concurrency (flash sales) scenarios, 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: JSON-like rich document data model, naturally friendly to developers
2. Availability: Automatic high availability based on the raft protocol, easily providing 99.999% availability
3. Scalability: Support for sharded clusters provides friendly horizontal expansion for business
4. High performance: nested model design support, reducing discrete writes, fully utilizing physical memory, and avoiding disk reads
5. Strong compression: The WiredTiger engine provides a variety of data compression strategies, with a compression ratio of 2 to 7 times, which greatly saves disk resources.

(II) Suitable scenarios

1. No multi-document transactions and multi-table association query requirements
2. Industries with rapid business iteration and frequent changes in demand
3. Single cluster has too much concurrency and cannot support business growth
4. Data volume growth is expected to reach TB and above storage requirements
5. Expect 99.999% database high availability scenario

3. What are the advantages of GO language compared with other programming languages? How to make choices in the actual production environment?

1. Natural support for high concurrency, strong consistent language, high development efficiency and stable and secure online operation
2. Garbage collection, no need to worry about memory allocation and recycling
3. Powerful GMP model, asynchronous processing, support for high concurrency, even novices can easily write high concurrency code

In the actual production environment, it is recommended to consider 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, mathematical operations that are CPU-intensive are not needed.
2. GO is good at quickly producing business prototypes and has high iterative development efficiency, so it is highly recommended by startups.
3. Look at the technology stack developed by the company. If there are big differences, then choosing GO will make it easier to get started and unify the programming style.

4. A large transaction with many updates has now been rolled back, but I am 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 cause problems such as master-slave replication delays.
2. When a large transaction is killed, MySQL will automatically roll back the transaction. You can see the ROLLING BACK transaction through the TRANSACTIONS of show engine innodb status. The corresponding row locks will still be held during the rollback operation.
3. If you forcibly shut down MySQL at this time, the rollback operation will still be performed after MySQL is restarted.
4. Therefore, to ensure data security, it is recommended to wait patiently for the rollback to be completed before shutting down and restarting. Before shutting down and restarting, you can lower innodb_max_dirty_pages_pct to refresh dirty pages as much as possible, and turn off innodb_fast_shutdown
5. If there is no other way to shut down the computer, you can use kill -9 to shut down MySQL first. The premise is that you need to set up double-one to ensure transaction security, otherwise more transaction data may be lost. Then after restarting the instance, InnoDB will automatically crash recovery and roll back the previous transaction.

PS, kill -9 is a high-risk operation, which may cause unpredictable problems such as MySQL failure to start, so use it with caution.

5. How to reduce the impact of incorrect or no WHERE conditions during UPDATE/DELETE

1. Try not to manually execute any SQL commands online, as it is easy to make mistakes. It is best to have a second person to help confirm when executing SQL commands directly online
2. It is best to execute SQL in the test environment and confirm that it is correct before executing it in the production environment, or edit it in the local text environment in advance and confirm it before executing it
3. It is recommended to turn on the sql_safe_updates option to prohibit the execution of UPDATE/DELETE commands without WHERE conditions, LIMIT conditions, or index conditions. You can also add the --safe-updates option when connecting to the server with the mysql client, for example: mysql --safe-updates -h xx -u xx
4. When manually executing DML operations online, enable transaction mode first so that you can roll back in case of erroneous operations. For example: mysql> begin; update xxx; rollback;
5. Execute DML operations through the DB management platform, and add judgments on such dangerous SQL on the platform to directly reject the execution of dangerous SQL
6. Configure a delayed slave database to quickly restore data from the delayed slave database after accidentally deleting data

6. How does MySQL control the number of attempts when a user enters an incorrect password?

1. Plug-in assistance

Starting from the official MySQL 5.7.17, the CONNECTION_CONTROL and CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plug-ins are provided, which in turn provide three parameters: connection_control_failed_connections_threshold, connection_control_min_connection_delay, and connection_control_max_connection_delay.

1. connection_control_failed_connections_threshold

The meaning of this parameter is to control the number of login failures before delaying login.

2. connectioncontrolminconnectiondelay

This parameter indicates the minimum delay time for each reconnection after the number of failures exceeds the limit. The delay calculation formula is (current total number of failures - failure threshold) connectioncontrolminconnection_delay. Therefore, the more error attempts, the longer the delay time.

3. connection_control_max_connection_delay

The maximum delay time, after which the client can reconnect

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

CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS to monitor the number of failed login attempts

2. Error log monitoring

By scanning the MySQL error log regularly to capture the number of account and password errors, when a certain threshold is reached, the corresponding host IP can be blocked in the system firewall to achieve the purpose of blocking 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)

(III) Other instructions

1. Some students mistakenly believe that max_connection_errors can control the number of attempts with incorrect 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 the production environment, you must pay attention to the status of aborted_clients and aborted_connects, and pay attention to any exceptions in time.

Summarize

The above is what I introduced to you about MySQL controlling the number of attempts when a user enters an incorrect password. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!