Export mysql database in Terminal- Dev Trick

Submitted by lwinmaungmaung on
Image by Sasin Tipchai from Pixabay

Trying to Export MySQL database is really hard if you are not used to export from the terminal. However, if you are using a command line like vagrant or some other tools, you have to deal with the terminal and you will now know that terminal is the fastest and easiest way to parse data and backup or restore data.

MySQL

Mysql is one of the most widely-used relational database in the world. It is set as default in CPanel Hosting and so on. There are two types of application or server: mysql and mariadb. They both have the same style and language, but different in processing.

Prerequisites

If you want to export mysql via ssh, you must have the followings:

  1. ssh - (username, hostname and password (or) key file if required)
  2. database (username, password)
  3. file storage for your database export on server
  4. fast connection to transfer back to your computer (optional)

 

Entering SSH

To export the database, try to connect to your server with the following command:

>> $ ssh username@host  

When you are logged in, the following information will be shown:

>> username@host /path/to/your/home:

Export Mysql Database

Exporting command is easy

Export Mysql database

>> $ mysqldump -u %username% -p %databasename% > %backupfilename%.sql

Let me explain in detail.

  1. mysqldump is the command to dump the database.
  2. -u is username option followed by %username% (if your username is root, you should type "-u root")
  3. -p is the password field. If you don't set the password, you don't have to add this option
  4. %databasename% is your database (if your databse name is test, you have to set test)
  5. %backupfilename% is the name you want to get sql

For example, I have to connect to customer database with username root  and password pass. I have to dump the database. I would like to set the file name to customer_backup.sql then:

>> $mysqldump -u root -p customer > customer_backup.sql

>> password: **** (test)

>> $ ls

>> rwx------     customer_backup.sql

That's it. Really easy!

If you cannot export then, there is something wrong with your syntax. Try again properly.

Reference: https://mariadb.com/kb/en/library/mysqldump/

 

Category