Бывает возникает ситуация, в которой необходимо загрузить на сервер хостинга базу данных больше разрешенного хостингом в утилите управления базами данных 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

----------------------------------

И ещё немного информации ...

Импорт и экспорт данных MySQL

Экспорт и импорт данных в MySQL обычно требуется при переносе информации из одной базы данных MySQL в другую и для осуществления резервного копирования.

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

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

Единственный способ быть уверенным в 100% восстановлении данных — самостоятельно выполнять регулярное резервное копирование информации. Для этого необходимо воспользоваться утилитой mysqldump, которая доступна через unix shell.

Пример команд для экспорта и импорта базы данных:

mysqldump -u имя_пользователя -p -h имя_сервера_БД имя_базы > dump.sql

Данные будут сохранены в файле dump.sql. Далее (в случае необходимости) восстанавливаем данные из резервной копии (дампа) так:

mysql -u имя_пользователя -p -h имя_сервера_БД имя_базы < dump.sql

Запускаемые указанным образом утилиты для работы с MySQL будут запрашивать пароль к базе данных. Нужно вводить пароль соответствующего пользователя БД. Если пароль был утерян, вы можете самостоятельно изменить его в панели управления, в разделе Древо услуг (выпадающее меню расположено в самой верхней строчке панели управления справа).

По умолчанию система считает, что данные в базе хранятся в кодировке UTF-8. Если ваши данные хранятся в другой кодировке, то после создания резервной копии (дампа) для последующего корректного восстановления данных необходимо открыть созданный файл текстовым редактором и исправить в нём строчку:

/*!40101 SET NAMES utf8 */;

Например, для кодировки Windows-1251 нужно указать:

/*!40101 SET NAMES cp1251 */;

Дополнительные опции утилиты mysqldump:

--add-drop-table — опция, которая добавляет команду DROP TABLE перед созданием таблиц. Перед восстановлением таблиц из дампа, таблицы с таким же именем в рабочей базе данных будут удалены и пересозданы из резервной копии. Рекомендуется использовать для предотвращения возможных ошибок после восстановления;

--add-locks — опция, которая добавляет команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы. Применяется для ускорения доступа к MySQL;

--quote-names — опция, сообщающая утилите о необходимости ставить кавычки для названий таблиц и столбцов. Рекомендуется к использованию для MySQL версий, младше 4.1.1. В старших версиях она активирована по умолчанию.

Опции --quick и --opt рекомендуется использовать, если база данных MySQL слишком большая для того, чтобы целиком поместиться в памяти.

При этом утилита mysqldump выдает ошибку:

mysqldump: Out of memory (Needed XXXXX bytes)
mysqldump: Got error: 2008: MySQL client
run out of memory when retrieving data from server 

В итоге строчка для создания копии базы данных получается следующей:

mysqldump --opt -u имя_пользователя -p -h имя_сервера_БД --add-drop-table имя_базы > dump.sql

либо такой:

mysqldump --quick -u имя_пользователя -p -h имя_сервера_БД --add-drop-table имя_базы > dump.sql

Для совместимости дампа, сделанного на стороннем сервере мы рекомендуем создавать дамп базы данных с ключом:

--set-variable max_allowed_packet=2M

либо

-O max_allowed_packet=2M

Если во время импорта вы получите ошибку вида

mysqldump: Error 2020:
Got packet bigger than 'max_allowed_packet'
bytes when dumping table `some_table_name ` at row: 2

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

Как правило, такое случается, если базу данных MySQL используют для хранения содержимого различных файлов, например, картинок или музыки, не размещая их непосредственно в файловой системе. В таком случае, если размер какого-нибудь файла превышает определённое значение — база данных становится непригодной для использования на наших серверах и потребуется либо удалить такие данные из вашей базы, либо рассмотреть вариант использования сервера баз данных на VPS-сервере , где можно установить нужное ограничение max_allowed_packet в необходимое значение, либо не использовать его вообще.

Если на вашем сервере установлена версия MySQL 4.0.x, то для корректного переноса данных со сторонних серверов необходимо при экспорте базы данных использовать ключ:

--compatible=mysql40

Как работать с MySQL из скриптов на Perl

Для работы с MySQL из Perl-скриптов как правило используют модуль DBI. Это позволяет удобно открывать и закрывать соединения к СУБД, подготавливать и выполнять запросы к базе, обрабатывать ошибки и так далее. Если вы не имеете опыта работы с MySQL из Perl, рекомендуем вам книгу «Программирование на Perl DBI» (рус., англ.).

Итак, пример работы с MySQL из Perl DBI:

#!/usr/bin/perl

use DBI;

my $host = "u12345.mysql.masterhost.ru"; # вымышленный MySQL-сервер
my $port = "3306"; # порт, на который открываем соединение
my $user = "u12345"; # имя пользователя (вымышленное)
my $pass = "password"; # пароль
my $db = $user; # имя базы данных -по умолчанию равно имени пользователя

print "Content-type: text/html\n\n";

$dbh = DBI->connect("DBI:mysql:$db:$host:$port",$user,$pass);
$sth = $dbh->prepare("select field1 from table2");# готовим запрос
$sth->execute; # исполняем запрос

while ($ref = $sth->fetchrow_arrayref) {
print "$$ref[0]\n"; # печатаем результат
}

$rc = $sth->finish;    # закрываем
$rc = $dbh->disconnect;  # соединение

В этом примере мы сначала определяем переменные с параметрами доступа к MySQL, далее выдаем HTTP-заголовок, так как это у нас скрипт для запуска через веб, затем соединяемся с базой, подготавливаем и отправляем select-запрос, получаем и печатаем результаты, после чего закрываем соединение. Это простейший скрипт для работы с MySQL из Perl. Более подробные примеры вы можете найти в этой статье.

Как работать с MySQL из скриптов на PHP

Для работы с MySQL вы можете использовать встроенный в язык PHP средства, поддержка которых уже осуществляется на хостинге. PHP имеет развитый и понятный интерфейс к этой СУБД, что делает работу с MySQL именно из PHP наиболее простой и удобной особенно для начинающих веб-мастеров и веб-программистов.

# соединяемся с сервером и выбираем свою базу данных
mysql_connect("u12345.mysql.masterhost.ru","u12345","password");
mysql_select_db("database");

# готовим и выполняем запрос к БД
$query = "SELECT * FROM table";
$result = mysql_query($query)
or die(mysql_error());

# выводим результаты выполнения запроса
while($i = mysql_fetch_row($result)) {
echo $i[0];
echo $i[1];
.....
} 

Полная документация на русском языке по работе с MySQL из PHP-скриптов находится здесь. Кроме описаний, там же вы найдете советы опытных пользователей и примеры использования функций, упоминаемых в документации. Также рекомендуем вам статью «Использование MySQL в PHP сценариях» на  сервере hostinfo.ru.

Загрузка данных с помощью LOAD DATA

Начиная с версии MySQL 3.23.49, LOAD DATA LOCAL по умолчанию запрещено по соображениям безопасности, и его надо специально включать.

MySQL-сервер у нас работает с включенным LOAD DATA LOCAL, но необходимо включение данной функциональности со стороны клиента.

Для утилиты mysql есть несколько вариантов включения данной опции:

1. Параметр --local-infile=1
2. Создать в домашней директории файл .my.cnf и в нем разрешить эту опцию для группы [mysql], которой пользуется данная утилита:

   [mysql]
   local-infile=1

Включение данного режима из perl'а: необходимо в dsn при подключении указать файл конфигурации mysql (т.к. библиотека libmysql по умолчанию никаких дополнительных файлов конфигурации не читает) и группу в нем, в файле создать эту группу, и для этой группы выставить local-infile=1:

Например, вписываем в ~/.my.cnf

[perl]
local-infile=1

а в скрипте подключение оформляем так:

my $dsn = "DBI:mysql:database:hostname;mysql_read_default_group=perl;". "mysql_read_default_file=~/.my.cnf";
my $dbh = DBI->connect($dsn, "username", "password") ||die "[err]: Can't connect to MySQL: $!";

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

В функции mysql_connect() необходимо использовать аргумент 128 (значение константы CLIENT_LOCAL_FILES) в качестве пятого параметра.

Пример:

$dbh = mysql_connect($server, $user, $pass, false, 128);

Дополнительные MySQL-база и MySQL-доступ (MySQL-пользователь)

Пользователям предоставляется определенное количество баз MySQL в рамках приобретенного тарифа. При этом автоматически подключается одна база, и реквизиты доступа к ней сообщаются в письме с уведомлением о заведении услуг. Клиенты могут самостоятельно создавать дополнительные базы данных и MySQL-доступы.

Для этого необходимо в контрольной панели в разделе Древо услуг выбрать площадку, для которой необходимо создать дополнительную услугу и в таблице «MySQL» выбрать команду «добавить» для услуги «MySQL-доступ (доп.)» или «MySQL-база (доп.)». Пароли доступа к базам данных можно изменить, вызвав команду «изменить» у требуемого MySQL-доступа.

--------------------

Вот еще немного инфы (которая, кстати работает, сам проверял)

Импорт больших файлов sql в базу данных mysql

Как изменить максимально допустимый размер импорта файла sql в базе данных Mysql.

Для этого нужен доступ к папке \usr\local\mysql-5.1\ на сервере (или на локальном сервере denver).

По умолчанию у вас установлен Максимально допустимый размер загружаемого файла 2 MB.

По этому пути \usr\local\mysql-5.1\ открываем файл конфигурации my.cnf.

Раскомментируйте следующие строки, если вы хотите изменить
настройки InnoDB по умолчанию.
#
#innodb_data_home_dir = /usr/local/mysql-5.1/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql-5.1/var/
#innodb_log_arch_dir = /usr/local/mysql-5.1/var/
# Вы можете установить .._buffer_pool_size размером до 50 - 80 %
# оперативной памяти, но опасайтесь перерасхода
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M - здесь меняем до нужного размера!
#Устанавливайте .._log_file_size to 25 % от _buffer_pool_size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout = 50

Затем перезагружаем сервер и весело работаем с импортом больших sql файлов.

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

Добавить комментарий / отзыв

Комментарии   

 

0 # ewff 08.02.2020 19:31

Спасибо чел. Перед этим прочитал кучу блогов, где не правильно писали. Благодаря тебе разобрался!

Ответить | Ответить с цитатой | Цитировать

 

 

0 # Евгений 05.07.2024 20:43

ssh реально рулит!!! спасибо за совет ;)

Ответить | Ответить с цитатой | Цитировать

 

Обновить список комментариев
RSS лента комментариев этой записи

Статьи по теме

Как быстро запустить Laravel на Windows

Все привет, меня зовут Павел Жуковский и сегодня я расскажу о том как быстро развернуть Laravel на Windows. Как развернуть чере...

2024-09-30

Next.js

На мой взгляд, Next.js - это лучший на сегодняшний день инструмент для разработки веб-приложений. Обратите внимание: руководство актуально �...

2024-09-25

OpenAI рассказал, как запретить ChatGPT использовать содержимое сайта для обучения

OpenAI рассказал, как запретить ChatGPT использовать содержимое сайта для обучения Либо ограничить доступ. OpenAI рассказа...

2024-09-05

WebSocket

Протокол WebSocket («веб-сокет»), описанный в спецификации RFC 6455, обеспечивает возможность обмена данными между браузером и сервером че...

2024-08-01

Ошибка 404: что это такое и как ее использовать для бизнеса

Для SEO 404 Error — возможность сохранить клиента и позиции в выдаче. Как это сделать, в статье. Окно «404 Not Found» вид...

2024-07-17

PostgreSQL 16. Изоляция транзакций. Часть 2

Введение Данная статья является продолжением первой части: "PostgreSQL 16. Организация данных. Часть 1". Как и первая часть, эта является объед�...

2024-05-27

Комментирование кода и генерация документации в PHP

Зачем нужны комментарии к программному коду? В каком виде их писать? Где они нужны, а где нет? Как правильно комментировать код? Как прид�...

2024-04-22

Что такое CSS-модули и зачем они нам?

В последнее время меня интригуют CSS-модули. Если вы о них не слышали — эта статья для вас. Мы рассмотрим, что это за проект, какие задачи о�...

2024-04-22

23 сервиса для эффективного экспресс-аудита любого сайта

Аудит сайта – комплексный процесс, который позволяет оценить эффективность сайта и понять, на каком этапе теряются продажи, почему люди...

2024-04-17

Яндекс переходит на новую версию Wordstat

Часть старой функциональности уже недоступна. Обновленный Яндекс Вордстат полностью перейдет на основной домен wordstat.ru 20 марта. С...

2024-04-08

Яндекс интегрировал в свой облачный сервис эмпатичную нейросеть

Она умеет распознавать эмоции человека по голосу. Алгоритм-эмпат определяет характер речи пользователя: говорит он с явным негатив...

2024-04-08

Новая версия нейросети Claude превзошла по мощности аналоги Google и OpenAI

Скорость обработки сложных научных статей составляет менее трех секунд. Новое поколение моделей ИИ от компании Antropic под названием Cl...

2024-04-08

Как пользоваться GPT 4 и Claude бесплатно и без VPN

Как бесплатно пользоваться GPT 4, его плагинами, а также новой нейросетью Claude 3? Это можно делать в рамках одного сайта, который называет�...

2024-04-08

Как использовать анимацию с помощью JavaScript-библиотеки GSAP

Узнайте, как использовать мощную JavaScript-библиотеку GSAP для создания захватывающих анимаций на веб-страницах, освоив основы и примеры. GSAP...

2024-03-13

Использование GSAP 3 для веб-анимации

Использование GSAP 3 для веб-анимации От автора: Greensock Animation Platform (GSAP) — это простая в использовании библиотека JavaScript для веб-анимации....

2024-03-13