Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to import multiple database at once through PhpMyAdmin?

Error

SQL query:

--
-- Database: `attend`
--

-- --------------------------------------------------------

--
-- Table structure for table `attend`
--

CREATE TABLE IF NOT EXISTS `attend` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) NOT NULL DEFAULT 'name',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `timeing` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

MySQL said: Documentation
#1046 - No database selected 

I am shifting my databases. I exported all the database at once.
So during import I cannot select single database.
And by directly importing sql file i am getting the above error.

Possible solutions found on phpmyadmin import database error
But dont know how to implement this solution for about 30 database in .sql file
Please help me as i have deleted old database after exporting all database, and need to import mASAP.

like image 536
Akash Kumar Avatar asked Mar 02 '14 05:03

Akash Kumar


3 Answers

The problem is that SQL needs to explicitly be told what database you're using, either on a global level or on each individual table create query. There's two things we need to do here:

A) If that database doesn't exist, you'll need to create it first. There's no way to put something in a database that isn't there.

B) Make sure you tell the query which database to use. The solution you linked uses this solution. You'll only want to use it if all the tables are going to one database. The line is:

USE `database_name`;

replacing database_name with the name of the database that you're going to use.


However, if each table belongs to a different database, you'll want to change the following:

CREATE TABLE IF NOT EXISTS `attend` (

to

CREATE TABLE IF NOT EXISTS `database_name`.`attend` (

replacing database_name with the name of the database that table is going to be a part of.

like image 161
BretHudson Avatar answered Nov 15 '22 12:11

BretHudson


In the current 4.1 version of phpMyAdmin, when you export at the server level, if you open the Custom export options, you'll see a choice "Add CREATE DATABASE / USE statement". This option is what you need to select.

like image 22
Marc Delisle Avatar answered Nov 15 '22 11:11

Marc Delisle


as stated at askubuntu.com, you can use:

-export using: mysqldump -u username -p –-all-databases > dump.sql
-import using: mysql -u username -p < dump.sql

like image 32
T.Todua Avatar answered Nov 15 '22 12:11

T.Todua