posted Dec 28, 2014, 6:42 AM by Joshua S
[
updated Jan 1, 2015, 8:39 AM
]
This post demonstrates how to build a server running the Relational Database Management System (RDBMS) MySQL. This platform can be used to support any number of applications and processes either locally on this Pi or remotely on other RaspberryPis.
This post initially is based on a great similar post from DuckyPond, but then extends it by shifting the storage of the database to an external USB drive. This helps for two reasons. First, RaspberryPis have limited storage space on the SD card thus much greater storage can be achieved from a USB drive (or other external storage of your choice). Second, RaspberryPi SD cards have a reputation for self corruption -- this moves the database files onto a different storage media thus protecting them from corruption.
Note: An earlier post demonstrated the process to mount an external USB drive. This, or a similar process should be followed if you plan to move the MySQL data out of the default location.
This is one project in a series and assumes the elements performed in several earlier ones are in place and integrated into the template (see the template projects) in order to work. Most of these projects can be performed in any order, but if you follow the order outlined here it will all definitely work. I've found a number of guides to help me, but many have partial documentation, skip key steps, etc. The goal is to build out a guide with everything you need to complete each step, but let me know if I miss something or it isn't clear.
For each template and initial setup, I used an 8GB memory card. With the B+, the image you back up will be the full size of the card whether you expand the file system or not. For the actual projects, I use a mix of card sizes -- generally 32GB, but I like my templates and initial setup configs to be 8GB to reduce the storage size of my backups.
Supply List: - MicroSD Card – A digital memory card, initially designed for media (think a camera) but which will serve as the hard drive for the RaspberryPi. All tutorials will focus on the 8GB size, but you can easily use this process for a larger format also. This should be pre-loaded with the template image created in the previous project (template step 01).
- PuTTY – A free SSH client which is excellent for working at the command line. I know, I know, no one loves the Command Line any more, but the more you use the RaspberryPi the more you will quickly learn that CommandLine > GUI.
- RaspberryPi B+ – The actual RaspberryPi hardware this will all be built around.
- USB Mass Storage Device – The USB storage device mounted in a separate tutorial to store the MySQL data files.
- Win32 Disk Imager – A Free Open Source Software (FOSS) utility to write of image (.IMG) files to various flash card media (SD, MicroSD, etc.). Download the software from the website.
Project: - Write the image you plan to enhance to the SD Card and load it into the RaspberryPi. In this example, we'll use the template file, created in a previous tutorial.
- Using PuTTY (or whatever SSH client you prefer) connect to the IP address of the RaspberryPi. You should know this from the previous step (192.168.84.158 in this example), but if you do not, follow the steps at the beginning of the first lesson which show how to use AngryIP scanner to locate the IP address.
- Once connected, log onto the Pi using:
- UserID: pi
- Password: raspberry
- Now let's update the software currently loaded. There are several ways to do this, but if we issue the dist-upgrade command it will intelligently add software, update packages, and remove unneeded packages.
- sudo apt-get -y dist-upgrade
- Finally, let's upgrade the Pi Kernel:
- Let's reboot now that the upgrades are complete:
- OK, good! Now that everything is updated, let's install our MySQL server. Use the following commands:
- sudo apt-get -y install mysql-server
- mysql-server – The base package for MySQL RDBMS server installations.
- Following the install, the configuration wizard will automatically launch and prompt you for the MySQL Root password. This is, and should be, different than the Root password for the RaspberryPi. Enter the password twice -- once at each prompt.
- The RaspberryPi has fewer resources than the typical server. As a result, we need to begin by copying over a configuration file designed to optimize performance in low resource servers. First, we copy a backup of the original configuration file, we then copy in the sample configuration file for low resource servers, and finally we edit the config file and add the line "query_cache_size = 8M".
- Now -- let's see if our installation works. We need to restart the MySQL server so it pulls in all of the updatec configurations. Use the following command:
- sudo service mysql restart
- To validate the installation we need to log in. We'll do this from the command line, and then enter the password when prompted. If we successfully log in then you will see similar messages to those in the image. Use this command:
- Good! With the login successful, let's create a new user. By default, MySQL limits users to be able to log on from specific hosts. This is a great security feature -- but also makes it very difficult as an end user. Let's do a very bad security decision and create a user with full authority to execute commands from any location. We'll have to have a separate tutorial later to show how to harden the MySQL Server once we have applications deployed. Once we create the user, we need to again restart the MySQL service. Note, replace <UserID> with a new UserID you are defining and <Password> with the password for that user. Use the following commands:
- GRANT ALL PRIVILEGES ON *.* TO '<UserID>' '@'%' IDENTIFIED BY '<Password>' WITH GRANT OPTION;
- FLUSH PRIVILEGES;
- exit
- sudo service mysql restart
- Well done! Let's relocate the storage location, and we'll be done with a new MySQL Server ready to go! Before we move the data location; let's stop the MySQL service using the following command:
- With the service stopped, we need to copy the existing data files to the new location. For the most part, the data files should be located in the /var/lib/mysql/ directory. In the previous tutorial, we mounted the USB drive to the /mnt/storage location. To copy the data, use the following cp command. Note, we use the -a switch to preserve the file permissions (amongst other attributes) and the -r switch to "recursively" copy all of the files within the directory.
- sudo cp -ar /var/lib/mysql/ /mnt/storage
- When we mounted the external storage, it was mounted with permissions granted to root which were relatively restrictive. To allow MySQL to access the drive, we need to alter the permissions lightly to allow read and execute authority. use the following command:
- sudo chmod 755 /mnt/storage
- MySQL keeps key configurations in the my.cnf file. Various versions of MySQL store this file in different locations, so if you don't find it where this tutorial specifies it would be wise to research your version of MySQL to locate this file. To edit the file, use the following command:
- sudo nano /etc/mysql/my.cnf
- Note, every my.cnf file looks slightly different -- the file in the screenshots is actually an updated version of the one you edited above. In addition to the streamlined file we modified earlier, I incorporated various settings in the [mysqld] section from the original file. That step is not included here, and is not really important to what we are trying to accomplish, but I wanted to avoid any confusion. We need to either add or update the datadir config in the [mysqld] section to look like this:
- datadir = /mnt/storage/mysql
- Now, remove the old data files from the original storage location, /var/lib/mysql/, using the following commands. Note, as in the example with DJCCHG0P, directories with the names of any custom databases you have already created should also be removed.
- rm -r DJCCHG0P
- rm -r performance_schema
- rm -r mysql
- Finally, let's start the database service. If it starts without errors, as in the below image, everything works and has been configured correctly. Use the following command:
- Well done! Configuration is complete and your database server is ready to use!
|
|
