Data export/import on MySQL
database
ID: 20140220
ACCESSING DATA...
SYSTEM: ARCHIVE
FILE: DATA EXPORT/IMPORT ON MYSQL
STATUS: ACTIVE
MySQL provides you with a lot of flexibility when it comes to import or export data between databases. Let’s examine a few possibilities to export/import data:
To export a whole database
The following command will export a whole MySQL database on a sql file:
mysqldump -u user DB_NAME > /tmp/file.sql
To export a single table
The following command allows you to dump a table’s metadata and data into asql file:
mysqldump -u user DB_NAME TABLE_NAME > /tmp/fle.sql
To export a portion of table data
It’s also possible to export data based on a sql criteria, like using a “where” clause on a sql query. The following command shows how to export table data having an ID lower than 100:
mysqldump -u user -w "ID < 100" DB_NAME TABLE_NAME > /tmp/file.sql
To import data into a MySQL database
And finally for the import part, here’s how to import data when you previously exported it on a sql file:
mysql -u user DB_NAME < tableNameFile.sql