Mysql Database Import

This chapter introduces several simple MySQL data import commands.

mysql -u your_username -p -h your_host -P your_port -D your_database

1. Import with the mysql Command

The syntax for importing using the mysql command is:

mysql -u your_username -p -h your_host -P your_port -D your_database

your_username, your_host, your_port, and your_database are your MySQL username, host, port, and database, respectively.

Example:

# mysql -uroot -p123456 < .sql

The above command imports the entire backed-up database .sql.

After executing the above command, the system will prompt for the MySQL user's password. Enter the password and press the Enter key.

This way, MySQL will execute the statements in the SQL file and import the data into the specified database.

Please note, if the SQL file contains statements to create a database, ensure the database already exists before executing the import. If the file contains statements to create tables, ensure the tables do not exist or are empty to avoid conflicts during data import.

2. Import with the source Command

To import a database using the source command, you first need to log in to the database terminal:

mysql> create database abc; # Create database
mysql> use abc; # Use the created database
mysql> set names utf8; # Set encoding
mysql> source /home/abc/abc.sql # Import backup database

The advantage of using the source command is that you can execute it directly within the MySQL command line without exiting MySQL and using other commands.

3. Import Data with LOAD DATA

MySQL provides the LOAD DATA INFILE statement for inserting data. The following example reads a file named dump.txt from the current directory and inserts the data from that file into the mytbl table in the current database.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

If the LOCAL keyword is specified, it indicates that the file is read from the client host according to the path. If not specified, the file is read from the server according to the path.

You can explicitly specify the column value delimiter and line terminator in the LOAD DATA statement, but the default delimiters are the tab character and newline character.

The syntax for the FIELDS and LINES clauses is the same for both commands. Both clauses are optional, but if both are specified, the FIELDS clause must appear before the LINES clause.

If a FIELDS clause is specified, its subclauses (TERMINATED BY, ENCLOSED BY, and ESCAPED BY) are also optional. However, you must specify at least one of them.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
    -> FIELDS TERMINATED BY ':'
    -> LINES TERMINATED BY 'rn';

By default, LOAD DATA inserts data in the order of the columns in the data file. If the columns in the data file do not match the columns in the table being inserted into, you need to specify the column order.

For example, if the column order in the data file is a,b,c, but the column order in the table is b,c,a, the data import syntax is as follows:

mysql> LOAD DATA LOCAL INFILE 'dump.txt'
    -> INTO TABLE mytbl (b, c, a);

4. Import Data with mysqlimport

The mysqlimport client provides a command-line interface for the LOAD DATA INFILE statement. Most options of mysqlimport correspond directly to LOAD DATA INFILE clauses.

To import data from the file dump.txt into the mytbl table, you can use the following command:

$ mysqlimport -u root -p --local mytbl dump.txt
password  *****

The mysqlimport command can specify options to set the format. The command syntax is as follows:

$ mysqlimport -u root -p --local --fields-terminated-by=":" 
    --lines-terminated-by="rn" mytbl dump.txt
password  *****

Use the --columns option in the mysqlimport statement to set the column order:

$ mysqlimport -u root -p --local --columns=b,c,a 
    mytbl dump.txt
password  *****

Introduction to Common mysqlimport Options

Option	Function
-d or --delete	Before importing new data, delete all information from the data table.
-f or --force	mysqlimport will force continue inserting data regardless of whether errors are encountered.
-i or --ignore	mysqlimport skips or ignores rows with the same unique key. Data in the import file will be ignored.
-l or --lock-tables	Lock the table before data is inserted, preventing user queries and updates from being affected while updating the database.
-r or --replace	This option has the opposite effect of the -i option; it replaces records in the table with the same unique key.
--fields-enclosed-by=char	Specifies the character used to enclose data records in the text file. In many cases, data is enclosed in double quotes. By default, data is not enclosed by any character.
--fields-terminated-by=char	Specifies the delimiter between the values of each data field. In a period-delimited file, the delimiter is a period. You can use this option to specify the delimiter between data. The default delimiter is the tab character.
--lines-terminated-by=str	This option specifies the string or character that separates rows of data in the text file. By default, mysqlimport uses a newline as the row delimiter. You can choose to use a string instead of a single character: a new line or a carriage return.

Other commonly used options for the mysqlimport command include -v to display the version, and -p to prompt for a password.

YouTip