UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Reinstalled Os And Mysql. How To Use Existing Data?, 5    
 
   
MCL1981
post Feb 16 2016, 01:17 PM
Post#1



Posts: 220
Joined: 1-November 11



I came in this morning to my sever blinking no boot device. Yay. So I had to reinstall the OS, then reinstalled mySQL and workbench. All the data is on a separate drive array, unhurt. How on earth do I get my new installation of mySQL to talk to the existing data? The OS is Win7. The old DB was mySQL 5.1. I installed the latest and greatest 5.8, hopefully that will be ok?

Also, I have backup dump files. But it seems the dump files don't include the views?!
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 08:47 AM
Post#2


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


At this stage of the game, it would be easiest to restore from the dump files.

Are you sure the views aren't there? mysqldump dumps views as tables (often at the end of the whole dump).

Also the user will need CREATE VIEW privilege - see here on StackOverflow

If they are truly missing, then you will need to restore from the datadir, but this is more tricky.

Investigate the dumps first.

d
Go to the top of the page
 
MCL1981
post Feb 17 2016, 10:33 AM
Post#3



Posts: 220
Joined: 1-November 11



Switching back to the matching old version this morning. I was trying to restore the dump file as root through Workbench. I'd think root has full privileges including SHOW VIEW and CREATE VIEW?
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 10:35 AM
Post#4


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Yes, root should have those privileges.

How did you get on with the restore? Did the views reappear?
Go to the top of the page
 
MCL1981
post Feb 17 2016, 10:48 AM
Post#5



Posts: 220
Joined: 1-November 11



I've reinstalled mySQL 5.0, which is what I had before. I imported the dump file. Again, it does the tables and data perfectly. I creates the necessary temporary tables that represent the views. But it does not create any views.
.
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 11:03 AM
Post#6


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Have you created a user who is the View Definer yet? See here on dba.stackexchange.com (perhaps a red herring - root should be able to cope).

If that doesn't help, then it could be Workbench with the issue.

Drop the databases and try a restore from the command line.

I haven't used Windows for years, but it should be something like:
CODE
C:\Program Files\MySQL\bin\mysql.exe -uroot -p < "C:\path\to\backup.sql"


hth,

d
Go to the top of the page
 
MCL1981
post Feb 17 2016, 12:05 PM
Post#7



Posts: 220
Joined: 1-November 11



My root account has full privileges in % schemas. However, I noticed something here. When I import the dump file, it seems the dump is overwriting the users, including root. The resulting "new" users, including root, don't have privileges to any schemas. Perhaps that's why it can't create the views?
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 12:10 PM
Post#8


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Is this a dump containing all schemas together?

I guess you are restoring the mysql database as well.

Go through the dump file until you find:
CODE
DROP DATABASE IF EXISTS mysql;
CREATE DATABASE mysql;

Either comment or cut all the lines until the next CREATE DATABASE .. statement.

Recreate your root user privileges, and drop all schemas and try again.

hth,

d
Go to the top of the page
 
MCL1981
post Feb 17 2016, 12:36 PM
Post#9



Posts: 220
Joined: 1-November 11



Yep, that [censored] was in there. I just deleted it.

However, now I think I'm seeing a major problem. Going into the dump file in Notepad, it seems the dump file is actually incomplete. The dump file ends in hte middle of creating those temporary tables for the views. Is there a such thing as dump file being too long?? string for creating the dump file used single transaction, all databases.

From the looks of it, I might be screwed with the dump files. It runs this dump export twice a day, and it appears to have been making incomplete dumps for 2 years. Shoot me.
Go to the top of the page
 
MCL1981
post Feb 17 2016, 12:48 PM
Post#10



Posts: 220
Joined: 1-November 11



On the assumption I'm out of luck with the dump files, it looks like I need a plan B to use my data directory files, which are all still intact.
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 12:49 PM
Post#11


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


How sensitive is your data?

Can I have a look at one of your dump files?

If you want/are able, pm me and I'll give you an email address to send to (or zip and post here if there's no issue).

d
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 12:58 PM
Post#12


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Plan B

Make sure you're working on a copy of everything in the data directory.

hth,

d
Go to the top of the page
 
MCL1981
post Feb 17 2016, 01:27 PM
Post#13



Posts: 220
Joined: 1-November 11



Sadly I can't share the dump file without getting fired and possibly arrested... When I open the dump file in notepad, it is cut off exactly where it appears to stop during the import. It's in the middle of creating the temporary tables for the views. I can only assume the failure is in the export, and I never bothered to confirm it was doing it properly.

I'm going to start over and try Plan B now, which appears oddly simple. No way it could possibly be that easy.
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 02:08 PM
Post#14


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Good luck - report back - I've got to go out now.

When it goes right, it's great.

If something doesn't quite work out ...

Do you have your copy of my.ini from before the crash? Try and make sure the server re-starts using the same configuration as before.

Don't forget to stop the server before moving the data!!!!

thumbup.gif

d
Go to the top of the page
 
MCL1981
post Feb 17 2016, 03:32 PM
Post#15



Posts: 220
Joined: 1-November 11



Every time I've tried to do this, all [censored] breaks loose. The server won't even start. I have an old copy of the my.ini, but I think it's still the same. Didn't seem to help. In fact, I can't get 5.0 to work at all anymore. I even uninstalled everything, reinstalled it, and it still won't even start the service on initial install. I think something must be hosed in the registry or something, but I couldn't find it. I've determined that 5.0 is a lost cause, to include restoring the data directories.

So I'm on to Plan C now. I've uninstalled 5.0 (for the 6th time today..) for good. I've installed the latest 5.7 and latest Workbench. Yay for new toys. I've restored the tables + data via the dump files, which thankfully worked just fine. The only thing I need to cobble together is views. I have an ERR model from about 6 months ago. That will get my views about 60-70% restored. I will have to cobble together my view changes from the last 6 months the old fashioned way... by hand.

A counterpart may have all my views saved in SSMA from last month, which might be my savior. They're checking now.
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 04:19 PM
Post#16


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Did you properly specify the data dir in the conf file?

I'm keeping my fingers crossed that your colleague has the view definitions for you

thumbup.gif

d
Go to the top of the page
 
MCL1981
post Feb 17 2016, 04:45 PM
Post#17



Posts: 220
Joined: 1-November 11



Yes, I changed the data directory. I moved the files into existing directories. I tried every which way.

I was successful in restoring the views from the ERR file that is 6 months old. That gets me much further than scratch. Hopefully I'll hear from the SQL guys on their version tomorrow. Many lessons learned...

And to top it off, it looks the firewall settings changed over the weekend too. My application can't connect to the server through the port we were using before. FML.
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 05:38 PM
Post#18


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Did you check the Bind Address in the my.ini?

You need to give it the ip address of the server machine.

hth,

d
Go to the top of the page
 
MCL1981
post Feb 17 2016, 06:48 PM
Post#19



Posts: 220
Joined: 1-November 11



I'm doing all this locally on the server itself to begin with. Also, the address hasn't changed.
Go to the top of the page
 
cheekybuddha
post Feb 17 2016, 07:53 PM
Post#20


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


OK, as long as you have the IP address is set as the bind address in my.ini, and SKIP-NETWORKING is commented out.

d
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 10:16 AM