Import & Export Data from MySQL

Initial Server Configuration

Before we dive into the nitty-gritty, make sure your server is properly configured. On MacOS, open Terminal and issue the following commands:

MYSQL_ROOT_PWD='S1r0ngP@ssw0rd'
DEMOUSER_PWD='P@ssword1234'

mysql -u root -p${MYSQL_ROOT_PWD} -e "SHOW VARIABLES LIKE 'secure_file_priv'"
echo "secure_file_priv = /Users/saurav.mitra/Demo" >> /opt/homebrew/etc/my.cnf
brew services restart mysql
mysql -u root -p${MYSQL_ROOT_PWD} -e "SHOW VARIABLES LIKE 'secure_file_priv'"

mysql -u root -p${MYSQL_ROOT_PWD} -e "SHOW VARIABLES LIKE 'local_infile'"
mysql -u root -p${MYSQL_ROOT_PWD} -e "SET GLOBAL local_infile = 1"
mysql -u root -p${MYSQL_ROOT_PWD} -e "SHOW VARIABLES LIKE 'local_infile'"

mysql -u root -p${MYSQL_ROOT_PWD} -e "GRANT FILE ON *.* TO demouser@'localhost'"

Import Data to Table

Import data into MySQL table from a comma delimited file with headers.

mysql -u demouser -p${DEMOUSER_PWD} demo -e "\
    LOAD DATA LOCAL INFILE '/Users/saurav.mitra/Demo/dept.csv' \
    INTO TABLE dept \
    FIELDS TERMINATED BY ',' \
    ENCLOSED BY '""' \
    LINES TERMINATED BY '\n' \
    IGNORE 1 ROWS \
"

Alternatively, you can import data with selected column values:

mysql -u demouser -p${DEMOUSER_PWD} demo -e "\
    LOAD DATA LOCAL INFILE '/Users/saurav.mitra/Demo/dept.csv' \
    INTO TABLE dept \
    FIELDS TERMINATED BY ',' \
    ENCLOSED BY '""' \
    LINES TERMINATED BY '\n' \
    IGNORE 1 ROWS \
    (@col1,@col2,@col3,@col4) \
    set deptno=@col1,dname=@col2 \
"

Export Data from Table

Export data from PostgreSQL table into comma delimited file.

mysql -u demouser -p${DEMOUSER_PWD} demo -e "\
    SELECT * FROM dept \
    INTO OUTFILE '/Users/saurav.mitra/Demo/dept.csv' \
    FIELDS TERMINATED BY ',' \
    ENCLOSED BY '""' \
    LINES TERMINATED BY '\n' \
"

Alternatively, you can export data with a header record:

mysql -u demouser -p${DEMOUSER_PWD} demo -e "\
    SELECT 'deptno', 'dname' UNION \
    SELECT * FROM dept \
    INTO OUTFILE '/Users/saurav.mitra/Demo/dept.csv' \
    FIELDS TERMINATED BY ',' \
    ENCLOSED BY '""' \
    LINES TERMINATED BY '\n' \
"

Import Data from SQL Insert File

Import data into table based on SQL insert statements in a file.

mysql -u demouser -p${DEMOUSER_PWD} demo < /Users/saurav.mitra/Demo/dept.sql

And that's it! With these simple steps, you should be able to effortlessly import and export data from MySQL. Happy coding!