MySQL密码哈希方法旧与新 [英] Mysql password hashing method old vs new
问题描述
我正在尝试从位于slicehost(两个不同的托管公司)的服务器中的php脚本连接到dreamhost上的mysql服务器. 我需要执行此操作，以便可以将slicehost上的新数据传输到dreamhost.使用转储不是一种选择，因为表结构不同，我只需要传输一小部分数据(每日记录100-200条) 问题是我在slicehost上使用了新的MySQL密码哈希方法，而dreamhost使用了旧的方法，所以我得到了
I'm trying to connect to a mysql server at dreamhost from a php scrip located in a server at slicehost (two different hosting companies). I need to do this so I can transfer new data at slicehost to dreamhost. Using a dump is not an option because the table structures are different and i only need to transfer a small subset of data (100-200 daily records) The problem is that I'm using the new MySQL Password Hashing method at slicehost, and dreamhost uses the old one, So i get
$link = mysql_connect($mysqlHost, $mysqlUser, $mysqlPass, FALSE);
Warning: mysql_connect() [function.mysql-connect]: OK packet 6 bytes shorter than expected
Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication
Warning: mysql_query() [function.mysql-query]: Access denied for user 'nodari'@'localhost' (using password: NO)
事实:
- 我需要在slicehost上继续使用新方法，并且我不能使用较早的php版本/库
- 数据库太大，无法每天通过转储进行传输
- 即使我这样做，表的结构也不同
- 我每天只需要复制其中的一小部分(每天仅更改100-200条记录)
- 由于表是如此不同，我需要使用php作为桥接来规范化数据
- 已经用谷歌搜索了
- 已经与两个支持人员进行了交谈
对我来说，更明显的选择是在dreamhost上开始使用新的MySQL密码哈希方法，但他们不会更改它，而且我不是root用户，所以我自己无法做到这一点.
The more obvious option to me would be to start using the new MySQL Password Hashing method at dreamhost, but they will not change it and i'm not root so i can't do this myself.
任何疯狂的主意吗?
通过VolkerK建议:
By VolkerK sugestion:
mysql> SET SESSION old_passwords=0;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @@global.old_passwords,@@session.old_passwords, Length(PASSWORD('abc'));
+------------------------+-------------------------+-------------------------+
| @@global.old_passwords | @@session.old_passwords | Length(PASSWORD('abc')) |
+------------------------+-------------------------+-------------------------+
| 1 | 0 | 41 |
+------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)
现在显而易见的事情是运行 mysql> SET GLOBAL old_passwords = 0; 但是我需要超级特权才能做到这一点，他们不会给我
The obvious thing now would be run a mysql> SET GLOBAL old_passwords=0; But i need SUPER privilege to do that and they wont give it to me
如果我运行查询
SET PASSWORD FOR 'nodari'@'HOSTNAME' = PASSWORD('new password');
我得到了错误
ERROR 1044 (42000): Access denied for user 'nodari'@'67.205.0.0/255.255.192.0' to database 'mysql'
我不是root ...
I'm not root...
dreamhost支持人员坚持说，问题出在我的头上.但是他说他将运行我告诉他的任何查询，因为它是一台私有服务器. 因此，我需要准确地告诉这个人要运行什么. 所以，告诉他跑步
The guy at dreamhost support insist saying thet the problem is at my end. But he said he will run any query I tell him since it's a private server. So, I need to tell this guy EXACTLY what to run. So, telling him to run
SET SESSION old_passwords=0;
SET GLOBAL old_passwords=0;
SET PASSWORD FOR 'nodari'@'HOSTNAME' = PASSWORD('new password');
grant all privileges on *.* to nodari@HOSTNAME identified by 'new password';
会是一个好的开始吗?
推荐答案
是的，看起来像个强悍的人.没有主机的合作，也没有更改密码格式或客户端库的能力，您将没有太多选择.
Yeah, that looks like a toughie. Without cooperation from your hosts or the ability to change password formats or client libraries, you don't have a lot of options.
老实说，我的首选是抛弃Dreamhost.可能需要做很多工作，但是如果他们要使用旧的不兼容的东西卡住，它将仍然是有问题的.
Honestly, my first choice would be to ditch Dreamhost. That's probably a lot of work, but if they're going to be stuck using old incompatible stuff, it will continue to be problematic.
如果这不是一种选择，那么联合自动化流程又如何呢?您可以将Slicehost一侧的数据导出到CSV文件中，然后将其压缩为Dreamhost所需的任何格式，然后将其上传到Dreamhost服务器.您可以让Dreamhost服务器上的cron脚本定期检查上传的文件并进行处理(确保在成功处理后将其移动或删除).
If that's not an option, what about a joint automated process? You could export the data on the Slicehost side into a CSV file and massage it into whatever format is necessary for Dreamhost, and then upload it to the Dreamhost server. You could have a cron script on the Dreamhost server check periodically for the uploaded file and process it (making sure to move or delete it after it was successfully processed).
这篇关于MySQL密码哈希方法旧与新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
