I had to update a link for a client today and most of their content is database driven. This makes it easy to do quick searches and even search and replace. Most databases support this and the syntax is pretty close, but this tip is specifically for MySQL.

So, lets say you have an affiliate link that looks like this:

http://www.domain.com/aff.php?affid=1234

But you get an email from your affiliate manager telling you to update your links to a new format like this:

http://affiliates.domain.com/offer/1234

MySQL will make that easy.

First, lets check to see how many of our records have that url:

SELECT * FROM table_name WHERE field_name like ‘%http://www.domain.com/aff.php?affid=1234%’

In plain English, that means, select everything from the table named ‘table_name’ where the filed named ‘field_name’ is like the url. The % signs around the url are “wildcards”.

Lets say it returned 189 records. Now lets do our replace

UPDATE table_name SET field_name = REPLACE(field_name, “http://www.domain.com/aff.php?affid=”, “http://affiliates.domain.com/offer/”) WHERE field_name like ‘%http://www.domain.com/aff.php?affid=1234%’

This query is a little more complex, but still pretty straight forward. You should see 189 records updated. You don’t have to use the where clause in this query, but I do simply because it speeds things up and it’s a good habit to always use a where clause unless you really need for your query to look at or check every record. In that query, inside the replace, the first field is the OLD string that you want to replace and the second one is the NEW string you want to replace it with. Just like search and replace using your favorite text editor.

Technorati Tags: MySQL, Search, Replace

Today I was doing some database work for a client and I had to delete some recent entries from a database. Like a moron, I forgot to add a “where” close and emptied the entire thing! No big deal, I’m the one who wrote the backup script so I went to grab the backup for yesterday. Yup, you know it. There wasn’t one. The backup stopped working back on July 3rd! Ouch. That burns. Luckily the host is also a good friend of mine and I shot him an email asking if he had a recent backup of the raw MySQL files. I knew that he did regular backups of the entire server so the raw files should be there. I don’t count on that, you should never count on anyone elses backups, but in this case it saved my bacon. Gary had the backup and gave me the quick instructions on restoring them, which I’ll share with you:

Get a copy of your raw mysql file. In my case, I didn’t want the whole database, I wanted one table from one database. Luckily MySQL stores each table in a 3 files called:

database_table.MYI
database_table.MYD
database_table.frm

So, once I located them - Gary sent them to me - I just needed to restore them. It’s pretty easy.

1. Stop mysql
2. cp database_table.* /var/lib/mysql/
That’s the typical path, but of course it may vary from host to host.
3. Restart mysql

And if everything went well (it usually does) you’re back in business.

Caveat: This won’t work if your database type is innodb. Ugh. The disturbing thing to me is that my automated backup stopped running for some reason. It looks like the process was killed at some point and never restarted. You should add “check that backups are working” to your weekly, or at a minimum, monthly checklist. Never ever count on your host or someone else to do it. Murphy’s laws are still very much alive and in effect.

Technorati Tags: MySQL, Backup, Restore, Disaster