Export and import mysql database via cli

1. To export a MySQL database using the command line, you can use the mysqldump utility that comes with MySQL. Here are the steps:

   1. Open your terminal or command prompt.

   2. Run the mysqldump command with the necessary options.

   Here is the basic syntax for the mysqldump command:
   
   mysqldump -u [username] -p [database_name] > [output_file.sql]

• -u [username]: Replace [username] with your MySQL username.

• -p: This flag tells mysqldump to prompt for the password. After entering this command, you will be prompted to enter your password securely.
• [database_name]: Replace [database_name] with the name of the database you want to export.
• [output_file.sql]: Replace [output_file.sql] with the desired output file name.

Example

If your MySQL username is root and your database name is my_database, and you want to export it to a file named backup.sql, the command would look like this:

mysqldump -u root -p my_database > backup.sql

After running this command, you will be prompted to enter your MySQL password. Once you enter the password, the export process will begin, and a file named backup.sql will be created in your current directory containing the SQL commands to recreate the database.

if any warning occured then use this below command

mysqldump -h [remote_host] -u [username] -p --single-transaction --set-gtid-purged=OFF --all-databases --routines --triggers --events > [output_file.sql]

mysqldump -h localhost -u username -p --single-transaction --set-gtid-purged=OFF --routines --triggers --events dbname > dbname.sql

Import Database 

To import a MySQL database into a remote host, you can use the mysql command-line utility. Here is the command to import the dbname.sql file into your remote database:

mysql -h [remote_host] -u [username] -p [database_name] < [input_file.sql]

if Progress not displaying then install 

sudo apt-get install pv

pv dbname.sql | mysql -h hostname -u username -p dbname