How to backup your MySQL database from Terminal in Ubuntu

Tutorial shows how to backup and import backups using mysqldump from a terminal.

It can be important that you know how to backup, and restore backups of your MySQL databases, but before we show how to import backups, let us first cover how to make a backup. To make a backup of a database, we will be using mysqldump, the following command should work on any system:

mysqldump --opt -hHOST -uUSER -pPASSWORD DatabaseName > /myfilename.sql

If this doesn't work on your system, try to use the exact path for mysqldump I.e.

/opt/lampp/bin/mysqldump --opt -hHOST -uUSER -pPASSWORD DatabaseName > /myfilename.sql

To compress your backups using gzip, you can use the vertical bar | character followed by gzip, just after the DatabaseName I.e.

mysqldump --opt -hHOST -uUSER -pPASSWORD DatabaseName | gzip > /myfilename.sql.gz

How to backup specific tables

In case you only need to backup a single table, you would first type the database name, then the name of the table that you want to dump.

mysqldump --opt -hHOST -uUSER -pPASSWORD Database_Name Table_Name > /Table_Name.sql

Backup two or more tables

To backup two or more tables in a database, leaving the rest behind, simply add the table names after the database name like shown below.

mysqldump --opt -hHOST -uUSER -pPASSWORD Database_Name Table_Name Table_Name2 > /my_database_backup.sql

Skip certain tables

You can also Exclude a single table using the --ignore-table option, this can be done like shown below.

mysqldump --opt -hHOST -uUSER -pPASSWORD Database_Name Table_Name --ignore-table=Database_Name.Table_Name > /backup.sql

Importing backups made with mysqldump

Importing a backup can be done by logging into MySQL through the terminal, just by typing the below:

mysql -uUSER -pPASSWORD

After logging in, you may need to create the database if it does not allready exist. I.e.

mysql> create database My_Database_Name;

Before you will be able to import your backup into the database, you also need to select the database. I.e.

mysql> use My_Database_Name;

All you need to do now is to import the database, which can be done using the MySQL source command I.e.

mysql> source /myfilename.sql;

The backup will then be imported into the database you just created.

More Tutorials

  1. Connecting to a MySQL server through Terminal