Configuring MySQL server on Arch


The previous article covered a basic MySQL server setup on Arch Linux. You set the root password, created a database, and created a user for the database. This article provides details about MySQL configuration, so that you can change it if something goes wrong.

Finding the configuration files

By default, the MySQL configuration file, my.cnf, is located in the following directory:

/etc/mysql

If the file is not there, you can find it by running the following command:

/usr/sbin/mysqld --help --verbose

A large amount of text is returned. The first part describes the options that you can send to the server when you launch it. The second part is all the configuration information that was set when the server was compiled.

The configuration file information that you want appears near the start of the output. Find a couple lines that look as follows:

Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

The server works through that list until it finds a configuration file.

my.cnf

After you locate the my.cnf file, open it and look at its contents.

/etc/mysql/my.cnf

Any lines starting with # are comments, and they mostly document what the different settings. Read through the comments to find details like the location of log files and where the database files are kept.

Configuration groups

Some lines in the configuration file contain only a word in square brackets, like [client] or [mysqld]. Those are configuration groups, and they tell the programs that read the configuration file which parts they should pay attention to. Although this documentation has focused on the server part, MySQL is technically a collection of tools that includes the server (mysqld), the client (mysql), and some other tools that are covered later. These programs look in my.cnf to see how they should behave.

Log files

If something goes wrong, the best place to start troubleshooting any program is its log/mysql directory. Open in the my.cnf file and look for a log_error line, as follows:

log_error = /var/log/mysql/error.log

If you don't see a line like that, create one in the mysqld section so that MySQL will use its own error log. We recommend using the location in the example, creating the /var/log/mysql directory if it doesn't already exist. Then restart MySQL to make the change.

Ensure that the log directory you choose can be written to by the user controlling the MySQL process (usually "mysql"). The user running the process will be defined in the "user" configuration value for mysqld in my.cnf.

Network settings

Both the client and server configuration sections might contain a port setting. The port setting in the server section controls what port the server will listen to. By default, that port is 3306, but you can change it.

The port in the client section tells the client what port to connect to by default. Generally, the client and server port settings should match.

If you don't see the port entries in the config file that just means they're using the default. If you want to change the port the lines in the appropriate categories, as shown in the following example:

[client]
port = 3306

[mysqld]
port = 3306</pre>

The other network setting to look for is the bind-address value. This value is usually set to the address for localhost, 127.0.0.1. By binding to localhost the server, no one can connect to it from outside the local machine.

If you're running your MySQL server on a different machine from your application, you should bind to a remotely-accessible address instead of localhost. Change the bind-address setting to match your public IP address (or, better, a back-end IP address on a network that fewer machines can access).

If you don't see a bind-address add one to the mysqld category to help control access to the server:

[mysqld]
bind-address = 127.0.0.1

Remember to account for the client's hostname when you set up your database users and to poke a hole in your firewall if you're running iptables.

mysqld and mysqld_safe

There are actually two versions of the MySQL server, mysqld and mysqld_safe. Both read the same config sections. The main difference is that mysqld_safe launches with a few more safety features enabled to make it easier to recover from a crash or other problem.

Both mysqld and mysqld_safe will read config entries in the "mysqld" section. If you include a "mysqld_safe" section, then only mysqld_safe will read those values in.

By default the mysql service launches "mysqld_safe". We recommend that you do not change that behavior, unless you have a specific reason.

Backups

You have a few options when it comes to for backing up your databases apart from the usual "back up the whole machine" approach. The main ones are copying the database files and using mysqldump.

File copy

By default MySQL creates a directory for each database in its data directory, as shown in the following example:

/var/lib/mysql

Before you copy the database files, you need to ensure that you can make a clean backup. When the database server is active, it could be writing new values to tables at any time. If it writes to a table halfway through your copy, some files will change and cause a corrupt backup.

To ensure that the database files are copied cleanly, you can shut the MySQL server down entirely before the copy. This option is safe but not always ideal. Another option is to lock the database as read-only for the duration of the copy. Then when you're done, you release the lock. That way your applications can still read data while you're backing up files.

Lock the databases to read-only by running the following command from the command line:

mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"

To unlock the database when you're done, run:

mysql -u root -p -e "UNLOCK TABLES;"

The -e option tells the mysql client to run the query shown in quotation marks as if you had entered it in the mysql shell.

Note that if you're setting up these commands up in a script, you can put the password in quotation marks right after -p with no space between the two, as in:

mysql -u root -p"password" -e "FLUSH TABLES WITH READ LOCK;"
mysql -u root -p"password" -e "UNLOCK TABLES;"

Ensure that you set the permissions on any script files to restrict read access. so that your password is not visible.

mysqldump

Another method for backing up your database is to use the mysqldump tool. Rather than copying the database files directly, mysqldump generates a text file that represents the database. By default the text file contains a list of SQL statements you would use to re-create the database, but you can also export the database in another format like CSV or XML. You can read the main page for mysqldump to see all its options.

The statements generated by mysqldump go directly to standard output. You should specify a file to redirect the output to when you run the command. For example:

mysqldump -u root -p demodb &gt; dbbackup.sql

That command tells mysqldump to re-create the demodb database in SQL statements and to write them to the file dbbackup.sql. Note that the username and password options function the same as in the mysql client, so you can include the password directly after -p in a script.

Restoring from mysqldump

The command that you use to restore a database that was backed up by using mysqldump looks similar to the command used to create the backup, but you use mysql instead of mysqldump, as follows:

mysql -u root -p demodb &lt; dbbackup.sql

The sign also changes, from a greater-than sign to a less-than sign. That switches the command from redirecting its output to telling it to read its input from the existing file. That input is sent to the mysql command, causing the mysqldump instructions to re-create the database.

Note that by default the SQL statements generated would just add to existing database tables, not overwrite them. If you're restoring a backup over an existing database, you should drop the database's tables first, or drop and re-create the database itself. You can change that behavior by using the --add-drop-table option with the command that creates the mysqldump file. That option causes mysqldump to add a command to the backup files it writes that will drop tables before re-creating them.

Database engine

The database engine writes data to and reads data from files, a process that works in the background. Usually you don't need to know anything more about it, but you might want to run an application that's been optimized for a particular database engine.

The engine type is set when a table is created. Tables are usually created by the application that's going to use them, so the syntax for setting the engine type varies.

To see the engine used by your database's tables, you can run the following command in the mysql shell, changing demodb to the name of your database:

SHOW TABLE STATUS FROM demodb;

Choosing an engine

Ideally you don't need to choose an engine. If you're not very familiar with MySQL, let the application select the engine, and if you're writing the application, use the default engine until you're more comfortable with your options.

The two database engines used most often with MySQL are MyISAM and InnoDB. The default database engine for MySQL version 5.1 and earlier is MyISAM, and InnoDB is the default database engine starting with MySQL version 5.5.

MyISAM

Because MyISAM has been the default in MySQL for a long time, it's the most compatible choice of the two main engines. Certain types of searches perform better on MyISAM than on InnoDB. Although it's the older of the two engines, MyISAM can be the better engine for a given application type.

InnoDB

InnoDB is more fault-tolerant than MyISAM and handles crashes and recovery with a much smaller chance of database corruption.

The main trouble with InnoDB is that for best performance it requires a great deal of tuning for your environment and access patterns. If you have a DBA, that should not be a problem, but if you're a developer who just wants a database up and running for a test server, you probably don't want to deal with tuning InnoDB.

If you're running an application that requires InnoDB and you're using MySQL 5.1 or earlier, the my.cnf configuration file might not contain any InnoDB settings. That can be a problem if you're running on a server that doesn't have an abundance of memory.

Following are some settings to get you started with InnoDB on a shared server with 256 MB of RAM are:

innodb_buffer_pool_size = 32M
innodb_log_file_size = 8M
innodb_thread_concurrency = 8
innodb_file_per_table

Add those settings to the [mysqld] section of the configuration file. These values are enough to get you running, but they are not optimized. To optimize the values, consult your DBA or experiment with incremental changes over time.

Summary

Now you should have MySQL configured for your environment, and you might even have accounted for the database engine being used by your tables.

The next article describes some basic tasks that you can run in the mysql shell to manipulate and observe your database at a high level.



Was this content helpful?




© 2014 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER