MySQL import and export commands
| MySQL is an open-source relational database management system (RDBMS). World's second most widely used open-source client–server RDBMS. The MySQL development project has made its source code available under the terms of the GNU General Public License and owned by Oracle Corporation. | |
Now lets see simple command line examples for exporting and importing MySQL database(s). All examples are shown under Windows machines. So when we try in different OS only path(s) will change rather than commands shown below.
Export Commands:
Open command prompt and goto MySQL installed bin folder as shown below location. NOTE: Installation directory path may change from system to system.
C:\Program Files\MySQL\MySQL Server 5.6\bin>
1. Export all databases
> mysqldump -u{username} -p{password} -h{host} --all-databases > C:\Backup\all-databases.sql
* username - MySQL username
* password - MySQL password
* host - MySQL server host. If MySQL running in local then please mention as 'localhost' else give remote MySQL server IP address. When we give remote server IP, need to make sure that we have access to remote MySQL server.
2. Export single database
> mysqldump -u{username} -p{password} -h{host} {database_name} > C:\Backup\database.sql
* database_name - MySQL database name to export to file.
3. Export more than 1 database
> mysqldump -u{username} -p{password} -h{host} --databases {database_name_1} {database_name_2} > C:\Backup\databases_1_2.sql
4. Export only specific tables from database
> mysqldump -u{username} -p{password} -h{host} {database_name} {table_name_1} {table_name_2} > C:\Backup\database_table_1_2.sql
mysqldump supports the various options, which can be specified on the command. For more informations on options please refer to MySQL site link under Table 4:11
Import Command:
Similar to mysqldump command please goto MySQL installed bin folder [C:\Program Files\MySQL\MySQL Server 5.6\bin>].
1. Import all databases
> mysql -u{username} -p{password} -h{host} < C:\Backup\all-databases.sql
2. Import particular database
> mysql -u{username} -p{password} -h{host} {database_name} < C:\Backup\database.sql
* NOTE: Database with name mentioned must exist already, else create empty database with the name mentioned before importing.
3. Import tables into particular database
> mysql -u{username} -p{password} -h{host} {database_name} < C:\Backup\database_table_1_2.sql
* NOTE: Database with name mentioned must exist already, else create empty database with the name mentioned before importing.