FreeKB - mySQL / MariaDB Import .sql file
mySQL / MariaDB - Import .sql file

Before importing a .sql file into a mySQL or MariaDB database, you may want to determine if the file contains CREATE DATABASE IF NOT EXISTS command. If not, you will want to create the database before importing the .sql file.

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `myDatabase` /*!40100 DEFAULT CHARACTER SET latin1 */;

 

If the database does not exist, and the .sql file contains CREATE DATABASE IF NOT EXISTS, there is no need to include the name of the database on the command line.

mysql -u root -p < foo.sql

 

If the database does already exist, or the .sql file does not contains CREATE DATABASE IF NOT EXISTS, you will need to include the name of the database on the command line. Be aware that this will append data to the database.

mysql -u root -p databasename < foo.sql

 

If the file is gzip compressed, use this syntax.

zcat the_file.sql.gz | mysql -u root -p databasename

 

In this scenario, while the zcat and mysql commands are running, the ps command should return the following.

~]# ps
  PID TTY          TIME CMD
 2905 pts/0    00:00:00 su
 2910 pts/0    00:00:00 bash
 3105 pts/0    00:00:00 zcat
 3106 pts/0    00:00:00 mysql
 3109 pts/0    00:00:00 ps

 

To verify that the data was imported, connect to your MySQL or MariaDB, and use the show databases, use database, show tables and select * from tablename commands.



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter daff4 in the box below so that we can be sure you are a human.




Comments

Web design by yours truely - me, myself, and I   |   jeremy.canfield@freekb.net   |