Import/Export MySQL Database

IMPORT MYSQL DATABASE

There are many ways to import .sql file in MySQL Database.Some of which are discussed below:-

First Way

1. Open MySQL command line client or execute mysql.exe from command prompt present in  MySQL installation path or add mysql(make sure mysql path till bin is placed in system environment path) ,
2. Create a new database.

mysql> Create database test
mysql> use test
Then import the database into test databse by using one of this commend:-
source test.sql
or
./ test.sql

Second Way

1. Using command prompt:-
2. Go to the location where mysql is installed or add MySQL.exe location(installationlocation/bin) in system environment path:-
   MySQL->bin
   now execute following command:-

mysql -u <user> -p<password> -h <hostname> <database> < /path/to/updated_script/database.sql

3. You can also invoke mysql with the --verbose option, which causes each statement to be displayed before the result that it produces.

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

Dedicated server

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

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

Import multiple files into a single sql file

C:\>Copy *.sql combined.sql

EXPORTING DATABASE

To export MySQL database follow following steps:-

mysqldump -u username -ppassword -h database > database_with_location.sql

or 

mysqldump -u username -ppassword -h database --result-file="location_to_export_database.sql"

Taking backup of more than one database  database1,database2

 mysqldump -u root -ptmppassword --databases database1 database2 > database1_database2.sql

Backup all databases

 mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

Backup a specific table

Backup only the accounts_contacts table from sugarcrm database

mysqldump -u root -ptmppassword sugarcrm accounts_contacts \      > tmp/sugarcrm_accounts_contacts.sql

 Backup a local database and restore to remote server using single command:

This is a sleek option, if you want to keep a read-only database on the remote-server, which is a copy of the master database on local-server. The example below will backup the sugarcrm database on the local-server and restore it as sugarcrm1 database on the remote-server. Please note that you should first create the sugarcrm1 database on the remote-server before executing the following command.

mysqldump -u root -ptmppassword sugarcrm | mysql \   -u root -ptmppassword --host=remote-server -C sugarcrm1