How to increase MySQL connections(max_connections)?

Question

Every socket of MySQL Database will have defaults connections as 100 but I am looking for any way to increase the number of possible connections > 100 to a socket connection of MySQL Database.

Abdul Manaf · Accepted Answer · 2014-03-10T10:48:07.740

158

If you need to increase MySQL Connections without MySQL restart do like below

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL max_connections = 150;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 150   |
+-----------------+-------+
1 row in set (0.00 sec)

These settings will change at MySQL Restart.

For permanent changes add below line in my.cnf and restart MySQL

max_connections = 150

edited Mar 10 '14 at 10:48

answered Mar 10 '14 at 10:36

Abdul Manaf

4,768
3
27
34

Thank you but may I know the maximum limit ? – shekhar Mar 10 '14 at 10:39
what do you mean by maximum limit ? – Abdul Manaf Mar 10 '14 at 10:40
@shekhar:- The maximum concurrent connection can be maximum range: 4,294,967,295. Check this:- http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_user_connections – Rahul Tripathi Mar 10 '14 at 10:40
@AbdulManaf Thank you so much. so you sure that we no need to restart MySQL DB ? – shekhar Mar 10 '14 at 10:46
@AbdulManaf May you help me if MySQL having more instances running how I have to move for achieving this ? – shekhar Mar 10 '14 at 10:48
@shekhar : You don't need to restart MySQL DB, but whenever MySQL will be restarted in future the setting will back to their default, for permanent changes add it in my.cnf and restart MySQL – Abdul Manaf Mar 10 '14 at 10:50
If You have more than one MySQL Server, You can change it one by one – Abdul Manaf Mar 10 '14 at 10:51
@AbdulManaf No I mean one MySQL server can have multiple instances , in such cases how can I approach ? – shekhar Mar 10 '14 at 11:10
Tks,I change the file /etc/my.cnf adding line max_connections = x – elirigobeli Dec 13 '19 at 17:24
How to decide what value to set for max_connections? – Daryl Aranha Aug 27 '20 at 08:50

Rahul Tripathi · Answer 2 · 2014-03-10T10:42:39.997

12

From Increase MySQL connection limit:-

MySQL’s default configuration sets the maximum simultaneous connections to 100. If you need to increase it, you can do it fairly easily:

For MySQL 3.x:

# vi /etc/my.cnf
set-variable = max_connections = 250

For MySQL 4.x and 5.x:

# vi /etc/my.cnf
max_connections = 250

Restart MySQL once you’ve made the changes and verify with:

echo "show variables like 'max_connections';" | mysql

EDIT:-(From comments)

The maximum concurrent connection can be maximum range: 4,294,967,295. Check MYSQL docs

edited Mar 10 '14 at 10:42

answered Mar 10 '14 at 10:36

Rahul Tripathi

168,305
31
280
331

Thank you but what are the maximum connections ? – shekhar Mar 10 '14 at 10:42
@shekhar:- Also to add that you may run out of memory if by any chance you reach that limit. As a suggestion, you should start simple, add complexity later as required. So take a smaller figure like 100 or may be 1000 – Rahul Tripathi Mar 10 '14 at 10:46
But we have to restart MySQL DB right. May you help me if MySQL having more instances running how I have to move for achieving this ? – shekhar Mar 10 '14 at 10:46
1

@shekhar:- Yes it is a good practice and also it will allow you to reflect the changes effectively! – Rahul Tripathi Mar 10 '14 at 10:47
11

@shekhar:- `Before increasing MySQL’s connection limit, you really owe it to yourself (and your server), to find out why you’re reaching the maximum number of connections. Over 90% of the MySQL servers that are hitting the maximum connection limit have a performance limiting issue that needs to be corrected instead.` – Rahul Tripathi Mar 10 '14 at 10:48
Please tell me , how to approach in the case of MySQL running with multiple instances ? – shekhar Mar 10 '14 at 11:10
@RahulTripathi this may be a few years but i've hit the limit before while doing a high speed parallel gziped piped export/backup. For instance on very large sites, you will immediately hit the connection limit when backing up in a parallel form (its a sin not to gzip/zip the export, as you save up to 10x space reducing disk bottleneck by 10x), at the same time the site is live with people connecting. Taking too long to export without downtime could mean the exported data being out of sync when you have many hundreds of tables and hundreds of gigabytes of data. – Error Messages Aug 23 '21 at 02:24
@ErrorMessages use hot backup for that, no need to worry about data being out of sync. – Andrew Feb 22 '23 at 11:56

score 6 · Answer 3 · edited Nov 19 '17 at 15:58

I had the same issue and I resolved it with MySQL workbench, as shown in the attached screenshot:

in the navigator (on the left side), under the section "management", click on "Status and System variables",
then choose "system variables" (tab at the top),
then search for "connection" in the search field,
and 5. you will see two fields that need to be adjusted to fit your needs (max_connections and mysqlx_max_connections).

Hope that helps!

The system does not allow me to upload pictures, instead please click on this link and you can see my screenshot...

How to increase MySQL connections(max_connections)?

3 Answers3

Linked

Related