Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use the Connection-Control plug-in in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

First look at the system variable plugin_dir and find the path where the plug-in (Plugins) is located, as shown below

Mysql > select version () from dual; +-+ | version () | +-+ | 8.0.18 | +-+ 1 row in set (0.00 sec) mysql > show variables like 'plugin_dir' +-+-+ | Variable_name | Value | +-+-+ | plugin_dir | / usr/lib64/mysql/plugin/ | +-+-- + 1 row in set [root@KerryDB] # cd / usr/lib64/mysql/plugin/ [root@KerryDB plugin] # ls-lrt total 76388-rwxr-xr-x. 1 root root 106696 Sep 20 2019 rewrite_example.so-rwxr-xr-x. 1 root root 104432 Sep 20 2019 mysql_no_login.so-rwxr-xr-x. 1 root root 111464 Sep 20 2019 mypluglib.so-rwxr-xr-x. 1 root root 106648 Sep 20 2019 auth_socket.so-rwxr-xr-x. 1 root root 163560 Sep 20 2019 adt_null.so-rwxr-xr-x. 1 root root 409032 Sep 20 2019 validate_password.so-rwxr-xr-x. 1 root root 9054776 Sep 20 2019 libpluginmecab.so-rwxr-xr-x. 1 root root 344696 Sep 20 2019 authentication_ldap_sasl_client.so-rwxr-xr-x. 1 root root 1145832 Sep 20 2019 rewriter.so-rwxr-xr-x. 1 root root 625944 Sep 20 2019 ha_example.so-rwxr-xr-x. 1 root root 388992 Sep 20 2019 semisync_slave.so-rwxr-xr-x. 1 root root 32368 Sep 20 2019 component_log_sink_json.so-rwxr-xr-x. 1 root root 235200 Sep 20 2019 component_audit_api_message_emit.so-rwxr-xr-x. 1 root root 494720 Sep 20 2019 keyring_udf.so-rwxr-xr-x. 1 root root 149280 Sep 20 2019 component_log_sink_syseventlog.so-rwxr-xr-x. 1 root root 1446024 Sep 20 2019 semisync_master.so-rwxr-xr-x. 1 root root 2277480 Sep 20 2019 mysql_clone.so-rwxr-xr-x. 1 root root 1231376 Sep 20 2019 libmemcached.so-rwxr-xr-x. 1 root root 454096 Sep 20 2019 component_mysqlbackup.so-rwxr-xr-x. 1 root root 193336 Sep 20 2019 component_log_filter_dragnet.so-rwxr-xr-x. 1 root root 1177352 Sep 20 2019 ha_mock.so-rwxr-xr-x. 1 root root 83936 Sep 20 2019 locking_service.so-rwxr-xr-x. 1 root root 1269784 Sep 20 2019 connection_control.so-rwxr-xr-x. 1 root root 1280936 Sep 20 2019 innodb_engine.so-rwxr-xr-x. 1 root root 442304 Sep 20 2019 component_validate_password.so-rwxr-xr-x. 1 root root 1206024 Sep 20 2019 version_token.so-rwxr-xr-x. 1 root root 2338880 Sep 20 2019 keyring_file.so-rwxr-xr-x. 1 root root 2031912 Sep 20 2019 ddl_rewriter.so-rwxr-xr-x. 1 root root 49246400 Sep 20 2019 group_replication.so drwxr-xr-x. 2 root root 4096 Nov 6 2019 debug

Install the plug-in

Mysql > INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; Query OK, 0 rows affected (0.02 sec) mysql > INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME' connection_control.so'; Query OK, 0 rows affected (0.00 sec)

Check whether the installation is successful

Mysql > show plugins; mysql > SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION-> FROM INFORMATION_SCHEMA.PLUGINS-> WHERE PLUGIN_LIBRARY = 'CONNECTION_CONTROL.SO' +-- +-- + | PLUGIN_NAME | PLUGIN_LIBRARY | | PLUGIN_STATUS | LOAD_OPTION | +-+ | CONNECTION_CONTROL | | | connection_control.so | ACTIVE | FORCE | | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | connection_control.so | ACTIVE | FORCE | +-- + -+-+ 2 rows in set (0.00 sec) mysql >

Set system variabl

Mysql > show variables like 'connection_control%' +-+-+ | Variable_name | Value | +-- -+-+ | connection_control_failed_connections_threshold | 3 | | connection_control_max_connection_delay | 2147483647 | | connection_control_min_connection_delay | 1000 | +- -+

Connection_control_failed_connections_threshold # limit on the number of failed logins. The default is 3.

Connection_control_max_connection_delay # limits the maximum retry time in milliseconds (milliseconds). The default is 2147483647.

Connection_control_min_connection_delay # limits the minimum retry time in milliseconds (milliseconds). The default value is 1000 milliseconds, that is, 1 second

Note:

The value of 1:connection_control_min_connection_delay must be less than the value of connection_control_max_connection_delay,connection_control_max_connection_delay and not less than the value of connection_control_min_connection_delay.

Mysql > set global connection_control_min_connection_delay=60000; Query OK, 0 rows affected (0.00 sec)

Note that the global system variable set in command mode is lost after the server is restarted, so it is best to set the global system variable in the parameter file my.cnf

-- the following configuration is added to the profile

[mysqld] plugin-load-add = connection_control.so # is not required connection-control = FORCE # is not necessary connection-control-failed-login-attempts = FORCE # is not necessary connection_control_min_connection_delay = 60000 connection_control_max_connection_delay = 1800000 connection_control_failed_connections_threshold = 3

After entering the wrong password three times in a row, it will hang after entering the password for the fourth time.

[root@lnx02 ~] # mysql-h 10.20.57.24-u test-p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES) [root@lnx02 ~] # mysql-h 10.20.57.24-u test-p Enter password: ERROR 1045 (28000): Access denied for user' test'@'192.168.27.180' (using password: YES) [root@lnx02 ~] # mysql-h 10.20.57.24-u test-p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES) [root@lnx02 ~] # mysql-h 10.20.57.24-u test-p Enter password:

Notice that after the MySQL service is restarted, all the data in the INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS is empty.

The CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin must be activated for this table to be available, and the CONNECTION_CONTROL plugin must be activated or the table contents will always be empty. See Section 6.4.2, "The Connection-Control Plugins".

The CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plug-in must be activated to use the table CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS, and either the CONNECTION_CONTROL plug-in must be activated, or the contents of the table are always empty. See section 6.4.2, "connection control plug-in".

The table contains rows only for clients that have had one or more consecutive failed connection attempts without a subsequent successful attempt. When a client connects successfully, its failed-connection count is reset to zero and the server removes any row corresponding to the client.

The table contains only rows for clients that have made one or more consecutive failed connection attempts without subsequent successful attempts. When a client connects successfully, its failed connection count is reset to zero and the server deletes any rows corresponding to the client.

Assigning a value to the connection_control_failed_connections_threshold system variable at runtime resets all accumulated failed-connection counters to zero, which causes the table to become empty.

Assigning a value to the connection_control_failed_connections_threshold system variable at run time resets all accumulated failed connection counters to zero, which causes the table to become empty.

Remove the limit on delayed response of account

Method 1: restart the MySQL instance

Method 2: adjust the value of system variable connection_control_failed_connections_threshold.

Mysql > SELECT * FROM-> INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS +-+ | USERHOST | FAILED_ATTEMPTS | +-+-+ | 'test'@'192.168%' | 5 | +- -+-+ 1 row in set (0.00 sec) mysql > mysql > set global connection_control_failed_connections_threshold=2 Query OK, 0 rows affected (0.00 sec) mysql > SELECT * FROM-> INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; Empty set (0.00 sec)

3: uninstall the plug-in plugin

Mysql > UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; mysql > UNINSTALL PLUGIN CONNECTION_CONTROL

Note:

With regard to the implementation principle of Connection-Control, Taobao Database Kernel monthly has an article that analyzes the code and introduces the implementation principle. Although the Connection-Control plug-in can prevent malicious violence from cracking MySQL accounts, it may waste MySQL resources.

For example, if there are a large number of malicious attacks in a short period of time, although the plug-in can prevent cracking the MySQL account, it will consume host resources (one thread per connection).

If the thread pool is used here, the consumption of host resources can be avoided, but when the threads in the thread pool are consumed, there will be a denial of service with new connections.

This is how to use the Connection-Control plug-in in MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.