# MySQL Database Backup and Migration using Terminal

There can be multiple ways for MySQL/MariaDB database export and import during database server migration or database backup. The quickest seems to be the command-line one. It seems much faster than PHPMyAdmin or SequelPro clients. I am sharing Linux commands for MySQL/MariaDB export and import. 

## Export MySQL Database
MySQL data backup program **mysqldump** creates a dump file of SQL statements for table data and database schema.

Log into MySQL on Linux shell. Depending on your MySQL server settings, use
```
mysqldump -u username -p database_name > db_dump.sql
```
`mysqldump` does not show a success message. Do `ls` in the directory where you ran the command to find `DB-dump.sql`. You can include or exclude specific content into a dump and have the gzipped compression. For details visit MySQL dump: Database backup program [resources](https://medium.com/r/?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fmysqldump.html).

## Moving MySQL dump to a new server
In terminal use `scp` to move database backup.
```
scp user@oldhost:/home/user/db_dump.sql user@newhost:/home/user
```
For more details, check this [SCP command](https://dev.to/waqar/replace-ftp-with-the-scp-command-in-the-terminal-4kk2) post to move the file from a server to another using the terminal.

Depending on your convenience and database size you can move SQL dump via FTP, SCP, or simply move the dump to a public_html directory and access it on HTTP. On your destination server run a WGET command to copy the dump file.

You will need to create a new database and a new database user account aside from the root user.

### Import MySQL Database
On the Linux shell of the destination server, move to the directory where you have the database SQL dump file and run this command.
```
mysqlimport -u username -p database_name < db_dump.sql
```
you can also import in MySQL command line, log in to MySQL command line and run
```
mysql> use database_name;
mysql > source db-dump.sql
```
or run this one command in Linux shell
```
mysql -u username -p database_name < db_dump.sql
```
### mysqldump | A database backup program

Do not run `mysqldump` inside the MySQL console. The `mysqldump` is not a MySQL command. It is a separate program to be run in a Linux shell.

The **mysqldump** command can also generate output in CSV, other delimited text, or XML format. `mysqldump` is convenient and flexible as you can view and edit the output before restoring. However, restoring data can be slow because executing SQL statements involves disk I/O operations. The best use cases for mysqldump are small sites, WordPress blog database or during the development phase of a project. For large-scale databases using physical backup and restore is more appropriate.

**mysqldump** can also populate databases by copying data from one MySQL server to another:
```
mysqldump --opt db_name | mysql --host=remote_host -C db_name
```

#### mysqldump options
`--opt` Enabled by default, `--opt` is shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
- `--skip-add-locks` Do not add locks
- `--skip-opt` Turn off options set by `--opt`
- `--verbose` Verbose mode
- `--where` Dump only rows selected by given WHERE condition
- `--xml` Produce XML output

#### mysqldump filter options
- `--tables` add table name arguments following the option to dump only those tables
- `--ignore-table` exclude tables from dump `--ignore-table=db_name.tbl_name `
- `--no-data, -d` dump only the CREATE TABLE statement for the table