Архів мітки: mysqldump

mysqldump, cron та –defaults-extra-file

Для резервного копіювання баз даних MySQL використовується утиліта mysqldump. Щоб не “світити” пароль доступу до бази даних можна використовувати файл .my.cnf такого змісту:

$ cat ~/.my.cnf 
[client]
user=КОРИСТУВАЧ
pass=ПАРОЛЬ

Тепер можна запускати mysqldump без вказування паролю:

$ mysqldump -u КОРИСТУВАЧ БАЗА_ДАННИХ > файл_дампу.sql

Але якщо ви спробуєте додати таку команду у crontab, то вас спіткає невдача:

mysqldump: Got error: 1045: Access denied for user 'КОРИСТУВАЧ'@'localhost'
 (using password: NO) when trying to connect

Справа в тому, що з CRON середовища файл ~/.my.cnf не зчитується. Треба додати спеціальну опцію –defaults-extra-file=’шлях до вашого .my.cnf’

Але і тут все не так просто. Наприклад, наступна команда не буде працювати:

$ mysqldump -u КОРИСТУВАЧ --default-extra-file=/home/КОРИСТУВАЧ/.my.cnf БД > файл_дампу.sql
mysqldump: unknown variable 'default-extra-file=/home/КОРИСТУВАЧ/.my.cnf'

Причина в послідовності аргументів. Першим має бути –default-extra-file:

$ mysqldump --default-extra-file=/home/КОРИСТУВАЧ/.my.cnf -u КОРИСТУВАЧ БД > файл_дампу.sql

mysqldump – кожен запис окремим рядком

Утиліта mysqldump при збереженні даних таблиць об’єднує усі записи у одну команду INSERT. Виглядає це приблизно так:

INSERT INTO `таблиця` VALUES (запис1),(запис2), ..., (останній запис);

Коли таблиця має багато записів, то команда INSERT перетворюється у довжелезний рядок. Це зручно для машини, але людині читати такий файл складно.

Одне з рішень – замість однієї команди INSERT використовувати окремі команди для кожного запису:

mysqldump --extended-insert=FALSE --complete-insert=TRUE ...

Тоді файл матиме вигляд:

INSERT INTO `таблиця` (опис полів) VALUES (запис1);
INSERT INTO `таблиця` (опис полів) VALUES (запис2);
INSERT INTO `таблиця` (опис полів) VALUES (...);
INSERT INTO `таблиця` (опис полів) VALUES (останній запис);

Але є краще рішення. Для цього достатньо пропустити результат mysqldump –extended-insert крізь регулярний вираз у інтерпретаторі Perl:

mysqldump --extended-insert ... | perl  -pane "s{),(}{),n(}smg" > дамп_файл.sql

Після цього сприймати зміст дампу стає значно простіше:

INSERT INTO `таблиця` (опис полів) VALUES (запис1),
(запис2),
(запис3),
...,
(останній запис);

Як витягнути дані з MySQL у звичайний текстовий файл

Гадаю, користуватися mysqldump вміють усі, але іноді потрібно з бази даних MySQL витягнути лише дані – без форматування та SQL синтаксису. Наприклад, вам потрібен перелік користувачів з таблиці users, тобто звичайний текстовий файл, де ім’я кожного користувача займає окремий рядок.

Мені потрібно було зібрати в одному текстовому файлі тексти, що написані італійською. Запит мовою SQL для цього запиту використовувався такий:

mysql> SELECT text FROM comments WHERE language = "it";

Але як спрямувати його до текстового файлу? Виявляється є стандартна команда INTO OUTFILE:

mysql> SELECT text FROM comments WHERE language = "it"
 INTO OUTFILE "comments_it.txt";
Query OK, 3852 rows affected (0.00 sec)

Спрацювала вона добре, але де ж цей файл comments_it.txt? Я шукав його так:

$ sudo -s
# updatedb
# locate comments_it.txt
/var/lib/mysql/ІМ'Я_БАЗИ_ДАНИХ/comments_it.txt

Тобто файл буде збережено у каталозі БД. Також майте на увазі, що абсолютних шляхів команда INTO OUTFILE не розуміє.

Конвертація дампу бази даних MySQL з Latin1 у UTF8

Постала задача забезпечити підтримку юнікоду у базі даних. Після модифікації параметрів підключення до БД виявилось, що замість юнікоду зберігається якась абракадабра.

Хоча були додані стандартні параметри:

  • useUnicode=true
  • characterEncoding=utf8
  • characterSetResults=utf8
  • connectionCollation=utf8_general_ci

Уважно подивившись у код дампу БД я побачив, що для усіх таблиць використовується кодування latin1 замість utf8. Нижче ви можете побачити фрагмент дампу:

...
DROP TABLE IF EXISTS `autosave`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `autosave` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `create_time` timestamp NOT NULL default CURRENT_TIMESTAMP
 on update CURRENT_TIMESTAMP,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
...

Традиційний програміст почав би копати документацію MySQL на тему зміни кодування БД та окремих таблиць, але сьогодні я обрав тактику системного адміністратора: я вирішив замінити кодування прямо у дампі БД.

Отже спочатку я створюю дамп БД у файлі dump.sql:

$ mysqldump -u КОРИСТУВАЧ -p БД > dump.sql

Потім використовую команду sed для заміни всіх кодувань таблиць:

$ sed -i 's/DEFAULT CHARSET=latin1;/DEFAULT CHARSET=utf8;/' dump.sql

Якщо лячно одразу використовувати цей запит на реальному дампі, можете переглянути спочатку його результати (що саме він модифікує) такою командою:

$ sed -n 's/DEFAULT CHARSET=latin1;/DEFAULT CHARSET=utf8;/p' dump.sql
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
...

Тепер видаляємо стару БД, створюємо її знову, але вже з кодуванням UTF-8 за умовчанням:

$ mysql -u КОРИСТУВАЧ -p БД
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 36
Server version: 5.0.51a-24+lenny2 (Debian)
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> DROP DATABASE БД;
Query OK, 64 rows affected (0.20 sec)
mysql> CREATE DATABASE БД DEFAULT CHARSET utf8;
Query OK, 1 row affected (0.01 sec)
mysql> exit
Bye

І нарешті заливаю новий дамп:

$ mysql -u КОРИСТУВАЧ -p БД < dump.sql