Copyright (C) 2000-2012 |
GNU Info (mysql.info)mysqldumpmysqldump, Dumping Table Structure and Data ------------------------------------------- Utility to dump a database or a collection of database for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table. If you are doing a backup on the server, you should consider using the `mysqlhotcopy' instead. Note: `mysqlhotcopy'. shell> mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] If you don't give any tables or use the `--databases' or `--all-databases', the whole database(s) will be dumped. You can get a list of the options your version of `mysqldump' supports by executing `mysqldump --help'. Note that if you run `mysqldump' without `--quick' or `--opt', `mysqldump' will load the whole result set into memory before dumping the result. This will probably be a problem if you are dumping a big database. Note that if you are using a new copy of the `mysqldump' program and you are going to do a dump that will be read into a very old MySQL server, you should not use the `--opt' or `-e' options. `mysqldump' supports the following options: `--add-locks' Add `LOCK TABLES' before and `UNLOCK TABLE' after each table dump. (To get faster inserts into MySQL.) `--add-drop-table' Add a `drop table' before each create statement. `-A, --all-databases' Dump all the databases. This will be same as `--databases' with all databases selected. `-a, --all' Include all MySQL-specific create options. `--allow-keywords' Allow creation of column names that are keywords. This works by prefixing each column name with the table name. `-c, --complete-insert' Use complete insert statements (with column names). `-C, --compress' Compress all information between the client and the server if both support compression. `-B, --databases' To dump several databases. Note the difference in usage. In this case no tables are given. All name arguments are regarded as database names. `USE db_name;' will be included in the output before each new database. `--delayed' Insert rows with the `INSERT DELAYED' command. `-e, --extended-insert' Use the new multiline `INSERT' syntax. (Gives more compact and faster inserts statements.) `-#, --debug[=option_string]' Trace usage of the program (for debugging). `--help' Display a help message and exit. `--fields-terminated-by=...' `--fields-enclosed-by=...' `--fields-optionally-enclosed-by=...' `--fields-escaped-by=...' `--lines-terminated-by=...' These options are used with the `-T' option and have the same meaning as the corresponding clauses for `LOAD DATA INFILE'. Note: `LOAD DATA'. `-F, --flush-logs' Flush log file in the MySQL server before starting the dump. `-f, --force,' Continue even if we get a SQL error during a table dump. `-h, --host=..' Dump data from the MySQL server on the named host. The default host is `localhost'. `-l, --lock-tables.' Lock all tables before starting the dump. The tables are locked with `READ LOCAL' to allow concurrent inserts in the case of `MyISAM' tables. `-n, --no-create-db' 'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will not be put in the output. The above line will be added otherwise, if -databases or -all-databases option was given. `-t, --no-create-info' Don't write table creation information (The `CREATE TABLE' statement.) `-d, --no-data' Don't write any row information for the table. This is very useful if you just want to get a dump of the structure for a table! `--opt' Same as `--quick --add-drop-table --add-locks --extended-insert --lock-tables'. Should give you the fastest possible dump for reading into a MySQL server. `-pyour_pass, --password[=your_pass]' The password to use when connecting to the server. If you specify no `=your_pass' part, `mysqldump' you will be prompted for a password. `-P port_num, --port=port_num' The TCP/IP port number to use for connecting to a host. (This is used for connections to hosts other than `localhost', for which Unix sockets are used.) `-q, --quick' Don't buffer query, dump directly to stdout. Uses `mysql_use_result()' to do this. `-r, --result-file=...' Direct output to a given file. This option should be used in MSDOS, because it prevents new line '\n' from being converted to '\n\r' (new line + carriage return). `-S /path/to/socket, --socket=/path/to/socket' The socket file to use when connecting to `localhost' (which is the default host). `--tables' Overrides option -databases (-B). `-T, --tab=path-to-some-directory' Creates a `table_name.sql' file, that contains the SQL CREATE commands, and a `table_name.txt' file, that contains the data, for each give table. *NOTE*: This only works if `mysqldump' is run on the same machine as the `mysqld' daemon. The format of the `.txt' file is made according to the `--fields-xxx' and `--lines--xxx' options. `-u user_name, --user=user_name' The MySQL user name to use when connecting to the server. The default value is your Unix login name. `-O var=option, --set-variable var=option' Set the value of a variable. The possible variables are listed below. `-v, --verbose' Verbose mode. Print out more information on what the program does. `-V, --version' Print version information and exit. `-w, --where='where-condition'' Dump only selected records. Note that QUOTES are mandatory: "--where=user='jimf'" "-wuserid>1" "-wuserid<1" `-O net_buffer_length=#, where # < 16M' When creating multi-row-insert statements (as with option `--extended-insert' or `--opt'), `mysqldump' will create rows up to `net_buffer_length' length. If you increase this variable, you should also ensure that the `max_allowed_packet' variable in the MySQL server is bigger than the `net_buffer_length'. The most normal use of `mysqldump' is probably for making a backup of whole databases. Note: Backup. mysqldump --opt database > backup-file.sql You can read this back into MySQL with: mysql database < backup-file.sql or mysql -e "source /patch-to-backup/backup-file.sql" database However, it's also very useful to populate another MySQL server with information from a database: mysqldump --opt database | mysql ---host=remote-host -C database It is possible to dump several databases with one command: mysqldump --databases database1 [database2 database3...] > my_databases.sql If all the databases are wanted, one can use: mysqldump --all-databases > all_databases.sql automatically generated by info2www version 1.2.2.9 |