MySql migration problem - urgent

Hello,

So far I used MySql 5.1 for Windows and if I wanted to migrate the whole database to another computer I did this:
- installed a MySql 5.1 on the new computer (yes I know I can install  from command line too)
- Stopped the server
- copied the old ibdata1 file and all the database folders from the old computer the the new one (data folder)
- Restarted the server and everything was fine

Today I installed 5.5 and I wanted to migrate the old 5.1 data and I wanted to repeat the whole process and to my greatest surprise there wasn't ibdata1 nor the log files in the data folder.

What can I do then? I have about 150 databases (it's a developer machine) so I don't want to export the Sql-s one by one.

Thank you
starhuAsked:
Who is Participating?
 
Chakravarthi AyyalaConnect With a Mentor Database AdministratorCommented:
So do you "datadir" in that file?
0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
If the first time migrated MySQL on 5.1 was running, did you check the location of data folder from my.cnf?
0
 
starhuAuthor Commented:
I haven't got my.cnf, I have my.ini.

Before installing MySql 5.5 there wasn't MySql on that computer at all (the 5.1 was on another computer)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
starhuAuthor Commented:
It was set to:
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"
however at intall I set the installation directory (C:\MySql5).

I corrected the path, restarted the service.
Now I can see all the databases and tables but the stored procedures and views are not there.

How can I migrate them too, by copying? Where are they stored?
0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
What is the output of the following query?

SELECT ROUTINE_TYPE, ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_SCHEMA=<'dbname'>;
0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
To add, replace the dbname with the dbname you are interested in.
0
 
starhuAuthor Commented:
the query result is empty for all databases.
Can I insert (do I have rights) into NFORMATION_SCHEMA.ROUTINES?
0
 
starhuAuthor Commented:
I exported all the routines from the old database and I wanted to insert into the new one but I got
"Access denied for user 'root'@'localhost' to database 'information_schema'"
0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
information_schema doesn't really exist as a physical database. Rather it is a collection of views of data.
Hence, i doubt inserting data into information_Schema.

Ref: http://forums.devshed.com/mysql-help-4/error-permision-denied-to-user-root-400633.html
0
 
starhuAuthor Commented:
It didn't solve my problem because the stored procedures were not in the new database - I needed to downgrade to 5.0 so as to import all the data and stored procedures
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.