Magento can't connect MySQL

Question

After someone messed up the server, Magento could not connect MySql DB.
First try, I used mysql -u <username> -h localhost -p and failed to authenticate.
After a lot of struggle this guy helped me (the solution is in the comments), so I finally succeeded connecting to the DB using Magento's credentials. But then I couldn't connect remotely, this one didn't help since --skip-networking disables remote connection, but I finally figured it out as well (now I don't remember what I did, either changed something in my.cnf or /etc/hosts).

So now I can connect with Magento username/password (configured in configuration.php) both locally and remotely.

Still, Magento prints to screen errors that it can't connect MySql.
I checked both local.xml and config.xml (under <Magento root>/app/etc) and both seems to be configured correctly.

I started thinking about installing the whole thing from scratch, the problem is that there isn't any good backup and I'm not sure what/if I'm going to loose data by doing that, but if I'll have to, I'll backup the files+DB and go for it...
Any ideas ?

---

UPDATE

After endless digging, apparently there were other XML files in the same directory with local.xml and config.xml. Removing these files (which were created as backups, but were left with the .xml extension) the problem was solved.

Conclusion: if you backup xml files, save the backup as file.xml.backup so it won't be treated the same as a file with an xml extension!

Answer 1

If you're thinking about reinstalling the whole thing, may I, as a foreword, advise to do that on a different server than the messed-up one - just in order to keep data on the old one in case things turn bad. You may also want to do that on the same server but with a different vhost, home folder and mysql database.

Here is the procedure I use when making Magento project migrations, imports and other stuff related to Magento moves from one server to another.

This requires that you can access mysql + mysqldump from the shell.

This is a procedure I use regularly on Debian based distros with LAMP.

---

On source server

1. Clean the BD

This is necessary if you consider that your DB is to heavy to be downloaded from your new destination server. Also, please make sure that you really know which tables you are truncating. I cannot tell which precisely as this depends on your Magento version.

Roughly, truncate index tables + core_url_rewrite, log tables, cron_schedule, flat catalog tables, dataflow batch tables and profile history, reports aggregation tables.

2. Backup the DB

mysqldump -h [host] -u [user] -p'[password]' [dbname] > magento.sql

3. Clean your Magento filesystem

From you Magento root folder:

rm -rf var/session/* && rm -rf var/cache/* && rm -rf var/log/*

4. Archive your Magento filesystem

From your Magento root folder:

tar -zcvf magento.tar.gz .

---

On the destination server

Retrieve your magento.sql and magento.tar.gz any way you like (wget, copy/paste from SSH GUI client...) and put them in your new Magento root directory.

5. Import your DB

mysql -h [your_host] -u [user] -p'[password]' [dbname]

That will open the mysql shell on your new DB

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> source /full/path/to/magento.sql
...
mysql> SET FOREIGN_KEY_CHECKS = 1;

6. Extract your magento.tar.gz

From your new Magento root directory

tar -zxvf magento.tar.gz

You should now be able to see your site. Some permissions modification and a fine tuning of app/etc/local.xml may be needed to make it fit to your destination server MySql configuration.

Answer 2

Try to flush cache from backend or delete /var/cache/*