Installing MySQL 8.0 on Ubuntu 18.04

2018-07-05 · mysql, ubuntu, linux, installation

I wanted MySQL 8.0 on a test box the week it went GA, mostly for the transactional data dictionary and roles. Ubuntu 18.04 only ships 5.7, so it means using Oracle's APT repository. The install is easy, but 8.0 changes a few things that caught me out: the authentication plugin, and several 5.7 options that no longer exist.

I'm installing 8.0.11 on a fresh 18.04 server (sudo, at least 1 GB of RAM, no mysql-server package installed, or a full backup if there is one), then securing it so existing client libraries can still connect.

Add Oracle's APT repository

Oracle distributes a small configuration package that sets up the repository and lets you choose which series to track.

cd /tmp
wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb

A text dialog opens. Select "MySQL Server & Cluster", then choose mysql-8.0, then "Ok". The package writes /etc/apt/sources.list.d/mysql.list and installs the signing key. If you ever want to switch series, run sudo dpkg-reconfigure mysql-apt-config.

sudo apt-get update
apt-cache policy mysql-server

The candidate version should now be 8.0.11-1ubuntu18.04 rather than the 5.7.x from the Ubuntu archive.

Install the server

sudo apt-get install -y mysql-server

Unlike the Ubuntu 5.7 package, this one prompts for a root password during installation. Set a strong one. The second prompt asks which authentication method to use as default. The choices are:

"Use Strong Password Encryption (RECOMMENDED)" sets caching_sha2_password, the new 8.0 default.
"Use Legacy Authentication Method" sets mysql_native_password, which every existing client understands.

If your stack is PHP 7.0/7.1, an older MySQL Connector/J, or anything you haven't verified against 8.0, choose Legacy. You can switch per user later. I pick Legacy on servers that serve existing applications and Strong only where I control every client. Debugging a client that can't authenticate is a bad way to spend an afternoon.

The service starts automatically. Confirm:

sudo systemctl status mysql
mysql --version
# mysql  Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)

Secure it

sudo mysql_secure_installation

Enter the root password you set. I enable the validate_password component, remove anonymous users, disallow remote root login, and remove the test database. The component enforces password strength for all future CREATE USER statements; if you want to tune it, the variables are validate_password.policy and validate_password.length (note the dot, not underscore, in 8.0).

Config

The server config lives in /etc/mysql/mysql.conf.d/mysqld.cnf. Oracle's package ships it almost empty. This is what I add on a small server:

[mysqld]
bind-address                  = 127.0.0.1
innodb_buffer_pool_size       = 1G
innodb_log_file_size          = 256M
log_error                     = /var/log/mysql/error.log
default_authentication_plugin = mysql_native_password

The last line only matters if you chose "Strong" during install and later find you need compatibility. Don't copy a 5.7 config over wholesale. I did once and the server refused to start. query_cache_size, query_cache_type, innodb_file_format, innodb_large_prefix and sql_mode = NO_AUTO_CREATE_USER were all removed in 8.0 and will stop the server from starting.

sudo systemctl restart mysql

Database and user

CREATE DATABASE appdb;
CREATE USER 'appuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Str0ng!Passw0rd';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';

Two 8.0 details here. First, utf8mb4 with utf8mb4_0900_ai_ci is now the default character set and collation, so there is no need to specify it. Second, GRANT ... IDENTIFIED BY no longer creates users implicitly; you must CREATE USER first, and FLUSH PRIVILEGES is unnecessary after account statements.

Did it work?

mysql -u appuser -p appdb -e "SELECT VERSION(), @@character_set_server, @@default_authentication_plugin;"
sudo ss -ltnp | grep mysqld

You should see 8.0.11, utf8mb4, and the socket bound to 127.0.0.1:3306. Also note that 8.0 opens a second port, 33060, for the X Protocol used by MySQL Shell; if you do not need it, add mysqlx = 0 to the config.

Reboot once and confirm systemctl is-enabled mysql returns enabled.

When it goes wrong

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded from an older client. Either upgrade the client library or alter the user:

ALTER USER 'appuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Str0ng!Passw0rd';

[ERROR] [MY-011071] [Server] unknown variable 'query_cache_size=64M' in /var/log/mysql/error.log and the service fails. A removed 5.7 option is in your config. Remove it; the query cache is gone entirely in 8.0.

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements comes from the validate_password component. Use a longer mixed-case password with a digit and symbol, or lower validate_password.policy to LOW for a development box.

E: The repository 'http://repo.mysql.com/apt/ubuntu bionic InRelease' is not signed means the GPG key did not import. Re-run sudo dpkg-reconfigure mysql-apt-config or import the key from the MySQL documentation page manually with apt-key add.

Since 8.0 delivers features in point releases, I read the release notes before every apt-get upgrade now. Back up first, as always.