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

This article focuses on "how to use MySQL 5.6Parameter extra_port", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to use MySQL 5.6Parameter extra_port"!

A preface

As a MySQL DBA, in the process of operation and maintenance of MySQL, we often encounter the error of Too many connections, which is how embarrassing it is that database maintainers can not log in to the database for maintenance. Some people may say that you can modify the configuration file and then restart the database to solve the problem. Yes, but this is the worst usability of many solutions. This article introduces a new 5.6 feature, the management port, to solve this problem.

Second, know extra_port.

MySQL introduced a parameter extra_port after version 5.6.14 to solve the too many connections problem.

Official introduction of percona:

According to the official documentation, use this parameter to specify a port number when MySQL starts (do not conflict with the normal database service port), and Percona Server will listen for requests from that port. Enabling this parameter can solve the problem that when using the thread_pool feature, DBA cannot connect to DB through the normal port because all connection pooling worker is busy with slow querey or locked, so that DBA can maintain the database properly.

The specific usage is as follows:

Mysql-port='extra-port-number'-protocol=tcp

Official introduction of Maridb (similar to percona's introduction, readers can translate it themselves)

Description: Extra port number to use for tcp-connections in an one-thread-per-connection manner. If set to 0, no other port is used. Introduced for the MariaDB 5.1 threadpool.

Extra_max_connections this parameter mainly controls how many connections can be created through the management port.

Official introduction:

"This variable can be used to specify the maximum allowed number of connections plus one extra SUPER users connection on the extra_port. This can be used with the extra_port variable to access the server in case no new connections can be established due to all worker threads being busy or being locked when pool-of-threads feature is enabled."

Three extra_port tests

Test environment: 5.6.26-74.0-log Percona Server

Parameter settings:

Max_connections = 1

Extra_max_connections = 1

Extra_port=13306

Use the above two parameters to set the number of connections to the entire database. With the above parameter configuration, a maximum of 4 connections can be created.

During the test, try to connect to the database by using the following command:

Scenario 1 simulates a business connection to a database and attempts to connect under two different session

Mysql-uyang-pyang-h227.0.0.1-P3306

Scenario 2 simulates a DBA connection to a database and attempts to connect under two different session

Mysql-uroot-h227.0.0.1-P3306

Scenario 3 simulates a DBA connection to a database and attempts to connect under two different session

Mysql-S / srv/my_3306/run/mysql.sock

Scenario 4 occupies all connections to the database, and then uses the extra_port port to connect as root

Mysql-- port=13306-- protocol=tcp-uroot-h227.0.0.1

Scenario 5 occupies all connections to the database, and then uses the extra_port port to connect as a business

Mysql-- port=13306-- protocol=tcp-uyang-pyang-h227.0.0.1

Scenario 6 do not specify a communication protocol this time, try to connect

Mysql-- port=13306-uyang-pyang-h227.0.0.1

Scenario 7 simulates DBA and business connecting to the database at the same time, attempting to connect under two different session

Mysql-uyang-pyang-h227.0.0.1-P3306

Mysql-S / srv/my_3306/run/mysql.sock

Mysql-uroot-h227.0.0.1-P3306

Results of the test

In scenario 1, you can only connect once and report an error ERROR 1040 (HY000): Too many connections for the second time.

In scenario 2, only 2 connections can be made, and the third error ERROR 1040 (HY000): Too many connections

In scenario 3, only 2 connections can be made, and the third error ERROR 1040 (HY000): Too many connections

Scenario 4, you can connect twice and report an error ERROR 1040 (HY000): Too many connections for the third time.

Scenario 5, unable to connect, prompts "ERROR 1040 (HY000): Too many connections"

Scenario 6, when the business occupies a full connection pool, so that the administrator can connect twice

Scenario 7 if the administrator logs in first, the business side cannot connect; if the business side logs in first, the administrator can log in once, but not the second time.

It can be connected 4 times in total.

At this point, I believe you have a deeper understanding of "how to use MySQL 5.6parameter extra_port". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.