Экспорт/импорт данных из/в MySQL при большом количестве записей в таблице

Размещено 2010-07-06 в разделе Программирование | Метки: MySQL

Недавно пришлось столкнуться с тем что при импорте в базу данных таблицы с количеством записей больше 1 млн, вставляются они мягко говоря медленно. Мне надо было перенести данные со своего сервака на shared hosting, как обычно я запустил mysqldump, выгрузил всю базу в файл, заархивировал, послал с помощью scp, распаковал и начал импортировать с помощью mysql
< file.sql. При этом Load average сильно вырос до 40 и рос дальше, данные все продолжали импортироваться больше 20 минут. Попробовал на своем серваке импортировать тоже очень долго. Скриптом вставлять по одному запросу или в запросе по несколько записей все равно было очень медленно даже с INSERT DELAYED, нашел способ делать это быстрее. Для экспорта данных сначала надо дать права себе на базы.

GRANT FILE ON *.* TO boris@'%';

Дальше я попробовал экспортировать данные и стокнулся с такой ошибкой

mysql> SELECT * INTO OUTFILE '/home/boris/temp/test.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM lj_usernames l LIMIT 0,1000;
ERROR 1 (HY000): Can't create/write to file '/home/boris/temp/test.SQL' (Errcode: 13)

Дело в том, что для MySQL надо указывать путь к файлу откуда может прочитать юзер mysql, я переложил файл в /var/lib/mysql и от туда уже все быстро экспортировалось.

mysql> SELECT * INTO OUTFILE '/var/lib/mysql/test.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM lj_usernames ;
Query OK, 1579578 ROWS affected (17.82 sec)

Для импорта использовал эту команду

LOAD DATA LOCAL INFILE '/home/username/domain.tld/test.sql' INTO TABLE lj_usernames FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ;