Як експортувати дані до mysql. Як імпортувати велику базу даних MySQL в обхід обмежень phpMyAdmin

Ця процедура передбачає перенесення даних з однієї бази даних (А) до іншої (Б). Як правило, база даних Б знаходиться на хостингу (або на Денвері), а база даних А, знаходиться на комп'ютері користувача і являє собою файл з розширенням sql. У бази даних А є ще одна назва – Dump.

Як імпортувати базу даних?

Імпорт бази даних MySQL за допомогою SSH

Цей спосіб використовується досить рідко, але його опишемо. Для початку залийте БД, з якої буде вироблено імпорт на сервер хостинг-провайдера, де зберігаються файли вашого сайту. Далі для імпорту бази даних використовується команда:

mysql -uUSERNAME -pUSERPASSWORD DBNAME< DUMPFILENAME.sql

mysql --user=USERNAME --password=USERPASSWORD DBNAME< DUMPFILENAME.sql

Замість слів написаних великими літерами підставляємо:
USERNAME - ім'я користувача бази даних, наприклад uhosting_databaseuser;

USERPASSWORD – пароль користувача БД, наприклад Rjkweuik12;

DBNAME - ім'я бази даних, в яку буде імпорт, наприклад uhosting_databasename

DUMPFILENAME - ім'я dump-файлу, з якого буде виконано імпорт даних. Тут ще потрібно вказати шлях до бази даних, яку ми заливали на сервер хостинг-провайдера.

Якщо ви зробили резервну копіюабо експортували базу даних у файл SQL, то можна імпортувати її в одну з баз даних MySQLвашого облікового запису хостингу через phpMyAdmin.

Примітка.У базі даних MySQL не повинно бути рядка CREATE DATABASE (СТВОРИТИ БАЗУ ДАНИХ). В інакшеімпорт може завершитися невдачею.

Інтерфейс phpMyAdmin дозволяє одночасно імпортувати 8 МБ даних. Якщо вам потрібно імпортувати більший обсяг файлу, розбийте його на кілька частин по 8 МБ.

Увага!Якщо ви імпортуєте базу даних для керованого хостингу WordPress, щоб ваш сайт працював без збоїв.

Імпорт файлів SQL у бази даних MySQL за допомогою phpMyAdmin

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

Примітка.Якщо з'явиться повідомлення про помилку Script timeout passed, якщо ви збираєтеся завантажити, ви можете відразу ж вибрати той самий файл і відновити процес.

Вітаю вас, друзі! 🙂

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

У статті я розповім про те, як зробити дамп бази MySQL, а також проводити вивантаження даних з MySQL Excel файлта csv формат.

Ми Роздивимось різні варіантивибірки інформації з: створення дампа однієї і кількох БД, експорт даних з окремих таблиць і результатів довільних SELECTзапитів.

А також поговоримо про те, як зробити висновок даних з бази MySQL в консолі сервера і командному рядку MySQL.

У цій статті я не розповідатиму про те, як експортувати дані за допомогою phpMyAdmin та інших візуальних інструментів.

По-перше, тому що на цю тему вже достатньо матеріалу в мережі. Причому якісного матеріалу, який я не горю бажанням копіпастити.

А, по-друге, я вже коротко сам розглядав процес виведення інформації з MySQL бази в SQL файл в одній зі своїх статей, де розповідав про .

Так що, якщо ви не є професійним розробником або системним адміністратором, Яким може бути корисна інформація про роботу з консоллю, і ви прийшли лише за інструкцією з експорту бази в phpMyAdmin, то можете обмежитися читанням інформації за посиланням вище.

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

На цьому вступна частина закінчена і ми переходимо до огляду консольних команд для створення дампа бази MySQL, які я вирішив розсортувати за обсягом даних, що зберігаються: починаючи від експорту всієї БД, закінчуючи окремими таблицями і результатами довільних запитів.

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

Хочу на самому початку зробити невелике уточнення.

Дамп бази- це файл з набором SQLкоманд, який при запуску дозволяє створювати бази даних та таблиці, а також наповнювати їх інформацією. Дамп потрібен тим, хто хоче завантажити базу даних MySQL з метою копіювання її на інший сервер або в рамках існуючого.

Також, якщо хтось не в курсі, бекап бази даних MySQL - це, по суті, її дамп, зроблений у певний період часу, що дозволяє відновити структуру та дані бази за потреби.

Експорт даних- це просто вилучення інформації з таблиць у текстовому вигляді для подальшої роботи з текстовим або графічним редакторами.

Отже, команди для цих дій будуть трохи різні.

Для створення дампа БД MySQL має вбудовану утиліту під назвою mysqldump, яку потрібно використовувати за межами командного рядка MySQL у консолі сервера або іншій оболонці.

Отже, для найпростішого і найпоширенішого варіанта — експорту даних конкретної БД в консолі MySQL для перенесення її на інший сервер або внутрішнього копіювання потрібно виконати таку команду:

Mysqldump -u ім'я_користувача -p ім'я_бази_даних > шлях_і_ім'я_файлу_дампа

Дана утилітаможе створювати дампи бази MySQL тільки у вигляді файлів з SQL командами, тому яке б ви розширення для свого файлу не вибрали - у будь-якому випадку його вміст буде однаковим. І не забудьте перед експортом інформації з MySQL перевірити права на запис у директорії, в якій він розташовуватиметься, щоб створення файлу було можливим.

Якщо раптом вам потрібно буде зробити дамп з усіма базами даних на сервері, використовуйте наступний варіант команди:

Mysqldump -u ім'я_користувача -p --all-databases > шлях_і_ім'я_файлу_дампа

Для створення дампа лише для кількох конкретних баз даних вам знадобиться виклик mysqldump із такими параметрами:

Mysqldump -u ім'я_користувача -p --databases ім'я_бази_даних1, ім'я_бази_даних2, ... > шлях_і_ім'я_файлу_дампу

У результаті, у кожному випадку ви отримаєте дамп бази MySQL, що містить команди для створення структури таблиць, що містяться (поля, їх типи, індекси, ключі і т.д.), а також операції наповнення їх даними.

Даний варіант годиться тільки для відновлення та копіювання баз даних повністю.

Про те, як робити бекапи певних таблиць MySQL та отримувати їх дані в читаному вигляді, йтиметься далі.

Робимо дамп таблиці MySQL та експорт даних

Для створення дампа певних таблиць MySQL бази даних нам знадобиться все та ж утиліта mysqldump, що викликається з наступними параметрами:

Mysqldump -u ім'я_користувача -p ім'я_бази_даних ім'я_таблиці1, ім'я_таблиці2, ... > шлях_і_ім'я_файла_дампа

Ще при викликі mysqldump можна вказувати необхідні таблиці як значення параметра -tables, при використанні якого параметр -databasesігноруватиметься:

Mysqldump -u ім'я_користувача -p --databases ім'я_бази_даних1, ім'я_бази_даних2 --tables ім'я_таблиці1, ім'я_таблиці2, ... > шлях_і_ім'я_файла_дампа

Наведений приклад виведе на екран таку помилку:

Mysqldump: Got error: 1049: Unknown database "ім'я_бази_даних1," when selecting the database

Як бачите, буде використовуватися лише остання БД зі списку вказаних. У принципі, така поведінка є цілком логічною, т.к. у всіх БД зазначених таблиць може бути.

Добре, дамп таблиці MySQL бази даних ми отримали. Його можна використовувати для відновлення або копіювання разом зі структурою.

А що, якщо потрібно отримати інформацію, що зберігається в них, і, бажано, в читаному вигляді, щоб можна було її відправити менеджеру і переглянути в звичайному текстовому або табличному редакторі? MySQL має кошти і для цього.

Досягти задуманого нам допоможе варіант виклику утиліти mysqlз консолі з певними параметрами:

Mysql -u ім'я_користувача -p ім'я_бази_даних -e "SELECT * FROM ім'я_таблиці"

Ця команда дозволить нам виконати запит до необхідної БД та вивести результат у консоль, не заходячи до командного рядка MySQL.

Ну а для того, щоб не виводити дані в консоль, а записати їх у файл, потрібно доповнити команду наступним чином:

Mysql -u ім'я_користувача -p -e "SELECT * FROM ім'я_таблиці" > шлях_і_ім'я_файлу

Завдяки даним конструкціям ми можемо не лише отримати дані, що зберігаються у всіх полях таблиці, а й у конкретних. Для цього достатньо замість символу wildcards (*) прописати через кому необхідні.

В результаті у нас на виході вийде звичайний текстовий файл, який міститиме назви полів у вигляді шапки та інформацію щодо них для всіх записів. Його можна відкрити у звичайному текстовому редакторі, незалежно від того, який дозвіл ви задасте при створенні.

Якщо ж захочете експортувати дані з бази MySQL в xls або csv форматі, щоб отриманий файл коректно відображався в табличних редакторів, то про те, як це зробити буде розказано трохи пізніше 🙂

Створення бекапів та виведення даних з 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 "шлях_і_ім'я_файлу";

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

Якщо перераховане – ваш випадок, то з повним списком параметрів та варіантів виклику даної команди ви можете ознайомитись тут – https://dev.mysql.com/doc/refman/5.7/en/select-into.html

На завершення свого короткого екскурсупо mysqldump хочу навести варіант виклику команди зі списком параметрів для створення оптимізованого дампа бази MySQL та таблиць, відновлення БД та окремих таблиць з якого займатиме менше часу, ніж при звичайному виклику:

Заради експерименту я використав даний варіантдля того, щоб зробити дамп бази MySQL розміром 143 Мб. Наступне відновлення зайняло 59 секунд часу проти 1 хвилини 3 секунд, коли БД відновлювалася з дампа, зробленого викликом mysqldump без спеціальних параметрів.

Згоден, що це дрібниця. Але це лише у разі цього обсягу даних. Якщо використовувати цю методику при створенні дампа розміром більше 1Гб, то різниця буде більшою.

Якщо ви зіткнетеся з такою ситуацією, то не забудьте ще запакувати дамп бази MySQL в архів. Найкраще tar.gz. Тоді відновлення займе ще менше часу.

Експорт даних з 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 бази даних в 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): MySQL Server є керуванням з --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" read only variable.

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

Вам, до речі, якщо захочете змінити шлях до буферного каталогу обміну файлами, потрібно буде зробити те саме.

У моєму випадку в конфізі ця змінна вже існувала, тільки в закоментованому вигляді:

Secure-file-priv = "%dprogdir%\userdata\temp"

Якщо у вас її не буде, то пропишіть її з нуля у секції (принаймні, у мене вона була там).

Я її розкоментував і вирішив використати у тому вигляді, в якому вона була прописана. Тобто. при експорті даних з MySQL та їх імпорті назад файли у мене тепер зберігатимуться в каталозі c:\openserver\userdata\temp\.

Після зміни конфіга (будь-якого, до речі), не забувайте перезапускати ваш сервер або окрему службу, налаштування якої ви правили, якщо є така можливість, щоб зміни набули чинності!

Для впевненості, після перезапуску сервера MySQL ще раз виводимо на екран змінну secure_file_privі копіюємо її значення буфер обміну.

А тепер нам потрібно викликати команду, що і на початку, тільки перед назвою файлу, в який буде зберігатися інформація з БД MySQL, прописати шлях, що зберігається в змінній змінній в наступному вигляді:

SELECT * FROM таблиця_бази_даних INTO OUTFILE "значення_secure_file_priv\ім'я_файлу.csv";

Після цього експорт даних з MySQL у разі запрацював.

Важливий момент! Якщо ви працюєте з MySQL під Windows, то не забувайте при вказівці шляху до файлу поміняти "\" на "/", інакше помилка з -secure-file-privвсе одно продовжить виводитися.

У цьому стаття у тому, як зробити дамп бази MySQL та її таблиць, і навіть зробити висновок даних з MySQL таблиць у різні формати, добігає кінця. Пишіть у коментарях свої відгуки та ділитесь з усіма своїми варіантами скриптів, якими ви користуєтеся на практиці найчастіше.

Якщо стаття вам сподобалася — ви можете подякувати автору репостом статті в соціальні мережіабо фінансово за допомогою форми нижче, щоб мати можливість елементарної оплати хостингу.

Всім удачі та до нових зустрічей! 🙂

P.S.: якщо вам потрібен сайт або необхідно внести редагування на існуючий, але для цього немає часу та бажання, можу запропонувати свої послуги.

Понад 5 років досвіду професійної розробкисайтів. Робота з PHP

Доброго дня друзі, сьогодні ми з Вами навчимося робити. Для чого це потрібно, можете поставити запитання Ви. Насамперед експорт бази данихнеобхідно робити періодично для того, щоб в екстрених ситуаціях Ви не втратили важливу для Вас інформацію. Експорт буде невеликим файлом, в якому зберігатиметься вся інформація про БД. Для експорту БДнеобхідно зайти в PHPMyAdmin і натиснути на БД, що Вас цікавить. Після цього ви побачите всі таблиці, що знаходяться в ній, і, не заходячи в них, клацніть на кнопку меню під назвою експорт. Перед Вами з'явиться наступна сторінка:

Я раджу Вам обрати швидкий спосібекспорту, а також у форматі вказати SQL. Після цього ви можете натиснути бл. Перед Вами з'явиться вікно, в якому Вам запропонують зберегти файл.

Ви зберігаєте файл у потрібному для Вас місці, головне пам'ятайте, куди Ви його зберегли, адже він дуже важливий для нас.
Що стосується звичайного способуекспорту. Ви можете також його використовувати, якщо Вам це необхідно, там є безліч додаткових налаштувань, які Ви можете встановити при експорті. Наприклад, вибрати потрібні таблиці з БД, вказати кодування, та багато іншого. Якщо Вам цікава дана настройка, Ви можете її переглянути. Але ми з Вами не заглиблюватимемося в дану настройку.
Після того як ви збережете файл у себе на комп'ютері, я попрошу Вас видалити БД. Як це зробити я не буду Вам пояснювати, тому що ми вже це проходили. Не бійтеся видаляти, ми повернемо з Вами на свої місця.
Настав час зайнятися імпортом бази даних. Переходимо у меню імпорт.

Вибираємо огляд вашого комп'ютера та вказуємо шлях до нашого файлу. І натискаємо бл. Як бачите, перед Вами постала помилка. Не лякайтеся, вся справа в тому, що ми з Вами імпортували не саму базу даних, а лише її таблиці. Тому створіть спочатку БД, зайдіть у неї та натисніть кнопку імпорт, проробивши все вказане вище. Натиснувши кнопку ок, у Вас все вийде, і якщо Ви все зробили правильно, помилки не з'явиться.

Як бачите, наша таблиця знову з'явилася на своєму місці, а також усі дані в ній були збережені. Тепер Ви розумієте, яка чудова нагода експорт та імпорт бази даних у PHPMyAdmin. Адже якщо Ви втратите в один день усі ваші напрацювання за довгі роки, завдяки даному файлуВи зможете все повернути. На цьому я з Вами прощаюсь до найшвидшого.

Доброго часу доби, колеги 🙂

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

Я вже написав статті про те, як виробляти основні дії з даними MySQL через консоль ним робити бекап бази MySQL, а також експорт інформації, що зберігається в ній.

Логічним продовженням цієї розповіді буде відновлення БД та інформації, що зберігається в ній, за допомогою операцій імпорту бази даних MySQL. І, що немаловажно, ми продовжимо робити це за допомогою інструменту всіх знівечених розробників через консоль.

Якщо вам потрібна інструкція з імпорту бази через phpMyAdmin, то ви можете знайти її в статті про . У цій статті я не маю бажанням описувати її ще раз, тим більше, що сьогоднішній матеріал буде присвячений виключно імпорту бази MySQL через консоль.

Але, перед тим, як ми приступимо до огляду методів і інструментів, кілька слів про те, що таке імпорт бази даних MySQL, яким він буває і як його краще робити?

Імпорт бази даних MySQL: що й навіщо?

Імпорт бази даних MySQL - це операція, коли відбувається наповнення бази інформацією. При цьому джерелом даних є файл дампа - зліпок іншої бази, що автоматично створений при операції експорту, або спеціально підготовлений SQL скрипт.

Імпорт, як і експорт БД MySQL, буває двох видів інформації, що зберігається на базі:

1. структури основи, її таблиць і які у них даних (у народі іменованих дампом БД);
2. просто даних, які у таблиці чи зібраних з допомогою SELECTзапитів.

У цій статті будуть розглянуті обидва варіанти.

Для відновлення з дампа MySQLбази даних з її структурою і всієї збереженої інформацією, як було зазначено, необхідний файл дампа БД, який представляє собою текстовий файл з будь-яким розширенням (попередньо може бути запакований в архів для зменшення розміру), що містить SQL командидля створення самої бази та таблиць, а також наповнення їх інформацією.

Отже, для того, щоб відновити MySQL базу з дампа, потрібно виконання команд, що містяться у файлі.

Для нормального відновлення даних такі складності не є обов'язковими. Достатньо мати в наявності тестовий файлик, інформація в якому буде структурована тим самим чином, що і в таблиці БД: кількість колонок з інформацією відповідає числу атрибутів запису таблиці.

Для цих цілей підійде і звичайний txt файл, Дані в якому будуть розділені, або файли, створювані в спеціальних табличних редакторах (Microsoft Office Excel, OpenOffice і т.д.), що мають відмінне розширення: xls, csv, odt та ін.

Дані формати навіть краще, т.к. при їх створенні обмежувачі даних додаються редакторами автоматично, і немає потреби вносити їх окремо, як у звичайного текстового файлу.

Додавання даних до MySQL: інструменти

З приводу інструментів здійснення імпорту бази даних MySQL можу сказати, що їх на сьогоднішній день існує цілих три.

Перерахую їх, починаючи з самих низькорівневих, закінчуючи високорівневими (з точки зору застосування усіляких оболонок та надбудов):

1. Консоль сервера та командний рядок MySQL;
2. Скрипти, написані мовами програмування, дозволяють робити запис даних MySQL з допомогою мовних средств;
3. Готові програми, що надають візуальний інтерфейс для роботи з БД (той самий phpMyAdmin, MySQL WorkBench, MySQL Manager та ін.).

Думаю, що порядок розташування інструментів не викличе жодного питання, т.к. засоби мов програмування, як правило, працюють на базі команд консолі MySQL, а програми базуються на скриптах або працюють з MySQL командним рядком безпосередньо.

Так чи інакше, на чолі лежить консоль, а інші інструменти, по суті, є її емуляторами.

Тому використання консолі при імпорті даних в MySQL дозволяє обійти різноманітні обмеження, що встановлюються налаштуваннями мов програмування на Web серверіі самих програм (які, до речі, який завжди можна змінити).

За рахунок цього залити базу даних MySQL через консоль можна як швидше, а й зробити цю операцію можливою у принципі, т.к. скрипти та програми схильні переривати імпорт при досягненні максимального часу виконання скрипта або взагалі не стартувати його через розмір файлу, що завантажується.

Думаю, всі, хто хоч раз намагався завантажити дамп у MySQL БД великого розміру через phpMyAdmin, розуміють, про що я говорю.

Найчастіше ці ліміти є причинами помилок при імпорті бази даних MySQL, які при використанні консолі ви ніколи не побачите.

Вони, звичайно, не константні, і їх можна змінити, але це додатковий біль голови, який для рядових користувачів, до речі, може виявитися нерозв'язним.

Сподіваюся, що я мотивував вас робити імпорт бази даних MySQL через консоль (причому як її структури, так і окремо даних).

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

Як відновити MySQL базу з дампи через консоль?

Отже, для того, щоб розгорнути дамп MySQL з консолі є два шляхи:

1. за допомогою команди у командному рядку MySQL;
2. у самій консолі сервера.

Почнемо по порядку.

Отже, щоб імпортувати дамп бази даних MySQL в існуюче сховище через , нам спочатку потрібно запустити її і вибрати потрібну базу даних, в яку ми будемо заливати наш дамп.

Виконання даних дій докладно описано у статті за посиланням вище, тому якщо вам потрібний їхній опис – візьміть звідти, т.к. дублювати їх по другому колу не хочу.

Після того, як ви зробите вказане, вводимо до MySQL Shell наступну команду:

Source шлях_і_ім'я_файлу_дампа;

Все, що нам залишиться, - це вивчати повідомлення в консолі про хід виконання операцій, що містяться в дампі.

Без попереднього перемикання на потрібну базу даних після з'єднання з сервером MySQL у консолі дамп можна імпортувати такою командою:

Mysql -u ім'я_користувача -p ім'я_бази_даних< путь_и_имя_файла_дампа

От і все. Головне – дочекатися закінчення імпорту, якщо файл дуже великий. Про закінчення заливки дампа можна судити з того, коли консоль сервера знову буде доступна.

Власне кажучи, в цьому і є недолік даного способупроти попереднім, т.к. у першому є можливість спостерігати за операціями, що виробляються з БД під час імпорту, а у другому — ні.

Якщо файл дампа буде запакований в архів, то при завантаженні потрібно буде попутно розпаковувати.

У Linux це можна зробити так:

Gunzip > [ім'я_файлу_архіву.sql.gz] | mysql -u -p

В Windows стандартноюутиліти для розпакування архіву в консолі немає, тому її потрібно встановити додатково.

Як бачите, імпорт дампа MySQL через консоль - операція дуже проста, яка виконується однією командою. Так що для виконання цієї процедури не обов'язково бути розробником.

Якщо раптом ви не знаєте, як запустити консоль сервера, то можете знайти цю інформацію в статті про командний рядок MySQL, посилання на яку я вже мав раніше.

До речі, описаними методами можливий також і імпорт таблиці MySQL, а не цілої бази. В цьому випадку в дампі, що заливається вами, повинні бути операції її створення і наповнення даними.

Завантаження даних у MySQL базу з файлу в консолі

Про відновлення БД MySQL із дампи в консолі ми поговорили. Тепер саме час розібратися з тим, як аналогічно можна імпортувати дані з файлів, у тому числі з xls і csv в базу MySQL.

Для цього ми знову маємо ті самі два інструменти, що й у попередньому випадку: командний рядок MySQL і консоль сервера.

Знову почнемо огляд по порядку.

Отже, для імпорту файлу в MySQL командному рядку ми знову запускаємо її і переходимо на БД, в яку завантажуватимуться дані.

LOAD DATA INFILE "шлях_і_ім'я_файла_дампа" INTO TABLE `таблиця_бази_даних` COLUMNS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY "\n";

Не забудьте, що якщо сервер MySQL був запущений з опцією -secure-file-priv(що часто буває при використанні MySQLдистрибутивів, що входять до WAMP/MAMP складання), то ім'я файлу потрібно вказувати з урахуванням системної змінної secure_file_priv.

Для того, щоб зробити імпорт бази даних MySQL в консолі сервера, не заходячи в MySQL Shell, нам знадобиться утиліта mysqlimport, що входить до складу дистрибутива MySQL, та наступний її виклик:

Ця утиліта є аналогом SQL команди LOAD DATA INFILE, тільки командного рядка. Але, питається, чому тоді серед параметрів її виклику не вказано таблицю, в яку завантажуватимуться дані з файлу?

Справа в тому, що mysqlimport просто фізично немає даного параметра. Натомість ім'я таблиці, в яку будуть завантажуватися дані, має бути присутнє в імені імпортованого файлу.

Тобто. якщо ви захочете зробити імпорт з Excel таблиців MySQL таблицю users, то ваш файл має називатися users.xls.

Розширення у імпортованого файлу, як говорилося, може бути будь-яким.

За допомогою mysqlimport також можна завантажувати відразу кілька файлів xls або CSV в MySQL. Щоб дані потрапили за призначенням, назви файлів та таблиць БД, як і попередньому прикладі, також мають збігатися.

Якщо раптом в файлі, що імпортується, стовпці йдуть не в тій же послідовності, що і колонки таблиці БД, то для уточнення їх порядку потрібно використовувати опцію -columns в наступному вигляді:

Mysqlimport –u ім'я_користувача –p ім'я_бази_даних --columns стовпець1, стовпець2, … ім'я_і_шлях_до_файлу_імпорту

Природно, що у прикладах я не розглянув повний списокпараметрів mysqlimport, т.к. деякі з них дуже специфічні і практично використовуються дуже рідко.

Якщо захочете ознайомитися з ними самостійно, повний їх список доступний тут - https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html

Особливості завантаження даних у MySQL базу з дампи

Якщо хочете, щоб процес імпорту великої бази MySQL проходив швидше, необхідно створювати дамп БД з використанням спеціальних опційкоманди mysqldump, про які я писав у своїй попередній статті про експорт БД MySQL, посилання на яке розміщував у тексті раніше.

У самих команд імпорту баз даних MySQL таких опцій, на жаль, немає.

Єдине, для збільшення швидкості завантаження великого дампа БД можете використовувати наступну фішку.

1. Відкриваємо файл дампа (бажано у файлових менеджерів, т.к. звичайні редактори можуть легко загнутися від великих файлів).

2. Прописуємо на початку файлу наступні рядки:

SET foreign_key_checks = 0; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0;

Зверніть увагу! Можливо, вони вже є або закоментовані (багато програм, за допомогою яких роблять дампи, можуть додавати їх автоматично)

3. Наприкінці файлу прописуємо зворотні дії:

SET foreign_key_checks = 1; SET UNIQUE_CHECKS = 1; SET AUTOCOMMIT = 1;

До речі, ці команди допоможуть не лише прискорити процес імпорту, але й зробити його можливим.

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

DROP TABLE IF EXISTS `clients`; CREATE TABLE `clients` (…);

Тобто. виконується пошук у БД таблиці з таким самим ім'ям, як і в імпортованої, і якщо така знайдена, вона видаляється і створюється наново.

І якщо раптом існуюча таблиця буде пов'язана з зовнішніми ключами з іншими, то все завантаження провалиться.

Тому відключення перевірки існування зовнішніх ключівта інших – це ще й чудова гарантія успішного виконання процесу імпорту бази даних MySQL.

Особливості імпорту CSV в MySQL БД та інших файлів

При завантаженні даних у MySQL БД з текстових файлівможе також знадобитися вимкнення зовнішніх ключів.

Причому, на відміну попередньої ситуації, у разі прописати директиви у файл вдасться, т.к. SQL команди в ньому не сприйматимуться і виконуватимуться.

У попередній статті про експорт бази MySQL я вже згадував про те, як це зробити за допомогою наступної операції в командному рядку MySQL:

SET FOREIGN_KEY_CHECKS=0;

Однак там я не згадав, що системна змінна MySQL FOREIGN_KEY_CHECKSмає два значення: глобальне та сесійне (для поточної сесії).

Глобальне значення змінних MySQL діє під час виконання будь-яких дій на сервері MySQL до його перезапуску. Тоді значення змінних буде скинуто і їм присвоєно значення за промовчанням.

Сесійне значення системної змінної MySQL встановлюється лише під час сеансу роботи користувача з сервером MySQL. Сеанс або сесія починається при підключенні клієнта до сервера, при якому йому надається унікальний connection id, і закінчується при відключенні від сервера, яке може статися будь-якої миті (наприклад, по таймууту).

Чому я про це вирішив згадати?

Тому що при виконанні команд завантаження файлу MySQL БД через консоль сервера, без заходу в MySQL Shell, я виявив, що відключення перевірки зовнішніх ключів наведеним раніше способом не працює.

У консолі так само видавалося повідомлення про помилку, що викликається наявністю в таблиці зовнішніх ключів.

А виникало воно з тієї причини, що наведеною командою відключалася перевірка існування зовнішніх ключів у рамках сесії, а не глобально, яка, крім зазначеного способу, може бути виконана ще й таким чином:

SET SESSION ім'я_змінної = значення_змінної; SET @@session.ім'я_змінної = значення_змінної; SET @@ім'я_змінної = значення_змінної;

У наведених командах змінна явно позначається як сесійна.

А оскільки я виконував завантаження csv файлу в MySQL таблицю через консоль сервера, без прямого підключення до сервера MySQL, то сеанс і не був створений, в рамках якого працювало б моє сесійне значення змінної.

В результаті я встановив глобальне значення FOREIGN_KEY_CHECKS і імпорт успішно виконався.

Зробити це можна одним із перерахованих способів:

SET GLOBAL ім'я_змінної = значення_змінної; SET @@global.ім'я_змінної = значення_змінної;

Після зміни значень для перевірки того, що зміни набули чинності, не зайвим буде переглянути значення змінної. Для виведення сесійного та глобального значень одночасно користуйтеся наступною командою:

SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;

На цьому сьогоднішня стаття, присвячена імпорту бази даних MySQL, добігла кінця. Діліться своїми враженнями та власними напрацюваннями у коментарях. Думаю, що багатьом буде цікавим ваш досвід.

До нових зустрічей! 🙂

P.S.: якщо вам потрібен сайт або необхідно внести редагування на існуючий, але для цього немає часу та бажання, можу запропонувати свої послуги.

Понад 5 років досвідупрофесійної розробки сайтів Робота з PHP, OpenCart,