Restore MySQL backup

marek · June 10, 2013, 6:13am

Hello all!

First posting here! So be patient with me.

I made a clean install with MacOS 10.8 and need to restore my Databases from my external backup drive. Apparently it is not possible, to create the MySQL user and password as before, and simply drag the databases from /backup/usr/local/mysql-5.5.13-osx10.6-x86_64/data/ into /usr/local/mysql-5.6.11-osx10.7-x86_64/data/

I checked the owners _mysql:wheel the permissions everything. Everything is as before on the backup. (off topic question: why is there an underscore before _mysql?)

When I log in as root:

mysql -u root -h localhost -p
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| webdb              |
+--------------------+
mysql> USE webdb
mysql> SHOW TABLES;
+-------------------+
| Tables_in_webdb   |
+-------------------+
| anz_werte         |
| einsendung        |
...
+-------------------+

But I am unable to see what is inside the tables with:

mysql> SELECT * FROM todo;
ERROR 1146 (42S02): Table 'webdb.todo' doesn't exist

Same when I log in as user webdev ...

Hope this first question was clear enough.

Best greetings from Munich

marek

radoulov · June 10, 2013, 6:36am

Hi marek,
I'm not familiar with Mac OS and I have no idea why the OS username is _mysql,
any valid OS name could be used.
Did you stop the database before copying the datafiles?
In any case, check the content of the mysql error log, it should give you a clue.

marek · June 10, 2013, 12:12pm

Thank you radoulov!

ooops! This is a good idea! BUT where is this error log?

sudo find . -iname "*.log" -print
sudo find . -iname "*error*" -print

does not help (searching in /usr/local/mysql-5.6.11-osx10.7-x86_64). Sorry for this beginner question! And the system.log in /var/log/ does not tell anything of mysql server ...

marek

ps: I tried mysqlaccess like follows:

mysqlaccess -h localhost --user=webdev --password=webdevpass --db=webdb
mysqlaccess Version 2.06, 20 Dec 2000
...
Sorry,
An error occured when trying to connect to the database
with the grant-tables:
* Maybe YOU do not have READ-access to this database?
* If you used the -U option, you may have supplied an invalid username?
  for the superuser?
* If you used the -U option, it may be possible you have to supply
  a superuser-password to, with the -P option?
* If you used the -P option, you may have supplied an invalid password?

And

mysqlaccess -h localhost -U --password=XXXX --db=webdb
mysqlaccess: No match.

Does this help more as information?

radoulov · June 10, 2013, 12:15pm

It seems that you're able to log into mysql as root.

mysql -u root -h localhost -p

You could find the error log name and path with:

show global variables like 'log_error';

marek · June 11, 2013, 10:01am

Yes, radoulov, I am able to log in as root, and read the Databases, but not the content of the Tables with SELECT ... And the same as user webdev ...

Meanwhile I found the error log. it resides in /usr/local/mysql-5.6.11-osx10.7-x86_64/data/localhost.err

Here I find plenty of errors like follows

2013-06-10 18:04:17 3146 [Warning] InnoDB: Cannot open table webdb/todo from the internal data dictionary 
of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html 
for how you can resolve the problem.

I will look into this reference tomorrow. Too late now! Perhaps I will find the mistake myself.

marek

---------- Post updated 06-11-13 at 08:37 AM ---------- Previous update was 06-10-13 at 11:46 PM ----------

I made a try, and created new tables as user webdev in the webdb. And apparently there is something wrong with my backup:

-rw-rw----  1 _mysql  wheel   8598 Jun  7 11:45 anz_werte.frm
-rw-rw----  1 _mysql  wheel   8690 Jun 11 08:04 authors.frm
-rw-rw----  1 _mysql  wheel  98304 Jun 11 08:09 authors.ibd
-rw-rw----  1 _mysql  wheel   8859 Jun 11 08:00 books.frm
-rw-rw----  1 _mysql  wheel  98304 Jun 11 08:11 books.ibd

In my backup are now files with extension .ibd as you may see with the first table "anz_werte.frm". I am stumbled! Where have gone these .ibd files?

I am not at home, so I am unable to check my backup now. I keep you informed this evening ...

marek

---------- Post updated at 04:01 PM ---------- Previous update was at 08:37 AM ----------

Hello all!

I am getting closer to the problem:

I switched from my installed mysql-5.6.11-osx10.7-x86_64 version to the preinstalled version (on Mountain Lion, MacOS 10.8) mysql-5.5.13-osx10.6-x86_64

(I simply changed the symbolic link mysql pointing to the older version of mysql-5.5)

Everything is working here! But here are no .ibd files. Does this mean, I installed the wrong version of MySQL? Or do I have to "upgrade" my old databases from version 5.5 ot 5.6 ?

Thank you for your help!

marek

radoulov · June 11, 2013, 11:23am

You definitely need to upgrade mysql (mysql_upgrade) if you want to use a newer release with the old datafiles.
You'll always have .idb files (the system tablespace, for example), unless you disable InnoDB completely.
They may or may not be in your <db_name>/ directory.

marek · June 11, 2013, 9:37pm

For me it is not clear, what means InnoDB and what changed in the update of MySQL. I don't know either, how to switch off InnoDB. But I will read the refman.

In any case I made

mysql_upgrade -u webdev -p
...
webdb.anz_werte
Error    : Table 'webdb.anz_werte' doesn't exist
status   : Operation failed
webdb.authors                                      OK
webdb.books                                        OK
...

My freshly created Tables authors and books are validating. I don't know what to do. Probably I will stick to the old MySQL.

Thank you again radoulov

marek

radoulov · June 12, 2013, 5:00am

Hi marek,
if you're able to bring up the instance with the old version and everything's working fine,
follow the upgrade procedure on dev.mysql.com.

marek · June 13, 2013, 12:58pm

Here I am again. Still struggling with the same problem. Here my Shell session with some comments; hope this is not too longish for this forum.

mysqldump with the former MySQL 5.5. I remove the link to the new MySQL and link to the old one:

cd /usr/local/
sudo rm mysql
sudo ln -s mysql-5.5.13-osx10.6-x86_64 mysql
ls -l
lrwxr-xr-x    1 root  wheel    27 Jun 13 18:54 mysql@ -> mysql-5.5.13-osx10.6-x86_64

I am now restarting the MySQL server (the old one). Now the mysqldump ...

mysqldump -u root -p /usr/local/mysql-5.5.13-osx10.6-x86_64/data/webdb ~/Desktop/dump.sql
Enter password: 
-- MySQL dump 10.13  Distrib 5.5.13, for osx10.6 (i386)
--
-- Host: localhost    Database: /usr/local/mysql-5.5.13-osx10.6-x86_64/data/webdb
-- ------------------------------------------------------
-- Server version	5.5.13

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1049: Unknown database '/usr/local/mysql-5.5.13-osx10.6-x86_64/data/webdb' when selecting the database

To be sure which mysqldump I am using:

which mysqldump
/usr/local/mysql/bin/mysqldump
[Mareks-MacBook-Pro:/usr/local] mstep% mysqldump --version
mysqldump  Ver 10.13 Distrib 5.5.13, for osx10.6 (i386)

I repeat the same with the mysqldump of the new installation (MySQL 5.6). With the same result. I am probably unaware of something obvious.

Neo · June 13, 2013, 1:06pm

Sorry for my confusion, but why are you running this command:

mysqldump -u root -p /usr/local/mysql-5.5.13-osx10.6-x86_64/data/webdb ~/Desktop/dump.sql

.. instead of:

mysqldump -u root -p webdb > ~/Desktop/dump.sql

??

marek · June 13, 2013, 2:14pm

Thanx! Neo!

Now finally I have it on my Desktop I forgot the '>'

And now I did:

mysql -u root -p < ~/Desktop/dump.sql

And it's working! Uff! Thank you radoulov and neo for your patience!

marek

Neo · June 13, 2013, 2:31pm

Great! FYI.. for the future, this command:

mysql -u root -p < ~/Desktop/dump.sql

.. should more-than-likely be this command

mysql -u root -p webdb < ~/Desktop/dump.sql

marek · June 13, 2013, 5:15pm

Yes I made an error with copy and paste. A little resum� for everybody. To upgrade from mysql-5.5 to mysql-5.6 and import the tables do following commands in your shell:


shell> mysql_admin -u root -p shutdown
password:
shell> cd /usr/local/
shell> sudo rm mysql
shell> sudo ln -s mysql-5.5.13-osx10.6-x86_64 mysql
shell> ls -l #is 'mysql' linking to the old version?
shell> sudo mysqld_safe
shell> Cntr + Z
shell> bg
[1]    sudo mysqld_safe & # Now the old MySQL server is running
mysqldump -u root -p webdb > ~/Desktop/dump.sql
shell> mysql_admin -u root -p shutdown
shell> sudo rm mysql
shell> sudo ln -s mysql-5.6.11-osx10.7-x86_64 mysql
shell> ls -l # is 'mysql' linked to the new version?
shell> sudo mysqld_safe
shell> Cntr + Z
shell> bg
[1]    sudo mysqld_safe & # now the new MySQL server is running
mysql -u root -p webdb < ~/Desktop/dump.sql
Enter password:

Hope this script receipt will help some other people out there.