MySQL Database Configuration in Detail

As noted when discussing MySQL's database configuration, the my.cnf file of MySQL can be a bit overwhelming. It is hard to know what to include in your MySQL configuration file. MySQL on Ubuntu keeps its system-wide configuration file at /etc/mysql/my.cnf. You can see the gist of MySQL's configuration file on the previous page in this series on MySQL. The following discussion is meant to interact with that summary so you can be informed about your choices when configuring MySQL.

[client] All MySQL settings under this section will be sent to MySQL clients when they try to connect.

port: This is the port on which the MySQL server listens for a connection. Even if you are using the server locally and will not have anyone but you on your computer using it, you must have a port for the server. This is due to the innate networking characteristic of Ubuntu's Linux infrastructure. If you choose to change default port number (which may be necessary if there is a conflict with another service), you must ensure the change is also done in the debian.cnf file in the same directory.
socket: This indicates the socket file you want MySQL to use. This file is temporary and is recreated every time MySQL starts. To have two MySQL database servers on the same computer, you just have to give each a different port and socket reference.

[mysqld_safe] The mysqld_safe setting is the optimal way to start the MySQL database server. When started with mysqld_safe, MySQL attempts to start using mysqld and pulls its configuration from the following sections of this configuration file: mysqld_safe, mysqld, and server.

socket: This is the location of the socket file to be used by the database server.
nice: This variable indicates the priority with which MySQL's commands should be executed by the system. The range is from the lowest priority of 20 to the highest priority of -20. The norm is 0.

[mysqld]

user: This is the user that MySQL should use on the local system. This is the username that will be registered in the logs and with whose permissions MySQL will function.
socket: This is the socket for the MySQL server. Obviously, this location and the location of the same name under [client] must be the same for things to work.
port: This is the port for the MySQL server. Again, there should be synchronicity between this listing and that in [client].
basedir: This is the base directory for MySQL's operations.
datadir: The datadir directive is where MySQL keeps the files of its databases.
tmpdir: This is MySQL's temporary directory and is used for temporary files.
skip-external-locking: External locking is used when you have multiple MySQL servers or database applications that access the same MySQL database files. This is not about applications that access data through the same instance of MySQL but is about separate processes that are operating independently. If you setup multiple MySQL servers with the same datadir, all MySQL servers will need to have external locking switched. Do this by commenting out this directive (i.e., put a hash symbol before it).
bind-address: The bind address is the IP address that MySQL should recognize as the host on which it runs. The default is 127.0.0.1, the IP address of the localhost.
key_buffer: This represents the largest size of key allowed in a database. If you don't understand what a key is, see the MySQL fundamentals page.
max_allowed_packet: This clarifies the maximum size of an allowed packet within the MySQL database.
thread_stack: This is the largest size allowed for all of the threads running at one time in the database process itself.
thread_cache_size: The size of the cache determines how much of information about MySQL threads are kept in memory so to make it more efficient.
myisam-recover: If you use the MyISAM database engine, do you want MySQL to support its recovery option?
query_cache_limit: This is the largest size of any cache entry. The query cache stores SELECT statements and their results.
query_cache_size: The MySQL database needs a minimum query cache size of 40k to work. If a query cache size of 16 MB is used, then MySQL will store a maximum of 16 queries of 1 MB in size.
log_error: This is where MySQL is to log information about any errors that occur.
expire_logs_days: This represents how many days the binary logs should be kept until being automatically removed by the system.
max_binlog_size: This represents the maximum allowable size of any MySQL binary log. If writing to the log would put it over that limit, MySQL will rotate the log so to keep the binary log under the limit set here.

[mysqldump] The mysqldump utility is used for archiving or otherwise backing up a MySQL database. This section in my.cnf configures how mysqldump will operate when used to access this database.

quick: When quick is included, mysqldump will dump the relevant databases row-by-row instead of buffering them completely before writing.
quote-names: Inclusion of this directive causes mysqldump to place all database names and other identifiers in quotes.
max_allowed_packet: This is the maximum size of the buffer allowed between client and server. The default is 1 GB.

[isamchk] The isamchk utility is used to check the consistency of a MySQL table and to repair or optimize one, as well.

key_buffer: The key buffer value indicates the maximum size a database key may be.

!includedir: This is how one includes configuration directories in MySQL's my.cnf file.

Knowing these basic configuration options of a MySQL database configuration file, you can adapt the service better to suit your needs. This is, however, only one part of a LAMP server. Additionally, you need to install Apache and use a scripting language like PHP or Python to control how content is processed.

MySQL Tutorials:

Install MySQL

Basic MySQL Configuration

MySQL Configuration In-Depth

Apache Tutorials:

Install Apache	Basic Configuration	Apache In-Depth
Recommend Web Hosting	Customising Apache	PHP Hosting

PHP Tutorials:

Install PHP	Basic PHP Configuration	PHP Configuration In-Depth
Learn PHP	PHP in HTML	PHP Keywords
PHP Expressions	PHP Variables	PHP Constants
Defined Constants	Constants Characteristics	PHP Datatypes
PHP Syntax Basics	PHP Loops

What is Ubuntu?

Subscribe

Installing Ubuntu

Laptops

Configure Ubuntu

Use Ubuntu

Servers

Programming

E-Commerce

Emulation

Administer Ubuntu

Change a Life

Stay Up-To-Date

MySQL Database Configuration on Ubuntu

A MySQL Database Server on Ubuntu

MySQL Tutorials:

Apache Tutorials: