LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)
-   -   mysql databases disappeared (https://www.linuxquestions.org/questions/linux-newbie-8/mysql-databases-disappeared-711022/)

leicaphotos 03-12-2009 07:14 AM

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)


bathory 03-12-2009 07:58 AM

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

leicaphotos 03-12-2009 08:05 AM

Quote:

Originally Posted by bathory (Post 3473125)
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?

leicaphotos 03-12-2009 08:08 AM

Quote:

Originally Posted by bathory (Post 3473125)
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

leicaphotos 03-12-2009 08:19 AM

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

leicaphotos 03-12-2009 08:25 AM

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

bathory 03-12-2009 08:43 AM

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.

leicaphotos 03-12-2009 09:09 AM

Quote:

Originally Posted by bathory (Post 3473178)
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?

leicaphotos 03-12-2009 09:13 AM

all the myd and myi files are there -- now, how do I go about reloading the database with this info

bathory 03-12-2009 09:23 AM

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

leicaphotos 03-12-2009 09:45 AM

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

leicaphotos 03-12-2009 09:49 AM

ok -- i got it fixed, thanks for your help -- daily backups from now on


All times are GMT -5. The time now is 08:49 PM.