Overclock.net › Forums › Software, Programming and Coding › Operating Systems › Linux, Unix › How do I restore mysql tables?
New Posts  All Forums:Forum Nav:

How do I restore mysql tables?

post #1 of 9
Thread Starter 
Ok i accidently my mysql tables and need a way to install a fresh mysql database onto a centos box.

Ive tried removing the mysql-server and mysql but when i reinstall it and run it the databases remain as they were before removing. (exactly the same).

How do i go about making sure i can do a fresh install of mysql including a fresh install of all the databases (ie, information_schema, mysql and test) otherwise i'm just going to scrap this container and make a new one.
Zardoz
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 950 @ 4.21 (HT/On) #3043A793 Rampage III GENE NVIDIA Geforce GTX 560 Ti MSI TFII @ Stock G.SKILL Ripjaws Series 8GB DDR3 1600 
Hard DriveOptical DriveOSMonitor
Intel X25-M SSD 80GB [x2] Raid 0 + Seagate 1TB Pioneer BR-Drive Win7 Ultimate 64bit Acer B273hu 
PowerCase
GE-M800A-d1 Gigabyte LIAN LI PC-A04B mATX 
  hide details  
Reply
Zardoz
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 950 @ 4.21 (HT/On) #3043A793 Rampage III GENE NVIDIA Geforce GTX 560 Ti MSI TFII @ Stock G.SKILL Ripjaws Series 8GB DDR3 1600 
Hard DriveOptical DriveOSMonitor
Intel X25-M SSD 80GB [x2] Raid 0 + Seagate 1TB Pioneer BR-Drive Win7 Ultimate 64bit Acer B273hu 
PowerCase
GE-M800A-d1 Gigabyte LIAN LI PC-A04B mATX 
  hide details  
Reply
post #2 of 9
drop database [database name];

Did you get a dump of the sql file before you accidentally the whole thing.
Edited by andrewmchugh - 8/8/11 at 2:38am
^3
(11 items)
 
   
CPUMotherboardGraphicsRAM
Intel Core i7 5930K  EVGA x99 Micro EVGA GTX 980 SC ACX 16GB Corsair DDR4 Vengeance LPX Black PC4-21300 
Hard DriveCoolingOSMonitor
500GB Samsung 840 EVO H105 Win 10 ASUS ROG PG279 
KeyboardPowerCase
Ducky Shine 1 MX-Red Corsair AX 860i  Fractal Design Node 804 Black 
CPUGraphicsRAMHard Drive
Core i7 @ 2.6  GT650 16GB of 1600 MHz DDR3L SDRAM 256GB SSD 
OSMonitorKeyboard
Mac OSX Lion Retina Display US layout 
  hide details  
Reply
^3
(11 items)
 
   
CPUMotherboardGraphicsRAM
Intel Core i7 5930K  EVGA x99 Micro EVGA GTX 980 SC ACX 16GB Corsair DDR4 Vengeance LPX Black PC4-21300 
Hard DriveCoolingOSMonitor
500GB Samsung 840 EVO H105 Win 10 ASUS ROG PG279 
KeyboardPowerCase
Ducky Shine 1 MX-Red Corsair AX 860i  Fractal Design Node 804 Black 
CPUGraphicsRAMHard Drive
Core i7 @ 2.6  GT650 16GB of 1600 MHz DDR3L SDRAM 256GB SSD 
OSMonitorKeyboard
Mac OSX Lion Retina Display US layout 
  hide details  
Reply
post #3 of 9
You do realize if you drop the database you won't have any posts/threads/site related data and will have a brand new message board/site/whatever? This will remove ALL hacks/tweaks you have made to templates and the like. Just sayin, you might have fuxxed up good. Otherwise I don't see the problem. Drop the database, make a new one, re-run the install.php (if you weren't smart and kept it, or whatever you are running sql for). BAM site back up.

[edit] This is why we invented phpmyadmin!
http://www.phpmyadmin.net/home_page/index.php
Edited by mushroomboy - 8/8/11 at 3:27am
Current Rig
(14 items)
 
  
CPUMotherboardGraphicsRAM
FX-8350 4.6GHz@1.44v GA-990FXA-UD3 R4.0 HD 7950 (1100/1450) 8G Muskin DDR3 1866@8CLS 
Hard DriveOptical DriveOSMonitor
1TB WD LiteOn DVD-RW DL Linux/Windows 19" Phillips TV 1080p 
PowerCaseMouseMouse Pad
OCZ 600W Generic Junk Logitech MX400 Generic Junk 
Audio
SBL 5.1 
  hide details  
Reply
Current Rig
(14 items)
 
  
CPUMotherboardGraphicsRAM
FX-8350 4.6GHz@1.44v GA-990FXA-UD3 R4.0 HD 7950 (1100/1450) 8G Muskin DDR3 1866@8CLS 
Hard DriveOptical DriveOSMonitor
1TB WD LiteOn DVD-RW DL Linux/Windows 19" Phillips TV 1080p 
PowerCaseMouseMouse Pad
OCZ 600W Generic Junk Logitech MX400 Generic Junk 
Audio
SBL 5.1 
  hide details  
Reply
post #4 of 9
Quote:
Originally Posted by anthony92 View Post
Ok i accidently my mysql tables
that made me laugh a little bit
Kinda meh now...
(13 items)
 
  
CPUMotherboardGraphicsRAM
PhII 940 BE asus M4N82 Deluxe 2x 8800gts(g92) 2x 1g ocz ddr2 1066 
Hard DriveOptical DriveOSMonitor
/dev/sd[abc] /dev/sr0 WinXP/Linux 22in acer 
PowerCaseMouseMouse Pad
950w rosewill timebomb Antec 900 (2small) Voodoo Deathadder (RED!) X-Trac Pads Ripper XL 
  hide details  
Reply
Kinda meh now...
(13 items)
 
  
CPUMotherboardGraphicsRAM
PhII 940 BE asus M4N82 Deluxe 2x 8800gts(g92) 2x 1g ocz ddr2 1066 
Hard DriveOptical DriveOSMonitor
/dev/sd[abc] /dev/sr0 WinXP/Linux 22in acer 
PowerCaseMouseMouse Pad
950w rosewill timebomb Antec 900 (2small) Voodoo Deathadder (RED!) X-Trac Pads Ripper XL 
  hide details  
Reply
post #5 of 9
Thread Starter 
Lol no data was lost. It was just the 'mysql' that was deleted, that installs automatically. It keeps the user permissions for accessing other databases.

I can't use mysql, i can't add users i can't do anything and i'm not sure on want to do to repair it.

I tried yum remove mysql mysql-server, then reinstalled but the old database was still there.
Zardoz
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 950 @ 4.21 (HT/On) #3043A793 Rampage III GENE NVIDIA Geforce GTX 560 Ti MSI TFII @ Stock G.SKILL Ripjaws Series 8GB DDR3 1600 
Hard DriveOptical DriveOSMonitor
Intel X25-M SSD 80GB [x2] Raid 0 + Seagate 1TB Pioneer BR-Drive Win7 Ultimate 64bit Acer B273hu 
PowerCase
GE-M800A-d1 Gigabyte LIAN LI PC-A04B mATX 
  hide details  
Reply
Zardoz
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 950 @ 4.21 (HT/On) #3043A793 Rampage III GENE NVIDIA Geforce GTX 560 Ti MSI TFII @ Stock G.SKILL Ripjaws Series 8GB DDR3 1600 
Hard DriveOptical DriveOSMonitor
Intel X25-M SSD 80GB [x2] Raid 0 + Seagate 1TB Pioneer BR-Drive Win7 Ultimate 64bit Acer B273hu 
PowerCase
GE-M800A-d1 Gigabyte LIAN LI PC-A04B mATX 
  hide details  
Reply
post #6 of 9
I don't know about yum, but with apt get you can use the switch "-purge" to remove everything associated with the application. I imagine that should clear out the database for when you re-install. See if yum has an equivalent option.
Daily
(16 items)
 
  
CPUMotherboardGraphicsRAM
i7 2600K @ 4.8GHz, 1.4V Maximus IV Extreme GTX 560 DCII TOP 4x4GB Kingston HyperX 1866MHz CL11 
Hard DriveHard DriveCoolingCooling
Kingston HyperX 3K 120GB Samsung 640GB Thermochill PA120.2 Jingway DB-1 Pump 
CoolingCoolingOSMonitor
2x Kaze Jyuni 1900RPM EK Supreme HF Copper plexi Arch Linux x64 Acer X223HQ 1920x1080 
KeyboardPowerCaseMouse
Microsoft Sidewinder X4 Corsair HX750 Modded Corsair Carbide 500R Logitech M500 
  hide details  
Reply
Daily
(16 items)
 
  
CPUMotherboardGraphicsRAM
i7 2600K @ 4.8GHz, 1.4V Maximus IV Extreme GTX 560 DCII TOP 4x4GB Kingston HyperX 1866MHz CL11 
Hard DriveHard DriveCoolingCooling
Kingston HyperX 3K 120GB Samsung 640GB Thermochill PA120.2 Jingway DB-1 Pump 
CoolingCoolingOSMonitor
2x Kaze Jyuni 1900RPM EK Supreme HF Copper plexi Arch Linux x64 Acer X223HQ 1920x1080 
KeyboardPowerCaseMouse
Microsoft Sidewinder X4 Corsair HX750 Modded Corsair Carbide 500R Logitech M500 
  hide details  
Reply
post #7 of 9
What do you mean the "mysql" was deleted. Either you have an active running DB or you don't. I don't get the problem. The DB has nothing to do with the setup/operation of mysql. You could purge mysql and then re-install it, that would give you defaults on your mysql root password. From what I gather you botched your sql users and now you can't add, drop, delete, or manage SQL databases? Purge the package, re-install, setup the package from scratch. If that doesn't work you've got a different problem. Though I don't see why you can't just login as root and edit the SQL users.
Current Rig
(14 items)
 
  
CPUMotherboardGraphicsRAM
FX-8350 4.6GHz@1.44v GA-990FXA-UD3 R4.0 HD 7950 (1100/1450) 8G Muskin DDR3 1866@8CLS 
Hard DriveOptical DriveOSMonitor
1TB WD LiteOn DVD-RW DL Linux/Windows 19" Phillips TV 1080p 
PowerCaseMouseMouse Pad
OCZ 600W Generic Junk Logitech MX400 Generic Junk 
Audio
SBL 5.1 
  hide details  
Reply
Current Rig
(14 items)
 
  
CPUMotherboardGraphicsRAM
FX-8350 4.6GHz@1.44v GA-990FXA-UD3 R4.0 HD 7950 (1100/1450) 8G Muskin DDR3 1866@8CLS 
Hard DriveOptical DriveOSMonitor
1TB WD LiteOn DVD-RW DL Linux/Windows 19" Phillips TV 1080p 
PowerCaseMouseMouse Pad
OCZ 600W Generic Junk Logitech MX400 Generic Junk 
Audio
SBL 5.1 
  hide details  
Reply
post #8 of 9
Quote:
Originally Posted by anthony92 View Post
Ok i accidently my mysql tables
I accidentally my brain.
post #9 of 9
Thread Starter 
Apparently remove and -purge are the same thing so without realising i restored "mysql".

I logged in as root. using mysql -u root -p. I made a new user for my minecraft server. And typed this and it replied.

"GRANT ALL PRIVILEGES ON logblock.* TO 'minecraft'@'localhost' WITH GRANT OPTION;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'logblock'"

I looked at the perms for root and this is what it said.


| localhost | root | XXXXXXXXXXXX | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 |


EDIT: Don't worry, its all fixed thanks everyone, cookies for all.
Edited by anthony92 - 8/8/11 at 6:57pm
Zardoz
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 950 @ 4.21 (HT/On) #3043A793 Rampage III GENE NVIDIA Geforce GTX 560 Ti MSI TFII @ Stock G.SKILL Ripjaws Series 8GB DDR3 1600 
Hard DriveOptical DriveOSMonitor
Intel X25-M SSD 80GB [x2] Raid 0 + Seagate 1TB Pioneer BR-Drive Win7 Ultimate 64bit Acer B273hu 
PowerCase
GE-M800A-d1 Gigabyte LIAN LI PC-A04B mATX 
  hide details  
Reply
Zardoz
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 950 @ 4.21 (HT/On) #3043A793 Rampage III GENE NVIDIA Geforce GTX 560 Ti MSI TFII @ Stock G.SKILL Ripjaws Series 8GB DDR3 1600 
Hard DriveOptical DriveOSMonitor
Intel X25-M SSD 80GB [x2] Raid 0 + Seagate 1TB Pioneer BR-Drive Win7 Ultimate 64bit Acer B273hu 
PowerCase
GE-M800A-d1 Gigabyte LIAN LI PC-A04B mATX 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Linux, Unix
Overclock.net › Forums › Software, Programming and Coding › Operating Systems › Linux, Unix › How do I restore mysql tables?