One place for hosting & domains

      Install and Manage MySQL Databases with Puppet Hiera on Ubuntu 18.04


      Updated by Linode Contributed by Linode

      Puppet is a configuration management system that helps simplify the use and deployment of different types of software, making system administration more reliable and replicable. In this guide, we use Puppet to manage an installation of MySQL, a popular relational database used for applications such as WordPress, Ruby on Rails, and others. Hiera is a method of defining configuration values that Puppet will use to simplify MySQL configuration.

      In this guide, you’ll use Puppet to deploy modules on your server. At the end, you will have MySQL installed, configured, and ready to use for a variety of applications that require a database backend.

      Note

      This guide is written for a non-root user. Commands that require elevated privileges are prefixed with sudo. If you’re not familiar with the sudo command, see the Users and Groups guide.

      Before You Begin

      1. A Linode 1GB plan should be sufficient to run MySQL. Consider using a larger plan if you plan to use MySQL heavily, or for more than just a simple personal website.

      2. Familiarize yourself with our Getting Started guide and complete the steps for setting your Linode’s hostname and timezone.

      3. This guide will use sudo wherever possible. Complete the sections of our Securing Your Server to create a standard user account, harden SSH access and remove unnecessary network services.

      4. Update your system:

        sudo apt-get update && sudo apt-get upgrade
        

      Install and Configure Puppet

      Follow these steps to set up Puppet for single-host, local-only deployment. If you need to configure more than one server or to deploy a Puppet master, follow our multi-server Puppet guide.

      Install the Puppet Package

      1. Install the puppetlabs-release-bionic repository to add the Puppet packages:

        wget https://apt.puppetlabs.com/puppet-release-bionic.deb
        sudo dpkg -i puppet-release-bionic.deb
        
      2. Update the apt package index to make the Puppet Labs repository packages available, then install Puppet. This will install the puppet-agent package, which provides the puppet executable within in a compatible Ruby environment:

        sudo apt update && sudo apt install puppet-agent
        
      3. Confirm the version of Puppet installed:

        puppet --version
        

        At the time of writing, the Puppet version is 6.1.0.

      Install the Puppet MySQL Module

      Puppet Forge is a collection of modules that aid in the installation of different types of software. The MySQL module handles the installation and configuration of MySQL without you needing to manage various configuration files and services by hand.

      1. Install the MySQL module:

        sudo puppet module install puppetlabs-mysql --version 7.0.0
        

        This will install the mysql module into the default path: /etc/puppetlabs/code/environments/production/modules/.

      Puppet MySQL Manifest

      This guide uses a Puppet manifest to provide Puppet with installation and configuration instructions. Alternatively, you can configure a Puppet master.

      While the entirety of a Puppet manifest can contain the desired configuration for a host, values for Puppet classes or types can also be defined in a Hiera configuration file to simplify writing Puppet manifests in most cases. In this example, the mysql::server class parameters will be defined in Hiera, but the class must first be applied to the host.

      To apply the mysql::server class to all hosts by default, create the following Puppet manifest:

      /etc/puppetlabs/code/environments/production/manifests/site.pp
      1
      
      include ::mysql::server

      Note that site.pp is the default manifest file. Without a qualifying node { .. } line, this applies the class to any host applying the manifest. Puppet now knows to apply the mysql::server class, but still needs values for resources like databases, users, and other settings. Configure Hiera to provide these values in the next section.

      Install and Configure Puppet Hiera

      To understand how Hiera works, consider this excerpt from the default hiera.yaml file:

      /etc/puppetlabs/code/environments/production/hiera.yaml
      1
      2
      3
      4
      5
      6
      7
      
      ---
      version: 5
      hierarchy:
        - name: "Per-node data"
          path: "nodes/%{::trusted.certname}.yaml"
        - name: "Common data"
          path: "common.yaml"

      This Hiera configuration instructs Puppet to accept variable values from nodes/%{::trusted.certname}.yaml. If your Linode’s hostname is examplehostname, define a file called nodes/examplehostname.yaml). Any variables found in YAML files higher in the hierarchy are preferred, while any variable names that do not exist in those files will fall-through to files lower in the hierarchy (in this example, common.yaml).

      The following configuration will define Puppet variables in common.yaml to inject variables into the mysql::server class.

      Initial Hiera Configuration

      Hiera configuration files are formatted as yaml, with keys defining the Puppet parameters to inject their associated values. To get started, set the MySQL root password. The following example of a Puppet manifest is one way to control this password:

      example.pp
      1
      2
      3
      
      class { '::mysql::server':
        root_password => 'examplepassword',
      }

      We can also define the root password with the following Hiera configuration file. Create the following YAML file and note how the root_password parameter is defined as Hiera yaml:

      /etc/puppetlabs/code/environments/production/data/common.yaml
      1
      
      mysql::server::root_password: examplepassword

      Replace examplepassword with the secure password of your choice. Run Puppet to set up MySQL with default settings and the chosen root password:

      sudo -i puppet apply /etc/puppetlabs/code/environments/production/manifests/site.pp
      

      Puppet will output its progress before completing. To confirm MySQL has been configured properly, run a command:

      mysql -u root -p -e 'select version();'
      

      Enter the password and MySQL returns its version:

      +-------------------------+
      | version()               |
      +-------------------------+
      | 5.7.24-0ubuntu0.18.04.1 |
      +-------------------------+
      

      Define MySQL Resources

      Using Hiera, we can define the rest of the MySQL configuration entirely in yaml. The following steps will create a database and user for use in a WordPress installation.

      1. Create a pre-hashed MySQL password. Replace the password wordpresspassword in this example, and when prompted for a the root MySQL password, use the first root password chosen in the previous section to authenticate. Note the string starting with a * that the command returns for Step 2:

        mysql -u root -p -NBe 'select password("wordpresspassword")'
        *E62D3F829F44A91CC231C76347712772B3B9DABC
        
      2. With the MySQL password hash ready, we can define Hiera values. The following YAML defines parameters to create a database called wordpress and a user named wpuser that has permission to connect from localhost. The YAML also defines a GRANT allowing wpuser to operate on the wordpress database with ALL permissions:

        /etc/puppetlabs/code/environments/production/data/common.yaml
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        
        mysql::server::root_password: examplepassword
        mysql::server::databases:
          wordpress:
            ensure: present
        mysql::server::users:
          wpuser@localhost:
            ensure: present
            password_hash: '*E62D3F829F44A91CC231C76347712772B3B9DABC'
        mysql::server::grants:
          wpuser@localhost/wordpress.*:
            ensure: present
            privileges: ALL
            table: wordpress.*
            user: wpuser@localhost
      3. Re-run Puppet:

        sudo -i puppet apply /etc/puppetlabs/code/environments/production/manifests/site.pp
        
      4. The wpuser should now be able to connect to the wordpress database. To verify, connect to the MySQL daemon as the user wpuser to the wordpress database:

        mysql -u wpuser -p wordpress
        

        After you enter the password for wpuser, exit the MySQL prompt:

        exit
        

      Add Hierarchies for Specific Environments

      Additional configurations can be added that will only be applied to specific environments. For example, backup jobs may only be applied for hosts in a certain region, or specific databases can be created in a particular deployment.

      In the following example, Puppet will configure the MySQL server with one additional database, but only if that server’s distribution is Debian-based.

      1. Modify hiera.yaml to contain the following:

        /etc/puppetlabs/code/environments/production/hiera.yaml
        1
        2
        3
        4
        5
        6
        7
        8
        
        ---
        version: 5
        hierarchy:
          - name: "Per OS Family"
            path: "os/%{facts.os.family}.yaml"
          - name: "Other YAML hierarchy levels"
            paths:
              - "common.yaml"

        This change instructs Hiera to look for Puppet parameters first in "os/%{facts.os.family}.yaml" and then in common.yaml. The first, fact-based element of the hierarchy is dynamic, and dependent upon the host that Puppet and Hiera control. In this Ubuntu-based example, Hiera will look for Debian.yaml in the os folder, while on a distribution such as CentOS, the file RedHat.yaml will automatically be referenced instead.

      2. Create the following YAML file:

        /etc/puppetlabs/code/environments/production/data/os/Debian.yaml
        1
        2
        3
        4
        5
        6
        7
        
        lookup_options:
          mysql::server::databases:
            merge: deep
        
        mysql::server::databases:
          ubuntu-backup:
            ensure: present

        Though similar to the common.yaml file defined in previous steps, this file will add the ubuntu-backup database only on Debian-based hosts (like Ubuntu). In addition, the lookup_options setting ensures that the mysql::server:databases parameter is merged between Debian.yaml and common.yaml so that all databases are managed. Without lookup_options set to deeply merge these hashes, only the most specific hierarchy file will be applied to the host, in this case, Debian.yaml.

        • Alternatively, because our Puppet manifest is short, we can test the same command using the -e flag to apply an inline manifest:

          sudo -i puppet apply -e 'include ::mysql::server'
          
      3. Run Puppet and observe the changes:

        sudo -i puppet apply /etc/puppetlabs/code/environments/production/manifests/site.pp
        
      4. Verify that the new database exists:

        mysql -u root -p -e 'show databases;'
        

        This includes the new ubuntu-backup database:

        +---------------------+
        | Database            |
        +---------------------+
        | information_schema  |
        | mysql               |
        | performance_schema  |
        | sys                 |
        | ubuntu-backup       |
        | wordpress           |
        +---------------------+
        

      Congratulations! You can now control your Puppet configuration via highly configurable Hiera definitions.

      More Information

      You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

      Find answers, ask questions, and help others.

      This guide is published under a CC BY-ND 4.0 license.



      Source link

      Uma Introdução às consultas no MySQL


      Introdução

      Bancos de dados são um componente chave em muitos websites e aplicações, e estão no centro de como os dados são armazenados e trocados pela Internet. Um dos aspectos mais importantes do gerenciamento de banco de dados é a prática de recuperar dados de um banco de dados, seja em uma base ad hoc ou parte de um processo codificado em um aplicativo. Existem várias maneiras de recuperar informações de um banco de dados, mas um dos métodos mais utilizados é realizado através do envio de consultas pela linha de comandos.

      Em sistemas de gerenciamento de bancos de dados relacionais, uma consulta é qualquer comando usado para recuperar dados de uma tabela. Na Linguagem de Consulta Estruturada ou Structured Query Language (SQL), consultas são feitas quase sempre usando o comando SELECT.

      Neste guia, discutiremos a sintaxe básica das consultas SQL, bem como algumas das funções e operadores mais comumente empregados. Vamos também praticar a criação de consultas SQL usando alguns dados de amostra em um banco de dados MySQL.

      O MySQL é um sistema de gerenciamento de banco de dados relacional open-source. Sendo um dos bancos de dados SQL mais amplamente implantados, o MySQL prioriza velocidade, confiabilidade e usabilidade. Em geral, ele segue o padrão SQL ANSI, embora haja alguns casos em que o MySQL executa operações de maneira diferente do padrão reconhecido.

      Pré-requisitos

      Em geral, os comandos e conceitos apresentados neste guia podem ser usados em qualquer sistema operacional baseado em Linux executando qualquer software de banco de dados SQL. No entanto, ele foi escrito especificamente com um servidor Ubuntu 18.04 executando o MySQL em mente. Para configurar isso, você precisará do seguinte:

      Com esta configuração pronta, podemos começar o tutorial.

      Criando um Banco de Dados de Amostra

      Antes de começarmos a fazer consultas no SQL, vamos primeiro criar um banco de dados e algumas tabelas, em seguida, preencher essas tabelas com alguns dados de amostra. Isso permitirá que você ganhe experiência prática quando começar a fazer consultas mais tarde.

      Para o banco de dados de amostra que usaremos ao longo deste guia, imagine o seguinte cenário:

      Você e vários de seus amigos celebram seus aniversários juntos. Em cada ocasião, os membros do grupo vão para a pista de boliche local, participam de um torneio amistoso e, em seguida, todos vão para sua casa, onde você prepara a refeição favorita do aniversariante.

      Agora que essa tradição já dura algum tempo, você decidiu começar a acompanhar os registros desses torneios. Além disso, para tornar o planejamento das refeições mais fácil, você decide criar um registro dos aniversários dos seus amigos e de suas entradas, acompanhamentos e sobremesas favoritas. Em vez de manter essas informações em um livro físico, você decide exercitar suas habilidades de banco de dados gravando-as em um banco de dados MySQL.

      Para começar, abra um prompt de MySQL como seu usuário root do MySQL:

      Note: Se você seguiu o pré-requisito do tutorial sobre Como Instalar o MySQL no Ubuntu 18.04, você pode ter configurado seu usuário root para autenticar usando uma senha. Neste caso, você irá se conectar ao prompt do MySQL com o seguinte comando:

      Em seguida, crie o banco de dados executando:

      • CREATE DATABASE `aniversarios`;

      Depois, selecione este banco de dados digitando:

      A seguir, crie duas tabelas dentro desse banco de dados. Vamos utilizar a primeira tabela para acompanhar os registros dos seus amigos na pista de boliche. O seguinte comando criará uma tabela chamada torneios com colunas para o nome de cada um de seus amigos, o número de torneios que eles ganharam (vitorias), a melhor pontuação deles em todo o tempo, e que tamanho de sapato de boliche eles usam (tamanho):

      • CREATE TABLE torneios (
      • nome varchar(30),
      • vitorias real,
      • melhor real,
      • tamanho real
      • );

      Depois de executar o comando CREATE TABLE e preenchê-lo com títulos das colunas, você receberá a seguinte saída:

      Output

      Query OK, 0 rows affected (0.00 sec)

      Preencha a tabela torneios com alguns dados de amostra:

      • INSERT INTO torneios (nome, vitorias, melhor, tamanho)
      • VALUES ('Dolly', '7', '245', '8.5'),
      • ('Etta', '4', '283', '9'),
      • ('Irma', '9', '266', '7'),
      • ('Barbara', '2', '197', '7.5'),
      • ('Gladys', '13', '273', '8');

      Você receberá uma saída como esta:

      Output

      Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0

      Depois disso, crie outra tabela dentro do mesmo banco de dados que usaremos para armazenar informações sobre as refeições de aniversário favoritas dos seus amigos. O seguinte comando cria uma tabela chamada refeicoes com colunas para o nome de cada um dos seus amigos, a data_nascimento, a entrada favorita de cada um, o acompanhamento preferido, e a sobremesa favorita:

      • CREATE TABLE refeicoes (
      • nome varchar(30),
      • data_nascimento date,
      • entrada varchar(30),
      • acompanhamento varchar(30),
      • sobremesa varchar(30)
      • );

      Da mesma forma, para esta tabela, você receberá um feedback confirmando que o comando foi executado com sucesso:

      Output

      Query OK, 0 rows affected (0.01 sec)

      Preencha esta tabela com alguns dados de amostra também:

      • INSERT INTO refeicoes (nome, data_nascimento, entrada, acompanhamento, sobremesa)
      • VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'),
      • ('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'),
      • ('Irma', '1941-02-18', 'tofu', 'fries', 'cake'),
      • ('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'),
      • ('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream');

      Output

      Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0

      Uma vez que esse comando tenha sido concluído com êxito, você acabou de configurar seu banco de dados. A seguir, vamos falar sobre a estrutura básica de comando das consultas SELECT.

      Entendendo Comandos SELECT

      Conforme mencionado na introdução, consultas SQL quase sempre começam com o comando SELECT. SELECT é usado em consultas para especificar quais colunas de uma tabela devem ser retornadas no conjunto de resultados ou result-set. As consultas também quase sempre incluem FROM, que é usado para especificar qual tabela o comando consultará.

      Geralmente, as consultas SQL seguem essa sintaxe:

      • SELECT coluna_a_selecionar FROM tabela_a_selecionar WHERE certas_condições_a_aplicar;

      A título de exemplo, o seguinte comando retornará a coluna nome inteira da tabela refeicoes:

      • SELECT nome FROM refeicoes;
      [seconday_label Output]
      +---------+
      | nome    |
      +---------+
      | Dolly   |
      | Etta    |
      | Irma    |
      | Barbara |
      | Gladys  |
      +---------+
      5 rows in set (0.00 sec)
      

      Você pode selecionar várias colunas da mesma tabela, separando seus nomes com uma vírgula, desta forma:

      • SELECT nome, data_nascimento FROM refeicoes;

      Output

      +---------+-----------------+ | nome | data_nascimento | +---------+-----------------+ | Dolly | 1946-01-19 | | Etta | 1938-01-25 | | Irma | 1941-02-18 | | Barbara | 1948-12-25 | | Gladys | 1944-05-28 | +---------+-----------------+ 5 rows in set (0.00 sec)

      Em vez de nomear uma coluna específica ou um conjunto de colunas, você pode seguir o operador SELECT com um asterisco (*) que serve como um curinga representando todas as colunas em uma tabela. O seguinte comando retorna todas as colunas da tabela torneios:

      Output

      +---------+----------+--------+---------+ | nome | vitorias | melhor | tamanho | +---------+----------+--------+---------+ | Dolly | 7 | 245 | 8.5 | | Etta | 4 | 283 | 9 | | Irma | 9 | 266 | 7 | | Barbara | 2 | 197 | 7.5 | | Gladys | 13 | 273 | 8 | +---------+----------+--------+---------+ 5 rows in set (0.00 sec)

      WHERE é usado em consultas para filtrar registros que atendem a uma condição especificada, e todas as linhas que não atendem a essa condição são eliminadas do resultado. Uma cláusula WHERE geralmente segue esta sintaxe:

      • . . . WHERE nome_da_coluna operador_de_comparação valor

      O operador de comparação em uma cláusula WHERE define como a coluna especificada deve ser comparada com o valor. Aqui estão alguns operadores comuns de comparação em SQL:

      Operador O que ele faz
      = testa a igualdade
      != testa a desigualdade
      < testa menor que
      > testa maior que
      <= testa menor que ou igual a
      >= testa maior que ou igual a
      BETWEEN testa se um valor está dentro de um determinado intervalo
      IN testa se o valor de uma linha está contido em um conjunto de valores especificados
      EXISTS testa se existem linhas, dadas as condições especificadas
      LIKE testa se um valor corresponde a uma string especificada
      IS NULL testa valores NULL
      IS NOT NULL testa todos os valores que não sejam NULL

      Por exemplo, se você quiser encontrar o tamanho do sapato de Irma, use a seguinte consulta:

      • SELECT tamanho FROM torneios WHERE nome = 'Irma';

      Output

      +---------+ | tamanho | +---------+ | 7 | +---------+ 1 row in set (0.00 sec)

      O SQL permite o uso de caracteres curinga, e eles são especialmente úteis quando usados em cláusulas WHERE. Os sinais de porcentagem (%) representam zero ou mais caracteres desconhecidos, e os sublinhados ou underscores (_) representam um único caractere desconhecido. Eles são úteis se você estiver tentando encontrar uma informação específica em uma tabela, mas não tiver certeza de qual é exatamente essa informação. Para ilustrar, digamos que você tenha esquecido a entrada favorita de alguns de seus amigos, mas você está certo de que este prato principal começa com um "t". Você pode encontrar seu nome executando a seguinte consulta:

      • SELECT entrada FROM refeicoes WHERE entrada LIKE 't%';

      Output

      +---------+ | entrada | +---------+ | tofu | | tofu | +---------+ 2 rows in set (0.00 sec)

      Com base na saída acima, vemos que a entrada que esquecemos é tofu.

      Pode haver momentos em que você está trabalhando com bancos de dados que possuem colunas ou tabelas com nomes relativamente longos ou difíceis de ler. Nesses casos, você pode tornar esses nomes mais legíveis criando um alias ou apelido com a palavra-chave AS. Apelidos criados com AS são temporários e existem apenas durante a consulta para a qual eles foram criados:

      • SELECT nome AS n, data_nascimento AS d, sobremesa AS s FROM refeicoes;

      Output

      +---------+------------+-----------+ | n | d | s | +---------+------------+-----------+ | Dolly | 1946-01-19 | cake | | Etta | 1938-01-25 | ice cream | | Irma | 1941-02-18 | cake | | Barbara | 1948-12-25 | ice cream | | Gladys | 1944-05-28 | ice cream | +---------+------------+-----------+ 5 rows in set (0.00 sec)

      Aqui, dissemos ao SQL para exibir a coluna nome como n, a coluna data_nascimento como d e a coluna sobremesa como s.

      Os exemplos que mostramos até aqui incluem algumas das palavras-chave e cláusulas mais usadas em consultas SQL. Elas são úteis para consultas básicas, mas não são úteis se você estiver tentando realizar um cálculo ou derivar um valor escalar (um valor único, em oposição a um conjunto de vários valores diferentes) com base em seus dados. É aqui que as funções de agregação entram em ação.

      Funções de Agregação

      Muitas vezes, ao trabalhar com dados, você não necessariamente quer ver os dados em si. Em vez disso, você quer informações sobre os dados. A sintaxe SQL inclui várias funções que permitem interpretar ou executar cálculos em seus dados apenas emitindo uma consulta SELECT. Estas são conhecidas como funções de agregação.

      A função COUNT conta e retorna o número de linhas que correspondem a um determinado critério. Por exemplo, se você quiser saber quantos dos seus amigos preferem o tofu para a entrada de aniversário, você pode fazer essa consulta:

      • SELECT COUNT(entrada) FROM refeicoes WHERE entrada = 'tofu';

      Output

      +----------------+ | COUNT(entrada) | +----------------+ | 2 | +----------------+ 1 row in set (0.00 sec)

      A função AVG retorna o valor médio (média) de uma coluna. Usando nossa tabela de exemplo, você pode encontrar a melhor pontuação média entre seus amigos com esta consulta:

      • SELECT AVG(melhor) FROM torneios;

      Output

      +-------------+ | AVG(melhor) | +-------------+ | 252.8 | +-------------+ 1 row in set (0.00 sec)

      SUM é usado para encontrar a soma total de uma determinada coluna. Por exemplo, se você quiser ver quantos jogos você e seus amigos jogaram ao longo dos anos, você pode executar essa consulta:

      • SELECT SUM(vitorias) FROM torneios;

      Output

      +---------------+ | SUM(vitorias) | +---------------+ | 35 | +---------------+ 1 row in set (0.00 sec)

      Observe que as funções AVG eSUM só funcionarão corretamente quando usadas com dados numéricos. Se você tentar usá-los em dados não numéricos, isso resultará em um erro ou apenas 0, dependendo de qual SGBD você está usando:

      • SELECT SUM(entrada) FROM refeicoes;

      Output

      +--------------+ | SUM(entrada) | +--------------+ | 0 | +--------------+ 1 row in set, 5 warnings (0.00 sec)

      MIN é usado para encontrar o menor valor dentro de uma coluna especificada. Você poderia usar essa consulta para ver qual o pior registro geral de boliche até agora (em termos de número de vitórias):

      • SELECT MIN(vitorias) FROM torneios;
      [secondarylabel Output]
      +---------------+
      | MIN(vitorias) |
      +---------------+
      |             2 |
      +---------------+
      1 row in set (0.00 sec)
      

      Da mesma forma, MAX é usado para encontrar o maior valor numérico em uma determinada coluna. A consulta a seguir mostrará o melhor registro geral de boliche:

      • SELECT MAX(vitorias) FROM torneios;

      Output

      +---------------+ | MAX(vitorias) | +---------------+ | 13 | +---------------+ 1 row in set (0.00 sec)

      Ao contrário de SUM eAVG, as funções MIN eMAX podem ser usadas para tipos de dados numéricos e alfabéticos. Quando executado em uma coluna contendo valores de string, a função MIN mostrará o primeiro valor alfabeticamente:

      • SELECT MIN(nome) FROM refeicoes;

      Output

      +-----------+ | MIN(nome) | +-----------+ | Barbara | +-----------+ 1 row in set (0.00 sec)

      Da mesma forma, quando executado em uma coluna contendo valores de string, a função MAX mostrará o último valor em ordem alfabética:

      • SELECT MAX(nome) FROM refeicoes;

      Output

      +-----------+ | MAX(nome) | +-----------+ | Irma | +-----------+ 1 row in set (0.00 sec)

      As funções agregadas têm muitos usos além do que foi descrito nesta seção. Elas são particularmente úteis quando usadas com a cláusula GROUP BY, que é abordada na próxima seção junto com várias outras cláusulas de consulta que afetam como os result-sets são classificados.

      Manipulando Saídas da Consulta

      Além das cláusulas FROM eWHERE, existem várias outras cláusulas que são usadas para manipular os resultados de uma consulta SELECT. Nesta seção, explicaremos e forneceremos exemplos para algumas das cláusulas de consulta mais comumente usadas.

      Uma das cláusulas de consulta mais usadas, além de FROM e WHERE, é a cláusula GROUP BY. Ela é normalmente usada quando você está executando uma função de agregação em uma coluna, mas em relação aos valores correspondentes em outra.

      Por exemplo, digamos que você queria saber quantos de seus amigos preferem cada uma das três entradas que você faz. Você pode encontrar essa informação com a seguinte consulta:

      • SELECT COUNT(nome), entrada FROM refeicoes GROUP BY entrada;

      Output

      +-------------+----------+ | COUNT(nome) | entrada | +-------------+----------+ | 1 | chicken | | 2 | steak | | 2 | tofu | +-------------+----------+ 3 rows in set (0.00 sec)

      A cláusula ORDER BY é usada para classificar os resultados da consulta. Por padrão, os valores numéricos são classificados em ordem crescente e os valores de texto são classificados em ordem alfabética. Para ilustrar, a consulta a seguir lista as colunas nome e data_nascimento, mas classifica os resultados por data_nascimento:

      • SELECT nome, data_nascimento FROM refeicoes ORDER BY data_nascimento;

      Output

      +---------+-----------------+ | nome | data_nascimento | +---------+-----------------+ | Etta | 1938-01-25 | | Irma | 1941-02-18 | | Gladys | 1944-05-28 | | Dolly | 1946-01-19 | | Barbara | 1948-12-25 | +---------+-----------------+ 5 rows in set (0.00 sec)

      Observe que o comportamento padrão de ORDER BY é classificar o result-set em ordem crescente. Para reverter isso e ter o resultado classificado em ordem decrescente, feche a consulta com DESC:

      • SELECT nome, data_nascimento FROM refeicoes ORDER BY data_nascimento DESC;

      Output

      +---------+-----------------+ | nome | data_nascimento | +---------+-----------------+ | Barbara | 1948-12-25 | | Dolly | 1946-01-19 | | Gladys | 1944-05-28 | | Irma | 1941-02-18 | | Etta | 1938-01-25 | +---------+-----------------+ 5 rows in set (0.00 sec)

      Como mencionado anteriormente, a cláusula WHERE é usada para filtrar resultados com base em condições específicas. No entanto, se você usar a cláusula WHERE com uma função de agregação, ela retornará um erro, como é o caso da seguinte tentativa de encontrar quais acompanhamentos são os favoritos de pelo menos três de seus amigos:

      • SELECT COUNT(nome), acompanhamento FROM refeicoes WHERE COUNT(nome) >= 3;

      Output

      ERROR 1111 (HY000): Invalid use of group function

      A cláusula HAVING foi adicionada ao SQL para fornecer funcionalidade semelhante à da cláusula WHERE, além de ser compatível com funções de agregação. É útil pensar na diferença entre essas duas cláusulas como sendo que WHERE se aplica a registros individuais, enquantoHAVING se aplica a grupos de registros. Para este fim, sempre que você emitir uma cláusula HAVING, a cláusula GROUP BY também deve estar presente.

      O exemplo a seguir é outra tentativa de descobrir quais são os acompanhamentos favoritos de pelo menos três de seus amigos, embora este retorne um resultado sem erro:

      • SELECT COUNT(nome), acompanhamento FROM refeicoes GROUP BY acompanhamento HAVING COUNT(nome) >= 3;

      Output

      +-------------+----------------+ | COUNT(nome) | acompanhamento | +-------------+----------------+ | 3 | fries | +-------------+----------------+ 1 row in set (0.00 sec)

      As funções de agregação são úteis para resumir os resultados de uma determinada coluna em uma dada tabela. No entanto, há muitos casos em que é necessário consultar o conteúdo de mais de uma tabela. Na próxima seção analisaremos algumas maneiras de fazer isso.

      Consultando Várias Tabelas

      Mais frequentemente, um banco de dados contém várias tabelas, cada uma contendo diferentes conjuntos de dados. O SQL fornece algumas maneiras diferentes de executar uma única consulta em várias tabelas.

      A cláusula JOIN pode ser usada para combinar linhas de duas ou mais tabelas em um resultado de consulta. Ele faz isso localizando uma coluna relacionada entre as tabelas e classifica os resultados adequadamente na saída.

      Os comandos SELECT que incluem uma cláusula JOIN geralmente seguem esta sintaxe:

      • SELECT tabela1.coluna1, tabela2.coluna2
      • FROM tabela1
      • JOIN tabela2 ON tabela1.coluna_relacionada=tabela2.coluna_relacionada;

      Note que como cláusulas JOIN comparam o conteúdo de mais de uma tabela, o exemplo anterior especifica em qual tabela selecionar cada coluna, precedendo o nome da coluna com o nome da tabela e um ponto. Você pode especificar de qual tabela uma coluna deve ser selecionada para qualquer consulta, embora isso não seja necessário ao selecionar de uma única tabela, como fizemos nas seções anteriores. Vamos examinar um exemplo usando nossos dados de amostra.

      Imagine que você queria comprar para cada um de seus amigos um par de sapatos de boliche como presente de aniversário. Como as informações sobre datas de nascimento e tamanhos de calçados dos seus amigos são mantidas em tabelas separadas, você pode consultar as duas tabelas separadamente e comparar os resultados de cada uma delas. Com uma cláusula JOIN, no entanto, você pode encontrar todas as informações desejadas com uma única consulta:

      • SELECT torneios.nome, torneios.tamanho, refeicoes.data_nascimento
      • FROM torneios
      • JOIN refeicoes ON torneios.nome=refeicoes.nome;

      Output

      +---------+---------+------------------+ | nome | tamanho | data_nascimento | +---------+---------+------------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | +---------+---------+------------------+ 5 rows in set (0.00 sec)

      A cláusula JOIN usada neste exemplo, sem nenhum outro argumento, é uma cláusula inner JOIN. Isso significa que ela seleciona todos os registros que possuem valores correspondentes nas duas tabelas e os imprime no result-set, enquanto todos os registros que não tem correspondência são excluídos. Para ilustrar essa ideia, vamos adicionar uma nova linha a cada tabela que não tenha uma entrada correspondente na outra:

      • INSERT INTO torneios (nome, vitorias, melhor, tamanho)
      • VALUES ('Bettye', '0', '193', '9');
      • INSERT INTO refeicoes (nome, data_nascimento, entrada, acompanhamento, sobremesa)
      • VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream');

      Então, execute novamente a instrução SELECT anterior com a cláusula JOIN:

      • SELECT torneios.nome, torneios.tamanho, refeicoes.data_nascimento
      • FROM torneios
      • JOIN refeicoes ON torneios.nome=refeicoes.nome;

      Output

      +---------+---------+-----------------+ | nome | tamanho | data_nascimento | +---------+---------+-----------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | +---------+---------+-----------------+ 5 rows in set (0.00 sec)

      Observe que, como a tabela torneios não tem entrada para Lesley e a tabela refeicoes não tem entrada para Bettye, esses registros estão ausentes desta saída.

      É possível, no entanto, retornar todos os registros de uma das tabelas usando uma cláusula outer JOIN. No MySQL, as cláusulas JOIN são escritas como LEFT JOIN ou RIGHT JOIN.

      Uma cláusula LEFT JOIN retorna todos os registros da tabela da "esquerda" e apenas os registros correspondentes da tabela da direita. No contexto de outer joins, a tabela da esquerda é aquela referenciada pela cláusula FROM e a tabela da direita é qualquer outra tabela referenciada após o comando JOIN.

      Execute a consulta anterior novamente, mas desta vez use uma cláusula LEFT JOIN:

      • SELECT torneios.nome, torneios.tamanho, refeicoes.data_nascimento
      • FROM torneios
      • LEFT JOIN refeicoes ON torneios.nome=refeicoes.nome;

      Este comando retornará todos os registros da tabela da esquerda (neste caso, torneios), mesmo que não tenha um registro correspondente na tabela da direita. Toda vez que não houver um registro correspondente da tabela da direita, ele será retornado como NULL ou apenas como um valor em branco, dependendo do seu SGBD:

      Output

      +---------+---------+-----------------+ | nome | tamanho | data_nascimento | +---------+---------+-----------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | | Bettye | 9 | NULL | +---------+---------+-----------------+ 6 rows in set (0.00 sec)

      Agora execute a consulta novamente, desta vez com uma cláusula RIGHT JOIN:

      • SELECT torneios.nome, torneios.tamanho, refeicoes.data_nascimento
      • FROM torneios
      • RIGHT JOIN refeicoes ON torneios.nome=refeicoes.nome;

      Isso retornará todos os registros da tabela da direita (refeicoes). Como a data de nascimento de Lesley está registrada na tabela da direita, mas não há uma linha correspondente para ela na tabela da esquerda, as colunas nome e tamanho retornarão como valores NULL nessa linha:

      Output

      +---------+---------+-----------------+ | nome | tamanho | data_nascimento | +---------+---------+-----------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | | NULL | NULL | 1946-05-02 | +---------+---------+-----------------+ 6 rows in set (0.00 sec)

      Observe que joins à esquerda e à direita podem ser escritos como LEFT OUTER JOIN ou RIGHT OUTER JOIN, embora a parte OUTER da cláusula esteja implícita. Da mesma forma, especificar INNER JOIN produzirá o mesmo resultado que apenas escrever JOIN.

      Como uma alternativa ao uso de JOIN para consultar registros de várias tabelas, você pode usar a cláusula UNION.

      O operador UNION funciona de forma ligeiramente diferente de uma cláusula JOIN: em vez de imprimir resultados de várias tabelas como colunas únicas usando um único comando SELECT, o UNION combina os resultados de dois comandos SELECT em uma única coluna.

      Para ilustrar, execute a seguinte consulta:

      • SELECT nome FROM torneios UNION SELECT nome FROM refeicoes;

      Esta consulta removerá quaisquer entradas duplicadas, que é o comportamento padrão do operador UNION:

      Output

      +---------+ | nome | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Bettye | | Lesley | +---------+ 7 rows in set (0.00 sec)

      Para retornar todas as entradas (incluindo as duplicadas), use o operador UNION ALL:

      • SELECT nome FROM torneios UNION ALL SELECT nome FROM refeicoes;

      Output

      +---------+ | nome | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Bettye | | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Lesley | +---------+ 12 rows in set (0.00 sec)

      Os nomes e números das colunas na tabela de resultados refletem o nome e o número de colunas consultadas pelo primeiro comando SELECT. Note que ao usar UNION para consultar múltiplas colunas de mais de uma tabela, cada comando SELECT deve consultar o mesmo número de colunas, as respectivas colunas devem ter tipos de dados similares, e as colunas em cada comando SELECT devem estar na mesma ordem. O exemplo a seguir mostra o que pode resultar se você usar uma cláusula UNION em dois comandos SELECT que consultam um número diferente de colunas:

      • SELECT nome FROM refeicoes UNION SELECT nome, vitorias FROM torneios;

      Output

      ERROR 1222 (21000): The used SELECT statements have a different number of columns

      Outra maneira de consultar várias tabelas é através do uso de subconsultas ou subqueries. As subqueries (também conhecidas como consultas internas ou aninhadas) são consultas incluídas em outra consulta. Elas são úteis nos casos em que você está tentando filtrar os resultados de uma consulta com base no resultado de uma função de agregação separada.

      Para ilustrar essa ideia, digamos que você queira saber quais dos seus amigos ganharam mais partidas do que Bárbara. Em vez de consultar quantos jogos Bárbara venceu e, em seguida, executar outra consulta para ver quem ganhou mais jogos do que isso, você pode calcular ambos com uma única consulta:

      • SELECT nome, vitorias FROM torneios
      • WHERE vitorias > (
      • SELECT vitorias FROM torneios WHERE nome = 'Barbara'
      • );

      Output

      +--------+----------+ | nome | vitorias | +--------+----------+ | Dolly | 7 | | Etta | 4 | | Irma | 9 | | Gladys | 13 | +--------+----------+ 4 rows in set (0.00 sec)

      A subquerie nesse comando foi executada apenas uma vez; ele só precisava encontrar o valor da coluna vitorias na mesma linha que Barbara na coluna nome, e os dados retornados pela subquerie e pela consulta externa são independentes um do outro. Existem casos, no entanto, em que a consulta externa deve primeiro ler todas as linhas de uma tabela e comparar esses valores com os dados retornados pela subquerie para retornar os dados desejados. Nesse caso, a subquerie é referida como uma subquerie correlacionada.

      O comando a seguir é um exemplo de uma subquerie correlacionada. Esta consulta procura descobrir quais dos seus amigos ganharam mais jogos do que a média para aqueles com o mesmo tamanho de calçado:

      • SELECT nome, tamanho FROM torneios AS t
      • WHERE vitorias > (
      • SELECT AVG(vitorias) FROM torneios WHERE tamanho = t.tamanho
      • );

      Para que a consulta seja concluída, ela deve primeiro coletar as colunas nome e tamanho da consulta externa. Em seguida, ele compara cada linha desse result-set com os resultados da consulta interna, que determina o número médio de vitórias para indivíduos com tamanhos de sapatos idênticos. Como você só tem dois amigos com o mesmo tamanho de calçado, só pode haver uma linha no result-set:

      Output

      +------+---------+ | nome | tamanho | +------+---------+ | Etta | 9 | +------+---------+ 1 row in set (0.00 sec)

      Conforme mencionado anteriormente, as subquerie podem ser usadas para consultar resultados de várias tabelas. Para ilustrar isso com um exemplo final, digamos que você queria fazer um jantar surpresa para o melhor jogador de todos os tempos do grupo. Você pode encontrar qual dos seus amigos tem o melhor registro de boliche e retornar sua refeição favorita com a seguinte consulta:

      • SELECT nome, entrada, acompanhamento, sobremesa
      • FROM refeicoes
      • WHERE nome = (SELECT nome FROM torneios
      • WHERE vitorias = (SELECT MAX(vitorias) FROM torneios));

      Output

      +--------+---------+-----------------+------------+ | nome | entrada | acompanhamento | sobremesa | +--------+---------+-----------------+------------+ | Gladys | steak | fries | ice cream | +--------+---------+-----------------+------------+ 1 row in set (0.00 sec)

      Observe que esse comando não inclui apenas uma subquerie, mas também contém uma subquerie dentro dessa subquerie.

      Conclusão

      A realização de consultas é uma das tarefas mais comuns no domínio do gerenciamento de banco de dados. Existem várias ferramentas de administração de banco de dados, como phpMyAdmin or pgAdmin, que permitem realizar consultas e visualizar os resultados, mas a execução de comandos SELECT a partir da linha de comando ainda é um fluxo de trabalho amplamente praticado que também pode fornecer maior controle.

      Se você é novato no trabalho com SQL, lhe encorajamos a usar nosso Guia de Consulta Rápida SQL como referência e a revisar a documentação oficial do MySQL. Além disso, se você quiser saber mais sobre bancos de dados relacionais e SQL, os seguintes tutoriais podem ser de seu interesse:

      Por Mark Drake



      Source link

      Como Solucionar Problemas no MySQL


      Introdução

      O MySQL é um sistema gerenciador de banco de dados relacional open-source, o mais popular de seu tipo no mundo. Como é comum quando se trabalha com qualquer software, tanto os novatos quanto os usuários experientes podem encontrar mensagens de erro confusas ou problemas difíceis de diagnosticar.

      Este guia servirá como um recurso de solução de problemas e um ponto de partida à medida que você diagnostica sua configuração do MySQL. Analisaremos alguns dos problemas que muitos usuários do MySQL encontram e forneceremos orientações para solucionar problemas específicos. Também incluiremos links para tutoriais da DigitalOcean e a documentação oficial do MySQL que pode ser útil em certos casos.

      Por favor, observe que este guia assume a configuração descrita em Como Instalar o MySQL no Ubuntu 18.04, e os tutoriais vinculados em todo o guia refletem essa configuração. Se seu servidor estiver executando outra distribuição, contudo, você pode encontrar um guia específico para essa distro no Menu de Versão do tutorial, na parte superior dos tutoriais vinculados, quando houver um disponível.

      O lugar onde muitos usuários iniciantes do MySQL se deparam com problemas é durante o processo de instalação e configuração. Nosso guia sobre Como Instalar o MySQL no Ubuntu 18.04 fornece instruções sobre como configurar uma instalação básica e pode ser útil para aqueles que são novos no MySQL.

      Outra razão pela qual alguns usuários enfrentam problemas é que seu aplicativo requer recursos de banco de dados que estão presentes apenas nas últimas versões, mas a versão do MySQL disponível nos repositórios padrão de algumas distribuições Linux — incluindo o Ubuntu — não é a última versão. Por esta razão, os desenvolvedores MySQL mantém seus próprios repositórios de software, que você pode utilizar para instalar a versão mais recente e mantê-la atualizada. Nosso tutorial “How To Install the Latest MySQL on Ubuntu 18.04” fornece instruções sobre como fazer isso.

      Como Acessar os Logs de Erros do MySQL

      Muitas vezes, a causa raiz de lentidão, falha ou outro comportamento inesperado no MySQL pode ser determinada pela análise de seus logs de erro. Em sistemas Ubuntu, a localização padrão para o MySQL é em /var/log/mysql/error.log. Em muitos casos, os logs de erros são lidos mais facilmente com o programa less, um utilitário de linha de comando que lhe permite ver arquivos mas não editá-los:

      • sudo less /var/log/mysql/error.log

      Se o MySQL não estiver se comportando como esperado, você pode obter mais informações sobre a origem do problema executando este comando e diagnosticando o erro com base no conteúdo do log.

      Redefinindo a Senha do Usuário root do MySQL

      Se você tiver definido uma senha para o usuário root da sua instalação do MySQL, mas se equeceu dela, você poderia ficar bloqueado para acesso aos seus bancos de dados. Contanto que você tenha acesso ao servidor no qual seu banco de dados está hospedado, contudo, você deverá ser capaz de redefini-la.

      Esse processo é diferente de redefinir a senha para um nome de usuário padrão do Linux. Verifique nosso guia How To Reset Your MySQL or MariaDB Root Password para ler e entender esse processo.

      Às vezes, os usuários enfrentam problemas quando começam a realizar consultas nos dados. Em alguns sistemas de banco de dados, incluindo o MySQL, os comandos de consulta devem terminar com um ponto-e-vírgula (;) para que sejam completados, como no seguinte exemplo:

      • SHOW * FROM nome_da_tabela;

      Se você não incluir um ponto-e-vírgula no final de sua consulta, o prompt continuará em uma nova linha até que você complete a consulta inserindo um ponto-e-vírgula e pressionando ENTER.

      Alguns usuários podem achar que suas consultas estão extremamente lentas. Uma maneira de descobrir qual comando de consulta é a causa de uma lentidão é ativar e visualizar o log de consultas lentas do MySQL. Para fazer isso, abra o seu arquivo mysqld.cnf, que é utilizado para configurar opções para o servidor do MySQL. Este arquivo é normalmente armazenado dentro do diretório /etc/mysql/mysql.conf.d/:

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

      Percorra o arquivo até ver as seguintes linhas:

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

      
      . . .
      #slow_query_log         = 1
      #slow_query_log_file    = /var/log/mysql/mysql-slow.log
      #long_query_time = 2
      #log-queries-not-using-indexes
      . . .
      

      Essas diretivas comentadas fornecem as opções padrão de configuração do MySQL para o log de consultas lentas. Especificamente, aqui está o que cada uma delas faz:

      • slow-query-log: Definir isso como 1 ativa o log de consultas lentas.

      • slow-query-log-file: Isso define o arquivo onde o MySQL registrará qualquer consulta lenta. Neste caso, ele aponta para o arquivo /var/log/mysql-slow.log.

      • long_query_time: Definindo essa diretiva para 2, configura o MySQL para registrar quaisquer consultas que demorem mais de 2 segundos para serem concluídas.

      • log_queries_not_using_indexes: Isso diz ao MySQL para registrar também quaisquer consultas que executem sem índices para o arquivo /var/log/mysql-slow.log. Essa configuração não é necessária para o log de consultas lentas funcionar, mas pode ser útil para detectar consultas ineficientes.

      Descomente cada uma dessas linhas removendo os sinais de cerquilha no início. A seção ficará assim agora:

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

      
      . . .
      slow_query_log = 1
      slow_query_log_file = /var/log/mysql-slow.log
      long_query_time = 2
      log_queries_not_using_indexes
      . . .
      

      Nota: Se você estiver executando o MySQL 8+, estas linhas comentadas não estarão no arquivo mysqld.cnf por padrão. Nesse caso, adicione as seguintes linhas à parte inferior do arquivo:

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

      
      . . .
      slow_query_log = 1
      slow_query_log_file = /var/log/mysql-slow.log
      long_query_time = 2
      log_queries_not_using_indexes
      

      Depois de habilitar o log de consultas lentas, salve e feche o arquivo. Em seguida, reinicie o serviço MySQL:

      • sudo systemctl restart mysql

      Com essas configurações valendo, você pode encontrar comandos de consulta problemáticos visualizando o log de consultas lentas. Você pode fazer isso com o less, assim:

      • sudo less /var/log/mysql_slow.log

      Depois de encontrar as consultas que causam a lentidão, você pode verificar nosso guia How To Optimize Queries and Tables in MySQL and MariaDB on a VPS que pode ser útil para otimizá-las.

      Além disso, o MySQL inclui o comando EXPLAIN, que fornece informações sobre como o MySQL executa consultas. Esta página da documentação oficial do MySQL fornece dicas sobre como usar o EXPLAIN para destacar consultas ineficientes.

      Para obter ajuda na compreensão das estruturas básicas de consulta, consulte nosso tutorial Introduction to MySQL Queries.

      Permitindo o Acesso Remoto

      Muitos sites e aplicativos começam com o servidor da web e o back-end de banco de dados hospedado na mesma máquina. Com o tempo, no entanto, uma configuração como essa pode se tornar incômoda e difícil de escalar. Uma solução comum é separar essas funções configurando um banco de dados remoto, permitindo que o servidor e o banco de dados cresçam em seu próprio ritmo em suas próprias máquinas. Um dos problemas mais comuns que os usuários enfrentam ao tentar configurar um banco de dados MySQL remoto é que sua instância do MySQL é configurada para escutar apenas conexões locais. Este é o padrão de configuração do MySQL, mas não funcionará para uma configuração de banco de dados remota, já que o MySQL deve ser capaz de escutar por um endereço IP externo onde o servidor possa ser alcançado. Para ativar isso, abra o seu arquivo mysqld.cnf:

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

      Navegue até a linha que começa com a diretiva bind-address. Será parecido com isto:

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

      
      . . .
      lc-messages-dir = /usr/share/mysql
      skip-external-locking
      #
      # Instead of skip-networking the default is now to listen only on
      # localhost which is more compatible and is not less secure.
      bind-address            = 127.0.0.1
      . . .
      

      Por padrão, este valor está definido para 127.0.0.1, significando que o servidor olhará somente para conexões locais. Você precisará alterar esta diretiva para referenciar um endereço IP externo. Para fins de solução de problemas, você pode definir essa diretiva como um endereço IP curinga, seja *, ::, ou 0.0.0.0:

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

      
      . . .
      lc-messages-dir = /usr/share/mysql
      skip-external-locking
      #
      # Instead of skip-networking the default is now to listen only on
      # localhost which is more compatible and is not less secure.
      bind-address            = 0.0.0.0
      . . .
      

      Nota: Se você estiver executando o MySQL 8+, a diretiva bind-address não estará no arquivo mysqld.cnf por padrão. Nesse caso, adicione a seguinte linha destacada à parte inferior do arquivo:

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

      
      . . .
      [mysqld]
      pid-file        = /var/run/mysqld/mysqld.pid
      socket          = /var/run/mysqld/mysqld.sock
      datadir         = /var/lib/mysql
      log-error       = /var/log/mysql/error.log
      bind-address            = 0.0.0.0
      

      Depois de alterar esta linha, salve e feche o arquivo e em seguida, reinicie o serviço MySQL:

      • sudo systemctl restart mysql

      Depois disso, tente acessar seu banco de dados remotamente a partir de outra máquina:

      • mysql -u usuário -h ip_servidor_de_banco_de_dados -p

      Se você for capaz de acessar seu banco de dados, isso confirma que a diretiva bind-address em seu arquivo de configuração era o problema. Por favor, observe, entretanto, que a configuração de bind-address para0.0.0.0 é insegura, pois permite conexões ao seu servidor a partir de qualquer endereço IP. Por outro lado, se você ainda não conseguir acessar o banco de dados remotamente, algo mais pode estar causando o problema. Em ambos os casos, você pode achar útil seguir nosso guia How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04 para definir uma configuração de banco de dados remota mais segura.

      O MySQL Pára Inesperadamente ou Falha ao Iniciar

      A causa mais comum de falhas no MySQL é que ele parou ou falhou ao iniciar devido a memória insuficiente. Para verificar isso, você precisará revisar o log de erros do MySQL após uma falha.

      Primeiro, tente iniciar o servidor MySQL digitando:

      • sudo systemctl start mysql

      Em seguida, revise os logs de erro para ver o que está causando o travamento do MySQL. Você pode usar o less para revisar seus logs, uma página por vez:

      • sudo less /var/log/mysql/error.log

      Algumas mensagens comuns que indicariam uma quantidade insuficiente de memória são Out of memory ou mmap can't allocate.

      Soluções potenciais para uma quantidade inadequada de memória são:

      • Otimização da sua configuração do MySQL. Uma ótima ferramenta open-source para isso é o MySQLtuner. A execução do script MySQLtuner irá gerar um conjunto de ajustes recomendados para o seu arquivo de configuração do MySQL (mysqld.cnf). Observe que quanto mais tempo seu servidor estiver rodando antes de usar o MySQLTuner, mais precisas serão as suas sugestões. Para obter uma estimativa de uso de memória das configurações atuais e as propostas pelo MySQLTimer, use esta Calculadora MySQL.

      • Redução da confiança da sua aplicação web no MySQL para carregamentos de página. Isso geralmente pode ser feito adicionando-se cache estático à sua aplicação. Exemplos disso incluem o Joomla, que tem o cache como um recurso interno que pode ser ativado, e o WP Super Cache, um plugin do WordPress que adiciona esse tipo de funcionalidade.

      • Atualização para um VPS maior. No mínimo, recomendamos um servidor com pelo menos 1 GB de RAM para qualquer servidor que use um banco de dados MySQL, mas o tamanho e o tipo dos dados podem afetar significativamente os requisitos de memória.

      Lembre-se de que, embora a atualização do seu servidor seja uma solução em potencial, ela só é recomendável depois que você investigar e pesar todas as outras opções. Um servidor atualizado com mais recursos também vai custar mais dinheiro, então você só deve redimensionar se realmente isso acabar sendo sua melhor opção. Observe também que a documentação do MySQL inclui várias outras sugestões para diagnosticar e prevenir falhas.

      Tabelas Corrompidas

      Ocasionalmente, as tabelas do MySQL podem ficar corrompidas, o que significa que ocorreu um erro e os dados contidos nelas não podem ser lidos. Tentativas de ler uma tabela corrompida geralmente levam à falha do servidor.

      Algumas causas comuns de tabelas corrompidas são:

      • O servidor MySQL parou no meio de uma escrita.

      • Um programa externo modifica uma tabela que está sendo modificada simultaneamente pelo servidor.

      • A máquina foi desligada inesperadamente.

      • O hardware do computador falhou.

      • Há um bug de software em algum lugar no código do MySQL.

      Se você suspeitar que uma de suas tabelas foi corrompida, faça um backup do diretório de dados antes de solucionar problemas ou de tentar consertar a tabela. Isso ajudará a minimizar o risco de perda de dados.

      Primeiro, pare o serviço MySQL:

      • sudo systemctl stop mysql

      Em seguida, copie todos os seus dados em um novo diretório de backup. Nos sistemas Ubuntu, o diretório de dados padrão é /var/lib/mysql/:

      • cp -r /var/lib/mysql /var/lib/mysql_bkp

      Depois de fazer o backup, você está pronto para começar a investigar se a tabela está de fato corrompida. Se a tabela utiliza o mecanismo de armazenamento MyISAM, você pode verificar se ela está corrompida executando um comando CHECK TABLE no prompt do MySQL:

      • CHECK TABLE nome_da_tabela;

      Uma mensagem aparecerá na saída desse comando informando se ela está corrompida ou não. Se a tabela MyISAM estiver realmente corrompida, ela geralmente pode ser reparada emitindo um comando REPAIR TABLE:

      • REPAIR TABLE nome_da_tabela;

      Supondo que o reparo foi bem sucedido, você verá uma mensagem como a seguinte em sua saída:

      Output

      +---------------------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------+--------+----------+----------+ | nome_do_banco_de_dados.nome_da_tabela | repair | status | OK | +---------------------------------------+--------+----------+----------+

      Se a tabela ainda estiver corrompida, a documentação do MySQL sugere alguns métodos alternativos para reparar tabelas corrompidas.

      Por outro lado, se a tabela corrompida usa o mecanismo de armazenamento InnoDB, então o processo para repará-la será diferente. O InnoDB é o mecanismo de armazenamento padrão no MySQL a partir da versão 5.5, e possui operações automatizadas de verificação e reparo de corrupção. O InnoDB verifica páginas corrompidas executando somas de verificação ou checksums em cada página que lê, e se encontrar uma discrepância no checksum, ele irá parar automaticamente o servidor MySQL.

      Raramente é necessário reparar tabelas InnoDB, já que o InnoDB possui um mecanismo de recuperação de falhas que pode resolver a maioria dos problemas quando o servidor é reiniciado. No entanto, se você encontrar uma situação onde você precisa reconstruir uma tabela InnoDB corrompida, a documentação do MySQL recomenda o método “Dump and Reload”. Isso envolve recuperar o acesso à tabela corrompida, usando o utilitário mysqldump para criar um backup lógico da tabela, que manterá a estrutura da tabela e os dados dentro dela e, em seguida, recarregar a tabela de volta no banco de dados.

      Com isso em mente, tente reiniciar o serviço MySQL para ver se isso permitirá o acesso ao servidor:

      • sudo systemctl restart mysql

      Se o servidor permanecer travado ou inacessível, então pode ser útil ativar a opção force_recovery do InnoDB. Você pode fazer isso editando o arquivo mysqld.cnf:

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

      Na seção [mysqld], adicione a seguinte linha:

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

      
      . . .
      [mysqld]
      . . .
      innodb_force_recovery=1
      

      Salve e feche o arquivo, e então tente reiniciar o serviço MySQL novamente. Se você conseguir acessar com sucesso a tabela corrompida, use o utilitário mysqldump para descarregar os dados da sua tabela para um novo arquivo. Você pode nomear este arquivo como quiser, mas aqui nós o nomearemos como out.sql:

      • mysqldump nome_do_bando_de_dados nome_da_tabela > out.sql

      Em seguida, elimine a tabela do banco de dados. Para evitar ter que reabrir o prompt do MySQL, você pode usar a seguinte sintaxe:

      • mysql -u usuário -p --execute="DROP TABLE nome_do_banco_de_dados.nome_da_tabela"

      Depois disso, restaure a tabela com o arquivo de dump que você acabou de criar:

      • mysql -u usuário -p < out.sql

      Observe que o mecanismo de armazenamento InnoDB geralmente é mais tolerante a falhas do que o antigo mecanismo MyISAM. As tabelas que usam o InnoDB ainda podem ser corrompidas, mas devido a seus recursos de recuperação automática o risco de corrupção da tabela e falhas é decididamente menor.

      Erros de Soquete

      O MySQL gerencia conexões com o servidor de banco de dados através do uso de um arquivo de soquete, um tipo especial de arquivo que facilita a comunicação entre diferentes processos. O arquivo de soquete do servidor MySQL é chamado mysqld.sock e nos sistemas Ubuntu ele é normalmente armazenado no diretório /var/run/mysqld/. Este arquivo é criado automaticamente pelo serviço MySQL.

      Às vezes, alterações no sistema ou na configuração do MySQL podem fazer com que o MySQL não consiga ler o arquivo de soquete, impedindo o acesso aos seus bancos de dados. O erro de soquete mais comum é semelhante ao seguinte:

      Output

      ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

      Existem algumas razões pelas quais esse erro pode ocorrer e algumas maneiras possíveis de resolvê-lo.

      Uma causa comum desse erro é que o serviço MySQL está parado ou não iniciou, o que significa que não foi possível criar o arquivo de soquete em um primeiro momento. Para descobrir se este é o motivo pelo qual você está vendo este erro, tente iniciar o serviço com systemctl:

      • sudo systemctl start mysql

      Em seguida, tente acessar o prompt do MySQL novamente. Se você ainda receber o erro de soquete, verifique novamente o local onde sua instalação do MySQL está procurando pelo arquivo de soquete. Esta informação pode ser encontrada no arquivo mysqld.cnf:

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

      Procure o parâmetro socket na seção[mysqld] deste arquivo. Isso parecerá assim:

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

      . . . [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 . . .

      Feche este arquivo, então certifique-se de que o arquivo mysqld.sock exista executando um comando ls no diretório onde o MySQL espera encontrá-lo:

      Se o arquivo de soquete existir, você o verá na saída deste comando:

      Output

      . .. mysqld.pid mysqld.sock mysqld.sock.lock

      Se o arquivo não existir, o motivo pode ser que o MySQL está tentando criá-lo, mas não possui permissões adequadas para isso. Você pode garantir que as permissões corretas estão em vigor, alterando a propriedade do diretório para o usuário e grupo mysql:

      • sudo chown mysql:mysql /var/run/mysqld/

      Em seguida, certifique-se de que o usuário mysql tenha as permissões apropriadas sobre o diretório. Definindo-as para 755 vai funcionar na maioria dos casos:

      • sudo chmod -R 755 /var/run/mysqld/

      Finalmente, reinicie o serviço MySQL para que ele possa tentar criar o arquivo de soquete novamente:

      • sudo systemctl restart mysql

      Em seguida, tente acessar o prompt do MySQL mais uma vez. Se você ainda encontrar o erro de soquete, provavelmente há um problema mais profundo com a sua instância do MySQL; nesse caso, você deve revisar o log de erros para ver se ele pode fornecer alguma pista.

      Conclusão

      O MySQL serve como o backbone de inúmeros aplicativos e sites orientados a dados. Com tantos casos de uso, há também tantas possíveis causas de erros. Da mesma forma, também há muitas maneiras diferentes de resolver tais erros. Neste guia, cobrimos alguns dos erros mais frequentemente encontrados, mas há muitos mais que podem surgir dependendo de como o seu aplicativo funciona com o MySQL.

      Se você não conseguiu encontrar uma solução para o seu problema específico, esperamos que este guia lhe tenha dado, pelo menos, algum conhecimento sobre solução de problemas do MySQL e o ajude a encontrar a fonte dos seus erros. Para mais informações, você pode ver a documentação oficial do MySQL, que abrange os tópicos que discutimos aqui, bem como outras estratégias de solução de problemas.

      Além disso, se o seu banco de dados MySQL estiver hospedado em um Droplet da DigitalOcean, você pode contatar nossa equipe de Suporte para obter mais assistência.

      Por Mark Drake



      Source link