How to update the max_connections setting in MySQL

If you are getting "too many connections" errors in MySQL you can change the max_connections setting to allow more connections, assuming you have enough RAM to handle the increased number. This post looks at how to update the max_connections setting in MySQL.
The default setting for max_connections is 100. You can see what the current setting is by running the following SQL command from the MySQL command line tool, phpMyAdmin, or a similar tool:

show variables like "max_connections";

This will return a resultset like this, showing you what your current max connections setting is:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

You can change the setting to e.g. 200 by issuing the following command without having to restart the MySQL server (obviously it will only work if the user you are logged in as has sufficient permissions to do this):

set global max_connections = 200;

This will take effect immediately, but will be forgotten the next time MySQL is restarted. To make the change permanent you need to edit the my.cnf configuration file. On CentOS, RedHat and similar distributions this is at /etc/my.cnf; other distros will store it elsewhere.
Under the [mysqld] section add the following setting:

max_connections = 200

Now when you restart MySQL the next time it will use this setting instead of the default.
Note that increasing the number of connections that can be made will increase the potential amount of RAM required for MySQL to run. Increase the max_connections setting with caution!

Related posts:

• Location of MySQL's my.cnf file
• How to enable remote access to a MySQL database server
• Showing running queries in MySQL
• MySQL Query Cache
• Logging queries with MySQL

Setting the MySQL timezone per connection

There may be instances when you need to set your MySQL database to a different timezone from the one the server is in, but only for a specific website or application. It is possible to set the timezone on a per connection basis with MySQL and this post looks at how to do it.
For example, you might want to set the MySQL timezone to be the same as in Los Angeles. After you have connected to the MySQL database you would issue this query:

SET time_zone = 'America/Los_Angeles';

Any subsequent queries using date and time functions will report the date and time using that timezone instead of the MySQL server default for your system.
A slight catch to the above example is that the named timezones are not set up by default with MySQL and you must set them up yourself. From a Linux system this is trival as long as you are administering the server yourself. If not, you'll need to resort to using UTC offsets instead like in the following example SQL query:

SET time_zone = '-8:00';

If you don't have the timezone names set up, or the timezone name you are trying to use doesn't exists, you'll see an error like so:

#1298 - Unknown or incorrect time zone: 'America/Los_Angeles'

If you do have sufficient rights to the server, you can simply issue a command like this from the command line, assuming your zoneinfo files are at /usr/share/zoneinfo (they are on a CentOS / Red Hat Enterprise Linux setup):

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

This will load the timezone names and offsets into the "mysql" database and you can view the names by querying the "time_zone_name" table.
Note that you should run this regularly to rebuild the timezone database because timezone information does change over time. The SQL queries run truncate data from the appropriate tables so each time you run the full set of data is updated and the old records purged.

Related posts:

• MySQL CONVERT_TZ returns null
• How to enable remote access to a MySQL database server
• Changing the timezone with PHP
• Using date_add in MySQL to add intervals to dates
• Formatting Dates and Times with MySQL
• Delete All Data in a MySQL Table