Hi. On Thu 2003-01-09 at 11:21:49 -0800, [EMAIL PROTECTED] wrote: > I have a small problem where a power outage has corrupted the user database > for mysql, now no users can authenticate/manipulate any databases hosted by > the MySQL Engine... This is rather annoying since I am no longer able to > administer the databases, and I appear to be unable to modify, or create > users all together. > The engine is running, and the availability of data is still there, but in a > read only manner. I can pull data out, but unless I can authenticate, I cant > enter any in! How can I fix this? Almost all of the admin tools I have found > will not work without logging in. > If there is no other solution, is it possible to import the existing > database I have (used for inventory purposes) into a fresh mysql install? I > cant export anything, and the database files are basically flat files at > this point... A good web resource would also be appreciated! > any help would be appreciated.
There are several possibilities, some of them depend on how recent
your version of MySQL is.
- You can try repairing the tables from the shell with myisamchk (be
sure to stop mysqld beforehand), like this:
myisamchk -r /var/lib/mysql/mysql/*.MYI
If there are no files ending in .MYI, but only .ISM, use the
following instead:
isamchk -r /var/lib/mysql/mysql/*.ISM
Usually you would use REPAIR TABLES from the mysql prompt (or some
graphic utility), but since you have problems authenticating I won't
go into depth about that. The (my-)isamchk solution only requires
that you have the file privileges (i.e. that you can become root).
Make sure that the files belong to the same user as they did
beforehand, when you are done.
- Alternatively you can start MySQL so that ignores the privileges
tables at all. *Note that running it this will is a security risk*,
depending on your configuration and you should keep the time running
MySQL this way short. Using --skip-networking reduces this risk,
because only connection from the localhost are allowed then.
You have to start MySQL with --skip-grant-tables (or short: -Sg).
You can start it using safe_mysql:
/usr/bin/safe_mysql --skip-grant-tables --skip-networking
If that makes problems, you can alternatively edit the MySQL
startup-script /etc/init.d/mysql (don't forget to change it back
afterwards).
When you connect to MySQL after starting it this way, you have all
privileges the MySQL root usually has. You can try the mentioned
REPAIR TABLES, if you want (it will do the same as myisamchk) or you
can make a dump of your data - something like
mysqldump --opt your_database > somefile
should do. Then remove all files in /var/lib/mysql and use
/usr/bin/mysql_install_db to setup the privilege tables from
scratch. Then play in your dump with something like
mysqladmin create your_database
mysql your_databaes < somefile
Well, I'll stop for now. Hope that gets you along.
Bye,
Benjamin.
msg114910/pgp00000.pgp
Description: PGP signature
