Bitcoin Forum
December 03, 2016, 08:02:36 PM *
News: Latest stable version of Bitcoin Core: 0.13.1  [Torrent].
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: [solved] database screwup - any help fixing  (Read 1256 times)
rjk
Sr. Member
****
Offline Offline

Activity: 420


1ngldh


View Profile
June 15, 2012, 10:21:19 PM
 #1

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?

Mining Rig Extraordinaire - the Trenton BPX6806 18-slot PCIe backplane [PICS] Dead project is dead, all hail the coming of the mighty ASIC!
1480795356
Hero Member
*
Offline Offline

Posts: 1480795356

View Profile Personal Message (Offline)

Ignore
1480795356
Reply with quote  #2

1480795356
Report to moderator
1480795356
Hero Member
*
Offline Offline

Posts: 1480795356

View Profile Personal Message (Offline)

Ignore
1480795356
Reply with quote  #2

1480795356
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
vite
Legendary
*
Offline Offline

Activity: 996


La Falta de Oficio Deteriora la Mente


View Profile WWW
June 15, 2012, 10:23:40 PM
 #2

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

Red Emerald
Hero Member
*****
Offline Offline

Activity: 742



View Profile WWW
June 15, 2012, 10:39:39 PM
 #3

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
Sr. Member
****
Offline Offline

Activity: 420


1ngldh


View Profile
June 15, 2012, 10:42:13 PM
 #4

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.

Mining Rig Extraordinaire - the Trenton BPX6806 18-slot PCIe backplane [PICS] Dead project is dead, all hail the coming of the mighty ASIC!
rjk
Sr. Member
****
Offline Offline

Activity: 420


1ngldh


View Profile
June 15, 2012, 10:48:39 PM
 #5

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.

Mining Rig Extraordinaire - the Trenton BPX6806 18-slot PCIe backplane [PICS] Dead project is dead, all hail the coming of the mighty ASIC!
Red Emerald
Hero Member
*****
Offline Offline

Activity: 742



View Profile WWW
June 15, 2012, 10:50:26 PM
 #6

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
Sr. Member
****
Offline Offline

Activity: 420


1ngldh


View Profile
June 15, 2012, 10:53:50 PM
 #7

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.

Mining Rig Extraordinaire - the Trenton BPX6806 18-slot PCIe backplane [PICS] Dead project is dead, all hail the coming of the mighty ASIC!
Red Emerald
Hero Member
*****
Offline Offline

Activity: 742



View Profile WWW
June 15, 2012, 10:58:43 PM
 #8

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

rjk
Sr. Member
****
Offline Offline

Activity: 420


1ngldh


View Profile
June 15, 2012, 11:05:52 PM
 #9

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

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.

Mining Rig Extraordinaire - the Trenton BPX6806 18-slot PCIe backplane [PICS] Dead project is dead, all hail the coming of the mighty ASIC!
Red Emerald
Hero Member
*****
Offline Offline

Activity: 742



View Profile WWW
June 17, 2012, 05:52:40 AM
 #10

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

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
Sr. Member
****
Offline Offline

Activity: 420


1ngldh


View Profile
June 17, 2012, 11:31:36 AM
 #11

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.

Mining Rig Extraordinaire - the Trenton BPX6806 18-slot PCIe backplane [PICS] Dead project is dead, all hail the coming of the mighty ASIC!
Pages: [1]
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!