Как добавить базу данных? [Справочная информация по Виртуальному хостингу]

Основной ареал применения

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

Он функциональнее и производительнее, и уступает лишь по некоторым моментам MySQL. Задействовать MySQL стоит в той ситуации, когда работа предстоит с крупной корпоративной базой данных, в которой важно поддерживать целостность сведений.

Основные преимущества mysql

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

Преимущества следующие:

Что можно делать посредством базы данных

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

• о добавленных новых функциях. Еженедельно вебмастера добавляют массу различных функций, о которых они успешно забывают в скором времени. База данных позволит всегда быть в курсе нового функционала, упрощая текущую деятельность специалиста;
• о медиа-запросах, посредством которых сайт адаптируется под мобильные устройства;
• о выполненных транзакциях, средствах на счетах и комиссиях;
• о дизайн-макете;
• о страницах, которые были сформированы с помощью языка гипертекстовой разметки и каскадных таблиц стилей;
• о клиентах и посетителях, которые с определенной периодичностью посещают веб-проект. Сохраняются сведения о тех или иных действиях — совершенных покупках, полученных бонусах, скидках и многом другом;
• о письмах, полученных на электронную почту и отправленных на нее.

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

Основные инструменты для работы с mysql

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

Перенос файлов

Лучший способ через ssh при помощи rsync:

Импорт базы данных с компьютера на сервер.

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

Перенос БД на примере MySQL

На обоих машинах открываем порт сервера MySQL в мир:

[mysqld]
...
bind-address            = * #127.0.0.1
...

Я напишу пример как я делаю бекапы каждый день, вырезка из logrotate.d:

#!/bin/bash
CMD="mysqldump --defaults-file=/root/my.cnf --no-create-info=FALSE --order-by-primary=FALSE --force=FALSE --no-data=FALSE --tz-utc=TRUE --flush-privileges=FALSE --compress=TRUE --replace=FALSE --host=localhost --insert-ignore=FALSE --extended-insert=TRUE --quote-names=TRUE --hex-blob=FALSE --complete-insert=FALSE --add-locks=TRUE --port=3306 --disable-keys=TRUE --delayed-insert=FALSE --create-options=TRUE --delete-master-logs=FALSE --comments=TRUE --default-character-set=utf8 --max_allowed_packet=1G --flush-logs=FALSE --dump-date=TRUE --lock-tables=TRUE --allow-keyw
ords=FALSE --events=FALSE --databases --routines"

for i in $(mysql --defaults-file=/root/my.cnf --batch --skip-column-names -e 'SHOW DATABASES' | grep -v '^information_schema$'); do
	if [ ! -e /srv/dumps/$i ]; then mkdir -m 700 /srv/dumps/$i; fi
		$CMD $i | gzip -c > /srv/dumps/$i/$i.sql.gz
done

/root/my.cnf

Перенаправление всех соединений к localhost:3306 — старый сервер на новый сервер

Довольно просто, используем ssh tunnel (предварительно остановив mysql):

Putty

Из соображений безопасности на виртуальном хостинге не предоставляется возможности настройки SSH-туннелирования для соединения с базой данных. Для этого мы рекомендуем приобрести VPS или выделенный сервер.

Альтернативное по

Я обещал по ходу изучения MySQL знакомить вас с различным программным обеспечением для администрирования СУБД. Так вы сможете расширить свой «профессиональный» кругозор, и выбрать ту программу, которая больше всего подходит под ваши потребности и род деятельности.

Восстановление базы с помощью mysql

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

Второй вариант переноса, а точнее загрузки дампа через ssh.

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

mysql -uroot -proot_pass db_name < db_name.sql

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

Самым лучшим выходом из ситуации будет ипморт напрямую через cli MySQL :

• Заходим в MySQL:

  mysql -uLogin -pPassword
      

  (вместо Login и Password вводим данные для авторизации пользователя БД)

• Выбираем базу данных:

  use db_name
      

  (вместо db_name вводим название БД)

• Делаем импорт файла в выбранную бд:

  source db_name.sql
      

  (вместо db_name.sql вводим название файла дампа БД или путь до файла, если вы находидест не в той папке, где лежит файл)

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

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

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

———————

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

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

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

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

По этому пути usrlocalmysql-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 = 16Minnodb_additional_mem_pool_size = 2M — здесь меняем до нужного размера!#Устанавливайте .._log_file_size to 25 % от _buffer_pool_sizeinnodb_log_file_size = 5Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit=1innodb_lock_wait_timeout = 50

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

Загрузка базы данных mysql на хостинг

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

Загрузка данных с помощью 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

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

Запуск сервера субд

Многие пользователи не знают, что делать с экземпляром СУБД после его инсталляции на свой ПК. Тут главное запомнить (а лучше записать) путь, куда во время установки распаковываются файлы сервера.

Измените пароль в конфигурационном файле сайта

Не забудьте изменить пароль базы данных в настройках сайта: Где CMS хранит настройки подключения к базе данных.

Импорт базы данных mysql через консоль (замена существующей)

Для начала необходимо авторизоваться в командной строке MySQL:

Импорт дампа базы данных

Мы рекомендуем создать новую базу данных и выполнить в нее импорт дампа.

Импорт и создание базы данных mysql через консоль

Выше был описан способ импорта, при котором осуществляется замена таблиц в существующей базе данных. Но такой вариант не подойдёт, если БД изначально не существует. В таком случае потребуется сначала создать новую базу данных, а уже потом импортировать в неё таблицы из резервной копии.

Для начала необходимо авторизоваться в консоли MySQL:

Имя сервера

В качестве сервера базы данных необходимо указывать «localhost».

Как перенести базу данных mysql на хостинг spaceweb? — раздел помощи — spaceweb

Как перенести базу данных на виртуальный сервер (vps)

Процесс переноса баз данных на VPS аналогичен импорту БД на хостинг. Выполнить загрузку дампа базы данных в ISPmanager или непосредственно через FTP.

Иногда размер дампа не позволяет использовать web-интерфейс MySQL. В таком случае, импорт осуществляется с помощью SSH-доступа. Используя подключение SSH, ввести команду как в примере, приведенном ниже.

mysql -uпользователь -pпароль имя_бд < ourdbfile.sql

В данном примере:

• «пользователь» — наименование пользователя в БД;
• «пароль» — пароль вышеуказанного пользователя;
• «имя_бд» — наименование импортируемой БД (например, «test_db»);
• «ourdbfile.sql» — наименование файла импортируемой БД (в данном примере файл расположен в корневой папке хостинга).

После флагов «-u» и «-p» пробела быть не должно.

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

Как работать с mysql из скриптов на perl

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

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

Как работать с mysql из скриптов на php

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

Как связать с сайтом?

Связка происходит автоматически на этапе установки движка. Там вы указываете имя БД, имя пользователя и его пароль для доступа к базе, сервер (почти всегда localhost) и префикс таблиц. Но если вдруг в процессе работы что-то меняется, то нужно будет изменить данные.

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

В связи с этим, СРАЗУ же после внесения подобных изменений нужно изменять и соответствующие параметры. Если у вас движок wordpress, то они хранятся в файле wp-config. В частности, там есть такие константы:

А также переменная: $table_prefix;

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

Как управлять mysql базами на хостинге

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

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

Как это работает?

Как можно догадаться, перенос БД — это два основных действия, проводимые непосредственно над БД:

• создание резервной копии БД на исходном сервере;
• восстановление БД из резервной копии на целевом сервере.

По соображениям безопасности для БД MySQL нельзя просто скопировать бинарные файлы БД. Поэтому для таких целей предусмотрены инструменты экспорта/импорта, которые можно проводить при помощи специальных команд и/или утилит. Таковыми являются команды mysqldump и source соответственно.

Есть ещё один инструмент для переноса БД — утилита mysqldbcopy. Она позволяет буквально одной командой скопировать объекты БД с одного сервера на другой. Утилита mysqlbdcopy является более удобным вариантом, однако её рекомендуется использовать опытным пользователям и администраторам.

Как изменить версию mysql?

На виртуальном хостинге доступны следующие версии MySQL: — MySQL Version 5.7.23(mysql Ver 14.14 Distrib 5.7.23-24, for Linux (x86_64) using 6.0).

Как изменить пароль базы данных?

Важно: в ISPmanager подраздел «Базы данных» недоступен, если вы используете тариф «Host-Lite».

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

Готово, пароль базы данных изменён.

Как создать базу данных?

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

Какие данные необходимо использовать для удалённого подключения?

Для удалённого соединения с базой данных (БД) и доступа к MySQL необходимо указывать следующие данные:

Команда mysqldump – резервирование бд

Данная команда обычно доступна в пакете mysql-client. Конечно, в зависимости от используемого дистрибутива Linux наименование пакета может несколько отличаться. У команды mysqldump синтаксис несложный и выглядит следующим образом:

musqldump [options] [db_name [tbl_name …]]

На хостинг-площадках команда mysqldump очень часто используется для автоматического (по расписанию) резервирования БД пользователей. Именно такие резервные копии БД и можно использовать для переноса (восстановления/импорта) на другой хостинг.

Команда mysqldump располагает внушительным арсеналом опций, это довольно универсальная команда. Ознакомиться более подробно можно на страницах интерактивного руководства, используя команду man mysqldump. Для того, чтобы сделать дамп БД (т. е. зафиксировать содержимое и состояние её таблиц в текстовом файле) нужно выполнить команду:

$ mysqldump -u john -p dbname > ~/backups/dbname_back-$(date  %d%m%y).dump

В результате будет создан файл dbname_back-130319.dump в подкаталоге backups домашней директории текущего пользователя. Ключи -u и -p служат для задания имени и пароля пользователя-клиента MySQL соответственно. Пароль непосредственно в команде mysqldump указывать не стоит — она автоматически предложит его ввести в процессе выполнения.

В качестве dbname выступает имя требуемой БД. Конструкция $(date %d%m%y) в имени файла позволяет задействовать команду date непосредственно в команде mysqldump для присутствия в имени файла даты создания дампа БД. Символ «>» используется для перенаправления стандартного потока STD_OUT (т. е. результата) команды mysqldump непосредственно в файл.На этом, собственно, и всё — дамп БД создан. Теперь его можно использовать для восстановления на целевом сервере.

Команда source mysql – восстановление резервной копии бд

Команда source является внутренней инструкцией языка SQL, в отличии от команды mysqldump, которая реализует возможности отдельной одноимённой утилиты. С помощью команды source выполняется импорт таблиц из дампа в заранее подготовленную БД. Таким образом, для восстановления на целевом сервере требуемой ДБ необходимо:

• создать пустую БД;
• выбрать созданную БД для использования;
• аполнить выбранную БД содержимым из файла-дампа, сознанного ранее командой mysqldump.

Следует заметить, что все вышеперечисленные действия выполняются средствами языка запросов SQL (MySQL). Итак, первым делом необходимо авторизоваться на сервере MySQL:

Минусы системы управления базами данных

Увы, но их имеет любое ПО, в том числе и самое современное. Что касается недостатков MySQL, то они следующие:

• Данные обновляются каскадным образом. Это неудобно, так как при удалении из базы данных сведений о пользователе или клиенте, тут же удаляются все записи о нем;
  Объединение нескольких запросов в один или конструкция Union;
• Отсутствие вложенных запросов. Чтобы получить ответ на какой-либо запрос, пользователю приходится собственноручно вводить его, а это долго и не очень практично, особенно если этих самых запросов множество;
• Невозможность исполнения нескольких запросов в одной транзакции. В целом этот минус не портит положительное представление о ПО, но было бы неплохо, если бы над ним поработали разработчики.

Может ли быть несколько баз на хостинге?

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

В общем, все это основы основ работы с БД, если вдруг вы захотите изучить работу с MySQL (а это язык, с помощью которого работают с базами даных) от А до Я, то у нас для вас есть замечательный курс, продолжительностью более 20-ти часов, в котором вы сможете освоить эту технологию раз и навсегда.

Ограниченные ресурсы сервера

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

Решение проблемы:

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

Перенос всего mysql

Прежде всего, для понимания всего процесса «телепортации» давайте разберемся, что такое сервер MySQL. Он представляет собой коллекцию БД, размещенных на одном дисковом пространстве и объединенных между собой определенными настройками доступа к ним.

Другими словами, сервер БД – это все базы, для которых заданы определенные привилегии конкретных пользователей, которые обладают этими правами лишь в масштабах данного «объединения».

То есть пользователь, который обладает правами администратора на текущем сервере не сможет реализовать свои привилегии на другом. Получается, что сервер – это самостоятельная и самая высшая единица иерархии в MySQL.

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

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

Перенос данных на сервер хостинга

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

Работа с дампом из командной строки

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

Для создания дампа базы данных выполните команду:

Создание бд через панель управления сервером

Пожалуй, это самый простой вариант. Любой хостинг предоставляет вам Cpanel или любую другую панель, чтобы управлять вашими сайтами. Там вы можете найти пункт “Базы данных”, где можно в визуальном режиме создать новую БД, нового пользователя, после чего связать его с базой.

Создание дампа базы mysql через консоль

Хочу в самом начале сделать небольшое уточнение.

Создание дампа на старом хостинге

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

Обратитесь с справочную службу вашего старого хостинга.

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

Общий вид — putty

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

Удалённый доступ к базе данных mysql

Для активации удалённого соединения с базой данных войдите в вашу панель управления хостингом, и следуйте соответствующей инструкции ниже:

Экспорт базы данных.

Делаем экспорт посредством удобного mysqldump :

mysqldump -uLogin -pPassword db_name > db_name.sql

Экспорт данных из mysql в excel и csv файлы

Я не зря объединил информацию о выводе информации из MySQL в эти два формата в одном блоке, т.к. они очень похожи, используются примерно одинаково (для структурирования информации в виде таблиц) и команды для экспорта будут вызываться одни и те же.

Как известно, единственным существенным различием между данными форматами является то, что расширение xls и xlsx имеют файлы, создаваемые в программе Microsoft Office Excel, которая работает только под Windows, а csv файлы являются более универсальными и операции с ними возможны во многих редакторах.

Это не значит, что xls нигде, кроме Microsoft Office Excel, не откроется. Тот же OpenOffice подтверждает обратное.

Но для такой возможности данная поддержка должна присутствовать в программном продукте. csv же файлы доступны для чтения даже в обыкновенном текстовом редакторе типа «Блокнот», только такая форма будет не совсем читабельной.

Начну с того, что экспортировать в xls или csv можно только результаты SQL запросов, работать с которыми мы с вами научились ранее, т.к. всю базу данных в один файл будет невозможно вывести за одну операцию.

Во-первых, это не оптимально, т.к. такой файл вряд ли откроется при больших объёмах хранящейся в БД информации. А, во-вторых, непонятно, как разбивать внутри файла информацию по таблицам и полям.

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

Если вы вдруг знаете, как можно экспортировать информацию из всей MySQL БД сразу в один или несколько xls файлов в консоли, то напишите об этом в комментариях. Думаю, почитать об этом будет полезно многим.

Итак, если мы говорим о том, как сделать экспорт данных из MySQL в xls и csv, то сделать это можно прямо в консоли сервера через утилиту mysql либо в командной строке MySQL, работой с которой я знакомил вас в предыдущей своей статье.

Начнём по порядку.

Экспортировать данные из MySQL базы данных в csv и xls форматы прямо в консоли сервера можно следующими командами.

На Linux системах:

mysql -u имя_пользователя -d имя_базы_данных -p -e "SELECT * FROM таблица_БД;" | sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" > путь_и_имя_файла.csv

В принципе, при крайней необходимости можете сделать данной командой и экспорт данных MySQL в Excel файл. Но я, если честно, на практике данным не занимался и что выйдет в итоге — без понятия, т.к. работаю сейчас под Windows. Если будете пользоваться данной командой под Linux — напишите в комментариях, пожалуйста, о результатах вашей работы. Думаю, информация будет интересна всем.

На Windows:

Экспорт данных из MySQL таблиц в csv приведённой выше командой в данном случае, к сожалению, не удастся, т.к. у Windows, в отличие от Linux, нет встроенной консольной команды для работы с потоками, какой является sed в Linux.

Установить её, конечно, можно, но слишком много хлопот. Ещё, как вариант, можете использовать CygWin — эмулятор консоли Linux для Windows систем.

Хорошо, если она у вас уже установлена. В противном случае экспорт данных из БД MySQL выбранным способом принесёт нам слишком много хлопот.

Зато извлечение информации в xls файл прост как 5 копеек 🙂 Запустить его очень просто следующим способом, который я опробовал лично:

mysql -u имя_пользователя -d имя_базы_данных -p -e "SELECT * FROM таблица_БД;" > путь_и_имя_файла.xls

Открылся данный файл в Microsoft Office Excel вообще без всяких проблем. Единственное, при открытии на экран вывелось сообщение с предупреждением о том, что действительный формат открываемого файла отличается от указываемого его расширения.

Но при подтверждении действия документ открылся без трудностей — вся информация была разбита по ячейкам в том виде, в каком она хранилась в самой таблице.

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

Если у вас в процессе использования экспортированного из MySQL xls файла возникнут какие-то проблемы либо в данной программе, либо в других, то дайте мне знать, пожалуйста, в комментариях.

Описанным выше путём можно экспортировать содержимое БД MySQL и в csv файл, в принципе. Но тогда данные из разных полей таблицы будут записаны скопом, без разделителей, что может плохо отображаться в различных программах для работы с таблицами, в которых обычно с csv файлами и работают.

OpenOffice, кстати, всё равно 🙂 Он автоматически разграничил информацию, полученную способом, которым мы экспортировали содержимое базы MySQL в xls. Не знаю, как он это делает — но рекомендую пользоваться 🙂

Ну, а тот же самый Microsoft Office Excel отобразил всю информацию, соответствующую одной записи в таблице, записав её в одну ячейку без каких-либо разделителей. Думаю, что подобным образом поступят и другие редакторы таблиц.

Поэтому при экспорте базы данных MySQL в csv файлы нужно делать это, разделяя информацию специальными символами, воспринимаемыми редакторами.

И тут я плавно подошёл ко второму способу экспорта MySQL данных в csv и xls, который заключается в использовании командной строки MySQL.

Итак, для того, чтобы экспортировать данные MySQL в csv файл данным способом, нам нужна следующая команда:

SELECT * FROM таблица_базы_данных
INTO OUTFILE 'путь_и_имя_файла.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY 'n';

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

Данная команда также отлично подходит и для экспорта данных MySQL в xls файл для корректного отображения в Microsoft Office Excel. Только в этом случае разделители нам не нужны, т.к. они будут мешать в разбиении информации по ячейкам:

SELECT * FROM таблица_базы_данных INTO OUTFILE 'путь_и_имя_файла.xls';

Однако, на практике не всё так просто, как я описал. Во время выполнения команды вы можете столкнутся со следующей ошибкой в консоли, препятствующей выполнению экспорта:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Она вызвана тем, что ваш MySQL сервер был запущен с опцией —secure-file-priv. Лично я столкнулся с данной проблемой из-за того, что для работы в консоли пользуюсь дистрибутивом MySQL, входящим в комплект WAMP OpenServer, который, в свою очередь запускает MySQL сервер данным образом.

Здесь есть два способа решения проблемы:

• Изменить параметры запуска MySQL сервера
• Изменить путь к конечному файлу экспорта MySQL

Первый способ мне показался слишком сложным, т.к. пришлось бы копаться в конфигурации OpenServer, которая не мною была написана со всеми вытекающими из этого обстоятельствами 🙂 Поэтому я решил пойти вторым путём. Если вы столкнулись с подобной проблемой — повторяйте за мной.

Сперва нужно зайти в командную строку MySQL и выполнить одну из следующих команд:

SHOW VARIABLES LIKE "secure_file_priv";
SELECT @@GLOBAL.secure_file_priv;

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

Т.е. при использовании команд LOAD DATA и SELECT … INTO OUTFILE экспортируемые и импортируемые файлы могут располагаться только внутри данного каталога.

В моём случае у данной переменной было вообще установлено значение NULL, т.к. я, как уже говорил, пользуюсь для работы в консоли утилитами MySQL из дистрибутива, входящего в OpenServer. Данное значение указывало на то, что операции экспорта данных MySQL и импорта с помощью указанных команд были вообще закрыты.

Как потом выяснилось, это распространённая ситуация в случае использования коробочных WAMP и MAMP серверов.

К сожалению, привычными методами изменения значений глобальных переменных MySQL в моём случае воспользоваться не удалось:

SET имя_переменной = значение;

В результате я увидел в консоли лишь следующую ошибку:

ERROR 1238 (HY000) at line 1: Variable 'secure_file_priv' is a read only variable</strong>.

В итоге, чтобы изменить значение переменной secure_file_priv и открыть операции экспорта и импорта, мне потребовалось зайти в файл конфигурации MySQL mysql.ini, который расположен в корневой директории дистрибутива MySQL, или к нему можно получить доступ иным способом, если MySQL входит в комплект вашего WAMP/LAMP/MAMP сборки сервера.

Вам, кстати, если захотите изменить путь к буферному каталогу обмена файлами, нужно будет сделать то же самое.

В моём случае в конфиге данная переменная уже существовала, только в закомментированном виде:

Создание бэкапов и вывод данных из mysql базы с помощью запросов

О том, как сделать дамп базы данных MySQL — одной и нескольких, а также отдельных их таблиц, мы поговорили. Но иногда на практике бывают случаи, когда нужно экспортировать набор данных, не ограниченный одной таблицей. Либо нужно выбрать лишь некоторые данные из таблицы.

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

Для бэкапа нам понадобится всё та же утилита mysqldump, которую нужно будет вызвать в таком виде:

mysqldump -u имя_пользователя -p имя_базы_данных имя_таблицы --where "уточняющий_запрос" > путь_и_имя_файла_дампа

В результате мы получим файл с SQL командами для создания таблицы со всей его структурой, которая после создания будет наполняться информацией, выбираемой с помощью уточняющего запроса.

Если нам будет нужно просто получить данные, хранящиеся в одной или нескольких таблицах, то нам потребуется модификация команды, используемой в предыдущем случае при выборке всех данных таблицы, только с некоторыми уточнениями:

mysql -u имя_пользователя -p -e "SELECT * FROM имя_таблицы WHERE уточняющий_запрос" > путь_и_имя_файла

Как вы понимаете, помимо различных уточнений, указываемых в запросе с помощью директивы WHERE, можно использовать и прочие SQL конструкции: JOIN, UNION и т.д.

Статистику собрать получится какую угодно 🙂

То же самое действие возможно произвести также при работе в командной строке MySQL с помощью следующей команды:

SELECT * FROM таблица_базы_данных WHERE уточняющий_запрос INTO OUTFILE 'путь_и_имя_файла';

Данная команда как раз и предназначена для создания файлов с результатами выборки. Причём, результаты можно не только экспортировать в файлы, но и записывать в переменные, а также выводимые данные можно форматировать различными способами.

Заключение

Как видим перенос базы MySql не представляет большой сложности. Следует отметить, что в случае с очень большими БД, перенос может занимать длительное время.Например, на перенос БД объёмом 200 Гб может потребоваться приблизительно неделя. В таких случаях обычно перенос осуществляют по частям, отдельными таблицами, или использовать другие инструменты переноса.