LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices

Reply
 
Search this Thread
Old 03-04-2003, 04:23 PM   #1
amp2000
Member
 
Registered: Oct 2001
Location: Dublin, Ireland
Distribution: Mandrake 9.0 mostly!
Posts: 303

Rep: Reputation: 30
Unhappy Lost all my MySQL DB's, help


Help, I think I've corrupted all my databases, I'm not sure.
I was messing around in phpMyAdmin earlier & I think changed some permissions.
Now all of my MySQL databases are showing up in phpMyAdmin as empty, ie no tables in any of them.
Even my mysql database is empty so there is no root user in it containing my password to access any of the other databases.
Whats wierd though is all my databases seem to contain data when I view them from the command line using 'ls -l'
When I try access a database from the command line as root I can access the database but it says permission denied when i try to show the tables.

Has anybody got an idea whats going on here

Thanks in advance for any replies.
 
Old 03-04-2003, 06:41 PM   #2
nxny
Member
 
Registered: May 2002
Location: AK - The last frontier.
Distribution: Red Hat 8.0, Slackware 8.1, Knoppix 3.7, Lunar 1.3, Sorcerer
Posts: 771

Rep: Reputation: 30
mysql_install_db is a script that can be run to replace your user/grant tables. It can be edited to make sure that it does only the things you want.

Here's is a whole section on the --skip-grant-tables option and resetting the root password.

I dont know what happened to your databases, but if they are intact and it's just permissions that are screwed up, you may be able to reinstate them once you can login as root.
 
Old 03-04-2003, 07:56 PM   #3
amp2000
Member
 
Registered: Oct 2001
Location: Dublin, Ireland
Distribution: Mandrake 9.0 mostly!
Posts: 303

Original Poster
Rep: Reputation: 30
Hi nxny, thanks for the reply, I have the root password, ie
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[root@hostname]# mysql mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
~~~~~~~~~~~~~~~~~~~~~~~~~~~
It's when I try to see what tables are there I get access denied.

Another thing, I reinstalled mysql & phpMyAdmin & still having the same problem.
phpMyAdmin & Webmin both claim that all my databases have no tables and all contain 0 bytes.I know there is data in /var/lib/mysql because I just put my /var/lib/mysql directory into a tar ball & it's over 3MB.

Any more ideas?

Thanks
 
Old 03-05-2003, 12:57 PM   #4
amp2000
Member
 
Registered: Oct 2001
Location: Dublin, Ireland
Distribution: Mandrake 9.0 mostly!
Posts: 303

Original Poster
Rep: Reputation: 30
Can anyone tell me why I am able to access my databases using my root password but when I try to update or view it I get 'access denied for root@localhost'
I thought the root user would be able to do anything to the DB.

any ideas anyone?

Thanks
 
Old 03-05-2003, 11:04 PM   #5
nxny
Member
 
Registered: May 2002
Location: AK - The last frontier.
Distribution: Red Hat 8.0, Slackware 8.1, Knoppix 3.7, Lunar 1.3, Sorcerer
Posts: 771

Rep: Reputation: 30
rm -rf /var/lib/mysql/mysql

That will destroy your grant tables.

Use mysql_install_db to restore those grant tables and insert rows similar to the ones you already have in mysql.user, mysql.db etc.
 
Old 03-06-2003, 06:02 AM   #6
amp2000
Member
 
Registered: Oct 2001
Location: Dublin, Ireland
Distribution: Mandrake 9.0 mostly!
Posts: 303

Original Poster
Rep: Reputation: 30
nxny, now why didnt I think of that
Thank you VERY much, it works now & I havent lost any of my databases

Thanks again, you saved me some major headaches


Last edited by amp2000; 03-06-2003 at 06:05 AM.
 
Old 03-06-2003, 12:30 PM   #7
nxny
Member
 
Registered: May 2002
Location: AK - The last frontier.
Distribution: Red Hat 8.0, Slackware 8.1, Knoppix 3.7, Lunar 1.3, Sorcerer
Posts: 771

Rep: Reputation: 30
You're welcome.
 
Old 04-17-2003, 02:25 AM   #8
markohrastovec
LQ Newbie
 
Registered: Apr 2003
Location: Slovenija
Distribution: RedHat
Posts: 1

Rep: Reputation: 0
I also have almost identical problem. The only difference is that I used RPMs to install MySQL on RedHat distribution. When I upgraded from 3.23 to 4 all tables from databases disapeared. The files were also present on the disks but the client could not access them.

When I downgraded to 3.23 again all the tables were visible again. I have performed that upgrade/downgrade procedure a few times always with the same result. I have tried everything I could remember (converting all ISAM tables to MyISAM,...).

The solution you have here should work for me to, I think. The fact that I am using RPMs should not be the problem. I will backup mysql database, empty it and then install new RPMs. I will report what will the result be.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Berkeley DB using a DbEnv and multiple Db's traene Programming 1 07-20-2005 01:12 PM
How to copy mysql db's from hardisk to new server? nbier Linux - Software 4 09-01-2004 04:24 PM
Backing up large MySQL DB's WeNdeL Linux - Software 5 11-29-2003 10:34 AM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 01:27 PM
Lost privileges for MySQL !!! davidgordon Linux - Newbie 2 01-09-2003 08:00 PM


All times are GMT -5. The time now is 11:27 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration