Error recovering MYSQL db

Question

The following error occurs when I try to restore a database in MYSQL via putty.

Command: mysql -u root -p db1<dbname.sql ;

ERROR 1 (HY000) on line 7904: cannot create / write to file './dbname/db.opt' (Errcode: 2)

What is the reason?

Answer 1

This often means that your dump file contains a command that must be run against a database that either does not exist in your local context or that the current user does not have access to. Open the dump file and look at the line indicated in the error to see what happens.

Answer 2

I encountered this error when working when the source database name was different from the target database name. I dumped the database on one server using mysqldump db1 > dumpfile and tried to import it to another server with mysql db2 < dumpfile .

It turns out that the dumpfile had ALTER TABLE db1 ... statements that were pointless on the target server, where I named the db2 database.

Perhaps a more elegant solution than this, but I just edited dumpfile on the destination server and replaced db1 with db2.

Answer 3

Learn What Errcode: 2

You can use the perror utility to find what error 2 means:

 $ perror 2 OS error code 2: No such file or directory 

Additional information can be found at the @Jocelyn link mentioned in their comment: http://dev.mysql.com/doc/refman/5.5/en/cannot-create.html

Find out which path ./ points to

Now we know that the file does not exist (or perhaps it cannot be written.) The error message gives us the relative path ./ , which makes it complicated ... Wouldn’t it be useful if it displays a fully qualified path? Yes.

Therefore, when MySQL imports an SQL file, it creates several temporary files in the file system. The path is usually specified by the "tmpfile" configuration parameter in the my.cnf MySQL my.cnf . You can quickly find the value by running the SQL query:

 $ mysql -h127.0.0.1 -uroot -p # I assume you're now logged into MySQL mysql> SHOW VARIABLES LIKE '%tmpdir%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | slave_load_tmpdir | /tmp | | tmpdir | /tmp | +-------------------+-------+ 2 rows in set (0.00 sec) 

Make sure the directory is writable by mysql

According to tmpdir, this means that MySQL was trying to create /tmp/dbnamehere/db.opt . Make sure that this directory exists and that mysql:mysql belongs to it. You may need to use sudo to raise privileges sufficient to create some directories.

 $ chown -R mysql:mysql /tmp/dbnamehere 

Still not working? Try using other default tmpdir paths

I hit problems on my system (Ubuntu 12.04 + Vagrant 1.7.2 + Chef 11.something + opscode mysql cookbook 6.0.6 ), where the value in tmpdir not considered or deduced from the expected.

MySQL actually tried to create a temporary file in one of the following places:

• / var / lib / mysql / dbnamehere
• / var / lib / mysql default / dbnamehere

I had to create these directories and change the ownership of mysql: mysql.

Answer 4

You can find this error in the MySQL manual: http://dev.mysql.com/doc/refman/5.5/en/cannot-create.html