Mysql – Getting recovered db files to work on rebuilt server

MySQLrestore

I've had to rebuild my server and have tried to copy the database files from the old drive to /var/lib/mysql/. I have restarted apache and run 'show databases', this list all the databases. I have entered one of these databases 'joomla' and run 'show tables' this shows all the tables I would expect.

However trying to run a query such as 'SELECT * from bsf_session' thows up and error:

ERROR 1146 (42S02): Table 'joomla.bsf_sessions' doesn't exist

checking the table I get:

mysql> check table bsf_seminar;
+--------------------+-------+----------+---------------------------------------------------------+
| Table              | Op    | Msg_type | Msg_text                                                |
+--------------------+-------+----------+---------------------------------------------------------+
| joomla.bsf_seminar | check | Error    | Can't find file: './joomla/bsf_seminar.frm' (errno: 13) |
| joomla.bsf_seminar | check | error    | Corrupt                                                 |
+--------------------+-------+----------+---------------------------------------------------------+
2 rows in set (0.00 sec)

When I copied the files over I change the permissions to match those of the mysql table, however I couldn't change ownership, the files currently look like this (ls -g):

-rw-rw---- 1 root   8842 2012-02-23 09:54 bsf_session.frm
-rw-rw---- 1 root 271012 2012-02-23 09:54 bsf_session.MYD
-rw-rw---- 1 root  25600 2012-02-23 09:54 bsf_session.MYI

I have tried to run

chown mysql bsf_session.frm

but this doesn't make a difference. I don't know if this is related to the main problem or not.

I'd really appreciate any help you could give…

I asked this on stackoverflow and was recommended to ask it here too…

Best Answer

If the user that the MySQL server does not have permissions to read and write to the files, then the server will not recognize the table. Usually the server runs under the 'mysql' user.

I would first stop your mysql instance before trying to change the ownership.

Once that is done, then (from a non-root user) do sudo chown mysql bsf_session.* in the directory with the files.

You could also try to change the group (sudo chgrp mysql bsf_session.*), however having the files as the same owner as the server is the best option.

If you do not have sudo privileges or root access, then you will need to contact whoever does to have them change the ownership.

Related Question