Como Configurar um Banco de Dados Remoto para Otimizar o Desempenho do Site com o MySQL no Ubuntu 18.04

Introdução

À medida que sua aplicação ou site cresce, pode chegar um momento em que você superou a configuração atual do seu servidor. Se você estiver hospedando o seu servidor web e o back-end do banco de dados na mesma máquina, pode ser uma boa ideia separar essas duas funções para que cada uma possa operar em seu próprio hardware e compartilhar a carga de responder às solicitações dos visitantes.

Neste guia, veremos como configurar um servidor de banco de dados MySQL remoto ao qual sua aplicação web pode se conectar. Usaremos o WordPress como exemplo para ter algo para trabalhar, mas a técnica é amplamente aplicável a qualquer aplicação suportada pelo MySQL.

Pré-requisitos

Antes de iniciar este tutorial, você precisará de:

  • Dois servidores Ubuntu 18.04. Cada um deles deve ter um usuário não-root com privilégios sudo e um firewall UFW habilitado, conforme descrito em nosso tutorial de Configuração Inicial de servidor com Ubuntu 18.04. Um desses servidores hospedará seu back-end MySQL e, ao longo deste guia, o chamaremos de servidor de banco de dados. O outro se conectará ao seu servidor de banco de dados remotamente e atuará como seu servidor web; da mesma forma, iremos nos referir a ele como servidor web ao longo deste guia.
  • Nginx e PHP instalado em seu servidor web. Nosso tutorial How To Install Linux, Nginx, MySQL, PHP (LEMP stack) in Ubuntu 18.04 o guiará no processo, mas observe que você deve pular o Passo 2 deste tutorial, que se concentra na instalação do MySQL, pois você instalará o MySQL no seu servidor de banco de dados.
  • MySQL instalado em seu servidor de banco de dados. Siga o tutorial Como Instalar o MySQL no Ubuntu 18.04 para configurar isso.
  • Opcionalmente (mas altamente recomendado), certificados TLS/SSL da Let’s Encrypt instalados em seu servidor web. Você precisará comprar um nome de domínio e ter registros DNS configurados para seu servidor, mas os certificados em si são gratuitos. Nosso guia Como Proteger o Nginx com o Let’s Encrypt no Ubuntu 18.04 lhe mostrará como obter esses certificados.

Passo 1 — Configurando o MySQL para Escutar Conexões Remotas

Ter os dados armazenados em um servidor separado é uma boa maneira de expandir elegantemente após atingir o limite máximo de desempenho de uma configuração de uma única máquina. Ela também fornece a estrutura básica necessária para balancear a carga e expandir sua infraestrutura ainda mais posteriormente. Após instalar o MySQL, seguindo o tutorial de pré-requisitos, você precisará alterar alguns valores de configuração para permitir conexões a partir de outros computadores.

A maioria das mudanças na configuração do servidor MySQL pode ser feita no arquivo mysqld.cnf, que é armazenado no diretório /etc/mysql/mysql.conf.d/ por padrão. Abra este arquivo em seu servidor de banco de dados com privilégios de root em seu editor preferido. Aqui, iremos usar o nano:

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

Este arquivo é dividido em seções indicadas por labels entre colchetes ([ e ]). Encontre a seção com o label mysqld:

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

Nesta seção, procure um parâmetro chamado bind-address. Isso informa ao software do banco de dados em qual endereço de rede escutar as conexões.

Por padrão, isso está definido como 127.0.0.1, significando que o MySQL está configurado para escutar apenas conexões locais. Você precisa alterar isso para fazer referência a um endereço IP externo onde seu servidor pode ser acessado.

Se os dois servidores estiverem em um datacenter com recursos de rede privada, use o IP da rede privada do seu servidor de banco de dados. Caso contrário, você pode usar seu endereço IP público:

/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld] . . . bind-address = ip_do_servidor_de_banco_de_dados 

Como você se conectará ao seu banco de dados pela Internet, é recomendável que você exija conexões criptografadas para manter seus dados seguros. Se você não criptografar sua conexão MySQL, qualquer pessoa na rede poderá fazer sniff por informações confidenciais entre seus servidores web e de banco de dados. Para criptografar conexões MySQL, adicione a seguinte linha após a linha bind-address que você acabou de atualizar:

/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld] . . . require_secure_transport = on . . . 

Salve e feche o arquivo quando terminar. Se você estiver usando nano, faça isso pressionando CTRL+X, Y e, em seguida, ENTER.

Para que as conexões SSL funcionem, você precisará criar algumas chaves e certificados. O MySQL vem com um comando que os configura automaticamente. Execute o seguinte comando, que cria os arquivos necessários. Ele também os torna legíveis pelo servidor MySQL, especificando o UID do usuário mysql:

  • sudo mysql_ssl_rsa_setup --uid=mysql

Para forçar o MySQL a atualizar sua configuração e ler as novas informações de SSL, reinicie o banco de dados:

  • sudo systemctl restart mysql

Para confirmar que o servidor agora está escutando na interface externa, execute o seguinte comando netstat:

  • sudo netstat -plunt | grep mysqld
Output
tcp 0 0 ip_do_servidor_de_banco_de_dados:3306 0.0.0.0:* LISTEN 27328/mysqld

O netstat imprime estatísticas sobre o sistema de rede do seu servidor. Esta saída nos mostra que um processo chamado mysqld está anexado ao ip_do_servidor_de_banco_de_dados na porta 3306, a porta padrão do MySQL, confirmando que o servidor está escutando na interface apropriada.

Em seguida, abra essa porta no firewall para permitir o tráfego através dela:

  • sudo ufw allow mysql

Essas são todas as alterações de configuração que você precisa fazer no MySQL. A seguir, veremos como configurar um banco de dados e alguns perfis de usuário, um dos quais você usará para acessar o servidor remotamente.

Passo 2 — Configurando um Banco de Dados para o WordPress e Credenciais Remotas

Embora o próprio MySQL agora esteja escutando em um endereço IP externo, atualmente não há usuários ou bancos de dados habilitados para controle remoto configurados. Vamos criar um banco de dados para o WordPress e um par de usuários que possam acessá-lo.

Comece conectando-se ao MySQL como o usuário root do MySQL:

  • sudo mysql

Nota: Se você tiver a autenticação por senha ativada, conforme descrito no Passo 3 do pré-requisito do tutorial do MySQL, você precisará usar o seguinte comando para acessar o shell do MySQL:

  • mysql -u root -p

Depois de executar este comando, você será solicitado a fornecer sua senha de root do MySQL e, após inseri-la, receberá um novo prompt mysql>.

No prompt do MySQL, crie um banco de dados que o WordPress usará. Pode ser útil atribuir a esse banco de dados um nome reconhecível para que você possa identificá-lo facilmente mais tarde. Aqui, vamos chamá-lo de wordpress:

  • CREATE DATABASE wordpress;

Agora que você criou seu banco de dados, você precisará criar um par de usuários. Criaremos um usuário somente local e um usuário remoto vinculado ao endereço IP do servidor web.

Primeiro, crie seu usuário local, wpuser, e faça com que esta conta corresponda apenas às tentativas de conexão local usando localhost na declaração:

  • CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'senha';

Em seguida, conceda a esta conta acesso total ao banco de dados wordpress:

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

Agora, esse usuário pode executar qualquer operação no banco de dados do WordPress, mas essa conta não pode ser usada remotamente, pois corresponde apenas às conexões da máquina local. Com isso em mente, crie uma conta complementar que corresponda às conexões exclusivamente do seu servidor web. Para isso, você precisará do endereço IP do seu servidor web.

Observe que você deve usar um endereço IP que utilize a mesma rede que você configurou no seu arquivo mysqld.cnf. Isso significa que, se você especificou um IP de rede privada no arquivo mysqld.cnf, precisará incluir o IP privado do seu servidor web nos dois comandos a seguir. Se você configurou o MySQL para usar a internet pública, você deve fazer isso corresponder ao endereço IP público do servidor web.

  • CREATE USER 'remotewpuser'@'ip_do_servidor_web' IDENTIFIED BY 'senha';

Depois de criar sua conta remota, conceda a ela os mesmos privilégios que o usuário local:

  • GRANT ALL PRIVILEGES ON wordpress.* TO 'remotewpuser'@'ip_do_servidor_web';

Por fim, atualize os privilégios para que o MySQL saiba começar a usá-los:

  • FLUSH PRIVILEGES;

Então saia do prompt do MySQL digitando:

  • exit

Agora que você configurou um novo banco de dados e um usuário habilitado remotamente, você pode testar se consegue se conectar ao banco de dados a partir do seu servidor web.

Passo 3 — Testando Conexões Remotas e Locais

Antes de continuar, é melhor verificar se você pode se conectar ao seu banco de dados tanto a partir da máquina local — seu servidor de banco de dados — quanto pelo seu servidor web.

Primeiro, teste a conexão local a partir do seu servidor de banco de dados tentando fazer login com sua nova conta:

  • mysql -u wpuser -p

Quando solicitado, digite a senha que você configurou para esta conta.

Se você receber um prompt do MySQL, então a conexão local foi bem-sucedida. Você pode sair novamente digitando:

  • exit

Em seguida, faça login no seu servidor web para testar as conexões remotas:

  • ssh sammy@ip_do_servidor_web

Você precisará instalar algumas ferramentas de cliente para MySQL em seu servidor web para acessar o banco de dados remoto. Primeiro, atualize o cache de pacotes local se você não tiver feito isso recentemente:

  • sudo apt update

Em seguida, instale os utilitários de cliente do MySQL:

  • sudo apt install mysql-client

Depois disso, conecte-se ao seu servidor de banco de dados usando a seguinte sintaxe:

  • mysql -u remotewpuser -h ip_do_servidor_de_banco_de_dados -p

Novamente, você deve certificar-se que está usando o endereço IP correto para o servidor de banco de dados. Se você configurou o MySQL para escutar na rede privada, digite o IP da rede privada do seu banco de dados. Caso contrário, digite o endereço IP público do seu servidor de banco de dados.

Você será solicitado a inserir a senha da sua conta remotewpuser. Depois de inseri-la, e se tudo estiver funcionando conforme o esperado, você verá o prompt do MySQL. Verifique se a conexão está usando SSL com o seguinte comando:

  • status

Se a conexão realmente estiver usando SSL, a linha SSL: indicará isso, como mostrado aqui:

Output
-------------- mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper Connection id: 52 Current database: Current user: remotewpuser@203.0.113.111 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 --------------

Depois de verificar que você pode se conectar remotamente, vá em frente e saia do prompt:

  • exit

Com isso, você verificou o acesso local e o acesso a partir do servidor web, mas não verificou se outras conexões serão recusadas. Para uma verificação adicional, tente fazer o mesmo em um terceiro servidor para o qual você não configurou uma conta de usuário específica para garantir que esse outro servidor não tenha o acesso concedido.

Observe que antes de executar o seguinte comando para tentar a conexão, talvez seja necessário instalar os utilitários de cliente do MySQL, como você fez acima:

  • mysql -u wordpressuser -h ip_do_servidor_de_banco_de_dados -p

Isso não deve ser concluído com êxito e deve gerar um erro semelhante a este:

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

Isso é esperado, já que você não criou um usuário do MySQL que tem permissão para se conectar a partir deste servidor, e também é desejado, uma vez que você quer ter certeza de que seu servidor de banco de dados negará o acesso de usuários não autorizados ao seu servidor do MySQL.

Após testar com êxito sua conexão remota, você pode instalar o WordPress em seu servidor web.

Passo 4 — Instalando o WordPress

Para demonstrar os recursos do seu novo servidor MySQL com capacidade remota, passaremos pelo processo de instalação e configuração do WordPress — o popular sistema de gerenciamento de conteúdo — em seu servidor web. Isso exigirá que você baixe e extraia o software, configure suas informações de conexão e então execute a instalação baseada em web do WordPress.

No seu servidor web, faça o download da versão mais recente do WordPress para o seu diretório home:

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

Extraia os arquivos, que criarão um diretório chamado wordpress no seu diretório home:

  • tar xzvf latest.tar.gz

O WordPress inclui um arquivo de configuração de exemplo que usaremos como ponto de partida. Faça uma cópia deste arquivo, removendo -sample do nome do arquivo para que ele seja carregado pelo WordPress:

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

Quando você abre o arquivo, sua primeira abordagem será ajustar algumas chaves secretas para fornecer mais segurança à sua instalação. O WordPress fornece um gerador seguro para esses valores, para que você não precise criar bons valores por conta própria. Eles são usados apenas internamente, portanto, não prejudicará a usabilidade ter valores complexos e seguros aqui.

Para obter valores seguros do gerador de chave secreta do WordPress, digite:

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

Isso imprimirá algumas chaves na sua saída. Você as adicionará momentaneamente ao seu arquivo wp-config.php:

Atenção! É importante que você solicite seus próprios valores únicos sempre. Não copie os valores mostrados aqui!

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');

Copie a saída que você recebeu para a área de transferência e abra o arquivo de configuração no seu editor de texto:

  • nano ~/wordpress/wp-config.php

Encontre a seção que contém os valores fictícios para essas configurações. Será algo parecido com isto:

/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'); . . . 

Exclua essas linhas e cole os valores que você copiou a partir da linha de comando.

Em seguida, insira as informações de conexão para seu banco de dados remoto. Essas linhas de configuração estão na parte superior do arquivo, logo acima de onde você colou suas chaves. Lembre-se de usar o mesmo endereço IP que você usou no teste de banco de dados remoto anteriormente:

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

E, finalmente, em qualquer lugar do arquivo, adicione a seguinte linha que diz ao WordPress para usar uma conexão SSL para o nosso banco de dados MySQL:

/wordpress/wp-config.php
define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL); 

Salve e feche o arquivo.

Em seguida, copie os arquivos e diretórios encontrados no diretório ~/wordpress para a raiz de documentos do Nginx. Observe que este comando inclui a flag -a para garantir que todas as permissões existentes sejam transferidas:

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

Depois disso, a única coisa a fazer é modificar a propriedade do arquivo. Altere a propriedade de todos os arquivos na raiz de documentos para www-data, o usuário padrão do servidor web do Ubuntu:

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

Com isso, o WordPress está instalado e você está pronto para executar sua rotina de configuração baseada em web.

Passo 5 — Configurando o WordPress Através da Interface Web

O WordPress possui um processo de configuração baseado na web. Conforme você avança, ele fará algumas perguntas e instalará todas as tabelas necessárias no seu banco de dados. Aqui, abordaremos as etapas iniciais da configuração do WordPress, que você pode usar como ponto de partida para criar seu próprio site personalizado que usa um back-end de banco de dados remoto.

Navegue até o nome de domínio (ou endereço IP público) associado ao seu servidor web:

http://example.com 

Você verá uma tela de seleção de idioma para o instalador do WordPress. Selecione o idioma apropriado e clique na tela principal de instalação:

WordPress install screen

Depois de enviar suas informações, você precisará fazer login na interface de administração do WordPress usando a conta que você acabou de criar. Você será direcionado para um painel onde poderá personalizar seu novo site WordPress.

Conclusão

Ao seguir este tutorial, você configurou um banco de dados MySQL para aceitar conexões protegidas por SSL a partir de uma instalação remota do WordPress. Os comandos e técnicas usados neste guia são aplicáveis a qualquer aplicação web escrita em qualquer linguagem de programação, mas os detalhes específicos da implementação serão diferentes. Consulte a documentação do banco de dados da aplicação ou linguagem para obter mais informações.

DigitalOcean Community Tutorials

How We Made WordPress Faster than Static Site Generators (Case Study – Speeding up WPBeginner)

On the 10th anniversary of WPBeginner, I shared that WPBeginner hosting infrastructure got a huge upgrade thanks to our web hosting partner, HostGator.

Shortly after, I started getting emails from readers asking me to share the details on how we made WPBeginner load blazing fast.

Yes, WPBeginner load faster than most static site generators and in some cases faster than Google AMP sites too.

In this article, I will give you behind the scenes look at how we made WordPress faster than static site generators and headless CMS platforms.

Speeding up WPBeginner - Behind the Scenes

Note: This article is a bit more technical than what we typically publish on WPBeginner. For non-techy users, I recommend following our ultimate guide on how to speed up WordPress.

Background

Lately WordPress has been getting a lot of bad rep from “modern” developers where they say WordPress is slow.

The statement is usually followed up with, you should switch to a JAMstack static site generator like GatsbyJS. Others in the enterprise world will say that you should switch to a headless CMS like Contentful.

Several of my very successful entrepreneur friends started asking me whether this was true.

Some even started the process of migrating to a headless CMS because they read case studies of how others unlocked huge speed improvements by switching from WordPress to static site generators.

This was very frustrating for me because I knew they were wasting tens of thousands of dollars in migration costs. Not to mention, the endless customization costs that will rack up in the future.

So I took it as a challenge to prove that a large WordPress content sites like WPBeginner can load just as fast if not faster than most modern static site generators.

You can call me old school, but at the end of the day, a static site is just a page loading from cache.

Results

Before I jump to the exact WordPress hosting infrastructure, server configurations, and plugins, I think its helpful to share the results.

Here’s how fast WPBeginner home page loads on Pingdom from their Washington, DC server:

WPBeginner Homepage Pingdom

Depending on the time of day and location you check from, this result will vary anywhere from 400ms – 700ms range which is pretty fast for a homepage.

Here’s a test that I ran for a single post page since it has bigger images and more content:

WPBeginner Single Posts Page Speed Test from Pingdom

We also got a perfect score of “100” in Google page speed test for desktop. Although we do have some room for improvement on mobile score.

WPBeginner Google Page Speed Test

The results above are for cached pages which is what our readers and search engine bots get when they view our website. The perceived load time of WPBeginner is near instant (more on this later).

For the sake of comparison, here’s a speed test result for Gatsby’s homepage. This is a popular static site generator that a lot of developers are raving about:

Gatsby Homepage Pingdom

Here’s the speed test result of Netlify’s homepage, a popular static site host, that a lot of developers recommend. Notice that they have half the amount of requests, and their page size is 30% of WPBeginner, yet it still loads slower than our homepage.

Netlify Homepage Pingdom

The homepage speed of Contentful, the headless CMS which is “how enterprises deliver better digital experiences” is just not optimized at all. This was the slowest website we tested.

Contentful Homepage Pingdom

I am sharing these stats not to discredit the other frameworks, but rather to give perspective that not all new things are as shiny as they may seem.

WordPress with a proper hosting infrastructure and optimizations can be just as fast as any static site generator. Furthermore, no other platform will even come close to the level of flexibility that WordPress offers to business owners through its large ecosystem of plugins and themes.

WPBeginner Hosting Infrastructure

When it comes to website speed, nothing plays a more important role than your web hosting infrastructure.

As many of you already know, I have been a HostGator customer since 2007. I started the WPBeginner blog in 2009 on a small HostGator shared hosting account.

As our website grew, we upgraded to their VPS hosting and then dedicated servers.

Over the last decade, I have gotten a chance to work closely with many of their team members, and they have become an extended part of the WPBeginner family.

So when I took on the challenge to make WPBeginner faster than static site generators, I turned to them for help.

I shared my vision with their leadership team, and they offered to help me build one of a kind enterprise hosting setup for WPBeginner.

They put the best engineers from both Bluehost and HostGator team to work closely with me to make WPBeginner blazing fast.

Here’s an overview of what the WPBeginner hosting setup looks like:

WPBeginner Hosting Infrastructure

As you can see, this is a multi-server setup spread across two geographical regions (Texas and Utah). There are a total of 9 servers not including the load balancer cloud. Each server is a Xeon-D CPU with 8 cores (16 threads) with 32GB RAM and 2 x 1TB SSD (RAID setup).

We are using Google’s Cloud Load Balancing platform, so we can have seamless autoscaling and load balancing, worldwide.

Once the hardware was setup with proper data syncing in place, the Bluehost and HostGator team worked together to optimize the server configurations for WordPress. My hope is that some of these optimizations will soon make it into future WordPress hosting plans :)

Server Configuration Summary

Summarizing the server configurations of this complex setup in just a few paragraph is very tough, but I will try my best.

We are using Apache for our web server software because the team is more familiar with it. I won’t go into the NGINX vs Apache debate.

We are using PHP 7.2 along with PHP-FPM pools, so we can handle high loads of processes and requests. If your hosting company is not using PHP 7+, then you’re missing out on serious speed optimization.

We’re using Opcode caching with an advanced cache warmer to ensure that no real user should experience an uncached pageview.

We’re also using Object cache with memcache, so we can improve the response time for uncached page hits and other API response times in the WordPress admin area for logged-in users (our writers). Here’s a network load tab of our “All Posts” screen in the WordPress admin:

WPBeginner Post Edit Screen

To put in perspective, our admin area experience is now 2X faster than what we had previously.

For our database server, we switched from MySQL to MariaDB which is a clone of MySQL but faster and better. We also switched from HyperDB to LudicrousDB because it helps us improve our database replication, failover, and load balancing.

There’s also a lot of other configurations that helps us with performance and scalability such as HTTP/2 and HSTS for faster connection + encryption, ability to spin up additional servers in new regions in case of datacenter outage, etc.

I feel like I’m not doing justice to the amazing setup that the team has built, but please know that my core strength is marketing. Yes, I am a blogger who writes about WordPress, but a lot of the technical optimizations here are way above my pay-grade.

They were done by super smart engineers in Endurance team including David Collins (chief architect of Endurance / CTO of HostGator), Mike Hansen (core WordPress developer), and others whom I’ll thank in the credits section below.

CDN, WAF, and DNS

Aside from web hosting, the other areas that play a significant role in your website speed is your DNS provider, your content delivery network (aka CDN), and your web application firewall (WAF).

While I have it listed as three separate things, a lot of companies are now offering these solutions in a bundled plan such as Sucuri, Cloudflare, MaxCDN (StackPath), etc.

Since I want to have maximum control and spread the risk, I am using three separate companies to handle each part efficiently.

WPBeginner DNS is powered by DNS Made Easy (same company as Constellix). They are consistently ranked as the fastest DNS providers in the world. The advantage of DNS Made Easy is that I can do global traffic direction when a specific data center on my CDN or WAF isn’t working properly to ensure maximum uptime.

Our CDN is powered by MaxCDN (StackPath). They basically allow us to serve our static assets (images, CSS files, and JavaScripts) from their large network of servers across the world.

We’re using Sucuri as our web application firewall. Aside from blocking attacks, they also act as another layer of CDN, and their overall performance is just amazing. I believe they have the best WordPress firewall solution in the market.

When working on website speed optimizations, shaving off every millisecond matters. That’s why using these solution providers combined with our new web hosting infrastructure makes a huge difference.

To illustrate, here’s the waterfall breakdown of WPBeginner.com vs GatsbyJS.org vs CloudFlare.com:

Waterfall Breakdown of Requests on WPBeginner

Notice that WPBeginner’s DNS time, SSL time, Connect time, and Wait time are all top notch when compared to these other popular websites. Each of these improvements compound to deliver the best results.

Instant.page, Optimized Images, and Other Best Practices

One of the things you might have noticed is the near instant load time when you browse WPBeginner posts and pages.

Aside from all the things I mentioned above, we’re also cheating latency by using a script called instant.page which uses just-in-time preloading.

Basically before a user clicks on a link, they have to hover their mouse over that link. When a user has hovered for 65ms (very short period of time), one out of two will actually click on the link.

Instant.page script starts preloading that page at this moment, so when the user actually clicks the link a lot of the heavy lifting is already done. This makes the human brain perceives website load time as nearly instant.

To enable Instant.page on your site, you can simply install and activate the Instant Page WordPress plugin.

Instant Page Script

This script is pretty neat. I highly recommend checking out their website and clicking on the “test your clicking speed” button to see how it cheats the brain.

Update: I have disabled instant.page for now, and I’m going to be testing FlyingPages plugin in the near future. Gijo Varghese shared his new plugin with me in the WPBeginner Engage Facebook group, and it seems to combine the best of instant.page and quicklink script.

Optimizing Images for Web

While there are new image formats being developed such as webp, we’re not using them yet. Instead we ask all of our writers to optimize each image using the TinyPNG tool.

You can also automate the image compression using plugins like Optimole or EWWW Image Optimizer.

However, I personally prefer to have the team do this manually, so we’re not uploading large files on the server.

Currently, we’re not doing any lazy loading for images, but I do plan to add it in the near future now that Google has lazy loading support built-in to Chrome 76.

There’s also a ticket in WordPress core to add this feature on all sites (really hoping that this happens soon), so I don’t have to write a custom plugin.

Limiting HTTP Queries + Best Practices

Reduce cross-domain HTTP requests

Depending on the WordPress plugins you use, some will add additional CSS and JavaScript files on each page load. These additional HTTP requests can get out of control if you have a lot of plugins on your website.

For more details, see how WordPress plugins can affect your site load time.

Now before you jump to the wrong conclusion that too many WordPress plugins are bad, I want to let you know that there are 62 active plugins running on the WPBeginner website.

What you need to do is combine CSS and JavaScript files where possible to reduce HTTP requests. Some WordPress caching plugins like WP Rocket can do this automatically with their minification feature.

You can also follow the instructions in this article to do it manually which is what our team at WPBeginner has done.

Aside from HTTP requests that plugins and themes add, you also want to be mindful of other third-party scripts that you add on your website because each script will impact your website speed.

For example, if you are running a lot of advertising scripts or retargeting scripts, then they will slow down your site. You may want to use a tool like Google Tag Manager to conditionally load scripts only when they’re needed.

If you’re an ad-supported website like TechCrunch or TheNextWeb, then there’s very little you can do about this since removing ads isn’t an option.

Luckily, WPBeginner doesn’t rely on third-party ad scripts to make money. Want to see how WPBeginner makes money? See my blog post on WPBeginner income.

Lessons Learned (so far) + My Final Thoughts

This is a brand new hosting infrastructure, and I’m sure there are tons of lessons I will be learning overtime.

So far I love the speed improvements because it has helped us boost our SEO rankings, and our admin area is much faster.

With the new multi-server setup, we introduced a new deployment workflow to bring WPBeginner up to par with the rest of Awesome Motive product sites.

What this means is that we now have proper version controlling built-in, and there are measures put in place to stop me from being reckless (i.e adding plugins without proper testing, updating plugins from the dashboard without testing, etc).

These changes also set the path for me to finally step out of development and hand over the reigns of WPBeginner site to our dev team.

I have been resisting this for years, but I think the time is coming, and I just need to accept it.

The new setup does not have cPanel or WHM, so this makes me practically useless anyways since I’m not very fluent with command line anymore.

So far we have learnt two big lessons:

First, updating WordPress isn’t as straight forward due to server sync / replication. When we upgraded my personal blog (SyedBalkhi.com) to WordPress 5.2, the update files didn’t sync properly on one of the web nodes, and debugging took much longer than anticipated. We’re working on building a better build / testing process for this.

Second, we need to have better communication across teams because we had a minor crisis with load balancer misconfigurations which resulted in some downtime. To make it worst, I was on a transatlantic flight on Turkish Airlines, and the WiFi wasn’t working.

Luckily everything got sorted thanks to the quick response time by the hosting team, but this helped us create several new Standard Operating Procedures (SOPs) to better handle the incident in the future.

Overall I’m very happy with the setup, and I know that some of the caching configurations / optimizations that were made for WPBeginner will become a standard part of HostGator Cloud and Bluehost WordPress hosting plans.

I think this should go without saying that if you’re just starting a website, blog, or an online store, then you DO NOT need this sophisticated enterprise setup.

I always recommend that you start small with HostGator shared or Bluehost shared plans like I did, and then upgrade your hosting infrastructure as your business grows.

You can apply a lot of the optimizations that I shared above on your current WordPress hosting plans.

For example, Bluehost standard plan already comes with a built-in caching plugin that you can use, and they offer PHP 7 by default as well.

You can combine that with a CDN + WAF like Sucuri to significantly speed up your website.

Now if you are a mid-market / enterprise company who wants a similar hosting setup, then please reach out to me via our contact form. I can help point you in the right direction.

Special Thanks + Credits

Thank you HostGator and Bluehost

While in the article above, I have given tons of shout out to HostGator and Bluehost brands, I want to take a moment to recognize and appreciate the individual people that worked behind the scenes to make it happen.

First, I want to say thank you to the Endurance leadership team Suhaib, Mitch, John Orlando, Mike Lillie, and Brady Nord for agreeing to help me with the challenge.

I also want to thank Mike Hansen, David Collins, Rick Radinger, Chris Miles, David Ryan, Jesse Cook, David Foster, Micah Wood, William Earnhardt, Robin Mendieta, Rod Johnson, Alfred Najem, and others in the data center team for actually doing the hard work and making it happen.

I want to give a special shout out to Steven Job (founder of DNSMadeEasy) for quickly answering my questions and helping me better understand some settings. Also want to give a shout out to Tony Perez and Daniel Cid at Sucuri for always having my back.

Last but not least, I want to give special recognition to Chris Christoff. He’s the co-founder of MonsterInsights, and he was kind enough to help me with a lot of the testing and deployment.

I really hope that you found this behind the scenes case study about WPBeginner hosting infrastructure to be helpful. You may also want to see our ultimate guide on how to speed up WordPress which is way more beginner friendly.

Bonus: Here are the best WordPress plugins and tools that I recommend for all WordPress sites.

If you liked this article, then please subscribe to our YouTube Channel for WordPress video tutorials. You can also find us on Twitter and Facebook.

The post How We Made WordPress Faster than Static Site Generators (Case Study – Speeding up WPBeginner) appeared first on WPBeginner.

WPBeginner

Changes in Licensing and Site Roles When Upgrading Tableau Server

site role changes in Tableau Server upgrade

It can be a challenge to know exactly what happens in regard to Tableau Server licensing and site roles when you plan to upgrade, especially from a pre-2018.1 version. Here, I tested all potential site roles from upgrading Tableau Server 10.5 to 2018.3.

Starting with a clean install of Tableau Server 10.5 with user-based licensing, I added Active Directory users by syncing the SALES – All group with AD (easy to populate the server for testing). This added 25 users with Interactor Site Roles:

site roles in Tableau Server

I set one of each Site Role for the top six users. These included:

  • Server Administrator
  • Site Administrator
  • Publisher
  • Interactor
  • Viewer
  • Unlicensed

Tableau Server site roles

Then I backed up the 10.5 Server with the tabadmin backup -v C:5_backup command, which verifies the repository during the back-up and places it under the C:\ directory. I named it 105_backup.tsbak:

Tableau Server site roles code

Installing Tableau Server 2018.3

To upgrade from a non-TSM version to one that uses Tableau Server Manager, I first had to uninstall Tableau Server 10.5 from Programs and Features but keep the existing folder structure. Then I installed Tableau Server 2018.3 and chose the upgrade option. Tableau Server uses the existing Tableau Server folders and files during the upgrade process:

Tableau Server site roles code

Once that was finished, I logged into the TSM web UI as a local administrator, started Tableau Server and waited for all those little green checkmarks. I opened a new browser tab and navigated to my Tableau Server URL and logged in with the Server Administrator account.

The licensing in 10.5 turns into Explorer licenses in 2018.3. You can see that I now have 30 Explorer licenses with 24 being used, one user is using the Viewer level license, and one user is Unlicensed:

Tableau Server licenses

Everything stays the same with the site roles, except Publisher becomes Explorer (can publish) and Interactor becomes Explorer:

Tableau Server site roles

Server Administrators are still under the Explorer license since we haven’t purchased Creator licenses yet:

site role updates in Tableau Server

Above: Explorer – Server Administrator (Tableau Server only; not applicable to Tableau Online)

If Explorer is the highest license type activated on the server when a new server administrator user is created, the user’s site role is Server Administrator; however, the user will not have the connecting and publishing capabilities that come only with the Creator license.

A Word on the Explorer License

With the Explorer license, a Server Administrator has unrestricted access to the configuration settings for the Tableau Server browser environment, all sites on the server, users and groups, and all content assets, such as projects, data sources (including connection information) and workbooks.

However, with the Explorer license, a Server Administrator cannot connect to Tableau-published data sources or external data from the browser, Tableau Desktop or Tableau Prep; they cannot create and publish new data sources; and they cannot author or publish workbooks.

To test the change in Site Roles with a new Creator license, I deactivated the existing license from the TSM web UI:

licensing in Tableau Server upgrade

Then I activated the 30 new Creator user licenses I had and checked to see the changes to the user’s Site Roles in Tableau Server:

site role changes in Tableau Server upgrade

As you can see, the Server Administrators were automatically given a Creator license and the Creator Server Administrator Site Role. The rest of the user’s Site Roles stayed the same, but I have the option to give any of them Creator and the means to use Tableau Desktop and Tableau Prep.

New Licenses

I now have the option to give any of the users a Creator license and the means to use Tableau Desktop and Tableau Prep. When purchasing Creator licenses for Tableau Server, you will receive two different licenses: one for your Desktop/Prep users and one for the Tableau Server Admin to activate on Tableau Server.

TC****** = Tableau Desktop/Prep Creator Licenses will start with TC

TS****** = Tableau Server Creator Licenses will start with TS

The post Changes in Licensing and Site Roles When Upgrading Tableau Server appeared first on InterWorks.

InterWorks

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:

  • sudo ufw allow mysql

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:

  • sudo mysql

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:

  • mysql -u root -p

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:

  • FLUSH PRIVILEGES;

Then exit the MySQL prompt by typing:

  • exit

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:

  • exit

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

  • ssh sammy@web_server_ip

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:

  • sudo apt update

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:

  • status

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: wordpressuser@203.0.113.111 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:

  • exit

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:

  • tar xzvf latest.tar.gz

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.

DigitalOcean Community Tutorials