How to get started with MySQL

Long a staple of open source computing, MySQL serves as the database back end to a massive array of applications, from network monitoring frameworks to blogging applications like WordPress to social networks like Facebook. To those uninitiated in how databases work, setting up MySQL for the first time can be daunting. Nevertheless, with a few pointers and concepts, you can quickly get a new MySQL instance up and running, and ready to deploy your application.

For the purposes of this guide, I will assume that the reader has little or no experience with MySQL on Linux. We’ll concentrate on getting MySQL installed and configured to the point where an application can be connected to the database and begin operation. Advanced elements of MySQL, such as database programming, MySQL performance tips, and the SQL language, are outside the scope of this discussion.

Guide to MySQL installation

First things first: we need to get MySQL installed on your system. The exact installation process will vary widely across operating systems, so we’ll cover the big three use cases: Microsoft Windows, macOS, and Linux.

Note that you may need additional packages aside from the main MySQL code in order to make your application function. For instance, if you were going to use a PHP-based application with MySQL, you would need to install the PHP MySQL packages that allow PHP to talk to MySQL servers.

MySQL components

When you set up MySQL, you may get to choose which components of the MySQL stack to install. Some are essential; others optional.

• MySQL Server: This is the actual MySQL database server, the one indispensable part of MySQL.
• MySQL Shell: The interactive command-line interface to MySQL. MySQL admins will want this installed on the same system as the server. It gives you a way to interact with MySQL directly and on any client machine.
• MySQL Workbench: The rich GUI interface used to connect to MySQL, manage databases, create and analyze queries, and do many more things. It’s often easier to work with MySQL with the Workbench than via only the command line, so it’s recommended to install this on any client that’ll be managing MySQL. (It’s not required to install it on the same system as the server, although it can be useful.)
• MySQL Router: A software component used to set up high-availability scenarios for MySQL installations. The router isn’t needed for basic MySQL setups, and can always be installed later if needed.

Installing MySQL on Microsoft Windows

Setting up MySQL on Microsoft Windows works like installing most any other application. Oracle offers an installation executable, which comes in two editions: a small (2.1MB) network-based installer and a full-sized (303MB) installer. The net installer downloads only the pieces you need for your particular setup, while the full installer has everything.

This legacy MySQL installer offers four options: server only, client only, full (server plus client plus other features), and custom. This last option lets you mix and match the pieces you want. Note that the legacy installer only provides versions of MySQL up to 8.0.39.

The MySQL Installer for Windows is used to install versions of MySQL higher than 8.0.39. This installer features a slightly more streamlined setup process but also uses a GUI. It’s recommended to use its default settings to start with, as they reflect common use cases.

One option you may want to consider changing in the GUI is MySQL’s server configuration type, which controls the memory profile for MySQL:

• Development: The default setting. Relatively small memory usage, so that MySQL can coexist with other apps and your development tools.
• Server: Moderate memory usage, as other apps will still run on the server but chiefly for the sake of using MySQL.
• Dedicated: Maximum memory usage, as MySQL may be the only app on the server.
• Manual: No changes to the defaults; you can edit the memory usage and other options later.

Note that you can adjust these settings at any time.

Winget, Microsoft’s native package manager for Windows, has MySQL available under the alias Oracle.MySQL. If you type the command winget install Oracle.MySQL, it places a MySQL installer—not the application itself—in the Program Files (x86)\MySQL directory. From there, run .\MySQLInstaller.exe Community to start the installer with the community edition of MySQL. If you only run .\MySQLInstaller.exe, the installer assumes you are trying to install the commercial edition of MySQL, and will not work unless you have a commercial license.

Another option is to install MySQL by way of a pre-composed application stack. The XAMPP project provides a distribution of the Apache web server with MySQL as one of the components, which is an easy way to set up MySQL. It’s available for macOS and Linux as well as Windows.

Installing MySQL on macOS

The download link for MySQL provides macOS 14 binaries for both ARM and x86 Macs, as a DMG archive or a .tar file. Most of the time, you’ll want the DMG, as that makes the installation process pretty much the same as any other Mac application. The macOS installer has a GUI nearly identical to the Microsoft Windows installer. Many of the common options are preselected there too, and don’t need to be altered.

Another common install source is the Homebrew package manager for macOS. The command brew install mysql@8.0 should install both MySQL 8 and a few key dependencies for macOS.

MacOS installs also offer a preference pane for MySQL. Through the pane, you can stop or restart the service and also make changes to common configuration options like the data directory.

Installing MySQL on Linux

Normally, a package like MySQL is installed from the package manager for whatever Linux distribution you’re using.

• In Ubuntu and Debian, use apt to install the mysql-server package.
• In Red Hat Enterprise Linux and Fedora, use dnf to install community-mysql-server.

MySQL’s download link also provides Linux binaries in various formats:

• Generic Linux binaries as tarballs.
• Ubuntu and Debian Linux .deb bundles.
• SUSE Linux Enterprise Server, Fedora, and Red Hat Enterprise Linux RPM bundles.

Configuring the MySQL Server

Once MySQL has been installed, the next step is to configure the server.

If you’ve installed MySQL on Microsoft Windows or macOS, the GUI installer steps you through the most important configuration options, like setting the root password. On Linux, you’ll need to configure most of these manually.

The process for configuring the RHEL/Fedora family of Linux is a little different from the Ubuntu/Debian family. In all cases, you need to do the following:

• Set the MySQL service to start automatically: On Ubuntu, this should be automatic; on RHEL/Fedora, you’d use sudo systemctl enable mysqld and sudo systemctl start mysqld.
• Set the bind address for MySQL: This may be configured automatically on some distributions. The bind address is set in /etc/mysql/mysql.conf.d/mysqld.cnf under bind-address; if it isn’t the IP address for the current machine, use ip address show to find out what that is and set it. You’ll need to restart MySQL for this to take effect.
• Set the root password: The command mysqladmin password, when run as root, will prompt you to input a new password for the root account. For security’s sake, the new password will not be echoed to the console as you type it.

One useful way to configure MySQL across all supported operating systems is the mysql_secure_installation command. Run it and it will set many common options for MySQL to sane, secure defaults. Running this command is recommended because it consolidates many separate configuration steps into a single, scriptable action.

Managing MySQL with phpMyAdmin

Once you have MySQL up and running, it’s often easier to use a web-based management tool to work with it than only the command line. The phpMyAdmin tool provides a web GUI for performing nearly all MySQL administrative tasks—managing databases, tables, users, and internal MySQL settings.

phpMyAdmin is included in the package managers with most Linux distributions, although the setup process varies slightly across distributions. With Microsoft Windows and macOS, the XAMPP Project provides MySQL along with phpMyAdmin as a pack-in. The XAMPP control panel provides one-click access to phpMyAdmin.

The phpMyAdmin tool also partly does away with the need to open a console connection to MySQL—and to some degree, the MySQL Workbench application. The phpMyAdmin interface provides a console of its own with autocomplete functionality. You can also use the GUI to write queries, run them and save the results, re-use queries, and get analytics about queries via the EXPLAIN keyword.