MySQL 101: Installation, care, and feeding on Ubuntu

One of the tasks nearly any sysadmin frequently encounters is the care and feeding of the MySQL database server. You can build an entire career around nothing but this topic—making you a DB admin, not a humble sysadmin like yours truly—but for today, we're just going to cover the basics.

For this guide, we're going to be using Ubuntu Linux as the underlying operating system—but most of these steps and tips will be either the same, or broadly similar, across nearly any OS or distribution you might install MySQL on.

Installing MySQL

Installing MySQL on a fresh Ubuntu instance is quite simple: sudo apt update if necessary, then sudo apt install mysql-server and you're off to the races. Once the package is downloaded and installed, mysql is fired up automatically (and will be after each system reboot).

What's less obvious is how you get into the little bugger once it's running. The answer here—on Ubuntu or Debian-derived distributions, at least—lies in the file /etc/mysql/debian.cnf. This file notes the automatically created mysql superadmin account name and password; the name is debian-sys-maint, and the password is randomly generated at installation time (and, therefore, different on each system).

Once you know the password for debian-sys-maint on your local system, you can log in to your new MySQL server with mysql -u debian-sys-maint -p—the system will ask you for the password thanks to the -p flag you specified, and then you're in!

To get out of the MySQL console, you can just exit; at any time. (Note: commands entered into the console must end with a semicolon.)

Creating databases and users

You absolutely don't want your day-to-day use of MySQL—meaning applications hitting databases, not you-the-sysadmin logging in to its console—using debian-sys-maint. So let's take a quick look at the process involved in creating a new database and a new user account or two to manage it.

To see a list of databases running in your MySQL instance, use the statement show databases;. This is pretty straightforward and does just what you'd think—on a new system, you'll see information_schema, performance_schema, sys, and mysql. These databases are the "guts" of your MySQL server itself and for the most part shouldn't be mucked around with directly as though they were normal data.

The users who can log in to your MySQL databases can be found in the mysql.user table, which can be queried with SELECT * from mysql.user (or USE mysql ; SELECT * from user; if you prefer). Unfortunately, there are a lot of columns in the mysql.user table—too many to fit on the screen without horizontal text wrapping, resulting in an unreadable mess.

For more useful, less mangled results, try SELECTing just the columns you're actually interested in—in this case, host and user. Now your command is SELECT host,user FROM user; and your results are much nicer to read:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+---------------+------------------+
| host          | user             |
+---------------+------------------+
| localhost     | debian-sys-maint |
| localhost     | mysql.session    |
| localhost     | mysql.sys        |
| localhost     | root             |
+---------------+------------------+
4 rows in set (0.00 sec)

If you're really a MySQL newbie, you might be wondering what the host column is really about. The answer is a bit frustrating—MySQL's user accounts are individual to each host that a user might log in from... meaning you can have multiple "users" with the same username!

Making matters even more confusing, each row in the user table has its own password field—meaning that the same username can have wildly different passwords and privileges depending on what IP address they access the MySQL server from.

By default, MySQL only exposes itself to the localhost interface—meaning there's only one possible host to access it from—so this is a moot point on a default-configured MySQL instance. But if you get into commercial support (or decide to get a bit more complex in your own infrastructure), you'll encounter infrastructure with dedicated DB and application servers (eg, one server running MySQL, and another running nginx or Apache). In that case, the distinction between jim on the local system and jim on the webserver becomes very important indeed!

To add a new user account, you could directly issue a standard UPDATE query against the mysql.user table itself—but that's a "cowboy" practice and frowned upon in general use. Instead, you should use MySQL's GRANT command, eg:

mysql> grant all on *.* to 'jim'@'localhost' identified by 'very-strong-password`;

This should be relatively clear—we created a user named jim, who must log in from localhost only, and whose password is very-strong-password. We granted all privileges on all databases and tables—that's the *.*—to our new user. You might think that jim@localhost is effectively a super-admin now, but that's not quite the case—there are a few fairly unusual operations that require genuine super-user privileges, which by default only debian-sys-maint and root have.

This brings us to a potentially serious security issue—the root account does not have a password within MySQL at all. This means that anyone who can become root on the underlying system can simply type in mysql at the root prompt and get root access to the database server as well. If that's not something you're comfortable with, you'll want to set a password on the root account itself.

To change the password on a MySQL user account, we can use the ALTER USER command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'super-strong-password';
Query OK, 0 rows affected (0.00 sec)

Creating new databases is similarly easy—in short, create database dbname and presto, you've got a new database named dbname. MySQL users who have privileges on *.* will have privileges to work with the new database by default, but that's not how you'll want applications to access it.

One very common practice is to create a username that matches the database name and has privileges only on that database—this is the account you would then feed to your application (for example, a web application like WordPress).

mysql> grant all on dbname.* to 'dbname'@'localhost' identified by 'another-password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Remember—the new account you just created can only log in from the hostname you specify after the @. If you need the account to work from any hostname, you can use the MySQL wildcard character %:

mysql> grant all on dbname.* to 'dbname'@'%' identified by 'another-password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

In this case, the dbname user account will be able to log in from any network location—assuming that network location can reach MySQL at all, of course. You can also use the % wildcard in a more limited way, for example 'dbname'@'192.%'  is an account that can log in from any IP address beginning with 192, for example 192.168.0.100.

Exposing MySQL to other machines

In earlier versions of Debian and Ubuntu, the MySQL configuration file was located at /etc/mysql/my.cnf. This has changed to a conf.d-style setup as of Ubuntu 18.04 and later; the old MySQL configuration file is located at /etc/mysql/mysql.conf.d/mysqld.cnf now.

We can see that MySQL will effectively be limited to localhost only by looking at the bind-address stanza in mysqld.cnf:

jim@locutus:/etc/mysql/mysql.conf.d$ grep -B3 bind-address *
mysqld.cnf-#
mysqld.cnf-# Instead of skip-networking the default is now to listen only on
mysqld.cnf-# localhost which is more compatible and is not less secure.
mysqld.cnf:bind-address = 127.0.0.1

If we want to provide MySQL service for other machines, we'll need to change that bind-address—either by overriding in another file or by editing mysqld.cnf directly. Either way, you'll need to actually specify a broader bind-address rather than just commenting out that stanza entirely, since MySQL itself will default to binding on localhost in the absence of explicit instructions.

If you only want MySQL to listen on a particular interface, you can specify that interface's IP address—for example, bind-address = 192.168.0.10. But this has some fairly nasty implications, beginning with the fact that this will block connections from or to localhost. More commonly, you'll nerf it entirely by specifying the "fake" IP 0.0.0.0: bind-address = 0.0.0.0.

Using 0.0.0.0 as your bind-address (and restarting MySQL afterward) will expose MySQL on all available interfaces, including localhost. If that's broader exposure than you wanted, you'll need to create system firewall rules to further limit access. What you should not do is rely on the host column of the user table as an access mechanism—it's not safe to expose MySQL to the whole Internet, no matter how strong your passwords are!

If you're using ufw, the syntax looks like this: ufw allow from 1.2.3.0/24 to any port 3306.  This allows any IP address beginning with 1.2.3 to access your MySQL server. Or you might prefer ufw allow in on eth0 to any port 3306 to allow any connections coming in over eth0 (but not any coming in over eth1 or other interfaces).

If you're using iptables, the syntax is quite similar—for example, iptables -A INPUT -p tcp --dport 3306 -s 1.2.3.0/24 -j ACCEPT—but you'll also need to know how, where, and when you're saving and loading your iptables ruleset, which is unfortunately beyond our scope today.

Once you've changed your bind-address and created any necessary firewall rules to limit who can hammer on your newly exposed MySQL instance, you can restart MySQL using systemd's systemctl command:

jim@locutus:~$ sudo systemctl restart mysql

Be careful to never expose MySQL to the Internet at large—if you change your bind-address to something that might expose MySQL to the entire world, create (and test!) the system firewall rule that prevents it from being so broad before restarting MySQL and actually applying the more liberal bind-address setting.