MySQL 101: Installation, care, and feeding on Ubuntu

This site is reader-supported. When you click through links on our site, we may be compensated.

Warning: Learning the care and feeding of MySQL instances does not grant knowledge of or safe interaction with actual marine mammals.
Enlarge / Warning: Learning the care and feeding of MySQL instances does not grant knowledge of or safe interaction with actual marine mammals.
Oracle

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

  <div class="gallery shortcode-gallery gallery-wide">
  <ul><li data-thumb="https://cdn.arstechnica.net/wp-content/uploads/2021/06/install-mysql-server-150x150.png"><img src="http://scoreit.online/wp-content/uploads/2021/06/install-mysql-server.png"><span cdn.arstechnica.net wp-content uploads install-mysql-server.png https: data-sub-html="#caption-1772448">
        </span><figure style="height:488px;"><div class="image" style="background-image:url('http://scoreit.online/wp-content/uploads/2021/06/install-mysql-server.png'); background-color:#000"></div>
                          <figcaption id="caption-1772448"><span class="icon caption-arrow icon-drop-indicator"></span>
                                  <div class="caption">
                  If you're even vaguely familiar with Ubuntu or Debian, the installation process shouldn't be surprising: <strong>apt install mysql-server</strong> and you're off to the races.                    </div>
                                                    <div class="credit">
                  <span class="icon icon-camera"></span>
                                          Jim Salter                                          </div>
                              </figcaption></figure></li>
              <li data-thumb="https://cdn.arstechnica.net/wp-content/uploads/2021/06/logging-into-mysql-150x150.png"><img src="http://scoreit.online/wp-content/uploads/2021/06/logging-into-mysql.png"><span cdn.arstechnica.net wp-content uploads logging-into-mysql.png https: data-sub-html="#caption-1772450">
        </span><figure style="height:488px;"><div class="image" style="background-image:url('http://scoreit.online/wp-content/uploads/2021/06/logging-into-mysql.png'); background-color:#000"></div>
                          <figcaption id="caption-1772450"><span class="icon caption-arrow icon-drop-indicator"></span>
                                  <div class="caption">
                  Oh no, how do I get in? On your first login (at least), you'll need to use the <strong>debian-sys-maint</strong> MySQL account, with a randomly generated password stored in /etc/mysql/debian.cnf.                    </div>
                                                    <div class="credit">
                  <span class="icon icon-camera"></span>
                                          Jim Salter                                          </div>
                              </figcaption></figure></li>
              <li data-thumb="https://cdn.arstechnica.net/wp-content/uploads/2021/06/logged-into-mysql-150x150.png"><img src="http://scoreit.online/wp-content/uploads/2021/06/logged-into-mysql.png"><span cdn.arstechnica.net wp-content uploads logged-into-mysql.png https: data-sub-html="#caption-1772449">
        </span><figure style="height:488px;"><div class="image" style="background-image:url('http://scoreit.online/wp-content/uploads/2021/06/logged-into-mysql.png'); background-color:#000"></div>
                          <figcaption id="caption-1772449"><span class="icon caption-arrow icon-drop-indicator"></span>
                                  <div class="caption">
                  Using the password found in debian.cnf, we can log in as the super-privileged debian-sys-maint mysql user.                    </div>
                                                    <div class="credit">
                  <span class="icon icon-camera"></span>
                                          Jim Salter                                          </div>
                              </figcaption></figure></li>
          </ul></div>

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

  <div class="gallery shortcode-gallery gallery-wide">
  <ul><li data-thumb="https://cdn.arstechnica.net/wp-content/uploads/2021/06/select-from-users-150x150.png"><img src="http://scoreit.online/wp-content/uploads/2021/06/select-from-users.png"><span cdn.arstechnica.net wp-content uploads select-from-users-980x550.png https: select-from-users-1440x808.png data-sub-html="#caption-1772457">
        </span><figure style="height:550px;"><div class="image" style="background-image:url('https://cdn.arstechnica.net/wp-content/uploads/2021/06/select-from-users-980x550.png'); background-color:#000"></div>
                          <figcaption id="caption-1772457"><span class="icon caption-arrow icon-drop-indicator"></span>
                                  <div class="caption">
                  You can run SELECT queries directly within the MySQL console, like this one, where we look at the contents of the USER table. There are too many columns to fit without wrapping, resulting in an unreadable mess—so we refine the query to specify just the columns we're really interested in.                    </div>
                                                    <div class="credit">
                  <span class="icon icon-camera"></span>
                                          Jim Salter                                          </div>
                              </figcaption></figure></li>
              <li data-thumb="https://cdn.arstechnica.net/wp-content/uploads/2021/06/adding-db-and-users-150x150.png"><img src="http://scoreit.online/wp-content/uploads/2021/06/adding-db-and-users.png"><span cdn.arstechnica.net wp-content uploads adding-db-and-users-980x550.png https: adding-db-and-users-1440x808.png data-sub-html="#caption-1772455">
        </span><figure style="height:550px;"><div class="image" style="background-image:url('https://cdn.arstechnica.net/wp-content/uploads/2021/06/adding-db-and-users-980x550.png'); background-color:#000"></div>
                          <figcaption id="caption-1772455"><span class="icon caption-arrow icon-drop-indicator"></span>
                                  <div class="caption">
                  Creating databases and users is fairly straightforward—but watch out for the fact that you can have multiple "users" with the same name, differentiated by what machine they logged in from!                    </div>
                                                    <div class="credit">
                  <span class="icon icon-camera"></span>
                                          Jim Salter                                          </div>
                              </figcaption></figure></li>
          </ul></div>

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 [email protected] 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:

[email protected]:/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:

[email protected]:~$ 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.

Simple MySQL backup

Backing up MySQL is unfortunately not as simple as merely making a copy of the files in which it stores its data. Those are kept in /var/lib/mysql and have filenames like ibdata1 and ib_logfile1, but they’re not for mortals to touch directly!

One of the problems with attempting to back MySQL up just by copying its data files is that those files may change while you’re attempting to back them up. If you’re in the process of copying ibdata1 while an INSERT query runs, for example, you’re likely to end up with an inconsistent ibdata1on the other end—one with part, but not all, of the changes that INSERT query was making. This inconsistent file might not start up at all, or it might have bad data in it, or it might even crash MySQL!

Another problem is that the data file format between MySQL instances on different operating systems and versions can be slightly different. To avoid all this, we do not touch the /var/lib/mysql directory itself—instead, we use the mysqldump command, installed along with MySQL itself.

mysqldump uses the same user authentication and access scheme as MySQL itself—so you might, for instance, back up using the built-in debian-sys-maint user, which has privileges on all databases and tables:

[email protected]:~$ mysqldump -u debian-sys-maint -p password -h localhost --all-databases > /home/jim/mysqlbackup.mysql

The mysqldump command just barfs out all of the SQL queries necessary to recreate all of your databases and tables, then INSERT all of your actual data in them afterward. It barfs them out straight to the console, so you’ll need to redirect its output to a file for a normal backup—in the above example, to /home/jim/mysqlbackup.mysql. If you want to get a little fancier, you can both compress your backup and get a progress bar while creating it:

[email protected]:/etc/mysql$ mysqldump -u debian-sys-maint -ppassword -h localhost --all-databases | pv | gzip > /home/jim/mysqlbackup.mysql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
855KiB 0:00:00 [7.72MiB/s] [<=>                                                                      ]

That’s a lot nicer—it takes no time at all to back up this essentially empty and unloaded brand-new MySQL install, but a big instance with multiple gigabytes of data and significant usage may take several minutes to finish! The compression is also usually an enormous win, since MySQL data tends to be uncompressed plain text in the majority of cases.

You’ll notice that mysqldump complains that passwords on the command line can be insecure—that’s because they may show up in bash_history or similar command logs. To avoid the problem, you can instead specify -p without arguments, which asks you for the password interactively. Or you can save the password in a file with limited access, then use backticks to read the password from that file:

[email protected]:~# echo mypassword > root/mypassword
[email protected]:~# mysqldump -u debian-sys-maint -pcat /root/mypassword -h localhost --all-databases | pv | gzip > /home/jim/mysqlbackup.mysql.gz

Before we move on, did you notice that there’s no space between -p and password above? That’s not a bug. If you put a space in between the -p and the password, mysqldump assumes that you want the password queried interactively and that password is a completely separate argument (which it won’t understand and will complain about). You need to specify it as shown, -ppassword, if you want the password “password” to be applied directly in the command.

Finally, while the --all-databases argument looks convenient, it’s a bit of a trap—you will absolutely get a valid backup of all databases, but it will unfortunately include quite a few tables in system databases that cannot be messed with later using simple INSERT and UPDATE queries, which is how you restore a MySQL dump. So instead, you’ll likely want to back up MySQL databases individually by specifying the individual database name (or names) at the end of the command, instead of just --all-databases.

Restoring from a MySQL dump is simple: you just pipe its contents into the mysql command itself, again using an account with appropriate permissions. Here’s a full example of backup and restore of one database:

[email protected]:~# mysqldump -u debian-sys-maint -p`cat /root/mypassword` dbname | gzip > /home/jim/dbname.mysql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[email protected]:~# zcat /home/jim/dbname.mysql.gz | mysql -u debian-sys-maint -p`cat /root/mypassword`
mysql: [Warning] Using a password on the command line interface can be insecure.

It’s that simple—you’ve now restored your database dbname in the condition it was when you dumped it. But remember, again—larger databases may take significant time for both dump and restore, so consider adding a pv in the middle to keep you from going nuts while you wait for the operation to finish!

Once you’ve successfully mysqldumped your database(s) to standard files, you can move those files around any way you’d like, including copying, rsyncing, or however else you’d like to get them from A to B. (You may also simply run mysqldump directly from a remote machine against the MySQL instance if you’ve exposed MySQL to remote machines and created an appropriate user account that is allowed to log in from the remote host in question.)

Conclusion

This is the barest scratching of the surface of installing and managing a MySQL server—but it’s enough to get most admins through all the things they really need to do in order to enable web (or other) applications relying on MySQL as a back end.

With an understanding of how to install MySQL, configure it for remote access (or not!), and create both databases and user accounts, you can get everything ready for an application like WordPress to store its data without any fuss or muss. Just don’t neglect that backup step—all data that matters needs to be backed up regularly, and SQL servers are certainly no exception.

In particular, please pay attention to how to properly back up and restore your MySQL server, as outlined above. Understanding these steps will both keep your data safe and give you the ability to easily migrate databases from one MySQL instance to another.

                                            </div>

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.