Restore MySQL backup


 
Thread Tools Search this Thread
Top Forums Web Development Restore MySQL backup
# 1  
Old 06-10-2013
Restore MySQL backup

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:

Code:
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:

Code:
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

Last edited by radoulov; 06-10-2013 at 07:26 AM..
# 2  
Old 06-10-2013
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.
This User Gave Thanks to radoulov For This Post:
# 3  
Old 06-10-2013
Thank you radoulov!


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

Code:
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:

Code:
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

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

Does this help more as information?
# 4  
Old 06-10-2013
It seems that you're able to log into mysql as root.
Code:
mysql -u root -h localhost -p

You could find the error log name and path with:
Code:
show global variables like 'log_error';

# 5  
Old 06-11-2013
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

Code:
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:

Code:
-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

Last edited by Scott; 06-11-2013 at 11:02 AM.. Reason: Code tags
# 6  
Old 06-11-2013
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.
# 7  
Old 06-11-2013
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

Code:
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Red Hat

Backup / Restore

Hi, I need to back up a RH file system (96G). The files are oracle .dbf format some of which are 5G in size. I know that tar has got a size restriction of 2G so I cannot use this. Can anyone recommend an alternative way of backuping up this FS? I have been looking at dump but this... (6 Replies)
Discussion started by: Duffs22
6 Replies

2. AIX

Backup and restore

Hi experts, i got a question. i have a production server with two Volume Group(VG) which are rootvg and datavg. Both of these VGs are 256 PP SIZE. On Disaster Recovery Server (DR server) contains two empty hardisks for restoring rootvg and datavg from production server. This two hardisks are... (7 Replies)
Discussion started by: polar
7 Replies

3. Shell Programming and Scripting

Backup and restore in unix

Hello everybody, i am trying to make a script in UNIX to backup some compressed files to a tape drive. The thing is that i cannot use cpio command because some of these files are greater than 2GB. so i think the only solution left is backup command. to restore the files i should use the... (6 Replies)
Discussion started by: omonoiatis9
6 Replies

4. Shell Programming and Scripting

Backup/restore scripts

hi people i am in need of some assistance here hoping to star a linux course in january to wanted to get some experiance before starting so got a hold of some old assessments from a mate at college so just working through them in my spare time for the past 8 weeks or so and this is the final ? that... (2 Replies)
Discussion started by: boabbyrab
2 Replies

5. AIX

How to restore from mksysb backup

system is not booting ... i want to restore from mksysb backup. (2 Replies)
Discussion started by: AIXlearner
2 Replies

6. HP-UX

Backup Tape Restore?

I am trying to do a restore on a backup tape (DDS2) and am having a little trouble. For one, I dont know how the tape was made, whether is was tar, cpio, dump..etc. Anyone know how to restore a tape without knowing the format of the backup? (5 Replies)
Discussion started by: bake255
5 Replies

7. HP-UX

F-Backup restore

Hello! i have a blank harddrive and a complete tape backup of the workstation. the backup is made with F-Backup. Now my question is: how can i restore my workstation? thanks for every idea! paul tittel hup-si (3 Replies)
Discussion started by: paultittel
3 Replies

8. AIX

Backup and restore

I have several H80 machines, all with AIX 4.3.3. On these machines I have mksysb running for rootvg backups and savevg for non-rootvg backups. I'm trying to get a list of files on the tapes, but I can't seem to do it with tar for the mksysb images. I keep getting the directory checksum errors?... (3 Replies)
Discussion started by: uXion
3 Replies

9. Solaris

Backup / restore

Hi.... everyone could help me to understand how to do a backup of my servers .. operating systems is sun solaris 8 . I have some question about .... 1) Is better backup phisical disk or partition ??? i sow the command is ufsdump 0cfu /expbck/bcksunver/c0t0d0s5 dev/dsk/c0t0d0s5 to... (4 Replies)
Discussion started by: tt155
4 Replies

10. HP-UX

HP-UX Filesystem backup/restore?

:confused: Hi Guys, I'm not new to UNIX but I am new to HP-UX. I have a proven backup and restore procedue using cpio on Solaris, however, the filesystem structure appears to be different on HP. Can anybody help me with the following questions? 1) What is the best method for performing a... (7 Replies)
Discussion started by: mybeat
7 Replies
Login or Register to Ask a Question