Как импортировать большую базу данных в mysql

17 Jul 2011 | Автор: anchous |

Бывает возникает ситуация, в которой необходимо загрузить на сервер хостинга базу данных больше разрешенного хостингом в утилите управления базами данных phpMyAdmin- обычно этот предел установлен в  ~2Mb. Если это случается на виртуале, то обычно просто заливаю базу через ftp и прошу суппорт хостинга помочь в данном вопросе.

Поскольку использую нормальные хостинги, то решение это проблемы занимает всего 10-15 минут, так что особо не парился, но тут столкнулся с тем что возникла необходимость подсказать человеку у которого суппорт курил бамбук, так что выделилось несколько вариантов решения данной проблемы. Оговорюсь сразу что наиболее удобен для импорта базы данных mysql последний вариант, но он подходит только тем у кого есть доступ к терминалу. Так что в статье описаны все возможные способы импорта большой базы данных в mysql, кроме общения с суппортом.

1. Варинт первый – выгружаем бэкап базы не целиком, а по несколько таблиц, чтобы файл не превышал наши лимиты. Основной минус данного решения- это то, что операцию выгрузки- загрузки необходимо будет проводить много раз, так что подойдет исключительно для тех у кого много свободного времени и нечем заняться на досуге.

2. Вариант второй – выгружать в пожатом состоянии- так как степень сжатия для текстового файла будет максимальной. Но тут опять же можно столкнуться с тем, что на хостинге не установлена поддержка архиваторов.

3. Используем сторонний скрипт, который позволяет производить экспорт и импорт базы данных, минуя общение с phpMyAdmin. Наиболее известный из имеющихся в наличии, и к тому же наиболее хорошо работающий- это BigDump. О том как его настроить – я отписался чуть позже, так что это касается и настройки по 5му пункту.

4. Как частный случай 3 вариант, возможно залить на сервер эмулятор терминала и сделать 5 пункт через него. Например можно попробовать PHPterm, но в этом варианте можно столкнуться с ограничениями хостинга в плане безопасности запуска команд и скриптов.

5. Также есть простенькая программка аналогичная BigDump, которая также позволяет импортировать большие базы данных, только в данном случае необходим Zend Optimizer либо Nu-Coder. Программка называется Sypex Dumper, и вторую версию скрипта можно даже интегрировать в наиболее популярные CMS. На сайте есть как платная утилита, так и облегченная бесплатная версия.

6. Если таки имеется доступ к консоли, то можно добавить в php.ini следующие строки (увеличиваем лимит загрузки файлов по времени и размеру), после загрузки, лучше вернуть все назад, чтобы не нагнули сервак с оказией:
max_execution_time = 20000
max_input_time = 20000
upload_max_filesize = 150M
post_max_size = 150M

7. Но на самом деле наиболее быстрым и оптимальным вариантом загрузки большой базы данных, имея доступ к консоли, будет загрузить дамп базы данных dump.sql через ftp, а затем залить её через консоль ssh:
mysql -h hostname -u user-name -p DB_name <  /path-to-dir/dump.sql
если у вас в качестве хоста базы данных используется стандартный localhost , то ключик -h можно и опустить, так что получится вида:
mysql -u user-name -p DB_name <  /path-to-dir/dump.sql

Да, чуть не забыл про самые поганые грабли всей пляски с импортом- при импорте базы данных могут возникнуть проблемы с кодировками, поэтому нам надо убедиться что дефолтная база и выгрузка sql находятся в одной кодировке, либо переконвертировать базу данных в стандартную для mysql кодировку (обычно это utf-8):
iconv -f CP1251 -t UTF-8 dump.sql > dump.sql.utf8
либо указать необходимую кодировку при процедуре импорта базы данных:
mysql -h hostname -u user-name -default_character_set utf8 -p DB_name <  /path-to-dir/dump.sql

VN:F [1.9.21_1169]
Rating: 8.2/10 (33 votes cast)
VN:F [1.9.21_1169]
Rating: +6 (from 8 votes)
Как импортировать большую базу данных в mysql, 8.2 out of 10 based on 33 ratings

Теги: , ,

Отзывов: 9 на «Как импортировать большую базу данных в mysql»

  1. Автор: x64 на 02 Sep 2011

    самым быстрым способом является копирование файлов таблиц в требуемые базы (папки). самый быстрый и надёжный способ. из минусов если только не везде «заведётся». у меня таких случаев не было, но говорят, что может быть.

    [Reply]

    anchous Reply:

    ну предположим что базы вертятся не под MyISAM, а в InnoDB- что тут копировать?
    к тому же такой способ требует гашения сервака, ибо если дернуть из под работающего- как раз могут и не завестись

    [Reply]

    x64 Reply:

    InnoDB далеко не самая распространённая.
    ну а ночью разок можно и стопнуть демона, не у всех же базы гигабайтами исчисляются. остановка будет всего на несколько секунд и её скорее всего просто не заметят. помнится раньше рамблер топ-100 в полночь уходил в глубокий даун, пересчитывая статистику, и ничего же)

    [Reply]

    Илья Reply:

    И где сейчас рамблер?

    VA:F [1.9.21_1169]
    Rating: 4.0/5 (3 votes cast)
    VA:F [1.9.21_1169]
    Rating: +2 (from 6 votes)
  2. Автор: kris на 26 Jun 2013

    а если база в innodb и весит 500+ Gb
    что тогда?
    тут стоп будет замечан 100%
    так что тут реплика нужна
    а на реплике уже можно и бэкап делать

    [Reply]

    VA:F [1.9.21_1169]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.21_1169]
    Rating: -1 (from 1 vote)
  3. Автор: Марина на 22 Jul 2013

    Спасибо за полезную информацию:)))

    [Reply]

    VA:F [1.9.21_1169]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.21_1169]
    Rating: -1 (from 1 vote)
  4. Автор: Сергей на 18 May 2014

    Здрасьте!
    Скажите пожалуйста, где находиться консоль ssh и куда нужно вписывать это:
    mysql -h hostname -u user-name -p DB_name < /path-to-dir/dump.sql ?

    [Reply]

    anchous Reply:

    консоль ssh находится на сервере, куда вы подключаетесь с помощью ssh клиента – простейший это puTTY

    [Reply]

    VN:F [1.9.21_1169]
    Rating: 0.0/5 (0 votes cast)
    VN:F [1.9.21_1169]
    Rating: 0 (from 0 votes)
  5. Автор: Артём Похила на 07 Aug 2018

    Импорт большой базы данных с помощью консоли (терминала):

    1) Необходимо загрузить файл с дампом базы на VPS. Для этого можно использовать подключение по SFTP-протоколу с правами root.

    Установив SFTP соединение, копируем sql-файл в любую директорию на сервере, например, /root.

    2) Теперь подключаемся к VPS через SSH-консоль с правами root. Такой уровень доступа вы получаете по умолчанию, заказав любой хостинг виртуальных серверов.

    Подключаемся к серверу MySQL командой:

    mysql -u root(Имя пользователя с помощью которого вы входите в phpmyadmin) –p

    Например, mysql -u oleg –p

    Вводим пароль mysql-пользователя root и нажимаем Enter. В результате в консоли будет выведено приветствие «Welcome to the MySQL monitor.» с указанием id сессии и версии MySQL.

    3) Зайдем в созданную базу данных при помощи команды:

    USE database_name (имя базы данных)

    Например, USE nano (имя базы данных)

    4) Для импорта дампа БД используется команда source. В случае с файлом database.sql команда будет следующей:

    source /root/database.sql;

    Визуальный пример выполнения этой команды представлен ниже:

    mysql> source /root/database.sql;
    Query OK, 0 rows affected (0.01 sec)
    Query OK, 0 rows affected (0.00 sec)
    Query OK, 0 rows affected (0.03 sec)
    Query OK, 0 rows affected (0.00 sec)
    Query OK, 0 rows affected (0.00 sec)
    Query OK, 0 rows affected (0.00 sec)
    Query OK, 1 row affected (0.00 sec)
    Database changed
    Query OK, 0 rows affected (0.01 sec)
    Query OK, 4 rows affected (0.01 sec)
    Records: 4 Duplicates: 0 Warnings: 0
    Query OK, 0 rows affected (0.00 sec)
    Query OK, 0 rows affected (0.00 sec)
    Query OK, 0 rows affected (0.00 sec)
    mysql>

    [Reply]

    VA:F [1.9.21_1169]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.21_1169]
    Rating: 0 (from 0 votes)

Ваш отзыв