Cнятие и установка дампа в MySQL (mysqldump) — tips & tricks

В теории

В теории все просто — MySQL из коробки имеет утилиту mysqldump, которая позволяет снять копию базы в виде набора SQL инструкций.

В реальности все гораздо хуже. Дело в том, что mysqldump криво работает с кодировками, отличными от Latin1. Тоесть экспортировать базу в UTF8 следуя официальной документации (man mysqldump), тоесть с указанием --default-character-set=utf8, и импортировать ее потом обратно — не представляется возможным. Кодировка будет битая в следствии «двойного преобразования», выполняемого утилитой mysqldump — пруфлинк

Собственно, по ссылке выше есть и рецеп. Суть его в том, что нужно экспортировать базу и затем импортировать ее в Latin1 вне зависимости от того, какая реально кодировка используется. При этом конечно нужно вычестить из *.sql файл инструкции SET NAMES…

Еще один важный момент, который следует учесть. Как правило мы снимаем дамп базы с именем A и пытаемся накатить его на базу с именем B. Соответственно, возможны коллизии из за того, что в созданном после экспорта файле в SQL запросах и командах СУБД будет присутствовать упоминание A. Нужно заменить все вхождения A на B, ниже под катом будет пример как это сделать средствами VIM.

И на последок, перед загрузкой дампа старую базу лучше полностью отчистить. Сделать это можно по-разному, я же для себя нашел удобным маленький shell-скрипт (drop_all.sh), который автоматизирует для меня эту работу:

#!/usr/local/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"
HOST="$4"

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

if [ $# -ne 4 ]
then
echo «Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} {MySQL-Host}»
echo «Drops all tables from a MySQL»
exit 1
fi

TABLES=$($MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

for t in $TABLES
do
echo «Deleting $t table from $MDB database...»
$MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e «SET FOREIGN_KEY_CHECKS = 0;drop table $t;SET FOREIGN_KEY_CHECKS = 1;»
done

Итак, алгоритм

Последовательность шагов такова:

1) Снимаем дамп.

bash> mysqldump -h {host} -u {user} -p{password} --default-character-set=latin1 -N {dbname} > latin_dump.sql

2) Открываем получившийся в итоге файл и осуществляем замену. Сохраняем изменения.

bash> vi latin_dump.sql

:%s/dbname/dbname2/g

3) Чистим базу перед загрузкой дампа.

bash> ./drop_all.sh {user} {password} {dbname2} {host}

4) Загружаем дамп.

bash> mysql --force -h {host} -u {user} -p{password} --default-character-set=latin1 -D {dbname2} < latin_dump.sql