Friday, February 27, 2015

How to update a MySQL table with data from itself

Suppose I want to update a MySQL table with data that refers to the same table. To make it easier to follow along, I will use the following actual WordPress administration scenario.

My task is to copy the attributes of a WordPress theme named decode to its child theme named decode-child. Theme attributes are stored in the MySQL table wp_options.

The table has 2 relevant fields:

  1. option_name

    This field identifies the theme. The parent theme has the value theme_mods_decode; the child theme, theme_mods_decode-child.

  2. option_value

    This is the field we want to copy from the parent theme to its child.

In essence, I want to copy the option_value of the record with option_name equals theme_mods_decode to the record with option_name equals theme_mods_decode-child.

We will use a subquery to accomplish the task. A subquery is a SELECT SQL statement within another SQL statement, say UPDATE.

My first subquery attempt resulted in an error:

update wp_options set option_value = 
  (select option_value from wp_options where option_name = 'theme_mods_decode') 
where option_value = 'theme_mods_decode-child'; 
ERROR 1093 (HY000): You can't specify target table 'wp_options' for update in FROM clause

MySQL does not allow the FROM clause of a subquery to refer to the same table targeted for update.

To get around the above error, replace the table reference wp_options in the original subquery with a nested subquery (select * from wp_options):

update wp_options set option_value =   
  (select option_value from   
    (select * from wp_options) as x  
   where option_name = 'theme_mods_decode')   
where option_name = 'theme_mods_decode-child';   
Query OK, 1 row affected (0.05 sec)   
Rows matched: 1  Changed: 1  Warnings: 0   

The above solution may look like a hack to some of us. There may even be other solutions, perhaps using complex joins and unions. But I like the above approach because it is simple, both conceptually and syntactically.

Friday, February 20, 2015

What to do next after spinning up a Debian VPS

Recently, I opened an unmanaged VPS hosting account with Digital Ocean (of which I am an affiliate), and created a barebone Debian virtual server. Because the hosting was unmanaged, I had to apply all the system changes to the machine myself. Below were the steps I took to set up the Debian virtual machine after spinning it up for the first time.

I assume that you want to set up the LAMP stack - Linux, Apache, MYSQL, PHP - on your machine.

  1. SSH into the virtual machine.
    Before you can login, you need to know the public IP address of the new machine. Digital Ocean sends it to you in an email.

    $ ssh root@your.IP.address

  2. Change root password.
    When you first login to the Digital Ocean server, you are automatically prompted to enter a new password. If your VPS is with someone else, change the password with the following command.

    # passwd

  3. Get to know your system environment.

    • Which Linux release?

      # lsb_release -a

    • How much memory, and swap space?

      # free -h

                  total used free shared buffers cached   
      Mem:         497M  64M 432M     0B    7.3M    33M     
      -/+ buffers/cache: 23M 473M     
      Swap:          0B   0B   0B

      Note: No swap space was pre-allocated.

    • How much disk space?

      # df -h

    • Is DNS configured?

      # cat /etc/resolv.conf
      nameserver 8.8.4.4
      nameserver 8.8.8.8
      nameserver 209.244.0.3

    • Which repositories?

      # cat /etc/apt/sources.list
      deb http://mirrors.digitalocean.com/debian wheezy main
      deb http://security.debian.org/ wheezy/updates main

  4. Update Debian.

    # apt-get update
    # apt-get upgrade

  5. Configure correct timezone.

    Run the date command to verify the current time. If the time looks wrong, your machine may be pre-configured to the wrong timezone.

    # date Thu Dec 4 00:07:37 UTC 2014

    The current time reported was in the UTC timezone. But, I live in Vancouver, Canada which is in the Pacific timezone. To change the timezone, write the proper time zone string to the file /etc/timezone, and run dpkg-reconfigure.

    # echo America/Vancouver > /etc/timezone
    # dpkg-reconfigure -f noninteractive tzdata
    Current default time zone: 'America/Vancouver'
    Local time is now: Wed Dec 3 16:08:58 PST 2014.
    Universal Time is now: Thu Dec 4 00:08:58 UTC 2014.

    Now, if you run date, you should see the correct time in the local timezone.

    # date
    Wed Dec 3 16:09:01 PST 2014

    Refer to my earlier post for more details.

  6. Customize default text editor.

    If you are not used to a text editor, it can drive you crazy. So, I change the default text editor from nano to vim. You will thank me when you run visudo in the next step.

    Note: my first choice for text editor is emacs, but emacs is not installed by default. Sigh.

    # update-alternatives --config editor

    Select /usr/bin/vim.tiny.

  7. Add new sudo user.

    Soon, I'll configure SSH to prevent direct root login. Before that can happen, a new user is needed.

    First, create a new user as follows:

    # adduser peter

    Then, grant the new user sudo privileges.

    # visudo

    Insert the following line in the User privilege specification section.

    peter ALL=(ALL:ALL) ALL

  8. Tighten up SSH security.

    • Disallow root login.

    # sed -i -e "s/^PermitRootLogin yes/PermitRootLogin no/" /etc/ssh/sshd_config

    • Change SSH port number from port 22 to port 2121.

    # sed -i -e "s/^Port 22/Port 2121/" /etc/ssh/sshd_config

    • Restart the SSH daemon.

    # service ssh restart

  9. Build up firewall.

    Run iptables to see how your firewall is currently configured:

    # iptables -L   
    Chain INPUT (policy ACCEPT)
    target  prot opt source   destination
    
    
    Chain FORWARD (policy ACCEPT) 
    target  prot opt source   destination
    
    
    Chain OUTPUT (policy ACCEPT)   
    target  prot opt source   destination

    Note: The firewall is set up by default to accept everything.

    To build a minimal firewall that allows incoming web, SSH and ping traffic, issue the following commands in the specified order:

    # iptables -I INPUT 1 -m conntrack --ctstate ESTABLISHED,RELATED -j ACCEPT
    # iptables -I INPUT 2 -i lo -j ACCEPT
    # iptables -I INPUT 3 -m conntrack --ctstate NEW -p tcp --syn --dport 80 --j ACCEPT
    # iptables -I INPUT 4 -m conntrack --ctstate NEW -p tcp --syn --dport 2121 --j ACCEPT
    # iptables -I INPUT 5 -p icmp --icmp-type echo-request -m limit --limit 2/second -j ACCEPT
    # iptables -P INPUT DROP
    # iptables -P FORWARD DROP

    Inspect the new firewall using the following command:

    # iptables -L -v

    Refer to my earlier post for more details on how to set up the firewall.

  10. Save firewall configuration.

    The new firewall configuration does not persist in the event of a system reboot. To save the firewall permanently, you must first install the iptables-persistent package.

    # apt-get install iptables-persistent

    The install program will ask if you want to save the current `iptables` rules. Answer yes.

    If you make more firewall changes after installing the above package, you need to explicitly save the rules using the command below:

    # iptables-save > /etc/iptables/rules.v4

  11. Deploy fail2ban.

    fail2ban scans system log files for signs of a cyber attack. Upon the detection of a possible attack, it bans the originating suspect IP address for a specified amount of time.

    • Install fail2ban.

      # apt-get install fail2ban

    • Configure fail2ban.

      # cat > /etc/fail2ban/jail.local
      [DEFAULT]
      ignoreip = 127.0.0.1/8
      bantime = 3600
      maxretry = 3
      [ssh]
      maxretry = 3
      port = 2121

      bantime specifies the ban duration in seconds. maxretry specifies the threshold for triggering the ban. port specifies the SSH port number.

    • Restart fail2ban.

      # service fail2ban restart

  12. Create swap file.

    My VPS hosting plan with Digital Ocean gives me 512 MB of RAM and 20 GB in SSD disk space. While RAM is limited in that configuration, the virtual server has plenty of unused disk space. The extra disk space can be utilized to boost the available virtual memory. Follow the steps below to create the swap file /var/swap.img.

    # fallocate -l 500M /var/swap.img
    # chmod 600 /var/swap.img
    # mkswap /var/swap.img
    # swapon /var/swap.img
    # echo "/var/swap.img none swap sw 0 0" >> /etc/fstab

    Refer to my earlier post for more details.

  13. Set up SMTP server to redirect root emails.

    You run the risk of ignoring important system alerts if emails to root are not read regularly. Follow the steps below to forward all root emails to an email address that you actually monitor.

    • Configure exim4 to redirect all outbound emails to the Google Gmail SMTP server.

      You need a Gmail account in order to use Gmail SMTP. Refer to my earlier post for more details.

    # hostname --fqdn > /etc/mailname
    The /etc/mailname file should contain the fully-qualified domain name to use for outgoing mail messages. The Sender email address of a message uses this value as its domain name.

    # sed -i -e "s/dc_eximconfig_configtype=.*/dc_eximconfig_configtype='smarthost'/" /etc/exim4/update-exim4.conf.conf
    smarthost is the configuration type you want.

    # sed -i -e "s/dc_smarthost=.*/dc_smarthost='smtp.gmail.com::587'/" /etc/exim4/update-exim4.conf.conf
    If you have a non-Gmail SMTP server, replace smtp.gmail.com with the proper domain name.

    # sed -i -e "s/dc_hide_mailname=.*/dc_hide_mailname='false'/" /etc/exim4/update-exim4.conf.conf

    # sed -i -e "s/dc_other_hostnames=.*/dc_other_hostnames=''/" /etc/exim4/update-exim4.conf.conf

    # echo '*.google.com:yourAccount@gmail.com:yourPassword' >> /etc/exim4/passwd.client
    Customize the above with your own Gmail account and password. If you are not using Gmail, replace google.com with the proper domain name.

    # update-exim4.conf

    # service exim4 restart

    • Redirect root emails to a valid email address.

      Refer to an earlier post for more details.

    # echo -e 'root: peter\npeter: yourEmailAddress@somedomain.com' >> /etc/aliases
    The above command inserts 2 lines into /etc/aliases. The first line (root: peter) specifies that all emails to root are forwarded to peter, a new user we added earlier. The second line (peter: yourEmailAddress@somedomain.com) specifies that all emails to peter are redirected to the external email address.

    # newaliases
    Run newaliases to rebuild the email aliases database.

  14. Note which essential packages are still uninstalled.

    My yet-to-install package list comprises:

    • chkconfig
    • MYSQL
    • Apache
    • PHP5

    You can use the following command to find out if a package, say PHP5, is installed:

    # dpkg -l php5*
    dpkg-query: no packages found matching php5*

  15. Install missing packages.

    • chkconfig

      # apt-get install chkconfig

    • MYSQL

      # apt-get install mysql-server mysql-client

      After installing MYSQL, run the mysql_secure_installation program to tighten up security.

      # mysql_secure_installation

    • Apache

      # apt-get install apache2

    • PHP5

      # apt-get install php5 php5-mysql php5-gd php5-curl

    For further details on how to install the above packages, refer to my earlier post.

After you put in all the hard work to get this far, your machine is finally in a usable state. It is wise (albeit optional) to save the current machine state. Digital Ocean allows you to create a system snapshot of your server. You can restore the server to that particular state at any time in the future.

When you login to the virtual server via SSH, don't forget to use the new user and port number.

$ ssh -p 2121 peter@your.ip.address

Tuesday, February 10, 2015

How to run multiple simultaneous X Window sessions

If you share your Linux desktop machine with other users, you may occasionally need to switch from 1 X session to another. For example, you are busy writing code or blogging, and your spouse walks up to you and asks to 'quickly' check her email. You can log out so that she can login using her own account. But, the current context of your work is lost, and you need to re-establish it when you resume.

A better way is to have her run a second simultaneous X session. The steps are as follows:

  1. Switch to a virtual terminal.
    By default, six virtual text terminals are available to you (Ctrl+Alt+F1 to F6). Press Control+Alt+F1 to go to virtual terminal 1.

  2. Login as her.

  3. Execute the following command:

    $ startx -- :1

    This starts another X session using the first free graphical console. By default, 6 X consoles are available (Ctrl+Alt+F7 to F12). Your own existing X session is Ctrl+Alt+F7. The next free X console is therefore Ctrl+Alt+F8.

    To switch from her X session to yours, and vice versa, press Ctrl+Alt+F7 and Ctrl+Alt+F8 respectively.

You may use the above procedure to create up to 5 additional X sessions (Ctrl+Alt+F8 to Ctrl+Alt+F12). For each additional X session, increment the console number in both step 1 and 3. For instance, switch to virtual terminal 2 (Control+Alt+F2) to execute the command startx -- :2.

Sunday, February 1, 2015

Learning command arguments using ExplainShell.com

Suppose you come across the following command in a script. You are not quite sure what it does. Specifically, you want to know the meaning of each command argument.

$ wget --spider -r -nd -nv -H -l 1 -w 2 -o test.log http://example.com/

Traditionally, to find out what each argument does, you look up the man page for the wget command. Then, you scroll through page after page of information, and extract the description for each argument. This is a time-consuming, and eye-straining exercise.

Thanks to the Explainshell.com website. Now, you simply enter the above command on that website, and click a button.

The output clearly displays a description of each specified argument from the man page.

One caveat is that the information from Explainshell.com is sourced from the Ubuntu man pages. So, it is great for commands that you often run on Ubuntu or Debian systems. But, you won't find anything for Fedora/Centos/RedHat-specific commands such as yum.

Even with that limitation, Explainshell.com is a great learning tool for Linux command-line users. If you are interested in the inner workings of Explainshell.com, click this link.