How to Reset MySQL max_connections Limit

connectionslimitsMySQLmysql-5.7

Last week I updated mysql max_connections to 1000 from 151 using

use mysql;
set global max_connections = 1000;

Today there was a power outage at the hosting company and the servers shut down and were then restarted. After restart, the max_connections variable was reset to 151.

Is this default behavior? How can I prevent max_connections global from getting reset?

Reference:

MySQL 5.7

Best Answer

Yes this is the default behavior. How ? The default value for max_connections is 151.

This is true for MySQL 5.7, MySQL 5.6, and MySQL 5.5

If /etc/my.cnf does not have max_connections, then 151 comes up as max_connections.

You can just run set global max_connections = 1000;.

Your hosting company must put that entry in /etc/my.cnf for you

[mysqld]
max_connections=1000

Otherwise, every restart of mysqld will reset max_connections to 151.

I have suggested this before

Jul 06, 2012 : How to resolve too many connections and fatal error in mysql running on vps

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

Mysql – Upgrade thesql from 5.0 to 5.5 in master-master configuration leads to broken replication

Since all the data is MyISAM, you should expect table locks rather frequently since INSERT , UPDATE, and DELETE command issues a full table lock.

If you have a lot of tables that use auto increment keys, you should add the following

Server1

[mysqld]
auto_increment_increment = 5
auto_increment_offset = 1

Server2

[mysqld]
auto_increment_increment = 5
auto_increment_offset = 2

This will prevent PRIMARY KEY collisions with autoincrement columns

I have other suggestions in my post I have been tasked with Mysql Master-Master replication?

Best Answer

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

Mysql – Upgrade thesql from 5.0 to 5.5 in master-master configuration leads to broken replication

Related Question