📄How do you import the database using CLI

There are several ways to import database

Database Import Script

In order to upload the database via the terminal, you must create the database first.

mysql> CREATE DATABASE db_name;

after creating the database, you can use

SHOW Database;

Statement to verify if the database exists.

Below script, you can use it import data into the existing database

mysql -u 'username' -p'password' -h 'host' --port='port' <existing_db> < <new_db>

the script I use for my development

mysql -u 'app' -p'app' -h 'mysql' --port='3306' swiss_db < german_new_db.sql

Database Backup Script

In order to take backup for database;

mysqldump -u [user] [database_name] > [filename].sql

Replace the database name & filename with your own used matching name

Step 1 — Exporting a MySQL or MariaDB Database

The mysqldump console utility exports databases to SQL text files. This makes it easier to transfer and move databases. You will need your database’s name and credentials for an account whose privileges allow at least full read-only access to the database.

Use mysqldump to export your database:

mysqldump -u username -p database_name > data-dump.sql

Copy

• username is the username you can log in to the database with

• database_name is the name of the database to export

• data-dump.sql is the file in the current directory that stores the output.

The command will produce no visual output, but you can inspect the contents of data-dump.sql to check if it’s a legitimate SQL dump file.

Run the following command:

head -n 5 data-dump.sql

Copy

The top of the file should look similar to this, showing a MySQL dump for a database named database_name.

SQL dump fragment-- MySQL dump 10.13  Distrib 5.7.16, for Linux (x86_64)
--
-- Host: localhost    Database: database_name
-- ------------------------------------------------------
-- Server version       5.7.16-0ubuntu0.16.04.1

If any errors occur during the export process, mysqldump will print them to the screen.

Step 2 — Importing a MySQL or MariaDB Database

To import an existing dump file into MySQL or MariaDB, you will have to create a new database. This database will hold the imported data.

First, log in to MySQL as root or another user with sufficient privileges to create new databases:

mysql -u root -p

Copy

This command will bring you into the MySQL shell prompt. Next, create a new database with the following command. In this example, the new database is called new_database:

CREATE DATABASE new_database;

Copy

You’ll see this output confirming the database creation.

OutputQuery OK, 1 row affected (0.00 sec)

Then exit the MySQL shell by pressing CTRL+D. From the normal command line, you can import the dump file with the following command:

mysql -u username -p new_database < data-dump.sql

Copy

• username is the username you can log in to the database with

• newdatabase is the name of the freshly created database

• data-dump.sql is the data dump file to be imported, located in the current directory

If the command runs successfully, it won’t produce any output. If any errors occur during the process, mysql will print them to the terminal instead. To check if the import was successful, log in to the MySQL shell and inspect the data. Selecting the new database with USE new_database and then use SHOW TABLES; or a similar command to look at some of the data.

MySQL :: MySQL 5.7 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program