Error 1049 sql - Исправление ошибок и поиск оптимальных решений проблем

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'

BenMorel

33.5k49 gold badges174 silver badges310 bronze badges

asked Oct 30, 2013 at 9:44

If dump file contains:

CREATE DATABASE mydatabasename;
USE mydatabasename;

You may just use in CLI:

mysql -uroot –pmypassword < mydatabase.sql

It works.

answered Mar 24, 2016 at 17:02

DobpbiuDobpbiu

4814 silver badges4 bronze badges

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 :

Remove USE mydatabasename; in your dump, and keep using :
mysql -uroot -pmypassword mydatabase<mydatabase.sql;
Change your local database name to fit the one in your SQL dump, and use :
mysql -uroot -pmypassword mydatabasename<mydatabase.sql;

answered Oct 30, 2013 at 10:02

zessxzessx

67.5k28 gold badges135 silver badges157 bronze badges

Open the sql file and comment out the line that tries to create the existing database and remove USE mydatabasename and try again.

answered Oct 30, 2013 at 10:05

Sathish DSathish D

4,83629 silver badges44 bronze badges

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;

answered Oct 11, 2016 at 8:20

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;

answered Jan 31, 2017 at 2:55

molavecmolavec

8,6311 gold badge26 silver badges21 bronze badges

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

answered Oct 19, 2020 at 8:05

Nabeel ShaikhNabeel Shaikh

1,1391 gold badge13 silver badges27 bronze badges

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:Usersusernamedesktop".

Thank you.

joanolo

5,7581 gold badge26 silver badges35 bronze badges

answered Aug 1, 2017 at 19:08

jhamezzz1315jhamezzz1315

231 gold badge4 silver badges10 bronze badges

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

answered Dec 9, 2019 at 17:19

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

answered Jan 23, 2020 at 15:42

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).

Rohan Shah

8491 gold badge8 silver badges26 bronze badges

answered Feb 20, 2020 at 11:30

mysql -uroot -psecret mysql < mydatabase.sql

answered May 29, 2020 at 14:40

I meet your issue. This is how to solve it

Check your DB name correct and exist in MySQL
Check if your IP and port is correct

answered May 26, 2021 at 10:18

It works by creating database and than typing command as :
C:Program FilesMySQLMySQL Server 8.0bin>mysql -u root -p -D cricket < C:Usershabib_s9ayvflDesktopsqlfile.sql

answered Aug 12, 2021 at 10:14

Create database:

CREATE DATABASE mydatabasename;
USE mydatabasename;

use this one:
mysql -u root -p ‘mydatabasename'< ‘/tmp/db_dump.sql’

answered Feb 11, 2022 at 10:33

Its very simple: Use mysql -u root -p mysql

answered Mar 25, 2022 at 9:58

sreesree

511 silver badge3 bronze badges

first, you need to check the folder /var/lib/mysql for mydatabasename (depend on how you installed mysql, but default folder is this one),
please check the folder exists or not and its owner should be mysql:mysql, and of course the folder permission should be rw to mysql;

second, possibly because of you made changes to /etc/my.cnf, for example in my case, we created a database TEST_DB in uppercase, and then someone added lower_case_table_names=1 restriction in my.cnf, it caused the Unknown database error because mysql will transalte TEST_DB to lowercase test_db even when i key in select from TEST_DB, so it’ll never find TEST_DB, simply comment out and restart mysql service solved my issue

answered Apr 30, 2022 at 8:12

LIU YUELIU YUE

1,45511 silver badges18 bronze badges

You can also try

> mysql mysql

and you will connect to MySQL database from which you can create your own schema.

mysql> CREATE DATABASE mydb; USE mydb;

answered Jun 20, 2022 at 12:05

alexlzalexlz

6081 gold badge9 silver badges24 bronze badges

when u import database from workbench or other method ,should be give same name as your dump to avoid this kind of error

answered Jul 24, 2020 at 11:56

Источник

Often while performing WHM backups, users notice an error “mysqldump: Got error: 1049: Unknown database”. This can happen when the database does not exist in MySQL.

As a part of our Server Management Services, we help our customers with similar requests related to WHM/ cPanel.

Let us today, discuss the possible reasons and fixes for this error.

What causes “MySQLdump: got error :1049 :unknown database ” error

MySQLdump helps to perform the logical backups, generating a set of SQL statements like DDL, DML for reproduced backup Schema. It dumps one or more MySQL databases for backup or transfers to another SQL server.

We can also generate output in CSV, other delimited text or XML format. The main drawback of MySQLdump is that the restoration procedure can take a long time to execute if the database is very large.

While performing WHM backups, at times, we can see the following error in the backup log:

The backup process encountered the following error: The backup process on “hostname.example.server” encountered an error.
[2021-05-10 02:25:26 -0600] mysqldump: Got error: 1049: Unknown database ‘example_database’ when selecting the database

Generally, This error indicates that the related database exists in a cPanel user’s database map, but the database does not exist in MySQL.

How to fix “MySQLdump: got error :1049 :unknown database ” error

The first thing that our Support Engineers perform on seeing this error is to check whether the database exists within MySQL. They does this by running the following command as the root user via SSH:

mysql -e "show databases;" | grep example_database

Replace example_database with the database found within the backup error in the backup logs. We can find the backup logs within /usr/local/cpanel/logs/cpbackup.

If the above command does not display any results, it indicates that the database does not exist in MySQL.

Thus, In order to correct the backup errors, we have to remove the databases that do not actually exist in MySQL from cPanel.

For this, we initially log in to the cPanel account for the particular database user. Then, we navigate to the Databases section and then click on the MySQL Databases option.

Here, we just need to delete the corresponding database from the current database section.

[Need any further assistance to fix cPanel errors? – We’re available 24*7]

Conclusion

The “MySQLdump: got error :1049 :unknown database ” triggers while performing cPanel backups. This can happen when the database does not exist in MySQL. Today, we saw how our Support Engineers fix this error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SEE SERVER ADMIN PLANS

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

In fact, I made a very serious mistake, and this mistake can only be reported for two reasons.
Reason 1: There is an extra space after your account password, which means you have the wrong database.
Fix: If it is a login password, report this error, as shown in the figure below:

It would be possible to have an extra space between p and 123, and then you would remove the space in between. You put p and 123 together and you type in p123
Reason 2: Your syntax is wrong, because use can only be followed by the database name, never the table name. An incorrect or incorrect table name will report the following error.

Solution: Double check your database to see if it has TB_EMP6.

Check your table again to see if it is your table name.

The name of the table is confused with the database name.
Summary is to confirm the database name is not exist, there are no spelling mistakes, if not again to see your grammar is not wrong.
This is better to locate your problem, in fact, to put it bluntly this is I made a stupid mistake, but also eat a catty gain a wisdom.
Hope to help you, welcome to leave a message to exchange ~

Источник

Mysqldump is a tool frequently used in creating a backup of a mariadb or mysql database. To use this tool is pretty straight forward, just run below command:

$ mysqldump -u root -p mydatabasename > mydatabasename.sql

The above command is fine, and we can always restore the data from the sql file into a database provided we have the database already in place, using below command:

$ mysql -u root -p mydatabasename < mydatabasename.sql

A problem appears when we are transferring the sql file to another server which does not have the database already created. If we try to import the sql file, without the database already existed, we will get below error:

ERROR 1049 (42000): Unknown database mydatabasename

We can prevent this by adding an option to our mysqldump command. The option is «—databases» or in short «-B». To test it out, we can use below commands (dump the db, drop the db, and import back the db from the sql file):

$ mysqldump -u root -p —databases mydatabasename > mydatabasename.sql

$ mysqladmin -u root -p drop mydatabasename

$ mysql -u root -p < mydatabasename.sql

This time, you would not get the above error, since the «—databases» option will add «CREATE DATABASE» query into the sql file, and that query will create the database if the database is not already exist.

Источник

This document (7009762) is provided subject to the disclaimer at the end of this document.

Environment

SUSE Linux Enterprise Server 10 Service Pack 1

SUSE Linux Enterprise Server 10 Service Pack 2

SUSE Linux Enterprise Server 10 Service Pack 3

SUSE Linux Enterprise Server 10 Service Pack 4

SUSE Linux Enterprise Server 11 Service Pack 1

SUSE Linux Enterprise Server 11 Service Pack 2

Situation

ERROR 1049 (42000): Unknown database ‘mysql’ when trying to access mysql via this command:

this command will access the default ‘mysql’ database, and needs to be done prior to setting or resetting the root users’ MySQL password.

Resolution

Depending on how MySQL was installed, it is possible that the default MySQL database was NOT created.

This may be checked by looking in /var/lib/mysql for a mysql subfolder (i.e. /var/lib/mysql/mysql ). If the path does NOT contain a mysql subfolder, it needs to be created by completing the following steps:

rcmysql stop
pkill mysql   (NOTE: wait until notification is given that mysqld ended, then hit <ENTER>)
/usr/bin/mysql_install_db

Now that the above steps have been run, check and make sure that the database was created:

cd /var/lib/mysql
ls -al | grep mysql

In the listing output there should now be a folder called mysql. Finally, the correct owner and group need to be set on the mysql folder.

Restart the mysql service in safe mode again and attempt the initial command again:

Disclaimer

This Support Knowledgebase provides a valuable tool for SUSE customers and parties interested in our products and solutions to acquire information, ideas and learn from one another. Materials are provided for informational, personal or non-commercial use within your organization and are presented «AS IS» WITHOUT WARRANTY OF ANY KIND.

Document ID:7009762
Creation Date:
17-Nov-2011
Modified Date:12-Aug-2022
- SUSE Linux Enterprise Server

< Back to Support Search

For questions or concerns with the SUSE Knowledgebase please contact: tidfeedback[at]suse.com

Источник

Based on your comments below your question, it sounds like you probably omitted the --databases option when you used mysqldump to backup the database.

If you backup the databases by doing mysqldump <database-name>, then the backup does not re-create the database for you. When you restore from that backup, you need to create the database that you want to import into and then import into that database, which may or may not be the same name as the database you exported from earlier.

If you do not want to be required to do this, then, when creating your backup, you export like this: mysqldump --databases <database-name>. Doing that, the backup will have the command to re-create the database when it is imported back into MySQL.

There are also other differences between those two usages. If you use the first version, then any symbols after the database name are considered table names to include in the backup. As in: mysqldump MyDatabase Table1 Table2 Table3 to backup tables 1, 2, and 3, but no others.

In the second variation, all symbols after the initial database name are also treated as additional database names, so you can get multiple databases. mysqldump --databases HRDatabase WebsiteDatabase DevTestDatabase That should export all three databases.

But back to the main point: next time you use mysqldump, if you specify the --databases option then you will not need to manually create the database and use it before importing, as that would then be taken care of for you.

For importing the database you need to use mysql not mysqldump in order to create the database automatically (only in case you exported with --databases <database-name> option:
mysql -u root -p < /var/www/html/example.com/backups/backup.sql
(as mentioned in a previous comment by HBruijn,

Using mysqldump for importing your database will not create your database automatically even if you exported it with --databases <database-name> option, these will not work without creating the database first:

mysqldump -u root -p < /var/www/html/example.com/backups/backup.sql
mysqldump --all-databases -u root -p < /var/www/html/example.com/backups/backup.sql
mysqldump --databases mydatabase_name -u root -p < /var/www/html/example.com/backups/backup.sql

Источник

Я пытаюсь восстановить базу данных из файла .sql, я создал базу данных в phpmyadmin, а также используя команду create if not exist в файле .sql, который я восстанавливаю в базе данных, и оба имени базы данных одинаковы в phpmyadmin и .sql файл, который является «mydatabase».

Вот команда, которую я использую для восстановления базы данных.

mysql -uroot -pmypassword mydatabase<mydatabase.sql;

Когда я выполняю вышеуказанную команду, я получаю следующую ошибку, я также дал все разрешения пользователю в этой базе данных.

ERROR 1049 (42000): Unknown database 'mydatabasename'

Пожалуйста, помогите мне, как я могу решить эту проблему.
Спасибо,

30 окт. 2013, в 11:11

Источник

6 ответов

Если файл дампа содержит:

CREATE DATABASE mydatabasename;
USE mydatabasename;

Вы можете просто использовать в CLI:

mysql -uroot –pmypassword < mydatabase.sql

Он работает.

Dobpbiu
24 март 2016, в 17:05

Откройте файл sql и закомментируйте строку, которая пытается создать существующую базу данных, и удалите USE mydatabasename и повторите попытку.

Sathish D
30 окт. 2013, в 10:29

Независимо от имени вашего файла дампа, оно имеет значение, которое имеет значение.

Вам нужно проверить mydatabase.sql и найти эту строку:

USE mydatabasename;

Это имя имеет значение, и оно должно использоваться в вашей команде:

mysql -uroot -pmypassword mydatabasename<mydatabase.sql;

Два варианта для вас:

Удалите USE mydatabasename; в своем дампе и продолжайте использовать:
mysql -uroot -pmypassword mydatabase<mydatabase.sql;
Измените локальное имя базы данных так, чтобы оно соответствовало таковому на вашем SQL-дампе, и используйте:
mysql -uroot -pmypassword mydatabasename<mydatabase.sql;

zessx
30 окт. 2013, в 10:06

Я решил, потому что у меня такая же проблема, и я даю вам несколько подсказок:

1.- Как комментарии @eggyal

mydatabase != mydatabasename

Итак, проверьте имя своей базы данных

2.- если в вашем файле вы хотите создать базу данных, вы не можете установить базу данных, которую вы еще не создали:

mysql -uroot -pmypassword mydatabase<mydatabase.sql;

измените его на:

mysql -uroot -pmypassword <mydatabase.sql;

molavec
31 янв. 2017, в 02:56

Ответ La Chi работает для меня.

Вы можете просмотреть его ответ в комментарии zessx на этой странице. Но у меня изначально возникла проблема с этим, если вы также просто настраиваете его ответ следующим образом: mysql -h localhost -u root -p -D mydatabase < mydatabase.sql.

Также я бы предположил, что в разделе mydatabase.sql вы указываете прямое расположение на своем компьютере, например "C:Usersusernamedesktop".

Спасибо.

jhamezzz1315
01 авг. 2017, в 21:05

Вы также можете создать базу данных с именем ‘mydatabasename’, а затем попытаться восстановить ее снова.

Создайте новую базу данных с помощью MySQL CLI:

mysql -u[username] -p[password]
CREATE DATABASE mydatabasename;

Затем попробуйте восстановить базу данных:

mysql -u[username] -p[password] mydatabase<mydatabase.sql;

Iulian Cucoanis
11 окт. 2016, в 10:19

Ещё вопросы

0Запуск приложения в фоновом режиме и перехват входящих и исходящих сообщений
0Фатальная ошибка новичка lkn1104 при запуске простого кода
1Android — устройства-слайдеры вызывают сброс действий
1Как использовать потоковый и файл добавить в Windows Phone8.1?
0Создается только первое динамически генерируемое поле выбора
0Как проверить, существуют ли данные в базе данных независимо от имени столбца? [Дубликат]
1Транзакционная аннотация не работает
0Проверка входных данных int C ++
0удалить изображения после клонирования изображений
0Использование списка строковых элементов в качестве источника для компонента Select2
1Обслуживание Tensorflow: в get_model_status отсутствуют «утилиты»
0Используйте html гиперссылки для индексирования файловых папок
0nginx PHP переписывает профиль пользователя
1python для эффективного использования результата функции в операторе if
1Как мне эффективно извлечь стековые трассировки из потока сообщений журнала?
0Как я могу проверить, есть ли Angular.js, загруженный на страницу? [Дубликат]
1Несоответствие количества транзакций с многоуровневыми транзакциями
1Числовой эквивалент if / elif / else, который сохраняет последнее значение, если условие не выполняется
1Как я могу создать равномерно расположенные кнопки?
1Файл json не создается с помощью Python Scrapy Spider
0Ошибка отладки C ++
0отделите каждую букву с помощью jquery
1Как мне использовать значение локальной переменной в angular2?
0Подключение Json к Jquery для добавления маркеров на Google Map
0JQuery / JavaScript исчезают в каждом элементе?
0Отправка данных в API и извлечение их в сетку
1Случайное расписание Alarm Manager с календарем
1Android: повторяющийся рабочий процесс Android. Огонь и забыть?
0HTML-оператор выбора и кнопка не работают с PayPal
0Почему этот SQL-запрос на создание таблицы выдает ошибку «# 1071 — Указанный ключ слишком длинный; максимальная длина ключа составляет 767 байт »
0Не удается заставить JQuery валидации работать с использованием правил, метода сообщений
1Возможно ли иметь десятичную ось X для линейного графика?
1Tinymce: не дублировать класс при добавлении нового абзаца при вводе
0Почему моя программа не определяет простые числа?
0PHPUnit Явные тесты
1RTSP live streaming не работает на Android 1.5 / 1.6?
1Странная вещь о выводе контента JSON в python
0Bootstrap 3: фиксированный столбец с отзывчивым столбцом
1Сгруппируйте столбец, если ни одна из строк не является уникальной, используя панд
1пользовательский вид изображения для Android
0Разница между объектом параметров AngularJS UI Router, указанным в состоянии и представлениях
0Наведите, над и .png картинки с прозрачным фоном
0Какой из способов является лучшим для производительности, чтобы установить событие наведения на элемент div?
0Выберите контрольный поиск на основе полей данных
1Показать все шаги для преобразования данной строки в другую (анаграммы)
1Битовая упаковка номеров фраз LZW
1подключение двух устройств Android через сокеты TCP
0Как получить список изображений для удаления из одного запроса MYSQL
0Скрыть содержимое таблицы, не вкладывая его в столбец
0Prestashop — Добавить модуль на крючок

Источник

What causes “MySQLdump: got error :1049 :unknown database ” error

How to fix “MySQLdump: got error :1049 :unknown database ” error

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Read More:

Environment

Situation

Resolution

Disclaimer

6 ответов

Ещё вопросы