Linux and UNIX Man Pages

Linux & Unix Commands - Search Man Pages

mysqldump(1) [opensolaris man page]

mysqldump(1)							  MySQL database						      mysqldump(1)

mysqldump - text-based client for dumping or backing up mysql databases, tables and or data. USAGE
mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] OPTION SYNOPSIS
mysqldump [-A|--all-databases] [-a|--all] [-#|--debug=...] [--character-sets-dir=...] [-?|--help] [-B|--databases] [-c|--complete-insert] [-C|--compress] [--default-character-set=...] [-e|--extended-insert] [--add-drop-table] [--add-locks] [--allow-keywords] [--delayed- insert] [-F|--flush-logs] [-f|--force] [-h|--host=...] [-l|--lock-tables] [-n|--no-create-db] [-t|--no-create-info] [-d|--no-data] [-O|--set-variablevar=option] [--opt] [-p|--password[=...]] [-P|--port=...] [-q|--quick] [-Q|--quote-names] [-S|--socket=...] [--tables] [-T|--tab=...] [-u|--user=#] [-v|--verbose] [-V|--version] [-w|--where=] [--delayed] [-e|--extended-insert] [--fields-terminated-by=...] [--fields-enclosed-by=...] [--fields-optionally-enclosed-by=...] [--fields-escaped-by=...] [--lines-terminated-by=...] [-v|--verbose] [-V|--version] [-O net_buffer_length=#, where # < 16M] DESCRIPTION
Dumping definition and data mysql database or table mysqldump supports by executing -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. -#|--debug=... Output debug log. Often this is 'd:t:o,filename`. --character-sets-dir=... Directory where character sets are -?|--help Display this help message and exit. -B|--databases To dump several databases. Note the difference in usage; In this case no tables are given. All name arguments are regarded as databasenames. -c|--complete-insert Use complete insert statements. -C|--compress Use compression in server/client protocol. --default-character-set=... Set the default character set -e|--extended-insert Allows utilization of the new, much faster INSERT syntax. --add-drop-table Add a 'drop table' before each create. --add-locks Add locks around insert statements. --allow-keywords Allow creation of column names that are keywords. --delayed-insert Insert rows with INSERT DELAYED. -F|--flush-logs Flush logs file in server before starting dump. -f|--force Continue even if we get an sql-error. -h|--host=... Connect to host. -l|--lock-tables Lock all tables for read. -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 info. -d|--no-data No row information. -O|--set-variable var=option give a variable a value. --help lists variables --opt Same as --add-drop-table --add-locks --all --extended-insert --quick --lock-tables -p|--password[=...] Password to use when connecting to server. If password is not given it's solicited on the tty. -P|--port=... Port number to use for connection. -q|--quick Don't buffer query, dump directly to stdout. -Q|--quote-names Quote table and column names with ` -S|--socket=... Socket file to use for connection. --tables Overrides option --databases(-B). -T|--tab=... Creates tab separated textfile for each table to given path. (creates .sql and .txt files). NOTE: This only works if mysqldump is run on the same machine as the mysqld daemon. -u|--user=# User for login if not current user. -v|--verbose Print info about the various stages. -V|--version Output version information and exit. -w|--where= dump only selected records; QUOTES mandatory! --delayed Insert rows with the INSERT DELAYED command. -e|--extended-insert Use the new multiline INSERT syntax. (Gives more compact and faster inserts statements.) --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. See Mysql manual section 7.23 LOAD DATA INFILE Syntax. -v|--verbose Verbose mode. Print out more information on what the program does. -V|--version Print version information and exit. -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_buf- fer_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. EXAMPLES
The most normal use of mysqldump is probably for making a backup of whole databases. See Mysql Manual section 21.2 Database Backups. 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 SEE ALSO
isamchk(1), isamlog(1), mysql(1), mysqlaccess(1), mysqladmin(1), mysqld(1), mysqld_multi(1), mysqld_safe(1), mysql_fix_privilege_tables(1), mysqlshow(1), mysql_zap(1), perror(1), replace(1) For more information please refer to the MySQL reference manual, which may already be installed locally and which is also available online at BUGS
Please refer to to report bugs. AUTHOR
Ver 1.0, distribution 4.0.24 Michael (Monty) Widenius (, MySQL AB ( This software comes with no warranty. Manual page by L. (Kill-9) Pedersen (, Mercurmedia Data Model Architect / system developer (http://www.mercur- ATTRIBUTES
See attributes(5) for descriptions of the following attributes: +--------------------+------------------------------------+ | ATTRIBUTE TYPE | ATTRIBUTE VALUE | +--------------------+------------------------------------+ |Availability | SUNWmysqlr, SUNWmysqlu, SUNWmysqlt | +--------------------+------------------------------------+ |Interface Stability | External | +--------------------+------------------------------------+ NOTES
Source for mysql is available on MySQL 4.0 19 December 2000 mysqldump(1)
Man Page