Welcome to part 5 of 8 in my series on Kick-Ass WordPress Optimization! If you're here, you've probably just completed setting up your server and DNS to host your website on your very own Digital Ocean droplet. Now we're going to get down to business and install your database and your WordPress installation!
Wordpress Optimization: Installation
This section on Installing and Configuring WordPress will consist of the following topics:
- Installing MySQL and Configuring Your Database
- Actually Installing MySQL
- Creating a Database
- Creating a User
- Granting your User Permission to Use Your Database
- Tuning MySQL for WordPress
- Installing and Configuring WordPress
- Downloading WordPress
- Configuring WordPress
- Updating WordPress
- Important WordPress Plugins
- Jetpack (Strapping In)
- Search Engine Optimization (Get Indexed)
- Performance (Speeding Up)
- Compression (Making it Smaller)
- Structured Data (Making it Readable)
So let's get started and dive right in. Go ahead and get back to your SSH console for your server (as seen in part 2 on Setting Up Your VPS) and let's roll.
Installing MySQL and Configuring Your Database
Actually Installing MySQL
One of the main things you'll need to run WordPress is a database to store all of your posts, settings, and bits of content you'll be working with as you use WordPress. Fortunately, this is rather easy and we can get you started pretty darn quick! So let's go to your console and type in the following:
mike@webofmike:~# sudo apt-get -y install mysql-server
This will go ahead and install the MySQL server on your droplet. Once this get's rolling, you'll be asked to enter a "root" password for MySQL. For the love of all that is sacred, make sure you either write this down or remember it. You'll hate yourself later if you don't. Should be bright pink so you can't really miss this...
Once this is done, let's move onto the next steps.
Creating a MySQL Database
This part is also rather easy and quite necessary! We're going to go ahead and create the WordPress database. Remember that fancy "root" password you JUST wrote down and/or remembered? Good. You'll need it right now. Go back to your console and type the following:
mike@webofmike:~# sudo mysql -u root -p
Give them your root password (you're logging into MySQL as the "root" or administrative user) and you should see a screen with a mysql prompt that looks like this:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37039 Server version: 5.5.41-0ubuntu0.14.04.1-log (Ubuntu) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
This will allow you to type in SQL (Structured Query Language) commands to build and manage your new WordPress database. So, let's go ahead and create the database. You'll want to enter a name for the database that's similar to your website. For example: For webofmike.com I'd use the database: "webofmike". Go back to your console and type the following (and change the database name to your own):
mysql> CREATE DATABASE yourblog;
Make sure you don't forget the semi-colon! Very important as this tells MySQL to execute the command. Once you've done this, you'll actually have your very own WordPress database!
Creating a MySQL User
Now that we have a database, we'd like to create a user that can access the database. This is good for security reasons as we never want to use the root user to allow access to a database. Too much power and really just unsafe. Again, you'll want to replace the username and password with something you prefer (or can remember) so, let's go ahead and type the following into your mysql prompt on your console:
mysql> CREATE USER 'yourblog'@'localhost' IDENTIFIED BY 'yourpassword';
This will go ahead and create your first MySQL user. Now that you have a database and a user, we have to give the user permission to actually use the database!
Granting your User Permission to Use Your Database
The final basic steps in the user / database access process is to grant your user the permission to alter, read from, and write to your database. This is rather simple and can be achieved with the following command in your console:
mysql> GRANT ALL PRIVILEGES ON yourblog.* TO 'yourblog'@'localhost' WITH GRANT OPTION;
Now your user has direct access to your database and we can get on to tuning this beast to be a raging data-serving machine.
Tuning MySQL for WordPress
Open your console and let's get ready to set some parameters for your MySQL configuration file to make it better and faster. We want your database to be as efficient as possible. We also want to be memory conscious so we can use as few resources as possible while maximizing our performance. This will save you money one day.
So let's get started. Go to your console and open the my.cnf MySQL configuration file by typing the following:
mike@webofmike:~# sudo nano /etc/mysql/my.cnf
We're going to optimize the InnoDB engine as this is our default for MySQL over 5.5. This and I much prefer it over the MyISAM engine for our WordPress application as I do lots of editing and like some of the rollback features, etc.
So, go towards the bottom of this file and find the line that says [mysqldump]. Above this line, go ahead and paste (right click) the following into your my.cnf file:
# INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 32M innodb_buffer_pool_instances = 2 innodb_use_sys_malloc = 0
This will optimize the InnoDB performance of MySQL. Next, we want to do some thread and buffer tuning in order to optimize memory usage, caching, and buffering of data. Find the line that says "bind-address = 127.0.0.1". Right below this line, paste the following:
# * Fine Tuning key_buffer = 16M max_allowed_packet = 16M thread_stack = 512K thread_cache_size = 8
This should cover the basics of tuning MySQL. I'll cover more on this topic in depth at a later date. For now, let's save and close the file. Press Ctrl+O, Enter, then Ctrl+X to save.
Once this is done, type the following into your console to reboot MySQL:
mike@webofmike:~# sudo service mysql restart
Now your MySQL has some solid basic optimization for WordPress. Now, we get down to the fun part: Installing WordPress.
Installing and Configuring WordPress
Downloading WordPress
We'll need to go get WordPress in order to install it. This is also a fairly easy step. We're going to use a program called wget that will help us download the files for WordPress to our server.