Databases MySQL programming Quick Tips servers sql

Finding Duplicates using SQL

While migrating old data to a new database schema I was unable to activate a PRIMARY KEY on the legacy_customer_id field because there were duplicates. It turned out that the old application did not clean the data really well, and it allowed duplicates to be created where one of the customer_ids had a space character making it unique.

I used the following query to test for others:

    (COUNT(customer_id) > 1);

This allowed me to find all customer_ids that had duplicates and clean things up.

Backup Cron Disaster Recovery linux MySQL OS Quick Tips

Linux backup using CRON to local directory

As many have pointed out I am on a backup and disaster recovery kick lately. Some would say that it is about time, others are simply glad to see that data is now being backed up. I have found that it is easiest to zip up files on a local machine prior to moving them to a final destination. So lets get started:

I have multiple Linux servers with many websites on each, as well as database. So I created a script that simply tar’s the files, then gzips them with the date in the filename for archiving.

Here is the file named ‘’ that I save in a place reachable by the user I will use to schedule this cronjob:

echo "############### Backing up files on the system... ###############"
backupfilename=server_file_backup_`date '+%Y-%m-%d'`
echo "----- First do the sql by deleting the old file and dumping the current data -----"
rm -f /tmp/backup.sql
mysqldump --user=mysqluser --password=password --all-databases --add-drop-table > /tmp/backup.sql
echo "----- Now tar, then zip up all files to be saved -----"
tar cvf /directory/to/store/file/${backupfilename}.tar /home/* /var/www/html/* /usr/local/svn/* /etc/php.ini /etc/httpd/conf/httpd.conf /tmp/backup.sql /var/trac/*
gzip /directory/to/store/file/${backupfilename}.tar
rm /directory/to/store/file/${backupfilename}.tar
chmod 666 /directory/to/store/file/${backupfilename}.tar.gz
echo "############### Completed backing up system... ###############"
apache Databases linux MySQL OS Quick Tips servers

Get SSL running on Apache (CentOS)

I was playing with a new virtual server that had CentOS installed on it recently, and wanted to get SSL working for Apache.  Since I was only setting up a development server I really didn’t need to purchase a certificate and decided to use a self-signed certificate.  Here is what I did:

First I needed to get ‘make’ and ‘mod_ssl’ running to allow for this. (I use sudo but you could login as su)

sudo yum install make
sudo yum install mod_ssl

Next I did the following steps:

  1. Go to /etc/pki/tls/certs
  2. Run the command sudo make mycert.pem
  3. Enter the information you are prompted for about country, state, city, host name etc, your certificate and key has been created
  4. Now edit /etc/httpd/conf.d/ssl.conf and update the following items:
    • SSLCACertificateFile /etc/pki/tls/certs/mycert.pem
    • SSLCACertificateKeyFile /etc/pki/tls/mycert.pem
  5. I was forced to create a symbolic link for the SSLCACertificateKeyFile as follows: (I think this was supposed to happen automagically.)
    • I went to /etc/pki/tls and created the symbolic link using the next line.
    • sudo ln -s certs/mycert.pem mycert.pem
  6. Restart Apache (/etc/init.d/httpd restart)

There, now you have a self-signed certificate for your apache virtualhosts.

Databases MySQL OS programming Ubuntu

MySQL not creating mysql.sock and broken on Hardy Heron

Recently I started receiving errors when I tried to connect to MySQL using command line or PHPMyAdmin. In command line I would get “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)”, and with PHPMyAdmin I would get “#2002 – The server is not responding (or the local MySQL server’s socket is not correctly configured)”.

The system in question is my Dell Inspiron 1720 running Hardy Heron Ubuntu. I knew of a few changes to my system, but none of them seemed to have caused the problem. So I hunted for a few days trying to figure it out. I performed multiple searches on the net, and each led me to a dead end. Many said, “Set this … in your php.ini” or “Set that … in your my.cnf”, and some even said Apache was to blame. However, I found the solution to be very simple.

MySQL was expecting the mysql.sock to be located in ‘/tmp/mysql.sock’. However, for some reason it had moved or the symbolic link to it’s actual location was deleted by some update or install I did recently.

I fixed the problem by adding a symbolic link to the actual home of mysqld.sock, which was /var/run/mysqld/mysqld.sock. Here is how I created the symbolic link: (at the command line)

sudo ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock

sudo puts us in super user mode, “ln -s” creates a symbolic link followed by the target the links sould point to and last by the location of the link.

Edited on Sep. 26th with update below:

The fix above did not work as a permanent fix. I found that whenever I rebooted the OS it forced me to recreate the symbolic link. MySQL was failing to recreate the symbolic link on it’s own. To permanently fix the issue I needed to add the creation of the symbolic link to my SESSION startup. Here is how I did that:

By going to the System->Preferences->Sessions to edit the Startup Programs. I added an item that automatically issues the command above, but without the “sudo”.

Mission accomplished…permanently.

Databases linux MySQL networking OS php programming Quick Tips

SSH port forwarding/tunneling for MySQL connection

Create an account on the remote MySQL database server.

useradd -s /bin/false remote_user
mkdir /home/remote_user/.ssh
touch /home/remote_user/.ssh/authorized_keys
chown -R remote_user:remote_user /home/remote_user/.ssh
chmod 755 /home/remote_user/.ssh
chmod 600 /home/remote_user/.ssh/authorized_keys

Add MySQL permissions in the remote MySQL database to allow user connections from localhost.

USE mysql;
GRANT ALL ON db.* TO [email protected].0.0.1 IDENTIFIED BY 'database_pass';

Now, on the local server (as root) create an RSA key pair to avoide the need for passwords for remote_user. (Simply hit enter for each question encountered.)

ssh-keygen -t rsa

Now transfer the public key file to the remote server from your local server.

scp /var/root/.ssh/
cat /tmp/ >> /home/remote_user/.ssh/authorized_keys

On the local server, create an SSH tunnel to the remote MySQL database server using the following command.

ssh -fNg -L 3306: sleep 9999

To use this from PHP you would simply do this:

$remote_server_mysql = mysql_connect( "", "database_user", "database_pass" );
mysql_select_db( "database", $remote_server_mysql );