[solved] database screwup - any help fixing

[rjk]

So I thought I would upgrade my MySQL minor version from 5.5.15 to 5.5.25. Stupid me, never touch something that is working.

For some stunningly idiotic reason, the "upgrade" deletes all local users and basically screws over the permissions.

Anyways, I used PHPmyAdmin's export functionality and exported the entire server, with all of its databases in one dump file, instead of doing each database individually. Is there any way to import this all at once? So far all I can find is that I need to import each database individually.

Unfortunately, PHPmyAdmin won't open since the permissions table is screwed. Any hints?

[1714189281]

1714189281

[1714189281]

1714189281

[1714189281]

1714189281

[1714189281]

1714189281

[vite]

pray to what ever entity you think will give you the answer

[Red Emerald]

Do you have access to a shell on your mysql server?

mysqldump is faster and more powerful than phpMyAdmin.

I don't think your dumps will not have any users or permissions in them though.  I don't think phpMyAdmin exports them.

[rjk]

Do you have access to a shell on your mysql server?

mysqldump is faster and more powerful than phpMyAdmin.

I don't think your dumps will not have any users or permissions in them though.  I don't think phpMyAdmin exports them.

Yes I have a shell, and when I search in the exported .sql file, I see usernames and password hashes. I'm just not sure how to import the dump using the command line. If I could log into phpmyadmin, I could import it there, but I can't login because the controluser is missing or some permission for it is messed up. I had to manually reset the root password since the upgrade blanked it.

[rjk]

Wow, my mail server wouldn't start previously because of an authentication error, but it seems to be working now. Same with Roundcube, all seems to be OK. I still can't use PHPmyAdmin though, wonder wtf is screwed up with that.

It says: "#1043 Cannot log in to the MySQL server" and also "Connection for controluser as defined in your configuration failed."

All the software is on the local machine.

[Red Emerald]

Do you have access to a shell on your mysql server?

mysqldump is faster and more powerful than phpMyAdmin.

I don't think your dumps will not have any users or permissions in them though.  I don't think phpMyAdmin exports them.

Yes I have a shell, and when I search in the exported .sql file, I see usernames and password hashes. I'm just not sure how to import the dump using the command line. If I could log into phpmyadmin, I could import it there, but I can't login because the controluser is missing or some permission for it is messed up. I had to manually reset the root password since the upgrade blanked it.

http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/

My bad.  mysqldump is just for dumping.  To import, you can just use mysql.

Code:

$ mysql -u root -p -h localhost < data.sql

You still have access to at least one user, right?

[rjk]

Do you have access to a shell on your mysql server?

mysqldump is faster and more powerful than phpMyAdmin.

I don't think your dumps will not have any users or permissions in them though.  I don't think phpMyAdmin exports them.

Yes I have a shell, and when I search in the exported .sql file, I see usernames and password hashes. I'm just not sure how to import the dump using the command line. If I could log into phpmyadmin, I could import it there, but I can't login because the controluser is missing or some permission for it is messed up. I had to manually reset the root password since the upgrade blanked it.

http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/

My bad.  mysqldump is just for dumping.  To import, you can just use mysql.

Code:

$ mysql -u root -p -h localhost < data.sql

You still have access to at least one user, right?

Ah-ha, now I see it. I need to use -h localhost. Thanks for the tip. At this point, the mail server and webmail server are working and I don't give a damn about phpmyadmin, so I will just delete its database and reinstall it from scratch.

Thanks for the help.

[Red Emerald]

No problem.  I've broken a few mysql installs myself haha.

[rjk]

Is there any way to update or upgrade minor versions without going through this heart attack inducing ordeal?

It seems to me that it is stupid to truncate the users table when only doing a minor version number update. Maybe if it was a major version that would be good, but its a pain in the ass.

[Red Emerald]

Is there any way to update or upgrade minor versions without going through this heart attack inducing ordeal?

It seems to me that it is stupid to truncate the users table when only doing a minor version number update. Maybe if it was a major version that would be good, but its a pain in the ass.

I usually dump the database, import it into a dev system and then update the dev system.

When it fails, I roll back the dev system (virtual machines are awesome) and try something different.  If it works, I do the same process on production.

This takes longer and may require more hardware, but at least I don't have to fear losing any production data.

[rjk]

I think what threw me into a panic was the fact that phpmyadmin would not open. After banging away at it for a while, I finally figured out the problem: after an update of phpmyadmin, it had enabled SSL connections to the MysSQL server, and was failing because php was connecting with a local socket. Disabling SSL fixed it, but I might need to look at re-enabling it on the MySQL side anyways.

The tool that helped me get back up and running was MySQL Workbench - it's a totally awesome administration tool. Obviously taking a full backup is preferable, but in this case I was able to restore just the mysql.user table and then my mail server and webmail script started working.

This system is already virtualized, so maybe I should clone it and use the cloned environment for testing. That seems like a good idea.