Робити дамп (бекап) бази даних дуже важливо. Тому я зробив деякі коментарі з прикладами до такої чудовою утилітоюяк mysqldump.

mysqldump - утиліта дозволяє отримати дамп вмісту бази даних або сукупності баз для створення резервної копіїабо пересилання даних на інший SQL-сервер бази даних (не обов'язково MySQL-сервер). Дамп міститиме набір команд SQL для створення та/або заповнення таблиць.

mysqldump-u root -p -f name_database >

Цією командою ми робимо бекап базиданих під ім'ям name_database на диск С файл mydb_backup_name_database.txt

Файл можна створювати, MySQL створить його сам.

mysql-u root -p -f name_database< C:\mydb_backup_name_database.txt

цією командою ми імпортуємо дані бекапу з файлу C:\mydb_backup_name_database.txt

Примітка: -f, --force - опція, яка вказує на продовження навіть при отриманні помилки SQL, тобто. ігнорувати помилки. Наприклад, якщо в таблиці вже існує у всьому ідентичний рядок.

Щоб пароль не вимагався, потрібно писати його відразу після -p, тобто без пробілів. Якщо пароль Pwd, то приклад вигладить так:

mysqldump-u root -p Pwd-f name_database > C:\mydb_backup_name_database.txt

Якщо ви часто використовуєте цю команду, то краще зробити окремого користувачаз необхідними правами, щоб менше світити пароль root-а

Розглянемо більше тонкі налаштування mysqldump:

--databasesдозволяє зробити так, що mysqldump включить до сценарію відновлення команди CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME та USE DBNAME. Це дозволить створювати робочі бази "з нуля". Тобто без використання --databases мається на увазі, що користувач відновлює одну базу даних і явно вказує, куди потрібно поміщати відновлювані дані. Якщо ж backup створюється з метою зробити повністю робочу копію даних, наприклад, на іншому MySQL-сервері, потрібно використовувати цей ключ;

--all-databasesдозволяє зробити копії всіх баз даних, які існують на даному MySQL-сервері. Якщо ж потрібно зробити копії лише деяких баз, потрібно просто вказати їх через пропуск при виклику mysqldump з командного рядка (див. вище);

Ключ --help. Програма mysqldump має багато версій. Подивитися, які можливості підтримуються безпосередньо Вашою версією, можна за допомогою цього ключа;

--add-drop-table- ключ, який змусить mysqldump додавати до підсумкового сценарію команду drop table перед створенням таблиць. Це дозволить уникнути деяких помилок під час відновлення бази з резервної копії. Звичайно, потрібно враховувати те, що таблиці, що знаходяться в робочій копії (якщо таблиці з таким же ім'ям існують у backup), перед відновленням із резервної копії будуть видалені з основної бази та перестворені з backup;

--no-data. За допомогою цього ключа можна швидко зробити копію структури таблиці/баз без даних. Наприклад, Ви створили складну таблицю та хотіли б зберегти на майбутнє її структуру, а самі дані, що знаходяться в цій таблиці, Вам у резервній копії не потрібні;

--result-file=...- цей ключ можна використовувати для перенаправлення виведення файлу. Можна використовувати звичайне unix-перенаправлення командою ">", а можна – ось цей ключ. Кому що подобається;

Ще один дуже корисна порадаз використання mysqldump в середовищі хостингу. Як правило, при використанні хостингу користувача накладаються деякі обмеження. Наприклад, не можна зайняти більше деякої кількості фізичної пам'яті(RAM, ОЗП). mysqldump за умовчанням поміщає всі отримані від MySQL-сервера дані на згадку, та був записує усе це на диск. Відповідно, якщо провайдер дає Вам зайняти, наприклад, 30Мб пам'яті, а база, копію якої Ви робите за допомогою mysqldump, займає 50Мб, звичайно тут виникне помилка - mysqldump не зможе відпрацювати коректно і завершиться аварійно, про що Вам повідомить. Щоб "змусити" mysqldump писати дані відразу на диск, а не зберігати їх, нехай навіть тимчасово, в пам'яті, використовуйте ключ --quick. Це вирішить проблему.

Наведемо ще кілька корисних прикладів:

mysqldump -u root -p Pwd-f --default-character-set=cp1251 DBNAME| gzip -c > filename.txt .gz

розпаковувати такий архів можна командою:

gunzip filename.txt .gz

Щоб знати, за яке число було зроблено бекап, можна написати таку команду:

mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date "+%Y-%m-%d"`.gz

а якщо доводиться робити дампи з різними кодуваннями, то зручно користуватися змінними:

set DBCHARACTER= utf8

set DBNAME= breach

mysqldump -u root -p Pwd-f --default-character-set=$ DBCHARACTER $DBNAME| bzip2 -c > sql. $DBNAME.`date "+%Y-%m-%d"`.bz2

Якщо Ви хочете автоматизувати видалення старих архівів, спробуйте скористатися cron командою findяка зазвичай є в unix. Запускаючи періодично

find~/каталог-з-архівами -name "*.gz" -mtime +7 -exec rm -f() \;

Тим самим Ви видалятимете архіви, які "старші" семи днів.

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

SET NAMES utf8; DROP DATABASE `mydb`; CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8; USE `mydb`; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET autocommit = 0; SET foreign_key_checks=0; SET unique_checks = 0; SOURCE /path/to/db_dump.sql; COMMIT; SET autocommit = 1; SET foreign_key_checks=1; SET unique_checks = 1;

Ці команди дозволять вам швидко імпортувати дамп бази даних та уникнути проблем. Причому деякі з цих проблем не вирішують навіть поширені mysql-клієнти, такі як phpMyAdmin та інші.

Спробуємо розібрати даний кодпо порядку.

SET NAMES utf8;каже, що обмін даними відбуватиметься у кодуванні UTF-8 і саме в ньому знаходиться Вихідний файлДампа бази і саме її матиме кінцева база даних.

DROP DATABASE `mydb`;і CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8;виконують знищеннябази даних та її подальше створення з кодуванням за умовчанням UTF-8. Зрозуміло, що це зітре всі таблиці та дані в них, але це і дасть нам впевненість у тому, що отримана БД буде повністю відповідати даним у файлі дампа.

USE `mydb`;вказує, що треба використовувати саме цю базу даних для всіх команд із файлу дампа (перевірте файл дампа на наявність цієї директиви із зазначенням іншої бази, щоб уникнути конфузу).

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";відключає збільшення лічильника авто-інкремента при записі в полі авто-інкремента значення 0. За замовчуванням, при записі в таке поле значення NULLабо 0 воно автоматично інкрементується. І хоча мати значення 0 в автоінкрементному полі - не найкраща практика, Іноді все ж трапляється що в дампі є такі рядки (я зустрівся з подібним в дампі від magento). Варто також замінити, що mysqldump з деяких пір включає цю директивуза замовчуванням у своїх дампах.

SET autocommit = 0;перед заливкою дампа та COMMIT;після нього - прискорюють виконання INSERT"ів для InnoDB таблиць, оскільки при включеному режимі авто-коміта логи зберігаються на диск при кожному INSERT". Може допомогти при накочуванні великих дампів.

SET foreign_key_checks=0;Найбільш очевидна опція, без якої більшість дамп взагалі не могла б бути застосована. Вимикає перевірки зовнішніх ключів, що дозволяє записувати дані в будь-якому порядку і в тому числі з циклічними посиланнями зовнішнім ключам. Ця функція також прискорює імпорт дампа.

SET unique_checks = 0;відключає перевірки на унікальність для вторинних ключів, що таки може прискорити процес імпорту.

Ну і нарешті, основна команда, заради якої все це було задумано. SOURCE /path/to/db_dump.sql;безпосередньо імпортує дамп бази mysql з вказаного файлу. Шлях до файлу може бути абсолютним або відносним від тієї директорії, звідки ви зайшли в консоль mysql.

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

Дивно, але в інтернеті знайти цю інформацію досить непросто. Купа лівих будь-яких рекомендацій. А насправді все просто. Відновити дамп можна командою:

# mysql-u -p< mysql_dump.sql.

Замість підставте ім'я користувача, особисто відновлення роблю від рута. А в mysql_dump,sql, власне, дамп зроблений mysqldumpутилітою. Опція -p вказує на авторизацію з паролем після натискання ентера у вас запитають пароль. Ось і все, власне. Зробити дамп можна командою:

# mysqldump-u root -p -f database_name > file_with_dump.sql

Тут параметр -f database_name визначає ім'я бази, яку зберігаємо. Якщо треба все – замінюємо на -all-databases.Ця опція вкаже зберігати всі бази сервера. Щоб не вводити кожен раз пароль, можна вказати його прямо в команді (це треба, якщо ви робите дамп по крону зі скрипту), втім, з точки зору безпеки тут виникають питання, особливо якщо рутовий пароль для м'яза. Ось якби м'яз розумів хоча б md5 паролі ... Втім не факт, що не розуміє, просто може я не знаю:)

Автор

Алекс Розгібалов

Божевільний чоловік, невизначеного віку, що насолоджується манією переслідування. Паталогічно недовірливий, егоїстичний, авторитарний. Вторинні діагнози - програміст та поц. Володіє англійською мовоюна рівні близько хренового розмовного. Також знайомий із деякими іншими мовами. Цікавиться всім і вся, за рахунок чого у будь-якій галузі знання є поверхневими, неглибокими. Характер невитриманий. Фортеця – 55 градусів.

Дамп та відновлення бази даних MySQLдосить просто та зручно робити віддалено через SSH або прямо через консоль сервера. Віддалено, це можна робити за допомогою програми Putty/Kitty. Також наведені нижче приклади Ви можете виконувати і на Windows запустивши командний рядок ‘cmd‘. Нижче наведено приклади про те, як створювати дампи бази даних MySQL і потім відновлювати їх у разі потреби, наприклад для Вашого сайту, інтернет-магазину або якогось іншого проекту.

Створення дампи бази даних MySQL

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

# Бекап однієї бази даних файл dump_file.sql mysqldump -uroot -p your_base > dump_file.sql # На windows дампнайкраще створювати трохи іншою командою, яка запобігає # випадкове затирання рядків дампа через конвертацію символів перекладу рядка "\r\n" в "\n" mysqldump -uroot -p your_base -r dump_file_utf8.sql # Якщо Вам потрібен бекап тільки окремих таблиць , а не всієї бази даних # (вказуємо найменування таблиць через пробіл після назви бази даних) mysqldump -uroot -p your_base no-data your_base > dump_file.sql # Бекап всіх баз даних у файл поточна_дата.gz mysqldump -uroot -p --all_databases | gzip -c > "date "+%Y-%m-%d"".gz # Бекап, де для кожного запису створюється окремий INSERT # і з явною вказівкою кодування бази даних UTF-8 mysqldump -uroot -p --default- character-set=utf8 your_base --extended-insert=FALSE | gzip -c > "date "+%Y-%m-%d"".gz

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

• -u– параметр вказує логін, який використовуватиметься для підключення до бази даних. У прикладі ми використовуємо логін root, який потрібно вказати у цьому параметрі без пропуску! В результаті у нас це виглядає як -uroot
• -p– параметр вказує, що потрібно ввести пароль для вказаного логіну. Ми залишили його порожнім, внаслідок чого пароль потрібно буде ввести після натискання “Enter” при виконанні команди. Тим не менш, можна вказати пароль відразу ж тут, як і в параметрі логіна, без пробілу після -p, однак цей спосіб не є безпечним, так як консоль зберігає Ваші команди в файлі лог і якщо Ви його регулярно не очищаєте, то він може бути переглянутий зловмисником.
• your_base– замість цього рядка у прикладі, вам необхідно вказати реальне ім'я Вашої бази даних, для якої Ви створюєте бекап.
• > - Оператор показує напрямок дії, тобто. як би вказує, що ви збираєтеся зробити запис із бази у файл.
• dump_file.sql– це назва Вашого файла.slq, в яку потрібно зберегти Вашу базу даних. Він вказується через пропуск після оператора '>'. Ви можете встановити будь-яке інше ім'я. Наприклад, щоб у імені система автоматично вставила поточний час, достатньо вказати рядок виду:
  

  "date "+%Y-%m-%d""

  "date "+%Y-%m-%d""

  
  після цього рядка у прикладі вказується розширення файлу ‘ .gz‘. В результаті буде створено файл виду ‘ 2014-11-15.gz‘.

  Увага!Якщо Ви вказуєте лише ім'я файлу, то він буде збережений у тій же директорії, щодо якої Ви виконуєте команду. Тобто. якщо Ви бачите у рядку запрошення введення команд щось на зразок # , де root@dvsце логін та ім'я сервера, то файл буде створено в директорії /home. Щоб змінити збереження файлу іншим шляхом, вкажіть замість імені повний шляхдля збереження файлу, наприклад: /var/www/backup/dump_file.sql.

• У другому прикладі замість оператора ‘ > 'використовується оператор' | ‘, який вказує на необхідність виконання додаткової команди gzip з параметром ‘ -c' яка дозволяє відразу ж запакувати дамп до архіву, а лише потім зберегти його у файл виду ' 2014-11-15.gz', про що повідомляє оператор ' > ‘.
• Параметр -no-dataдозволяє створити дамп лише структури бази даних без даних. У деяких випадках досить корисно, коли дані не потрібні.
• Параметри –default-character-set=utf8і -extended-insert=FALSE. Перший дозволяє Вам явно вказати кодування, яке використовується цією базою даних, тим самим уникнути збереження бази в неправильному кодуванні Замість utf8 можна вказати будь-яке інше кодування, наприклад CP1251. Другий параметр дозволяє вказати, що для експорту для кожного запису необхідно створити окрему команду INSERT. У деяких випадках це може знадобитися при частковому відновленні даних із дампи.

Відновлення бази даних із файлу дампа MySQL

Тепер розглянемо зворотний процес відновлення бази даних із файлу дампа. Ця діявиконується за допомогою програми MySQL. Розглянемо відразу приклад.