Friday 7 July 2017

Investigating linux server crashes - MySQL

MySQL Configuration

The MySQL configuration file can be found at

/etc/my.cnf

By default it does not create a separate log file for MySQL errors, but you can add a line to separate the error messages out.

log_error = /var/log/mysqld.log

MySQL Tuner

For suggestions on improving your MySQL configuration, install MySQL tuner. Run it after MySQL has been running for at least 24 hours. The more time the better the suggestions.

wget  http://mysqltuner.com/mysqltuner.pl
chmod +x  mysqltuner.pl

Then to run the program, you must be logged in as a user that has privileges to access MySQL. For me, it would not work as my usual user account and I solved the problem by logging in as root and changing to the directory that I wget'ed mysqltuner to.

./mysqltuner.pl

A sample output might be:

-------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysqld.log file Control error line(s) into /var/log/mysqld.log file Dedicate this server to your database for highest performance. Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Set thread_cache_size to 4 as a starting value Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version Consider installing Sys schema from https://github.com/mysql/mysql-sys Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_limit (> 1M, or use smaller result sets) tmp_table_size (> 16M) max_heap_table_size (> 16M) thread_cache_size (start at 4) performance_schema = OFF disable PFS innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.

MySQL Log Files

The two main log files for MySQL will now both be in the following directory.

log-slow-queries=/var/lib/mysql/g9online-vps-slow.log
log_error = /var/log/mysqld.log

The Slow Query Log can be analysed using mysqldumpslow

mysqldumpslow /var/lib/mysql/g9online-vps-slow.log

It summarises the number of times the same query has been logged to the Slow Query log. By default, mysqldumpslow sorts by average query time but you can use the -s option to sort by t or at, query time, l or al, lock time, r or ar, rows sent or c, count.

Another useful options is -t N to display only the first N queries in the output.



Tuesday 4 July 2017

Setting up Dakboard to run on a Rasperry Pi

To set up DAKBoard to run on a Raspberry Pi, these notes have been taken from http://dakboard.com/blog/diy-wall-display/

  • Download Raspbian - Download the full version with the GUI, not the lite version
  • Use a program like Etcher to put the Raspbian img image file onto an SD card. If you're on Windows use Win32DiskImager.
  • Boot the Pi 
  • It should boot into the graphical user interface
    • Set the locale and timezone under preferences
    • Enable SSH server under preferences
    • Connect to WiFi by waiting for the task bar network symbol to detect local SiFi SSIDs
  • If the device is not booting into the GUI, run raspi-config to configure the device
Start a terminal window.

I often have to set the display properties to avoid blank displays on reboot. I think is because I am using older monitors.

sudo nano /boot/config.txt
Then uncomment the following lines:
hdmi_force_hotplug=1 config_hdmi_boost=4
It might be worth a reboot at this point to make sure the display works. As a precaution you can check your IP address so that you can SSH into the Pi if the screen doesn't come back
sudo ip addr sudo reboot

Install the unclutter app which hides the mouse pointer after inactivity

sudo apt-get install unclutter
Then make boot configuration changes
sudo nano /boot/config.txt
Add the following lines
# Display orientation. Landscape = 0, Portrait = 1 display_rotate=1 # Use 24 bit colors framebuffer_depth=24
Finally, to make the Pi auto run DAKBoard in a chromium browser kiosk view. Edit the autostart file:
sudo nano ~/.config/lxsession/LXDE-pi/autostart
Add the following lines to it
@xset s off @xset -dpms @xset s noblank @chromium-browser --noerrdialogs --incognito --kiosk http://dakboard.com/app/?p=YOUR_PRIVATE_URL
Now you can reboot the Pi and it should boot up to run DAKboard
sudo reboot

Remote Desktop Setup

In order to set up Remote Desktop, the easiest way for me is to use Microsoft's Remote Desktop Viewer, as i use it for other tasks, which uses RDP, Remote Desktop Protocol.
I had to remove xrdp, vnc4server, tightvncserver, which were all installed in the wrong order.

I had to install tightvncserver, then install xrdp.

Please see:

https://bugs.launchpad.net/ubuntu/+s...dp/+bug/375755
https://bugs.launchpad.net/ubuntu/+s...dp/+bug/220005

It's important to do tightvnc before xrdp.

Doing things in this manner will cause xrdp to use tightvncserver instead of vnc4server, which will not work.
sudo apt-get remove xrdp
sudo apt-get remove vnc4server
sudo apt-get remove tightvncserver

sudo apt-get install tightvncserver
sudo apt-get install xrdp

Setting the time to update on reboot

The pi will not remember the time between reboots. It does not have a real time clock. We need to modify the ntp settings

To back up the configuration file before making any changes to it type the following:

‘sudo cp /etc/ntp.conf /etc/ntp_backup.conf’

I live in the UK so these will be set to ones within the UK.

Type ‘sudo nano /etc/ntp.conf’ you will see on the page something like this:

server 0.pool.ntp.org iburst server 1.pool.ntp.org iburst server 2.pool.ntp.org iburst server 3.pool.ntp.org iburst

Change this to your local time servers. Navigate to http://www.pool.ntp.org/en/use.html to find yours. Example if your in the UK you should change the entry in your ntp.conf file so it looks like this. 

server 0.uk.pool.ntp.org iburst server 1.uk.pool.ntp.org iburst server 2.uk.pool.ntp.org iburst server 3.uk.pool.ntp.org iburst

Finally further down the file you will see the following:

restrict 127.0.0.1 restrict ::1

comment these out by inserting a ‘#’ (without quotes) at the beginning of these lines press crtl + x then ‘Y’ will save your changes.

To update the time. you can reboot or: type ‘sudo service ntp restart’ followed by ‘date’

Setting the monitor to turn off overnight

You can set up a cron job to turn the monitor off overnight. Copy the code in the following script
#!/bin/sh

# Enable and disable HDMI output on the Raspberry Pi

is_off ()
{
 tvservice -s | grep "TV is off" >/dev/null
}

case $1 in
 off)
  tvservice -o
 ;;
 on)
  if is_off
  then
   tvservice -p
   curr_vt=`fgconsole`
   if [ "$curr_vt" = "1" ]
   then
    chvt 2
    chvt 1
   else
    chvt 1
    chvt "$curr_vt"
   fi
  fi
 ;;
 status)
  if is_off
  then
   echo off
  else
   echo on
  fi
 ;;
 *)
  echo "Usage: $0 on|off|status" >&2
  exit 2
 ;;
esac

exit 0
Then create a new file and paste the code into it.
sudo nano /home/pi/rpi-hdmi.sh
Then update the permissions on the file to make it executable
sudo chmod +x /home/pi/rpi-hdmi.sh
And finally edit the cron table to schedule the script
sudo crontab -e
Add the following lines at the bottom
# Turn HDMI Off (22:00/10:00pm)
45 00 * * * /home/pi/rpi-hdmi.sh off

# Turn HDMI On (7:00/7:00am)
45 6 * * * /home/pi/rpi-hdmi.sh on

Creating an SD Card Image

It's wise to create a backup image of the SD card when you have it all setup. The Pi is a fickle beast and at some point, after power cuts or problems, it'll be corrupted. Being able to reflash an image will save time in getting it back up and running again.

On a Mac

dd if=/dev/rdiskx of=/path/to/image bs=1m
Where
/dev/rdiskx
is your SD card.

(using rdisk is preferable as its the raw device - quicker)

To find out which disk your device is type
diskutil list
at a command prompt - also, you may need to be root; to do this type
sudo -s
and enter your password when prompted.

On Linux, you can use the standard dd tool:
dd if=/dev/sdx of=/path/to/image bs=1M
Where
/dev/sdx
is your SD card.

SSH Remote Identification Change

If when SSHing into the Pi you get an identification changed warning after you have reflashed the image
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@    WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!     @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
You can refresh the key using
ssh-keygen -R 
For example,
ssh-keygen -R 192.168.2.10