Mysqldump игнорировать ошибки

mysqldump — Данная утилита позволяет получить дамп («моментальный снимок») содержимого базы данных или совокупности баз для создания резервной копии
или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер).
Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

Чтобы создать дамп всей базы данных, не называйте никакие таблицы после db_nameили используйте параметр —databasesили —all-databases.
mysqldump [options] db_name [tbl_name …] (база данных [таблицы])
mysqldump [options] —databases db_name … (—базы данных [ПАРАМЕТРЫ] DB1 [DB2 DB3…])
mysqldump [options] —all-databases (—все базы данных [ПАРАМЕТРЫ])

mysqldump —help список параметров, поддерживаемых вашей версией

Делаем бэкап
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Создаём структуру базы без данных
mysqldump —no-data — u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

Если нужно сделать дамп только одной или нескольких таблиц
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

Создаём бекап и сразу его архивируем
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Создание бекапа с указанием его даты
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Заливаем бекап в базу данных
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

Заливаем архив бекапа в базу
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
или так
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Создаём новую базу данных
mysqladmin -u USER -pPASSWORD create NEWDATABASE

Удобно использовать бекап с дополнительными опциями -Q -c -e, т.е.
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql, где:

-Q оборачивает имена обратными кавычками
-c делает полную вставку, включая имена колонок
-e делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее

Выгрузка БД zabbixdb
mysqldump -u root -p zabbixdb > zabbixdb.sql
Выгрузка 1 таблицы history
mysqldump -u root -p zabbixdb history > history.sql

Без функции create чтоб в дальнейшем не пере затереть таблицы, а подлить данные (не содержит команды create создать)
В конце имени содержит букву h не перепутайте.
mysqldump -u root -p —no-create-info —lock-tables zabbixdb history > historyh.sql

mysqldump -uroot -p123456789 database —ignore-table database.table1 —ignore-table database.table2 > dump.sql

где
root — имя пользователя
123456789 — пароль
database — имя базы данных
database.table1 и database.table2 — исключаемые из дампа таблицы

Оф. mysqldump EN

Название опции Описание
—add-drop-database Добавьте оператор DROP DATABASE перед каждым оператором CREATE DATABASE.

—add-drop-table Добавьте оператор DROP TABLE перед каждым оператором CREATE TABLE.

—add-drop-trigger Добавьте оператор DROP TRIGGER перед каждым оператором CREATE TRIGGER.

—add-locks Окружите каждый дамп таблицы операторами LOCK TABLES и UNLOCK TABLES.

-A —all-databases Дамп всех таблиц во всех базах данных

-Y —all-tablespaces Сбросьте все табличные пространства.

—allow-keywords Разрешить создание имен столбцов, которые являются ключевыми словами

—apply-replica-statements Включите STOP REPLICA перед оператором CHANGE REPLICATION SOURCE TO и START REPLICA в конце вывода.

—apply-slave-statements Включите STOP SLAVE перед оператором CHANGE MASTER и START SLAVE в конце вывода.

—bind-address Использовать указанный сетевой интерфейс для подключения к серверу MySQL

—character-sets-dir=name Каталог, в котором установлены наборы символов

—column-statistics Напишите операторы ANALYZE TABLE для создания гистограмм статистики.

-i —comments Добавить комментарии к файлу дампа

—compact Производите более компактную продукцию

—compatible=name Производить выходные данные, более совместимые с другими системами баз данных или со старыми серверами MySQL.

-c —complete-insert Используйте полные операторы INSERT, которые включают имена столбцов.

-C —compress Сжатие всей информации, передаваемой между клиентом и сервером

—compression-algorithms Разрешенные алгоритмы сжатия для соединений с сервером

-a —create-options Включите все специфичные для MySQL параметры таблицы в операторы CREATE TABLE.

-B —databases Интерпретировать все аргументы имени как имена базы данных

-# —debug[=#] Написать журнал отладки

—debug-check Распечатывать отладочную информацию при выходе из программы

—debug-info Распечатывать отладочную информацию, память и статистику ЦП при выходе из программы

—default-auth=name Плагин аутентификации для использования

—default-character-set=name Укажите набор символов по умолчанию

—delayed-insert Вставка строк с ЗАДЕРЖКОЙ ВСТАВКИ.

—defaults-extra-file Чтение именованного файла опций в дополнение к обычным файлам опций

—defaults-file Только для чтения именованный файл опций

—defaults-group-suffix Значение суффикса группы параметров

—delete-master-logs На исходном сервере репликации удалите двоичные журналы после выполнения операции создания дампа.

—delete-source-logs На исходном сервере репликации удалите двоичные журналы после выполнения операции создания дампа.

-K —disable-keys Для каждой таблицы окружите операторы INSERT операторами для отключения и включения ключей.

—dump-date Включите дату дампа в качестве комментария «Дамп завершен», если задан параметр —comments

—dump-replica Включите оператор CHANGE REPLICATION SOURCE TO, в котором перечислены координаты двоичного журнала источника реплики.

—dump-slave[=#] Включите оператор CHANGE MASTER, в котором перечислены координаты двоичного журнала источника реплики.

—enable-cleartext-plugin Включить плагин аутентификации открытым текстом

-E —events Дамп событий из дампов баз данных

-e —extended-insert Использовать многострочный синтаксис INSERT

—fields-enclosed-by Эта опция используется с опцией —tab и имеет то же значение, что и соответствующее предложение для LOAD DATA.

—fields-escaped-by=name Эта опция используется с опцией —tab и имеет то же значение, что и соответствующее предложение для LOAD DATA.

—fields-optionally-enclosed-by=name Эта опция используется с опцией —tab и имеет то же значение, что и соответствующее предложение для LOAD DATA.

—fields-terminated-by=name Эта опция используется с опцией —tab и имеет то же значение, что и соответствующее предложение для LOAD DATA.

-F —flush-logs Сбросить файлы журнала сервера MySQL перед запуском дампа

—flush-privileges Выдать оператор FLUSH PRIVILEGES после дампа базы данных mysql

-f —force Продолжайте, даже если во время дампа таблицы возникает ошибка SQL.

—gtid Используется вместе с —master-data=1 или —dump-slave=1.При включении выходные данные этих параметров будут задавать сетку позиция вместо файла binlog и смещения; файл /смещение будут отображаться только в качестве комментария. При отключении позиция СЕТКИ по-прежнему будет отображаться в выходных данных, но только прокомментировал.

—get-server-public-key Запросить открытый ключ RSA с сервера

-? —help Показать справочное сообщение и выйти

—hex-blob Дамп двоичных столбцов с использованием шестнадцатеричной записи

-h —host=name Хост, на котором расположен сервер MySQL

—ignore-error Игнорировать указанные ошибки

—ignore-table=name Не сбрасывать данную таблицу

—include-master-host-port Включить параметры MASTER_HOST/MASTER_PORT в оператор CHANGE MASTER, созданный с —dump-slave

—include-source-host-port Включите параметры SOURCE_HOST и SOURCE_PORT в оператор CHANGE REPLICATION SOURCE TO, созданный с —dump-replica

—insert-ignore Пишите операторы INSERT IGNORE вместо INSERT.

—lines-terminated-by=name Эта опция используется с опцией —tab и имеет то же значение, что и соответствующее предложение для LOAD DATA.

-x —lock-all-tables Заблокировать все таблицы во всех базах данных

-l —lock-tables Блокировать все таблицы перед их сбросом

—log-error=name Добавить предупреждения и ошибки в именованный файл

—log-queries При восстановлении дампа сервер будет, если ведение журнала включен, регистрируйте запросы в общий и медленный запрос очереди

—login-path Прочитайте параметры пути входа в систему из .mylogin.cnf

—master-data[=#] Запишите имя файла двоичного журнала и позицию в вывод.

—max-allowed-packet=# Максимальная длина пакета для отправки или получения с сервера

—net-buffer-length=# Размер буфера для TCP/IP и связи через сокеты

—network-timeout Увеличьте время ожидания сети, чтобы разрешить большие дампы таблиц.

—no-autocommit Включите операторы INSERT для каждой выгруженной таблицы в операторы SET autocommit = 0 и COMMIT.

-n —no-create-db Не пишите операторы CREATE DATABASE.

-t —no-create-info Не пишите операторы CREATE TABLE, которые заново создают каждую выгруженную таблицу.

-d —no-data Не сбрасывать содержимое таблицы

—no-data-med Нет информации о строках для движков, которые управляют внешними данными ((MRG_MyISAM, MERGE_ISAM, CONNECT, OQGRAPH, SPIDER, VP, FEDERATED).

—no-defaults Не читать файлы опций

-N —no-set-names То же, что —skip-set-charset

-y —no-tablespaces Не записывайте в вывод никаких операторов CREATE LOGFILE GROUP или CREATE TABLESPACE.

—opt Сокращение для —add-drop-table —add-locks —create-options —disable-keys —extended-insert —lock-tables —quick —set-charset

—order-by-primary Дамп строк каждой таблицы, отсортированных по первичному ключу или по первому уникальному индексу.

-p —password[=name] Пароль для использования при подключении к серверу

—password1 Первый пароль многофакторной аутентификации для использования при подключении к серверу

—password2 Второй пароль многофакторной аутентификации для использования при подключении к серверу

—password3 Третий пароль многофакторной аутентификации для использования при подключении к серверу

—pipe Подключиться к серверу с помощью именованного канала (только для Windows)

—plugin-dir=name Каталог, в котором установлены плагины

-P —port Номер порта TCP/IP для подключения

—print-defaults Параметры печати по умолчанию

—protocol=name Транспортный протокол для использования

-q —quick Извлекать строки для таблицы с сервера построчно за раз

-Q —quote-names Идентификаторы кавычек внутри символов обратной кавычки

—replace Пишите операторы REPLACE, а не операторы INSERT.

-r —result-file=name Прямой вывод в заданный файл

-R —routines Дамп хранимых подпрограмм (процедур и функций) из выгруженных баз данных

—server-public-key-path Путь к файлу, содержащему открытый ключ RSA

—set-charset Добавьте SET NAMES default_character_set для вывода

—set-gtid-purged Добавлять ли SET @@GLOBAL.GTID_PURGED в вывод

—shared-memory-base-name Имя общей памяти для соединений с общей памятью (только для Windows)

—show-create-skip-secondary-engine Исключить предложение SECONDARY ENGINE из инструкций CREATE TABLE.

—single-transaction Выполните оператор BEGIN SQL перед сбросом данных с сервера

—skip-add-drop-table Не добавляйте оператор DROP TABLE перед каждым оператором CREATE TABLE.

—skip-add-locks Не добавлять замки

—skip-comments Не добавлять комментарии в файл дампа

—skip-compact Не производить более компактный вывод

—skip-disable-keys Не отключайте ключи

—skip-extended-insert Отключить расширенную вставку

—skip-opt Отключить параметры, установленные —opt

—skip-quick Не извлекать строки для таблицы с сервера построчно за раз

—skip-quote-names Не указывать идентификаторы

—skip-set-charset Не пишите инструкцию SET NAMES

—skip-triggers Не сбрасывать триггеры

—skip-tz-utc Выключить tz-utc

-S —socket Файл сокета Unix или именованный канал Windows для использования

—source-data Запишите имя файла двоичного журнала и позицию в вывод.

—ssl Включить SSL для подключения (автоматически включается с помощью другие флаги).

—ssl-ca=name Файл, содержащий список доверенных центров сертификации SSL.

—ssl-capath=name Каталог, содержащий файлы сертификатов доверенного центра сертификации SSL.

—ssl-cert=name Файл, содержащий сертификат X.509

—ssl-cipher=name Допустимые шифры для шифрования соединения

—ssl-crl=name Файл, содержащий списки отзыва сертификатов

—ssl-crlpath=name Каталог, содержащий файлы списка отзыва сертификатов

—ssl-verify-server-cert Проверьте «Общее имя» сервера в его сертификате на соответствие имя хоста, используемое при подключении. По умолчанию эта опция отключена

—ssl-fips-mode Включить ли режим FIPS на стороне клиента

—ssl-key=name Файл, содержащий ключ X.509

—ssl-mode Желаемое состояние безопасности подключения к серверу

—ssl-session-data Файл, содержащий данные сеанса SSL

—ssl-session-data-continue-on-failed-reuse Устанавливать ли соединения, если повторное использование сеанса не удается

-T —tab=name Создание файлов данных, разделенных табуляцией

—tables Переопределить параметр —databases или -B

—tls-ciphersuites Допустимые наборы шифров TLSv1.3 для зашифрованных соединений

—tls-version Допустимые протоколы TLS для зашифрованных соединений

—triggers Триггеры дампа для каждой таблицы дампа

—tz-utc Добавьте SET TIME_ZONE=’+00:00′ в файл дампа

-u —user=name Имя пользователя MySQL для использования при подключении к серверу

-v —verbose Подробный режим

-V —version Показать информацию о версии и выйти

-w —where=name Дамп только строк, выбранных по заданному условию WHERE

-X —xml Создание XML-вывода

—zstd-compression-level Уровень сжатия для подключений к серверу, использующих сжатие zstd

Variables (—variable-name=value) имя переменной=значение

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another database server (not necessarily MariaDB or MySQL). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

If you are doing a backup on the server and your tables all are MyISAM tables, consider using mysqlhotcopy instead because it can accomplish faster backups and faster restores.

mysqldump dumps triggers along with tables, as these are part of the table definition. However, stored procedures, views, and events are not, and need extra parameters to be recreated explicitly (for example, --routines and --events). Procedures and functions are however also part of the system tables (for example mysql.proc).

mysqldump supports the enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT.

From MariaDB 10.4.6, mariadb-dump is a symlink to mysqldump.

Performance

mysqldump doesn’t usually consume much CPU resources on modern hardware as by default it uses a single thread. This method is good for a heavily loaded server.

Disk input/outputs per second (IOPS), can however increase for multiple reasons. When you back-up on the same device as the database, this produces unnecessary random IOPS. The dump is done sequentially, on a per table basis, causing a full-table scan and many buffer page misses on tables that are not fully cached in memory.

It’s recommended that you back-up from a network location to remove disk IOPS on the database server, but it is vital to use a separate network card to keep network bandwidth available for regular traffic.

Although mysqldump will by default preserve your resources for regular spindle disks and low-core hardware, this doesn’t mean that concurrent dumps cannot benefit from hardware architecture like SAN, flash storage, low write workload. The back-up time would benefit from a tool such as MyDumper.

Usage

There are three general ways to invoke mysqldump:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

mysqldump does not dump the INFORMATION_SCHEMA database by default. MariaDB dumps the INFORMATION_SCHEMA if you name it explicitly on the command line, although currently you must also use the --skip-lock-tables option.

To see a list of the options your version of mysqldump supports, execute mysqldump --help.

Row by Row vs. Buffering

mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default, so to enable memory buffering, use --skip-quick.

mysqldump in MariaDB 10.3 and Higher

mysqldump in MariaDB 10.3 includes logic to cater for the mysql.transaction_registry table. mysqldump from an earlier MariaDB release cannot be used on MariaDB 10.3 and beyond.

mysqldump and Old Versions of MySQL

If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, you should not use the --opt or --extended-insert option. Use --skip-opt instead.

Options

mysqldump supports the following options:

Group Options

Some mysqldump options are shorthand for groups of other options:

• Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.
• Use of --compact is the same as specifying --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.

To reverse the effect of a group option, uses its --skip-xxx form (--skip-opt or --skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples:

• To select the effect of --opt except for some features, use the --skip option for each feature. To disable extended inserts and memory buffering, use --opt --skip-extended-insert --skip-quick. (Actually, --skip-extended-insert --skip-quick is sufficient because --opt is on by default.)
• To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.

When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, --disable-keys --lock-tables --skip-opt would not have the intended effect; it is the same as --skip-opt by itself.

Special Characters in Option Values

Some options, like --lines-terminated-by, accept a string. The string can be quoted, if necessary. For example, on Unix systems this is the option to enclose fields within double quotes:

--fields-enclosed-by='"'

An alternative to specify the hexadecimal value of a character. For example, the following syntax works on any platform:

--fields-enclosed-by=0x22

Option Files

In addition to reading options from the command-line, mysqldump can also read options from option files. If an unknown option is provided to mysqldump in an option file, then it is ignored.

The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:

In MariaDB 10.2 and later, mysqldump is linked with MariaDB Connector/C. However, MariaDB Connector/C does not yet handle the parsing of option files for this client. That is still performed by the server option file parsing code. See MDEV-19035 for more information.

Option Groups

mysqldump reads options from the following option groups from option files:

NULL, ´NULL´, and Empty Values in XML

For a column named column_name, the NULL value, an empty string, and the string value ´NULL´ are distinguished from one another in the output generated by this option as follows.

The output from the mysql client when run using the --xml option also follows the preceding rules.

XML output from mysqldump includes the XML namespace, as shown here :

shell> mysqldump --xml -u root world City
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="world">
<table_structure name="City">
<field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
<field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
<field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
<field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
<field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
<key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
<options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
Index_length="43008" Data_free="0" Auto_increment="4080"
Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
Collation="latin1_swedish_ci" Create_options="" Comment="" />
</table_structure>
<table_data name="City">
<row>
<field name="ID">1</field>
<field name="Name">Kabul</field>
<field name="CountryCode">AFG</field>
<field name="District">Kabol</field>
<field name="Population">1780000</field>
</row>
...
<row>
<field name="ID">4079</field>
<field name="Name">Rafah</field>
<field name="CountryCode">PSE</field>
<field name="District">Rafah</field>
<field name="Population">92020</field>
</row>
</table_data>
</database>
</mysqldump>

Restoring

To restore a backup created with mysqldump, use the mysql client to import the dump, for example:

mysql db_name < backup-file.sql

Variables

You can also set the following variables (--variable-name=value) and boolean options {FALSE|TRUE} by using:

Examples

A common use of mysqldump is for making a backup of an entire database:

shell> mysqldump db_name > backup-file.sql

You can load the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data from one MariaDB server to another:

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option:

shell> mysqldump --all-databases > all_databases.sql

For InnoDB tables, mysqldump provides a way of making an online backup:

shell> mysqldump --all-databases --single-transaction all_databases.sql

This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the MariaDB server may get stalled until those statements finish. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MariaDB server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.

For point-in-time recovery (also known as “roll-forward,” when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log or at least know the binary log coordinates to which the dump corresponds:

shell> mysqldump --all-databases --master-data=2 > all_databases.sql

Or:

shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql

The --master-data and --single-transaction options can be used simultaneously, which provides a convenient way to make an online backup suitable for use prior to point-in-time recovery if tables are stored using the InnoDB storage engine.

See Also

• Mariabackup
• MariaDB Enterprise Backup