Solved

MySql Backing up working database w/o password

Posted on 2011-03-22
20
1,283 Views
Last Modified: 2012-06-27
How do I backup MySQL and restore it on another machine with the situation detailed below?

Doctor's office brings me a dead server. They were running it with:

MySQL version 12.22 Distrib 4.0.17 (i32)
WinMySQLadmin1.4
Windows Server 2003 R2 SP1
CrystalPM GUI version #2.1.6 library 2.1.1
Dell PowerEdge 830


I took their raid drives and controller out of their dead PE830 and stuck it in my Dell SC1420. It boots and all seems well.

What I need to do is backup the database and get them running on some different hardware. I figure a simple mysqldump and restore ought to be the route to go. This is where I run into problems. The password they think is their database password does not seem to work. But maybe I am just doing something wrong.

I have seen several explanations on how to change the password but I am not sure if I should because of the following:

Crystal PM seems to be able to pull up data. If I do a search for Smith I get data (phone numbers address etc..) So I think the database is running. However WinMySQLadmin says that there are no databases loaded. Yet, it shows the my.ini file with the username and password that the office had written down as their login info.

So I guess more specifically I am asking, do I want to change the password, or are there other steps I should take first that are less drastic and risky?

Concerning me is that the password reset instructions I have seen all start with the mysql command and their database is running mysqld-max-nt.

I do have Administrator access to the server, am using it locally only (for now).

Have tried mysqldump commands repeatedly and I get different errors based on my format. Mostly I get:

mysql -u Administrator

results in

 error 1045 Access denied for user Adminstrator@localhost

Have tried this with their username and password and get essentially the same error.

Please help, and thank you taking the time to get this far.
0
Comment
Question by:www_rusty_bz
  • 9
  • 4
  • 3
  • +2
20 Comments
 
LVL 18

Expert Comment

by:Andrej Pirman
Comment Utility
If database is of type MYISAM, which is most likely, one of almost 100% method of copying data is to simply copy old C:\MySQL\DATA folder to new installation. All credentials, data and users are inside this folder. And you do not need to know mysql password for this operation.

Try this and report back if you suceed.
0
 

Author Comment

by:www_rusty_bz
Comment Utility
Thanks Labsy, in order to get some movement on this, I will install MySql tonight and try this on a different machine. Any suggestions on how best I can confirm the database moved succesfully?
0
 

Author Comment

by:www_rusty_bz
Comment Utility
Installed the 5.5 MySQL, it works with its default installation. When I change my.ini to point at the backed up MySql\data directory the service will not start.
0
 

Author Comment

by:www_rusty_bz
Comment Utility
On my laptop:

Using the copy of the Mysql\data directory (on a different machine), I have tried two different password reset instructions for MySql databases. They both fail to load the service. When I point the Mysql at its default Mysql\data directory it starts. This seems to mean that the service will not start unless I know the password.

On the server:
 I still have a working database sitting here, does anyone know of a way to use the fact that it is running and responding to calls for data from Crystal PM as an inroad to figuring out this password or circumventing it? When I say the database is running I mean that the Mysql service reports that it is running in the services control panel app. Can Crystal PM be told to do a report that contains the entire database? Could that then piped into a "new" database?
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
>>> When I change my.ini to point at the backed up MySql\data directory the service will not start.

Don't move the mountain..bring the mountain to Mohammed.  Set up MySQL like you want it to be, then dump your existing database directories into the data directory.  Make sure both source and destination services are off when you do.  

The fact that it would not restart tends to imply you have some InnoDB tables in there.  Make sure you copy the ib* files as well.  You should be able to start the service with --skip-grant-tables and get full administrative access.
0
 
LVL 32

Expert Comment

by:DrDamnit
Comment Utility
Just out of morbid curiosity, did you try

mysql -u root -p

and then leave the password blank?
0
 

Author Comment

by:www_rusty_bz
Comment Utility
Thanks for the input routinet and DrDamnit,

I just finished making another image of the drive. I'll try your suggestion on the new image, routinet. DrDamnit, yes, I tried that. That seems the easiest way to try passwords. Thanks though. I'll be back.
0
 
LVL 32

Expert Comment

by:DrDamnit
Comment Utility
0
 

Author Comment

by:www_rusty_bz
Comment Utility
Hey DrDamnit,

Tried that, results in the Mysql service not starting.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
>>> I'll try your suggestion on the new image, routinet

When you have copied the files and directories, make sure they are writable by the mysql service user.
0
 
LVL 32

Expert Comment

by:DrDamnit
Comment Utility
Even if the service fails to start, you can try to start it from the command line, and you will hopefully get some errors we can work with.
0
 

Author Comment

by:www_rusty_bz
Comment Utility
Okay, I copied the data directory on top of the one that was installed by default (that was working btw). The MySql service is listed as automatic but in control panel services show as stopped.

The error message I get when I type mysql at the dos prompt is:

Error 2003 (HY000): Can't connect to MySQL server on localhost (10061)
0
 

Expert Comment

by:rwwff
Comment Utility
Couple thoughts for you.  Mysqldump is likely the right tool, but your original server is running a 4.x release of mysql, and the system you are restoring to is a new install ie, 5.5.  There's nothing to terrible about that as far as your doctor's data, but there is something lurking, and that is the restore of a complete mysqldump will initialize the user access logins and privileges along with the data.   You need to NOT do that.   Instead of just blindly firing off msyqldump and then sucking the result back into the new server, snag just the databases (or just specific tables) that you want.

so do "mysqldump drinfo_database >drinfo.sql"

and then load that up into your fresh, new install.  Do the same for each database(aka catalog) other than the one named "mysql".

It may seem like the long way around, but in the end, pulling user data into an mysql instance that you have full command of, is much easier than trying to get full administrative command of an mysql instance thats messed up and you may not have passwords for.   Once you have the data in an mysql instance that you have control of, its relatively easy to move the data around.

0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
>>> The MySql service is listed as automatic but in control panel services show as stopped.

What happens when you try to start the service manually?  Do you see any events in the event viewer?  What happens when you attempt to start it from command line?  (not "mysql"...likely "mysqld")

rwwff's comments are on the ball, and I probably should have explained that more thoroughly.  When I say copying the data files, I do mean the data files, not the mysql database files.  If you copy only  your databases, your new MySQL installation will still have the default security until you change it.  Regardless, even if you import the old security, you can always bypass it with --skip-grant-tables and reset the admin password.

Of course, that all relies on the service actually being able to start.  You should focus on that first...worry about getting the data there once you have a service that can use it.
0
 

Author Comment

by:www_rusty_bz
Comment Utility
Thank you for all the attention to this question. I never was able to perform a mysqldump because I do not know the password. Copying the databases into a working mysql installation did not allow me to get around this problem, despite repeated attempts. I guess the lesson is do not lose your mysql password because sometimes there is no way to get around this problem. At least no way that I could find in a week of trying.

Again, thank you all for help.
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
Comment Utility
>>> I never was able to perform a mysqldump because I do not know the password

Did you try using --skip-grant-tables to reset the password?
0
 

Author Comment

by:www_rusty_bz
Comment Utility
Hey routinet,

Had to move on, thanks for all the help. Imaging the drive gives them a backup, and they are making a new database going forward. I know I tried the --skip-grant-tables but I'm not sure if I did all of the rest of the copying properly.
0
 

Author Closing Comment

by:www_rusty_bz
Comment Utility
Thanks again for all the help. I'm confident the suggestions posted will work in similar situations. I just think there must be some factor I don't know to share, that caused my situation to end up less than fully successful. Oh well, can't fix everything.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now