解决以下问题：
mysql4.0在freebsd中安装成功，但是不能启动，在错误日志报错如下：
060831 5:49:04 libexec/mysqld: Can't open file: 'host.MYI'. (errno: 142)...
连接数据库报错：ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Freebsd 6.1下安装后不能启动问题的解决
一、运行环境
OS: Freebsd 6.1,
DB:mysql 4.0
二、故障描述：
在freebsd中安装了mysql4.1后，卸载，又安装mysql4.0,安装成功，但是不能启动，在错误日志报错如下：
060831 5:49:04 r/local/libexec/mysqld: Can't open file: 'host.MYI'. (errno: 142)
060831 5:49:04 Fatal error: Can't open privilege tables: File '/usr/local/share/mysql/charsets/?.c
onf' not found (Errcode: 2)
060831 5:49:04 Aborting
连接数据库报错：ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
错误日志的路径：
一般在/var/db/mysql目录下
三、原因分析：
这是因为降级安装后，数据库系统表不会重新安装，于是mysql4.0读取原来安装的mysql4.1的系统表，导致出现问题。
四、解决办法：
1：备份原来的 /var/db/mysql目录
2：卸载安装mysql4.0
3:删除原来的 /var/db/mysql目录
4:重新安装mysql4.0
5:运行命令：/usr/local/etc/rc.d/mysql-server start
更简单的办法：
1：备份原来的 /var/db/mysql目录
2:删除原来的 /var/db/mysql目录
3:运行命令：/usr/local/etc/rc.d/mysql-server start
因为/usr/local/etc/rc.d/mysql-server start会自动重建mysql系统数据库，日志如下：
060831 06:36:53 mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
060831 6:36:53 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
060831 6:36:54 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
060831 6:36:54 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
060831 6:36:55 InnoDB: Started
五、相关资料
Really Really im in a Hole PLEASE help :(
5need03-11-2005, 10:40 AM
Hello ,
Im sorry for my last subject i didn't answer but i'm was sick and my customers calls me and said that my server MySQL not work my problem is when i upgrade mysql from 4.0 to 4.1 and then no mysql work i try to do rpm -e for all mysql rpm files installed and install it again /scripts/mysqlup --force nothing done i need to be back to version 4.0 after many trys it's showing me
Warning, no valid mysql.sock file found.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!
mysql has failed
Unknown argument '--verbose'
Install Complete
I try to make ln -s for the sock file but when i start the mysql it's telling me that no .pid file i don't know what to do just i wanna now to back mysql 4.0 please help please :(
:bawling:
Thanks u all
--------------------------------------------------------------------------------
zupanm03-11-2005, 10:47 AM
is mysql running? check in a ps aux | grep mysql
and then see if there is a sock file in /tmp
--------------------------------------------------------------------------------
5need03-11-2005, 10:51 AM
mysql not work
/tmp nothing here nothing shortcut there i make it but after restart it removed
--------------------------------------------------------------------------------
Website Rob03-11-2005, 11:00 AM
Solution
type: killall -9 mysqld
type: cd /etc/rc.d/init.d
type: safe_mysqld --skip-grant-tables &
type: exit
type: mysqladmin status << need to be using WHM for this command
Unless the problem is more complicated, the above usually gets 'mysqld' back up & running. The last command verifies that for you.
--------------------------------------------------------------------------------
5need03-11-2005, 11:27 AM
Hello Man ,
Thanks all for your help zupanm & Website Rob
I have use the command before but the mysql always stop after a whole time :( Please help :(
--------------------------------------------------------------------------------
zupanm03-11-2005, 11:31 AM
tail -f /var/log/mysqld.log
then try to start mysql with service mysqld start.. see what happens.. feel free to IM me on aol if you have it.. hijinks7
--------------------------------------------------------------------------------
5need03-11-2005, 11:36 AM
Hello
THat what is shown
root@host [/etc/rc.d/init.d]# tail -f /var/log/mysqld.log
050311 11:32:11 /usr/sbin/mysqld: Can't open file: 'host.MYI'. (errno: 142)
050311 11:32:11 Fatal error: Can't open privilege tables: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
050311 11:32:18 /usr/sbin/mysqld: Can't open file: 'post.MYI'. (errno: 144)
050311 11:32:35 /usr/sbin/mysqld: Can't open file: 'user.MYI'. (errno: 142)
050311 11:32:35 /usr/sbin/mysqld: Can't open file: 'db.MYI'. (errno: 142)
050311 11:32:35 /usr/sbin/mysqld: Can't open file: 'host.MYI'. (errno: 142)
050311 11:32:35 Fatal error: Can't open privilege tables: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
050311 11:32:35 /usr/sbin/mysqld: Can't open file: 'host.MYI'. (errno: 142)
050311 11:32:35 Fatal error: Can't open privilege tables: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
050311 11:32:46 /usr/sbin/mysqld: Can't open file: 'post.MYI'. (errno: 144)
zupanm i will contact you soon
--------------------------------------------------------------------------------
zupanm03-11-2005, 12:59 PM
sounds like when you upgraded.. its looking for your databases in the wrong place
--------------------------------------------------------------------------------
5need03-11-2005, 01:04 PM
zupanm
If you have MSN Can i have it ?
Kind Of Regards .
--------------------------------------------------------------------------------
5need03-12-2005, 05:54 AM
Please help :(
--------------------------------------------------------------------------------
EXOWorks03-12-2005, 09:54 AM
You'll need to reinstall mysql so that it creates a new mysql db ...
--------------------------------------------------------------------------------
Steven03-12-2005, 09:56 AM
OUCH!!!
You made a huge mistake downgrading...!!! It took me 3 hours to figure out a fix this on another guys server. You first have to fix the charset, then you have to repair the databases, and in my case I had to recreate the root user. This is because there are some large changes to the mysql tables and downgrading messes it up, and upgrading again messes it up once again
--------------------------------------------------------------------------------
Steven03-12-2005, 10:00 AM
Originally posted by Mike_R
You'll need to reinstall mysql so that it creates a new mysql db ...
if you recreate the mysql db he will lose data.
--------------------------------------------------------------------------------
UniServe Hosting03-12-2005, 01:27 PM
if you recreate the mysql db he will lose data.
Anyone ever hear of backups?
MySQL tables are stored as files which makes it easy to do a backup.
mysqldump --tab=/path/to/some/dir --opt db_name
--------------------------------------------------------------------------------
5need03-12-2005, 01:47 PM
oh **** :(
If i upgrade to 4.1 i'm in problem if in 4.0 what the best to do :(
:( regards :(
--------------------------------------------------------------------------------
Steven03-12-2005, 05:57 PM
Originally posted by unixparse
Anyone ever hear of backups?
MySQL tables are stored as files which makes it easy to do a backup.
mysqldump --tab=/path/to/some/dir --opt db_name
Yes but hes still going to have table errors if he restores the database.
--------------------------------------------------------------------------------
5need03-13-2005, 06:14 AM
thelinuxguy can you give me a price for fix ?
or anybody can help me to fix :(
Regards
--------------------------------------------------------------------------------
iPetey03-13-2005, 09:12 AM
Hiya,
Don't know if you have fixed the problem, from your previous post:
050311 11:32:11 /usr/sbin/mysqld: Can't open file: 'host.MYI'. (errno: 142)
050311 11:32:11 Fatal error: Can't open privilege tables: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
050311 11:32:18 /usr/sbin/mysqld: Can't open file: 'post.MYI'. (errno: 144)
050311 11:32:35 /usr/sbin/mysqld: Can't open file: 'user.MYI'. (errno: 142)
050311 11:32:35 /usr/sbin/mysqld: Can't open file: 'db.MYI'. (errno: 142)
050311 11:32:35 /usr/sbin/mysqld: Can't open file: 'host.MYI'. (errno: 142)
050311 11:32:35 Fatal error: Can't open privilege tables: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
050311 11:32:35 /usr/sbin/mysqld: Can't open file: 'host.MYI'. (errno: 142)
050311 11:32:35 Fatal error: Can't open privilege tables: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
050311 11:32:46 /usr/sbin/mysqld: Can't open file: 'post.MYI'. (errno: 144)
MySQL error: 142 = Unknown character set used
MySQL error: 144 = Table is crashed and last repair failed
This is because in 4.1 has a different setup for character sets and collations. Have you fixed it and which version are you now using?
--------------------------------------------------------------------------------
iPetey03-13-2005, 09:18 AM
Going back to the original problem. You need to check the configuration file. If it's an RPM install it's probably been put in /etc
You need to check the locations of the socket file (make sure their in the same place)
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
#socket = /tmp/mysql.sock
skip-locking
but also, as importantly do a search for:
#skip-networking
Make sure this is commented out otherwise it won't listen on port 3306 and so then it tries the socket files.
You should be able to upgrade to 4.1 without any problems on your databases (unless maybe you have a different from default character set? not sure there but I've done an upgrade like this and all I did was export the databases on the old version and then import the into the new database)
So do what the unixparse said about exporting the databases and then re-import to the new version.
--------------------------------------------------------------------------------
Steven03-13-2005, 11:15 AM
iPetey he wants to downgrade but its to late now. The datacenter screwed up the user accounts.
--------------------------------------------------------------------------------
5need03-13-2005, 01:45 PM
IF i upgrade to 4.1 i will have a lot of problems like phpmyadmin seeing all databases to anyuser 2. all users that connect to datebase will be delted and many many problems now i have work in mysql 4.0 but i have problems that i start it on the command /usr/bin/safe_mysqld --skip-grant-tables but it's daily stop and i need to do 3 commands /scripts/upcp --force /scripts/mysqlup --force /scripts/easyapache :( what the best to do to fix the problem >>>
--------------------------------------------------------------------------------
macooper03-13-2005, 05:24 PM
Hi,
I'm not sure if it will help, but I came accross this on the mysql site :-
"The Berkeley DB table handler is updated to DB 4.1 (from 3.2) which has a new log format. If you have to downgrade back to 4.0 you must use mysqldump to convert your BDB tables to text format and delete all log.?????? files before you start MySQL 4.0 and read back the data."
The full page is here (http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html).
As your starting with --skip-grant-tables, you will be running without a password. So try :-
mysqldump -c --all-databases > mysql.bak
Check the backup to make sure it looks complete. Particularly check that it has backed up the mysql database and the passwords in the user table. Apparently, the other part of the problem is that the password field is longer in mysql 4.1, so you may need to manually remove the passwords from the file and then reset them once the database is restored.
I can't be sure the above procedure will work, as your installation seems to be in a bit of a mess at the moment. To be on the safe side, backup your mysql database directory using tar or similar before trying this procedure. At least then, you can get back to where you started if anything goes wrong.
stop mysql, delete the log files as mentioned in the above article, then use phpmyadmin to restore the databases from the backup.
--------------------------------------------------------------------------------
5need03-14-2005, 11:45 AM
Got Fixed !
Thanks to all bodys whose try to help
what i do to fix the problem
--- first of this you must do service mysql stop
and then :-
1.Take Backup of Mysql DIR /var/lib/mysql
2.i do this command rpm -qa | grep MySQL | xargs rpm -e
3. i do this command /scripts/mysqlup --force /scripts/upcp --force
4.do this command mysql_fix_privilege_tables root_password
5.I go to WHM and rebuild and but right in Use Mysql system
6.Problem Got Fixed :D
If you have some users with password in Cpanel do the following command after untarring all backups you have in cpbackup in daily dir
for i in ` ls -l */mysql.sql | awk '{print $9}' ` ; do mysql -parameters --password=rootpasswordofmysql < $i ; done
Do this command only if you don't see that users connect with DB
Regards to all :D
H a V E a N i C E D a Y
Spiecal Thanks to macooper thelinuxguy iPetey Website Rob unixparse
原文网址：http://www.webhostingtalk.com/archive/index.php/t-383746-p-1.html
标题: a problem about mysql
标题: a problem about mysql
--------------------------------------------------------------------------------
作者: brucelee520 时间: 2005-8-3 16:45 标题: a problem about mysql
after installation of mysql,finish the serverclientdevelshareshare-compat packages with no problem,but why the information "ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) "show when i run the command names mysqlshow??????
the four packages is the type of rpm
please tell me the reason,and how to solute it?thanks!
--------------------------------------------------------------------------------
作者: brucelee520 时间: 2005-8-3 18:38 标题: a problem about mysql
哈哈后 ，找到原因啦
哈哈后，在赛迪网发现的，不过为了让后来着少犯路
我特付上，哈哈后
一般情况下都是不能正常链接数据库，错误提示一般为：
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
其实网上大家问的最多的都是整个问题，说什么链接不到mysqld.sock，其实大家不妨看看mysql的错误日志就明白怎么回事，我这里的错误日志是在/var/lib/mysql/*.err.
你会发现mysql只所以不能启动，是因为/var/lib/mysql的权限不允许mysql服务访问，英文mysql默认是调用mysql用户来启动服务的，好了，既然知道是什么原因找到不能启动，那就简单了。我们只要chown -R mysql:mysql /var/lib/mysql就行，如果还是启动不了，再慢慢调试权限，反正一般启动不了都是权限的问题。
如果大家还是不能启动不了的话，那就用我的比较繁琐的权限的设置，反正我每次都是这么做的，一般不会有问题，见下：
chown -R root /usr/local/mysql
chgrp -R mysql /usr/local/mysql
chown -R root /usr/local/mysql/bin
chgrp -R mysql /usr/local/mysql/bin
chgrp -R mysql /var/lib/mysql
chmod 777 /var/lib/mysql
chown -R root /var/lib/mysql/mysql
chgrp -R mysql /var/lib/mysql/mysql
chmod 777 /var/lib/mysql/mysql
chown -R root /var/lib/mysql/mysql/*
chgrp -R mysql /var/lib/mysql/mysql/*
chmod 777 /var/lib/mysql/mysql/*
chmod 777 /usr/local/mysql/lib/mysql/libmysqlclient.a
做完上面的步骤，然后把你编译目录的一个脚本COPY过去
cp support-files/mysql.server /etc/rc.d/init.d/mysqld chkconfig --add mysqld 用ntsysv设置使mysql每次启动都能自动运行。
好了，至此mysql安装完毕，你可以这样起动你的mysql服务
--------------------------------------------------------------------------------
作者: carset 时间: 2005-8-3 21:04 标题: a problem about mysql
mysql 的 INSTLL 里推荐的脚本
CODE:[Copy to clipboard]#whoami
root
#addgroup mysql
#adduser -g mysql mysql
#pwd
your_path/mysql
#scripts/mysql_install_db --user=mysql
#chown -R root .
#chgrp -R mysql .
#chown -R mysql data
--------------------------------------------------------------------------------
作者: brucelee520 时间: 2005-8-4 12:15 标题: a problem about mysql
cp support-files/mysql.server /etc/rc.d/init.d/mysqld chkconfig --add mysqld 用ntsysv设置使mysql每次启动都能自动运行。
...........................................................................
how can i do??
before this step,there is no error messenges
but,i can't start the mysql
--------------------------------------------------------------------------------
作者: brucelee520 时间: 2005-8-4 12:32 标题: a problem about mysql
sir, following the step of your messenges,the error 2002 messenges still show,
please tell me the details in the last step to me?thanks!
--------------------------------------------------------------------------------
作者: brucelee520 时间: 2005-8-4 21:03 标题: a problem about mysql
the error messenges is that
050720 19:53:17 mysqld started
050720 19:53:17 Warning: Asked for 196608 thread stack, but got 126976
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
050720 19:53:17 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
050720 19:53:21 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
050720 19:53:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
050720 19:53:27 InnoDB: Started
050720 19:53:27 /usr/sbin/mysqld: Can't open file: 'host.MYI'. (errno: 142)
050720 19:53:27 Fatal error: Can't open privilege tables: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
050720 19:53:27 Aborting
050720 19:53:27 InnoDB: Starting shutdown...
050720 19:53:29 InnoDB: Shutdown completed
050720 19:53:29 /usr/sbin/mysqld: Shutdown Complete
050720 19:53:29 mysqld ended
--------------------------------------------------------------------------------
原文网址：http://bbs.chinaunix.net/viewthread.php?action=printable&tid=587971
主 题： 急：unix下mysql 启动不起来 [ERROR] /usr/local/mysql/bin/mysqld: Can't find file: 'host.MYI' (errno: 2)
所属论坛： 其他数据库开发 MySQL/Postgresql
回复次数： 1
发表时间： 2006-4-10 15:14:57
作 者： goodboyliu ()
使用如下命令启动mysql：
/usr/local/mysql/scripts/mysql_install_db &
/usr/local/mysql/bin/safe_mysqld &
出现错误log：
060410 14:30:17 mysqld started
060410 14:30:17 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060410 14:30:17 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
060410 14:30:17 InnoDB: Flushing modified pages from the buffer pool...
060410 14:30:17 InnoDB: Started; log sequence number 0 43634
060410 14:30:17 [ERROR] /usr/local/mysql/bin/mysqld: Can't find file: 'host.MYI' (errno: 2)
060410 14:30:17 [ERROR] /usr/local/mysql/bin/mysqld: Can't find file: 'host.MYI' (errno: 2)
060410 14:30:17 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: 'host.MYI' (errno: 2)
060410 14:30:17 mysqld ended
但是在当前目录下有/usr/local/mysql/ 下host.frm文件啊？我是用root登陆的，应该不存在权限的问题。切换到support-files
[root@pek-redhat1 support-files]# ./mysql.server start
Can't execute ./bin/mysqld_safe[FAILED]
请大虾指点一下！！！（我是初学mysql，希望您能详细指导指导，谢谢！！）
回复人：iisbsd(墙头草) 2006-04-16 10:27:00 得分:0
? host表的索引文件没了，执行mysql_install_db试试。
344
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
