One place for hosting & domains

      November 2018

      How to Create an Email Newsletter (And Why You Should)


      It seems like everyone has a newsletter these days. In fact, you probably stumble across newsletter sign-up forms on most sites you visit. While the many invitations can seem overwhelming, there’s a reason so many sites want your email address.

      As it turns out, running a newsletter is a smart move for many reasons. It can help you increase brand awareness, improve user engagement, and even generate more sales. The best part is that creating and sending a newsletter doesn’t have to be difficult or time-consuming.

      In this article, we’ll take a closer look at the benefits of running a newsletter for your website, using our own The WordCandy Weekly as an example. We’ll also discuss what you can do with your newsletter, before showing you the basics of how to set one up for yourself. Let’s get to work!

      A Brief Introduction to Email Newsletters

      The sign-up form for The WordCandy Weekly.

      Chances are that you’re pretty familiar with newsletters. They’ve become as much a part of everyday internet life as blogs or cat memes. In fact, you probably receive at least a few of these messages in your inbox on a weekly or daily basis.

      When something becomes that mundane, you can easily to forget why it became so popular in the first place. You might even expect that newsletters aren’t as useful as they once were. However, the reality is that they’ve seen something of a resurgence in recent years.

      One of the main reasons for their continuing popularity is that newsletters offer a straightforward way to digest news and updates, without needing to seek out the information on social media. In addition, mobile web usage is becoming increasingly popular. Among other things, this means people are more likely to check their emails on the go, making newsletters more useful than ever.

      Of course, we should clarify that while the term ‘newsletter’ implies that these messages are used to communicate news, that’s not their only application. You can also use your newsletter to promote products and services, let people know about upcoming events, solicit feedback, and much more. You can even create automated emails that trigger at specific times or are sent when a user performs a particular action (more on this later).

      The point is that newsletters offer you a lot of freedom and can be applied to the specific needs of your business. Before we discuss how to start one up, let’s dig deeper into how they can benefit you.

      Why You Should Consider Starting Your Own Newsletter

      Naturally, getting a newsletter off the ground will require you to invest a certain degree of time and effort. As such, it will need to provide benefits to offset the costs. With that in mind, let’s take a look at some of the ways a newsletter can benefit you and your business.

      For example, a newsletter can:

      • Help you increase traffic and engagement. If you use your newsletter to highlight your products or content, you can raise awareness and interest in what you’re offering. This also helps your audience stay on top of everything you do.
      • Let you control your message. You can inform users directly about news and other valuable information right away. As such, your existing audience will hear everything straight from you, and you’ll have control over how it’s presented.
      • Enable you to connect directly with your users. Newsletters are a perfect tool for creating a personal connection with your users since you can address them directly. You can also segment your audience to create email campaigns that are specifically tailored to a smaller part of your overall customer base.
      • Generate more conversions. Since a newsletter enables you to raise awareness and publicize your offerings, it often leads to more conversions and even increased revenue.

      As we mentioned earlier, how you decide to use your newsletter will depend primarily on your goals and business model. For example, an e-commerce business might leverage emails to highlight products, publicize deals, and even provide offers that are unique to subscribers.

      However, you can also use your newsletter more holistically. It doesn’t even have to be directly (or even indirectly) related to your actual business. Instead, you can provide general information that’s of interest to your audience, offering a service rather than engaging in direct marketing.

      One example of this in action is is our own newsletter: The WordCandy Weekly. This is a regular news roundup of the latest stories from the WordPress community.

      An issue from The WordCandy Weekly.

      At WordCandy, we provide content solutions for WordPress businesses so this resource is obviously relevant to our company. At the same time, it’s not directly tied to the work we produce.

      Instead, our intention with this newsletter is to provide value to our existing clients and readers. By doing that, we’ve been able to help our subscribers stay up-to-date with news that matters to them. At the same time, we can raise awareness of our business, without needing to spend time and money on marketing or compromising the value of the product.

      In other words, this is a type of promotion that benefits all parties. By using a newsletter in this way, you can better establish yourself as a knowledgeable part of your niche or community. You can also grow your brand awareness, all while providing a genuinely useful service to your mailing list.

      This doesn’t mean there’s anything wrong with using your newsletter for direct promotion, of course. Which route you decide to use will depend largely on your business, target market, and goals. An excellent starting point is to study newsletters created by businesses and websites that are similar to yours, and see if there’s a particular niche or need that’s currently unfulfilled.

      How to Create a Newsletter

      Once you’ve settled on a concept for your newsletter and know what you want it to focus on, it’s time to start putting it together. If you’re worried that this means manually sending hundreds or thousands of emails from your personal inbox, you’ll be pleased to hear that the actual process is much more painless.

      In fact, there are plenty of solutions that can help you create and manage your newsletter. If you have a WordPress site, you can even do this straight from your admin dashboard by using a plugin like Jackmail.

      The Jackmail plugin.

      You can use this plugin to incorporate your newsletter directly into your site. Jackmail even makes it possible to generate automatic emails at predetermined times, such as whenever you publish a new post. It also includes an email builder, enabling you to create gorgeous templates for your messages with ease.

      Another popular solution, and the one we use for our own newsletter, is Mailchimp.

      The Mailchimp home page.

      Mailchimp is a perfect newsletter solution for almost any application; it’s easy to use while providing a lot of opportunities to customize your email campaigns. It offers many key features, along with various ways that you can integrate your Mailchimp account with other platforms. Like Jackmail, it also includes an intuitive email builder, along with a series of pre-made templates you can use.

      This particular solution also has a robust free plan, which enables you to gather up to 2,000 subscribers and send up to 12,000 emails every month. This should be more than enough to begin with, and when your list start to grow, you can always upgrade to a premium plan.

      Which solution you decide to use will once again depend on your preferences, as well as what specific features you need. However, it’s vital to remember that whether your newsletter becomes a success will depend primarily on the quality of its content.

      3 Tips for Making Your Newsletter a Success

      After choosing an email marketing solution and putting in place a general plan for your newsletter, you’ll just need to ensure that you take advantage of its full potential. To give you a running start, here are some tips that will help you optimize your newsletter.

      1. Create a Simple, Attention-Grabbing Template

      One of the most important (and fun) aspects of creating your newsletter is putting together your template. As you might expect, this template will be the basis for your newsletter design. This way you can keep each message’s look the same while adding new content each time.

      For example, the template we use for our weekly newsletters looks like this.

      The WordCandy Weekly email template.

      This makes it easy for us to add new content, subject lines, and preheaders each week without needing to recreate the look and layout each time. Building this template was also quite effortless. Almost all newsletter solutions will offer you both layouts and themes you can use as a basis for your campaigns.

      For example, Mailchimp offers various design options for free.

      A sample of several Mailchimp themes.

      One thing you’ll notice about these themes is that, while they feature very different looks and are designed for various purposes, they almost all prioritize simplicity. This is a key consideration when it comes time to design your template. While making your emails look compelling is necessary, what matters more is that the design doesn’t overwhelm or bury the actual content.

      You can see this philosophy applied in our newsletter as well, as we opted for a straightforward, accessible approach. We decided to only use a single image, as our primary goal is to highlight the featured news items. However, images can serve a variety of useful purposes, especially if you’re using your campaigns to highlight products or if your newsletter will have a more commercial focus. In the end, your template’s design should serve your ultimate goals.

      It’s also a smart idea to design with accessibility in mind. This includes making sure that color combinations and fonts are easy to read. What’s more, many email marketing solutions will let you send a version of your newsletter that strips out all images and styling. This is called a plain-text campaign. Mailchimp, like many similar tools, automatically generates a plain-text campaign and sends it to subscribers who have opted not to receive the standard HTML version of your emails.

      Once you’ve designed your template, it’s always critical to test it before going live. A simple way of doing this is by sending out test emails and getting feedback from your coworkers, friends, and so on. If you have created multiple templates, you can also perform A/B testing to see which one is most effective.

      2. Use Automation to Your Advantage

      To make the most out of your newsletter, you should aim to do as little work as possible. This may sound counterintuitive, but the point is that it’s vital to make your campaigns efficient. After all, consider how much time it would take to manually write and send every single email if you had to construct it all from scratch each time.

      Fortunately, any quality email marketing service will provide you with automation options. There are several ways you can use these to your advantage, but let’s look at a few of the most helpful strategies.

      First and foremost, you can set up your campaign to send full emails automatically, at specific trigger points. Jackmail refers to this as creating an automation or an automated workflow. A common example of this is to send a welcome email to new users, which triggers when they sign up to your site.

      However, you can get even more creative with this option. For instance, you could send a birthday congratulation email on each subscriber’s birthday and even include a coupon code as a gift. This adds a personal element to your newsletters.

      To elaborate further on that point, you should also aim to make sure every email feels like it’s directed at the recipient. A good way to achieve this is by using variables, which will be dynamically replaced with relevant information. In Mailchimp, these are referred to as Merge Tags, and you can use them in a variety of ways.

      One option that we implemented in our newsletter is the customizable date tag. When editing a new email, the date simply appears like this:

      The date merge tag in the Mailchimp editor.

      However, the system will automatically add the current date when the newsletter is generated.

      The current date in a sent newsletter.

      An even better use for merge tags is to display information that’s specific to each recipient. For example, if you collected their names during the registration process, you can add the *|FNAME|* tag to display each person’s first name in their email. This helps make each message seem less impersonal, even if they’re automated. In turn, this can help increase the number of clicks on your newsletters.

      3. Keep a Close Eye on Your Newsletter Statistics

      As your newsletter grows, it becomes crucial that you don’t get complacent. In fact, this is where your job really begins, as you’ll need to make sure that your campaigns are (and remain) effective. To do that, you’ll have to study and analyze your email analytics.

      Which metrics you should focus on will depend somewhat on your newsletter’s purpose. However, here are some of the main numbers you should look out for in all your emails:

      • Click Through Rate (CTR): This shows you how many recipients clicked on at least one link on a specific email.
      • Open rate: This is a percentage value displaying how many recipients opened your email at least once.
      • Subscriber growth: This lets you know how many new users have subscribed to your newsletter.

      Once you have this information, which should be provided by your email marketing solution, you can start to improve metrics that are sub-par. For example, you may find that your links are not clearly defined, making them less likely to be clicked on. Or perhaps your subject lines aren’t engaging enough, leading people to ignore your emails. If your subscriber count has plateaued, on the other hand, you might consider marketing your newsletter more on your website and social media.

      Finally, an effective way to get better results from your campaigns is to tailor them to a specific portion of your audience. This is known as audience segmentation, which means focusing unique campaigns on the users who most likely to be interested in them. Using segmentation in your newsletters enables you to raise your CTR and conversions, as subscribers will receive emails that are a better match to their demographics and interests.

      Push Send

      You might think that most people’s inboxes are too stuffed to fit in another newsletter, but you’d be mistaken. There’s always room for one more subscription, especially if it’s well-written, engaging, and provides genuine value to the reader. The best part is that sending out email campaigns will benefit both you and your subscribers.



      Source link

      How To Set Up Jupyter Notebook with Python 3 on Ubuntu 18.04


      Introduction

      An open-source web application, Jupyter Notebook lets you create and share interactive code, visualizations, and more. This tool can be used with several programming languages, including Python, Julia, R, Haskell, and Ruby. It is often used for working with data, statistical modeling, and machine learning.

      This tutorial will walk you through setting up Jupyter Notebook to run from an Ubuntu 18.04 server, as well as teach you how to connect to and use the notebook. Jupyter Notebooks (or simply Notebooks) are documents produced by the Jupyter Notebook app which contain both computer code and rich text elements (paragraph, equations, figures, links, etc.) which aid in presenting and sharing reproducible research.

      By the end of this guide, you will be able to run Python 3 code using Jupyter Notebook running on a remote server.

      Prerequisites

      In order to complete this guide, you should have a fresh Ubuntu 18.04 server instance with a basic firewall and a non-root user with sudo privileges configured. You can learn how to set this up by running through our initial server setup tutorial.

      Step 1 — Set Up Python

      To begin the process, we’ll install the dependencies we need for our Python programming environment from the Ubuntu repositories. Ubuntu 18.04 comes preinstalled with Python 3.6. We will use the Python package manager pip to install additional components a bit later.

      We first need to update the local apt package index and then download and install the packages:

      Next, install pip and the Python header files, which are used by some of Jupyter’s dependencies:

      • sudo apt install python3-pip python3-dev

      We can now move on to setting up a Python virtual environment into which we’ll install Jupyter.

      Step 2 — Create a Python Virtual Environment for Jupyter

      Now that we have Python 3, its header files, and pip ready to go, we can create a Python virtual environment to manage our projects. We will install Jupyter into this virtual environment.

      To do this, we first need access to the virtualenv command which we can install with pip.

      Upgrade pip and install the package by typing:

      • sudo -H pip3 install --upgrade pip
      • sudo -H pip3 install virtualenv

      The -H flag ensures that the security policy sets the home environment variable to the home directory of the target user.

      With virtualenv installed, we can start forming our environment. Create and move into a directory where we can keep our project files. We’ll call this my_project_dir, but you should use a name that is meaningful for you and what you’re working on.

      • mkdir ~/my_project_dir
      • cd ~/my_project_dir

      Within the project directory, we’ll create a Python virtual environment. For the purpose of this tutorial, we’ll call it my_project_env but you should call it something that is relevant to your project.

      • virtualenv my_project_env

      This will create a directory called my_project_env within your my_project_dir directory. Inside, it will install a local version of Python and a local version of pip. We can use this to install and configure an isolated Python environment for Jupyter.

      Before we install Jupyter, we need to activate the virtual environment. You can do that by typing:

      • source my_project_env/bin/activate

      Your prompt should change to indicate that you are now operating within a Python virtual environment. It will look something like this: (my_project_env)user@host:~/my_project_dir$.

      You’re now ready to install Jupyter into this virtual environment.

      Step 3 — Install Jupyter

      With your virtual environment active, install Jupyter with the local instance of pip.

      Note: When the virtual environment is activated (when your prompt has (my_project_env) preceding it), use pip instead of pip3, even if you are using Python 3. The virtual environment's copy of the tool is always named pip, regardless of the Python version.

      At this point, you’ve successfully installed all the software needed to run Jupyter. We can now start the Notebook server.

      Step 4 — Run Jupyter Notebook

      You now have everything you need to run Jupyter Notebook! To run it, execute the following command:

      A log of the activities of the Jupyter Notebook will be printed to the terminal. When you run Jupyter Notebook, it runs on a specific port number. The first Notebook you run will usually use port 8888. To check the specific port number Jupyter Notebook is running on, refer to the output of the command used to start it:

      Output

      [I 21:23:21.198 NotebookApp] Writing notebook server cookie secret to /run/user/1001/jupyter/notebook_cookie_secret [I 21:23:21.361 NotebookApp] Serving notebooks from local directory: /home/sammy/my_project_dir [I 21:23:21.361 NotebookApp] The Jupyter Notebook is running at: [I 21:23:21.361 NotebookApp] http://localhost:8888/?token=1fefa6ab49a498a3f37c959404f7baf16b9a2eda3eaa6d72 [I 21:23:21.361 NotebookApp] Use Control-C to stop this server and shut down all kernels (twice to skip confirmation). [W 21:23:21.361 NotebookApp] No web browser found: could not locate runnable browser. [C 21:23:21.361 NotebookApp] Copy/paste this URL into your browser when you connect for the first time, to login with a token: http://localhost:8888/?token=1fefa6ab49a498a3f37c959404f7baf16b9a2eda3eaa6d72

      If you are running Jupyter Notebook on a local computer (not on a server), you can navigate to the displayed URL to connect to Jupyter Notebook. If you are running Jupyter Notebook on a server, you will need to connect to the server using SSH tunneling as outlined in the next section.

      At this point, you can keep the SSH connection open and keep Jupyter Notebook running or you can exit the app and re-run it once you set up SSH tunneling. Let's choose to stop the Jupyter Notebook process. We will run it again once we have SSH tunneling set up. To stop the Jupyter Notebook process, press CTRL+C, type Y, and then ENTER to confirm. The following output will be displayed:

      Output

      [C 21:28:28.512 NotebookApp] Shutdown confirmed [I 21:28:28.512 NotebookApp] Shutting down 0 kernels

      We’ll now set up an SSH tunnel so that we can access the Notebook.

      Step 5 — Connect to the Server Using SSH Tunneling

      In this section we will learn how to connect to the Jupyter Notebook web interface using SSH tunneling. Since Jupyter Notebook will run on a specific port on the server (such as :8888, :8889 etc.), SSH tunneling enables you to connect to the server’s port securely.

      The next two subsections describe how to create an SSH tunnel from 1) a Mac or Linux, and 2) Windows. Please refer to the subsection for your local computer.

      SSH Tunneling with a Mac or Linux

      If you are using a Mac or Linux, the steps for creating an SSH tunnel are similar to using SSH to log in to your remote server, except that there are additional parameters in the ssh command. This subsection will outline the additional parameters needed in the ssh command to tunnel successfully.

      SSH tunneling can be done by running the following SSH command in a new local terminal window:

      • ssh -L 8888:localhost:8888 your_server_username@your_server_ip

      The ssh command opens an SSH connection, but -L specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side (server). This means that whatever is running on the second port number (e.g. 8888) on the server will appear on the first port number (e.g. 8888) on your local computer.

      Optionally change port 8888 to one of your choosing to avoid using a port already in use by another process.

      server_username is your username (e.g. sammy) on the server which you created and your_server_ip is the IP address of your server.

      For example, for the username sammy and the server address 203.0.113.0, the command would be:

      • ssh -L 8888:localhost:8888 sammy@203.0.113.0

      If no error shows up after running the ssh -L command, you can move into your programming environment and run Jupyter Notebook:

      You’ll receive output with a URL. From a web browser on your local machine, open the Jupyter Notebook web interface with the URL that starts with http://localhost:8888. Ensure that the token number is included, or enter the token number string when prompted at http://localhost:8888.

      SSH Tunneling with Windows and Putty

      If you are using Windows, you can create an SSH tunnel using Putty.

      First, enter the server URL or IP address as the hostname as shown:

      Set Hostname for SSH Tunnel

      Next, click SSH on the bottom of the left pane to expand the menu, and then click Tunnels. Enter the local port number you want to use to access Jupyter on your local machine. Choose 8000 or greater to avoid ports used by other services, and set the destination as localhost:8888 where :8888 is the number of the port that Jupyter Notebook is running on.

      Now click the Add button, and the ports should appear in the Forwarded ports list:

      Forwarded ports list

      Finally, click the Open button to connect to the server via SSH and tunnel the desired ports. Navigate to http://localhost:8000 (or whatever port you chose) in a web browser to connect to Jupyter Notebook running on the server. Ensure that the token number is included, or enter the token number string when prompted at http://localhost:8000.

      Step 6 — Using Jupyter Notebook

      This section goes over the basics of using Jupyter Notebook. If you don’t currently have Jupyter Notebook running, start it with the jupyter notebook command.

      You should now be connected to it using a web browser. Jupyter Notebook is a very powerful tool with many features. This section will outline a few of the basic features to get you started using the Notebook. Jupyter Notebook will show all of the files and folders in the directory it is run from, so when you’re working on a project make sure to start it from the project directory.

      To create a new Notebook file, select New > Python 3 from the top right pull-down menu:

      Create a new Python 3 notebook

      This will open a Notebook. We can now run Python code in the cell or change the cell to markdown. For example, change the first cell to accept Markdown by clicking Cell > Cell Type > Markdown from the top navigation bar. We can now write notes using Markdown and even include equations written in LaTeX by putting them between the $$ symbols. For example, type the following into the cell after changing it to markdown:

      # First Equation
      
      Let us now implement the following equation:
      $$ y = x^2$$
      
      where $x = 2$
      

      To turn the markdown into rich text, press CTRL+ENTER, and the following should be the results:

      results of markdown

      You can use the markdown cells to make notes and document your code. Let's implement that equation and print the result. Click on the top cell, then press ALT+ENTER to add a cell below it. Enter the following code in the new cell.

      x = 2
      y = x**2
      print(y)
      

      To run the code, press CTRL+ENTER. You’ll receive the following results:

      first equation results

      You now have the ability to import modules and use the Notebook as you would with any other Python development environment!

      Conclusion

      Congratulations! You should now be able to write reproducible Python code and notes in Markdown using Jupyter Notebook. To get a quick tour of Jupyter Notebook from within the interface, select Help > User Interface Tour from the top navigation menu to learn more.

      From here, you can begin a data analysis and visualization project by reading Data Analysis and Visualization with pandas and Jupyter Notebook in Python 3.

      If you’re interested in digging in more, you can read our series on Time Series Visualization and Forecasting.



      Source link

      How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04


      Introduction

      As your application or website grows, there may come a point where you’ve outgrown your current server setup. If you are hosting your web server and database backend on the same machine, it may be a good idea to separate these two functions so that each can operate on its own hardware and share the load of responding to your visitors’ requests.

      In this guide, we’ll go over how to configure a remote MySQL database server that your web application can connect to. We will use WordPress as an example in order to have something to work with, but the technique is widely applicable to any application backed by MySQL.

      Prerequisites

      Before beginning this tutorial, you will need:

      • Two Ubuntu 18.04 servers. Each should have a non-root user with sudo privileges and a UFW firewall enabled, as described in our Initial Server Setup with Ubuntu 18.04 tutorial. One of these servers will host your MySQL backend, and throughout this guide we will refer to it as the database server. The other will connect to your database server remotely and act as your web server; likewise, we will refer to it as the web server over the course of this guide.
      • Nginx and PHP installed on your web server. Our tutorial How To Install Linux, Nginx, MySQL, PHP (LEMP stack) in Ubuntu 18.04 will guide you through the process, but note that you should skip Step 2 of this tutorial, which focuses on installing MySQL, as you will install MySQL on your database server.
      • MySQL installed on your database server. Follow “How To Install MySQL on Ubuntu 18.04” to set this up.
      • Optionally (but strongly recommended), TLS/SSL certificates from Let’s Encrypt installed on your web server. You’ll need to purchase a domain name and have DNS records set up for your server, but the certificates themselves are free. Our guide How To Secure Nginx with Let’s Encrypt on Ubuntu 18.04 will show you how to obtain these certificates.

      Step 1 — Configuring MySQL to Listen for Remote Connections

      Having one’s data stored on a separate server is a good way to expand gracefully after hitting the performance ceiling of a one-machine configuration. It also provides the basic structure necessary to load balance and expand your infrastructure even more at a later time. After installing MySQL by following the prerequisite tutorial, you’ll need to change some configuration values to allow connections from other computers.

      Most of the MySQL server’s configuration changes can be made in the mysqld.cnf file, which is stored in the /etc/mysql/mysql.conf.d/ directory by default. Open up this file with root privileges in your preferred editor. Here, we’ll use nano:

      • sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

      This file is divided into sections denoted by labels in square brackets ([ and ]). Find the section labeled mysqld:

      /etc/mysql/mysql.conf.d/mysqld.cnf

      . . .
      [mysqld]
      . . .
      

      Within this section, look for a parameter called bind-address. This tells the database software which network address to listen for connections on.

      By default, this is set to 127.0.0.1, meaning that MySQL is configured to only look for local connections. You need to change this to reference an external IP address where your server can be reached.

      If both of your servers are in a datacenter with private networking capabilities, use your database server’s private network IP. Otherwise, you can use its public IP address:

      /etc/mysql/mysql.conf.d/mysqld.cnf

      [mysqld]
      . . .
      bind-address = db_server_ip
      

      Because you’ll connect to your database over the internet, it’s recommended that you require encrypted connections to keep your data secure. If you don’t encrypt your MySQL connection, anybody on the network could sniff sensitive information between your web and database servers. To encrypt MySQL connections, add the following line after the bind-address line you just updated:

      /etc/mysql/mysql.conf.d/mysqld.cnf

      [mysqld]
      . . .
      require_secure_transport = on
      . . .
      

      Save and close the file when you are finished. If you’re using nano, do this by pressing CTRL+X, Y, and then ENTER.

      For SSL connections to work, you will need to create some keys and certificates. MySQL comes with a command that will automatically set these up. Run the following command, which creates the necessary files. It also makes them readable by the MySQL server by specifying the UID of the mysql user:

      • sudo mysql_ssl_rsa_setup --uid=mysql

      To force MySQL to update its configuration and read the new SSL information, restart the database:

      • sudo systemctl restart mysql

      To confirm that the server is now listening on the external interface, run the following netstat command:

      • sudo netstat -plunt | grep mysqld

      Output

      tcp 0 0 db_server_ip:3306 0.0.0.0:* LISTEN 27328/mysqld

      netstat prints statistics about your server’s networking system. This output shows us that a process called mysqld is attached to the db_server_ip at port 3306, the standard MySQL port, confirming that the server is listening on the appropriate interface.

      Next, open up that port on the firewall to allow traffic through:

      Those are all the configuration changes you need to make to MySQL. Next, we will go over how to set up a database and some user profiles, one of which you will use to access the server remotely.

      Step 2 — Setting Up a WordPress Database and Remote Credentials

      Even though MySQL itself is now listening on an external IP address, there are currently no remote-enabled users or databases configured. Let's create a database for WordPress, and a pair of users that can access it.

      Begin by connecting to MySQL as the root MySQL user:

      Note: If you have password authentication enabled, as described in Step 3 of the prerequisite MySQL tutorial, you will instead need to use the following command to access the MySQL shell:

      After running this command, you will be asked for your MySQL root password and, after entering it, you'll be given a new mysql> prompt.

      From the MySQL prompt, create a database that WordPress will use. It may be helpful to give this database a recognizable name so that you can easily identify it later on. Here, we will name it wordpress:

      • CREATE DATABASE wordpress;

      Now that you've created your database, you next need to create a pair of users. We will create a local-only user as well as a remote user tied to the web server’s IP address.

      First, create your local user, wordpressuser, and make this account only match local connection attempts by using localhost in the declaration:

      • CREATE USER 'wordpressuser'@'localhost' IDENTIFIED BY 'password';

      Then grant this account full access to the wordpress database:

      • GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost';

      This user can now do any operation on the database for WordPress, but this account cannot be used remotely, as it only matches connections from the local machine. With this in mind, create a companion account that will match connections exclusively from your web server. For this, you'll need your web server's IP address.

      Please note that you must use an IP address that utilizes the same network that you configured in your mysqld.cnf file. This means that if you specified a private networking IP in the mysqld.cnf file, you'll need to include the private IP of your web server in the following two commands. If you configured MySQL to use the public internet, you should match that with the web server's public IP address.

      • CREATE USER 'wordpressuser'@'web-server_ip' IDENTIFIED BY 'password';

      After creating your remote account, give it the same privileges as your local user:

      • GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'web_server_ip';

      Lastly, flush the privileges so MySQL knows to begin using them:

      Then exit the MySQL prompt by typing:

      Now that you've set up a new database and a remote-enabled user, you can move on to testing whether you're able to connect to the database from your web server.

      Step 3 — Testing Remote and Local Connections

      Before continuing, it's best to verify that you can connect to your database from both the local machine — your database server — and from your web server with each of the wordpressuser accounts.

      First, test the local connection from your database server by attempting to log in with your new account:

      • mysql -u wordpressuser -p

      When prompted, enter the password that you set up for this account.

      If you are given a MySQL prompt, then the local connection was successful. You can exit out again by typing:

      Next, log into your web server to test remote connections:

      You'll need to install some client tools for MySQL on your web server in order to access the remote database. First, update your local package cache if you haven't done so recently:

      Then install the MySQL client utilities:

      • sudo apt install mysql-client

      Following this, connect to your database server using the following syntax:

      • mysql -u wordpressuser -h db_server_ip -p

      Again, you must make sure that you are using the correct IP address for the database server. If you configured MySQL to listen on the private network, enter your database's private network IP. Otherwise, enter your database server's public IP address.

      You will be asked for the password for your wordpressuser account. After entering it, and if everything is working as expected, you will see the MySQL prompt. Verify that the connection is using SSL with the following command:

      If the connection is indeed using SSL, the SSL: line will indicate this, as shown here:

      Output

      -------------- mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper Connection id: 52 Current database: Current user: [email protected] SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu) Protocol version: 10 Connection: 203.0.113.111 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 3 hours 43 min 40 sec Threads: 1 Questions: 1858 Slow queries: 0 Opens: 276 Flush tables: 1 Open tables: 184 Queries per second avg: 0.138 --------------

      After verifying that you can connect remotely, go ahead and exit the prompt:

      With that, you've verified local access and access from the web server, but you have not verified that other connections will be refused. For an additional check, try doing the same thing from a third server for which you did not configure a specific user account in order to make sure that this other server is not granted access.

      Note that before running the following command to attempt the connection, you may have to install the MySQL client utilities as you did above:

      • mysql -u wordpressuser -h db_server_ip -p

      This should not complete successfully, and should throw back an error that looks similar to this:

      Output

      ERROR 1130 (HY000): Host '203.0.113.12' is not allowed to connect to this MySQL server

      This is expected, since you haven't created a MySQL user that's allowed to connect from this server, and also desired, since you want to be sure that your database server will deny unauthorized users access to your MySQL server.

      After successfully testing your remote connection, you can proceed to installing WordPress on your web server.

      Step 4 — Installing WordPress

      To demonstrate the capabilities of your new remote-capable MySQL server, we will go through the process of installing and configuring WordPress — the popular content management system — on your web server. This will require you to download and extract the software, configure your connection information, and then run through WordPress's web-based installation.

      On your web server, download the latest release of WordPress to your home directory:

      • cd ~
      • curl -O https://wordpress.org/latest.tar.gz

      Extract the files, which will create a directory called wordpress in your home directory:

      WordPress includes a sample configuration file which we'll use as a starting point. Make a copy of this file, removing -sample from the filename so it will be loaded by WordPress:

      • cp ~/wordpress/wp-config-sample.php ~/wordpress/wp-config.php

      When you open the file, your first order of business will be to adjust some secret keys to provide more security to your installation. WordPress provides a secure generator for these values so that you do not have to try to come up with good values on your own. These are only used internally, so it won't hurt usability to have complex, secure values here.

      To grab secure values from the WordPress secret key generator, type:

      • curl -s https://api.wordpress.org/secret-key/1.1/salt/

      This will print some keys to your output. You will add these to your wp-config.php file momentarily:

      Warning! It is important that you request your own unique values each time. Do not copy the values shown here!

      Output

      define('AUTH_KEY', 'L4|2Yh(giOtMLHg3#] DO NOT COPY THESE VALUES %G00o|te^5YG@)'); define('SECURE_AUTH_KEY', 'DCs-k+MwB90/-E(=!/ DO NOT COPY THESE VALUES +WBzDq:7U[#Wn9'); define('LOGGED_IN_KEY', '*0kP!|VS.K=;#fPMlO DO NOT COPY THESE VALUES +&[%8xF*,18c @'); define('NONCE_KEY', 'fmFPF?UJi&(j-{8=$- DO NOT COPY THESE VALUES CCZ?Q+_~1ZU~;G'); define('AUTH_SALT', '@qA7f}2utTEFNdnbEa DO NOT COPY THESE VALUES t}Vw+8=K%20s=a'); define('SECURE_AUTH_SALT', '%BW6s+d:7K?-`C%zw4 DO NOT COPY THESE VALUES 70U}PO1ejW+7|8'); define('LOGGED_IN_SALT', '-l>F:-dbcWof%4kKmj DO NOT COPY THESE VALUES 8Ypslin3~d|wLD'); define('NONCE_SALT', '4J(<`4&&F (WiK9K#] DO NOT COPY THESE VALUES ^ZikS`es#Fo:V6');

      Copy the output you received to your clipboard, then open the configuration file in your text editor:

      • nano ~/wordpress/wp-config.php

      Find the section that contains the dummy values for those settings. It will look something like this:

      /wordpress/wp-config.php

      . . .
      define('AUTH_KEY',         'put your unique phrase here');
      define('SECURE_AUTH_KEY',  'put your unique phrase here');
      define('LOGGED_IN_KEY',    'put your unique phrase here');
      define('NONCE_KEY',        'put your unique phrase here');
      define('AUTH_SALT',        'put your unique phrase here');
      define('SECURE_AUTH_SALT', 'put your unique phrase here');
      define('LOGGED_IN_SALT',   'put your unique phrase here');
      define('NONCE_SALT',       'put your unique phrase here');
      . . .
      

      Delete those lines and paste in the values you copied from the command line.

      Next, enter the connection information for your remote database. These configuration lines are at the top of the file, just above where you pasted in your keys. Remember to use the same IP address you used in your remote database test earlier:

      /wordpress/wp-config.php

      . . .
      /** The name of the database for WordPress */
      define('DB_NAME', 'wordpress');
      
      /** MySQL database username */
      define('DB_USER', 'wordpressuser');
      
      /** MySQL database password */
      define('DB_PASSWORD', 'password');
      
      /** MySQL hostname */
      define('DB_HOST', 'db_server_ip');
      . . .
      

      And finally, anywhere in the file, add the following line which tells WordPress to use an SSL connection to our MySQL database:

      /wordpress/wp-config.php

      define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);
      

      Save and close the file.

      Next, copy the files and directories found in your ~/wordpress directory to Nginx's document root. Note that this command includes the -a flag to make sure all the existing permissions are carried over:

      • sudo cp -a ~/wordpress/* /var/www/html

      After this, the only thing left to do is modify the file ownership. Change the ownership of all the files in the document root over to www-data, Ubuntu's default web server user:

      • sudo chown -R www-data:www-data /var/www/html

      With that, WordPress is installed and you're ready to run through its web-based setup routine.

      Step 5 — Setting Up WordPress Through the Web Interface

      WordPress has a web-based setup process. As you go through it, it will ask a few questions and install all the tables it needs in your database. Here, we will go over the initial steps of setting up WordPress, which you can use as a starting point for building your own custom website that uses a remote database backend.

      Navigate to the domain name (or public IP address) associated with your web server:

      http://example.com
      

      You will see a language selection screen for the WordPress installer. Select the appropriate language and click through to the main installation screen:

      WordPress install screen

      Once you have submitted your information, you will need to log into the WordPress admin interface using the account you just created. You will then be taken to a dashboard where you can customize your new WordPress site.

      Conclusion

      By following this tutorial, you've set up a MySQL database to accept SSL-protected connections from a remote WordPress installation. The commands and techniques used in this guide are applicable to any web application written in any programming language, but the specific implementation details will differ. Refer to your application or language's database documentation for more information.



      Source link