gravatar

Edmonds Commerce are a UK based E-Commerce web development agency. We are based in Shipley which is in Yorkshire near Leeds and Bradford.

Our primary specialism is Magento and Magento2 and other PHP based E-Commerce platforms such as OpenCart.

In terms of technology we are very strong in PHP, MySQL, Linux, HTML, CSS and Javascript.

SSH / Command Line Mysql Dump and Compress

Jan 28, 2009 · 2 minute read
Category: mysql

This is post is now quite old and the the information it contains may be out of date or innacurate.

If you find any errors or have any suggestions to update the information please let us know or create a pull request on GitHub

Some times you are working on a database that is just too big to export and import via phpMyAdmin. In those circumstances the best alternative is to use the Linux command line, usually via SSH.

If you want to be able to download the file easily via FTP, you need to first of all go to your web root. For example:


cd /home/myhome/public_html

If you are not sure where you web root is, you can log in via FTP and copy the path displayed in the remote pane of the FTP programme.

Then to create the database dump run the following command, replacing the dbuser and dbname with the mysql username and the name of the database you want to back up.


mysqldump --opt -u dbuser -p dbname > dbname.sql

You will then be prompted to enter the password for that mysql user. Enter this and mysql will then create an SQL dump file in your web root.

If the dump file is really big, it is usually a good idea to then compress this file to make moving it around a bit easier. To create a gzip compressed copy of the file, use the following command, replacing dbname.sql with the actual database name you used.


gzip -c dbname.sql>dbname.sql.gz

At this point you might decide to delete the uncompressed SQL file to conserve space. To do that run the following command, again replacing dbname.sql with the actual name of the database.


rm -f dbname.sql