How to Customize the MySQL Server

Knowing how to customize the MySQL server will come in handy as you learn your way through programming. The MySQL server uses two different filenames for its configuration settings:

• my.cnf for Linux and Mac systems
• my.ini for Windows systems

One of the more confusing features about the MySQL server is that there are three ways to specify configuration settings:

• They can be compiled into the executable server program when built from source code.
• They can be specified as command-line options when the server starts.
• They can be set in the MySQL configuration file.

You can compile all the settings you need into the MySQL executable server program and run with no configuration file at all (that's the approach the MAMP all-in-one package takes). The downside to that is it’s hard to determine just which settings are set to which values.

Most MySQL server installations use a combination of compiling some basic settings into the executable server program and creating a basic configuration file for the rest. The setting values set in the configuration file override anything compiled into the executable server program or set on the command line.

As with the Apache web server, the MySQL database server has lots of options you can change in the configuration file to fine-tune how things work. That said, there are only a few items that you’d ever really need to tweak in a normal setup.

The core server settings

The core server settings define the basics of how the MySQL server operates. These settings in the XAMPP for Windows setup look like this:

[mysqld]
port = 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql"
tmpdir = "C:/xampp/mysql/tmp"
datadir = "C:/xampp/mysql/data"
log_error = "mysql_error.log"

The port setting defines the TCP port the MySQL server listens for incoming requests on. The socket setting defines the location of a socket file that local clients can use to communicate with the MySQL server without using the network.

The basedir, tmpdir, and datadir settings define the locations on the server that MySQL will use for storing its working files. The datadir setting defines where MySQL stores the actual database files.

Working with the InnoDB storage engine

The InnoDB storage engine provides advanced database features for the MySQL server. It has its own set of configuration settings that control exactly how it operates and how it handles the data contained in tables that use that storage engine.

There are two main configuration settings that you may need to tweak for your specific MySQL server installation:

innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend

The innodb_data_home_dir setting defines the location where MySQL places files required to support the InnoDB storage engine. This allows you to separate those files from the normal MySQL database files if needed.

The innodb_data_file_path setting defines three pieces of information for the storage engine:

• The filename MySQL uses for the main InnoDB storage file
• The initial size of the storage file
• What happens when the storage file fills up

To help speed up the data storage process, the InnoDB storage engine pre-allocates space on the system hard drive for the database storage file. That way, for each data record that's inserted into a table, the storage engine doesn’t need to ask the operating system for more disk space to add to the database file — it’s already there! This greatly speeds up the database performance. The second parameter defines the initial amount of disk space that the InnoDB storage engine allocates.

The third parameter is where things get interesting. It defines what the InnoDB storage engine does when the space allocated for the storage file becomes full. By default, the InnoDB storage engine will block new data inserts to the tables when it runs out of allocated storage space. You would have to manually extend the storage file size.

When you specify the autoextend setting, that allows the InnoDB storage engine to automatically allocate more space for the file. That’s convenient, but it can also be dangerous in some environments. The InnoDB storage engine will keep allocating more storage space as needed until the server runs out of disk space!

When you use the InnoDB storage engine for your MySQL applications, it’s always a good idea to keep an eye on the storage space folder to make sure it’s not taking up all the server disk space.