MySql Backing up working database w/o password
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.
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.
ASKER
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?
ASKER
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.
ASKER
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?
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?
>>> 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.
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.
Just out of morbid curiosity, did you try
mysql -u root -p
and then leave the password blank?
mysql -u root -p
and then leave the password blank?
ASKER
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.
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.
In that case, have you tried this procedure?
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-windows
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-windows
ASKER
Hey DrDamnit,
Tried that, results in the Mysql service not starting.
Tried that, results in the Mysql service not starting.
>>> 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.
When you have copied the files and directories, make sure they are writable by the mysql service user.
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.
ASKER
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)
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)
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.
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.
>>> 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.
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.
ASKER
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.
Again, thank you all for help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
Try this and report back if you suceed.