Author Topic: How do I change MySQL settings?  (Read 35812 times)

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
How do I change MySQL settings?
« on: September 20, 2005, 04:41:25 PM »
Unless you're on a dedicated/virtual-private server, you cannot change MySQL's settings.

If you have root access to /etc/my.cnf and the root user of MySQL, there are two ways.  One is real time (MySQL 4.0 and higher), and the other requires a restart.

The /etc/my.cnf file will usually look like this:

Code: [Select]


By default, you'll only have the one section you're interested in - mysqld or mysql.server.  Otherwise, look for the mysqld section.

Under it, you can add settings.  For MySQL 3.x, the format is:

set-variable = setting_name=value

For MySQL 4.x and above, it is simply:

setting_name = value

The other method is only temporary.  It's useful for testing new values (although they won't, obviously, be retroactive, so status.php will take some time to show their effects) or for changing settings without a restart.  Once you're into MySQL as root - either with phpMyAdmin or the shell, run this query:

SET @@global.setting_name = value;

For this version, there are two things to keep in mind about the value.  If it is not numeric, you have to quote it ('hello'), and if it has a unit (15M), you have to multiply it out.  For example, M means * 1024 * 1024, so you might use:

SET @@global.setting_name = 15 * 1024 * 1024;

Other expressions are, of course, allowed too.  This change should be relatively immediate, but if you restart mysqld it will revert to whatever is in my.cnf, or the default.