LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 03-12-2009, 08:14 AM   #1
leicaphotos
Member
 
Registered: Oct 2008
Posts: 68

Rep: Reputation: 15
mysql databases disappeared


help
!!!!!!

I had a system hickup yesterday -- something happened and i'm not sure what - all I know is I can't find my mysql database -- it's not listed in phpmyadmin - need to recover database cookbooks asap - how can I find it? I ran all this as I was getting a mysql crash - not starting, stopping from pid error ---

running pcbsd,mysql

Code:
root@pcbsd# /usr/local/bin/mysqld_safe --user=root &
[1] 2298                                            
root@pcbsd# Starting mysqld daemon with databases from /var/db/mysql
STOPPING server from pid file /var/db/mysql/pcbsd.pid               
090311 13:44:21  mysqld ended                                       


[1]    Done                          /usr/local/bin/mysqld_safe --user=root
root@pcbsd# /usr/bin/mysqld_safe --user=root &                             
[1] 87632                                                                  
root@pcbsd# /usr/bin/mysqld_safe: Command not found.                       
cd /usr/local/bin                                                          
[1]    Exit 1                        /usr/bin/mysqld_safe --user=root      
root@pcbsd#                                                                
root@pcbsd# cd /usr/local/bin
root@pcbsd# mysql_install_db 
Installing MySQL system tables...
090312  6:45:10 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090312  6:45:10 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090312  6:45:10 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072                    
OK                                                                                                          
Filling help tables...                                                                                      
090312  6:45:10 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090312  6:45:10 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090312  6:45:10 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072                    
OK                                                                                                                

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'     
/usr/local/bin/mysqladmin -u root -h pcbsd password 'new-password'

Alternatively you can run:
/usr/local/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.             

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local ; /usr/local/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl              

Please report any problems with the /usr/local/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com                                         
Support MySQL by buying support/licenses at http://shop.mysql.com
\root@pcbsd# /usr/bin/mysqld_safe --user=root &
[1] 95492                                      
/usr/bin/mysqld_safe: Command not found.       
root@pcbsd# /usr/local/bin/mysqld_safe --user=root &
[2] 95576
[1]    Exit 1                        /usr/bin/mysqld_safe --user=root
root@pcbsd# Starting mysqld daemon with databases from /var/db/mysql

root@pcbsd# mysql --user=root mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 207
Server version: 5.0.67 FreeBSD port: mysql-server-5.0.67_1

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

mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY '123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
mysql> GRANT ALL ON *.* TO 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> mysqladmin -u root password m3mph1s
    ->
    -> use mysql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'mysqladmin -u root password m3mph1s

use mysql' at line 1
mysql> update user set password=PASSWORD("123") where User='root';
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
Old 03-12-2009, 08:58 AM   #2
bathory
LQ Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 11,598

Rep: Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532
You shouldn't have run mysql_install_db, because the mysql database was overwitten by a new one with only the default user (root) and privileges. Whatl you have to do now is to reconfigure phpmyadmin with the new root/password.
Hopefully your other databases should be there.
You can read this to see how you can recover mysql from crash and consider keeping frequent backups of your databases.

Regards

Last edited by bathory; 03-12-2009 at 08:59 AM.
 
Old 03-12-2009, 09:05 AM   #3
leicaphotos
Member
 
Registered: Oct 2008
Posts: 68

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by bathory View Post
You shouldn't have run mysql_install_db, because the mysql database was overwitten by a new one with only the default user (root) and privileges. Whatl you have to do now is to reconfigure phpmyadmin with the new root/password.
Hopefully your other databases should be there.
You can read this to see how you can recover mysql from crash and consider keeping frequent backups of your databases.

Regards
does that mean the database could be gone?
 
Old 03-12-2009, 09:08 AM   #4
leicaphotos
Member
 
Registered: Oct 2008
Posts: 68

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by bathory View Post
You shouldn't have run mysql_install_db, because the mysql database was overwitten by a new one with only the default user (root) and privileges. Whatl you have to do now is to reconfigure phpmyadmin with the new root/password.
Hopefully your other databases should be there.
You can read this to see how you can recover mysql from crash and consider keeping frequent backups of your databases.

Regards
also, the databases are not showing up at all when they are called by apps on the server
 
Old 03-12-2009, 09:19 AM   #5
leicaphotos
Member
 
Registered: Oct 2008
Posts: 68

Original Poster
Rep: Reputation: 15
I did a mysql show databases from terminal and the database in question is not showing

how screwed am I? I've got a backup from 2 or three weeks ago
 
Old 03-12-2009, 09:25 AM   #6
leicaphotos
Member
 
Registered: Oct 2008
Posts: 68

Original Poster
Rep: Reputation: 15
there is a file called cookbooks which is the database sitting in /usr/local/www/apache22/data/phpmyadmin -- this may be the info =--- how do I get it to reload?


it contains the following
admin:$2a$04$b3YRT/..G.....O.../5iuOGhVrc3Ns5hwigGuZzwiM9nqYSNCTsu

Last edited by leicaphotos; 03-12-2009 at 09:28 AM.
 
Old 03-12-2009, 09:43 AM   #7
bathory
LQ Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 11,598

Rep: Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532
Quote:
also, the databases are not showing up at all when they are called by apps on the server
Maybe that's not a problem if the applications in question use the old credentials/privileges etc
Quote:
I did a mysql show databases from terminal and the database in question is not showing
how screwed am I? I've got a backup from 2 or three weeks ago
This is a bit scary. It should show the available databases. Take a look at /usr/local/mysql/data (if you still use the same mysql from this post) to see if the other databases are there.
What you can try is to stop mysqld, and from the backup you have copy the mysql database in the directory where the databases are stored (I guess it's /usr/local/mysql/data) overwriting the new one. Make a backup first or better you can move it to somewhere else:
Code:
mv /usr/local/mysql/data/mysql /usr/local/mysql/data/mysql.old
So you'll have the previous mysql db with the previous users/credentials/privileges etc
Start mysqld and see if it works
Quote:
there is a file called cookbooks which is the database sitting in /usr/local/www/apache22/data/phpmyadmin -- this may be the info =--- how do I get it to reload?
it contains the following
admin:$2a$04$b3YRT/..G.....O.../5iuOGhVrc3Ns5hwigGuZzwiM9nqYSNCTsu
I don't know what this file is and how it's used by phpmyadmin.
 
Old 03-12-2009, 10:09 AM   #8
leicaphotos
Member
 
Registered: Oct 2008
Posts: 68

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by bathory View Post
Maybe that's not a problem if the applications in question use the old credentials/privileges etc
This is a bit scary. It should show the available databases. Take a look at /usr/local/mysql/data (if you still use the same mysql from this post) to see if the other databases are there.
What you can try is to stop mysqld, and from the backup you have copy the mysql database in the directory where the databases are stored (I guess it's /usr/local/mysql/data) overwriting the new one. Make a backup first or better you can move it to somewhere else:
Code:
mv /usr/local/mysql/data/mysql /usr/local/mysql/data/mysql.old
So you'll have the previous mysql db with the previous users/credentials/privileges etc
Start mysqld and see if it works
I don't know what this file is and how it's used by phpmyadmin.


old dbase is located in -- just found it -- it looks correct -- now, how do I get that back into mysql's system?
 
Old 03-12-2009, 10:13 AM   #9
leicaphotos
Member
 
Registered: Oct 2008
Posts: 68

Original Poster
Rep: Reputation: 15
all the myd and myi files are there -- now, how do I go about reloading the database with this info
 
Old 03-12-2009, 10:23 AM   #10
bathory
LQ Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 11,598

Rep: Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532Reputation: 1532
Quote:
all the myd and myi files are there -- now, how do I go about reloading the database with this info
I guess you mean the tables of the mysql database (the initial database that holds users etc).
If that's correct, then as I told you, you have to:
1. Stop the mysqld daemon
2. Rename the /usr/local/mysqldata/mysql to something else.
3. Copy from the backup only the mysql directory in the above location.
4. Restart mysqld
 
Old 03-12-2009, 10:45 AM   #11
leicaphotos
Member
 
Registered: Oct 2008
Posts: 68

Original Poster
Rep: Reputation: 15
the my.cnf got hosed as part of reconstructing my original problem from yesterday, now, I'm back to getting this error:

STOPPING server from pid file /var/run/mysqld/mysqld.pid
090312 09:43:25 mysqld ended


the data seems in tact
 
Old 03-12-2009, 10:49 AM   #12
leicaphotos
Member
 
Registered: Oct 2008
Posts: 68

Original Poster
Rep: Reputation: 15
ok -- i got it fixed, thanks for your help -- daily backups from now on
 
  


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 Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: How To Back Up MySQL Databases Without Interrupting MySQL LXer Syndicated Linux News 0 05-14-2007 10:16 AM
MySQL big databases baddah Programming 2 08-29-2006 01:55 AM
Comparing two MySQL Databases WeNdeL Linux - Software 1 09-30-2003 04:47 PM
restoring mysql databases? and mysql permissions... armegeden Linux - Software 0 03-13-2003 12:04 PM
mysql databases k3v0 Linux - Software 2 12-03-2002 08:41 PM


All times are GMT -5. The time now is 09:56 AM.

Main Menu
Advertisement
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
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration