Configuring Your System

The initial MySQL configuration, as installed, will work as-is; however, some useful features are turned off by default, and it starts out insecure .

In this book, we recommend that you change the following configuration options:

• We will be using InnoDB tables, so we need to perform a basic setup for these tables.

• We recommend that you turn on binary logging in all situations. This is highly useful for disaster recovery.

• We recommend that you turn on slow query logging. This tracks slow queries (as you might expect from the name ) and will help you optimize your applications.

MySQL uses options files to store configuration values. On Windows, your global options file can be located either in your Windows directory and named my.ini or in c:\my.cnf. We recommend using my.ini because .cnf is sometimes used as a file extension by other applications.

Under Unix-like operating systems, the global options file is typically located in /etc/my.cnf. If you want to run more than one server per machine, you can keep server-specific information in your data directory in my.cnf for each server. You can also allow individual users to have separate options in their own account ”these should be located in ~/.my.cnf (note the . before the filename).

Depending on your setup, you may or may not have an options file to begin with. Open up the file in your favorite text editor ”Notepad will do if you are using Windows ”or create it if it does not exist.

We suggest an initial options file as shown in Listing 1.1.

Listing 1.1 Suggested Options File

 [mysqld] # turn on binary logging and slow query logging log-bin log-slow-queries # InnoDB config # This is the basic config as suggested in the manual # Datafile(s) must be able to # hold your data and indexes. # Make sure you have enough # free disk space. innodb_data_file_path = ibdata1:10M:autoextend # Set buffer pool size to # 50 - 80 % of your computer's # memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # Set the log file size to about # 25 % of the buffer pool size set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M # Set ..flush_log_at_trx_commit # to 0 if you can afford losing # some last transactions  innodb_flush_log_at_trx_commit=1 

Most of this options file is based on the very simple InnoDB configuration file suggested in the MySQL manual. One point to note is that if you have an existing installation of MySQL with InnoDB and are adding this options file to it, you may have to comment out the line

 set-variable = innodb_log_file_size=20M 

by placing a # at the start of the line. (If you have an existing log file of a different size, MySQL will become confused .)

We will look further at configuration in Chapter 12, "Configuring MySQL."