One of the tasks that almost every system administrator often encounters is taking care of and feeding the MySQL database server. You can build a whole career around nothing but this subject – making you a DB administrator, not a humble system administrator like yours – but for today we’re just going to cover the basics.
For this guide, we are going to use Ubuntu Linux as the underlying operating system, but most of these steps and tips will be the same or broadly similar for almost any operating system or distribution you install MySQL on.
Installing MySQL on a new Ubuntu instance is quite simple:
sudo apt update if necessary, then
sudo apt install mysql-server and you go to the races. Once the package is downloaded and installed,
mysql will start automatically (and will be after every system reboot).
What’s less obvious is how you get go inside the little bugger once it spins. The answer here – at least on Ubuntu or Debian-derived distributions – is in the file
/etc/mysql/debian.cnf† This file records the automatically generated mysql superadmin account name and password; the name is
debian-sys-maintand the password is randomly generated during installation (and therefore different on each system).
Once you know the password for
debian-sys-maint on your local system you can login 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 entered, and then you’re in!
To get out of the MySQL console, you can just:
exit; at all times. (Note: Commands entered into the console must end with a semicolon.)
Create databases and users
You definitely don’t want your day-to-day use of MySQL – meaning applications hitting databases, not you-the-sysadmin logging into the console – using
debian-sys-maint† So let’s take a quick look at the process involved in creating a new database and one or two new user accounts to manage it.
Use the instruction. to see a list of databases running in your MySQL instance
show databases;† This is quite simple and does exactly what you would think – on a new system you will see
mysql† These databases are the “guts” of your MySQL server itself and for the most part should not be directly tampered with as if they were normal data.
The users who can login to your MySQL databases can be found in the
mysql.user table, which can be retrieved with
SELECT * from mysql.user (or
USE mysql ; SELECT * from user; if you want). Unfortunately there are many columns in the
mysql.user table – too much to fit on screen without horizontal text wrapping, resulting in an unreadable mess.
For more useful, less garbled results, try
SELECTonly the columns you are really interested in, in this case
user† Now your assignment is:
SELECT host,user FROM user; and your results are much more fun 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 for each host that a user can log in to… meaning you can have multiple “users” with the same username!
To make things more confusing, each row in the
user table has its own
password field—meaning 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 is only one possible host to access it – so this is a moot point on a default configured MySQL instance. But if you get commercial support (or decide to get a bit more complex in your own infrastructure), you’ll find an infrastructure with dedicated DB and application servers (for example, one server with MySQL and another with nginx or Apache). In that case, the distinction between
jim on the local system and
jim on the web server becomes very important indeed!
To add a new user account, you can directly issue a standard
UPDATE ask the
mysql.user table itself, but that is a ‘cowboy’ practice and is generally frowned upon. You should use MySQL’s instead
GRANT command, eg:
mysql> grant all on *.* to 'jim'@'localhost' identified by 'very-strong-password`;
This should be relatively obvious: we created a user called
jimwho needs to log in?
localhost alone, 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
[email protected] is now basically a super admin, but that’s not quite the case – there are some pretty unusual operations that require real super user privileges, which by default only
root to have.
This brings us to a potentially serious security issue: the
root account has no password at all within MySQL. This means that anyone who can become
root on the underlying system you can just type
mysql at the root prompt and also get root access to the database server. If you are not comfortable with that, you can set a password on the
root self account.
To change the password of a MySQL user account, we can use the
ALTER USER assignment:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'super-strong-password';
Query OK, 0 rows affected (0.00 sec)
Creating new databases is just as easy – in short,
create database dbname and presto, you have a new database called
dbname† MySQL users who have privileges on
*.* will have privileges by default to work with the new database, but that’s not how you want applications to access it.
A common practice is to create a username that matches the database name and has privileges only for that database – this is the account you would then add to your application (e.g. 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 login from the hostname you specify after the
@† If you need the account to work from each hostname, you can use the MySQL wildcard
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 can login from each network location – assuming the network location can reach MySQL all the way, of course. You can also use the
% wildcard in a more limited way, for example
'dbname'@'192.%' is an account that can login from any IP address that starts with
Exposing MySQL to other machines
In previous versions of Debian and Ubuntu, the MySQL configuration file was located at:
/etc/mysql/my.cnf† This has turned into a
conf.dstyle setup from Ubuntu 18.04 and later; the old MySQL configuration file is located at:
We can see that MySQL will be effectively limited to:
localhost just by looking at the
bind-address stanza in
[email protected]:/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 need to change that
bind-address—by overwriting it in another file or by editing it
mysqld.cnf straight away. Anyway, you really should specify a wider
bind-address instead of just fully commenting that stanza, since MySQL itself will bind to . by default
localhost in the absence of explicit instructions.
If you want MySQL to listen only on a particular interface, you can specify the IP address of that interface, for example:
bind-address = 192.168.0.10† But this has rather nasty implications, starting with the fact that this will block connections from or to
localhost† Usually you destroy it completely by specifying the “fake” IP 0.0.0.0:
bind-address = 0.0.0.0†
0.0.0.0 if your
bind-address (then restarting MySQL) will expose MySQL to all available interfaces, including localhost. If that’s broader exposure than you wanted, you’ll need to create system firewall rules to further restrict access. What should you do? not to do is to rely on the
host column of the
user table as an access mechanism – it’s not safe to expose MySQL to the entire internet, no matter how strong your passwords are!
If you use
ufwthe syntax looks like this:
ufw allow from 184.108.40.206/24 to any port 3306† This allows any IP address starting with 1.2.3 to access your MySQL server. Or maybe rather
ufw allow in on eth0 to any port 3306 to allow any incoming connections
eth0 (but not every one that enters
eth1 or other interfaces).
If you use
iptablesthe syntax is quite similar, for example
iptables -A INPUT -p tcp --dport 3306 -s 220.127.116.11/24 -j ACCEPT—but you also need to know how, where and when you get your . saves and loads
iptables ruleset, which is unfortunately beyond our reach today.
Once you have your . has changed
bind-address and created the necessary firewall rules to restrict who can hammer on your newly exposed MySQL instance, you can restart MySQL with systemds
[email protected]:~$ sudo systemctl restart mysql
Be careful to never expose MySQL to the Internet in general – if you
bind-address to something that could expose MySQL to the whole world, create (and test!) the system firewall rule that prevents it from being that wide before Restart MySQL and actually apply the more liberal