Common MySQL import commands for exporting data

To export

Export the data for the specified db_name:

$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

To export the data for the entire library:

$ mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql

Export data for a specified table

$ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql

Export the packaged data file (if the file is large)

$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz

To export data on a remote machine:

$ mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql

如果只导出sql语句，不需要数据，可加参数 --no-data

To import

Type the following command to import SQL data file:

$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

In this example, the import ' data.sql ' file into ' blog ' database using sathish as Username:

$ mysql -u sat -p -h localhost blog < data.sql

If you have a dedicated database server, replace the localhost hostname with with actual server name or IP address as follows:

$ mysql -u username -p -h 202.54.1.10 databasename < data.sql

OR use hostname such as mysql.cyberciti.biz

$ mysql -u username -p -h mysql.cyberciti.biz database-name < data.sql

If you don't know the database name or database name is included in SQL dump you can try out something as follows:

$ mysql -u username -p -h 202.54.1.10 < data.sql

Common MySQL import commands for exporting data