<rmcreative>

RSS

Импорт больших SQL-дампов через PHP

27 мая 2011

Основная проблема при импорте большого SQL-дампа — нехватка памяти при чтении всего дампа. Решение очевидно: грузить в память только то, что нужно в данный момент.

$f = @fopen("path/to/dump.sql", "r");
if($f)
{
    $q = '';
 
    while(!feof($f))
    {
        // читаем построчно в буфер $q      
        $q .= fgets($f);
 
        // пока не упрёмся в ;
        if(substr(rtrim($q), -1) == ';')
        {
            // выполяем запрос
            execute_sql($q);
 
            // обнуляем буфер
            $q = '';
        }
    }
}

Комментарии RSS

  1. №4746
    seocoder
    seocoder 27 мая 2011 г., 21:05:48

    Консоль или sypex dumper Зачем "велик" то?

  2. №4747
    seocoder
    seocoder 27 мая 2011 г., 21:07:27

    И код такой работать тоже не будет скорее всего. Нету гарантии что в 4кб попала целая строка

  3. №4748
    Sam
    Sam 27 мая 2011 г., 21:42:14

    seocoder, например, если нужно сделать свой инсталлер для продукта. Не заставлять же пользователя ставить и использовать Sypex. Ограничение убрал.

  4. №4750
    conf
    conf 27 мая 2011 г., 21:52:16

    Как-то ненадежно у вас это все, да и медленно будет. А если последовательность ";\n" встретится внутри какого-нибудь текстового поля? Я бы больше предпочел команду из консоли

    mysql -uuser -ppassword database < dump.sql

    либо если доступа к ней нет, то инструкцию LOAD DATA INFILE.

  5. №4751
    Sam
    Sam 27 мая 2011 г., 21:59:12

    conf, если вообще нет доступа к ssh и exec нельзя?

  6. №4752
    Sam
    Sam 27 мая 2011 г., 22:06:51

    Кстати, перенос строки в данных импорт не сломает. Вот ; может.

  7. №4753
    Максим
    Максим 27 мая 2011 г., 22:20:46

    Выложу свои решения-ка... import.php:

    <?
            error_reporting(E_ALL);
            mysql_connect('mysqlserver','user','pass');
            mysql_select_db('db');
            mysql_query('SET NAMES "cp1251"');
            $dump=file_get_contents('dump.sql');
            $q='';
            $state=0;
            for($i=0;$i<strlen($dump);$i++){
                switch($dump{$i}){
                    case '"':
                        if($state==0) $state=1;
                        elseif($state==1) $state=0;
                        break;
                    case "'":
                        if($state==0) $state=2;
                        elseif($state==2) $state=0;
                        break;
                    case "`":
                        if($state==0) $state=3;
                        elseif($state==3) $state=0;
                        break;
                    case ";":
                        if($state==0) {
                            echo $q."\n;\n";
                            mysql_query($q);
                            $q='';
                            $state=4;
                        }
                        break;
                    case "\\":
                        if(in_array($state,array(1,2,3)))
                        $q.=$dump[$i++];
                        break;
                }
                if($state==4) $state=0;else $q.=$dump{$i};
            }
    ?>

    export.php:

    set_time_limit(0);
        $dump=fopen(dirname(__FILE__).'/dump.sql','w');
     
        function dump($sql) {
            global $dump;
            fwrite($dump,$sql);
            ob_flush();
        }
     
        function trace($msg)
        {
            echo $msg.'<br>';
            ob_flush();
        }
     
        mysql_connect('localhost','user','pass');
        mysql_select_db('db');
        mysql_query('set names utf8');
     
        $res=mysql_query('show tables');
     
        while($tbl=mysql_fetch_array($res))
        {
          $table=$tbl[0];
          $r=mysql_query('show create table `'.mysql_real_escape_string($table).'`');
          $struct=mysql_fetch_array($r);
          $sql_struct[$table]=$struct[1].';';
        }
     
     
        dump("set names utf8;\n");
     
        foreach($sql_struct as $tbl_name=>$crt_str){
            trace('Экспортирую '.$tbl_name);
            dump("DROP TABLE IF EXISTS `".$tbl_name."`;\n");
            dump($crt_str."\n");
            dump("LOCK TABLES `".$tbl_name."` WRITE;\n");
            mysql_query('LOCK TABLES `'.$tbl_name.'` READ');
            $res=mysql_query('select * from `'.$tbl_name.'`');
            $insert_str='insert into `'.$tbl_name.'` values ';
            while($item=mysql_fetch_assoc($res)){
                foreach($item as $k=>$v){
                    $item[$k]=mysql_real_escape_string($v);
                }
                dump($insert_str.'("'.implode('","',$item).'");'."\n");
            }
            dump("UNLOCK TABLES;\n");
            mysql_query('UNLOCK TABLES');
        }
     
        dump('-- end of export');
        trace('Все таблицы были успешно экспортированы');

    Работают безотказно. Лучше чем phpmyadmin. Даже когда все скрипты/проги не могут импортнуть/экспортнуть - эти скрипты справляется.

  8. №4754
    Sam
    Sam 27 мая 2011 г., 22:23:00

    Максим, на большом дампе file_get_contents захлебнётся.

  9. №4755
    Максим
    Максим 27 мая 2011 г., 22:26:23

    Можно переделать на fopen, это всего лишь основа. Зато мой скрипт анализирует синтаксис sql файла. И всегда точно определяет запросы.

  10. №4757
    просто Вася
    просто Вася 28 мая 2011 г., 14:07:29

    много велосипедов хороших и разных, чем консоль mysql не устраивает? это нормально работает и с 8 гб дампами mysql>use db; mysql>source file.sql;

  11. №4758
    Сергей #3
    Сергей #3 28 мая 2011 г., 15:36:23

    Ужос ужос!

    Как правильно заметили, ";" внутри данных ваш код поломают. Еще может попасться ";" внутри комментария (это, конечно, реже, но возможность есть). Да и freads лучше на fread() заменить наверное.

    Решение от Максима еще куда ни шло, но нужно допилить для комментариев и для больших дампов (заменить file_get_contents и убрать strlen($dump) на каждой итерации как минимум)

  12. №4759
    Максим
    Максим 28 мая 2011 г., 17:59:19

    Сергей #3, Если вы обладаете более лучшим решением, то покажите нам его. А про оптимизации я и так знаю.

  13. №4760
    Сергей
    Сергей 28 мая 2011 г., 21:39:15

    Вы тут нас совсем за дурачков/идиотов принимаете с такими постами?))))

  14. №4761
    Максим
    Максим 28 мая 2011 г., 23:10:34

    А по-моему хороший пост. Потому что: 1) не всегда есть доступ к phpmyadmin. 2) не всегда серверные решения работают корректно. Я именно по этим причинам и написал свой велосипед когда работал с рекламном агентстве и занимался сайтами. Когда подписан договор и уже пошел процесс работы ни принимающая/ни отдающая сторона не станет искать данные у человека который ответственен за доступы к панеле управления, и который, возможно, уже не работает в компании-заказчике.

  15. №4768
    mitallast
    mitallast 29 мая 2011 г., 23:00:29

    Ниочем вообще. Синтаксис sql куда богаче, одним if-ом не обойтись. Даже если удастся правильно разбирать поток на наличие знака ";" это все равно ничего не гарантирует - в mysql можно вызвать SET DELIMITER.

    Плюс ко всему, ну правда, в чем смысл? Почему так тянет на велосипеды? Делать такой код бессмысленно, тратите как свое время, так и время заказчика на пустую проблему - гораздо дешевле взять более правильный тариф или хостинг, чем пытаться писать неработоспособный код и поддерживать его.

    Даже если есть необходимость, все можно сделать проще - вынести table definion в отдельный файл, table content - в другой, например формата csv - и это будет гарантированный результат работы, который можно покрыть тестами. Сделать аналогичное для sql92 хотяб куда сложнее.

  16. №4769
    Максим
    Максим 30 мая 2011 г., 0:01:55

    Для sql используется простой лексический анализатор, мой скрипт импорта прекрасно распознает концы запросов, при этом не путая их с ; внутри строк...

    Если слушать критику, тогда ни phpmyadmin, ни sypex dumper и ему подобные не нужны, так как заказчика можно убедить перейти на хороший хостинг с поддержкой ssh.

  17. №4771
    Sergunik
    Sergunik 30 мая 2011 г., 17:11:30

    Такой вариант подходит только если к консоли нету доступа.

  18. №4799
    Dmitry Scriptin
    Dmitry Scriptin 04 июня 2011 г., 16:36:37

    Реально бывают случаи, когда нет ни консоли, ни phpMyAdmin. Например, образовательным учреждениям бесплатно раздают убогие площадки для сайтов. В таких случаях я бы сам поставил phpMyAdmin и не парился.

  19. №5271
    Vov4ik
    Vov4ik 02 сент. 2011 г., 19:15:55

    Почему-то большинству не приходит в голову, что вариант чтения дампа скриптом всё-таки в некоторых случаях - единственное решение. Реальный пример:

    Заказчик просит проработать оную возможность, т.к. хочет

    1. Откладывать в определённую папку на FTP вышеупомянутый дамп сгенерированный посредством 1С.
    2. Cron подхватывает этот дамп и посредством скрипта (т.к. хостинг шаровый) реализует его исполнение.

    Только указанный в посте вариант укакается при конструкции такого вида:

    INSERT INTO TableName (...) VALUES (...),(...), ... ; ,

    где список содержимого состоит из многих полей. Т.е. сам ";" стоИт в самом конце и сводит на нет саму логику примера.

  20. №9506
    Виталий
    Виталий 24 дек. 2014 г., 9:38:07

    Было бы шикарно, если бы кроме критики еще и решение

  1. Почта опубликована не будет.

  2. Можно использовать синтаксис Markdown или HTML.

  3. Введите ответ в поле. Щёлкните, чтобы получить другую задачу.