PDA

View Full Version : How to recover mysql



ptsz
31st May 2006, 06:35 PM
So, could somody be so kind to help me to recover my database, pls.

'FC5, mysql-5.1'

Well, I had 1 root, 1 user
user had right to insert, ...,
sometimes from root i granted that user to 'all'
and that user dropped the 'mysl' datebase
right after I reinstalled mysql & mysql-server
did not helped,

then I erased every "mysql*", "./mysql"
after I reinstalled mysql & mysql-server
user remained but all databases lost
some error, like:
Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' or something lie that appears anytime

Do you have any solution?
Thks

landoncz
31st May 2006, 06:52 PM
can't connect to local server is different than can't select a database. It appears that your mysql daemon is not running. Does the command:

ps -ef|grep mysqld
output anything? If not, then you need to start your daemon...

/etc/init.d/mysqld start
as root

ptsz
31st May 2006, 06:54 PM
NO it is running...

ptsz
1st June 2006, 09:39 AM
OK, exactly:
1.
ps -ef|grep mysqld:
ptszaboo 2727 2543 0 10:32 pts/2 00:00:00 grep mysqld


./etc/init.d/mysqld start
2.a as user
touch: cannot touch `/var/log/mysqld.log': assess denied
chown: `mysql:mysql': invalid user
chmod: `/var/log/mysqld.log' assess change: assess denied
2.b as root
chown: `mysql:mysql': invalid user

So, this is the case.

ptsz
1st June 2006, 09:41 AM
And from promt:
# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

ptsz
1st June 2006, 09:51 AM
/var/log/mysqld.log:

060601 10:45:29 mysqld started
060601 10:45:29 [ERROR] Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists!
060601 10:45:29 [ERROR] Aborting
060601 10:45:29 [Note] /usr/libexec/mysqld: Shutdown complete
060601 10:45:29 mysqld ended

pete_1967
1st June 2006, 09:53 AM
You need to check that user and group mysql exist, if not, you have to create them, or alternatively change mysql to run as another user.

ptsz
1st June 2006, 09:56 AM
Ok, msql group is exists, and on.
mysql user not exists

landoncz
1st June 2006, 03:08 PM
OK, exactly:
1.
ps -ef|grep mysqld:
ptszaboo 2727 2543 0 10:32 pts/2 00:00:00 grep mysqld


./etc/init.d/mysqld start
2.a as user
touch: cannot touch `/var/log/mysqld.log': assess denied
chown: `mysql:mysql': invalid user
chmod: `/var/log/mysqld.log' assess change: assess denied
2.b as root
chown: `mysql:mysql': invalid user

So, this is the case.

Actually if the above output is actually what you got from your grep of mysqld, then mysql was NOT running, as it only printed out your grep of mysqld. Here is an example from my machine of what it should print out when it is running:

[landon@fedora ~]$ ps -ef|grep mysqld
root 1892 1 0 May31 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --log-error=/var/log/mysqld.log
mysql 1928 1892 0 May31 ? 00:00:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock
landon 13261 13238 0 09:07 pts/2 00:00:00 grep mysqld
[landon@fedora ~]$


But of course like pete said, you also need to make sure you have the user and group 'mysql'...

ptsz
2nd June 2006, 06:00 AM
Understand, I am newbie in linux, and very newbie in mysql...
Ok, I was wrong, I thought it was running because it was supposed to run,,,,
By the end I reinstall all Mysql,
It said, no mysql user, and use as root user..
Now as user: ps -ef|grep mysqld
"ptszaboo 2431 2284 0 06:55 pts/3 00:00:00 grep mysqld"
as root:
"root 2450 2438 0 06:56 pts/3 00:00:00 grep mysqld"

It works now, thanks a lot.
How can I reset that config as yours?

ptsz
2nd June 2006, 06:33 AM
Ok the previpous was my second failure:
mysql deamon was off,
i started it manually, and
NOW :
[ptszaboo@Pentium4 ~]$ ps -ef|grep mysqld
root 3257 2438 0 07:24 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --user=root
root 3297 3257 0 07:24 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=root --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock
ptszaboo 3480 3429 0 07:30 pts/4 00:00:00 grep mysqld

It seems to be similar to yours, isn't?

landoncz
2nd June 2006, 02:39 PM
It is no problem, we were all newbies at one time or another...

Yep, looks like you've got it running ok, now can you connect? Or do you still get the connection errors? Try something like:

mysql -u root
and see if it lets you in or gives you an error...

Also, if you want to have the daemon startup automatically, open up your services configuration (system-config-services as root or from the menu), and select the mysqld service to start at boot and save your changes... You may have already done that though

ptsz
2nd June 2006, 03:29 PM
Well
I knew about auto sevices starting. BUT
If I it set to start auto, I got errors.
And right now I got error, when I tried to start it manually:
060602 16:08:08 mysqld started
060602 16:08:08 [ERROR] Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists!

060602 16:08:08 [ERROR] Aborting

060602 16:08:08 [Note] /usr/libexec/mysqld: Shutdown complete

I dont understand , 6 Hours ago I worked with my database ....
????????

ptsz
2nd June 2006, 03:48 PM
Well after a while I found one solution for the problem:
I create mysql user, AND add write permission to
/var/run/mysqld folder
It seems to be good.
[root@webboy ptszaboo]# ps -ef|grep mysqld
root 7952 1 0 16:45 pts/3 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --log-error=/var/log/mysqld.log
mysql 7988 7952 0 16:45 pts/3 00:00:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock
root 8048 4380 0 16:48 pts/3 00:00:00 grep mysqld
[root@webboy ptszaboo]#
Thanks

SHtRO
2nd June 2006, 03:51 PM
You have hosed your installation. You might try backing up your database directory. Uninstall mysqld. Reinstall it from RPM's (which should handle setting up the users/groups, etc.). Then you can try restoring your DB directory, since the DB version should be the same.

You apparently deleted something extra when you were trying to clean up. Now your DB has difficulty starting and cannot interact properly.

Backup, Uninstall, and Reinstall. Then be more careful about what you delete. Make prolific use of 'rpm -q' to find out what is required and what is not (see "man rpm"). Also do a bunch of reading on MySQL administration. There are innumerable articles and tutorials on the 'net that are current.

But the real thrust here is to do your homework before deleting files and setting perms on your system. Considering you are a newbie, you might want to review some security HOWTO's as well.

Maybe someone else has in their mind some concise commands for the ops mentioned above...

Best of luck.