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.