Though because of some quite good MySQL client software these days it has been quite easier to import and export the databases whenever required. But importing and exporting bigger files is quite difficult task for all. Specially, phpmyadmin (a web based popular application for browsing MySQL online written in PHP) rely on the PHP’s upload_max_filesize, memory_limit and post_max_size configurations in php.ini. So there are no other options to use command line MySQL somehow.
If there is access to the server via SSH, then by running some MySQL commands, we can export and import large databases as well.
Export
Exporting a single database:
mysqldump -u [dbuser] -p [dbpwd] [dbname] > dumpfile.sql
Export all the databases in the server (rare case though useful in local systems sometimes):
mysqldump -u [dbuser] -p [dbpwd] > dumpfile.sql
Export whole multiple databses:
mysqldump -u [dbuser] -p [dbpwd] –databases [dbname] [dbname] > dumpfile.sql
Exporting specific tables:
mysqldump -u [dbuser] -p [dbpwd] [dbname] [table1 table2]
Restore
Restoring database is mostly done from a sql dump file. The same filename dumpfile.sql will be used here.
Restore a database:
mysql -u [dbuser] -p [dbpwd] [dbname] < dumpfile.sql
Restore a bulk SQL with multiple database:
mysql -u [dbuser] -p [dbpwd] < dumpfile.sql
Zipped files to be imported:
gunzip < dumpfile.sql.gz | mysql -u [dbuser] -p [dbpwd]
Restore multiple dump files using cat command:
cat dumpfile1.sql dumpfile2.sql | mysql -u [dbuser] -p [dbpwd]
Note: if your database user password has some special characters, do not use the password in the command itself. Just leave -p without password and you will be asked to enter the password when you hit the enter key to execute the command.
Example:
mysqldump -u [username] -p [dbname] > [backupfile.sql]