Last updated on: 2020-10-09
Authored by: Jered Heeschen
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.
Find the configuration files
By default, you can find the MySQL® configuration files in:
/etc/mysql
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.
my.cnf 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.
Config groups
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 [mysqld], the client [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
Log files are the best place to start troubleshooting any program. By default, MySQL stores its log files in the following directory:
/var/log/mysql
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.
Network settings
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 running iptables.
mysqld and mysqld_safe
Behind the scenes, there are two versions of the MySQL server, mysqld and mysqld_safe. Both read the same configuration sections. However, mysqld_safe launches with more safety features
enabled to make it easier to recover from troubleshooting.
Both mysqld and 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
The 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.
Backups
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 mysqldump.
File copy
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.
mysqldump
Another approach to 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 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.
Restore from mysqldump
Restoring a mysqldump database is similar to how you created the dump, but you use mysql instead of mysqldump, as shown in the following restore command:
$ mysql -u root -p demodb < dbbackup.sql
Also, note that the