MySQL перекодировка базы

Mysql поддерживает много кодировок и это нередко является головной болью для системных администраторов. 

Самая частая проблема - кракозябры вместо русских букв. А также бывает, что сайт будет показываться нормально, но при поиске будет выдавать совсем не то, если вообще что-то будет искаться.
Это происходит из за того, что текст на сервере в неверной кодировке, либо текст отдается клиенту в неверной кодировке.

Настройки mysql отвечающие за кодировки:
character_set_client - кодировка, в которой данные будут поступать от клиента
character_set_connection - кодировка по умолчанию для всего, что в рамках соединения не имеет кодировки
character_set_database - кодировка по умолчанию для баз
character_set_filesystem - кодировка для работы с файловой системой
character_set_results - кодировка, в которой будет выбран результат
character_set_server - кодировка, в которой работает сервер
character_set_system - кодировка, в которой задаются идентификаторы MySQL, всегда UTF8
character_sets_dir - папка с кодировками

Их значения можно посмотреть таким запросом: SHOW VARIABLES LIKE 'char%';
MariaDB [(none)]> SHOW VARIABLES LIKE 'character_set%';
----------------------------------------------------+
Variable_name Value
----------------------------------------------------+
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
----------------------------------------------------+

После установки mysql сервера, по умолчанию выставлена кодировку latin1.
Соответственно указанные выше глобальные переменные будут в latin1.
Базы соответственно по умолчанию и таблицы так же.

Рассмотрим как получать нормальную кодировку utf8 в нашей базе?
1. Делаем дамп базы с указанием необходимой нам кодировки:
mysqldump --u user -p --default-character-set=latin1 --skip-set-charset dbname > dump-latin1.sql

2. Сконвертируем наш файл dump-latin1.sql в нужную кодировку utf8
iconv -f ISO-8859-1 -t UTF8 dump-latin1.sql > dump-utf8.sql
Назначение ключей следующее:
-f latin1 – конвертировать из кодировки latin1 (кстати latin1 и iso-8859-1 это одно и то же)
-t UTF-8 – в кодировку UTF-8
dump-latin1.sql – файл который мы будем конвертировать
dump-utf8.sql – результаты запишутся в этот файл
Иногда встречаются особенности: на определенной позиции iconv может выдать ошибку о недопустимом символе во входящем файле.
Данная проблема вызвана тем, что в базе есть таблицы типа blob. И как следствие iconv не может сконвертить эти данные и останавливается с ошибкой.
Чтобы такого не произошло, дамп нужно делать с указанием параметра который экспортирует базу с полями blob в бинарном формате.
mysqldump --u user -p --default-character-set=latin1 --skip-set-charset --hex-blob dbname > dump-latin1.sql
После чего конвертация проходит без проблем.

3. Далее в файле дампа заменяем все упоминания о кодировке latin1 на utf8:
cat dump-utf8.sql|sed 's/CHARSET=latin1/CHARSET=utf8/g' > dump-utf8-new.sql

4. После этого удаляем нашу базу данных и создаем заново с нужной кодировкой:
mysql --user=user -p --execute="DROP DATABASE databasename; CREATE DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;"

5. Заливаем дамп в свежесозданную базу
mysql --user=root -p --default-character-set=utf8 databasename < dump-utf8-new.sql

Глобальные переменные инициализируются значениями по-умолчанию при старте MySQL сервера, или принимают значения, установленные в конфигурационном файле my.cnf.
Поэтому теперь идем править файл my.cnf, и приводим его в примерно такой вид:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
character-set-server=utf8
character-set-client=utf8
collation-server=utf8_unicode_ci
init-connect='SET NAMES utf8'

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

[client]
default-character-set=utf8

Рестартим нашу базу и проверяем:
MariaDB [(none)]> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Готово.

unix-way