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.


Format Description Introduced Deprecated
–auto-rehash Enable automatic rehashing
–auto-vertical-output Enable automatic vertical result set display
–batch Do not use history file
–binary-mode Disable \r\n – to – \n translation and treatment of \0 as end-of-query
–bind-address Use specified network interface to connect to MySQL Server
–character-sets-dir Directory where character sets are installed
–column-names Write column names in results
–column-type-info Display result set metadata
–comments Whether to retain or strip comments in statements sent to the server
–compress Compress all information sent between client and server
–connect-expired-password Indicate to server that client can handle expired-password sandbox mode. 5.7.2
–connect_timeout Number of seconds before connection timeout
–database The database to use
–debug Write debugging log; supported only if MySQL was built with debugging support
–debug-check Print debugging information when program exits
–debug-info Print debugging information, memory, and CPU statistics when program exits
–default-auth Authentication plugin to use
–default-character-set Specify default character set
–defaults-extra-file Read named option file in addition to usual option files
–defaults-file Read only named option file
–defaults-group-suffix Option group suffix value
–delimiter Set the statement delimiter
–enable-cleartext-plugin Enable cleartext authentication plugin
–execute Execute the statement and quit
–force Continue even if an SQL error occurs
–help Display help message and exit
–histignore Patterns specifying which statements to ignore for logging
–host Connect to MySQL server on given host
–html Produce HTML output
–ignore-spaces Ignore spaces after function names
–init-command SQL statement to execute after connecting
–line-numbers Write line numbers for errors
–local-infile Enable or disable for LOCAL capability for LOAD DATA INFILE
–login-path Read login path options from .mylogin.cnf
–max_allowed_packet Maximum packet length to send to or receive from server
–max_join_size The automatic limit for rows in a join when using –safe-updates
–named-commands Enable named mysql commands
–net_buffer_length Buffer size for TCP/IP and socket communication
–no-auto-rehash Disable automatic rehashing
–no-beep Do not beep when errors occur
–no-defaults Read no option files
–one-database Ignore statements except those for the default database named on the command line
–pager Use the given command for paging query output
–password Password to use when connecting to server
–pipe On Windows, connect to server using named pipe
–plugin-dir Directory where plugins are installed
–port TCP/IP port number to use for connection
–print-defaults Print default options
–prompt Set the prompt to the specified format
–protocol Connection protocol to use
–quick Do not cache each query result
–raw Write column values without escape conversion
–reconnect If the connection to the server is lost, automatically try to reconnect
–i-am-a-dummy, –safe-updates Allow only UPDATE and DELETE statements that specify key values
–secure-auth Do not send passwords to server in old (pre-4.1) format 5.7.5
–select_limit The automatic limit for SELECT statements when using –safe-updates
–server-public-key-path Path name to file containing RSA public key
–shared-memory-base-name The name of shared memory to use for shared-memory connections
–show-warnings Show warnings after each statement if there are any
–sigint-ignore Ignore SIGINT signals (typically the result of typing Control+C)
–silent Silent mode
–skip-auto-rehash Disable automatic rehashing
–skip-column-names Do not write column names in results
–skip-line-numbers Skip line numbers for errors
–skip-named-commands Disable named mysql commands
–skip-pager Disable paging
–skip-reconnect Disable reconnecting
–socket For connections to localhost, the Unix socket file to use
–ssl Enable secure connection
–ssl-ca Path of file that contains list of trusted SSL CAs
–ssl-capath Path of directory that contains trusted SSL CA certificates in PEM format
–ssl-cert Path of file that contains X509 certificate in PEM format
–ssl-cipher List of permitted ciphers to use for connection encryption
–ssl-crl Path of file that contains certificate revocation lists
–ssl-crlpath Path of directory that contains certificate revocation list files
–ssl-key Path of file that contains X509 key in PEM format
–ssl-mode Security state of connection to server 5.7.11
–ssl-verify-server-cert Verify server certificate Common Name value against host name used when connecting to server
–syslog Log interactive statements to syslog 5.7.1
–table Display output in tabular format
–tee Append a copy of output to named file
–tls-version Protocols permitted for secure connections 5.7.10
–unbuffered Flush the buffer after each query
–user MySQL user name to use when connecting to server
–verbose Verbose mode
–version Display version information and exit
–vertical Print query output rows vertically (one line per column value)
–wait If the connection cannot be established, wait and retry instead of aborting
–xml Produce XML output
4.5/5 - (2 votes)