Помощь

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

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

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

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

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

Если пароль был утерян, вы можете самостоятельно изменить его в панели управления, в разделе "Базы данных".

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

Опцию --quick рекомендуется использовать, если база данных 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 --quick --opt -uимя_пользователя -p -hимя_сервера_БД --add-drop-table имя_базы > dump.sql

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

--max_allowed_packet=1M

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

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 в необходимое значение, либо не использовать его вообще.

Для корректного переноса баз в кодировке отличной от cp1251 добавьте опцию

--default-character-set=utf8

Если на сервере, с которого вы переносите базу, Вам недоступен ssh, воспользуйтесь cgi скриптом:

#!/bin/sh 

DB=db 
USER=user 
PASS=pass 
HOST=localhost 
TODAY=`/bin/date '+%Y-%m-%d_%H-%M-%S'` 

echo "Content-Type: text/plain" 
echo "Content-Disposition: attachment; filename=$DB.$TODAY.sql" 
echo 
   
mysqldump --opt --quick --max_allowed_packet=1M --default-character-set=utf8 -u "$USER" -p"$PASS" -h $HOST $DB 

Замените db, user и pass на свои значения, закачайте скрипт в cgi-bin директорию своего сайта под именем dump.cgi, установите на файл права 755 и откройте в браузере http://site.tld/cgi-bin/dump.cgi

Браузер предложит сохранить дамп с именем db.2012-01-01.sql

В качестве Hostname указывайте aNNNNN.mysql.mchost.ru, где aNNNNN - ваш логин в панель управления. Логин и пароль для доступа к базе назначает сам пользователь через панель управления.

Да, но таблицы такого типа не будут попадать в бэкап.

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

# соединяемся с сервером и выбираем свою базу данных
mysql_connect("aNNNNN.mysql.mchost.ru", 
"aNNNNN","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 из Perl-скриптов как правило используют модуль DBI4. Это позволяет удобно открывать и закрывать соединения к СУБД, подготавливать и выполнять запросы к базе, обрабатывать ошибки и так далее. Если вы не имеете опыта работы с MySQL из Perl.

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

#!/usr/bin/perl
        
use DBI;
        
my $host = "aNNNNN.mysql.mchost.ru";  
# вымышленный MySQL-сервер
my $port = "3306";  
# порт, на который открываем соединение
my $user = "aNNNNN";  
# имя пользователя (вымышленное)
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.

Используем ПО: Putty

рис.1

Первоначальные установки удаленного соединения:

* Host Name: ftp.ваш_домен — Хост для соединения по SSH, где ваш_домен — имя вашего домена.

* Port: 22

* Protocol: SSH

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

* Source Port: 3306 — порт для локального подключения после установки туннеля.

* Destination: Local — указываем явным образом, что подключение будет осуществляться на локальный порт, указанный выше.

* Destination: aXXXX.mysql.mchost.ru:3306 — хост, с которым будет осуществляться туннелирование по определенному порту (3306 — стандартно для MySQL).

рис. 2

Теперь необходимо добавить настройки в меню Forwarded ports нажатием кнопки «Add» как показано на рисунке ниже, для достижения следующего результата:

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

рис.3

Настройка закончена, переходим в первое меню «Session», как на рисунке 1, и нажимаем кнопку «Open» для установки соединения с удаленным сервером по протоколу SSH. Проходим авторизацию по логину и паролю, и попадаем в bash, находясь локально на вашей площадке /home/httpd/vhosts/ваш_домен/. Тем временем, согласно нашим настройкам, происходит туннелирование соединения c удаленным MySQL сервером, используя SSH протокол и внутренние средства Putty. Таким образом, соединяясь на удаленный порт aXXXX.mysql.mchost.ru:3306 и используя любое ПО для работы с MySQL сервером, мы получаем доступ к администрированию удаленной базы данных, одновременно защищая свое соединение по протоколу SSH.

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

Если Вы точно уверены, что использование MySQL в Вашем случае оправдано - тогда следует перейти к оптимизации запросов. Прежде всего следует понимать, что наибольшая нагрузка создается при получении большого числа элементов из таблицы, поэтому основную часть задач следует перекладывать на PHP или Perl скрипты, не забывая создавать минимально требовательные запросы с максимальным количеством уточняющих условий. Например, Вам надо получить из таблицы элемент со значением параметра 'N' равным '5', для этого следует использовать запрос "SELECT * FROM table WHERE N=5;", а не использовать запрос "SELECT * FROM table;" и после этого в цикле сравнивать значения 'N'. SQL-запрос должен быть максимально чётким и узким, когда это позволяет специфика сайта.

Далее попробуем углубиться в анализирование запросов. В MySQL существует оператор EXPLAIN, он служит для получения получение информации о SELECT-запросе. Если оператору SELECT предшествует EXPLAIN, то MySQL сообщит о том, как будет производиться обработка SELECT, и предоставит информацию о порядке и методе связывания таблиц. При помощи EXPLAIN можно выяснить, когда стоит снабдить таблицы индексами, чтобы получить более быструю выборку, использующую индексы для поиска записей. Используя EXPLAIN перед сложными запросами, охватывающими несколько таблиц, можно получить полную информацию об обработке запроса SELECT и, проанализировав данные, изменить таблицы для ускорения быстродействия. Подробно на примере оптимизация таблиц рассматривается по ссылке, приведенной выше.

Также следует обратить внимание на INSERT, UPDATE и DELETE запросы. Не смотря на относительную редкость (по сравнению с SELECT) их использования, они также могут создавать высокую нагрузку на сервер. UPDATE, по сути, включает в себя запрос SELECT и INSERT, поэтому к нему актуальны те же методы оптимизации. Запрос DELETE в оптимизации, как таковой, не нуждается, можно лишь отметить тот факт, что для очищения таблицы целиком следует использовать оператор TRUNCATE TABLE, так как уничтожение каждого элемента по отдельности займет много времени.

Не надо обходить стороной вопрос структуры самих таблиц. Например, следует избегать типов VARCHAR или BLOB для всех столбцов. При использовании единичного столбца VARCHAR или BLOB Вы получите динамическую длину строки.

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