Developer's Daily | Unix by Example |
main | java | perl | unix | dev directory | web log |
mysqldump ? text-based client for dumping or backing up mysql databases , tables and or data. |
mysqldump [OPTIONS] database [tables] |
OR |
mysqldump [OPTIONS] ??databases [OPTIONS] DB1 [DB2 DB3...] |
||
OR |
mysqldump [OPTIONS] ??all-databases [OPTIONS] |
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] |
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 |
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_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. 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 |
isamchk (1), isamlog (1), mysqlaccess (1), mysqladmin (1), mysqlbug (1), mysqld (1), mysqldump (1), mysqlshow (1), msql2mysql (1), perror (1), replace (1), safe_mysqld (1), which1 (1), zap (1), |
Ver 1.0, distribution 3.23.29a Michael (Monty) Widenius (monty@tcx.se), TCX Datakonsult AB (http://www.tcx.se). This software comes with no warranty. Manual page by L. (Kill-9) Pedersen (kill-9@kill-9.dk), Mercurmedia Data Model Architect / system developer (http://www.mercurmedia.com) |