tag: #N/S/Medium #N/T/Linux #N/T/Tool/Util #N/T/Public 2022-08-04 21:23, [Source](https://adw0rd.com/2009/06/07/mysqldump-and-cheat-sheet/), Related: [[]], Docs: ## Описание Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц. Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла. ## Шаблон ## Примеры: ### Создание дампа ##### Дамп одной конкретной базы, по имени Сдампим базу "database" в файл "dump.sql" с указанием логина, хосата и пароля ```bash mysqldump -u USER -h 82.82.82.82 -pPASSWORD DATABASE > /path/to/file/dump.sql ``` Одной транзакцией ```bash mysqldump --single-transaction=TRUE postfix > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz` ``` ##### Дамп несколько баз данных ```bash mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql ``` ##### дамп всех баз данных ```bash mysqldump -A > all-databases.sql ``` ##### Создаём структуру базы без данных ```bash mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql ``` ##### Если нужно сделать дамп только одной или нескольких таблиц ```bash mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql ``` ##### Создаём бекап и сразу его архивируем ```bash mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz ``` ##### Создание бекапа с указанием его даты ```bash mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz` ``` ##### Удобно использовать бекап с дополнительными опциями -Q -c -e, т.е. ```bash mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql, ``` где: - -Q оборачивает имена обратными кавычками - -c делает полную вставку, включая имена колонок - -e делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее #### Перенести базу данных MySQL на другой сервер с архивацией [src](https://codernet.ru/articles/linux/kak_perenesti_bazu_dannyix_mysql_na_drugoj_server/) На целевой машине: ```bash nc -l 55555 | gzip -d -c | mysql <database name> -u<user> -p<password> ``` На исходной машине: ```bash mysqldump -u<user> -p<password> <database name> | gzip | nc <ip of target server> 55555 ``` ### Развертывание дампа Перенаправляем поток в обратную сторону и развертываем базу данных: ```bash mysql -uroot -h82.82.82.82 -p database < database.sql ``` Или через mysql-console: ```bash mysql> use database; mysql> source database.sql ``` Ну, а если у нас gz-архив к примеру, то: ```bash zcat database.sql.gz | mysql -uroot -h82.82.82.82 -p database ``` ##### Заливаем бекап в базу данных ```bash mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql ``` ##### Заливаем архив бекапа в базу ```bash gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE или так zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE ``` ##### Создаём новую базу данных ```bash mysqladmin -u USER -pPASSWORD create NEWDATABASE ``` ### Пример использование некоторых параметров Например, нам нужны данные с "продакшен версии базы" для "версии разработчика", то есть нам нужна "песочница". Выбираем не более 100 записей: ```bash mysqldump -uroot -h82.82.82.82 -p --where="true limit 100" database > database.sql ``` Или нам нужна только структура, без данных: ```bash mysqldump -uroot -h82.82.82.82 -p --no-data database > database.sql ``` Примеры навеяны постом Александра Макарова - [http://rmcreative.ru/blog/post/ljogkiy-damp-mysql](http://rmcreative.ru/blog/post/ljogkiy-damp-mysql) Делаем дамп только триггеров, процедур и [событий](https://adw0rd.com/2009/mysql-event-sheduler/): ```bash mysqldump --no-create-info --no-data --triggers --routines --events -uroot -p database | gzip > ~/database.sql.gz ``` ### Шпаргалки: ### Еще пару слов о бекапе в MySQL #### mysqlhotcopy для MyISAM Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать "[[% mysqlhotcopy]]", которая скопирует файлы \*.frm, \*.MYD и \*.MYI: ```bash mysqlhotcopy db_name /path/to/dir ``` Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных. #### xtrabackup для InnoDB Для InnoDB есть [[% xtrabackup]], рекомендую посмотреть! #### Бин-лог и репликации Для репликации "mysqldump" не предназначена, для этого есть бин-лог (--log-bin): ```bash mysqlbinlog binlog.[0-9]* | mysql ``` Для полной репликации необходимо вести бин-лог с самого начала работы БД, то есть еще до создания структур и данных. #### Резервирование данныс в MySQL 6.x С версии MySQL 6.x доступен online-backup, вот слайд объясняющий нововведения: [](http://adw0rd.ru/media/2009/06/online_backup.jpg) ### Используемые или важные ключи. - -u или -–user=... - имя пользователя - -h или --host=... - удаленный хост (для локального хоста можно опустить этот параметр) - -p или --password - запросить пароль - --databases (или сокращенно -B) - --all-databases (или сокращенно -A) Приведу некоторые параметры, которые могут понадобится при работе с утилитой mysqldump. `--add-drop-database` Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE. `--add-drop-table` Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE. `--add-locks` Добавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL). `--all-databases, -A` Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера. `--allow-keywords ` Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца. `--comments, -i` Данный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL. `--compact` Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным --comments. `--compatible=name` Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: "ansi", "mysql323", "mysql40", "postgresql", "oracle", "mssql", "db2", "maxdb", "no_key_options", "no_table_options", "no_field_options". Можно использовать несколько значений, разделив их запятыми. `--complete-insert, -c` Используется полная форма оператора INSERT (с именами столбцов). `--create-options` Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры. `--databases, -B` Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп. `--delayed` Использовать команду INSERT DELAYED при вставке строк. `--delete-master-logs` На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр "--master-data". `--disable-keys, -K` Для каждой таблицы, окружает оператор INSERT выражениями /\*!40000 ALTER TABLE tbl_name DISABLE KEYS \*/; и /\*!40000 ALTER TABLE tbl_name ENABLE KEYS \*/; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных. ` --extended-insert, -e` Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода). `--flush-logs, -F` Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа. `--force, -f` Продолжать даже если в процессе создания дампа произошла ошибка. `--hex-blob` Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность "abc" будет заменена на 0x616263. `--ignore-table=db_name.tbl_name` Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров "--ignore-table", указывая по одной таблице в каждом из параметров. `--insert-ignore` Добавляет ключевое слово IGNORE в оператор INSERT. `--lock-all-tables, -x` Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных. `--lock-tables, -l` Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп. `--no-autocommit` Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных. `--no-create-db, -n` Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров --databases и --all-databases. `--no-data, -d` Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных. `--opt` Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: --quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset. Начиная с MySQL 4.1, параметр --opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров --skip-opt `--order-by-primary` Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу. `--port, -P ` Номер TCP порта, используемого для подключения к хосту. `--protocol={TCP|SOCKET|PIPE|MEMORY}` Параметр позволяет задать протокол подключения к серверу. `--quick, -q` Позволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память. `--quote-names, -Q` Помещает имена баз данных, таблиц и столбцов в обратные апострофы . Начиная с MySQL 4.1, данный параметр включен по умолчанию. `--replace` Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3. `--result-file=/path/to/file, -r /path/to/file` Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>. `--routines, -R ` Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2. `--single-transaction` Параметр создает дамп в виде одной транзакции. `--skip-comments` Данный параметр позволяет подавить вывод в дамп дополнительной информации. `--socket=/path/to/socket, -S /path/to/socket` Файл сокета для подсоединения к localhost. `--tab=/path/, -T /path/` При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров --fields-xxx и --lines-xxx. `--tables` Перекрывает действия параметра --databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц. `--triggers` Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр --skip-triggers. `--events, -E` Создается дамп событий. Смотрите [MySQL Event Scheduler или встроенный диспетчер событий в MySQL](https://adw0rd.com/2009/mysql-event-sheduler/). `--tz-utc` при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE='+00:00', который позволит обмениваться дампа в различных временных зонах. `--verbose, -v` Расширенный режим вывода. Вывод более детальной информации о работе программы. `--version, -V` Вывести информацию о версии программы. `--where='where-condition', -w 'where-condition'` Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны. `--xml, -X` Представляет дамп базы данных в виде XML. `--first-slave, -x` Блокирует все таблицы во всех базах данных. `--debug=..., -#` Отслеживать прохождение программы (для отладки). `--help` Вывести справочную информацию и выйти из программы.