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 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
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,
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
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
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
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
We can see that MySQL will effectively be limited to
localhost only by looking at the
bind-address stanza in
jim@locutus:/etc/mysql/mysql.conf.d$ grep -B3 bind-address *
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.
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 18.104.22.168/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 22.214.171.124/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
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