1500字范文 > mysql命令行进行数据导入和导出

mysql命令行进行数据导入和导出

时间：2020-06-26 05:45:34

相关推荐

mysql命令行进行数据导入和导出

背景

开发过程中经常会遇到数据库迁移&备份等操作。可以采用客户端或者命令行来进行数据备份和导入操作，由于目前可视化客户端比较多，操作比较简单，目前暂时不做介绍。下面主要是将linux下使用命令行操作将数据库导入导出做个记录

常用命令

导出

Dumping structure and contents of MySQL databases and tables.Usage: mysqldump [OPTIONS] database [tables]ORmysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]ORmysqldump [OPTIONS] --all-databases [OPTIONS]

-A, --all-databases Dump all the databases. This will be same as --databaseswith all databases selected.-Y, --all-tablespaces Dump all the tablespaces.-y, --no-tablespaces Do not dump any tablespace information.--add-drop-database Add a DROP DATABASE before each create.--add-drop-table Add a DROP TABLE before each create.(Defaults to on; use --skip-add-drop-table to disable.)--add-locks Add locks around INSERT statements.(Defaults to on; use --skip-add-locks to disable.)--allow-keywords Allow creation of column names that are keywords.--apply-slave-statements Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'STARTSLAVE' to bottom of dump.--character-sets-dir=name Directory for character set files.-i, --commentsWrite additional information.(Defaults to on; use --skip-comments to disable.)--compatible=name Change the dump to be compatible with a given mode. Bydefault tables are dumped in a format optimized forMySQL. Legal modes are: ansi, mysql323, mysql40,postgresql, oracle, mssql, db2, maxdb, no_key_options,no_table_options, no_field_options. One can use severalmodes separated by commas. Note: Requires MySQL serverversion 4.1.0 or higher. This option is ignored withearlier server versions.--compact Give less verbose output (useful for debugging). Disablesstructure comments and header/footer constructs. Enablesoptions --skip-add-drop-table --skip-add-locks--skip-comments --skip-disable-keys --skip-set-charset.-c, --complete-insert Use complete insert statements.-C, --compressUse compression in server/client protocol.-a, --create-options Include all MySQL specific create options.(Defaults to on; use --skip-create-options to disable.)-B, --databasesDump several databases. Note the difference in usage; inthis case no tables are given. All name arguments areregarded as database names. 'USE db_name;' will beincluded in the output.-#, --debug[=#]This is a non-debug version. Catch this and exit.--debug-check Check memory and open file usage at exit.--debug-info Print some debug info at exit.--default-character-set=name Set the default character set.--delayed-insert Insert rows with INSERT DELAYED.--delete-master-logs Delete logs on master after backup. This automaticallyenables --master-data.-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be putin the output.(Defaults to on; use --skip-disable-keys to disable.)--dump-slave[=#] This causes the binary log position and filename of themaster to be appended to the dumped data output. Settingthe value to 1, will printit as a CHANGE MASTER commandin the dumped data output; if equal to 2, that commandwill be prefixed with a comment symbol. This option willturn --lock-all-tables on, unless --single-transaction isspecified too (in which case a global read lock is onlytaken a short time at the beginning of the dump - don'tforget to read about --single-transaction below). In allcases any action on logs will happen at the exact momentof the dump.Option automatically turns --lock-tables off.-E, --events Dump events.-e, --extended-insert Use multiple-row INSERT syntax that include severalVALUES lists.(Defaults to on; use --skip-extended-insert to disable.)--fields-terminated-by=name Fields in the output file are terminated by the givenstring.--fields-enclosed-by=name Fields in the output file are enclosed by the givencharacter.--fields-optionally-enclosed-by=name Fields in the output file are optionally enclosed by thegiven character.--fields-escaped-by=name Fields in the output file are escaped by the givencharacter.-F, --flush-logs Flush logs file in server before starting dump. Note thatif you dump many databases at once (using the option--databases= or --all-databases), the logs will beflushed for each database dumped. The exception is whenusing --lock-all-tables or --master-data: in this casethe logs will be flushed only once, corresponding to themoment all tables are locked. So if you want your dumpand the log flush to happen at the same exact moment youshould use --lock-all-tables or --master-data with--flush-logs.--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysqldatabase. This option should be used any time the dumpcontains the mysql database and any other database thatdepends on the data in the mysql database for properrestore. -f, --force Continue even if we get an SQL error.-?, --helpDisplay this help message and exit.--hex-blobDump binary strings (BINARY, VARBINARY, BLOB) inhexadecimal format.-h, --host=nameConnect to host.--ignore-table=name Do not dump the specified table. To specify more than onetable to ignore, use the directive multiple times, oncefor each table. Each table must be specified with bothdatabase and table names, e.g.,--ignore-table=database.table.--include-master-host-port Adds 'MASTER_HOST=<host>, MASTER_PORT=<port>' to 'CHANGEMASTER TO..' in dump produced with --dump-slave.--insert-ignoreInsert rows with INSERT IGNORE.--lines-terminated-by=name Lines in the output file are terminated by the givenstring.-x, --lock-all-tables Locks all tables across all databases. This is achievedby taking a global read lock for the duration of thewhole dump. Automatically turns --single-transaction and--lock-tables off.-l, --lock-tables Lock all tables for read.(Defaults to on; use --skip-lock-tables to disable.)--log-error=name Append warnings and errors to given file.--master-data[=#] This causes the binary log position and filename to beappended to the output. If equal to 1, will print it as aCHANGE MASTER command; if equal to 2, that command willbe prefixed with a comment symbol. This option will turn--lock-all-tables on, unless --single-transaction isspecified too (on servers before MariaDB 5.3 this willstill take a global read lock for a short time at thebeginning of the dump; don't forget to read about--single-transaction below). In all cases, any action onlogs will happen at the exact moment of the dump. Optionautomatically turns --lock-tables off.--max-allowed-packet=# The maximum packet length to send to or receive fromserver.--net-buffer-length=# The buffer size for TCP/IP and socket communication.--no-autocommitWrap tables with autocommit/commit statements.-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement thatnormally is output for each dumped database if--all-databases or --databases is given.-t, --no-create-info Don't write table creation info.-d, --no-data No row information.-N, --no-set-names Same as --skip-set-charset.--optSame as --add-drop-table, --add-locks, --create-options,--quick, --extended-insert, --lock-tables, --set-charset,and --disable-keys. Enabled by default, disable with--skip-opt.--order-by-primary Sorts each table's rows by primary key, or first uniquekey, if such a key exists. Useful when dumping a MyISAMtable to be loaded into an InnoDB table, but will makethe dump itself take considerably longer.-p, --password[=name] Password to use when connecting to server. If password isnot given it's solicited on the tty.-P, --port=# Port number to use for connection.--protocol=nameThe protocol to use for connection (tcp, socket, pipe,memory).-q, --quick Don't buffer query, dump directly to stdout.(Defaults to on; use --skip-quick to disable.)-Q, --quote-names Quote table and column names with backticks (`).(Defaults to on; use --skip-quote-names to disable.)--replace Use REPLACE INTO instead of INSERT INTO.-r, --result-file=name Direct output to a given file. This option should be usedin systems (e.g., DOS, Windows) that use carriage-returnlinefeed pairs (\r\n) to separate text lines. This optionensures that only a single newline is used.-R, --routinesDump stored routines (functions and procedures).--set-charset Add 'SET NAMES default_character_set' to the output.(Defaults to on; use --skip-set-charset to disable.)--single-transaction Creates a consistent snapshot by dumping all tables in asingle transaction. Works ONLY for tables stored instorage engines which support multiversioning (currentlyonly InnoDB does); the dump is NOT guaranteed to beconsistent for other storage engines. While a--single-transaction dump is in process, to ensure avalid dump file (correct table contents and binary logposition), no other connection should use the followingstatements: ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE, as consistent snapshot is not isolatedfrom them. Option automatically turns off --lock-tables.--dump-date Put a dump date to the end of the output.(Defaults to on; use --skip-dump-date to disable.)--skip-optDisable --opt. Disables --add-drop-table, --add-locks,--create-options, --quick, --extended-insert,--lock-tables, --set-charset, and --disable-keys.-S, --socket=name The socket file to use for connection.--sslEnable SSL for connection (automatically enabled withother flags).--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies--ssl).--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).--ssl-cert=nameX509 cert in PEM format (implies --ssl).--ssl-cipher=name SSL cipher to use (implies --ssl).--ssl-key=nameX509 key in PEM format (implies --ssl).--ssl-verify-server-cert Verify server's "Common Name" in its cert againsthostname used when connecting. This option is disabled bydefault.-T, --tab=nameCreate tab-separated textfile for each table to givenpath. (Create .sql and .txt files.) NOTE: This only worksif mysqldump is run on the same machine as the mysqldserver.--tables Overrides option --databases (-B).--triggersDump triggers for each dumped table.(Defaults to on; use --skip-triggers to disable.)--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping ofTIMESTAMP data when a server has data in different timezones or data is being moved between servers withdifferent time zones.(Defaults to on; use --skip-tz-utc to disable.)-u, --user=nameUser for login if not current user.-v, --verbose Print info about the various stages.-V, --version Output version information and exit.-w, --where=name Dump only selected records. Quotes are mandatory.-X, --xml Dump a database as well formed XML.--plugin-dir=name Directory for client-side plugins.--default-auth=name Default authentication client-side plugin to use.

导入

通过mysql命令行导入

mysql>source 123.sql;

通过< 命令导入

mysql -u用户名 -p密码数据库名 < 123.sql