Skip to content

database

How To Do A Search & Replace On Your Website’s MySQL Database

For year’s I’ve used the WordPress Plugin SyntaxHighlighter to display code on my site.  Yesterday I ran into a huge problem. I wanted to change the way code is displayed on my site, which required changing hundreds of tags. SytaxHighlighter uses bash tags in shortcode format, but I needed to change all of those to standard pre tags. Not a fun night!

Every plugin a tried to do a search and replace didn’t work. I’m pretty sure the plugins weren’t able to handle the special characters properly. I hosed my database a few times along the way. Everything from screwed up Primary Key’s to bizarre Auto Increment problems. In the end, simplicity prevailed.

 

Quick Tip:  Don’t forget to create a backup of your database before making changes.  Just click the “Export” tab and select “Go!”

How To Search & Replace In MySQL

 

Most every website these days has PHPmyAdmin installed.  If not, it’s easy to do so (I’ll write a post on that soon). This is precisely the tool to do this job quickly and correctly.

Go ahead and log into PHPmyAdmin and select your database in the left-hand column.  Then select “SQL,” which is located in the top row of buttons.

Click Here To Continue Reading!

MailCleaner Spam Filter – How To Open a Port & Add IPTables Firewall Rules

MailCleaner is a nice Open Source Linux distribution that creates a spam filter appliance. It is designed to sit in between an email server and the internet and filter spam out of email using advanced rules, DNS RBL (realtime black list), and many other techniques. It also scans email for viruses. Although I no longer use MailCleaner (I have replaced it with ScrollOut F1), I remember coming across a big issue in the past that took me some time to figure out, so I thought I would share it.

Because MailCleaner is more or less an appliance, most aspects of the operating system are controlled by MailCleaner. A majority of the settings you need to change are easily available on the web interface, however firewall rules are not. MailCleaner is designed so that it manages all IPTables rules. If you manually add an IPTables rule from the command line, once it’s reloaded or the system is reboot, the rule is gone. That is because MailCleaner wipes out and reloads IPTables rules from data stored in its MySQL database. So, in order to open any additional ports, you must modify the database. I encountered this dilemma when I installed a remote monitoring client (the Nagios based Check_MK to be exact), and needed to open a port to allow the monitoring server to connect.

Lets assume I need to open up SSH (port 22) and RSYNC (port 873) and I only want my mail server’s IP, 1.2.3.4, to connect. Normally we would enter the following iptables commands:

sudo iptables -A INPUT -s 1.2.3.4/32 -p tcp -m tcp --dport 873 -j ACCEPT
sudo iptables -A INPUT -s 1.2.3.4/32 -p tcp -m tcp --dport 22 -j ACCEPT

But in this case, we cannot. The good news is the MailCleaner will do it for you if you add the correct info into the MySQL database. Here’s how you do that (from a command prompt on the MailCleaner server):

Click Here To Read The Entire Tutorial!