How to import database from sql file into Mysql

Mysql SQL Mysql command

A thorough explanation of how to import SQL files using the command-line interface will be given. 
 This guide will cover the steps for using a hosted MySQL server shell.

import database

 mysql -u username -p database_name < path_to_mysql_file.sql

 

Backup database

 mysqldump -u root -p db_name > path_to_mysql_file.sql

 

multiple databases or all the databases

 mysqldump -u root -p --databases db_1 db_2 > path_to_mysql_file.sql

 mysqldump -u root -p --all-databases > path_to_mysql_file.sql

backup specific table

To generate a backup of the table1 and Table2 tables in the db_name database, the names of the tables must be separated by a space in the following command.

 mysqldump -u root -p db_name table1 table2 > path_to_mysql_file.sql

Backup only database structure

To backup the structure of a database, the mysqldump command must be used with the –no-data  option. The command for backing up the structure of the db_name database is as follows.

 mysqldump -u root -p --no-data db_name > path_to_mysql_file.sql

 

Backup only database data

To backup the data without the structure of a database, the mysqldump command must be used with the –no-create-info option. The command for backing up the data of the db_name database is as follows.

 mysqldump -u root -p db_name --no-create-info > path_to_mysql_file.sql

 

Mysqldump syntax

 mysqldump -u [username] –p [password] [options] [database_name] [table_name] > [dump_filename.sql]
  1. u [user_name]: It is a username to connect to the MySQL server. To generate the backup using mysqldump, ‘Select‘ to dump the tables, ‘Show View‘ for views, ‘Trigger‘ for the triggers. If you are not using —single-transaction option, then ‘Lock Tables‘ privileges must be granted to the user
  2. p [password]: The valid password of the MySQL user
  3. [option]: The configuration option to customize the backup
  4. [database name]: Name of the database that you want to take backup
  5. [table name]: This is an optional parameter. If you want to take the backup specific tables, then you can specify the names in the command
  6. “<” OR ”>”: This character indicates whether we are generating the backup of the database or restoring the database. You can use “>” to generate the backup and “<” to restore the backup
  7. [dumpfilename.sql]: Path and name of the backup file. As I mentioned, we can generate the backup in XML, delimited text, or SQL file so we can provide the extension of the file accordingly