Import Export mysql database using command prompt

Its for Developer who are using Mysql.

Basically this article is meant for situation where you have complete access of Hosting server and where you can fire some commands.

Those who know php in depth, they must aware of fire console Commands from PHP script itself.They can also use below command from php script itself.

Steps for Export Mysql Database data using Console

Goto Command prompt

type below command

Command Syntax

mysqldump -h HOST -u USERNAME databaseName > backup.sql

Command Examples

mysqldump -h localhost -u root waytocodedb > backup.sql

OR
mysqldump -h localhost -u root waytocodedb > C: backup.sql

 

Steps for Import Mysql Database data using Console

Goto Command prompt

type below command

Command Syntax

mysql -h HOST -u USERNAME databaseName < backup.sql

Command Examples

mysql -h localhost -u root waytocodedb < backup.sql

OR
mysql -h localhost -u root waytocodedb < C: backup.sql

 

In above command

mysqldump for Exporting data and mysql for importing mysql data.

HOST is basically where mysql Database server is running,like for local Mysql Server,Host will be localhost.

-h Stands for Host name.

USERNAME is the mysql server user name,like for local Mysql server,USERNAME will be root.

-u stands for User name.

If you have password in the same you can specify the password using “-p”

DATABASE NAME is the name of the databaseĀ  that you want to import or export.

backup.sql is the file name where import or New exported file will be placed.

Symbol “>” while exporting.

Symbol “<” while importing.

 

How to Import huge mysql data

Basically for importĀ  function, each and every database is having limited packet size that can be imported. But we may face problem while importing huge amount of that, for that we have to increase the max allowed Packet size. Below is the command.

SET GLOBAL max_allowed_packet=33554432;