mysqldump options

Tags: mysql mysqldumpWhen I recently looked at the help documentation for the MySQL lock table, I found a bit of a problem with the lock table before using Mysqldump Backup, which was the mysql-a-bse "flush tables with read lock" when doing the backup script. Back to the official document know that this is invalid, because the flush tables with read lock, the statement after the MySQL session exit will be implicitly executed unlock tables, then flush

Author/Translator: ye Jinrong (Email: imysql@imysql.cn), source: http://imysql.cn, reproduced please indicate/translator and source, and cannot be used for commercial purposes, offenders must investigate.0. Preface This article mainly discussesMysqldumpAnd compare themMk-parralel-dumpTo provide more help for the selection of backup methods.1. mysqldump First, let's take a look.MysqldumpThe actual working method of several main parameters.Main

1. mysqldump First, let's take a look.MysqldumpThe actual working method of several main parameters.Main mysqldump options 1.-Q It's very simple. I just added SQL _no_cacheTo ensure that the data in the cache is not read. 081022 17:39:33 7 Connect root@localhost on7 Query /*!40100 SET @@SQL_MODE='' */7 Init DB yejr7 Query SHOW TABLES LI

1. Backup command:# mysqldump-u ' root '-P ' 12345 '-h 127.0.0.1 \-Q--single-transaction-r-e \--triggers--default-character-set=utf8 \-B Test | gzip > Test. ' Date +%f_%t '. sql2. Backup script:# cat/dbback.sh#!/bin/bashDatetime= ' Date +%f_%t 'Dbname= (MySQL test)echo $datetime >/backdb.logFor DB in ${dbname[*]}Doecho "############################## db back $db begin time is $ (date +%f_%t) ##############################" >>/b Ackdb.logMysqldump-u '

-A--all-databases back up all databases-B--databases backing up the specified database, you can specify multiple library names at the same time-D backs up the table structure only-T backs up table data only-T--tab separates table structure from table data in plain text format, not SQL statement, followed by path-F Refresh the Binlog log to generate a new Mysql-bin file-MASTER-DATA=[1|2] 1 means that the log file and pos,2 are recorded as annotations, and 2 is commonly used for backup.-X--lock-al

1. Various usage instructions A. The simplest use: Copy Code code as follows: Mysqldump-uroot-ppassword [Database name] > [Dump file] The above command backs up the specified database to a dump file (dump file), for example: Copy Code code as follows: mysqldump-uroot-p123 Test > Test.dump The generated Test.dump file contains the Build table statement, wh

keys */; statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables. -- Extended-insert = true | false By default, mysqldump enables the -- complete-insert mode, so if you do not want to use it, use this option to set its value to false. -- Hex-blob Export binary string fields in hexadecimal format. This option is required if binary da

table enable keys */; Statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables. -- Extended-insert = true | false By default, mysqldump enables the -- complete-insert mode, so if you do not want to use it, use this option to set its value to false. -- Hex-blob Export binary string fields in hexadecimal format. This option is required

-databases -- compress -- Create-options,- The create table statement contains all MySQL feature options. (Enabled by default) Mysqldump-uroot-p -- all-databases -- Databases,-B Export several databases. All name parameters following the parameter are considered as the database name. Mysqldump-uroot-p -- databases test

exported data will be compatible with other databases or old MySQL versions. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc, Use commas to separate the values. It does not guarantee full compatibility, but is as compatible as possible. Mysqldump-uroot-p -- all-databases -- compatible = ansi -- Compact Export less output information (for debugging ). Remove com

exported data will be compatible with other databases or old MySQL versions. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc, Use commas to separate the values. It does not guarantee full compatibility, but is as compatible as possible. Mysqldump-uroot-p -- all-databases -- compatible = ansi -- Compact Export less output information (for debugging ). Remove com

-- compress -- Create-options,- The create table statement contains all MySQL feature options. (Enabled by default) Mysqldump-uroot-p -- all-databases -- Databases,-B Export several databases. All name parameters following the parameter are considered as the database name. Mysqldump-uroot-p -- databases test mysql --

*/;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2015-05-07 16:54:19 The above command has simply backed up the database named database databasename to the bak. SQL file. If we want

as compatible as possible. Mysqldump-uroot-p--all-databases--compatible=ansi --compact Export less output information (for debugging). Remove the structure of annotations and Kinsoku. Available options:--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 stateme

In MySQLDump, use the-w statement for backup. mysqldump-w When we use mysqldump to back up data, we have the option-where/-w. You can specify the backup conditions. The options are described as follows: -w, --where=name Dump only selected records. Quotes are mandatory We can perform a test, for example:

Tags: mysql database password ext win/usr file stored procedure applies to connection numberTransfer from: http://www.cnblogs.com/chenmh/p/5300370.html introductionIn daily maintenance work often need to export the data, and mysqldump is to export the data process is a very frequent use of a tool, it comes with a lot of function parameters, the article will list some common operations, at the end of the article will be all the parameters detailed list

Implementation principle of mysqldump and implementation principle of mysqldump MySQL backup can be divided into the following two types: 1. Cold backup 2. Hot Standby Among them, cold backup, as its name implies, is to turn off the database and use operating system commands to copy database-related files. Hot Standby refers to online Hot Standby, that is, backing up the database without shutting down the d

* *; statement, which greatly increases the speed of the INSERT statement because it rebuilds the index after all the data has been inserted. This option is only suitable for MyISAM tables.–extended-insert = True|falseBy default, Mysqldump opens the –complete-insert mode, so if you don't want to use it, use this option to set its value to false.–hex-blobExports a binary string field using hexadecimal format. You must use this option if you have binar

possible.Mysqldump-uroot-p -- all-databases -- compatible = ansi-- CompactExport less output information (for debugging ). Remove comments, headers, and tails. Option: -- skip-add-drop-table -- skip-add-locks -- skip-comments -- skip-disable-keysMysqldump-uroot-p -- all-databases -- compact-- Complete-insert,-cUse the complete insert statement (including the column name ). This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion

Mysqldump is a client program that MySQL uses to store databases. It mainly produces a series of SQL statements that can be encapsulated into a file that contains all the SQL commands needed to rebuild your database, such as CREATE database,create Table,insert, and so on. Can be used to implement lightweight, fast migration or recovery databases. is a way to implement a logical backup of the MySQL database. In the day-to-day maintenance work will oft