MYSQL View maximum number of connections and modify maximum connections

MySQL view maximum number of connections and modify maximum connections

1. View the maximum number of connections
Show variables like '%max_connections% ';
2. Modify the maximum number of connections
Set GLOBAL max_connections = 200;

The following article is mainly to introduce you to the MySQL maximum connection number of changes, we all know that MySQL maximum connection number of the default value is 100, this value for a number of concurrent connections to the application of a lot of databases is not enough, when the connection request is greater than the default number of connections, there will be no connection to the database error, So we need to make it a little bigger. When using the MySQL database, you often encounter a problem that is "Can not connect to MySQL server." Too many connections "-mysql 1040 error because the number of connections that have not yet been released to MySQL has reached the maximum MySQL limit. Typically, the maximum number of connections to MySQL is 100 by default, up to 16384.

The two most common ways to modify the maximum number of connections are as follows:

First: The command line to view and modify the maximum number of connections (max_connections).

  >mysql -uuser -ppassword(命令行登录MySQL)

     mysql>show variables  like ‘max_connections‘ ;(查可以看当前的最大连接数)      msyql> set global max_connections=1000;(设置最大连接数为1000，可以再次查看是否设置成功)      mysql>exit   


The problem with this approach is that the maximum number of connections set is only valid in the MySQL current service process and will revert to its original state once MySQL restarts. Because the initialization after MySQL starts is to read the data from its configuration file, this method does not make changes to its configuration file.

Second: Modify the MySQL maximum connection number (Max_connections) by modifying the configuration file.

This approach is simple, just modify the MySQL configuration file my.ini or my.cnf parameters max_connections, change it to max_connections=1000, and then restart MySQL. But one of the hardest is My.ini where this file is found. Usually there are two possible, one is in the installation directory, the other is in the directory of data files, when installed if there is no man to change the directory, generally in the C:/programdata/mysql down the directory, Linux system generally in/etc directory.

others to note:

In programming, because the database is invoked with MySQL statements, a temporary variable is used to open the database each time the statement is executed, so when you use the MySQL statement, you remember to close the MySQL temp variable after each call to MySQL.

In addition, for large traffic, you can consider writing directly to the text, according to the predicted traffic, first define if it is 100 file name, when necessary, then all the text files in the analysis, and then import the database.

If it is the root account, you can see the current connection for all users. If you are a normal account, you can only see the connection you occupy

How do I get into the MySQL command line? MySQL installation directory has a bin directory, first use the command line to enter the directory, and then use mysql-uroot-p123456 to log in (note: The user name and password do not include "")

Command: Show Processlist;
If it is the root account, you can see the current connection for all users. If you are a normal account, you can only see the connection you are occupying.

Show Processlist; only the first 100 are listed, please use show full processlist if you want to list them all.
Mysql> show Processlist;

Command: Show status;
Command: Show status like '% under variable% ';
Aborted_clients the number of connections that have been discarded because the client did not properly close the connection already dead.
Aborted_connects the number of times the connection to the MySQL server has failed.
Connections the number of attempts to connect to the MySQL server.
Created_tmp_tables the number of suppressed temporary tables that have been created when the statement is executed.
Delayed_insert_threads the number of deferred plug-in processor threads being used.
Delayed_writes the number of rows written with the insert Delayed.
Delayed_errors the number of rows written with insert Delayed for some errors (possibly repeating key values).
Flush_commands the number of times the flush command was executed.
Handler_delete the number of times a row was requested to be deleted from a table.
The number of times the Handler_read_first request reads the first row in the table.
The Handler_read_key requests a number based on the key read line.
The number of times a handler_read_next request reads into a row based on one key.
The number of times a HANDLER_READ_RND request reads into a row based on a fixed position.
Handler_update the number of times a row in the table was requested to be updated.
The number of times the Handler_write request inserts a row into the table.
key_blocks_used the number of blocks used for the keyword cache.
Key_read_requests the number of times a key value was requested to be read from the cache.
Key_reads the number of times a key value has been physically read from disk.
Key_write_requests requests a keyword block to be written to the cache count.
Key_writes the number of times a key-value block is physically written to disk.
Max_used_connections the maximum number of connections that are used at the same time.
Not_flushed_key_blocks a key block that has been changed in the key cache but has not been emptied to disk.
Not_flushed_delayed_rows the number of rows waiting to be written in the Insert delay queue.
Open_tables the number of open tables.
Open_files the number of open files.
Open_streams Number of open streams (primarily for log records)
Opened_tables the number of tables that have been opened.
Questions the number of queries destined for the server.
Slow_queries the number of queries that will take more than long_query_time time.
threads_connected the number of connections currently open.
Threads_running the number of threads that are not sleeping.
Uptime How many seconds the server has worked.

MYSQL View maximum number of connections and modify maximum connections