starhu
asked on
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
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
Chakravarthi Ayyala
If the first time migrated MySQL on 5.1 was running, did you check the location of data folder from my.cnf?
ASKER
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)
Before installing MySql 5.5 there wasn't MySql on that computer at all (the 5.1 was on another computer)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
What is the output of the following query?
SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINE
WHERE ROUTINE_SCHEMA=<'dbname'>;
SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINE
WHERE ROUTINE_SCHEMA=<'dbname'>;
To add, replace the dbname with the dbname you are interested in.
ASKER
the query result is empty for all databases.
Can I insert (do I have rights) into NFORMATION_SCHEMA.ROUTINES
Can I insert (do I have rights) into NFORMATION_SCHEMA.ROUTINES
ASKER
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'"
"Access denied for user 'root'@'localhost' to database 'information_schema'"
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
Hence, i doubt inserting data into information_Schema.
Ref: http://forums.devshed.com/mysql-help-4/error-permision-denied-to-user-root-400633.html
ASKER
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