I have a Wordpress-based log, hosted on Dreamhost. I'm on Snow Leopard and I wanted to install local site, that will connect to my remote database. However, this does not work and I get this error:
mysqlnd cannot connect to MySQL 4.1+ using old authentication
I did not try to do this in Leopard, so I don't is it new thing in SL or was an error in Leopard as well.
How to solve/workaround this?
Do you know for sure that this is even possible with Dreamhost? A lot of hosting companies only allow local applications to connect to hosted mysql. You might try
telnet thehost 3306
and see if you get Connection refused. If you do, then you will need ssh access and a tunnel, or some other sort of VPN solution.
As DigitalRoss said it's very likely that Dreamhost won't allow remote connections as it is a huge security risk to expose a database over the internet that way.
I would recommend instead that you put some kind of script on your Dreamhost space to fetch the data and call the script from your local host if you really need to do this.
Two ways of doing that is either through SOAP or a httprequest. I find SOAP to be the better solution though if possible, as remote data transports are one of the things webservices was ment for.
I agree that your issue does not really have to do with snow leopard but with Dreamhost. I would be very very surprised if they let you do what you are intending. What you can do if you are trying to testing things out on your local server is setup a backup on your wordpress installation. I use the WordPress Database Backup to backup the database of by blogs weekly and email me the backup.
I have resolved this. The issue indeed was due to Snow Leopard's default of using mysqlnd as the module for PHP 5.3 it ships with, not with Dreamhost. That module flatly refuses to connect with servers using OLD_PASSWORD hash.
The only solution to this (as I can't change MySQL settings on shared hosting) is to recompile your own PHP, that will not use mysqlnd but mysql/mysqli. Took me 3h to figure out the correct ./configure for it to work (I'm far from fluent in this area, so it was hit and miss). Searching through the net, various options worked for various people. No idea will this work or others, it did worked for me like this:
Get libpng and compile, install into /usr/local/libpng
Compile latest MySQL, per hivelogic.com instructions
Set to use 64bit
export MACOSX_DEPLOYMENT_TARGET=10.6 \
CFLAGS="-arch x86_64" \
CXXFLAGS="-arch x86_64"
Use this for configure PHP (I used 5.2.11, does not really matters to me as long as it's 5.x)
./configure --prefix=/usr/local/php5 \ --mandir=/usr/share/man \ --infodir=/usr/share/info \ --sysconfdir=/private/etc \ --with-apxs2=/usr/sbin/apxs \ --enable-cli \ --with-libxml-dir=/usr \ --with-openssl=/usr \ --with-kerberos=/usr \ --with-zlib=/usr \ --enable-bcmath \ --with-bz2=/usr \ --enable-calendar \ --with-curl=/usr \ --enable-exif \ --enable-ftp \ --with-gd \ --with-jpeg-dir=/usr/local/libjpeg \ --with-png-dir=/usr/local/libpng \ --enable-gd-native-ttf \ --with-ldap=/usr \ --with-ldap-sasl=/usr \ --enable-mbstring \ --enable-mbregex \ --with-pdo-mysql=/usr/local/mysql \ --with-mysql=/usr/local/mysql \ --with-mysqli=/usr/local/mysql/bin/mysql_config \ --with-mysql-sock=/tmp/mysql.sock \ --with-iodbc=/usr \ --enable-shmop \ --with-snmp=/usr \ --enable-soap \ --enable-sockets \ --enable-sysvmsg \ --enable-sysvsem \ --enable-sysvshm \ --with-xmlrpc \ --without-iconv \ --with-xsl=/usr
Again, I'm rookie with all this, need PHP just to run local wordpress, so use at your own risk.