How to Optimize MySQL

IMPORTANT

1. Always make full backups of all of your databases before making any changes to your my.cnf.
2. Avoid changes to your InnoDB settings unless you are very familiar with making those changes.  Many of the InnoDB settings must remain the same else data corruption will occur with your InnoDB tables.  We do not recommend making any changes to your InnoDB settings, unless you do so before creating your databases.

---

CentOS

There is a default my.cnf that comes with mysql (4+5) that will make mysql run a bit quicker if you have 2+ gig of ram

cp -f /usr/share/mysql/my-large.cnf /etc/my.cnf

There is also my-huge.cnf, or my-medium.cnf depending on your hardware setup.   Check the contents of these my*.cnf files for the one that's right for you.

*NOTE 1* the log-bin option is enabled  by default.  This will quickly use a lot of disk space.  It's recommended to comment out the log-bin line from your /etc/my.cnf, if it exists.

*NOTE 2* Take note of your old /etc/my.cnf file.  If you have innodb_file_per_table=1 make sure the new my.cnf you install also has this setting.  Similarly, if your old one does not have innodb_file_per_table=1 enabled, then your new my.cnf should also not have it enabled.   If the new my.cnf has a different setting for innodb_file_per_table, then it may corrupt your data.

Be sure to make full backups of your .sql files before doing any changes to your my.cnf.

---

Debian/FreeBSD

We don't currently have optimized my.cnf files for these OSs.
The /etc/my.cnf will rely on the internal defaults in the mysqld binaries.

---

CentOS and MySQL 5.6

New MySQL installs might not have any included my-*.cnf files.
We've added a few from MySQL 5.5 which seem to work with 5.6, eg:

cp /etc/my.cnf /etc/my.cnf.old
wget -O /etc/my.cnf http://files.directadmin.com/services/all/mysql/my-huge-5.5.cnf

---

MySQL 4.x

- Depreciated - DO NOT USE
If you've got mysql 4 (and not mysql 5), then you can use the following code in your /etc/my.cnf:

Referenced from the Forum

vi /etc/my.cnf [ENTER]

Press 'i' to enter insert mode, then paste:

[mysqld]
local-infile=0
skip-locking
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
server-id=1

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

Press ctrl-c to exit insert mode.  Then press shift-Z shift-Z to save and quit.   Restart mysqld:
Redhat:

/sbin/service mysqld restart

FreeBSD:

/usr/local/etc/rc.d/mysqld restart