How to convert MythTV MySQL tables from MyISAM into InnoDB

I wanted to upgrade my MythTV MySQL tables from MyISAM into InnoDB format. When I installed Mythbuntu 12.04, MyISAM was the default database engine. Now the default database engine moving forward in InnoDB. Here are the steps I took to upgrade using phpMyAdmin. I am not sure if I documented all the steps here, so use at your own risk.

SET @DATABASE_NAME = 'mythconverg';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC;

  • Copy the output from this query and paste these results back in the “Run SQL query/queries on database mythconverg” SQL query input box.

I switched from 14.04 Ubuntu-Unity to Ubuntu-Gnome

I been running Ubuntu-Gnome 14.04 for about two weeks now and I really enjoy it and find it easy to use. I switched from the unity desktop because of crashes and errors I was getting. I had been getting crash alerts since running 13.04 unity when I first logged in after a reboot. I had been sending crash reports to Ubuntu/launchpad but finally got fed up enough that I put a fresh (erase partition and use fresh install from live cd). I have always liked the unity interface and the use of the meta/windows key to launch applications. This feels faster because my hands stay on the keyboard and I am not reaching for the mouse to access menus or a dock.

I also tried Debian Jessie and Wheezy with gnome desktop. Debian felt really fast and snappy. Debian was definately harder to setup because I had to enable the non-free (propriety) software for my hardware to work (AMD R-9 270 graphics card). I did not stick with Debian because it would not boot after a software update. I definately want to try Debian-testing again after I get hardware that is more compatible and all my hardware purchases in the future will focus on stability and compatibility with linux in the future. I have Debian Jessie installation on a spare hard drive that I use for recovery purpose and as an emergency working system that does not look as pretty but it gets the job done.

Eventually I installed Ubuntu Gnome 14.04 and installed /root and /home on LUKS encrypted partitions. I will probably run this until version 15.04 comes out. I have heard Ubuntu is shifting its graphics from wayland to mir display server. If they go mir, then I will probably wait a year to for them to get the bugs worked out before I try it.

HowTo update Ubuntu 14.04 / php5-apcu to a owncloud 8.02 supported version

After updating to owncloud 8.0.2 on Ubuntu 14.04, I was getting the following error in owncloud on the administration page “APCu below version 4.0.6 is installed, for stability and performance reasons we recommend to update to a newer APCu version.” I found this post on how to upgrade the php5-apcu package. Here is the link https://github.com/owncloud/core/issues/14386

Backup mysql databases with mysqldump

Addendum 2015-0318: I am now using automysqlbackup for mysql backups. It is simple to use and offers rotating daily/weekly/monthly backups. See my post at https://blog.chadchenault.com/2015/04/25/rotating-backups-of-mysql-database-with-automysqlbackup-version-3-0/.

I have multiple (five) databases in mysql and need to perform regular backups and off-site backups. I am using a python script that uses the mysqldump program to backup my databases.  I am doing daily backups to my home folder at ~/Documents/backup.mysql/. My home directory is backed up daily to another hard drive with a daily cron job and using rsync to create differential backups. I plan on backing up all databases to individual files because I read that it makes restoring a single database easier than if all databases are backed up into one big file. The files will also be compressed with gzip. I did a gzip compression test using the default compression and the “-9” option (best compression) and the difference was minimal for my needs. I decided to use the default compression for speed and simplicity.

mysqldump from command line for testing

sudo mysqldump -u user --all-databases --events | gzip -c > /home/chad0320/Documents/backup.mysql/testbackup.all-databases.sql.gz

 

References

1. python help using mysqldump: http://whatthehost.net/python-s3-backup-script-for-ubuntudebian-web-and-mysql-database-servers/

2. mysqldump manpage: http://manpages.ubuntu.com/manpages/saucy/man1/mysqldump.1.html

 

 

 

gedit 3.10.4 Plugins in Ubuntu 14.04

I like the gedit text editor in Ubuntu 14.04. It is simple and trivial to add new features through plugins. Below I will list the plugins I like and where to find them. I will also give some general installation instructions. Commands from the terminal look like this sentence.

  • System Information
  • Plugins I have installed
    • From https://wiki.gnome.org/Apps/Gedit/ThirdPartyPlugins-v3.8
      • Advanced Find & Replace
        • Advanced find & replace functions. Find & replace in documents.
        • Installation from install script
          • bash install.sh
      • Control Your Tabs
        • Switch between document tabs using Ctrl+Tab / Ctrl+Shift+Tab (most recently used order) and Ctrl+PageUp / Ctrl+PageDown (tabbar order).
        • Installation
          • cp -av controlyourtabs.py controlyourtabs.plugin ~/.local/share/gedit/plugins
      • Toggle Text Wrapping
        • Quickly toggle text wrapping from the View menu or Toolbar.
        • Installation
          • cp -avR toggletextwrapping toggletextwrapping.plugin ~/.local/share/gedit/plugins
    • From https://wiki.gnome.org/Apps/Gedit/ThirdPartyPlugins-v3.0
      • Favorites
        • Organize your favorite documents for quick access.
        • Installation
          • cp -avR favorites favorites.plugin ~/.local/share/gedit/plugins
      • gedit-markdown
        • Syntax highlighting, snippets, external tool, color scheme and embedded HTML preview (can also be used as a Web browser) for Markdown
        • I did not install this, but wanted it here to find it later
      • Pylint 2
        • Analyze Python source code with Pylint
        • Installation
          • I added Gedit BC Developer Plugins which contains Pylint
          • sudo apt-add-repository ppa:gedit-bc-dev-plugins/releases
          • sudo apt-get update
          • sudo apt-get install gedit-developer-plugins
  • Example Installation For Favorites Plugin
    • Most plugins are installed like the “favorites” plugin. All plugins provided good documentation for installation. A few plugins provide a bash installation script making installation even easier.
    • Download and extract the archive.
    • From the terminal, change directory to the extracted archive location with the “cd” command
    • Copy the file favorites.plugin and the folder favorites to ~/.local/share/gedit/plugins/.
      • cp -avR favorites favorites.plugin ~/.local/share/gedit/plugins
    • Restart Gedit.
    • Activate the plugin in gedit.
      • From the menu choose ‘Edit > Preferences’, select the ‘Plugins’ tab, and check the box next to ‘Favorites’

That is it. gedit comes for free from the Gnome Foundation. I appreciate everything they do and for promoting open source software. More information about gnome is here https://www.gnome.org/foundation/.

Create a Multiple Resolution Favicon.ico file with a Linux Convert Command

I found these instructions for creating a multiple resolution favicon.ico file with linux convert command. A favicon.ico file is:

(short for Favorite icon), also known as a shortcut icon, Web site icon, tab icon or bookmark icon, is a file containing one or more[1] small icons, most commonly 16×16 pixels, associated with a particular Web site or Web page. https://en.wikipedia.org/wiki/Favicon.

The commands come from a user cappi2000 from this blog: http://blog.lavoie.sl/2012/11/multi-resolution-favicon-using-imagemagick.html. Code entered into the terminal will look like this sentence.

  • Install imagemagick
    • sudo apt-get install imagemagick
  • rename your source image (with a resolution of at least 256×256 pixels and a PNG format file) to ‘favicon.png’. Replace “your.original.png” in the command with the name of your PNG file.
    • cp -av your.original.png favicon.png
  • Run imagemagick “convert” command

convert favicon.png -bordercolor white -border 0 \
\( -clone 0 -resize 16x16 \) \
\( -clone 0 -resize 32x32 \) \
\( -clone 0 -resize 48x48 \) \
\( -clone 0 -resize 57x57 \) \
\( -clone 0 -resize 64x64 \) \
\( -clone 0 -resize 72x72 \) \
\( -clone 0 -resize 110x110 \) \
\( -clone 0 -resize 114x114 \) \
\( -clone 0 -resize 120x120 \) \
\( -clone 0 -resize 128x128 \) \
\( -clone 0 -resize 144x144 \) \
\( -clone 0 -resize 152x152 \) \
-delete 0 -alpha off -colors 256 favicon.ico

You will have a favicon.ico with most well-known formats stored into one file.

Encrypt Hard Drive for Secure Storage in Linux Ubuntu 14.04

I need to be able to back up my data to an external hard drive that will be stored off site to protect my data if my house burns down or the contents are stolen. I also want the data to be private, so that means an encrypted hard drive. I am using Ubuntu 14.04. This machine is on a headless server so all commands are entered from the terminal over ssh. I have incorporated all of these commands into a python backup script for ease of use. Terminal code will look like this sentence. If you have monitor hooked up to your computer, gui tools are available.

  • References
  • Install cryptsetup
    • sudo apt-get install cryptsetup
  • Identify the correct hard drive to use. You will be erasing all data on the drive.
  • Fill the hard drive with random data. I saw arguments were this step is not required, however I felt it safer to do this and I am not in a rush. This step takes a long time.
    • run command as root
      • sudo -s
      • This method is fast if CPU supports AES-NI (hardware acceleration). see http://serverfault.com/questions/6440/is-there-an-alternative-to-dev-urandom
        • openssl enc -aes-256-ctr -pass pass:"$(dd if=/dev/urandom bs=128 count=1 2>/dev/null | base64)" -nosalt < /dev/zero | pv -pterb > /dev/sdj
      • This was another command posted but was not as fast
        • dd if=/dev/urandom of=/dev/sdj
      • I then played a youtube playlist in the background to help generate random data
  • Encrypt the hard drive
  • Verify the encryption is using luks
    • sudo cryptsetup -v isLuks /dev/disk/by-uuid/2228745a-0db3-48c7-b582-5a3ddf7e7c70
      • Output should be “Command successful.” if LUKS device
  • Open the encrypted device (decrypt/unlock the device)
    • Then first time the encrypted device is opened, a symbolic link called a “mapping” is created which becomes the name of the LUKS device.
      • For example I chose a descriptive name “backup.serv-offsite” and the LUKS device will be created at /dev/mapper/backup.serv-offsite. “/dev/mapper/backup.serv-offsite” will only be created when the LUKS device is opened.
    • sudo cryptsetup -v luksOpen /dev/disk/by-uuid/2228745a-0db3-48c7-b582-5a3ddf7e7c70 backup.serv-offsite
  • See if the LUKS device is already unlocked/open.
    • sudo cryptsetup status backup.serv-offsite
    • output if device is opened
      • /dev/mapper/backup.serv-offsite is active and is in use.
      • type: LUKS1
      • cipher: aes-xts-plain64
      • keysize: 512 bits
      • device: /dev/sde
      • offset: 4096 sectors
      • size: 3907025072 sectors
      • mode: read/write
    • output if device is closed
      • /dev/mapper/backup.serv-offsite is inactive.
  • Create a filesystem once device is opened
    • mkfs.ext4 /dev/mapper/backup.serv-offsite
  • Mount the filesystem
    • sudo mount --verbose -t ext4 /dev/mapper/media.serv-backup /media/bkup.mserv
      • /dev/mapper/media.serv-backup is the opened LUKS device
      • /media/bkup.mserv is the location mount point of the LUKS device
  • Umount the filesystem
    • sudo umount /media/bkup.mserv
  • Close the LUKS device so that the data stays private
    • sudo cryptsetup -v close media.serv-backup
  • Verify the LUKS device was closed, data is not available
    • sudo cryptsetup status backup.serv-offsite
  • Done

Identify a hard drive serial number in Linux Ubuntu 14.04

It is very important to accurately identify a hard drive before you erase the drive or prepare the drive for encryption by writing random data over the whole drive. If you choose the wrong drive, you can loose all your data from that drive. Below are some commands to indentify your drives. I am using linux Ubuntu 14.04. All commands are entered from the terminal. Terminal code will look like this sentence.

  • For the drive you are about to erase, find the serial number on the drive and record the last 5 digits (you do not need to write down the whole serial number and you will save time).
    • Here are instructions for Western Digital drives. Other manufactures are similar.
    • from http://wdc.custhelp.com/app/answers/detail/a_id/249/~/how-to-find-the-serial-number-of-a-wd-drive-or-product

      The serial number is located on the label on the back or side of the drive. Please note that the location will vary according to the drive. It is usually proceeded by a S/N: or SN:. If the serial number is unable to be read, many customers have taken digital pictures of the label and enlarged it on their computer to make it easier to read.

    • Here are google images of serial number numbers on hard drives if you are still unsure.
    • show serial number in software
      • List all physical devices with “blockid” command
        • sudo blkid
          • output is
          • /dev/sdb: UUID=”2628745a-0db3-48c7-b582-5a3ddf7e7c70″ TYPE=”crypto_LUKS”
          • Remember or copy the uuid. You can identify the device by the UUID (preferred because the UUID doesn’t change) as /dev/disk/by-uuid/2628745a-0db3-48c7-b582-5a3ddf7e7c70
      • show serial number with “hdparm” command
        • you have to change /dev/sdb with what is found on the above blkid command or use UUID
        • Identify disk by UUID
          • sudo hdparm -I /dev/disk/by-uuid/2628745a-0db3-48c7-b582-5a3ddf7e7c70 | grep -e "/dev" -e "Serial Number" -e "device sizewith M = 1000"
        • Identify disk by /dev/sdx
          • sudo hdparm -I /dev/sdb | grep -e "/dev" -e "Serial Number" -e "device sizewith M = 1000"
        • output below
          • /dev/sdb:
          • Serial Number:      WD-WCC4MKC41F22
          • device size with M = 1000*1000:     2000398 MBytes (2000 GB)
        • verify this serial number matches what is on the outside of the hard drive

Open a LUKS device with Python 3 script

Here code from a script to unlock a LUKS device before making a backup to the device. I use LUKS for encrypting hard drives for off-site storage.


print ("Determine if luks device mapper link exists. If link exists, then luks is open")
answer = os.path.islink(luks_location)
print ("Result of luks device being open: ", answer)

if answer == False:
    #luks device is closed, attempt to open
    print ("Attempting to open luks device: ", luks_device_uuid)
    subprocess.call("cryptsetup luksOpen " + "--key-file " + repr(luks_keyfile) + " " + repr(luks_device_uuid) + " " + repr(luks_name), shell=True)
    answer = os.path.islink(luks_location)

    # test to see if luksOpen failed and need to exit program
    if answer == False:
        # luksopen failed, exit program
        print ("luks device failed to open. The answer variable is: ", answer)
        sys.exit ("Exiting program now.")
    else:
        # luksopen was successful
        print ("luks device is now open: ", answer)
        print ("luks location is: ", luks_location)
        print ("luks device mapper name is: ", luks_name)

else:
    # luks device was already open
    print ("luks device is already open: ", answer)
    print ("luks location is: ", luks_location)
    print ("luks device mapper name is: ", luks_name)
sys.exit("exiting program")

Next blog will be mounting the device with python.

Linux Screen command

Use this command to start a long process while ssh into a machine to keep the process running if the ssh connection gets dropped.

http://ss64.com/bash/screen.html

  • screen -r         Resume a detached screen session
  • Control-a ?     Display brief help
  • Control-a ”     List all windows for selection
  • Control-a d     Detach screen from this terminal
  • Control-a c     Create new window running a shell
  • screen -t backup   Start a screen with title “backup”
  • Control-a f    Toggle flow on, off or auto
  • Control-a n    Switch to the Next window
  • Control-a p    Switch to the Previous window
  • Control-a A    Accept a title name for the current window

Screen Customizations