Skip to main content

MySQLDump

Commands​

mysqldump --single-transaction --host=abc.ap-south-1.rds.amazonaws.com -u username --flush-logs --master-data=2 -p user_kyc > backup.sql

-- host=abc.ap-south-1.rds.amazonaws.com - This is the Cluster level Hostname in which Master data concept will not able to work
-- while taking backup of rds with mysqldump master-data=2 will not work as it is no longer with the mysql 8
-- Also you are missing one things is this command which is --set-gtid-purged=OFF Please check for this

-- dump via mysqlworkbench
mysqldump --defaults-file="/var/folders/9j/l_15x5sx6c133kcr5vybw54m0000gn/T/tmpco89tdwc/extraparams.cnf"  --host=127.0.0.1 --port=1053 --default-character-set=utf8 --user=root --protocol=tcp --skip-triggers "schema_name"

-- dump specific table using mysqldump
mysqldump --host=127.0.0.1 --port=1053 --default-character-set=utf8 --user=devops_read_user --protocol=tcp --lock-tables=FALSE --skip-triggers schema_name table_name > dump_file.sql -p

  • Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

  • Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.

  • In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.

  • You need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

To make a backup of an entire database​

mysqldump db_name > backup-file.sql

To load the dump file back into the server​

mysql db_name < backup-file.sql

Another way to reload the dump file​

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

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

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

You can dump several databases with one command​

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

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

mysqldump --all-databases > all_databases.sql

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

mysqldump --all-databases --master-data --single-transaction > all_databases.sql

Or, in MySQL 8.0.26 and later​

mysqldump --all-databases --source-data --single-transaction > all_databases.sql

Change max_execution_time​

mysql -u $DB_USER -p $DB_PASSWORD -h $DB_HOST -e "SET GLOBAL max_execution_time = 0;"

-- Perform the mysqldump
mysqldump -u $DB_USER -p $DB_PASSWORD -h $DB_HOST $DB_NAME > /path/to/your/backup.sql

-- Revert max_execution_time to its original value
mysql -u $DB_USER -p $DB_PASSWORD -h $DB_HOST -e "SET GLOBAL max_execution_time = $ORIGINAL_MAX_EXECUTION_TIME;"

6.5.4 mysqldump — A Database Backup Program

mysqldump Options​

Option NameDescriptionIntroducedDeprecated
--add-drop-databaseAdd DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-tableAdd DROP TABLE statement before each CREATE TABLE statement
--add-drop-triggerAdd DROP TRIGGER statement before each CREATE TRIGGER statement
--add-locksSurround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databasesDump all tables in all databases
--allow-keywordsAllow creation of column names that are keywords
--apply-replica-statementsInclude STOP REPLICA prior to CHANGE REPLICATION SOURCE TO statement and START REPLICA at end of output8.0.26
--apply-slave-statementsInclude STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output8.0.26
--bind-addressUse specified network interface to connect to MySQL Server
--character-sets-dirDirectory where character sets are installed
--column-statisticsWrite ANALYZE TABLE statements to generate statistics histograms
--commentsAdd comments to dump file
--compactProduce more compact output
--compatibleProduce output that is more compatible with other database systems or with older MySQL servers
--complete-insertUse complete INSERT statements that include column names
--compressCompress all information sent between client and server8.0.18
--compression-algorithmsPermitted compression algorithms for connections to server8.0.18
--create-optionsInclude all MySQL-specific table options in CREATE TABLE statements
--databasesInterpret all name arguments as database names
--debugWrite debugging log
--debug-checkPrint debugging information when program exits
--debug-infoPrint debugging information, memory, and CPU statistics when program exits
--default-authAuthentication plugin to use
--default-character-setSpecify default character set
--defaults-extra-fileRead named option file in addition to usual option files
--defaults-fileRead only named option file
--defaults-group-suffixOption group suffix value
--delete-master-logsOn a replication source server, delete the binary logs after performing the dump operation8.0.26
--delete-source-logsOn a replication source server, delete the binary logs after performing the dump operation8.0.26
--disable-keysFor each table, surround INSERT statements with statements to disable and enable keys
--dump-dateInclude dump date as "Dump completed on" comment if --comments is given
--dump-replicaInclude CHANGE REPLICATION SOURCE TO statement that lists binary log coordinates of replica's source8.0.26
--dump-slaveInclude CHANGE MASTER statement that lists binary log coordinates of replica's source8.0.26
--enable-cleartext-pluginEnable cleartext authentication plugin
--eventsDump events from dumped databases
--extended-insertUse multiple-row INSERT syntax
--fields-enclosed-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-escaped-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-optionally-enclosed-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-terminated-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--flush-logsFlush MySQL server log files before starting dump
--flush-privilegesEmit a FLUSH PRIVILEGES statement after dumping mysql database
--forceContinue even if an SQL error occurs during a table dump
--get-server-public-keyRequest RSA public key from server
--helpDisplay help message and exit
--hex-blobDump binary columns using hexadecimal notation
--hostHost on which MySQL server is located
--ignore-errorIgnore specified errors
--ignore-tableDo not dump given table
--include-master-host-portInclude MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave8.0.26
--include-source-host-portInclude SOURCE_HOST and SOURCE_PORT options in CHANGE REPLICATION SOURCE TO statement produced with --dump-replica8.0.26
--insert-ignoreWrite INSERT IGNORE rather than INSERT statements
--lines-terminated-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--lock-all-tablesLock all tables across all databases
--lock-tablesLock all tables before dumping them
--log-errorAppend warnings and errors to named file
--login-pathRead login path options from .mylogin.cnf
--master-dataWrite the binary log file name and position to the output8.0.26
--max-allowed-packetMaximum packet length to send to or receive from server
--mysqld-long-query-timeSession value for slow query threshold8.0.30
--net-buffer-lengthBuffer size for TCP/IP and socket communication
--network-timeoutIncrease network timeouts to permit larger table dumps
--no-autocommitEnclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-dbDo not write CREATE DATABASE statements
--no-create-infoDo not write CREATE TABLE statements that re-create each dumped table
--no-dataDo not dump table contents
--no-defaultsRead no option files
--no-set-namesSame as --skip-set-charset
--no-tablespacesDo not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
--optShorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
--order-by-primaryDump each table's rows sorted by its primary key, or by its first unique index
--passwordPassword to use when connecting to server
--password1First multifactor authentication password to use when connecting to server8.0.27
--password2Second multifactor authentication password to use when connecting to server8.0.27
--password3Third multifactor authentication password to use when connecting to server8.0.27
--pipeConnect to server using named pipe (Windows only)
--plugin-authentication-kerberos-client-modePermit GSSAPI pluggable authentication through the MIT Kerberos library on Windows8.0.32
--plugin-dirDirectory where plugins are installed
--portTCP/IP port number for connection
--print-defaultsPrint default options
--protocolTransport protocol to use
--quickRetrieve rows for a table from the server a row at a time
--quote-namesQuote identifiers within backtick characters
--replaceWrite REPLACE statements rather than INSERT statements
--result-fileDirect output to a given file
--routinesDump stored routines (procedures and functions) from dumped databases
--server-public-key-pathPath name to file containing RSA public key
--set-charsetAdd SET NAMES default_character_set to output
--set-gtid-purgedWhether to add SET @@GLOBAL.GTID_PURGED to output
--shared-memory-base-nameShared-memory name for shared-memory connections (Windows only)
--show-create-skip-secondary-engineExclude SECONDARY ENGINE clause from CREATE TABLE statements8.0.18
--single-transactionIssue a BEGIN SQL statement before dumping data from server
--skip-add-drop-tableDo not add a DROP TABLE statement before each CREATE TABLE statement
--skip-add-locksDo not add locks
--skip-commentsDo not add comments to dump file
--skip-compactDo not produce more compact output
--skip-disable-keysDo not disable keys
--skip-extended-insertTurn off extended-insert
--skip-generated-invisible-primary-keyDo not include generated invisible primary keys in dump file8.0.30
--skip-optTurn off options set by --opt
--skip-quickDo not retrieve rows for a table from the server a row at a time
--skip-quote-namesDo not quote identifiers
--skip-set-charsetDo not write SET NAMES statement
--skip-triggersDo not dump triggers
--skip-tz-utcTurn off tz-utc
--socketUnix socket file or Windows named pipe to use
--source-dataWrite the binary log file name and position to the output8.0.26
--ssl-caFile that contains list of trusted SSL Certificate Authorities
--ssl-capathDirectory that contains trusted SSL Certificate Authority certificate files
--ssl-certFile that contains X.509 certificate
--ssl-cipherPermissible ciphers for connection encryption
--ssl-crlFile that contains certificate revocation lists
--ssl-crlpathDirectory that contains certificate revocation-list files
--ssl-fips-modeWhether to enable FIPS mode on client side8.0.34
--ssl-keyFile that contains X.509 key
--ssl-modeDesired security state of connection to server
--ssl-session-dataFile that contains SSL session data8.0.29
--ssl-session-data-continue-on-failed-reuseWhether to establish connections if session reuse fails8.0.29
--tabProduce tab-separated data files
--tablesOverride --databases or -B option
--tls-ciphersuitesPermissible TLSv1.3 ciphersuites for encrypted connections8.0.16
--tls-versionPermissible TLS protocols for encrypted connections
--triggersDump triggers for each dumped table
--tz-utcAdd SET TIME_ZONE='+00:00' to dump file
--userMySQL user name to use when connecting to server
--verboseVerbose mode
--versionDisplay version information and exit
--whereDump only rows selected by given WHERE condition
--xmlProduce XML output
--zstd-compression-levelCompression level for connections to server that use zstd compression8.0.18

Restrictions​

  • mysqldump does not dump the performance_schema or sys schema by default. To dump any of these, name them explicitly on the command line. You can also name them with the --databases option. For performance_schema, also use the --skip-lock-tables option.
  • mysqldump does not dump the INFORMATION_SCHEMA schema.
  • mysqldump does not dump InnoDB CREATE TABLESPACE statements.
  • mysqldump does not dump the NDB Cluster ndbinfo information database.
  • mysqldump includes statements to recreate the general_log and slow_query_log tables for dumps of the mysql database. Log table contents are not dumped.

Other tools​