Mysql 数据泵的使用方法方式说明

mysqldump 简介

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

mysqldump 在备份对应表和对应视图|触发器的时候, 需要对应的权限

最常用的导出语句:

$ mysqldump -h192.168.x.x -uroot -p123123 db_name [table_name1, table_name2] > gomyck.sql

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.

简单翻译: 使用 –opt –compact 可以代表一些选项默认开启, 不需要在额外的声明

sql 还原


# A common use of mysqldump is for making a backup of an entire database
$ mysqldump db_name > backup-file.sql

# You can load the dump file back into the server like this:                                  
$ mysql db_name < backup-file.sql

# Or like this:                                    
$ mysql -e "source /path-to-backup/backup-file.sql" db_name

Table 4.13 mysqldump Options

optiondescribe
–add-drop-databaseAdd DROP DATABASE statement before each CREATE DATABASE statement
–add-drop-tableAdd DROP TABLE statement before each CREATE TABLE 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-slave-statementsInclude STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output
–bind-addressUse specified network interface to connect to MySQL Server
–character-sets-dirDirectory where character sets are installed
–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 server
–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
–delayed-insertWrite INSERT DELAYED statements rather than INSERT statements
–delete-master-logsOn a master replication server, delete the binary logs after performing the dump operation
–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-slaveInclude CHANGE MASTER statement that lists binary log coordinates of slave’s master
–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
–first-slaveDeprecated; use –lock-all-tables instead
–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
–helpDisplay help message and exit
–hex-blobDump binary columns using hexadecimal notation
–hostHost on which MySQL server is located
–ignore-tableDo not dump given table
–include-master-host-portInclude MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with –dump-slave 5.5.3
–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
–master-dataWrite the binary log file name and position to the output
–max-allowed-packetMaximum packet length to send to or receive from server
–net-buffer-lengthBuffer size for TCP/IP and socket communication
–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
–pipeConnect to server using named pipe (Windows only)
–plugin-dirDirectory where plugins are installed
–portTCP/IP port number for connection
–print-defaultsPrint default options
–protocolConnection 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
–set-charsetAdd SET NAMES default_character_set to output
–shared-memory-base-nameName of shared memory to use for shared-memory connections
–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-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
–sslEnable connection encryption
–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-keyFile that contains X.509 key
–ssl-modeDesired security state of connection to server 5.5.49
–ssl-verify-server-certVerify host name against server certificate Common Name identity
–tabProduce tab-separated data files
–tablesOverride –databases or -B option
–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


相关文章:
⤧  上一篇 Js prototype 与 _proto_ 的关系 ⤧  下一篇 CentOS 搭建 ftp 服务 (VSFTP)