Tag Archives: mysql

Monitor your servers with phpservermonitor

I have a handful of servers and for years I’ve been wanting to get some sort of monitoring in place. Today I tried out php server monitor and found it was pretty easy to set up and use.

Download

The installation process was pretty straightforward.

  • Install PHP, mysql, and apache
  • Create database, user, password, and access rights for mysql
  • Download .tar.gz and extract to /var/www
  • Configure Apache site file to point to phpservermonitor directory
  • Navigate to the IP / URL of your apache server and run the installation script

The above process is documented fairly well on their website. I configured this to run on my Raspberry Pi 2. This was my process:

Install dependencies:

sudo apt-get install php5 php5-curl php5-mysql mysql-server

Configure mysql:

sudo mysql_secure_installation

Create database:

mysql -u root -p
create database phpservermon;
create user 'phpservermon'@'localhost' IDENTIFIED BY 'password';
 grant all privileges on phpservermon.* TO 'phpservermon'@'localhost'; 
flush privileges;

Extract phpservermon to /var/www and grant permissions

tar zxvf <phpservermon_gzip_filename> -C /var/www
sudo chown -R www-data /var/www/*

Configure php:

sudo vim /etc/php5/apache2/php.ini
#uncomment date.timezone and set your timezone
date.timezone = "America/Boise"

Configure apache:

sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/phpservermon

#Modify /etc/apache2/sites-available/phpservermon server root to point to directory above, also add a ServerName if desired

sudo a2ensite phpservermon
sudo service apache2 reload

Configure cron (I have it check every minute but you can configure whatever you like)

*/1 * * * * /usr/bin/php /var/www/phpservermon/cron/status.cron.php

Navigate to the web address you’ve configured in apache and follow the wizard.

It’s pretty simple but it works! A nice php application to monitor websites and services.

 

Script to change WordPress URL

I wrote up a little script to run when you migrate a wordpress installation from one host to another (hostname change.)  Once this script is run you can access the site via the hostname of the server it’s running on and then change the URL to whatever you like. This comes in handy for when you want to migrate one internal host to another, then specify an external hostname once things are looking how you like them.

Change SQL_COMMAND to reflect the name of the wordpress table in the destination server. Thanks to this site for the guidance in writing the script.

#!/bin/bash

#A simple script to update the wordpress database to reflect a change in hostname
#Run this after changing the hostname / IP of a wordpress server

#Prompt for mysql root password
read -s -p "Enter mysql root password: " SQL_PASSWORD

SQL_COMMAND="mysql -u root -p$SQL_PASSWORD wordpress -e"

#Determine what the old URL was and save to variable
OLD_URL=$(mysql -u root -p$SQL_PASSWORD wordpress -e 'select option_value from wp_options where option_id = 1;' | grep http)
#Get current hostname
HOST=$(hostname)

#SQL statements to update database to new hostname
$SQL_COMMAND "UPDATE wp_options SET option_value = replace(option_value, '$OLD_URL', 'http://$HOST') WHERE option_name = 'home' OR option_name = 'siteurl';"
$SQL_COMMAND "UPDATE wp_posts SET guid = replace(guid, '$OLD_URL','http://$HOST');"
$SQL_COMMAND "UPDATE wp_posts SET post_content = replace(post_content, '$OLD_URL', 'http://$HOST');"
$SQL_COMMAND "UPDATE wp_postmeta SET meta_value = replace(meta_value,'$OLD_URL','http://$HOST');"

Install WordPress on CentOS 7 with SELinux

I’ve been frustrated lately with the number of tutorials for installing WordPress on CentOS 7 that say to simply turn SELinux off. This is an unacceptable workaround. It’s simply lazy administration. SELinux is not designed to make your job harder (although it can do that pretty well), it’s there to make your system safer.

I have scoured the internet and pieced together bits of information from several sources – mainly here, here, and here to put together a tutorial that walks you through how to install WordPress in CentOS7 while keeping SELinux turned on.

It took a lot of reading to understand SELinux, but once you understand it, it makes a whole lot more sense. Go figure.

Apache

Install required packages:

sudo yum -y install httpd
sudo systemctl enable httpd

Modify apache config to allow mod_rewrite:

sudo sed -i /etc/httpd/conf/httpd.conf -e 's/AllowOverride None/AllowOverride All/g'

Open necessary firewall ports:

sudo firewall-cmd --add-service=http --permanent
sudo systemctl restart firewalld

Start apache:

sudo systemctl start httpd

Navigate to your new site to make sure the testing page comes up.

Update 11/4/2016: If you are behind a reverse proxy such as varnish or a web application firewall, you will want to modify your apache configuration to log x-forwarded-for IPs to make the logs more meaningful:

sudo sed -i /etc/httpd/conf/httpd.conf -e 's/%h/%{X-Forwarded-For}i/g'

MariaDB

Install:

sudo yum -y install mariadb-server mariadb
sudo systemctl enable mariadb

Run initial mysql configuration to set database root password

sudo systemctl start mariadb
sudo mysql_secure_installation

Create a wordpress database and user:

mysql -u root -p 
#enter your mysql root password here
create user wordpress;
create database wordpress;
GRANT ALL PRIVILEGES ON wordpress.* To 'wordpress'@'localhost' IDENTIFIED BY 'password';
quit;

WordPress

Install PHP and restart apache

sudo yum -y install php php-mysql php-gd php-ldap php-odbc php-pear php-xml php-xmlrpc php-mbstring php-snmp php-soap curl
sudo systemctl restart httpd

Configure base wordpress directory

Download, extract, and set permissions for your wordpress installation:

wget https://wordpress.org/latest.zip
sudo unzip latest.zip -d /var/www/html
sudo chown apache:apache -R /var/www/html/wordpress

Optional: Change Apache document root so you don’t need to tack /wordpress at the end of the url:

sudo sed -i /etc/httpd/conf/httpd.conf -e 's/DocumentRoot \"\/var\/www\/html/&\/wordpress/g'
sudo systemctl restart httpd

Configure upload directory

If you want users to upload content, then you will want to assign the http_sys_rw_content_t selinux security context for the wp-uploads directory (create it if it doesn’t exist)

sudo mkdir /var/www/html/wordpress/wp-content/uploads
sudo chown apache:apache /var/www/html/wordpress/wp-content/uploads
sudo semanage fcontext -a -t httpd_sys_rw_content_t "/var/www/html/wordpress/wp-content/uploads(/.*)?"
sudo restorecon -Rv /var/www/html/wordpress/wp-content/uploads

Run the wizard

In order for the wizard to run properly we need to temporarily give the wordpress directory httpd_sys_rw_content_t selinux context

sudo chcon -t httpd_sys_rw_content_t /var/www/html/wordpress/

Now navigate to your new website in a browser and follow the wizard, which will create a wp-config.php file inside the wordpress directory. Once your site is properly set up, restore the original security context for the wordpress directory:

sudo restorecon -v /var/www/html/wordpress/

Success! Everything is working within the proper SELinux contexts.

Troubleshooting

Permission denied when accessing /wordpress

The obvious thing to check is to make sure the directory /var/www/html/wordpress has the ownership set to apache. That didn’t fix my issue, though. Thanks to serverfault I narrowed this down to a selinux permissions issue.  Changing the selinux context to httpd_sys_content_t for the wordpress folder fixed the issue.

sudo semanage fcontext -a -t httpd_sys_content_t /var/www/html/wordpress/
sudo restorecon -v /var/www/html/wordpress/

Sorry, but I can’t write the wp-config.php file.

“You can create the wp-config.php manually and paste the following text into it.”

I had a suspicion that writing files was also a selinux issue. This was confirmed when I decided to tail /var/log/audit/audit.log and found this when the wordpress installer tried to write wp-config.php:

type=AVC msg=audit(1475596102.558:16868): avc: denied { write } for pid=5751 comm="httpd" name="wordpress" dev="dm-0" ino=68494934 scontext=system_u:system_r:httpd_t:s0 tcontext=system_u:object_r:httpd_sys_content_t:s0 tclass=dir
type=SYSCALL msg=audit(1475596102.558:16868): arch=c000003e syscall=21 success=no exit=-13 a0=7f9f4b931478 a1=2 a2=0 a3=1 items=0 ppid=5740 pid=5751 auid=4294967295 uid=48 gid=48 euid=48 suid=48 fsuid=48 egid=48 sgid=48 fsgid=48 tty=(none) ses=4294967295 comm="httpd" exe="/usr/sbin/httpd" subj=system_u:system_r:httpd_t:s0 key=(null)

This led me to find this great explanation from Francis Pereira on how to get wordpress and selinux to peacefully coexist. Also thanks to the excellent Redhat Manual for more information on what to do, combined with this very helpful guide from serverlab.

First, we need to temporarily grant httpd_sys_rw_content_t to the wordpress directory. This is to allow the initial wizard to create the wp-config.php file.

sudo chcon -t httpd_sys_rw_content_t /var/www/html/wordpress/

Now you can run the wizard and it will work properly.

Once your site is set up, restore the context to http_sys_content_t

sudo restorecon -v /var/www/html/wordpress/

Install Guacamole 0.9.9 on Ubuntu 15.10

Lately I’ve been trying to upgrade my installation of Guacamole 0.9.8 to 0.9.9. You’d think it would be simple. It is not. I ended up just blowing up my 0.9.8 VM and starting over, this time with Ubuntu 15.10. I found this excellent guide which got me most of the way there, with one small hiccup that took way more time than it should have to figure out.

I will paste the guide I got from the above site for convenience with my added notes for clarification on parts that I had trouble with.

Installation

#!/bin/bash
# WORKING ON UBUNTU 15.10 WITH GUAC 0.9.9 AND TOMCAT8

#Update Everything
apt-get update && apt-get -y dist-upgrade

#Install Stuff
#You will be prompted for a mysql root password. Remember this for the configuration step; change MYSQLROOTPASSWORD to whatever you enter here.
apt-get -y install libcairo2-dev libpng12-dev libossp-uuid-dev libfreerdp-dev libpango1.0-dev libssh2-1-dev libtelnet-dev libvncserver-dev libpulse-dev libssl-dev libvorbis-dev libwebp-dev mysql-server mysql-client mysql-common mysql-utilities tomcat8

# Install libjpeg-turbo-dev
wget -O libjpeg-turbo-official_1.4.2_amd64.deb http://downloads.sourceforge.net/project/libjpeg-turbo/1.4.2/libjpeg-turbo-official_1.4.2_amd64.deb
dpkg -i libjpeg-turbo-official_1.4.2_amd64.deb

# Add GUACAMOLE_HOME to Tomcat8 ENV
echo "" >> /etc/default/tomcat8
echo "# GUACAMOLE EVN VARIABLE" >> /etc/default/tomcat8
echo "GUACAMOLE_HOME=/etc/guacamole" >> /etc/default/tomcat8

#Download Guacamole Files
wget -O guacamole-0.9.9.war http://downloads.sourceforge.net/project/guacamole/current/binary/guacamole-0.9.9.war
wget -O guacamole-server-0.9.9.tar.gz http://sourceforge.net/projects/guacamole/files/current/source/guacamole-server-0.9.9.tar.gz
wget -O guacamole-auth-jdbc-0.9.9.tar.gz http://sourceforge.net/projects/guacamole/files/current/extensions/guacamole-auth-jdbc-0.9.9.tar.gz
wget -O mysql-connector-java-5.1.38.tar.gz http://dev.mysql.com/get/Downloads/Connector/j/mysql-connector-java-5.1.38.tar.gz

#Extract Guac
tar -xzf guacamole-server-0.9.9.tar.gz
tar -xzf guacamole-auth-jdbc-0.9.9.tar.gz
tar -xzf mysql-connector-java-5.1.38.tar.gz

# MAKE DIRECTORIES
mkdir /etc/guacamole
mkdir /etc/guacamole/lib
mkdir /etc/guacamole/extensions

# Install GUACD
cd guacamole-server-0.9.9
./configure --with-init-dir=/etc/init.d
make
make install
ldconfig
systemctl enable guacd
cd ..

# Move files to correct locations
mv guacamole-0.9.9.war /etc/guacamole/guacamole.war
ln -s /etc/guacamole/guacamole.war /var/lib/tomcat8/webapps/
cp mysql-connector-java-5.1.38/mysql-connector-java-5.1.38-bin.jar /etc/guacamole/lib/
cp guacamole-auth-jdbc-0.9.9/mysql/guacamole-auth-jdbc-mysql-0.9.9.jar /etc/guacamole/extensions/

Configuration

# Configure guacamole.properties 
echo "mysql-hostname: localhost" >> /etc/guacamole/guacamole.properties 
echo "mysql-port: 3306" >> /etc/guacamole/guacamole.properties 
echo "mysql-database: guacamole_db" >> /etc/guacamole/guacamole.properties 
echo "mysql-username: guacamole_user" >> /etc/guacamole/guacamole.properties
 
# This is where you will want to change "PASSWORD" 
echo "mysql-password: PASSWORD" >> /etc/guacamole/guacamole.properties 
rm -rf /usr/share/tomcat8/.guacamole 
ln -s /etc/guacamole /usr/share/tomcat8/.guacamole 

# Restart Tomcat Service 
service tomcat8 restart

#Configure the MySQL database
#Make sure you change MYSQLROOTPASSWORD and PASSWORD
mysql -u root -pMYSQLROOTPASSWORD
create database guacamole_db;
create user 'guacamole_user'@'localhost' identified by 'PASSWORD';
GRANT SELECT,INSERT,UPDATE,DELETE ON guacamole_db.* TO 'guacamole_user'@'localhost';
flush privileges;
quit

#Populate the database
#Make sure you change MYSQLROOTPASSWORD
cat guacamole-auth-jdbc-0.9.9/mysql/schema/*.sql | mysql -u root -pMYSQLROOTPASSWORD guacamole_db

After that it should be as simple as logging into your shiny guacamale server as guacadmin/guacadmin.

It wasn’t that simple for me. When I tried to log in all I got was a blank page. Reading the log file /var/log/tomcat8/localhost.<date>.log revealed the following:

Error querying database. Cause: java.sql.SQLException: Access denied for user 'guacamole_user '@'localhost' (using password: YES)

I kept changing and double checking the password for guacamole and couldn’t figure out why it was getting access denied. I even manually logged into mysql with that username and password and it worked, yet guacamole would not load in the browser.

After staring at the log long enough I realized that there is a space before the closing tick on the username. Aha! There were pesky trailing spaces in my guacamole.properties. Removing those spaces did the trick. Always some mundane detail!

Finally I have guacamole 0.9.9 working.

Install Guacamole 0.9.8 in CentOS 7

Lately I’ve embarked in installing the latest version of Guacamole, 0.9.8, in a fresh installation of CentOS 7. Kudos go to the excellent guide I found from here.  Derek’s guide is for 0.9.7 but it also works for 0.9.8. I ran into a few hangups but after I figured them out it worked beautifully.

First, fetch the needed binaries:

rpm -Uvh http://mirror.metrocast.net/fedora/epel/7/x86_64/e/epel-release-7-5.noarch.rpm   # EPEL Repo
yum -y install wget   # wget
wget http://download.opensuse.org/repositories/home:/felfert/Fedora_19/home:felfert.repo && mv home\:felfert.repo /etc/yum.repos.d/   # Felfert Repo
yum -y install tomcat libvncserver freerdp libvorbis libguac libguac-client-vnc libguac-client-rdp libguac-client-ssh
yum -y install cairo-devel pango-devel libvorbis-devel openssl-devel gcc pulseaudio-libs-devel libvncserver-devel terminus-fonts \
freerdp-devel uuid-devel libssh2-devel libtelnet libtelnet-devel tomcat-webapps tomcat-admin-webapps java-1.7.0-openjdk.x86_64

Next, install guac server (the latest as of this writing is 0.9.8)

mkdir ~/guacamole && cd ~/
wget http://sourceforge.net/projects/guacamole/files/current/source/guacamole-server-0.9.8.tar.gz
tar -xzf guacamole-server-0.9.8.tar.gz && cd guacamole-server-0.9.8
./configure --with-init-dir=/etc/init.d
make
make install
ldconfig

I received an error while running ./configure :

checking for jpeg_start_compress in -ljpeg... no
configure: error: "libjpeg is required for writing jpeg messages"

It means I didn’t have libjpeg dev libraries installed. Easily fixed:

yum install libjpeg-turbo-devel

Next, install the guacamole war files

mkdir -p /var/lib/guacamole && cd /var/lib/guacamole/
 wget http://sourceforge.net/projects/guacamole/files/current/binary/guacamole-0.9.8.war -O guacamole.war
 ln -s /var/lib/guacamole/guacamole.war /var/lib/tomcat/webapps/
 rm -rf /usr/lib64/freerdp/guacdr.so
 ln -s /usr/local/lib/freerdp/guacdr.so /usr/lib64/freerdp/

Next comes configuring the database

#Install database and connector
yum -y install mariadb mariadb-server
 mkdir -p ~/guacamole/sqlauth && cd ~/guacamole/sqlauth
 wget http://sourceforge.net/projects/guacamole/files/current/extensions/guacamole-auth-jdbc-0.9.8.tar.gz
 tar -zxf guacamole-auth-jdbc-0.9.8.tar.gz
 wget http://dev.mysql.com/get/Downloads/Connector/j/mysql-connector-java-5.1.32.tar.gz
 tar -zxf mysql-connector-java-5.1.32.tar.gz
 mkdir -p /usr/share/tomcat/.guacamole/{extensions,lib}
 mv guacamole-auth-jdbc-0.9.8/mysql/guacamole-auth-jdbc-mysql-0.9.8.jar /usr/share/tomcat/.guacamole/extensions/
 mv mysql-connector-java-5.1.32/mysql-connector-java-5.1.32-bin.jar /usr/share/tomcat/.guacamole/lib/
 systemctl restart mariadb.service

#Configure database
mysqladmin -u root password MySQLRootPass
mysql -u root -p   # Enter above password
create database guacdb;
create user 'guacuser'@'localhost' identified by 'guacDBpass';
grant select,insert,update,delete on guacdb.* to 'guacuser'@'localhost';
flush privileges;
quit
cd ~/guacamole/sqlauth/guacamole-auth-jdbc-0.9.8/mysql/schema/
cat ./*.sql | mysql -u root -p guacdb   # Enter SQL root password set above

Now we need to configure guacamole to use our new database.

mkdir -p /etc/guacamole/ && vi /etc/guacamole/guacamole.properties
# MySQL properties
mysql-hostname: localhost
mysql-port: 3306
mysql-database: guacdb
mysql-username: guacuser
mysql-password: guacDBpass

# Additional settings
mysql-disallow-duplicate-connections: false

Link the file you just made to the tomcat configuration directory

ln -s /etc/guacamole/guacamole.properties /usr/share/tomcat/.guacamole/

Cleanup temporary files and enable necessary services on boot

cd ~ && rm -rf guacamole*
systemctl enable tomcat.service && systemctl enable mariadb.service && chkconfig guacd on
systemctl reboot

Lastly, open the firewall up for port 8080 (thanks stack overflow)

firewall-cmd --permanent --add-port=8080/tcp
firewall-cmd --reload

Navigate to guacamole in your browser: http://<IP address>/guacamole:8080. You should see the guacamole login screen.

Additional hiccup

This new version of guacamole has a different user interface. It took me longer than I’d like to admit to realize how to get out of a guacamole session once it’s started. Sessions are now full screen with no obvious way to exit.

The way to exit the full screen guacamole session is to press the magic key combination of ctrl, alt, and shift. It will reveal a menu from the side. This is all clearly defined in the user documentation, but my lack of willingness to read it caused me to waste much time. Lesson learned!

Fix images in wordpress posts after address change

In my experiments with WordPress I have discovered an annoying complication when it comes to changing servers. When you change the address of your wordpress site, links to images and other files inside posts are not updated to point to the new wordpress URL. Links to the posts themselves are all updated but for whatever reason WordPress ignores post content.

What this means is if you transform a staging server (say, http://server) into a “production” server (say, http://jeppson.org) posts containing links to any files on that server will now be (silently!) broken. The files are all still there, the posts just point to the old address instead of the new one.

There are numerous plugins for WordPress that can take care of this; however I am averse to installing random WordPress plugins due to security concerns. Fortunately, the fix for this vexing problem is a fairly simple one. It requires executing a command on the MySQL database hosting the wordpress site.

The table we are interested in is the <prefix>_posts table. <prefix> was defined when the wordpress site was set up. If you don’t remember what that prefix was, take a peek at /wp-config.php line 62

Capture

The field we are interested in is post_content – this is where all the links to pictures in your various posts resides. From here execute the update command and use the replace function to find all instances of the old URL and update them with the new URL

Capture

That’s it! Your images should now re-appear. The two commands I used in my specific example are below.  Be sure to replace the following :

  • wordpress with the name of your wordpress database
  • nm_posts with the name of your site’s post table,
  • http://server with your original wordpress URL and
  • http://jeppson.org with your new wordpress URL
mysql -u root -p wordpress
update nm_posts set post_content = replace(post_content, 'http://server' , 'http://jeppson.org');

 


 

Update: I’ve discovered a very handy script that will take care of this for you: wp-cli

To do the same thing as above with less steps, simply download wp-cli

curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar

Make the script executable and move it to /usr/bin

chmod +x wp-cli.phar
sudo mv wp-cli.phar /usr/local/bin/wp

Navigate to the directory of your wordpress install and run wp search-replace

cd /var/www/wordpress
wp search-replace 'http://server' 'http://jeppson.org'

Easy.

Capture