Mysqldump option file

These are the source server coordinates from which the replica should start replicating. Inconsistencies in the sequence of transactions from the relay log which have been executed can cause the wrong position to be used.

In addition, specifiying this option causes the --master-data option to be overridden, if used, and effectively ignored. The option value is handled the same way as for --master-data setting no value or 1 causes a CHANGE MASTER TO statement to be written to the dump, setting 2 causes the statement to be written but encased in SQL comments and has the same effect as --master-data in terms of enabling or disabling other options and in how locking is handled.

This option causes mysqldump to stop the replica SQL thread before the dump and restart it again after. In conjunction with --dump-slave , the --apply-slave-statements and --include-master-host-port options can also be used. Use this option to dump a source replication server to produce a dump file that can be used to set up another server as a replica of the source.

These are the source server coordinates from which the replica should start replicating after you load the dump file into the replica. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded.

If no option value is specified, the default value is 1. The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables , unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump see the description for --single-transaction.

In all cases, any action on logs happens at the exact moment of the dump. It is also possible to set up a replica by dumping an existing replica of the source, using the --dump-slave option, which overrides --master-data and causes it to be ignored if both options are used.

This option may also cause a statement to be written to the output that disables binary logging while the dump file is being reloaded. The following table shows the permitted option values. The default value is AUTO.

Use ON if the intention is to deploy a new replication replica using only some of the data from the dumped server. Use OFF if the intention is to repair a table by copying it within a topology. Use OFF if the intention is to copy a table between replication topologies that are disjoint and for them to remain so.

The --set-gtid-purged option has the following effect on binary logging when the dump file is reloaded:. Also be aware that loading a dump file from a server with GTIDs enabled, into another server with GTIDs enabled, causes different transaction identifiers to be generated.

The following options specify how to represent the entire dump file or certain kinds of data in the dump file. They also control whether certain optional information is written to the dump file.

Produce more compact output. This option enables the --skip-add-drop-table , --skip-add-locks , --skip-comments , --skip-disable-keys , and --skip-set-charset options. Produce output that is more compatible with other database systems or with older MySQL servers.

To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5. This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. Dump binary columns using hexadecimal notation for example, 'abc' becomes 0x It can be disabled with --skip-quote-names , but this option should be given after any option such as --compatible that may enable --quote-names.

Direct output to the named file. The result file is created and its previous contents overwritten, even if an error occurs while generating the dump.

Produce tab-separated text-format data files. The option value is the directory in which to write the files. This option should be used only when mysqldump is run on the same machine as the mysqld server. By default, the. The format can be specified explicitly using the --fields- xxx and --lines-terminated-by options. Column values are converted to the character set specified by the --default-character-set option. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change if the servers are in different time zones.

To disable it, use --skip-tz-utc. The output from the mysql client when run using the --xml option also follows the preceding rules. The following options control which kinds of schema objects are written to the dump file: by category, such as triggers or events; by name, for example, choosing which databases and tables to dump; or even filtering rows from the table data using a WHERE clause.

Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line. Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names. Also use the --skip-lock-tables option.

Include Event Scheduler events for the dumped databases in the output. However, these statements do not include attributes such as the event creation and modification timestamps, so when the events are reloaded, they are created with timestamps equal to the reload time. If you require events to be created with their original timestamp attributes, do not use --events. Instead, dump and reload the contents of the mysql. Ignore the specified errors. The option value is a list of comma-separated error numbers specifying the errors to ignore during mysqldump execution.

If the --force option is also given to ignore all errors, --force takes precedence. Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times. This option also can be used to ignore views. Do not write any table row information that is, do not dump table contents. Include stored routines procedures and functions for the dumped databases in the output. However, these statements do not include attributes such as the routine creation and modification timestamps, so when the routines are reloaded, they are created with timestamps equal to the reload time.

If you require routines to be created with their original timestamp attributes, do not use --routines. Override the --databases or -B option. Include triggers for each dumped table in the output. This option is enabled by default; disable it with --skip-triggers. Multiple triggers are permitted. It can be disabled with --skip-quote-names , but this option should be given after any option such as --compatible that may enable --quote-names. The result file is created and its previous contents overwritten, even if an error occurs while generating the dump.

However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time. If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql. Before that, stored routines are not dumped. This means that before 5. If you require routines to be re-created with their original definer, dump and load the contents of the mysql.

It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications. When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

You should always use NDB backup and restore instead. To dump large tables, you should combine the --single-transaction option with --quick. The option value is the directory in which to write the files.

Tag Description Note This option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify. By default, the. The format can be specified explicitly using the --fields- xxx and --lines-terminated-by options.

Prior to 5. In effect, there is no character set conversion. If a table contains columns in several character sets, the output data file will as well and you may not be able to reload the file correctly. This option is enabled by default; disable it with --skip-triggers. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change if the servers are in different time zones.

To disable it, use --skip-tz-utc. Print more information about what the program does. Quotes around the condition are mandatory if it contains spaces or other characters that are special to your command interpreter. Beginning with MySQL 5. The maximum is 1GB. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MySQL server receives are short in terms of execution time , the initial lock period should not be noticeable, even with many updates.

For more information on making backups, see Section 6. If you encounter problems backing up views, please read the section that covers restrictions on views which describes a workaround for backing up views when this fails due to insufficient privileges. See Section E. All rights reserved. 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 select the effect of --opt except for some features, use the --skip option for each feature.

To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.. Tag Description. This is the same as using the --databases option and naming all the databases on the command line.. Option Description --print-defaults Print the program argument list and exit. Available starting with MariaDB For example, mysqldump.

This is useful for options like socket and port, which is common between the server and the clients. The maximum is 1GB. Comments loading Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB.

The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party. Dump all the databases. Allow creation of column names that are keywords. Dump system versioned table as of specified timestamp. Directory for character set files. Write additional information in the dump file such as program version, server version, and host.

Give less verbose output useful for debugging. Change the dump to be compatible with a given mode. By default S3 tables are ignored. Dump several databases. If using a debug version of MariaDB, write a debugging log.

Set the default character set to name. Read the file name after the global files are read. Only read default options from the given file name. Also read groups with a suffix of str. If the --comments option and this option are given, mysqldump produces a comment at the end of the dump of the following form: -- Dump completed on DATE However, the date causes dump files taken at different times to appear to be different, even if the data are otherwise identical.

Used for producing a dump file from a replica server that can be used to set up another replica server with the same primary.

Include Event Scheduler events for the dumped databases in the output. Fields in the output file are terminated by the given string. Fields in the output file are enclosed by the given character. Fields in the output file are optionally enclosed by the given character. Fields in the output file are escaped by the given character. Removed in MariaDB 5. Flush the MariaDB server log files before starting the dump. Continue even if an SQL error occurs during a table dump. Used together with --master-data and --dump-slave to more conveniently set up a new GTID replica.

Do not dump the specified database. Do not dump the specified table. Do not dump the specified table data only the structure. Lines in the output file are terminated by the given string. Lock all tables across all databases. For each dumped database, lock all tables to be dumped before dumping them. When restoring the dump, the server will, if logging is turned on, log the queries to the general and slow query log.

Causes the binary log position and filename to be appended to the output, useful for dumping a primary replication server to produce a dump file that can be used to set up another server as a replica of the primary. Do not write any table row information that is, do not dump table contents. Do not dump rows for engines that manage external data i. This option is shorthand. Sorts each table's rows by primary key, or first unique key, if such a key exists.

The password to use when connecting to the server. On Windows, connect to the server via a named pipe. This option is useful for dumping large tables. Direct output to a given file. Include stored routines procedures and functions for the dumped databases in the output. Change the value of a variable.

Shared-memory name to use for Windows connections using shared memory to a local server started with the --shared-memory option. Disable the --opt option disables --add-drop-table , --add-locks , --create-options , --quick , --extended-insert , --lock-tables , --set-charset , and --disable-keys. For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

Enables TLS. Although here I'm just another member of the family. Backup your databases with mysqldump. Tags debian Linux server sql Unix. Comment: Please enter your comment! Latest articles. The choose command in Linux January 13, Backup your databases with mysqldump January 12, MySql Contains Podman January 11, Install a Redis server on Debian 11 January 11, The choose command in Linux Linux Angelo - January 13, 0.

In this post, you will learn how to use choose command in Linux.