Uğur Kazdal

I'm a Linux Ent. Who loves PHP, Python, Go and So.

Mysql Import And Export in CLI

» linux

Many times I don’t want to use Workbench or any other GUI supported DB Product, I mostly live in CLI, so I need to do my stuff in cli. Also Mysql Import and Export is easy on CLI, so let’s give it a shot.

This is how we export current mysql database,

mysqldump -u {USERNAME} -p {PASSWORD} database > db_backup.sql

If you don’t type password in this line you could just skip typing then cli asks you to put your password in secret mode. :)

Let’s say you want to dump all the databases you have in mysql so you can run this;

mysqldump -u {USERNAME} -p {PASSWORD}  --all-databases > db_backup.sql

Not enough ? You want to get geeky ? You need to export just tables ? Then we have;

mysqldump -u {USERNAME} -p {PASSWORD}  database table1 table2 > tables.sql

NOT ENOUGH ??? ARE YOU HANGRY !!!! you need to compress your mysql backup !!!

mysqldump -u {USERNAME} -p {PASSWORD}  database | gzip > backups.sql.gz

OMG. You do not want to ssh to your server and enabled remote access for mysql ? I see … so you should run this;

mysqldump -P 3306 -h {REMOTE_IP} -u {USERNAME} -p database > backup.sql

So all of those cli commands are for exporting mysql databases. What if you want to import a mysql database? Let’s try this command,

mysqldump -u {USERNAME} -p {PASSWORD} database < db_backup.sql

You saw that ? we just mirrored > to <.

One other tip to upload your sql to your server.

Please use scp just as

scp {WHAT} {WHERE} let’s give an example for scp

scp my_mysql_dump_file [email protected]_IP:/var/www/backups

Now you know how to upload via scp and import & export in mysql !

Related Posts