Skip to content

Instantly share code, notes, and snippets.

@riceissa

riceissa/blah.md

Last active Jan 20, 2020
Embed
What would you like to do?
Setting up Linode block storage with nginx, MySQL, php-fpm on Ubuntu https://github.com/vipulnaik/working-drafts/issues/6

Linode instance

Follow https://www.linode.com/docs/getting-started/ to create new linode and set it up

I am using ubuntu 18.04 LTS.

I've set hostname to testlinode.

For my test instance, I am skipping lots of steps that are unnecessary (e.g. I don't need a non-root account).

Since I am using my root account for everything, I won't write out sudo before commands. You might want to change that if you're working under a regular user account.

MySQL

Follow https://www.linode.com/docs/databases/mysql/install-mysql-on-ubuntu-14-04/

create database wikipediaviews;
create user 'issa'@'localhost' identified by 'secret';
grant all on wikipediaviews.* to 'issa'@'localhost';

(Actually I had to create a separate db user because otherwise I was getting permission errors when trying to query using mysqli on php-fpm; see https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost for more.)

nginx

Follow https://www.linode.com/docs/web-servers/nginx/install-nginx-ubuntu/

php

Follow https://www.linode.com/docs/web-servers/nginx/serve-php-php-fpm-and-nginx/

The php-mysqlnd package was necessary to get mysqli working:

apt install php-mysqlnd

wikipediaviews

Get wikipediaviews source:

cd /var/www
git clone https://github.com/vipulnaik/wikipediaviews.git

Add the following to nginx config to block access to password file (but you already have a working config, so you can just copy that over):

    location /backend/passwordFile.inc {
            return 403;
    }

This completes the setup for nginx, so reload the config:

nginx -s reload

Install pip:

apt install python3-pip

Install aws (might want --user flag after install if not root):

python3 -m pip install awscli

Copy over or create ~/.aws/credentials file.

Check that aws is working:

aws s3 ls s3://issa-bae/wikipediaviews-backups/

(You should see some dumps listed.)

Copy dump to linode:

aws s3 cp s3://issa-bae/wikipediaviews-backups/2020-01-16.sql.bz2 wvdump.sql.bz2

Load in dump (you will be prompted for a password, and after you hit enter it will take a long time (around 14 hours when I ran it); you can run htop or similar to check that mysql is working hard, and run df -lh periodically to check that disk usage on /dev/sda is steadily increasing; also a good idea to run this under screen/tmux so that if the connection is cut off or you want to run it overnight it will work):

bunzip2 < wvdump.sql.bz2 | mysql -u root -p wikipediaviews

(note: I ran this command before I had created a separate mysql user, so that's why I'm using root above. But if you already have a mysql user who has access to the wikipediaviews db, you should be able to use that instead.)

Now Wikipedia Views should be working. Visit your IP address in a browser.

Debugging

If it doesn't work, use the following to check php errors:

less /var/log/nginx/error.log

Create a test.php file with the following:

<?php
$mysqli = new mysqli("localhost", "issa", 'secret', "wikipediaviews");

$stmt = $mysqli->prepare("select 5+5");
$stmt->execute();
$result = $stmt->get_result();
print_r($result->fetch_assoc());

echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;

mysqli_close($mysqli);

?>

Visit test.php in your browser.

Block storage

So far, everything has been on the Linode instance's default SSD. Now we will move mysql over to block storage.

follow https://www.linode.com/docs/platform/block-storage/how-to-use-block-storage-with-your-linode/

mkfs.ext4 "/dev/disk/by-id/scsi-0Linode_Volume_blockstorage60gb"
mkdir "/mnt/blockstorage60gb"
mount "/dev/disk/by-id/scsi-0Linode_Volume_blockstorage60gb" "/mnt/blockstorage60gb"

For the next steps, I'm mostly following this answer but I'm changing a few things, so I'll write out all the steps. You might want to refer to that answer for more details.

Log into mysql:

mysql -u root -p

Check where the database directory is:

select @@datadir;

I get /var/lib/mysql/ so that's what I'll be using.

Now exit from mysql.

Stop mysql:

service mysql stop

At this point, you can check to see how much disk space mysql is using:

du -hs /var/lib/mysql
42G     /var/lib/mysql

You can also run du -hs /var/lib/mysql/wikipediaviews to verify that wikipediaviews is what is taking up all this space.

Copy over files to blockstorage (the -v flag is for verbosity, and the -a flag is for archival mode, which I think does nice things like preserving all the permissions info):

rsync -av /var/lib/mysql /mnt/blockstorage60gb

(If you just want to copy over the wikipediaviews directory, you will want something like rsync -av /var/lib/mysql/wikipediaviews /mnt/blockstorage60gb, but if you do this your wikipediaviews directory will be at /mnt/blockstorage60gb/wikipediaviews instead of /mnt/blockstorage60gb/mysql/wikipediaviews so you'll need to adjust those parts below.)

Move original wikipediaviews mysql directory out of the way:

mkdir -p /var/lib/mysql.old_before_move_to_blockstorage
mv /var/lib/mysql/wikipediaviews /var/lib/mysql.old_before_move_to_blockstorage/wikipediaviews

I'll be following a combination of this guide and this answer below, but I'll write out all the steps.

Create a symlink to point to the new location:

ln -s /mnt/blockstorage60gb/mysql/wikipediaviews /var/lib/mysql/wikipediaviews

Now the only problem is that Ubuntu comes with AppArmor, which prevents mysql from accessing parts of the file system. So we need to tell AppArmor that it is okay for mysql to use /mnt/blockstorage60gb/mysql/wikipediaviews.

Edit /etc/apparmor.d/local/usr.sbin.mysqld and add the following lines:

/mnt/blockstorage60gb/mysql/wikipediaviews/ r,
/mnt/blockstorage60gb/mysql/wikipediaviews/** rwk,

Restart AppArmor:

service apparmor restart

Start mysql:

service mysql start

Now visit your IP address in a browser, to confirm that wikipediaviews is able to access the database. You can also check that the mysql directory isn't taking up space anymore:

du -hs /var/lib/mysql
198M    /var/lib/mysql

Now just edit /etc/fstab so that the mounting happens each time at boot. Add to /etc/fstab the following line:

/dev/disk/by-id/scsi-0Linode_Volume_blockstorage60gb /mnt/blockstorage60gb ext4 defaults,noatime,nofail 0 2
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.