I am trying to restore database from .sql file , i have created the database in phpmyadmin and also using the create if not exist command in the .sql file which i am restoring to the database and both names of database are same in phpmyadmin and .sql file which is"mydatabase".
Here is the command which i am using to restore database.
mysql -uroot -pmypassword mydatabase<mydatabase.sql;
When i execute the above command i am getting the following error, i have also given all the permission to the user upon this database.
ERROR 1049 (42000): Unknown database 'mydatabasename'
Please help me how can i solve this issue. Thanks,
This question is related to
mysql
Create database which gave error as Unknown database, Login to mysql shell:
sudo mysql -u root -p
create database db_name;
Now try restoring database using .sql file, -p flag will ask for a sql user's password once command is executed.
sudo mysql -u root -p db_name < db_name.sql
I solved because I have the same problem and I give you some clues:
1.- As @eggyal comments
mydatabase != mydatabasename
So, check your database name
2.- if in your file, you want create database, you can't set database that you not create yet:
mysql -uroot -pmypassword mydatabase<mydatabase.sql;
change it for:
mysql -uroot -pmypassword <mydatabase.sql;
If initially typed the name of the database incorrectly. Then did a Php artisan migrate .You will then receive an error message .Later even if fixed the name of the databese you need to turn off the server and restart server
I found these lines in one of the .sql files
"To connect with a manager that does not use port 3306
, you must specify the port number:
$mysqli = new mysqli('127.0.0.0.1','user','password','database','3307');
or, in procedural terms:
$mysqli = mysqli_connect('127.0.0.0.1','user','password','database','3307');"
It resolved the error for me . So i will suggest must use port number while making connection to server to resolve the error 1049
(unknown database).
Whatever the name of your dump file, it's the content which does matter.
You need to check your mydatabase.sql
and find this line :
USE mydatabasename;
This name does matter, and it's the one you must use in your command :
mysql -uroot -pmypassword mydatabasename<mydatabase.sql;
Two options for you :
USE mydatabasename;
in your dump, and keep using :mysql -uroot -pmypassword mydatabase<mydatabase.sql;
mysql -uroot -pmypassword mydatabasename<mydatabase.sql;
La Chi's answer works for me.
You can view his/her answer in the comment of zessx answer in this page. But I initially have a problem with it if you also do just tweak his/her answer like this: mysql -h localhost -u root -p -D mydatabase < mydatabase.sql
.
Also I would suggest that in the mydatabase.sql
portion you include the direct location of it in your computer like "C:\Users\username\desktop"
.
Thank you.
when u import database from workbench or other method ,should be give same name as your dump to avoid this kind of error
mysql -uroot -psecret mysql < mydatabase.sql
Open the sql file and comment out the line that tries to create the existing database and remove USE mydatabasename
and try again.
If dump file contains:
CREATE DATABASE mydatabasename;
USE mydatabasename;
You may just use in CLI:
mysql -uroot –pmypassword < mydatabase.sql
It works.
I had the same issue, i run this command on command line and just like you i had added the ';' at the end. Removing it solved the issue. Instead of this
mysql -uroot -pmypassword mydatabase<mydatabase.sql;
try this
mysql -uroot -pmypassword mydatabase<mydatabase.sql
You can also create a database named 'mydatabasename' and then try restoring it again.
Create a new database using MySQL CLI:
mysql -u[username] -p[password]
CREATE DATABASE mydatabasename;
Then try to restore your database:
mysql -u[username] -p[password] mydatabase<mydatabase.sql;
Source: Stackoverflow.com