Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


segunda-feira, 16 de abril de 2018

Abordando a instalação e configuração do MySQL no Linux

Por Eduardo Legatti

Olá,



Neste artigo irei abordar a instalação e configuração do MySQL (5.7.18 x86-64) em um Linux Centos 7.3 (64 bits). O primeiro passo é realizar o download dos pacotes de instalação para o servidor. Para isso, irei usar o utilitário wget, conforme a seguir.

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 335516 (328K) [application/x-redhat-package-manager]
Salvando em: “mysql-community-common-5.7.18-1.el7.x86_64.rpm”

100%[==============================================================================================>] 335.516     1,21M/s   em 0,3s

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 2103888 (2,0M) [application/x-redhat-package-manager]
Salvando em: “mysql-community-libs-5.7.18-1.el7.x86_64.rpm”

100%[===============================================================================================>] 2.103.888   3,40M/s   em 0,6s

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 23039984 (22M) [application/x-redhat-package-manager]
Salvando em: “mysql-community-client-5.7.18-1.el7.x86_64.rpm”

100%[===============================================================================================>] 23.039.984  2,86M/s   em 7,3s

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 156322552 (149M) [application/x-redhat-package-manager]
Salvando em: “mysql-community-server-5.7.18-1.el7.x86_64.rpm”

100%[===============================================================================================>] 156.322.552  967K/s   em 3m 4s

[root@linux1 ~]# ls -lh *.rpm
-rw-r--r-- 1 root root 328K Mar 20 06:40 mysql-community-common-5.7.18-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 2,1M Mar 20 06:40 mysql-community-libs-5.7.18-1.el7.x86_64.rpm
-rw-r--r-- 1 root root  22M Mar 20 06:40 mysql-community-client-5.7.18-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 150M Mar 20 06:41 mysql-community-server-5.7.18-1.el7.x86_64.rpm

Após a realização do download dos pacotes de instalação, irei realizar a instalação dos mesmos usando o utilitário rpm. Vale a pena salientar que a ordem de instalação dos pacotes a seguir é importante.

[root@linux1 ~]# rpm -ivh mysql-community-common-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-common-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-common ########################################### [100%]

[root@linux1 ~]# rpm -ivh mysql-community-libs-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-libs-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-libs   ########################################### [100%]

[root@linux1 ~]# rpm -ivh mysql-community-client-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-client-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-client ########################################### [100%]

[root@linux1 ~]# rpm -ivh mysql-community-server-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-server-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-server ########################################### [100%]

Pronto. Podemos checar pelo comando abaixo que os pacotes do MySQL foram instalados com sucesso.

[root@linux1 ~]# rpm -qa | grep mysql
mysql-community-server-5.7.18-1.el7.x86_64
mysql-community-common-5.7.18-1.el7.x86_64
mysql-community-libs-5.7.18-1.el7.x86_64
mysql-community-client-5.7.18-1.el7.x86_64

Se quisermos prosseguir com a desinstalação do MySQL, basta utilizarmos os comandos abaixo.

[root@linux1 ~]# rpm -e mysql-community-server-5.7.18-1.el7.x86_64 --nodeps
[root@linux1 ~]# rpm -e mysql-community-common-5.7.18-1.el7.x86_64 --nodeps
[root@linux1 ~]# rpm -e mysql-community-libs-5.7.18-1.el7.x86_64 --nodeps
[root@linux1 ~]# rpm -e mysql-community-client-5.7.18-1.el7.x86_64 –nodeps
[root@linux1 ~]# rm -rf /var/lib/mysql/

Uma vez que o MySQL já se encontra está instalado, poderemos iniciar o seu serviço e começar a realizar a configuração inicial, conforme demonstrado abaixo.

[root@linux1 ~]# systemctl start mysqld
* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2017-05-02 13:45:55 BST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3105 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3084 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3109 (mysqld)
   CGroup: /system.slice/mysqld.service
           ??3109 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Abr 16 13:45:50 linux1 systemd[1]: Starting MySQL Server...
Abr 16 13:45:55 linux1 systemd[1]: Started MySQL Server.

Após a inicialização do serviço da instância do MySQL, poderemos conectar no mesmo após obter uma senha temporária.

[root@linux1]# grep 'temporary password' /var/log/mysqld.log
2018-04-16T12:30 [Note] A temporary password is generated for root@localhost: 8V*vubKtFQp(

[root@linux1 ~]# mysql -uroot –p'8V*vubKtFQp('
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 Server version: 5.7.18

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Pronto. Após conectarmos na instância com o usuário root, somos forçados a alterar a senha por questões de segurança

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Agora irei realizar o procedimento de alteração da senha que deve conter pelo menos uma letra maiúscula, um caractere especial, além de números, como também alterar a política de segurança de senhas de MEDIUM para LOW.

mysql> alter user root@localhost identified by 'Asdjer3jk4kjd4@#';
Query OK, 0 rows affected (0,00 sec)

mysql> show variables like '%password_policy';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| validate_password_policy | MEDIUM |
+--------------------------+--------+
1 row in set (0,07 sec)

mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0,03 sec)

Após diminuir o nível de segurança da senha, é possível perceber que ainda não é possível utilizar uma senha mais simples. Apesar de não precisar de letras maiúsculas, e caracteres especiais, a mesma precisa ter pelo menos um tamanho de 8 caracteres conforme demonstrado abaixo.

mysql> alter user root@localhost identified by 'admin';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> alter user root@localhost identified by '12345678';
Query OK, 0 rows affected (0,01 sec)

Para desativar esta obrigatoriedade, bastará desinstalar o plugin validate_password.

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0,01 sec)

mysql> UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected (0,07 sec)

Uma vez desinstalado, poderemos usar uma senha mais simples.

mysql> alter user root@localhost identified by 'admin';
Query OK, 0 rows affected (0,00 sec)

Após a alteração da senha, poderemos instalar novamente o plugin validate_password, caso seja necessário.

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0,03 sec)

Para realizarmos qualquer operação no MySQL, fazemos uso do utilitário mysql.

[root@linux1]# whereis mysql
mysql: /usr/bin/mysql /usr/lib/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

Para iniciar, parar ou checar o status da instância do MySQL, poderemos fazer uso do comando systemctl. No Linux 6.x e anteriores, fazíamos uso do comando service.

[root@linux1 ~]# systemctl start mysqld
[root@linux1 ~]# systemctl status mysqld
* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2017-05-02 13:45:55 BST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3105 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3084 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3109 (mysqld)
   CGroup: /system.slice/mysqld.service
           ??3109 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Abr 16 13:45:50 linux1 systemd[1]: Starting MySQL Server...
Abr 16 13:45:55 linux1 systemd[1]: Started MySQL Server.

[root@linux1 ~]# systemctl stop mysqld
[root@linux1 ~]# systemctl status mysqld
* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Thu 2017-05-02 13:47:00 BST; 904ms ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3105 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3084 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3109 (code=exited, status=0/SUCCESS)

Abr 16 13:45:50 linux1 systemd[1]: Starting MySQL Server...
Abr 16 13:45:55 linux1 systemd[1]: Started MySQL Server.
Abr 16 13:46:59 linux1 systemd[1]: Stopping MySQL Server...
Abr 16 13:47:00 linux1 systemd[1]: Stopped MySQL Server.

Em relação a possibilidade de conexão remota à instância do MySQL, podemos checar que qualquer computar poderá ter acesso ao mesmo. Caso queiramos que a conectividade fuique restrita ao servidor, bastara apenas setar o parâmetro bind_address para 127.0.0.1.

mysql> show variables like '%bind%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| bind_address  | *     |
+---------------+-------+

[root@linux1 ~]# netstat -na | grep 3306
tcp        0      0 :::3306       :::*          LISTEN

Para finalizar, segue abaixo umas informações do arquivo de configuração da instância do MySQL. O mesmo pode ser encotrado em /etc/my.cnf, mas dependendo da distribuição do Linux, ele poderá estar em outro diretório.

[root@linux1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



Nenhum comentário:

Postagens populares