Tag: Import Export MySQL Dumpfile

Import Export MySQL Dumpfile MySQL Server on Terminal

This is an alternative way to restore or import MySQL Dumpfile data with mysql command on Terminal.
Best tool to restore or import MySQL Dumpfile is phpMyAdmin, here is the tutorial to Set up Apache phpMyadmin and Nginx phpMyAdmin

This is restore or import dbuser.sql dumpfile into dbuser MySQL database using user as username:

First we upload MySQL dumpfile to terminal or home folder of user
Easy simple way by log in via SFTP (SSH File Transfer Protocol), this is same way a  user log via ssh. We can use Bitvise, Filezilla, etc.

Assume database ‘dbuser’ has been created and MySQL dumpfile (dbuser.sql) has been uploaded to user home folder, user log in to terminal and in home directory position.

#ls -la
dbuser.sql

Import: To MySQL dump data file to MySQL Server type the following command:

#pwd
/home/user
#mysql -u user -p -h localhost dbuser < dbuser.sql

Export: To Export a database and create dump file from MySQL Server type the following command:

#mysqldump -u [username] -p [database name] > [database name].sql

Important to avoid Mysql::Error: Incorrect string value: ‘\xE2\x80\xA8\x09 while inserting data to the table

#mysql -u root
MariaDB [(none)]>use dbname;
MariaDB [(none)]>ALTER TABLE dbname.tablename CONVERT TO CHARACTER SET utf8;