MySQL 101: Installation, care, and feeding on Ubuntu

Warning: Learning the care and feeding of MySQL instances does not grant knowledge of or safe interaction with actual marine mammals.
Enlarge / Warning: Studying the care and feeding of MySQL cases doesn’t grant information of or protected interplay with precise marine mammals.

Oracle

One of many duties almost any sysadmin continuously encounters is the care and feeding of the MySQL database server. You’ll be able to construct a whole profession round nothing however this subject—making you a DB admin, not a humble sysadmin like yours actually—however for in the present day, we’re simply going to cowl the fundamentals.

For this information, we’ll be utilizing Ubuntu Linux because the underlying working system—however most of those steps and ideas will likely be both the identical, or broadly comparable, throughout almost any OS or distribution you would possibly set up MySQL on.

Putting in MySQL

Putting in MySQL on a recent Ubuntu occasion is sort of easy: sudo apt replace if crucial, then sudo apt set up mysql-server and also you’re off to the races. As soon as the bundle is downloaded and put in, mysql is fired up robotically (and will likely be after every system reboot).

What’s much less apparent is the way you get into the little bugger as soon as it is operating. The reply right here—on Ubuntu or Debian-derived distributions, at the least—lies within the file /and so on/mysql/debian.cnf. This file notes the robotically created mysql superadmin account identify and password; the identify is debian-sys-maint, and the password is randomly generated at set up time (and, subsequently, completely different on every system).

As soon as you understand the password for debian-sys-maint in your native system, you’ll be able to log in to your new MySQL server with mysql -u debian-sys-maint -p—the system will ask you for the password because of the -p flag you specified, and you then’re in!

To get out of the MySQL console, you’ll be able to simply exit; at any time. (Be aware: instructions entered into the console should finish with a semicolon.)

Creating databases and customers

You completely don’t need your day-to-day use of MySQL—that means functions hitting databases, not you-the-sysadmin logging in to its console—utilizing debian-sys-maint. So let’s take a fast take a look at the method concerned in creating a brand new database and a brand new person account or two to handle it.

To see an inventory of databases operating in your MySQL occasion, use the assertion present databases;. That is fairly simple and does simply what you’d assume—on a brand new system, you will see information_schema, performance_schema, sys, and mysql. These databases are the “guts” of your MySQL server itself and for essentially the most half should not be mucked round with instantly as if they had been regular knowledge.

The customers who can log in to your MySQL databases may be discovered within the mysql.person desk, which may be queried with SELECT * from mysql.person (or USE mysql ; SELECT * from person; for those who favor). Sadly, there are quite a lot of columns within the mysql.person desk—too many to suit on the display with out horizontal textual content wrapping, leading to an unreadable mess.

For extra helpful, much less mangled outcomes, attempt SELECTing simply the columns you are really thinking about—on this case, host and person. Now your command is SELECT host,person FROM person; and your outcomes are a lot nicer to learn:

mysql> use mysql;
Studying desk info for completion of desk and column names
You'll be able to flip off this function to get a faster startup with -A

Database modified
mysql> choose host,person from person;
+---------------+------------------+
| host          | person             |
+---------------+------------------+
| localhost     | debian-sys-maint |
| localhost     | mysql.session    |
| localhost     | mysql.sys        |
| localhost     | root             |
+---------------+------------------+
four rows in set (zero.00 sec)

In the event you’re actually a MySQL beginner, you could be questioning what the host column is absolutely about. The reply is a bit irritating—MySQL’s person accounts are particular person to every host person would possibly log in from… that means you’ll be able to have a number of “customers” with the identical username!

Making issues much more complicated, every row within the person desk has its personal password area—that means that the identical personidentify can have wildly completely different passwords and privileges relying on what IP tackle they entry the MySQL server from.

By default, MySQL solely exposes itself to the localhost interface—that means there’s just one doable host to entry it from—so this can be a moot level on a default-configured MySQL occasion. However for those who get into business assist (or resolve to get a bit extra complicated in your individual infrastructure), you will encounter infrastructure with devoted DB and software servers (eg, one server operating MySQL, and one other operating nginx or Apache). In that case, the excellence between jim on the native system and jim on the webserver turns into essential certainly!

So as to add a brand new person account, you can instantly problem a typical UPDATE question in opposition to the mysql.person desk itself—however that is a “cowboy” apply and frowned upon typically use. As an alternative, you need to use MySQL’s GRANT command, eg:

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

This must be comparatively clear—we created a person named jim, who should log in from localhost solely, and whose password is very-strong-password. We granted all privileges on all databases and tables—that is the *.*—to our new person. You would possibly assume that [email protected] is successfully a super-admin now, however that is not fairly the case—there are a number of pretty uncommon operations that require real super-user privileges, which by default solely debian-sys-maint and root have.

This brings us to a doubtlessly severe safety problem—the root account doesn’t have a password inside MySQL in any respect. Which means anybody who can turn out to be root on the underlying system can merely sort in mysql on the root immediate and get root entry to the database server as effectively. If that is not one thing you are snug with, you will need to set a password on the root account itself.

To vary the password on a MySQL person account, we are able to use the ALTER USER command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'super-strong-password';
Question OK, zero rows affected (zero.00 sec)

Creating new databases is equally straightforward—briefly, create database dbname and presto, you have obtained a brand new database named dbname. MySQL customers who’ve privileges on *.* could have privileges to work with the brand new database by default, however that is not how you will need functions to entry it.

One quite common apply is to create a username that matches the database identify and has privileges solely on that database—that is the account you’ll then feed to your software (for instance, an internet software like WordPress).

mysql> grant all on dbname.* to 'dbname'@'localhost' recognized by 'another-password';
Question OK, zero rows affected, 1 warning (zero.00 sec)

Bear in mind—the brand new account you simply created can solely log in from the hostname you specify after the @. In the event you want the account to work from any hostname, you should utilize the MySQL wildcard character %:

mysql> grant all on dbname.* to 'dbname'@'%' recognized by 'another-password';
Question OK, zero rows affected, 1 warning (zero.00 sec)

On this case, the dbname person account will be capable of log in from any community location—assuming that community location can attain MySQL in any respect, after all. You may also use the % wildcard in a extra restricted approach, for instance 'dbname'@'192.%'  is an account that may log in from any IP tackle starting with 192, for instance 192.168.zero.100.

Exposing MySQL to different machines

In earlier variations of Debian and Ubuntu, the MySQL configuration file was situated at /and so on/mysql/my.cnf. This has modified to a conf.d-style setup as of Ubuntu 18.04 and later; the outdated MySQL configuration file is situated at /and so on/mysql/mysql.conf.d/mysqld.cnf now.

We are able to see that MySQL will successfully be restricted to localhost solely by trying on the bind-address stanza in mysqld.cnf:

[email protected]:/and so on/mysql/mysql.conf.d$ grep -B3 bind-address *
mysqld.cnf-#
mysqld.cnf-# As an alternative of skip-networking the default is now to pay attention solely on
mysqld.cnf-# localhost which is extra suitable and isn't much less safe.
mysqld.cnf:bind-address = 127.zero.zero.1

If we need to present MySQL service for different machines, we’ll want to alter that bind-address—both by overriding in one other file or by enhancing mysqld.cnf instantly. Both approach, you will want to really specify a broader bind-address somewhat than simply commenting out that stanza solely, since MySQL itself will default to binding on localhost within the absence of specific directions.

In the event you solely need MySQL to pay attention on a selected interface, you’ll be able to specify that interface’s IP tackle—for instance, bind-address = 192.168.zero.10. However this has some pretty nasty implications, starting with the truth that this may block connections from or to localhost. Extra generally, you will nerf it solely by specifying the “faux” IP zero.zero.zero.zero: bind-address = zero.zero.zero.zero.

Utilizing zero.zero.zero.zero as your bind-address (and restarting MySQL afterward) will expose MySQL on all out there interfaces, together with localhost. If that is broader publicity than you wished, you will must create system firewall guidelines to additional restrict entry. What you need to not do is depend on the host column of the person desk as an entry mechanism—it isn’t protected to reveal MySQL to the entire Web, regardless of how sturdy your passwords are!

In the event you’re utilizing ufw, the syntax appears like this: ufw enable from 1.2.three.zero/24 to any port 3306.  This enables any IP tackle starting with 1.2.three to entry your MySQL server. Otherwise you would possibly favor ufw enable in on eth0 to any port 3306 to permit any connections coming in over eth0 (however not any coming in over eth1 or different interfaces).

In the event you’re utilizing iptables, the syntax is sort of comparable—for instance, iptables -A INPUT -p tcp --dport 3306 -s 1.2.three.zero/24 -j ACCEPT—however you will additionally must understand how, the place, and while you’re saving and loading your iptables ruleset, which is sadly past our scope in the present day.

As soon as you have modified your bind-address and created any crucial firewall guidelines to restrict who can hammer in your newly uncovered MySQL occasion, you’ll be able to restart MySQL utilizing systemd’s systemctl command:

[email protected]:~$ sudo systemctl restart mysql

Watch out to by no means expose MySQL to the Web at massive—for those who change your bind-address to one thing which may expose MySQL to your entire world, create (and check!) the system firewall rule that forestalls it from being so broad earlier than restarting MySQL and really making use of the extra liberal bind-address setting.