MySQL 101: Installation, Maintenance and Power on Ubuntu | GeekComparison

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.

Install MySQL

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 information_schema† performance_schema† sysand 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 host and 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

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 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 debian-sys-maint and 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 192for example 192.168.0.100†

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: /etc/mysql/mysql.conf.d/mysqld.cnf now.

We can see that MySQL will be effectively limited to: localhost just 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 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†

Using 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 1.2.3.0/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 1.2.3.0/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 systemctl assignment:

[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 bind-address institution.