Usando Linux para muchas cosas y disfrutando de videojuegos, en dispositivos moviles, consolas y pc. Using Linux for many things and enjoying video games, mobile devices, consoles and pc

Como optimizar MySQL / MariaDB de forma segura

Como optimizar MySQL / MariaDB de forma segura

Muchas de las aplicaciones de hoy en día utilizan MySQL o MariaDB como motor de bases de datos para soluciones LAMP. En base a esto se necesita que los query (consultas por medio de SQL) sean lo mas rápido posible para que no tengamos retrasos en la entrega de datos. Nuestro articulo hoy sera sobre como optimizar MySQL / MariaDB de forma segura.

Ademas de tener un buen código, es buena idea tener bien optimizado tu sistema para que todo sea rápido. Buscando en internet encontré este script que nos permite optimizar el motor de bases de datos de una forma bastante sencilla.

Su nombre es MySQLTuner y esta escrito en perl pudiéndose ejecutar en cualquier distribución Linux actual que tenga perl instalado. Para este articulo lo mostrare en un servidor Linux Centos 7 con MariaDB pero esto no significa que no lo puedas ejecutarlo en otra distribución Linux cumpliendo los siguientes requerimientos.

Requerimientos para ejecutar MySQLTuner

  • Linux
  • MariaDB o MySQL
  • Cuenta root del motor de bases de datos
  • Perl

Como optimizar MySQL / MariaDB de forma segura

-Antes de comenzar te recomiendo siempre tener un backup de la configuracion de tu MariaDB o MySQL. Ya sea copiando el /etc/my.cnf a otro carpeta o dejando una copia comentada de las variables modificadas

-Instalamos para nuestro uso
yum install wget less

-El primer paso es verificar que nuestro servidor de bases de datos este ejecutándose. Ejecutamos el siguiente comando

sudo systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2018-02-16 20:32:43 UTC; 12min ago
 Main PID: 389 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─389 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─550 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/pl...

Feb 16 20:32:38 cenwp.drivemeca.com systemd[1]: Starting MariaDB database server...
Feb 16 20:32:38 cenwp.drivemeca.com mariadb-prepare-db-dir[358]: Database MariaDB is probably initialized i...e.
Feb 16 20:32:39 cenwp.drivemeca.com mysqld_safe[389]: 180216 20:32:39 mysqld_safe Logging to '/var/log/mar...g'.
Feb 16 20:32:39 cenwp.drivemeca.com mysqld_safe[389]: 180216 20:32:39 mysqld_safe Starting mysqld daemon w...sql
Feb 16 20:32:43 cenwp.drivemeca.com systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.
Verificamos este ejecutandose el servicio en Linux

-Para conocer mas sobre MariaDB verificamos que versión tenemos instalada en nuestro Linux Centos
rpm -qa|grep mariadb
mariadb-5.5.56-2.el7.x86_64
mariadb-server-5.5.56-2.el7.x86_64
mariadb-libs-5.5.56-2.el7.x86_64
Conocer que paquetes tienes de MariaDB puede ser ventajoso

-Creamos un directorio para descargar el script y ejecutarlo desde ahi
mkdir mysqltuner
cd mysqltuner

-Descargamos el script MySQLTuner junto a dos archivos mas que nos pueden servir para otras tareas de verificacion
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
Con MySQLTuner descargamos 2 archivos mas

-Damos permisos de ejecución al script
chmod +x mysqltuner.pl

-Tienes dudas de como usar el script. Su ayuda te servirá para sacarle el mayor provecho al script
./mysqltuner.pl --help
Name:
     MySQLTuner 1.7.5 - MySQL High Performance Tuning Script

Important Usage Guidelines:
    To run the script with the default options, run the script without
    arguments Allow MySQL server to run for at least 24-48 hours before
    trusting suggestions Some routines may require root level privileges
    (script will provide warnings) You must provide the remote server's
    total memory when connecting to other servers

Connection and Authentification:
     --host <hostname>           Connect to a remote host to perform tests (default: localhost)
     --socket <socket>           Use a different socket for a local connection
     --port <port>               Port to use for connection (default: 3306)
     --user <username>           Username to use for authentication
     --userenv <envvar>          Name of env variable which contains username to use for authentication
     --pass <password>           Password to use for authentication
     --passenv <envvar>          Name of env variable which contains password to use for authentication
     --ssl-ca <path>             Path to public key
     --mysqladmin <path>         Path to a custom mysqladmin executable
     --mysqlcmd <path>           Path to a custom mysql executable
     --defaults-file <path>      Path to a custom .my.cnf

Performance and Reporting Options:
     --skipsize                  Don't enumerate tables and their types/sizes (default: on)
                                 (Recommended for servers with many tables)
     --skippassword              Don't perform checks on user passwords(default: off)
     --checkversion              Check for updates to MySQLTuner (default: don't check)
     --updateversion             Check for updates to MySQLTuner and update when newer version is available (default: don't check)
     --forcemem <size>           Amount of RAM installed in megabytes
     --forceswap <size>          Amount of swap memory configured in megabytes
     --passwordfile <path>       Path to a password file list(one password by line)

Output Options:
     --silent                    Don't output anything on screen
     --nogood                    Remove OK responses
     --nobad                     Remove negative/suggestion responses
     --noinfo                    Remove informational responses
     --debug                     Print debug information
     --dbstat                    Print database information
     --idxstat                   Print index information
     --sysstat                   Print system information
     --pfstat                    Print Performance schema
     --bannedports               Ports banned separated by comma(,)
     --maxportallowed            Number of ports opened allowed on this hosts
     --cvefile <path>            CVE File for vulnerability checks
     --nocolor                   Don't print output in color
     --json                      Print result as JSON string
     --buffers                   Print global and per-thread buffer values
     --outputfile <path>         Path to a output txt file
     --reportfile <path>         Path to a report txt file
     --template   <path>         Path to a template file
     --verbose                   Prints out all options (default: no verbose)

-La forma mas sencilla de ejecutarlo es sin parámetros. De esta forma buscara un MySQL /MariaDB ejecutándose localmente. MySQLTuner nos muestra mucha información ayudado con colores

  • Mensajes en verde: Normal
  • Mensajes en rojo: Corregir o mejorar


Se nos pedirá la cuenta root (o una cuenta con permisos de root) y contraseña de MySQL / MariaDB
./mysqltuner.pl 
 >>  MySQLTuner 1.7.5 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.5.56-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb.log(3K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[OK] /var/log/mariadb/mariadb.log doesn't contain any warning.
[OK] /var/log/mariadb/mariadb.log doesn't contain any error.
[--] 2 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 180216 20:32:42 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 171127 23:14:55 [Note] /usr/libexec/mysqld: ready for connections.
[--] 1 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 171127 23:57:34 [Note] /usr/libexec/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 976K (Tables: 12)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 24m 45s (17 q [0.011 qps], 15 conn, TX: 40K, RX: 1K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 512.0M
[--] Max MySQL memory    : 836.0M
[--] Other process memory: 78.2M
[--] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 418.8M (81.79% of installed RAM)
[!!] Maximum possible memory usage: 836.0M (163.28% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/17)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 46.67%  (7/15)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 7 selects)
[OK] Query cache prunes per day: 0
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 4 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 100% (26 open / 0 opened)
[OK] Open file limit used: 2% (21/1K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (5.5.56-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/99.0K

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[OK] InnoDB buffer pool / data size: 128.0M/976.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.8125 %): 5.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 91.91% (4531 hits/ 4930 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Set thread_cache_size to 4 as a starting value
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    thread_cache_size (start at 4)
    innodb_file_per_table=ON

    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

-Despues de ejecutar MySQLTuner veremos al final una serie de recomendaciones para agregar a nuestro motor de bases de datos. Editamos y agregamos
vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
thread_cache_size=4

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

-Reiniciamos el servicio y verificamos quede ejecutándose correctamente con los cambios en su configuracion
systemctl restart mariadb
systemctl status mariadb


-Tienes dudas de como mas ejecutar el script? Su ayuda te servira
./mysqltuner.pl --help
Name:
     MySQLTuner 1.7.5 - MySQL High Performance Tuning Script

Important Usage Guidelines:
    To run the script with the default options, run the script without
    arguments Allow MySQL server to run for at least 24-48 hours before
    trusting suggestions Some routines may require root level privileges
    (script will provide warnings) You must provide the remote server's
    total memory when connecting to other servers

Connection and Authentification:
     --host <hostname>           Connect to a remote host to perform tests (default: localhost)
     --socket <socket>           Use a different socket for a local connection
     --port <port>               Port to use for connection (default: 3306)
     --user <username>           Username to use for authentication
     --userenv <envvar>          Name of env variable which contains username to use for authentication
     --pass <password>           Password to use for authentication
     --passenv <envvar>          Name of env variable which contains password to use for authentication
     --ssl-ca <path>             Path to public key
     --mysqladmin <path>         Path to a custom mysqladmin executable
     --mysqlcmd <path>           Path to a custom mysql executable
     --defaults-file <path>      Path to a custom .my.cnf

Performance and Reporting Options:
     --skipsize                  Don't enumerate tables and their types/sizes (default: on)
                                 (Recommended for servers with many tables)
     --skippassword              Don't perform checks on user passwords(default: off)
     --checkversion              Check for updates to MySQLTuner (default: don't check)
     --updateversion             Check for updates to MySQLTuner and update when newer version is available (default: don't check)
     --forcemem <size>           Amount of RAM installed in megabytes
     --forceswap <size>          Amount of swap memory configured in megabytes
     --passwordfile <path>       Path to a password file list(one password by line)

Output Options:
     --silent                    Don't output anything on screen
     --nogood                    Remove OK responses
     --nobad                     Remove negative/suggestion responses
     --noinfo                    Remove informational responses
     --debug                     Print debug information
     --dbstat                    Print database information
     --idxstat                   Print index information
     --sysstat                   Print system information
     --pfstat                    Print Performance schema
     --bannedports               Ports banned separated by comma(,)
     --maxportallowed            Number of ports opened allowed on this hosts
     --cvefile <path>            CVE File for vulnerability checks
     --nocolor                   Don't print output in color
     --json                      Print result as JSON string
     --buffers                   Print global and per-thread buffer values
     --outputfile <path>         Path to a output txt file
     --reportfile <path>         Path to a report txt file
     --template   <path>         Path to a template file

     --verbose                   Prints out all options (default: no verbose)

MySQLTuner no reemplaza a un DBA pero te servirá para hacer unos cambios simples en la configuracion de MySQL / MariaDB mejorando su rendimiento

Artículos recomendados:Acelera con nginx tu servidor Ubuntu LEMP
                       Como instalar Adminer en Linux Centos paso a paso
                       Como cambiar la contraseña de root en MySQL
                       15 minutos para configurar un Linux Centos 7 LAMP
                       Como instalar phpMyAdmin paso a paso
                       Como instalar MySQL Workbench en Linux Ubuntu paso a paso



Quiero vivir de mi blog, de enseñar opensource, pero esto no sera posible sin tu apoyo, me ayudas?


Satisfech@ con el articulo? Bien, hazme un favor, compártelo en tus redes sociales (compartir es sexy). Escríbeme en los comentarios aquí debajo y pasa la voz compartiendo el tweet.
Sígueme en twitter google+facebook, email o YouTube  y podrás estar enterado de todas mis publicaciones.
Disfrútenlo.


Share/Bookmark