So many choices! In previous blogs I’ve talked about the traditional LAMP stack, in which the “M” stands for the open-source MySQL database engine. I barely understand the details, but somehow Oracle now owns MySQL. There’s still a free open-source version, often referred to as the community edition. Some open-source developers weren’t happy about the Oracle switch, however, and “forked” the MySQL code into a new project called MariaDB.
At the same time, Amazon Web Services offers the Amazon Relational Database Service. With this, you get a running instance of the database of your choice (MySQL, MariaDB, or others) and Amazon manages it, including set up, updates, and back ups. Amazon also offers its own SQL engine called Amazon Aurora that is backed up in multiple data centers. It appears that any of the Amazon RDS products could be connected to a Lightsail instance, although the details of how to accomplish that are still a bit murky to me.
In a production environment, Amazon Aurora looks like the best choice to me, but what we have here is a demonstration project. So for now I’m sticking with the original plan of running a database engine on the same instance as the web server, R, and Shiny-Server.
In addition to our choice of either the MySQL or the MariaDB database engine, we’ll need an R package for connecting to it. I thought that was a package called RMySQL. However, when I went to get that package I learned that The ‘RMySQL’ package contains an old implementation based on legacy code from S-PLUS which is being phased out. A modern ‘MySQL’ client based on ‘Rcpp’ is available from the ‘RMariaDB‘ package.
So now I had a choice of either MySQL or MariaDB but I preferred the RMariaDB package. First I tried MySQL, which is available in yum while MariaDB isn’t. That installed fine, but the RMariaDB package wouldn’t install. So I thought I’d try MariaDB instead of MySQL. There’s a tool to select the right Maria DB version to download, which writes a little file for you to add to your yum repository (which is at /ect/yum.repos.d/). After you’ve done that you can use yum commands to install MariaDB. As I will explain, I eventually had to switch back to MySQL to get everything to work, but for the historical record here’s how I loaded MariaDB:
# Create MariaDB repo file printf "# MariaDB 10.2 RedHat repository list - created 2017-11-05 21:05 UTC\n# http://downloads.mariadb.org/mariadb/repositories/\n[mariadb]\nname = MariaDB\nbaseurl = http://yum.mariadb.org/10.2/rhel6-amd64\ngpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB\ngpgcheck=1\n" > /home/ec2-user/instance-setup-files/mariadb.repo # Copy MariaDB repo file to right directory sudo cp /home/ec2-user/instance-setup-files/mariadb.repo /etc/yum.repos.d/ # MariaDB installations sudo yum install MariaDB-server.x86_64 -y sudo yum install MariaDB-devel.x86_64 -y # Magic sauce allowing RMariaDB to install sudo ln -s /usr/lib64/libmariadbclient.a /usr/lib64/libmariadb.a
After I installed MariaDB, the RMariaDB package still wouldn’t install. I eventually figured out that problem; details are at the link and the solution is the final “magic sauce” line in the box above, which helps the RMariaDB installation program find a file it needs.
This seemed like success, but when I tried to connect to the database from R, I got another error, which is described here. That error, it turned out, was an open issue with the RMariaDB team. At that point I decided to switch back to MySQL. As before, RMariaDB wouldn’t install, but this time I realized I’d encountered this problem before – RMariaDB was looking for a file that wasn’t where it expected it to be. So I found the file manually, wrote another “magic sauce” symbolic link, and finally got things to work.
Here’s how to proceed. Using PuTTY, connect to your instance.
- Enter cd ~/instance-setup-files to get to the right folder.
- Do a ls -la to see what’s already in this folder.
- Enter nano mysql-cmds to get into the nano editor.
- Copy what’s in the following box and paste it into the nano editing screen with a right click.
# mysql-cmds
sudo yum install mysql57-server.x86_64 -y
sudo yum install mysql57-devel.x86_64 -y
# start mysql at re-boot
chkconfig mysqld on
# Magic sauce allowing RMariaDB to install
sudo ln -s /usr/lib64/mysql57/libmysqlclient.so.1020 /usr/lib64/libmysqlclient.so.1020
# add packages to R
# first create a script and save it
printf "install.packages('RMariaDB', repos='http://lib.stat.cmu.edu/R/CRAN/')\n" > /home/ec2-user/instance-setup-files/install.R.packages2
# next run the script
sudo Rscript /home/ec2-user/instance-setup-files/install.R.packages2
# added commands for mysql
printf 'sudo service mysqld restart' > /home/ec2-user/bin/restart.mysql
printf 'sudo service mysqld start' > /home/ec2-user/bin/start.mysql
printf 'sudo service mysqld stop' > /home/ec2-user/bin/stop.mysql
chown -R ec2-user:ec2-user /home/ec2-user/bin
chmod -R 755 /home/ec2-user/bin # change on all files in the directory
# links to the commands from the configs folder
ln -s /home/ec2-user/bin/restart.mysql /srv/configs/restart.mysql
ln -s /home/ec2-user/bin/start.mysql /srv/configs/start.mysql
ln -s /home/ec2-user/bin/stop.mysql /srv/configs/stop.mysql
- Press control-O (output) then return to save the file
- Press control-X to exit nano.
- Enter chmod 755 mysql-cmds to make the file executable.
- Run the file with sudo ./mysql-cmds
Next you’ll see lots of stuff scroll by as MySQL is installed. When it stops scrolling and you’re back at the $ prompt:
- Enter cd ../bin and ls -la to make sure there are new restart.mysql, start.mysql, stop.mysql commands.
- Enter whats.autostart to make sure nginx and mysql restart after a boot. (You’ll see a big table. The column on the left lists programs, look for nginx and mysqld. There are seven additional columns, numbered 0 through 6. The ones numbered 2 through 5 should say on rather than off.)
- Enter sudo nano /etc/my.cnf to move the MySQL log file to our /srv/logs folder. Near the bottom of the file there’s a line that begins with log-error=. In that line, change /var/log to /srv/logs. Then control-O, return, control-X.
- Enter start.mysql
- Enter cd /srv/logs and ls -la. You should now see the mysqld.log file, but to be able to read it you need to go sudo chmod 644 mysqld.log
Now that we have MySQL running, we also need to secure it. Enter mysql_secure_installation to begin.
First the secure installation software will ask you if you want to use the Validate Password Plugin. On the one hand, I’m a big believer in long random passwords. On the other, it’s just me here and to avoid additional confusion I’m going to say any other key for No.
Next it asks for a new password. Note that what you type here will not appear on the screen. (The first time I did this, I thought the program had crashed!) Enter a suitably long and random password that you have written down elsewhere. End it with return and you’ll get to blindly enter it a second time. If you didn’t enter the same password both times, you’ll get to try again.
Next you’re asked if you want to Remove anonymous users? I don’t know the best answer here, but I said Yes.
Then you’re asked if you want to Disallow root login remotely? Just say Yes, because it turns out that with Amazon’s SSH keys you’ll be able to log in as root remotely anyhow.
Penultimately, you’re asked if you want to Remove test database and access to it? I said No, but apparently the yum file doesn’t include the test database, because there wasn’t one.
Finally, you’re asked if want to Reload privilege tables now? Why not, I thought, as I answered Yes.
After that I suggest you fire up HeidiSQL and, referring to this post about connecting Lightsail to some helpful Windows tools, log in as root with the password you just gave to MySQL. If it connects you’re rolling. The final test, however, is getting your R code to connect to MySQL. And that’s now working for me.