---
title: "MySQL housekeeping: log files, upgrades and utf8mb4"
date: "2025-01-03"
path: "/224-mysql-housekeeping-logs-upgrades-and-utf8mb4.html"
section: "/ramblings"
description: "Notes on resizing InnoDB redo logs, upgrading MySQL safely, creating least-privilege users and dealing with utf8 versus utf8mb4 on MySQL 8."
---

A collection of MySQL and MariaDB jobs that come round every year or two, each one just far enough apart that I have to look it up again. These are the notes I keep going back to, tidied up.

## Resizing the InnoDB redo log

The redo log (`ib_logfile0`, `ib_logfile1` in the data directory) is a fixed-size ring that InnoDB writes every change to before it reaches the tablespace. If it is too small for the write load, the server spends its time flushing to make room, and big imports crawl. The setting is `innodb_log_file_size`, in the `[mysqld]` section; on Debian's MariaDB packages that is `/etc/mysql/mariadb.conf.d/50-server.cnf`.

On old servers (MySQL before 5.6.8, and older MariaDB) changing the size was not enough on its own. The server refused to start because the existing files did not match the new size. The procedure was:

```bash
systemctl stop mysql
cd /var/lib/mysql
mv ib_logfile0 ib_logfile0_bak
mv ib_logfile1 ib_logfile1_bak
systemctl start mysql
```

InnoDB creates new log files at the new size on start-up. If it will not start, stop it, move the `_bak` files back and work out why before trying again.

This is only safe after a clean shutdown. The redo log holds changes that may not be in the tablespace yet; delete or move it after a crash or a `kill -9` and those changes are gone. If in doubt, set a slow shutdown first so everything is flushed:

```sql
SET GLOBAL innodb_fast_shutdown = 0;
```

Then stop the server, check the error log ends with a normal shutdown message, and only then touch the files. Keep the old files until the server has been running happily for a while.

Newer versions make this mostly unnecessary:

- MySQL 5.6.8 onwards and MariaDB 10.0 onwards resize the logs themselves on restart if the setting has changed. Just change the config and restart.
- MariaDB 10.5 uses a single `ib_logfile0`, and from 10.11 you can change `innodb_log_file_size` with `SET GLOBAL` without a restart.
- MySQL 8.0.30 replaced the setting with `innodb_redo_log_capacity`, which is dynamic, and moved the files into `#innodb_redo/`. Leave them alone.

## Upgrading

Years ago, getting a newer MySQL on Debian meant adding a third-party repository such as dotdeb. Those repositories are gone. Today the choice is the distribution's own packages or the vendor's APT repository (MySQL's or MariaDB's). The habits from those days still apply:

- Back up first, with a dump you have test-restored, not just a copy of `/var/lib/mysql`.
- Simulate before you install, so you can see what will be removed:

```bash
apt-get -s install mysql-server
```

- Read that output. A server upgrade often pulls in new client libraries, and anything compiled against the old ones (PHP extensions in particular) may need rebuilding or upgrading too.
- Stop the server, install, start it, and check the version and error log.
- On MySQL before 8.0.16 and on MariaDB, run `mysql_upgrade` (`mariadb-upgrade` on newer MariaDB) afterwards to update the system tables. MySQL 8.0.16 and later do it on start-up.
- Go one major version at a time. 5.7 to 8.0 is supported; 5.6 straight to 8.0 is not.

## Least-privilege users

Applications should not connect as root. Create a user per application with only the rights it needs on only its own database:

```sql
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'CHANGE_ME';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';
SHOW GRANTS FOR 'app_user'@'localhost';
```

No `FLUSH PRIVILEGES` is needed after `CREATE USER` and `GRANT`; it is only needed if you edit the `mysql` tables directly. Give schema-change rights (`CREATE`, `ALTER`, `INDEX`, `DROP`) to a separate deploy user if the application runs migrations.

To change a password, use `ALTER USER`:

```sql
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new-password';
```

The older `SET PASSWORD ... = PASSWORD('...')` form, and updating `mysql.user` by hand, do not work on MySQL 8: the `PASSWORD()` function was removed.

## utf8, utf8mb4 and MySQL 8

MySQL's `utf8` is not real UTF-8. It stores at most three bytes per character (it is now called `utf8mb3`), so emoji and some CJK characters either fail to save or are silently cut off. `utf8mb4` is the full thing.

MySQL 8.0 changed the server default to `utf8mb4` with the collation `utf8mb4_0900_ai_ci`. I hit this moving an old PHP application, whose tables were all `utf8` with `utf8_general_ci`, onto MySQL 8. It shows up in two ways:

- New tables, and new connections that did not set a character set, used the 8.0 default. Queries joining old and new columns failed with "Illegal mix of collations".
- Old PHP client libraries do not know collation number 255 (`utf8mb4_0900_ai_ci`) and refuse to connect, complaining that the server sent a character set unknown to the client.

The quick fix was to set the server back to what the application expected:

```text
[mysqld]
character_set_server = utf8
collation_server = utf8_general_ci
```

On a managed service such as Amazon RDS these go in the parameter group instead of a config file. You can see what is available with `SHOW COLLATION;`; the list on 8.0 ends with the `utf8mb4_0900_*` collations.

The proper fix is to move the application to `utf8mb4`. Do the database default and each table:

```sql
ALTER DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

then set the connection character set in the application (`charset=utf8mb4` in a PDO DSN). Points to watch:

- `CONVERT TO` rewrites the whole table. Test the time on a copy, and do big tables in a quiet window.
- On 5.6 and older, an index on a `VARCHAR(255)` fails in `utf8mb4` because 255 × 4 bytes is over the 767-byte limit. On 5.7 and 8.0 with the default `DYNAMIC` row format the limit is 3072 bytes, so this goes away.
- Pick one collation and use it everywhere. Mixed collations are what cause the "Illegal mix" errors in the first place. `utf8mb4_unicode_ci` works on both 5.7 and 8.0; use `utf8mb4_0900_ai_ci` if everything is on 8.0 and the clients are new enough.