Upgrade DB from latin1 to utf8mb4 (mysql 5.7)

November 10, 2023

  • Stop app container (to avoid any extra writes to DB)
docker-compose stop app
  • backup DATABASE
docker-compose exec mysql mysqldump db_name | gzip > mysqldump-`date --iso`.sql.gz
  • Copy, unzip to local machine (optional)
gunzip mysqldump-xxxxx.sql.gz
  • Change utf8 and latin1 to utf8mb4
sed -i s'/utf8/utf8mb4/g' mysqldump-2023-11-06.sql
sed -i s'/latin1/utf8mb4/g' mysqldump-2023-11-06.sql

  • Copy db back to production if copied to local machine (or edit directly on production server, so there will be no need to copy back and forth). sed can be a bit slower, but overall faster than passing SQL file from different servers.

  • Change docker-compose.yaml under your mysql service to use new command for mysql:

command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
  • Recreate mysql container with new command
docker-compose up -d mysql
  • drop existing database and create new
docker-compose exec -T mysql sh -c 'echo "DROP DATABASE db_name; CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" | mysql -uuser'
  • import data from dump
docker-compose exec -T mysql sh -c 'MYSQL_PWD=password mysql -uuser -hroot_mysql_1 -D db_name' < mysqldump-2023-11-06.sql
  • in case of too long keys errors, change all relevant varchar lengths to 191 (this is only needed for mysql version lower than 5.7)
sed -i s'/varchar(250)/varchar(191)/g' mysqldump-2023-11-06.sql
  • start app
docker-compose up -d app