Peter Davies

Ramblings

MySQL housekeeping: log files, upgrades and utf8mb4

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:

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:

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:

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:

apt-get -s install mysql-server

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:

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:

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:

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

[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:

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: