Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ Parameters of the extra port MySQL 5.6     - Security Features Linux and Unix operating system, programming (Linux)

- Ubuntu 12.04 / 14.04 users to install software LyX document processing (Linux)

- C language binary tree (Programming)

- Fatal NI connect error 12170 error in Alert Log (Database)

- Single-node Hadoop environment to build (Server)

- Java reflection technology explain (Programming)

- struts2 completely the wrong way to capture 404 (Programming)

- Mounting Windows shared directory system under the Linux (Linux)

- Use OpenWrt build WDS wireless network extension on V2 WHR-G300N (Linux)

- CentOS 6.4 dial-up Raiders (Linux)

- Ubuntu Live CD by updating Grub resume boot the Boot Menu (Linux)

- Ubuntu Linux to create and increase the Swap partition tutorial (Linux)

- How to avoid two Chrome icon appears in ELementary OS Freya (Linux)

- Wine 1.7 is installed on a system based on RedHat or Debian (Linux)

- Simple to use Linux GPG (Linux)

- Vagrant build LNMP environment (Server)

- Linux foundation tutorial: how to modify the host name on CentOS or RHEL 7 (Linux)

- Linux system started to learn: how to solve the xxx is not in the sudoers file error (Linux)

- Git version rollback (Linux)

- OpenDJ installed on RHEL6 (Linux)

  Parameters of the extra port MySQL 5.6
  Add Date : 2018-11-21      
  One. Introduction
    As a MySQL DBA, MySQL operation and maintenance during operation, the often encountered Too many connections error, then as a database maintenance staff are not landing database maintenance, how embarrassing thing. One might argue that you can modify the configuration file, and then restart the database to solve. Well, but this is the availability of a wide variety of solutions in the worst kind, this article describes the new features by 5.6 - management port to solve this problem.
Two recognized extra_port
    MySQL introduced a parameter after the 5.6.14 version extra_port to solve the problem of too many connections.
percona official description:
From the official document describes the view, in the MySQL startup use this parameter to specify a port request (and not a normal database service port conflicts), Percona Server listens from the port. Enabling this parameter can be solved using thread_pool feature, since all the connection pool worker querey slow or busy processing result DBA can not be locked through the normal port to connect DB, so the DBA can properly maintain the database.
 Used as follows:

mysql --port = 'extra-port-number' --protocol = tcp
Maridb official presentation (and percona introduced similar Readers can own translation)

Description:. Extra port number to use for tcp-connections in a 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 is mainly to control how many connections can be created through the management port.
 Official description:
    "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 test
  Test environment: 5.6.26-74.0-log Percona Server
    parameter settings:

max_connections = 1
extra_max_connections = 1

extra_port = 13306
Through the above two parameters to set the number of connections of the entire database. By the above configuration parameters, you can create up to four connections.
 Testing process by using the following command to try to connect to the database:
 Scene 1 analog business connect to the database in two different session tries to connect

mysql -uyang -pyang -h127.0.0.1 -P3306
Scene 2 analog DBA database connection, at two different session tries to connect

mysql -uroot -h127.0.0.1 -P3306
Scenario 3 analog DBA database connection, at two different session tries to connect

mysql -S /srv/my_3306/run/mysql.sock
All connections Scene 4 filled database, and then use the port as root connection extra_port

mysql --port = 13306 --protocol = tcp -uroot -h127.0.0.1
All connections 5 scenes filled database, and then use extra_port port to connect business identity

mysql --port = 13306 --protocol = tcp -uyang -pyang -h127.0.0.1
Scenario 6 The communication protocol is not specified, try to connect

mysql --port = 13306 -uyang -pyang -h127.0.0.1
Scene 7 Analog DBA and operations simultaneously connect to the database, try to connect at two different session

mysql -uyang -pyang -h127.0.0.1 -P3306

mysql -S /srv/my_3306/run/mysql.sock
mysql -uroot -h127.0.0.1 -P3306
Test results
      Scene 1, only connect 1, 2nd error ERROR 1040 (HY000): Too many connections
      Scene 2, only connect 2nd, 3rd error ERROR 1040 (HY000): Too many connections
      Scene 3, only connect 2nd, 3rd error ERROR 1040 (HY000): Too many connections
      Scenario 4, you can connect 2nd, 3rd error ERROR 1040 (HY000): Too many connections
      Scenario 5, you can not connect, suggesting "ERROR 1040 (HY000): Too many connections"
      Scenario 6, the business occupied the connection pool is full, the administrator can connect to 2 times,
      Scene 7 If the administrator log in first, business party is unable to connect; if the business side before landing, the administrator can log in once and could not land a second time
      You can connect a total of 4 times.
Four summary
    1 If you do not extra_port parameters, MySQL max_connection connections were all occupied, DBA can still root or that is a super permission to connect to DB on the DB management, but can only connect once. Business account can only login max_connection times.
    2 If extra_port parameters, MySQL max_connection connections were all occupied, DBA can create extra_max_connections + 1 connection with administrator privileges.
    3 max_connections property
      MySQL anyway a reservation for an administrator (SUPER) landing connection, connect to the database administrator for maintenance operations, even if the current number of connections has been reached max_connections. Therefore, the actual maximum number of connections MySQL can max_connections + 1; the parameters of the actual functioning of the maximum (the actual maximum number of connections available) 16384, that is, the parameter must not exceed 16384, 16384 even exceed also prevail; increase max_connections value of the parameter will not take up too much system resources. System resources (CPU, memory) occupied mainly depends on the query density and efficiency;
- Linux Detailed instructions alias settings (Linux)
- ASP.NET 5 is connected with the Redis server on the Linux platform (Server)
- CentOS system dual network card IP information configuration (Linux)
- Struts2 Result Types (Programming)
- How to Install Focuswriter 1.4.5 (Linux)
- Safe learning through self-extracting package to achieve invasion (Linux)
- About Java 7 module system (Programming)
- Elaborate .NET Multithreading: Using Task (Programming)
- Beautiful start Ubuntu installation tool Duck Launcher 0.64.5 (Linux)
- After Oracle 11g dataguard failover rebuild the archive logs are not applied to be NO problem (Database)
- Transfer files and permissions from Windows to Linux system by Samba (Linux)
- See Linux kernel, CPU, memory, and various versions of the command module and means (Linux)
- Linux system security reinforcement (Linux)
- Ubuntu How to install Pacman (Linux)
- Monitoring Linux System 7 command-line tool (Linux)
- Oracle capability i / o (stress test database server i / o performance) (Database)
- Ora-1092: OPI colleague K aborting process --- killed by OO Well killer (Database)
- Linux environment variable configuration and save places (Linux)
- PHP Performance Analysis and Experiment: Performance Micro Analysis (Programming)
- CentOS 6.5 Telnet SecureCRT use management tools (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.