I was trying to export a database file of big in size then I could not because max-time in server in less so that’s why I was getting timeout error then I searched a lot and got to know its a simple thing to upload and download database file .Its just take 5 to 10 sec to export a .sql file having size 150 to 250 MB.Then if .sql file a little high it may take another few sec .So is not it a simple way .This simple way is through command line.It can be done through putty if you are trying to access live data base and you have SSH credential details.Also If you want to do it on your localhost and you are using windows system you can do it through cmd and if you have Linux then command line.So lets see How to import export sql file from command line by some tricks and some command through which we can easily import and export .sql file.
Note: If you are trying to export and import through cmd and getting the error like mysql is not recognized as an internal or external command then you can set the path of your mysql location I mean you have to inform to system the location of mysql setup .For that you can do it by direct changing directory in cmd or setting path in environmental variable to system.Here I am not explaining all those things because this is not our topic.Leave that lets proceeds for different types of command.Also If you want to know how to set path in system environment variable and please feel free to comment below of this post to get the solution.
Note: Please don’t be confused I have used all example for my case having database name test password root and username root.
To Export:
If you want to export the entire database then :
$ mysqldump -u [uname] -p[pass] db_name > C:\Users\Jyotiranjan\Desktop\database_backup\db_backup.sql e.g: mysqldump -u root -proot test > C:\Users\Jyotiranjan\Desktop\database_backup\test_backup.sql
If you want to export the entire database then :
$ mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql e.g: $ mysqldump -u root -proot --all-databases > C:\Users\Jyotiranjan\Desktop\database_backup\all_db_backup.sql
If you your databse is so high and want compress then :
$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz e.g: $ mysqldump -u root -proot test | gzip > C:\Users\Jyotiranjan\Desktop\database_backup\db_backup.sql.gz
If you want to export a specific table within database then:
$ mysqldump -u [uname] -p[pass] db_name table1 table2 > C:\Users\Jyotiranjan\Desktop\database_backup\table_backup.sql e.g: $ mysqldump -u root -proot test student teacher > C:\Users\Jyotiranjan\Desktop\database_backup\table_backup.sql
Those are all about localhost system means in your local system.But you have access to the server having mysql port number 22 then :
$ mysqldump -P 22 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql
if you have dedicated databse server having some ipaddress then:
$ mysqldump -u username -p -h ipaddress databasename < data.sql
To Import:
To import is the simillar way to export but just use the symbole >(export) to <(Import)
If you want to import the entire database then :
$ mysql -u [uname] -p[pass] db_name < C:\Users\Jyotiranjan\Desktop\database_backup\db_backup.sql e.g: mysql -u root -proot test < C:\Users\Jyotiranjan\Desktop\database_backup\test_backup.sql
If you want to import the entire database then :
$ mysql -u [uname] -p[pass] --all-databases < all_db_backup.sql e.g: $ mysql -u root -proot --all-databases < C:\Users\Jyotiranjan\Desktop\database_backup\all_db_backup.sql
Also another easy trick I can give you here.That is if you can access SQL shell then:
use databasename; source data.sql;
