MySQL remains one of the most common and consistent elements in the modern application programming stack. If you want a database for your app or service, and your needs are fairly generic, MySQL is one of the easy defaults. It’s widely used and well-understood, so you can draw on a wealth of community knowledge and experience when deploying MySQL for your particular application.
The latest major revision, MySQL 8, fixes a number of long-standing issues with MySQL—changes major enough to demand a revision to the left of the decimal point.
In this article we’ll walk through the basic steps needed to bring up a standard MySQL 8 server installation. Along the way we’ll pay attention to the settings you need to keep in mind both when installing fresh or upgrading from a previous MySQL installation.
Note that the discussion in this article focuses on the community open-source edition of MySQL 8, not the enterprise edition. The enterprise edition has more advanced features available only with a commercial license.
Where to download MySQL 8
Oracle maintains its own download site for MySQL 8. Note that each operating system’s downloads are stored on a different page; you’ll need to select an OS from the “Select Operating System” drop-down menu. Otherwise the website will default to offering downloads for the OS detected through your browser.
How to install MySQL 8
The process for setting up MySQL varies widely depending on the target operating system.
- Microsoft Windows and MacOS users can download and run a GUI-based installer. This walks the user through the setup process and provides defaults for the most common configuration options.
- Linux users can download generic binaries provided by Oracle, or install MySQL via the package management system of their Linux distribution.
Some things to keep in mind when installing MySQL on Linux:
- MySQL, or at least its most recent edition, might not be available in the default software repository for a given Linux distribution. For instance, on Fedora Linux, you might need to add the MySQL repository manually to the package management system, and then install from that repository.
- You shouldn’t switch from using a generic Linux binary to a version provided by a distro’s package manager unless you completely uninstall the previous version. (The same applies in reverse, too.) Your best bet is to stick with the same distribution path whenever possible for the sake of consistency.
MySQL is also available as a Docker image. Many of the most common options in MySQL can be changed by passing command-line flags to the container. If you need more granular control, you can create a custom configuration file and pass that to the container. (The container image can be further customized by adding layers, if you’re a Docker maven.)
Another option available from Oracle for many platforms is a generic binary archive with no installer. This has to be set up by hand, mainly by creating and editing its configuration file. The advantage is that it’s easy to remove the whole thing later, simply by deleting the binary and its support files.
Upgrade to MySQL 8 from a previous version
If you’re moving to MySQL 8 from a previous version of MySQL, you have a choice between two general strategies for handling the upgrade. Note that only upgrades from GA releases (preferably the latest GA release) of MySQL 7 are supported. Upgrades from non-GA releases of MySQL 7 and earlier releases of MySQL (e.g. MySQL 6) are not guaranteed to work.
- In-place upgrade. This involves replacing the existing MySQL 7 binaries with the MySQL 8 binaries.
- Export and re-import. MySQL’s documentation calls this process a “logical upgrade.” This may be performed with the
mysqldumpormysqlpumputilities.
In either case, you should ensure that the databases you’re upgrading don’t have lingering compatibility issues. Also be conscious of the upgrade methods specific to the platform you’re running, since the upgrade process for a Windows or MacOS system won’t be anything like the Linux process.
Configure MySQL 8
The sheer number of configuration options for MySQL can be overwhelming, but mercifully many of them aren’t needed in the initial phases of the installation or upgrade process.
Edit the MySQL 8 configuration file
The Microsoft Windows and MacOS graphical installers for MySQL 8 step the user through configuring the most important options, with sensible defaults chosen automatically. Those selections are recorded in a configuration file used by MySQL. Linux users will also have a basic configuration file with some auto-generated defaults, although not nearly as many settings are added by default on Linux.
To find out where your MySQL configuration file is located, run mysql —help from the shell and look for the line that reads, “Default options are read from the following files in the given order.” Configuration files and their locations will be listed there.
Unfortunately, the defaults for all the options in the MySQL configuration file aren’t listed in the config file itself. You’ll find them in the documentation for the file.
One good way to reduce headache here is to use the MySQL Workbench application, included with most MySQL installations, to edit the options file. The MySQL Workbench provides you with a tabbed GUI listing every possible setting in the file and a description of what each setting does. MySQL Workbench also makes it harder to mess up the config file by, say, accidentally inserting a linebreak where it doesn’t belong.
Initially, MySQL Workbench doesn’t have an options file registered with a particular MySQL connection. To add an options file, follow these steps:
- In the MySQL Workbench homepage, right-click on the instance connection you want to add an option file for and select “Edit Connection.”
- Under the System Profile tab, fill in the full path to the option file in the “Configuration File” line.
- Click “Test Connection” to ensure everything works.
- When you next open that connection, you can edit the option file settings from the left-hand tree menu by way of “Instance | Options File.”
Set the root password for MySQL 8
If there’s one part of MySQL setup that needs attention, it’s setting a root password for the server right from the get-go to prevent security disasters.
The Windows and MacOS installers for MySQL 8 prompt the user for a root password during the install process. Some Linux installers for MySQL 8, such as Ubuntu’s apt-get-based installation, allow the user to supply the root MySQL password.
In other cases, such as when using Fedora’s yum or dnf installer, the root password is set randomly and echoed in MySQL’s error log. It can be revealed with the command sudo grep ‘temporary password’ /var/log/mysqld.log. This password should be changed as soon as possible. Be sure to replace it with a password at least as long and complex.
Use MySQL 8’s new authentication method
If you want to use MySQL Workbench on Linux—recommended, because it makes working with MySQL a good deal more convenient—there’s another configuration option in MySQL 8 that you need to be aware of.
When installing MySQL 8, you’ll be presented with the option to use the new SHA-256 password authentication method. This method is far more secure than the legacy “native password” method, and for that reason is recommended as the default for new installations.
However, the new method has the major downside of not being compatible with anything other than the most recent builds of MySQL client software, in particular MySQL Workbench.
Plus there’s an additional caveat: The latest builds of the client software aren’t available for all Linux editions via the default installation path. In other words, if you install a brand-new MySQL 8 on, say, Ubuntu 18.04 from the MySQL repositories for that OS, the included copy of MySQL Workbench won’t support SHA-256 authentication!
There are a few ways around this obstacle:
- Download a development release of MySQL Workbench. This depends entirely on whether a development release has been built for your operating system, but most Linuxes seem to have up-to-date development binary builds available. This is generally the easiest route, and updating Workbench is less potentially flaky than tinkering with MySQL’s own security settings.
- If you’re only permitting connections to MySQL from
localhost, you can eschew using SHA-256 authentication and not lose much in the way of security. This doesn’t have to be your permanent solution; you can use the legacy connection protocol until you obtain a more recent build of the client software for the system, then change to the new authentication method. Note that this means you have to change the authentication method in MySQL’s config file, and alter the MySQL user account to match. - Build the client software from scratch. This is the most laborious workaround, so it’s only recommended for those who have literally no other option. The vast majority of us should never have to do this.
Major MySQL 8 configuration options
The vast majority of options for MySQL 8 don’t need to be set out of the gate. A few, like the above-mentioned client security protocol, should be decided as early on as possible, whether you use the GUI installation wizard or edit the config file by hand. Here are several others:
Additional MySQL users
Whenever possible, don’t use the root account for connection from an application that uses MySQL. Create an account that has full permissions only on the databases you need for that application. The MySQL Workbench app makes this possible with only a couple of clicks.
MySQL connection protocols
MySQL 8 can accept incoming connections to the server in multiple ways:
- TCP/IP. This is the most widely used and understood way to connect to a MySQL instance, since it allows both local and remote connections. It’s also the slowest overall, since the traffic has to go through the entire TCP/IP stack, although that is generally only a concern in scenarios where you need extreme performance. For any situation where you need remote client connectivity, TCP/IP is the easy answer. However, you need to pay close attention to the security of the connection. Use the more advanced password security described above, maybe restrict connections to a VPN, and so on.
- Unix sockets/named pipes. These are two roughly equivalent features in Unix/Linux and Windows, respectively. Sockets and pipes are best for when the client/application and server are on the same system, since data can be transferred between client and server all the faster. It’s also possible to publish such connections across a network, but it’s cumbersome to do so; you’re better off just using TCP/IP in such cases.
- Shared memory. This is the fastest connection option between server and client, but also the least flexible. It works only if server and client are on the same physical system, and it can be difficult for the server to detect if the client has timed out or been killed, causing unterminated connections to accumulate and waste resources.
Note that on Unix systems, any connections from localhost default to using Unix sockets for the sake of performance.
MySQL logging options
In addition to the general error log, MySQL can maintain three other kinds of logs. All are disabled by default, but can be enabled as needed for debugging during the configuration process.
- General. This provides a detailed running summary of MySQL’s activities from startup to shutdown.
- Slow query. Any queries that take more than x seconds to execute (the number is configurable) can be logged for debugging purposes.
- Binlog. Short for “binary log,” this log can be configured to contain binary dumps of all the modifications to database files. For obvious reasons this has a major performance impact, so it shouldn’t be enabled except when trying to debug truly intractable issues.
By default, logs are not automatically cleaned out, so must be removed periodically by way of the mysql-log-rotate script. The one exception to this is binlogs, which by default expire after 30 days.