How to import an SQL file using the command line in MySQL?


mysql -u username -p database_name < file.sql

Note: It is better to use the full path of the SQL file file.sql.

mysql Options

mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] groups of an option file.


–auto-rehashEnable automatic rehashing
–auto-vertical-outputEnable automatic vertical result set display
–batchDo not use history file
–binary-modeDisable \r\n – to – \n translation and treatment of \0 as end-of-query
–bind-addressUse specified network interface to connect to MySQL Server
–character-sets-dirDirectory where character sets are installed
–column-namesWrite column names in results
–column-type-infoDisplay result set metadata
–commentsWhether to retain or strip comments in statements sent to the server
–compressCompress all information sent between client and server
–connect-expired-passwordIndicate to server that client can handle expired-password sandbox mode.5.7.2
–connect_timeoutNumber of seconds before connection timeout
–databaseThe database to use
–debugWrite debugging log; supported only if MySQL was built with debugging support
–debug-checkPrint debugging information when program exits
–debug-infoPrint debugging information, memory, and CPU statistics when program exits
–default-authAuthentication plugin to use
–default-character-setSpecify default character set
–defaults-extra-fileRead named option file in addition to usual option files
–defaults-fileRead only named option file
–defaults-group-suffixOption group suffix value
–delimiterSet the statement delimiter
–enable-cleartext-pluginEnable cleartext authentication plugin
–executeExecute the statement and quit
–forceContinue even if an SQL error occurs
–helpDisplay help message and exit
–histignorePatterns specifying which statements to ignore for logging
–hostConnect to MySQL server on given host
–htmlProduce HTML output
–ignore-spacesIgnore spaces after function names
–init-commandSQL statement to execute after connecting
–line-numbersWrite line numbers for errors
–local-infileEnable or disable for LOCAL capability for LOAD DATA INFILE
–login-pathRead login path options from .mylogin.cnf
–max_allowed_packetMaximum packet length to send to or receive from server
–max_join_sizeThe automatic limit for rows in a join when using –safe-updates
–named-commandsEnable named mysql commands
–net_buffer_lengthBuffer size for TCP/IP and socket communication
–no-auto-rehashDisable automatic rehashing
–no-beepDo not beep when errors occur
–no-defaultsRead no option files
–one-databaseIgnore statements except those for the default database named on the command line
–pagerUse the given command for paging query output
–passwordPassword to use when connecting to server
–pipeOn Windows, connect to server using named pipe
–plugin-dirDirectory where plugins are installed
–portTCP/IP port number to use for connection
–print-defaultsPrint default options
–promptSet the prompt to the specified format
–protocolConnection protocol to use
–quickDo not cache each query result
–rawWrite column values without escape conversion
–reconnectIf the connection to the server is lost, automatically try to reconnect
–i-am-a-dummy, –safe-updatesAllow only UPDATE and DELETE statements that specify key values
–secure-authDo not send passwords to server in old (pre-4.1) format5.7.5
–select_limitThe automatic limit for SELECT statements when using –safe-updates
–server-public-key-pathPath name to file containing RSA public key
–shared-memory-base-nameThe name of shared memory to use for shared-memory connections
–show-warningsShow warnings after each statement if there are any
–sigint-ignoreIgnore SIGINT signals (typically the result of typing Control+C)
–silentSilent mode
–skip-auto-rehashDisable automatic rehashing
–skip-column-namesDo not write column names in results
–skip-line-numbersSkip line numbers for errors
–skip-named-commandsDisable named mysql commands
–skip-pagerDisable paging
–skip-reconnectDisable reconnecting
–socketFor connections to localhost, the Unix socket file to use
–sslEnable secure connection
–ssl-caPath of file that contains list of trusted SSL CAs
–ssl-capathPath of directory that contains trusted SSL CA certificates in PEM format
–ssl-certPath of file that contains X509 certificate in PEM format
–ssl-cipherList of permitted ciphers to use for connection encryption
–ssl-crlPath of file that contains certificate revocation lists
–ssl-crlpathPath of directory that contains certificate revocation list files
–ssl-keyPath of file that contains X509 key in PEM format
–ssl-modeSecurity state of connection to server5.7.11
–ssl-verify-server-certVerify server certificate Common Name value against host name used when connecting to server
–syslogLog interactive statements to syslog5.7.1
–tableDisplay output in tabular format
–teeAppend a copy of output to named file
–tls-versionProtocols permitted for secure connections5.7.10
–unbufferedFlush the buffer after each query
–userMySQL user name to use when connecting to server
–verboseVerbose mode
–versionDisplay version information and exit
–verticalPrint query output rows vertically (one line per column value)
–waitIf the connection cannot be established, wait and retry instead of aborting
–xmlProduce XML output
4.5/5 - (2 votes)