MySQL Server 5.1 on Ubuntu Server 10.04.3 LTS 64-bit

Post Reply
User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

MySQL Server 5.1 on Ubuntu Server 10.04.3 LTS 64-bit

Post: # 227Post LHammonds
Thu Dec 15, 2011 5:11 pm

Greetings and salutations,

I hope this thread will be helpful to those who follow in my foot steps as well as getting any advice based on what I've done / documented. I am a documentation freak so this will probably grow into a very LARGE step-by-step process. If useful, it will probably be best to convert to a wiki once completed.

High-level overview

This thread will cover installation of Ubuntu server inside a vSphere 4.1 virtual machine running on ESXi 4.1 servers. It will also cover the installation and configuration of MySQL 5.1 and phpmyadmin. This is a merge of several other guides, comments and my own experience of putting it all together in one place in a format that is easy for me to follow and reproduce the same results (yes, I create documentation that I will have to re-read one day because of my bad memory problems!). Please excuse my ignorance since I am fairly new to Linux.

Tools utilized in this process
Helpful links

The list below are sources of information that helped me configure this system as well as some places that might be helpful to me later on as this process continues. Assumptions

This documentation will need to make use of some very-specific information that will most-likely be different for each person / location. And as such, I will note some of these in this section. They will be highlighted in red throughout the document as a reminder that you should plug-in your own value rather than actually using my "place-holder" value.

Under no circumstance should you use that actual values I list below. They are place-holders for the real thing. This is just a checklist template you need to have answered before you start the install process.

Wherever you see RED in this document, you need to substitute it for what your company uses. Use the list below as a template you need to have answered before you continue.

  • Ubuntu Server name: mysql
  • Internet domain: mydomain.com
  • Server domain alias (Windows): work
  • Server fully-qualified domain: work.mydomain.com
  • Ubuntu Server IP address: 192.168.107.20
  • Ubuntu Server IP subnet mask: 255.255.255.0
  • Ubuntu Server IP gateway: 192.168.107.1
  • Internal DNS Server 1: 192.168.107.23
  • Internal DNS Server 2: 192.168.107.212
  • Ubuntu Admin ID: administrator
  • Ubuntu Admin Password: myadminpass
  • Ubuntu Root Password: myrootpass
  • MySQL Admin Password: mysqladminpass
  • MySQL Application Password: mysqlapppass
I also assume the reader knows how to use the VI editor. If not, you will need to beef up your skill set or use a different editor in place of it.

Install PuTTY

Download the portable edition and run the install...except it doesn't really "install" like a normal program, it simply extracts to a specified folder and will run from that folder even if you put it on a USB stick and carry over to a new computer (requires no install to run and thus leaves a very small footprint on your system)

  1. Start PuTTY
  2. Type the following and click the Save button:
    Host Name: MySQL (or the IP such as 192.168.107.20)
    Port: 22
    Connection type: SSH
    Saved Sessions: MySQL
  3. Now all you have to do is double-click on the MySQL session and it will connect to your server (when online).

Virtual Machine Settings

Virtual Manager: VMware vSphere Client 4.1
Virtual Host: VMware ESXi Server 4.1
  • Configuration: Custom
  • Name: MySQL
  • Datastore: DS3400-LUN0 (use whatever storage container you have)
  • Virtual Machine Version: 7
  • Guest Operating System: Linux, Version: Ubuntu Linux (64-bit)
  • Number of virtual processors: 1
  • Memory Size: 512 MB
  • Number of NICs: 1
  • NIC 1: VM Network
  • Adapter: E1000, Connect at Power On: Checked
  • SCSI controller: LSI Logic Parallel
  • Select a Disk: Create a new virtual disk
  • Create a Disk: 8 GB, No thin provisioning, No cluster features, Store with the virtual machine
  • Advanced Options: Virtual Device Node = SCSI (0:0)
  • Remove Floppy Drive
  • Mount CD/DVD Drive to Ubuntu ISO (ubuntu-10.04.3-server-amd64.iso). Make sure CD/DVD is set to Connect at power on
  • Set boot options to Force BIOS Setup so you can set CDROM to boot before the Hard Disk

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Re: MySQL Server 5.1 on Ubuntu Server 10.04.3 LTS 64-bit

Post: # 228Post LHammonds
Thu Dec 15, 2011 5:18 pm

Install Ubuntu Server
  1. Power on the Virtual Machine (VM)
  2. Press {ENTER} to accept English
  3. Select Install Ubuntu Server {ENTER}
  4. Press {ENTER} to accept English
  5. Press {ENTER} to accept United States
  6. Select No to not detect keyboard layout
  7. Press {ENTER} to accept USA
  8. Press {ENTER} to accept USA
  9. Type mysql {ENTER} (this is your hostname)
  10. Press {ENTER} to accept detected time zone (America/Chicago)
  11. Select Manual {ENTER}
  12. Select SCSI3 (0,0,0) (sda) - 8.6 GB VMware Virtual disk {ENTER}
  13. Select Yes to create new empty partition table, {ENTER}
  14. Select pri/log 8.6 GB FREE SPACE {ENTER}
  15. Select Create a new partition {ENTER}
  16. Type 200 MB, {ENTER} (NOTE: This will be the /boot partition)
  17. Select Primary {ENTER}
  18. Select Beginning {ENTER}
  19. Select Use as: Ext4 journaling file system {ENTER}
  20. Select Ext2 file system {ENTER}
  21. Select Mount point: / {ENTER}
  22. Select /boot - static files of the boot loader {ENTER}
  23. Select Bootable flag: off {ENTER} (NOTE: This toggles it on)
  24. Select Done setting up the partition {ENTER}
  25. Select Configure the Logical Volume Manager {ENTER}
  26. Select Yes to write change to disks and configure LVM, {ENTER}
  27. Select Create volume group {ENTER}
  28. Type LVG {ENTER}
  29. Select /dev/sda free #1 (8389MB; FREE SPACE), {SPACEBAR}, {ENTER}
  30. Select Yes to write change to disks and configure LVM, {ENTER}
  31. Select Create logical volume {ENTER}
  32. Select LVG (8384MB) {ENTER}
  33. Type swap {ENTER}
  34. Type 1G {ENTER} (NOTE: This is double the amount of RAM)
  35. Select Create logical volume {ENTER}
  36. Select LVG (7386MB) {ENTER}
  37. Type bak {ENTER}
  38. Type 2G {ENTER}
  39. Select Create logical volume {ENTER}
  40. Select LVG (5389MB) {ENTER}
  41. Type opt {ENTER}
  42. Type 1G {ENTER}
  43. Select Create logical volume {ENTER}
  44. Select LVG (4391MB) {ENTER}
  45. Type temp {ENTER}
  46. Type 2G {ENTER}
  47. Select Create logical volume {ENTER}
  48. Select LVG (2394MB) {ENTER}
  49. Type root {ENTER}
  50. Type 2G {ENTER}
  51. Select Finish {ENTER}
  52. Select #1 998.2 MB directly under LVM VG LVG, LV swap, {ENTER}
  53. Select Use as: do not use {ENTER}
  54. Select swap area {ENTER}
  55. Select Done setting up the partition {ENTER}
  56. Select #1 2.0 GB directly under LVM VG LVG, LV root, {ENTER}
  57. Select Use as: do not use {ENTER}
  58. Select Ext4 journaling file system {ENTER}
  59. Select Mount point: none {ENTER}
  60. Select / - the root file system {ENTER}
  61. Select Done setting up the partition {ENTER}
  62. Select #1 2.0 GB directly under LVM VG LVG, LV bak, {ENTER}
  63. Select Use as: do not use {ENTER}
  64. Select Ext4 journaling file system {ENTER}
  65. Select Mount point: none {ENTER}
  66. Select Enter manually {ENTER}
  67. Type /var/backup {ENTER}
  68. Select Label: none {ENTER}
  69. Type backup {ENTER}
  70. Select Done setting up the partition {ENTER}
  71. Select #1 998.2 MB directly under LVM VG LVG, LV opt, {ENTER}
  72. Select Use as: do not use {ENTER}
  73. Select Ext4 journaling file system {ENTER}
  74. Select Mount point: none {ENTER}
  75. Select /opt - add-on application software packages {ENTER}
  76. Select Label: none {ENTER}
  77. Type mysqldb {ENTER}
  78. Select Done setting up the partition {ENTER}
  79. Select #1 2.0 GB directly under LVM VG LVG, LV temp, {ENTER}
  80. Select Use as: do not use {ENTER}
  81. Select Ext4 journaling file system {ENTER}
  82. Select Mount point: none {ENTER}
  83. Select Enter manually {ENTER}
  84. Type /var/temp {ENTER}
  85. Select Label: {ENTER}
  86. Type temp {ENTER}
  87. Select Done setting up the partition {ENTER}
  88. Here is what the screen looks like at this point: Partitions
  89. Select Finish partitioning and write changes to disk {ENTER}
  90. Select Yes to write changes to disk, {ENTER}
  91. Type Administrator, {ENTER} for the full name
  92. Press {ENTER} to accept the default of the lowercase name of administrator
  93. Type myadminpass, {ENTER}, myadminpass, {ENTER}
  94. Select No, {ENTER} to not encrypt your home directory
  95. Press {ENTER} to accept a blank line for the HTTP proxy
  96. Select Install security updates automatically, {ENTER}
  97. Highlight only OpenSSH server and press {SPACEBAR} to enable, {ENTER} to continue. NOTE: This allows us to use PuTTY after installation to connect to the server.
  98. Select Yes, {ENTER} to install GRUB boot loader to the master boot record
  99. Installation Complete - from the VM menu, select VM --> Edit Settings and select CD/DVD Drive 1 and change to "Client Device" which will effectively remove the ISO. Now press {ENTER} to reboot.

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Re: MySQL Server 5.1 on Ubuntu Server 10.04.3 LTS 64-bit

Post: # 229Post LHammonds
Thu Dec 15, 2011 5:30 pm

Initial Configurations

NOTE: During the setup process, we are going to use the root account to make things easier. Later on, it will be best to use the "sudo" command to only temporarily grant root access rather than logging in with root access.

  1. At the console login prompt, login with your administrator account (administrator / myadminpass)
  2. At the $ prompt, type sudo passwd root {ENTER}, type your administrator password (myadminpass), it will ask for a new password, type myrootpass and verify. NOTE: This enables the root account.
  3. At the $ prompt, change to the super user root account by typing su {ENTER} and then provide the new root password you just configured.
  4. Type vi /etc/network/interfaces {ENTER} and change the following: (We need to change the network interface card (NIC) from using DHCP to a static IP)

    From:

    Code: Select all

    iface eth0 inet dhcp
    To:
    iface eth0 inet static address 192.168.107.20 netmask 255.255.255.0 gateway 192.168.107.1 network 192.168.107.0 broadcast 192.168.107.255
    NOTE #1: You may need to manually remove the DHCP record (lease) associated to this Ubuntu server from your DHCP server so the correct IP can be found by other machines on the network. This can be avoided by temporarily configuring the VM Network Adapter connection to be "Host Only Network" instead of "VM Network" so the server is isolated during setup...at least until you reach the testing of the static IP below.

    NOTE #2: You might also need to manually add a HOST(A) record to your Windows DNS server (for mysql.mydomain.com and mysql.work.mydomain.com)
  5. Restart the network by typing /etc/init.d/networking restart
  6. Sanity check! Type ifconfig and make sure the settings are correct. Then type ping http://www.google.com or similar and see if ping works.
  7. Shutdown and power off the server by typing shutdown -P now {ENTER}
  8. At this point forward, you can use PuTTY to access the console rather than the console itself for better performance, ability to scroll, etc.
  9. In VM menu, select VM --> Snapshot --> Take Snapshot. Give it a name like STEP 1 and description of Ubuntu Server 10.04.3 LTS, clean install, Static IP: 192.168.107.20 and click OK
Software Configurations
  1. Start the Ubuntu server and connect using PuTTY.
  2. At the login prompt, login with your administrator account (administrator / myadminpass) and then type su and the root password (myrootpass)
  3. At the $ prompt, type aptitude -y install vim-nox for use instead of the built-in VI editor. more info
  4. At the $ prompt, type aptitude -y install p7zip-full to install 7-zip archive utility.
  5. Type dpkg-reconfigure dash and answer No
  6. Remove AppArmor to avoid problems by typing the following:

    Code: Select all

    /etc/init.d/apparmor stop
    update-rc.d -f apparmor remove
    aptitude remove apparmor apparmor-utils
  7. Reboot the server by typing reboot
Configure Ubuntu for File Sharing

This file sharing section is optional but can be handy if you need to swap files between the Linux server and a Windows machine.

This documentation will utilize this share for passing pre-configured files (configs, scripts, etc.) to make it faster/easier during installation.
  1. Start the Ubuntu server and connect using PuTTY.
  2. At the login prompt, login with your administrator account (administrator / myadminpass) and then type su and the root password (myrootpass)
  3. Install Samba by typing aptitude -y install samba smbfs (NOTE: To share a folder with Windows, you just need the samba package, to connect to a Windows share, you need both samba and smbfs)
  4. Type the following commands:

    Code: Select all

    cp /etc/samba/smb.conf /etc/samba/smb.conf.bak
    mkdir -p /srv/samba/share
    chown nobody:nogroup /srv/samba/share/
    chmod 0777 /srv/samba/share
    
  5. Edit the configuration file by typing vi /etc/samba/smb.conf
  6. Change workgroup = WORKGROUP to workgroup = work (you are using the domain alias)
  7. Change:

    Code: Select all

    #   security = user
    to:

    Code: Select all

       security = user
  8. Add the following section to the end of the file:

    Code: Select all

    [share]
    comment = Ubuntu File Server Share
    path = /srv/samba/share
    browsable = yes
    guest ok = yes
    read only = no
    create mask = 0755
  9. Save and exit the file.
  10. Restart the samba services to utilize the new configuration by typing:

    Code: Select all

    restart smbd
    restart nmbd
  11. You should now be able to click Start --> Run and type \\mysql or \\192.168.107.20 {ENTER} and see an explorer window with a Share folder. Drag-n-drop a file into the Share folder. If it worked, it will not display an error message and you should be able to view it from the server by typing ls -l /srv/samba/share/
  12. Shutdown and power off the server by typing shutdown -P now {ENTER}
  13. In VM menu, select VM --> Snapshot --> Take Snapshot. Give it a name like STEP 2 and description of Ubuntu Server 10.04.3 LTS, File share configured, Static IP: 192.168.107.20. The Snapshot Manager should now have a nice hierarchy of snapshots (STEP 1 --> STEP 2 --> You are here)
Configure Windows Remote Mount Point

The remote offsite location is a Windows 2008 server that has a vast amount of hard drive space and currently backs up other servers.

Part of the backup process will be to write files to a mounted folder to a Windows share.

First, let's document the variables for this solution below and highlight them in red throughout the document for easy identification.

The values below are merely samples which you need to change in order to match your environment:

Windows AD Domain Name: work
Windows Server Name: SRV-Backup
Windows Server IP: 192.168.107.218
Windows Share Name: mysql
Windows Physical Share Location: D:\MySQL\
Windows Share ID: mysqlshare
Windows Share Password: mysqlpassword

Create a share on a Windows 2008 server

  1. In Windows Explorer, right-click on the D:\MySQL folder and select Properties
  2. Click the Sharing tab
  3. Click the Advanced Sharing button
  4. Place a checkmark beside Share this folder
  5. Change the Share name to mysql
  6. Set the Comment to MySQL Backup
  7. Click the Permissions button
  8. Select Everyone and click the Remove button
  9. Click the Add button
  10. Type in your MySQL share account: work\mysqlshare and click the Check Names button, click OK
  11. Place a checkmark for Allow Full Control and click OK, click OK, click OK
  12. Create a text file in the root of the shared folder called "online.txt" and you might want to add some text inside saying to never delete this file because it is used (will be) by a MySQL backup script. Probably a good idea to make it read-only as well. Example: D:\MySQL\online.txt
Create an NFS mount to the Windows 2008 server

Connecting to a Windows share requires the samba and smbfs packages to be installed. If you do not have them, type aptitude -y install samba smbfs

  1. At the login prompt, login with your administrator account (administrator / myadminpass) and then type su and the root password (myrootpass)
  2. Type the following commands:
    mkdir -p /mnt/backup chown root:root /mnt/backup chmod 0755 /mnt/backup echo "This file is used to tell if the mount is active or not" > /mnt/backup/offline.txt chown root:root /mnt/backup/offline.txt chmod 0444 /mnt/backup/offline.txt touch /etc/cifspw chmod 0600 /etc/cifspw echo -e "username=work\mysqlshare\npassword=mysqlpassword"
  3. Type vi /etc/hosts and add the following line anywhere in the file:
    192.168.107.218 srv-backup
  4. At this point, you might want to type ping srv-backup to make sure you typed the right IP address as well as seeing a good response.
  5. To mount this system for backups, type the following command:

    Code: Select all

    mount -t cifs //srv-backup/mysql /mnt/backup --options nouser,rw,nofail,noatime,noexec,credentials=/etc/cifspw
  6. To test it, type cp /etc/hosts /mnt/backup/hosts.txt and look on the Windows server and see if the file shows up. Then type rm /mnt/backup/hosts.txt and verify that the file was deleted on the windows server.
  7. This would also be a good time to verify that you can see the "online.txt" file that will be used by the backup script. Type ls -l /mnt/backup/*.txt
  8. To dismount the windows share, type the following command:

    Code: Select all

    umount /mnt/backup
The scripts will call a common mount and unmount function to connect to this share only when needed.

However, if you would rather have it mounted all the time (even after a reboot), do the following (but remember to not use the mount/umount functions in the scripts later):

  1. Type vi /etc/fstab and add the following line at the bottom of the file:
    //srv-backup/mysql /mnt/backup cifs nouser,rw,nofail,noatime,noexec,credentials=/etc/cifspw 0 0
  2. Type mount -a and if it does not spew out any error messages, it will quietly mount the share.
  3. To test it, type cp /etc/hosts /mnt/backup/hosts.txt and look on the Windows server and see if the file shows up. Then type rm /mnt/backup/hosts.txt and verify that the file was deleted on the windows server.
  4. If you need to unmount it, simply type umount /mnt/backup and it will remain unmounted until you reboot. To make it permanent, you need to remove the line you added in the /etc/fstab file.
Sometimes it is helpful during tests to toggle the mount on or off so here is a script you might find helpful. You can find the contents of "standard.conf" file further down the thread.

togglemount.sh

Code: Select all

#!/bin/bash
#############################################
## Name          : togglemount.sh
## Version       : 1.0
## Date          : 2011-11-05
## Author        : LHammonds
## Compatibility : Ubuntu Server 10.04.3 LTS
## Purpose       : Toggle the mount status of a pre-configured backup mount.
## Run Frequency : Manual as needed.
## Exit Codes    :
##   0 = success
##   1 = failure
################ CHANGE LOG #################
## DATE       WHO WHAT WAS CHANGED
## ---------- --- ----------------------------
## 2011-11-05 LTH Created script.
#############################################

## Import common variables and functions. ##
source /var/scripts/common/standard.conf
ERRORFLAG=0

if [ -f ${OFFSITEDIR}/offline.txt ]; then
  echo "Windows share is not mounted.  Mounting share now..."
  f_mount
  sleep 2
  if [ -f ${OFFSITEDIR}/online.txt ]; then
    echo "Mount successful.  Listing contents:"
  else
    echo "Mount failed.  Listing contents:"
    ERRORFLAG=1
  fi
else
  echo "Windows share is mounted.  Dismounting share now..."
  f_umount
  sleep 2
  if [ -f ${OFFSITEDIR}/offline.txt ]; then
    echo "Dismount successful.  Listing contents:"
  else
    echo "Dismount failed.  Listing contents:"
    ERRORFLAG=1
  fi
fi
ls -l ${OFFSITEDIR}
exit ${ERRORFLAG}

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Re: MySQL Server 5.1 on Ubuntu Server 10.04.3 LTS 64-bit

Post: # 230Post LHammonds
Thu Dec 15, 2011 5:45 pm

Install MySQL

  1. Start the Ubuntu server and connect using PuTTY.
  2. At the login prompt, login with your administrator account (administrator / myadminpass) and then type su and the root password (myrootpass)
  3. Install MySQL by typing:

    Code: Select all

    aptitude -y install mysql-server
  4. Type your password for the MySQL "root" user account {ENTER}, re-type the password again {ENTER}
  5. When installation is completed, the MySQL service should automatically start
  6. Verify the service is running by typing either of the following two commands:

    Code: Select all

    service mysql status
    or

    Code: Select all

    netstat -tap | grep mysql

Configure MySQL
  1. Make the new database storage folder and set the correct permissions:

    Code: Select all

    mkdir -p /opt/mysql
    chown mysql:mysql /opt/mysql
  2. Make a personal mysql config file for the root user so the password is not required when running mysql commands

    Code: Select all

    touch ~/my.cnf
    chmod 0600 ~/my.cnf
    vi ~/my.cnf
  3. Type vi ~/my.cnf and add the following lines to it:
    [client] password=myrootpass
  4. Edit the global mysql config file by typing:

    Code: Select all

    vi /etc/mysql/my.cnf
  5. Change the location where the databases are stored:

    Code: Select all

    datadir = /var/lib/mysql
    to

    Code: Select all

    datadir = opt/mysql
  6. Comment-out the bind-address

    Code: Select all

    bind-address = 127.0.0.1
    to

    Code: Select all

    #bind-address = 127.0.0.1
  7. Add the following line to the very bottom:

    Code: Select all

    !include ~/my.cnf
    
  8. Stop the mysql service by typing:

    Code: Select all

    service mysql stop
  9. Move the existing data directory to the new location:

    Code: Select all

    mv /var/lib/mysql/* /opt/mysql/.
  10. Start the mysql service:

    Code: Select all

    service mysql start
  11. Verify the service is running by typing either of the following two commands:

    Code: Select all

    service mysql status
    or

    Code: Select all

    netstat -tap | grep mysql
  12. See if the following command will work without prompting for a password:

    Code: Select all

    mysqladmin status
  13. If this did not work, it is possible you did not uninstall apparmor in an earlier step

Install MySQLAdmin
  1. Install phpmyadmin by typing:

    Code: Select all

    aptitude -y install phpmyadmin
  2. Web server to reconfigure: Select apache2 {SPACEBAR} {ENTER}
  3. Configure database for phpmyadmin with dbconfig-common? Select Yes {ENTER}
  4. Password of the database administrator user: mysqladminpass {ENTER}
  5. MySQL application password for phpmyadmin: mysqlapppass {ENTER}
  6. Password confirmation: mysqlapppass {ENTER}
  7. To verifiy that it install and is running, open a web browser and go to http://192.168.107.20/phpmyadmin and login with the root ID and password
If configuration fails or you need to uninstall and re-install phpmyadmin for any reason and you are not prompted for the same things you entered the 1st time around, you may need to force a reconfiguration after it is installed by typing: dpkg-reconfigure -plow phpmyadmin

Post Reply