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 To Install the Latest MySQL on Debian 10

Introduction

MySQL is a prominent open source database management system used to store and retrieve data for a wide variety of popular applications. MySQL is the M in the LAMP stack, a commonly used set of open source software that also includes Linux, the Apache web server, and the PHP programming language.

In Debian 10, MariaDB, a community fork of the MySQL project, is packaged as the default MySQL variant. While MariaDB works well in most cases, if you need features found only in Oracle’s MySQL, you can install and use packages from a repository maintained by the MySQL developers.

To install the latest version of MySQL, we’ll add this repository, install the MySQL software itself, secure the install, and finally we’ll test that MySQL is running and responding to commands.

Prerequisites

Before starting this tutorial, you will need:

Step 1 — Adding the MySQL Software Repository

The MySQL developers provide a .deb package that handles configuring and installing the official MySQL software repositories. Once the repositories are set up, we’ll be able to use Debian’s standard apt command to install the software.

Before we do this, we need to install the prerequisite GnuPG package, an open-source implementation of the OpenPGP standard.

Let’s begin by updating the local package index to reflect the latest upstream changes:

  • sudo apt update

Then, install the gnupg package:

  • sudo apt install gnupg

After confirming the installation, apt will install gnupg and its dependencies.

Next, we’ll download the MySQL .deb package with wget and then install it using the dpkg command.

Load the MySQL download page in your web browser. Find the Download button in the lower-right corner and click through to the next page. This page will prompt you to log in or sign up for an Oracle web account. We can skip that and instead look for the link that says No thanks, just start my download. Right-click the link and select Copy Link Address (this option may be worded differently, depending on your browser).

Now we’re going to download the file. On your server, move to a directory you can write to. Download the file using wget, remembering to paste the address you just copied in place of the highlighted portion below:

  • cd /tmp
  • wget https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

The file should now be downloaded in our current directory. List the files to make sure:

  • ls

You should see the filename listed:

Output
mysql-apt-config_0.8.13-1_all.deb . . .

Now we’re ready to install:

  • sudo dpkg -i mysql-apt-config*

dpkg is used to install, remove, and inspect .deb software packages. The -i flag indicates that we’d like to install from the specified file.

During the installation, you’ll be presented with a configuration screen where you can specify which version of MySQL you’d prefer, along with an option to install repositories for other MySQL-related tools. The defaults will add the repository information for the latest stable version of MySQL and nothing else. This is what we want, so use the down arrow to navigate to the Ok menu option and hit ENTER.

The package will now finish adding the repository. Refresh your apt package cache to make the new software packages available:

  • sudo apt update

Now that we’ve added the MySQL repositories, we’re ready to install the actual MySQL server software. If you ever need to update the configuration of these repositories, just run sudo dpkg-reconfigure mysql-apt-config, select new options, and then sudo apt-get update to refresh your package cache.

Step 2 — Installing MySQL

Having added the repository and with our package cache freshly updated, we can now use apt to install the latest MySQL server package:

  • sudo apt install mysql-server

apt will look at all available mysql-server packages and determine that the MySQL provided package is the newest and best candidate. It will then calculate package dependencies and ask you to approve the installation. Type y then ENTER. The software will install.

You will be asked to set a root password during the configuration phase of the installation. Choose and confirm a secure password to continue. Next, a prompt will appear asking for you to select a default authentication plugin. Read the display to understand the choices. If you are not sure, choosing Use Strong Password Encryption is safer.

MySQL should be installed and running now. Let’s check using systemctl:

  • sudo systemctl status mysql
● mysql.service - MySQL Community Server    Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)    Active: active (running) since Thu 2019-07-25 17:20:12 UTC; 3s ago      Docs: man:mysqld(8)            http://dev.mysql.com/doc/refman/en/using-systemd.html   Process: 2673 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)  Main PID: 2709 (mysqld)    Status: "Server is operational"     Tasks: 39 (limit: 4915)    Memory: 378.4M    CGroup: /system.slice/mysql.service            └─2709 /usr/sbin/mysqld  Jul 25 17:20:10 sammy systemd[1]: Starting MySQL Community Server... Jul 25 17:20:12 sammy systemd[1]: Started MySQL Community Server. 

The Active: active (running) line means MySQL is installed and running. Now we’ll make the installation a little more secure.

Step 3 — Securing MySQL

MySQL comes with a command we can use to perform a few security-related updates on our new install. Let’s run it now:

  • mysql_secure_installation

This will ask you for the MySQL root password that you set during installation. Type it in and press ENTER. Now we’ll answer a series of yes or no prompts. Let’s go through them:

First, we are asked about the validate password plugin, a plugin that can automatically enforce certain password strength rules for your MySQL users. Enabling this is a decision you’ll need to make based on your individual security needs. Type y and ENTER to enable it, or just hit ENTER to skip it. If enabled, you will also be prompted to choose a level from 0–2 for how strict the password validation will be. Choose a number and hit ENTER to continue.

Next you’ll be asked if you want to change the root password. Since we just created the password when we installed MySQL, we can safely skip this. Hit ENTER to continue without updating the password.

The rest of the prompts can be answered yes. You will be asked about removing the anonymous MySQL user, disallowing remote root login, removing the test database, and reloading privilege tables to ensure the previous changes take effect properly. These are all a good idea. Type y and hit ENTER for each.

The script will exit after all the prompts are answered. Now our MySQL installation is reasonably secured. Let’s test it again by running a client that connects to the server and returns some information.

Step 4 – Testing MySQL

mysqladmin is a command line administrative client for MySQL. We’ll use it to connect to the server and output some version and status information:

  • mysqladmin -u root -p version

The -u root portion tells mysqladmin to log in as the MySQL root user, -p instructs the client to ask for a password, and version is the actual command we want to run.

The output will let us know what version of the MySQL server is running, its uptime, and some other status information:

Output
mysqladmin Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 8.0.17 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 3 min 9 sec Threads: 2 Questions: 10 Slow queries: 0 Opens: 128 Flush tables: 3 Open tables: 48 Queries per second avg: 0.052

This output confirms that you’ve successfully installed and secured the latest MySQL server.

Conclusion

You’ve now installed the latest stable version of MySQL, which should work for many popular applications.

DigitalOcean Community Tutorials

How To Deploy an ASP.NET Core Application with MySQL Server Using Nginx on Ubuntu 18.04

The author selected the Open Source Initiative to receive a donation as part of the Write for DOnations program.

Introduction

ASP.NET Core is a high-performant, open-source framework for building modern web applications, meant to be a more modular version of Microsoft’s ASP.NET Framework. Released in 2016, it can run on several operating systems such as Linux and macOS. This enables developers to target a particular operating system for development based on design requirements. With ASP.NET Core, a developer can build any kind of web application or service irrespective of the complexity and size. Developers can also make use of Razor pages to create page-focused design working on top of the traditional Model-View-Controller (MVC) pattern.

ASP.NET Core provides the flexibility to integrate with any front-end frameworks to handle client-side logic or consume a web service. You could, for example, build a RESTful API with ASP.NET Core and easily consume it with JavaScript frameworks such as Angular, React, and Vue.js.

In this tutorial you’ll set up and deploy a production-ready ASP.NET Core application with a MySQL Server on Ubuntu 18.04 using Nginx. You will deploy a demo ASP.NET Core application similar to the application from Microsoft’s documentation and hosted on GitHub. Once deployed, the demo application will allow you to create a list of movies and store it in the database. You’ll be able to create, read, update, and delete records from the database. You can use this tutorial to deploy your own ASP.NET Core application instead; it’s possible you’ll have to implement extra steps that include generating a new migration file for your database.

Prerequisites

You will need the following for this tutorial:

Step 1 — Installing .NET Core Runtime

A .NET Core runtime is required to successfully run a .NET Core application, so you’ll start by installing this to your machine. First, you need to register the Microsoft Key and product repository. After that, you will install the required dependencies.

First, logged in as your new created user, make sure you’re in your root directory:

  • cd ~

Next, run the following command to register the Microsoft key and product repository:

  • wget -q https://packages.microsoft.com/config/ubuntu/18.04/packages-microsoft-prod.deb

Use dpkg with the -i flag to install the specified file:

  • sudo dpkg -i packages-microsoft-prod.deb

To facilitate the installation of other packages required for your application, you will install the universe repository with the following command:

  • sudo add-apt-repository universe

Next install the apt-transport package to allow the use of repositories accessed via the HTTP Secure protocol:

  • sudo apt install apt-transport-https

Now, run the following command to download the packages list from the repositories and update them to get information on the newest versions of packages and their dependencies:

  • sudo apt update

Finally, you can install the .NET runtime SDK with:

  • sudo apt install dotnet-sdk-2.2

You will be prompted with the details of the size of additional files that will be installed. Type Y and hit ENTER to continue.

Now that you’re done installing the .NET Core runtime SDK on the server, you are almost ready to download the demo application from GitHub and set up the deployment configuration. But first, you’ll create the database for the application.

Step 2 — Creating a MySQL User and Database

In this section, you will create a MySQL server user, create a database for the application, and grant all the necessary privileges for the new user to connect to the database from your application.

To begin, you need to access the MySQL client using the MySQL root account as shown here:

  • mysql -u root -p

You will be prompted to enter the root account password, set up during the prerequisite tutorial.

Next, create a MySQL database for the application with:

  • CREATE DATABASE MovieAppDb;

You will see the following output in the console:

Output
Query OK, 1 row affected (0.03 sec)

You’ve now created the database successfully. Next, you will create a new MySQL user, associate them with the newly created database, and grant them all privileges.

Run the following command to create the MySQL user and password. Remember to change the username and password to something more secure:

  • CREATE USER 'movie-admin'@'localhost' IDENTIFIED BY 'password';

You will see the following output:

Output
Query OK, 0 rows affected (0.02 sec)

To access a database or carry out a specific action on it, a MySQL user needs the appropriate permission. At the moment movie-admin does not have the appropriate permission over the application database.

You will change that by running the following command to grant access to movie-admin on MovieAppDb:

  • GRANT ALL PRIVILEGES ON MovieAppDb.* TO 'movie-admin'@'localhost';

You will see the following output:

Output
Query OK, 0 rows affected (0.01 sec)

Now, you can reload the grant tables by running the following command to apply the changes that you just made using the flush statement:

  • FLUSH PRIVILEGES;

You will see the following output:

Output
Query OK, 0 rows affected (0.00 sec)

You are done creating a new user and granting privileges. To test if you are on track, exit the MySQL client:

  • quit;

Log in again, using the credentials of the MySQL user you just created and enter the appropriate password when prompted:

  • mysql -u movie-admin -p

Check to be sure that the user movie-admin can access the created database, check with:

  • SHOW DATABASES;

You will see the MovieAppDb table listed in the output:

Output
+--------------------+ | Database | +--------------------+ | MovieAppDb | | information_schema | +--------------------+ 2 rows in set (0.01 sec)

Now, exit the MySQL client:

  • quit;

You’ve created a database, made a new MySQL user for the demo application, and granted the newly created user the right privileges to access the database. In the next section, you will start setting up the demo application.

Step 3 — Setting Up the Demo App and Database Credentials

As stated earlier, you’ll deploy an existing ASP.NET Core application. This application was built to create a movie list and it uses the Model-View-Controller design pattern to ensure a proper structure and separation of concerns. To create or add a new movie to the list, the user will populate the form fields with the appropriate details and click on the Create button to post the details to the controller. The controller at this point will receive a POST HTTP request with the submitted details and persist the data in the database through the model.

You will use Git to pull the source code of this demo application from GitHub and save it in a new directory. You could also download an alternate application here if you will be deploying a different application.

To begin, create a new directory named movie-app from the terminal by using the following command:

  • sudo mkdir -p /var/www/movie-app

This will serve as the root directory for your application. Next, change the folder owner and group in order to allow a non-root user account to work with the project files:

  • sudo chown sammy:sammy /var/www/movie-app

Replace sammy with your sudo non-root username.

Now, you can move into the parent directory and clone the application on GitHub:

  • cd /var/www
  • git clone https://github.com/do-community/movie-app-list.git movie-app

You will see the following output:

Output
Cloning into 'movie-app'... remote: Enumerating objects: 91, done. remote: Counting objects: 100% (91/91), done. remote: Compressing objects: 100% (73/73), done. remote: Total 91 (delta 13), reused 91 (delta 13), pack-reused 0 Unpacking objects: 100% (91/91), done.

You have successfully cloned the demo application from GitHub, so the next step will be to create a successful connection to the application database. You will do this by editing the ConnectionStrings property within the appsettings.json file and add the details of the database.

Change directory into the application:

  • cd movie-app

Now open the file for editing:

  • sudo nano appsettings.json

Add your database credentials:

appsettings.json
{   "Logging": {     "LogLevel": {       "Default": "Warning"     }   },   "AllowedHosts": "*",   "ConnectionStrings": {     "MovieContext": "Server=localhost;User Id=movie-admin;Password=password;Database=MovieAppDb"   } } 

With this in place, you’ve successfully created a connection to your database. Now press CTRL+X to save your changes to the file and type Y to confirm. Then hit ENTER to exit the page.

ASP.NET Core applications use a .NET standard library named Entity Framework (EF) Core to manage interaction with the database. Entity Framework Core is a lightweight, cross-platform version of the popular Entity Framework data access technology. It is an object-relational mapper (ORM) that enables .NET developers to work with a database using any of the database providers, such as MySQL.

You can now update your database with the tables from the cloned demo application. Run the following command for that purpose:

  • dotnet ef database update

This will apply an update to the database and create the appropriate schemas.

Now, to build the project and all its dependencies, run the following command:

  • dotnet build

You will see output similar to:

Output
Microsoft (R) Build Engine version 16.1.76+g14b0a930a7 for .NET Core Copyright (C) Microsoft Corporation. All rights reserved. Restore completed in 95.09 ms for /var/www/movie-app/MvcMovie.csproj. MvcMovie -> /var/www/movie-app/bin/Debug/netcoreapp2.2/MvcMovie.dll MvcMovie -> /var/www/movie-app/bin/Debug/netcoreapp2.2/MvcMovie.Views.dll Build succeeded. 0 Warning(s) 0 Error(s) Time Elapsed 00:00:01.91

This will build the project and install any third-party dependencies listed in the project.assets.json file but the application won’t be ready for production yet. To get the application ready for deployment, run the following command:

  • dotnet publish

You will see the following:

Output
Microsoft (R) Build Engine version 16.1.76+g14b0a930a7 for .NET Core Copyright (C) Microsoft Corporation. All rights reserved. Restore completed in 89.62 ms for /var/www/movie-app/MvcMovie.csproj. MvcMovie -> /var/www/movie-app/bin/Debug/netcoreapp2.2/MvcMovie.dll MvcMovie -> /var/www/movie-app/bin/Debug/netcoreapp2.2/MvcMovie.Views.dll MvcMovie -> /var/www/movie-app/bin/Debug/netcoreapp2.2/publish/

This will pack and compile the application, read through its dependencies, publish the resulting set of files into a folder for deployment, and produce a cross-platform .dll file that uses the installed .NET Core runtime to run the application.

By installing dependencies, creating a connection to the database, updating the database with the necessary tables, and publishing it for production, you’ve completed the setup for this demo application. In the next step you will configure the web server to make the application accessible and secure at your domain.

Step 4 — Configuring the Web Server

By now, having followed the How To Secure Nginx with Let’s Encrypt tutorial, you’ll have a server block for your domain at /etc/nginx/sites-available/your_domain with the server_name directive already set appropriately. In this step, you will edit this server block to configure Nginx as a reverse proxy for your application. A reverse proxy is a server that sits in front of web servers and forwards every web browser’s request to those web servers. It receives all requests from the network and forwards them to a different web server.

In the case of an ASP.NET Core application, Kestrel is the preferred web server that is included with it by default. It is great for serving dynamic content from an ASP.NET Core application as it provides better request-processing performance and was designed to make ASP.NET as fast as possible. However, Kestrel isn’t considered a full-featured web server because it can’t manage security and serve static files, which is why it is advisable to always run it behind a web server.

To begin, ensure that you are within the root directory of your server:

  • cd ~

Open the server block for editing with:

  • sudo nano /etc/nginx/sites-available/your_domain

As detailed in the Step 4 of the How To Secure Nginx with Let’s Encrypt tutorial, if you selected option 2, Certbot will automatically configure this server block in order to redirect HTTP traffic to HTTPS with just a few modifications.

Continue with the configuration by editing the first two blocks in the file to reflect the following:

/etc/nginx/sites-available/your-domain
server {      server_name your-domain  www.your-domain;     location / {      proxy_pass http://localhost:5000;      proxy_http_version 1.1;      proxy_set_header Upgrade $  http_upgrade;      proxy_set_header Connection keep-alive;      proxy_set_header Host $  host;      proxy_cache_bypass $  http_upgrade;      proxy_set_header X-Forwarded-For $  proxy_add_x_forwarded_for;      proxy_set_header X-Forwarded-Proto $  scheme;     }  listen [::]:443 ssl ipv6only=on; # managed by Certbot listen 443 ssl; # managed by Certbot ssl_certificate /etc/letsencrypt/live/your-domain/fullchain.pem; # managed by Certbot ssl_certificate_key /etc/letsencrypt/live/your-domain/privkey.pem; # managed by Certbot include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot   } ... 

The configuration in this server block will instruct Nginx to listen on port 443, which is the standard port for websites that use SSL. Furthermore, Nginx will accept public traffic on port 443 and forward every matching request to the built-in Kestrel server at http://localhost:5000.

Finally, following the server block you just edited in the file, ensure that the second server block looks like so:

/etc/nginx/sites-available/your-domain
 ... server { if ($  host = www.your-domain) {     return 301 https://$  host$  request_uri; } # managed by Certbot   if ($  host = your-domain) {     return 301 https://$  host$  request_uri; } # managed by Certbot       listen 80;     listen [::]:80;      server_name your-domain  www.your-domain; return 404; # managed by Certbot } 

This server block will redirect all requests to https://your-domain and https://www.your-domain to a secure HTTPS access.

Next, force Nginx to pick up the changes you’ve made to the server block by running:

  • sudo nginx -s reload

With the Nginx configuration successfully completed, the server is fully set up to forward all HTTPS requests made to https://your-domain on to the ASP.NET Core app running on Kestrel at http://localhost:5000. However, Nginx isn’t set up to manage the Kestrel server process. To handle this and ensure that the Kestrel process keeps running in the background, you will use systemd functionalities.

Systemd files will allow you to manage a process by providing start, stop, restart, and log functionalities once you create a process of work called a unit.

Move into the systemd directory:

  • cd /etc/systemd/systems

Create a new file for editing:

  • sudo nano movie.service

Add the following content to it:

movie.service
[Unit] Description=Movie app  [Service] WorkingDirectory=/var/www/movie-app ExecStart=/usr/bin/dotnet /var/www/movie-app/bin/Debug/netcoreapp2.2/publish/MvcMovie.dll Restart=always RestartSec=10 SyslogIdentifier=movie User=sammy Environment=ASPNETCORE_ENVIRONMENT=Production Environment=DOTNET_PRINT_TELEMETRY_MESSAGE=false  [Install] WantedBy=multi-user.target 

The configuration file specifies the location of the project’s folder with WorkingDirectory and the command to execute at the start of the process in ExecStart. In addition, you’ve used the RestartSec directive to specify when to restart the systemd service if the .NET runtime service crashes.

Now save the file and enable the new movie service created with:

  • sudo systemctl enable movie.service

After that, proceed to start the service and verify that it’s running by starting the service:

  • sudo systemctl start movie.service

Then check its status:

  • sudo systemctl status movie.service

You will see the following output:

Output
movie.service - Movie app Loaded: loaded (/etc/systemd/system/movie.service; enabled; vendor preset: enabled) Active: active (running) since Sun 2019-06-23 04:51:28 UTC; 11s ago Main PID: 6038 (dotnet) Tasks: 16 (limit: 1152) CGroup: /system.slice/movie.service └─6038 /usr/bin/dotnet /var/www/movie-app/bin/Debug/netcoreapp2.2/publish/MvcMovie.dll

This output gives you an overview of the current status of the movie.service created to keep your app running. It indicates that the service is enabled and currently active.

Navigate to https://your-domain from your browser to run and test out the application.

You’ll see the home page for the demo application—Movie List Application.

Movie list application

With the reverse proxy configured and Kestrel managed through systemd, the web app is fully configured and can be accessed from a browser.

Conclusion

In this tutorial, you deployed an ASP.NET Core application to an Ubuntu server. To persist and manage data, you installed and used MySQL server and used the Nginx web server as a reverse proxy to serve your application.

Beyond this tutorial, if you’re interested in building an interactive web application using C# instead of Javascript you could try a web UI framework by Microsoft called Blazor. It is an event-driven component-based web UI for implementing logic on the client side of an ASP.NET Core application.

If you wish to deploy your own application, you’ll need to consider other required procedures to deploy your app. The complete source code for this demo application can be found here on GitHub.

DigitalOcean Community Tutorials

How To Install an Apache, MySQL, and PHP (FAMP) Stack on FreeBSD 12.0

The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.

Introduction

A FAMP stack, which is similar to a LAMP stack on Linux, is a group of open source software that is typically installed together to enable a FreeBSD server to host dynamic websites and web apps. FAMP is an acronym that stands for FreeBSD (operating system), Apache (web server), MySQL (database server), and PHP (to process dynamic PHP content).

In this guide, we’ll get a FAMP stack installed on a FreeBSD 12.0 cloud server using pkg, the FreeBSD package manager.

Prerequisites

Before you begin this guide you’ll need the following:

  • A FreeBSD 12.0 Droplet.
  • Access to a user with root privileges (or allowed by using sudo) in order to make configuration changes.
  • A firewall configured using this tutorial on Recommended Steps For New FreeBSD 12.0 Servers. Ensure you open ports 80 and 443 as part of your setup.
  • Familiarity with the CLI (Command Line Interface) is recommended. FreeBSD’s vi editor has almost identical syntax as vim.

Step 1 — Installing Apache

The Apache web server is currently the most popular web server in the world, which makes it a great choice for hosting a website.

You can install Apache using FreeBSD’s package manager, pkg. A package manager allows you to install most software pain-free from a repository maintained by FreeBSD. You can learn more about how to use pkg here.

To install Apache 2.4 using pkg, use this command:

  • sudo pkg install apache24

Enter y at the confirmation prompt to install Apache and its dependencies.

To enable Apache as a service, add apache24_enable="YES" to the /etc/rc.conf file. You’ll use the sysrc command to do just that:

  • sudo sysrc apache24_enable="YES"

Now start Apache:

  • sudo service apache24 start

To check that Apache has started you can run the following command:

  • sudo service apache24 status

As a result you’ll see something similar to:

Output
apache24 is running as pid 20815.

You can do a spot check right away to verify that everything went as planned by visiting your server’s public IP address in your web browser. See the note under the next heading to find out what your public IP address is, if you do not have this information already:

http://your_server_IP_address/ 

You will see the default FreeBSD Apache web page, which is there for testing purposes. You’ll see: It Works!, which indicates that your web server is correctly installed.

How To find Your Server’s Public IP Address

If you do not know what your server’s public IP address is, there are a number of ways that you can find it. Usually, this is the address you use to connect to your server through SSH.

If you are using DigitalOcean, you may look in the Control Panel for your server’s IP address. You may also use the DigitalOcean Metadata service, from the server itself, with this command: curl -w "\n" http://169.254.169.254/metadata/v1/interfaces/public/0/ipv4/address.

A more universal way to look up the IP address is to use the ifconfig command, on the server itself. The ifconfig command will print out information about your network interfaces. In order to narrow down the output to only the server’s public IP address, use this command (note that the highlighted part is the name of the network interface, and may vary):

  • ifconfig vtnet0 | grep "inet " | awk '{ print $ 2; exit }'

You could also use curl to contact an outside party, like icanhazip, to tell you how it sees your server. This is done by asking a specific server what your IP address is:

  • curl http://icanhazip.com

Now that you have the public IP address, you may use it in your web browser’s address bar to access your web server.

Step 2 — Installing MySQL

Now that you have your web server up and running, it is time to install MySQL, the relational database management system. The MySQL server will organize and provide access to databases where your server can store information.

Again, you can use pkg to acquire and install your software.

To install MySQL 8.0 using pkg, use this command:

  • sudo pkg install mysql80-server

Enter y at the confirmation prompt to install the MySQL server and client packages.

To enable MySQL server as a service, add mysql_enable="YES" to the /etc/rc.conf file. You can us the sysrc command to do just that:

  • sudo sysrc mysql_enable="YES"

Now start the MySQL server with the following command:

  • sudo service mysql-server start

You can verify the service is up and running:

  • sudo service mysql-server status

You’ll read something similar to the following:

Output
mysql is running as pid 21587.

Now that your MySQL database is running, you will want to run a simple security script that will remove some dangerous defaults and slightly restrict access to your database system. Start the interactive script by running this command:

  • sudo mysql_secure_installation

The prompt will ask you if you want to set a password. Since you just installed MySQL, you most likely won’t have one, so type Y and follow the instructions:

 Would you like to setup VALIDATE PASSWORD component?  Press y|Y for Yes, any other key for No: y  There are three levels of password validation policy:  LOW    Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file  Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0 Please set the password for root here.  New password:  password  Re-enter new password:  password  Estimated strength of the password: 50 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y 

For the rest of the questions, you should hit the y key at each prompt to accept the recommended safe values. This will remove some sample users and databases, disable remote root logins, and load these new rules so that MySQL immediately respects the changes you’ve made.

At this point, your database system is now set up and you can move on to installing PHP.

Step 3 — Installing PHP

PHP is the component of your setup that will process code to display dynamic content. It can run scripts, connect to MySQL databases to get information, and hand the processed content over to the web server to display.

You can once again leverage the pkg system to install your components. You’re going to include the mod_php, php-mysql, and php-mysqli package as well.

To install PHP 7.3 with pkg, run this command:

  • sudo pkg install php73 php73-mysqli mod_php73

Enter y at the confirmation prompt. This installs the php73, mod_php73, and php73-mysqli packages.

Now copy the sample PHP configuration file into place with this command:

  • sudo cp /usr/local/etc/php.ini-production /usr/local/etc/php.ini

Now run the rehash command to regenerate the system’s cached information about your installed executable files:

  • rehash

Before using PHP, you must configure it to work with Apache.

Installing PHP Modules (Optional)

To enhance the functionality of PHP, you can optionally install some additional modules.

To see the available options for PHP 7.3 modules and libraries, you can type this:

pkg search php73 

The results will be mostly PHP 7.3 modules that you can install:

Output
php73-7.3.5 PHP Scripting Language php73-aphpbreakdown-2.2.2 Code-Analyzer for PHP for Compatibility Check-UP php73-aphpunit-1.8 Testing framework for unit tests php73-bcmath-7.3.5 The bcmath shared extension for php php73-brotli-0.6.2 Brotli extension for PHP php73-bsdconv-11.5.0 PHP wrapper for bsdconv php73-bz2-7.3.5 The bz2 shared extension for php php73-calendar-7.3.5 The calendar shared extension for php php73-composer-1.8.4 Dependency Manager for PHP php73-ctype-7.3.5 The ctype shared extension for php php73-curl-7.3.5 The curl shared extension for php php73-dba-7.3.5 The dba shared extension for php php73-deployer-6.4.3 Deployment tool for PHP php73-dom-7.3.5 The dom shared extension for php ...

To get more information about what each module does, you can either search the internet or you can look at the long description of the package by typing:

  • pkg search -f package_name

There will be a lot of output, with one field called Comment which will have an explanation of the functionality that the module provides.

For example, to find out what the php73-calendar package does, you could type this:

  • pkg search -f php73-calendar

Along with a large amount of other information, you’ll find something that looks like this:

Output
php73-calendar-7.3.5 Name : php73-calendar Version : 7.3.5 ... Comment : The calendar shared extension for php ...

If, after researching, you decide that you would like to install a package, you can do so by using the pkg install command.

For example, if you decide that php73-calendar is something that you need, you could type:

  • sudo pkg install php73-calendar

If you want to install more than one module at a time, you can do that by listing each one, separated by a space, following the pkg install command, like this:

  • sudo pkg install package1 package2 ...

Step 4 — Configuring Apache to Use PHP Module

Apache HTTP has a dedicated directory to write configuration files into it for specific modules. You will write one of those configuration files for Apache HTTP to “speak” PHP.

  • sudo vi /usr/local/etc/apache24/modules.d/001_mod-php.conf

Add the following lines to that file:

/usr/local/etc/apache24/modules.d/001_mod-php.conf
<IfModule dir_module>     DirectoryIndex index.php index.html     <FilesMatch "\.php$  ">         SetHandler application/x-httpd-php     </FilesMatch>     <FilesMatch "\.phps$  ">         SetHandler application/x-httpd-php-source     </FilesMatch> </IfModule> 

Now check Apache’s HTTP configuration is in good condition:

  • sudo apachectl configtest

You’ll see the following output:

Output
Performing sanity check on apache24 configuration: Syntax OK

Because you’ve made configuration changes in Apache you have to restart the service for those to be applied. Otherwise Apache will still work with the prior configuration.

  • sudo apachectl restart

Now you can move on to testing PHP on your system.

Step 5 — Testing PHP Processing

In order to test that your system is configured properly for PHP, you can create a very basic PHP script.

You’ll call this script info.php. In order for Apache to find the file and serve it correctly, it must be saved under a specific directory—DocumentRoot—which is where Apache will look for files when a user accesses the web server. The location of DocumentRoot is specified in the Apache configuration file that you modified earlier (/usr/local/etc/apache24/httpd.conf).

By default, the DocumentRoot is set to /usr/local/www/apache24/data. You can create the info.php file under that location by typing:

  • sudo vi /usr/local/www/apache24/data/info.php

This will open a blank file. Insert this PHP code into the file:

/usr/local/www/apache24/data/info.php
<?php phpinfo(); ?> 

Save and exit.

Now you can test whether your web server can correctly display content generated by a PHP script. To try this out, you can visit this page in your web browser:

http://your_server_IP_address/info.php 

You’ll see a PHP FreeBSD testing page.

FreeBSD info.php

This page gives you information about your server from the perspective of PHP. It is useful for debugging and to ensure that your settings are being applied correctly.

If this was successful, then your PHP is working as expected.

You should remove this file after this test because it could actually give information about your server to unauthorized users. To do this, you can type this:

  • sudo rm /usr/local/www/apache24/data/info.php

You can always recreate this page if you need to access the information again later.

Conclusion

Now that you have a FAMP stack installed, you have many choices for what to do next. You’ve installed a platform that will allow you to install most kinds of websites and web software on your server.

DigitalOcean Community Tutorials

How To Optimize MySQL with Query Cache on Ubuntu 18.04

The author selected the Apache Software Foundation to receive a donation as part of the Write for DOnations program.

Introduction

Query cache is a prominent MySQL feature that speeds up data retrieval from a database. It achieves this by storing MySQL SELECT statements together with the retrieved record set in memory, then if a client requests identical queries it can serve the data faster without executing commands again from the database.

Compared to data read from disk, cached data from RAM (Random Access Memory) has a shorter access time, which reduces latency and improves input/output (I/O) operations. As an example, for a WordPress site or an e-commerce portal with high read calls and infrequent data changes, query cache can drastically boost the performance of the database server and make it more scalable.

In this tutorial, you will first configure MySQL without query cache and run queries to see how quickly they are executed. Then you’ll set up query cache and test your MySQL server with it enabled to show the difference in performance.

Note: Although query cache is deprecated as of MySQL 5.7.20, and removed in MySQL 8.0, it is still a powerful tool if you’re using supported versions of MySQL. However, if you are using newer versions of MySQL, you may adopt alternative third-party tools like ProxySQL to optimize performance on your MySQL database.

Prerequisites

Before you begin, you will need the following:

Step 1 — Checking the Availability of Query Cache

Before you set up query cache, you’ll check whether your version of MySQL supports this feature. First, ssh into your Ubuntu 18.04 server:

  • ssh user_name@your_server_ip

Then, run the following command to log in to the MySQL server as the root user:

  • sudo mysql -u root -p

Enter your MySQL server root password when prompted and then press ENTER to continue.

Use the following command to check if query cache is supported:

  • show variables like 'have_query_cache';

You should get an output similar to the following:

Output
+------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in set (0.01 sec)

You can see the value of have_query_cache is set to YES and this means query cache is supported. If you receive an output showing that your version does not support query cache, please see the note in the Introduction section for more information.

Now that you have checked and confirmed that your version of MySQL supports query cache, you will move on to examining the variables that control this feature on your database server.

Step 2 — Checking the Default Query Cache Variables

In MySQL, a number of variables control query cache. In this step, you’ll check the default values that ship with MySQL and understand what each variable controls.

You can examine these variables using the following command:

  • show variables like 'query_cache_%' ;

You will see the variables listed in your output:

Output
+------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 5 rows in set (0.00 sec)

The query_cache_limit value determines the maximum size of individual query results that can be cached. The default value is 1,048,576 bytes and this is equivalent to 1MB.

MySQL does not handle cached data in one big chunk; instead it is handled in blocks. The minimum amount of memory allocated to each block is determined by the query_cache_min_res_unit variable. The default value is 4096 bytes or 4KB.

query_cache_size controls the total amount of memory allocated to the query cache. If the value is set to zero, it means query cache is disabled. In most cases, the default value may be set to 16,777,216 (around 16MB). Also, keep in mind that query_cache_size needs at least 40KB to allocate its structures. The value allocated here is aligned to the nearest 1024 byte block. This means the reported value may be slightly different from what you set.

MySQL determines the queries to cache by examining the query_cache_type variable. Setting this value to 0 or OFF prevents caching or retrieval of cached queries. You can also set it to 1 to enable caching for all queries except for ones beginning with the SELECT SQL_NO_CACHE statement. A value of 2 tells MySQL to only cache queries that begin with SELECT SQL_CACHE command.

The variable query_cache_wlock_invalidate controls whether MySQL should retrieve results from the cache if the table used on the query is locked. The default value is OFF.

Note: The query_cache_wlock_invalidate variable is deprecated as of MySQL version 5.7.20. As a result, you may not see this in your output depending on the MySQL version you’re using.

Having reviewed the system variables that control the MySQL query cache, you’ll now test how MySQL performs without first enabling the feature.

Step 3 — Testing Your MySQL Server Without Query Cache

The goal of this tutorial is to optimize your MySQL server by using the query cache feature. To see the difference in speed, you’re going to run queries and see their performance before and after implementing the feature.

In this step you’re going to create a sample database and insert some data to see how MySQL performs without query cache.

While still logged in to your MySQL server, create a database and name it sample_db by running the following command:

  • Create database sample_db;
Output
Query OK, 1 row affected (0.00 sec)

Then switch to the database:

  • Use sample_db;
Output
Database changed

Create a table with two fields (customer_id and customer_name) and name it customers:

  • Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;
Output
Query OK, 0 rows affected (0.01 sec)

Then, run the following commands to insert some sample data:

  • Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
  • Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
  • Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
  • Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
  • Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
  • Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
  • Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
  • Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
  • Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
  • Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');
Output
Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) ...

The next step is starting the MySQL profiler, which is an analysis service for monitoring the performance of MySQL queries. To turn the profile on for the current session, run the following command, setting it to 1, which is on:

  • SET profiling = 1;
Output
Query OK, 0 rows affected, 1 warning (0.00 sec)

Then, run the following query to retrieve all customers:

  • Select * from customers;

You’ll receive the following output:

Output
+-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | JANE DOE | | 2 | JANIE DOE | | 3 | JOHN ROE | | 4 | MARY ROE | | 5 | RICHARD ROE | | 6 | JOHNNY DOE | | 7 | JOHN SMITH | | 8 | JOE BLOGGS | | 9 | JANE POE | | 10 | MARK MOE | +-------------+---------------+ 10 rows in set (0.00 sec)

Then, run the SHOW PROFILES command to retrieve performance information about the SELECT query you just ran:

  • SHOW PROFILES;

You will get output similar to the following:

Output
+----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00044075 | Select * from customers | +----------+------------+-------------------------+ 1 row in set, 1 warning (0.00 sec)

The output shows the total time spent by MySQL when retrieving records from the database. You are going to compare this data in the next steps when query cache is enabled, so keep note of your Duration. You can ignore the warning within the output since this simply indicates that SHOW PROFILES command will be removed in a future MySQL release and replaced with Performance Schema.

Next, exit from the MySQL Command Line Interface.

  • quit;

You have ran a query with MySQL before enabling query cache and noted down the Duration or time spent to retrieve records. Next, you will enable query cache and see if there is a performance boost when running the same query.

Step 4 — Setting Up Query Cache

In the previous step, you created sample data and ran a SELECT statement before you enabled query cache. In this step, you’ll enable query cache by editing the MySQL configuration file.

Use nano to edit the file:

  • sudo nano /etc/mysql/my.cnf

Add the following information to the end of your file:

/etc/mysql/my.cnf
... [mysqld] query_cache_type=1 query_cache_size = 10M query_cache_limit=256K 

Here you’ve enabled query cache by setting the query_cache_type to 1. You’ve also set up the individual query limit size to 256K and instructed MySQL to allocate 10 megabytes to query cache by setting the value of query_cache_size to 10M.

Save and close the file by pressing CTRL + X, Y, then ENTER. Then, restart your MySQL server to implement the changes:

  • sudo systemctl restart mysql

You have now enabled query cache.

Once you have configured query cache and restarted MySQL to apply the changes, you will go ahead and test the performance of MySQL with the feature enabled.

Step 5 — Testing Your MySQL Server with Query Cache Enabled

In this step, you’ll run the same query you ran in Step 3 one more time to check how query cache has optimized the performance of your MySQL server.

First, connect to your MySQL server as the root user:

  • sudo mysql -u root -p

Enter your root password for the database server and hit ENTER to continue.

Now confirm your configuration set in the previous step to ensure you enabled query cache:

  • show variables like 'query_cache_%' ;

You’ll see the following output:

Output
+------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 262144 | | query_cache_min_res_unit | 4096 | | query_cache_size | 10485760 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 5 rows in set (0.01 sec)

The variable query_cache_type is set to ON; this confirms that you enabled query cache with the parameters defined in the previous step.

Switch to the sample_db database that you created earlier.

  • Use sample_db;

Start the MySQL profiler:

  • SET profiling = 1;

Then, run the query to retrieve all customers at least two times in order to generate enough profiling information.

Remember, once you’ve run the first query, MySQL will create a cache of the results and therefore, you must run the query twice to trigger the cache:

  • Select * from customers;
  • Select * from customers;

Then, list the profiles information:

  • SHOW PROFILES;

You’ll receive an output similar to the following:

Output
+----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00049250 | Select * from customers | | 2 | 0.00026000 | Select * from customers | +----------+------------+-------------------------+ 2 rows in set, 1 warning (0.00 sec)

As you can see the time taken to run the query has drastically reduced from 0.00044075 (without query cache in Step 3) to 0.00026000 (the second query) in this step.

You can see the optimization from enabling the query cache feature by profiling the first query in detail:

  • SHOW PROFILE FOR QUERY 1;
Output
+--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000025 | | Waiting for query cache lock | 0.000004 | | starting | 0.000003 | | checking query cache for query | 0.000045 | | checking permissions | 0.000008 | | Opening tables | 0.000014 | | init | 0.000018 | | System lock | 0.000008 | | Waiting for query cache lock | 0.000002 | | System lock | 0.000018 | | optimizing | 0.000003 | | statistics | 0.000013 | | preparing | 0.000010 | | executing | 0.000003 | | Sending data | 0.000048 | | end | 0.000004 | | query end | 0.000006 | | closing tables | 0.000006 | | freeing items | 0.000006 | | Waiting for query cache lock | 0.000003 | | freeing items | 0.000213 | | Waiting for query cache lock | 0.000019 | | freeing items | 0.000002 | | storing result in query cache | 0.000003 | | cleaning up | 0.000012 | +--------------------------------+----------+ 25 rows in set, 1 warning (0.00 sec)

Run the following command to show profile information for the second query, which is cached:

  • SHOW PROFILE FOR QUERY 2;
Output
+--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000024 | | Waiting for query cache lock | 0.000003 | | starting | 0.000002 | | checking query cache for query | 0.000006 | | checking privileges on cached | 0.000003 | | checking permissions | 0.000027 | | sending cached result to clien | 0.000187 | | cleaning up | 0.000008 | +--------------------------------+----------+ 8 rows in set, 1 warning (0.00 sec)

The outputs from the profiler show that MySQL took less time on the second query because it was able to retrieve data from the query cache instead of reading it from the disk. You can compare the two sets of output for each of the queries. If you look at the profile information on QUERY 2, the status of sending cached result to client shows that data was read from the cache and no tables were opened since the Opening tables status is missing.

With the MySQL query cache feature enabled on your server, you’ll now experience improved read speeds.

Conclusion

You have set up query cache to speed up your MySQL server on Ubuntu 18.04. Using features like MySQL’s query cache can enhance the speed of your website or web application. Caching reduces unnecessary execution for SQL statements and is a highly recommended and popular method for optimizing your database. For more on speeding up your MySQL server, try the How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04 tutorial.

DigitalOcean Community Tutorials