As shown in the link above, the content of the entire database is exported, can it be exported with conditions? Of course you can!
Command:
Parameter analysis:
- -h: Specify the database address
- --port: Specify the database port, if it is the default port 3306, it can be omitted
- -u: account number
- -p: password
- db_name: This refers to which database to export
- tab_name: This refers to which table to export
- --where: Data filter criteria
- --no-create-info: only exports data without adding a CREATE TABLE statement.
At the end, follow the path to the location where you need to export the storage.
Illustrate:
-t: Only guide data
--where: condition
--triggers=false: Do not guide triggers
--replace: Replace INSERT INTO with REPLACE INTO
Parameters:
--all-databases , -A
Export all databases.
mysqldump -uroot -p --all-databases
--all-tablespaces , -Y
Export all tablespaces.
mysqldump -uroot -p --all-databases --all-tablespaces
--no-tablespaces , -y
No tablespace information is exported.
mysqldump -uroot -p --all-databases --no-tablespaces
--add-drop-database
Add a drop database statement before each database is created.
mysqldump -uroot -p --all-databases --add-drop-database
--add-drop-table
Add a drop table statement before each data table is created. (Default is open, use --skip-add-drop-table to cancel option)
mysqldump -uroot -p --all-databases (add drop statement by default)
mysqldump -uroot -p --all-databases –skip-add-drop-table (undrop statement)
--add-locks
Add LOCK TABLES before each table export and UNLOCK TABLE afterwards. (default is on, use --skip-add-locks to cancel option)
mysqldump -uroot -p --all-databases (LOCK statement is added by default)
mysqldump -uroot -p --all-databases –skip-add-locks (unlock statement)
--allow-keywords
Allows the creation of column names that are keywords. This is done by prefixing the table name to each column name.
mysqldump -uroot -p --all-databases --allow-keywords
--apply-slave-statements
Add 'STOP SLAVE' before 'CHANGE MASTER' and 'START SLAVE' at the end of the export.
mysqldump -uroot -p --all-databases --apply-slave-statements
--character-sets-dir
directory of character set files
mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets
--comments
Additional note information. It is on by default, and can be canceled with --skip-comments
mysqldump -uroot -p --all-databases (default record comments)
mysqldump -uroot -p --all-databases --skip-comments (uncomment)
--compatible
The exported data will be compatible with other databases or older versions of MySQL. The values can be ANSI, MySQL323, MySQL40, PostgreSQL, Oracle, MSSQL, DB2, MaxDB, no_key_options, no_tables_options, no_field_options, etc.,
To use several values, space them apart with commas. It is not guaranteed to be fully compatible, but as much as possible.
mysqldump -uroot -p --all-databases --compatible=ansi
--compact
Export less output information (for debugging). Remove structures such as annotations and heads and tails. The option is available: --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
mysqldump -uroot -p --all-databases --compact
--complete-insert, -c
Use the full insert statement (including the column name). This can improve insertion efficiency, but it may be affected by max_allowed_packet parameters and cause insertion failures.
mysqldump -uroot -p --all-databases --complete-insert
--compress, -C
Enable compression to pass all information between the client and the server
mysqldump -uroot -p --all-databases --compress
--create-options, -a
Include all MySQL feature options in the CREATE TABLE statement. (Default is on)
mysqldump -uroot -p --all-databases
--databases, -B
Export several databases. All name parameters after the parameter are treated as database names.
mysqldump -uroot -p --databases test mysql
--debug
Output debug information for debugging. The default value is: d:t:o,/tmp/mysqldump.trace
mysqldump -uroot -p --all-databases --debug
mysqldump -uroot -p --all-databases --debug=” d:t:o,/tmp/debug.trace”
--debug-check
Check the memory and open file instructions and exit.
mysqldump -uroot -p --all-databases --debug-check
--debug-info
Output debug information and exit
mysqldump -uroot -p --all-databases --debug-info
--default-character-set
Set the default character set, the default value is utf8
mysqldump -uroot -p --all-databases --default-character-set=latin1
--delayed-insert
INSERT DELAYED to export data
mysqldump -uroot -p --all-databases --delayed-insert
--delete-master-logs
master backup deletes logs. This parameter will automatically activate --master-data.
mysqldump -uroot -p --all-databases --delete-master-logs
--disable-keys
For each table, use /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; The statement references the INSERT statement. This allows for faster import of dumped files, as it creates an index after inserting all the rows. This option is only available for MyISAM tables, which defaults to the open state.
mysqldump -uroot -p --all-databases
--dump-slave
This option will cause the main binlog location and file name to be appended to the file from which the exported data is appended. When set to 1, the CHANGE MASTER command will be used to output to the data file. When set to 2, add explanatory information before the command. This option will turn on --lock-all-tables unless --single-transaction is specified. This option automatically turns off the --lock-tables option. The default value is 0.
mysqldump -uroot -p --all-databases --dump-slave=1
mysqldump -uroot -p --all-databases --dump-slave=2
--events, -E
Export events.
mysqldump -uroot -p --all-databases --events
--extended-insert, -e
Use the INSERT syntax with multiple VALUES columns. This makes exporting files smaller and speeds up imports. Default to open state, use --skip-extended-insert to cancel option.
mysqldump -uroot -p --all-databases
mysqldump -uroot -p --all-databases--skip-extended-insert (unoption)
--fields-terminated-by
Ignore the given fields in the export file. Used with the --tab option, not for the --databases and --all-databases options
mysqldump -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#”
--fields-enclosed-by
The individual fields in the output file are wrapped with the given characters. Used with the --tab option, not for the --databases and --all-databases options
mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#”
--fields-optionally-enclosed-by
The fields in the output file are selectively wrapped with the given characters. Used with the --tab option, not for the --databases and --all-databases options
mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#” --fields-optionally-enclosed-by =”#”
--fields-escaped-by
The individual fields in the output file ignore the given characters. Used with the --tab option, not for the --databases and --all-databases options
mysqldump -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”
--flush-logs
Refresh the logs before starting the export.
Please note: if you export multiple databases at once (using the --databases or --all-databases option), the logs will be refreshed on a database-by-database basis. Except by using --lock-all-tables or --master-data. In this case, the log will be refreshed once and the corresponding table will be locked at the same time. Therefore, if you intend to export and flush logs at the same time, you should use --lock-all-tables or --master-data and --flush-logs.
mysqldump -uroot -p --all-databases --flush-logs
--flush-privileges
After exporting the mysql database, issue a FLUSH PRIVILEGES statement. For proper recovery, this option should be used for exporting MySQL database and dependent MySQL database data anytime.
mysqldump -uroot -p --all-databases --flush-privileges
--force
Ignore SQL errors that occur during export.
mysqldump -uroot -p --all-databases --force
--help
Display help information and exit.
mysqldump --help
--hex-blob
Export binary string fields using hex format. This option must be used if binary data is available. The affected field types are BINARY, VARBINARY, and BLOB.
mysqldump -uroot -p --all-databases --hex-blob
--host, -h
Host information that needs to be exported
mysqldump -uroot -p --host=localhost --all-databases
--ignore-table
The specified table is not exported. When specifying that multiple tables are ignored, it needs to be repeated multiple times, one table at a time. Each table must specify both the database and the table name. For example: --ignore-table=database.table1 --ignore-table=database.table2 ......
mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
--include-master-host-port
'CHANGE MASTER TO..' generated by --dump-slave Add 'MASTER_HOST=<host>,MASTER_PORT=<port>' to the statement
mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port
--insert-ignore
Use the INSERT IGNORE statement when inserting a line.
mysqldump -uroot -p --host=localhost --all-databases --insert-ignore
--lines-terminated-by
Each line of the output file is divided by a given string. Used with the --tab option, not for the --databases and --all-databases options.
mysqldump -uroot -p --host=localhost test test --tab=”/tmp/mysql” --lines-terminated-by=”##”
--lock-all-tables, -x
Submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock, and the --single-transaction and --lock-tables options are automatically turned off.
mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables
--lock-tables, -l
Lock all tables before starting the export. Lock the table with READ LOCAL to allow MyISAM tables to insert in parallel. For tables that support transactions, such as InnoDB and BDB,--single-transaction is a better option because it doesn't require locking tables at all.
Note that when exporting multiple databases,-- lock-tables locks tables for each database separately. Therefore, this option does not guarantee logical consistency between databases for tables in the exported file. The export status of different database tables can be completely different.
mysqldump -uroot -p --host=localhost --all-databases --lock-tables
--log-error
Attach warnings and error messages to a given file
mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err
--master-data
This option appends the location and file name of the binlog to the output file. If it is 1, the CHANGE MASTER command will be output; If it is 2, add a comment before the output CHANGE MASTER command. The option will turn on the --lock-all-tables option unless --single- transaction is also specified (in this case, the global read lock gets a short time when the export starts; For other content, please refer to the --single-transaction option below). This option is automatically turned off --lock-tables option.
mysqldump -uroot -p --host=localhost --all-databases --master-data=1;
mysqldump -uroot -p --host=localhost --all-databases --master-data=2;
--max_allowed_packet
The maximum packet length sent and accepted by the server.
mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240
--net_buffer_length
Cache size for TCP/IP and socket connections.
mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024
--no-autocommit
Use the autocommit/commit statement to wrap the table.
mysqldump -uroot -p --host=localhost --all-databases --no-autocommit
--no-create-db, -n
Only export the data without adding the CREATE DATABASE statement.
mysqldump -uroot -p --host=localhost --all-databases --no-create-db
--no-create-info, -t
Only export the data without adding the CREATE TABLE statement.
mysqldump -uroot -p --host=localhost --all-databases --no-create-info
--no-data, -d
No data is exported, only the database table structure is exported.
mysqldump -uroot -p --host=localhost --all-databases --no-data
--no-set-names, -N
Equivalent to --skip-set-charset
mysqldump -uroot -p --host=localhost --all-databases --no-set-names
--opt
Equivalent to --add-drop-table, --add-locks, --create- options, --quick, --extended-insert, --lock-tables, --set- charset, --disable-keys This option is enabled by default, and can be disabled with --skip-opt.
mysqldump -uroot -p --host=localhost --all-databases --opt
--order-by-primary
If a primary key exists, or the first unique key, sort the records for each table. This works when exporting MyISAM tables to InnoDB tables, but makes the export process take a long time.
mysqldump -uroot -p --host=localhost --all-databases --order-by-primary
--password, -p
Connect the database password
--pipe (available for windows)
Connect to mysql using a named pipeline
mysqldump -uroot -p --host=localhost --all-databases --pipe
--port, -P
Connect to the database port number
--protocol
Connection protocols used, including: tcp, socket, pipe, memory.
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
--quick, -q
Export directly to standard output without buffering queries. Default is on, use --skip-quick to cancel this option.
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-quick
--quote-names,-Q
Use (') to cause table and column names. Default is on, use --skip-quote-names to cancel this option.
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names
--replace
Use REPLACE INTO to replace INSERT INTO.
mysqldump -uroot -p --host=localhost --all-databases --replace
--result-file, -r
Output directly to the specified file. This option should be used on systems that use newline wraps (e.g. DOS, Windows). This option ensures that only one line is used.
mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt
--routines, -R
Export stored procedures as well as custom functions.
mysqldump -uroot -p --host=localhost --all-databases --routines
--set-charset
Add 'SET NAMES default_character_set' to the output file. Default to open state, use --skip-set-charset to close the option.
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset
--single-transaction
This option submits a BEGIN SQL statement before exporting the data, which does not block any application and guarantees a consistent state of the database at the time of export. It only works with multi-version storage engines, only InnoDB. This option and the --lock-tables option are mutually exclusive, because LOCK TABLES makes any pending transaction implicitly committed. If you want to export a large table, you should use the --quick option in combination.
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
--dump-date
Add the export time to the output file. Default to open state, use --skip-dump-date to close option.
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date
--skip-opt
Disable the –opt option.
mysqldump -uroot -p --host=localhost --all-databases --skip-opt
--socket,-S
Specify the socket file location that connects to mysql, and the default path is /tmp/mysql.sock
mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock
--tab,-T
Create a tab-split text file for each table in a given path. Note: Only for mysqldump and mysqld servers running on the same machine.
mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"
--tables
Override the --databases (-B) parameter to specify the table name to be exported.
mysqldump -uroot -p --host=localhost --databases test --tables test
--triggers
Export triggers. This option is enabled by default, disable it with --skip-triggers.
mysqldump -uroot -p --host=localhost --all-databases --triggers
--tz-utc
Set the time zone TIME_ZONE='+00:00' at the top of the export to ensure the correctness of the TIMESTAMP data exported in different time zones or when the data is moved to a different time zone.
mysqldump -uroot -p --host=localhost --all-databases --tz-utc
--user, -u
Specify the username of the connection.
--verbose, --v
Output multiple platform information.
--version, -V
Output mysqldump version information and exit
--where, -w
Only the records selected for the given WHERE condition are dumped. Note that if the condition contains a command interpreter-specific space or character, be sure to reference the condition.
mysqldump -uroot -p --host=localhost --all-databases --where=” user=’root’”
--xml, -X
Export XML format.
mysqldump -uroot -p --host=localhost --all-databases --xml
--plugin_dir
A directory of client-side plugins for compatibility with different plugin versions.
mysqldump -uroot -p --host=localhost --all-databases --plugin_dir=”/usr/local/lib/plugin”
--default_auth
Client plugins use permissions by default.
mysqldump -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/<PLUGIN>”
|
Posted on 4/26/2019 4:07:23 PM
|
|
|
|
Posted on 6/1/2019 2:16:47 AM
|
Landlord|
Posted on 2/7/2021 10:01:20 AM
|
