The article,, covered a basic MySQL®
server setup on the Ubuntu® operating system. It described how to set the root password, create a database,
and add a user for the database. This artcile examines MySQL congfiguration in a little more detail so you can
adjust its configuration and be ready in case something goes wrong.
By default, you can find the MySQL® configuration files in:
If they're not there, however, you can use
mysqld to find the
configuration. Run the following command:
$ /usr/sbin/mysqld --help --verbose
The first part of the lengthy response describes the options
you can send to the server when you launch it. The second part displays
the configuration set during the server compilation.
Near the start of the output, find a couple of lines that look similar to the following example:
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 examines that list until it finds a configuration file.
Open and review the /etc/mysql/my.cnf file.
Comment lines, starting with #, document the use of the different settings. They show details about the
location of log files, database files, and other details.
The configuration file contains lines with a single word in square brackets, like [client] or [mysqld].
Those sections are configuration groups. They make important configuration elements more visible to the programs
that read the configuration file.
The server configuration section is, technically, a collection of tools. That includes the server (
mysql), and other tools. Those programs look in my.cnf to see how they should behave.
The client configuration section controls the
mysql client, and the
mysqld section controls the server configuration.
Log files are the best place to start troubleshooting any program. By default, MySQL stores its log files in the
You might need to use
sudo to get a listing of the files in that directory.
If you don't find the MySQL® logs in the default directory, check the MySQL configuration. View the my.cnf
file and look for a
log_error line, as in:
log_error = /var/log/mysql/error.log
If you don't see a line like that, create one in the mysqld section so MySQL® can use its own error log. Use
the location in the example, creating the /var/log/mysql directory if it doesn't already exist. Then, restart
MySQL to make the change.
Make sure the user can write to the chosen log directory by controlling the
mysql process. The user running the
process is defined in the user configuration value for mysqld in my.cnf.
There might be a "port" setting under both the client and server configuration sections. The port under
the server section controls what port the server listens to. The default port is
3306 but, you can change it.
The port in the client section tells the client what port to connect to by default. You generally want both port
settings to match.
If you are using the default settings, you won't see the port entries in the configuration file. If you want to change
the port, add the following lines in the appropriate categories:
[client] port = 3306 [mysqld] port = 3306
The other network setting to look for is the bind-address value. That usually gets set to the address for localhost,
127.0.0.1. By binding to localhost, the server ensures no one can connect to it from outside the local computer.
If you're running your MySQL server on a different computer from your application, you should bind to a remotely-accessible
address instead of the localhost. Change the bind-address setting to match your public IP address. For security reasons,
you should use a backend IP address on a network.
If you don't see a bind-address entry, you should put one into 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 provide firewall access if you're
Behind the scenes, there are two versions of the MySQL server,
mysqld_safe. Both read the same configuration
mysqld_safe launches with more safety features enabled to make it easier to recover from troubleshooting.
mysqld_safe read configuration entries in the mysqld
section. If you include a mysqld_safe section, then only mysqld_safe uses those values.
By default, the MySQL service launches
mysqld_safe, which is appropriate.
mysqladmin tool lets you perform some administrative functions from
the command line, which this article does not address. You might
explore the tool in more depth later to see what it can do,
particularly if you need to build scripts that perform functions such as
checking the status of the server or creating and dropping databases.
You have the following options to backup your databases, apart from the usual back up the whole computer approach.
The main two options are to copy the database files or use
By default, MySQL creates a directory for each database in its data directory, /var/lib/mysql.
After you find the data directory, wait a moment before making a copy of it. When the database server is active, it
writes new values to tables. Don't interrupt this processes to avoid potentially corrupting your backup.
To ensure that you copied the database files cleanly, you should shut the MySQL server down entirely before the copy.
You can lock the database as read-only for the duration of the copy. When you finish, 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;"
Run the following command to unlock the database when you're done:
$ mysql -u root -p -e "UNLOCK TABLES;"
The MySQL client option,
-e, tells the client to run the query in quotes as if we entered it in the MySQL® shell.
If you're writing these commands in a script, you can put the password in quotes right after
-p with no space between the
two, similar to the following examples:
$ mysql -u root -p"password" -e "FLUSH TABLES WITH READ LOCK;" $ mysql -u root -p"password" -e "UNLOCK TABLES;"
Make sure you set the permissions on that file to restrict read access for password-security reasons.
Another approach to backing up your database is to use the
mysqldump tool. Rather than copying the database
mysqldump generates a text file that represents the database. By default, the text file contains a list
of SQL statements to recreate the database, but you can also export the database in another format like .CSV
or .XML. You can read the man page for
mysqldump to see all its options.
The statements generated by
mysqldump go straight to standard output. You can specify a to redirect the output by
running the following command in the command line:
$ mysqldump -u root -p demodb > dbbackup.sql
This command tells
mysqldump to recreate 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 the MySQL client so that you can
include the password directly after
-p in a script.
mysqldump database is similar to how you created the dump, but you use
mysql instead of
as shown in the following restore command:
$ mysql -u root -p demodb < dbbackup.sql
Also, note that the
< angle bracket changes direction. That switches the command from redirecting its output to extracting
input from the existing file. That input is sent to the
mysql command, causing the instructions to recreate the database.
By default, the SQL statements generated add to existing database tables and not overwrite them. If you're restoring a backup
over an existing database, you should drop the database tables first, or drop and recreate the database itself. You can change
that behavior by using the
$ --add-drop-table option with the command that creates the
mysqldump. That causes
to add a command to the backup files it writes that drops tables before recreating them.
The database engine is the process that works behind the scenes,
writing to and reading data from files. You only need to know about this if you want to run an
application that's been optimized for a particular database engine.
Applications that need tables create them on-demand and create the engine type automatically.
To see the engine your database's tables use, you can run the following command in the MySQL shell,
changing demodb to the name of your database.:
$ SHOW TABLE STATUS FROM demodb;
Ideally, you won't need to choose an engine. If you're not very familiar with MySQL, allow the application
to choose one by default. If you're writing the application, use the default engine until you're more comfortable
with your options.
Follow your database administrator's (DBA) instructions, if you have one.
The two database engines used most often with MySQL are
InnoDB. The default database engine for MySQL
version 5.1 and earlier is
InnoDB is the default database engine starting with MySQL version 5.5.
MyISAM has been the default in MySQL for a while, it's the most compatible choice of the two main engines.
Certain types of searches perform better on
InnoDB. Even though it's the older of the two, it
might be the best choice for a given application type.
NOTE: A DBA can help you achieve the best
InnoDB performance because
many adjustments to your environment and access patterns. The default configuration is optimal for everyday software
InnoDB is more fault-tolerant than
MyISAM and handles crashes and recovery with a much smaller risk of database
If your application requires InnoDB and you're using MySQL 5.1 or earlier, there might not be any settings
already in the my.cnf configuration file. That can be a problem if you're running on a server that doesn't
have much memory.
The following settings can get you started with InnoDB on a shared server with 256 megs of RAM:
innodb_buffer_pool_size = 32M innodb_log_file_size = 8M innodb_thread_concurrency = 8 innodb_file_per_table
Add those to the [mysqld] section of the configuration file. Again, those are
only rough guides. They can get you started but they are not optimized. Consult with your DBA or
experiment with incremental changes over time to improve performance.
Updated 3 months ago