Use mysqldump to export a database

Use mysqldump to export a database

Mysqldump is a client program used by mysql to store databases. It mainly produces a series of SQL statements that can be encapsulated into a file. This package contains all the SQL commands required to reconstruct your DATABASE, such as CREATE DATABASE, CREATE TABLE, and INSERT. It can be used for lightweight fast migration or database recovery. It is a logical backup method for mysql databases. This article describes some important parameters of mysqldump and provides relevant examples for your reference.

Using mysqldump in Linux to back up a MySQL database as an SQL File

Use mysqldump in Linux to regularly back up MySQL Databases

Mysqldump missing-q Parameter causes MySQL to be killed by oom

Mysqldump and LVM logical volume Snapshot

MySQL backup solution --> (using mysqldump and binlog binary logs)

[MySQL] Using mysqldump for text backup

1. Obtain the help information of mysqldump.
[Root @ SZDB ~] # Mysqldump -- help | more
Mysqldump Ver 10.13 Distrib 5.6.12, for Linux (x86_64)

# Below are several common methods for MySQL dump call
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] -- databases [OPTIONS] DB1 [DB2 DB3. ..]
OR mysqldump [OPTIONS] -- all-databases [OPTIONS]

# If no table is specified or the --- database or -- all -- database option is used, the whole database is dumped.

-- Opt Same as -- add-drop-table, -- add-locks, -- create-options,
-- Quick, -- extended-insert, -- lock-tables, -- set-charset,
And -- disable-keys. Enabled by default, disable
-- Skip-opt.

-Q, -- quick Don't buffer query, dump directly to stdout.
(Defaults to on; use -- skip-quick to disable .)
# If the above two parameters are not used, all the content will be loaded into the memory before the dump result. For a large database dump, the performance will be seriously affected.
# By default, these two parameters are enabled. Some are similar to Oracle's direct write that bypasses PGA.

-- Skip-opt Disable -- opt. Disables -- add-drop-table, -- add-locks,
-- Create-options, -- quick, -- extended-insert,
-- Lock-tables, -- set-charset, and -- disable-keys.
# The skip-opt parameter is opposite to the first two parameters. It is loaded to the memory before the dump.

-- Compatible = name Change the dump to be compatible with a given mode.
Default tables are dumped in a format optimized
MySQL. Legal modes are: ansi, mysql323, mysql40,
Postgresql, oracle, mssql, db2, maxdb, no_key_options,
No_table_options, no_field_options. One can use several
Modes separated by commas. Note: Requires MySQL server
Version 4.1.0 or higher. This option is ignored
Earlier server versions.

# Generate output compatible with other database systems or old MySQL servers. It is used for cross-database and cross-version migration.
# The value can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, or no_field_options.
# If you want to use multiple values, use commas to separate them. This option cannot guarantee full compatibility with other database servers. For example, Oracle data type.

-- Compact Give less verbose output (useful for debugging). Disables
Structure comments and header/footer constructs. Enables
Options -- skip-add-drop-table -- skip-add-locks
-- Skip-comments -- skip-disable-keys -- skip-set-charset.
# This option makes the output file smaller. After it is enabled, it is used to use some skip items.

-B, -- databases Dump several databases. Note the difference in usage; in
This case no tables are given. All name arguments are
Regarded as database names. 'Use db_name; 'will be
Encoded ded in the output.
# This option exports all the name parameters of multiple databases at a time as the DATABASE name. More importantly, create database if not exists dbname is generated.

-- Default-character-set = name
Set the default character set.
# Set the character set of the exported script to UTF8 if not specified.

-- Flush-privileges Emit a flush privileges statement after dumping the mysql
Database. This option shocould be used any time the dump
Contains the mysql database and any other database that
Depends on the data in the mysql database for proper
Restore.
# We recommend that you use this option to generate the flush privileges statement when dump the mysql database and when it depends on the mysql database for restoration.

-F, -- flush-logs Flush logs file in server before starting dump. Note that
If you dump your databases at once (using the option
-- Databases = or -- all-databases), the logs will be
Flushed for each database dumped. The exception is when
Using -- lock-all-tables or -- master-data: in this case
The logs will be flushed only once, corresponding to
Moment all tables are locked. So if you want your dump
And the log flush to happen at the same exact moment you
Shocould use -- lock-all-tables or -- master-data
-- Flush-logs.
# Flush logs before starting dump. This method can be used for Incremental backup.

-D, -- no-data No row information.
# No data rows are output. Only the structure is exported.

-F, -- force Continue even if we get an SQL error.
# Force dump when an error occurs

-- Master-data [= #] This causes the binary log position and filename to be
Appended to the output. If equal to 1, will print it as
Change master command; if equal to 2, that command will
Be prefixed with a comment symbol. This option will turn
-- Lock-all-tables on, unless -- single-transaction is
Specified too (in which case a global read lock is only
Taken a short time at the beginning of the dump; don't
Forget to read about -- single-transaction below). In all
Cases, any action on logs will happen at the exact moment
Of the dump. Option automatically turns -- lock-tables
Off.
# Add a binary log location to the output. 1 indicates that the change master command is output, and 2 indicates that the change master command is output.

-R, -- routines Dump stored routines (functions and procedures ).
# Export functions, processes, and triggers. These are not exported by default.

-T, -- no-create-info
Don't write table creation info.
# Do not generate a table creation statement

-- Single-transaction
Creates a consistent snapshot by dumping all tables in
Single transaction. Works ONLY for tables stored in
Storage engines which support multiversioning (currently
Only InnoDB does); the dump is NOT guaranteed to be
Consistent for other storage engines. While
-- Single-transaction dump is in process, to ensure
Valid dump file (correct table contents and binary log
Position), no other connection shocould use the following
Statements: alter table, drop table, rename table,
Truncate table, as consistent snapshot is not isolated
From them. Option automatically turns off -- lock-tables.
# Create consistent snapshots for the innodb engine only
# Other operations are not allowed: alter table, drop table, rename table, truncate table, disable -- lock-tables.

-W, -- where = name Dump only selected records. Quotes are mandatory.
# Use the where clause to export only matching records
# Author: Leshami
# Blog:

1 2 Next Page