Configuración MariaDB (MySQL) Gestor Base de Datos

En abril de 2009, Oracle compró el proyecto MySQL. Como resultado, se creó una bifurcación comunitaria MySQL llamada MariaDB. La razón principal para crear la bifurcación fue mantener el proyecto libre bajo la Licencia Pública General. WordPress, Joomla, Magento y demás utilizan siempre una Base De Datos para guardar el contenido. El paso de MySQL a MariaDB es relativamente fácil y tiene la ventaja adicional de que MariaDB es compatible con todos los scripts PHP, al menos con WordPress, XenForo, phpBB, MyBB, SMF, Drupal, Vbulletin.

 

 

Consulta SQL para Ver Variables por defecto y las actuales:

select information_schema.system_variables.variable_name,
information_schema.system_variables.default_value,
global_variables.variable_value from
information_schema.system_variables,information_schema.global_variables where
system_variables.variable_name=global_variables.variable_name and
system_variables.default_value <> global_variables.variable_value and
system_variables.default_value <> 0

MyISAM vs InnoDB

 Muchos sitios antiguos todavía usan el motor de almacenamiento MyISAM en su base de datos. Hace ya muchos años InnoDB ha demostrado ser más eficaz y más confiable. Una razón para usar InnoDB en lugar de MyISAM, es la falta del bloqueo a nivel de tabla completo (full table-level locking). Esto permite un proceso de consultas más rápido. El valor más importante para tunear (size) en MyISAM es el Key Buffer y en InnoDB su equivalente es el Buffer Pool

InnoDB vs MyISAM

Aquí están la mayores diferencias entre InnoDB y MyISAM:

• InnoDB tiene bloqueo a nivel de renglón (row-level locking). MyISAM sólo tiene bloqueo a nivel de tabla completo.
• InnoDB tiene la que llaman integridad referencial que se trata de soportar claves externas (RDBMS) y restricciones, MyISAM no lo hace (DMBS).
• InnoDB soporta transacciones, lo que significa que se puede guardar y deshacer. Con MyISAM no se puede.
• InnoDB es más confiable al hacer registros transaccionales. MyISAM no lo es.

Ver tablas MyIsam

SELECT TABLE_NAME,
 ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database' and ENGINE = 'myISAM'

Convertir MyISAM en InnoDB con phpMyAdmin

Se puede convertir MyISAM en InnoDB bastante fácilmente. En este ejemplo abajo se usan la tabla wp_comments. Simplemente ejecute el comando ALTER para convertirlo en motor de almacenamiento InnoDB. Nota: Antes de realizar cualquier tipo de operaciones en su base de datos MySQL, siempre recomendamos hacer un backup sobre ello.

ALTER TABLE wp_comments ENGINE=InnoDB;

Asegúrese que estás ejecutando MySQL 5.6.4 o una versión superior. En caso contrario puede tener varios problemas donde InnoDB ya no soporta full-text indexing. 

Convertir todas las tablas MyISAM a InnoDB de una base de datos:

 

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
 FROM INFORMATION_SCHEMA.TABLES
 WHERE ENGINE='MyISAM'
 AND table_schema = 'mibasededatos';

Para ver las tablas y el tamaño de las BD que tenemos en MyISAM e InnoDB actualmente ejecutar la siguiente consulta:

SELECT  ENGINE,

        ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",

        ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",

        ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",

        COUNT(*) "Num Tables"

    FROM  INFORMATION_SCHEMA.TABLES

    WHERE  table_schema not in ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")

    GROUP BY  ENGINE;

 

Nos mostrará 

• "Engine" (motor)
• Data (Datos)
• Index (índices)
• Total MB
• Número Tablas

Tuning MySQL (Optimización y rendimiento de MariaDB)

Configurando los Límites (/etc/security/limits.conf)

# https://mariadb.com/kb/en/configuring-linux-for-mariadb/

mysql soft nofile 65535
mysql hard nofile 65535

mysql soft core unlimited
mysql hard core unlimited

Herramientas para optimizar

• MySQLTuner
• TUNING-PRIMER

Fichero configuración my.cnf

InnoDB un archivo por tabla

InnoDB es el motor predeterminado de mariaDB y después de configurar estos parámetros, todas las tablas tendrán su propio archivo .idb en el servidor. Ahora surge la pregunta, ¿cómo hará que MariaDB sea más eficiente? Como todas las operaciones realizadas en esta tabla usarán la E / S de ese único archivo .idb y si trunca esa tabla, puede reclamar ese espacio, ya que se eliminará el archivo de esa tabla. También puede almacenar tablas en diferentes dispositivos de almacenamiento, ya que los archivos .idb pueden estar presentes en otro servidor. Para habilitar este parámetro, debe configurar el siguiente parámetro en el archivo /etc/my.cnf

innodb_file_per_table = 1

 Tamaño de búfer de InnoDB

La gestión de la memoria es fundamental en los servidores de bases de datos, ya que puede haber cientos y miles de transacciones ejecutándose por segundo. Puede habilitar el almacenamiento en caché y la indexación en el servidor MariaDB configurando el parámetro de tamaño del grupo de búfer de InnoDB. La cantidad de memoria que desea dedicar depende únicamente de la cantidad de RAM que tenga su servidor. Si su servidor está dedicado a la base de datos, puede establecer el parámetro en el 60 por ciento de su memoria, pero si otros servicios se ejecutan en el mismo servidor, debe considerar un valor diferente. El valor predeterminado del tamaño del grupo de búfer es 8 MB y puede cambiar este valor agregando la siguiente línea en su archivo my.cnf

Algo que también hay que saber es que siempre que nuestro innodb_buffer_pool_size sea menor de 1GB, nuestro innodb_buffer_pool_instances será 1, así como el valor por defecto de innodb_buffer_pool_size son 128MB

El valor aunque puede ser hasta un 80% de la memoria RAM total instalada, dependerá del tamaño de nuestras tablas en la base de datos. No tiene sentido asignar 10GB si nuestra tabla más grande ocupa 1GB o menos.

  innodb_buffer_pool_size = 1G

 

El motor InnoDB tiene un grupo de búfer que se utiliza para almacenar datos e índices en la memoria. Por supuesto, esto ayudará a que sus consultas de MySQL / MariaDB se ejecuten significativamente más rápido. Elegir aquí el tamaño adecuado requiere algunas decisiones muy importantes y un buen conocimiento del consumo de memoria de su sistema.

Esto es lo que debe considerar:

•      Cuánta memoria necesitas para otros procesos. Esto incluye los procesos de su sistema, tablas de páginas, búferes de socket.
•      ¿Su servidor está dedicado para MySQL o ejecutará otros servicios que consumen mucha memoria?

En un servidor dedicado, probablemente querrá ceder alrededor del 60-70% de la memoria RAM a innodb_buffer_pool_size. Si planea ejecutar más servicios en el servidor, debe reconsiderar la cantidad de memoria que dedica a su innodb_buffer_pool_size.

 Para calcularlo podemos usar la siguiente consulta:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Esto nos dará el RIBPS (Recommended InnoDB Buffer Pool Size) basdo en todas las tablas InnoDB Data e Indexes (índicies) con un adicional del 60%.

Por ejemplo:

mysql>     SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    ->     (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    ->     FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     8 |
+-------+
1 row in set (4.31 sec)

[mysqld]
innodb_buffer_pool_size=8G

 

¿Y qué hay de la regla del 80% de RAM?

A priori, esta regla nos indica que deberíamos asignar un 80% de nuestra memoria física a nuestro buffer pool, Quiere decir que si tenemos 1TB de RAM deberíamos asignar 250GB? Como habréis deducido no tiene ningún sentido. Esta regla es un punto de partida inicial pero servidores pequeños pero una vez se van dimensionando dependerá de muchos factores, tales como si el servidor de base de datos es dedicado o no.

 

 Activar Log Consultas lentas

# log querys lentas
# enable the log of slow queries
slow_query_log = 1
# set the time in seconds
long_query_time = 1
# the name of your slow query log file and the path to it
slow_query_log_file = /var/log/mysqld-slow-querys.log
# whether to write queries that do not use indexes to the log file log_queries_not_using_indexes

Crear fichero y propietario correctos:

touch /var/log/mysqld-slow-querys.log
chown mysql:mysql /var/log/mysqld-slow-querys.log

Mucho cuidado con aumentar sin sentido los valores como:

sort_buffer_size no es específico de ningún motor de almacenamiento y se aplica de manera general para la optimización.

Lo primero que debe saber es que sort_buffer_size es un búfer por sesión. Es decir, esta memoria se asigna por conexión / hilo.

En segundo lugar, internamente en el uso del sistema operativo independientemente de MySQL, hay un umbral> 256K. Si el búfer se establece en un valor superior a 256 K, utiliza mmap () en lugar de malloc () para la asignación de memoria. En realidad, esto es algo de libc malloc y se puede ajustar, pero el valor predeterminado es 256k. Un búfer de más de 256 K provoca una ralentización de 37 veces. Esto se aplica a todos los búferes por sesión, no solo al búfer de clasificación.

Si ve muchos sort_merge_passes por segundo en la salida SHOW GLOBAL STATUS, puede considerar aumentar el valor sort_buffer_size para acelerar las operaciones ORDER BY o GROUP BY que no se pueden mejorar con la optimización de consultas o la indexación mejorada. El búfer completo se asigna incluso si no se necesita todo, por lo que establecerlo en un valor mayor al requerido globalmente ralentizará la mayoría de las consultas de ese tipo. Es mejor aumentarlo como configuración de sesión y solo para las sesiones que necesitan un tamaño mayor.

En conclusión, en Linux, existen umbrales de 256 KB y 2 MB en los que los valores más grandes pueden ralentizar significativamente la asignación de memoria, por lo que debe considerar permanecer por debajo de uno de esos valores. Experimente para encontrar el mejor valor para su carga de trabajo.

Seguridad básica - Permisos usuario por Base de Datos

Recuerda otorgar permisos a los usuarios por base de datos, es decir, que cada usuario sólo puede ver y manipular su DB, y no el resto.

• ALL – permite el acceso completo a una base de datos específica. Si no se especifica una base de datos, permite el acceso completo a la totalidad de MySQL.
• CREATE – permite que un usuario cree bases de datos y tablas.
• DELETE – permite que un usuario elimine filas de una tabla.
• DROP – permite que un usuario elimine bases de datos y tablas.
• EXECUTE – permite que un usuario ejecute rutinas almacenadas.
• GRANT OPTION – permite que un usuario conceda o elimine los privilegios de otro usuario.
• INSERT – permite que un usuario inserte filas de una tabla.
• SELECT – permite que un usuario seleccione datos de una base de datos.
• SHOW DATABASES – permite que un usuario vea una lista de todas las bases de datos.
• UPDATE – permite que un usuario actualice filas en una tabla.

Seguridad básica

Recuerda una vez has instalado MySQL (MariaDB) ejecutar el script de instalación segura.

Ya que después de instalar MySQL siempre es bueno securizar el sitio, ponerle una contraseña al usuario root, quitar accesos remotos…

• mariadb-secure-installation o mysql_secure_installation

sudo mysql_secure_installation

Opciones de Seguridad fichero my.cnf

#security
local-infile=0
# para poner mysql remoto comentar (mysql sólo en local)
skip-networking
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#no dns lookups
skip-name-resolve

Actualmente se recomienda dejar query_cache_size en 0 y query_cache_type en OFF

Optimizar MyISAM

El valor más importante para tablas MySAM es el:

• key_buffer_size

Advertencia importante de MySQL®️

MySQL advierte: establecer un valor demasiado grande aquí puede hacer que el sistema comience a no funcionar. Esto se debe a la dependencia de MySQL del caché del sistema de archivos del sistema operativo, que es necesario para las operaciones de lectura de datos. Es imperativo que quede suficiente memoria libre para el caché del sistema de archivos.

La cantidad de sobrecarga requerida por key_buffer_size para las tareas administrativas es pequeña pero crece junto con el tamaño de los índices de la tabla. El uso de un valor genérico del 5% de la longitud total del índice de todas las tablas MyISAM garantiza que haya suficiente espacio disponible en la caché de claves para gastos generales. Sin embargo, en configuraciones de bases de datos extremadamente grandes, la sobrecarga requerida se puede reducir aún más aumentando key_cache_block_size. Esto se debe a que se accede a más filas a la vez, lo que reduce la necesidad de gastos administrativos para enmarcar esas filas adicionales.

Cómo calcular el tamaño de la caché de claves MyISAM (key_buffer_size)

Hay un par de métodos diferentes que se utilizan para calcular un key_buffer_size adecuado. Estos se enumeran y detallan a continuación.

1. Calcular key_buffer_size de TODAS las longitudes de índice MyISAM
2. Servidor dedicado: Calcule key_buffer_size a partir de la memoria física total (recomendado por MySQL)
3. Servidor compartido: Calcule key_buffer_size de la memoria del sistema disponible

Calcular key_buffer_size de TODAS las longitudes de índice MyISAM

Para mantener el uso de memoria de key_buffer_size tan pequeño como sea posible con poco riesgo de establecer el valor demasiado pequeño. Este método se basa en calcular la longitud total de los índices para todas las tablas MyISAM actualmente en el servidor.

Regla de oro: Calcule key_buffer_size de todas las longitudes del índice MyISAM.

Establezca key_buffer_size en la longitud total del índice de todos los índices de la tabla MyISAM más un 5% para gastos generales. El siguiente script de muestra ayuda a realizar este cálculo leyendo los valores necesarios de la base de datos INFORMATION_SCHEMA. El valor de porcentaje (PCT) utilizado para calcular los gastos generales se puede ajustar. El 5 resaltado en la primera línea se puede establecer en el porcentaje deseado necesario.

set @overhead = 5 / 100;

        select count(INDEX_LENGTH) as Indexes,

        sum(INDEX_LENGTH) as Total_Index_Length,

        floor(@overhead * 100) as PCT,

        floor(sum(INDEX_LENGTH)*@overhead) as Overhead,

        floor(sum(INDEX_LENGTH)*(1+@overhead)) as key_buffer_size

        FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';"

o bien:

SELECT CONCAT(ROUND(KBS/POWER(1024,  IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),  SUBSTR(' KMG',IF(PowerOf1024<0,0,  IF(PowerOf1024>3,0,PowerOf1024))+1,1))  recommended_key_buffer_size FROM  (SELECT LEAST(POWER(2,32),KBS1) KBS  FROM (SELECT SUM(index_length) KBS1  FROM information_schema.tables  WHERE engine='MyISAM' AND  table_schema NOT IN ('information_schema','mysql')) AA ) A,  (SELECT 3 PowerOf1024) B;

Copias de seguridad de MySQL 

Tenemos varias opciones:

• mysqldump
  
• mysqlhotcopy (deprecated)
• mariabackup (fork de Percona XtraBackup)
  

 MySQLHotCopy

mysqlhotcopy está actualmente en desuso. Sólo sirve para tablas y DB en MyIsam (no funciona con InnoDB). Actualmente el mejor sistema  es usar mysqldump o bien Mariabackup que es un fork de Percona XtraBackup.

mysqldump 

Lo ideal es primero el "dump" y luego comprimir para que se más rápido y no las dos cosas a la vez.

 

Básico

 mysqldump [OPTIONS] database [tables]

 Ejemplo básico

mysqldump -u -p base_datos > fichero.sql

 A tener en cuenta:

mysqldump hace un backup completo de la base de datos pero se pueden añadir parámetros:

• sólo datos
• estructura
• eventos,
• procedimientos
• triggers
• vistas

Opciones:

• –all-databases (todas las BD)
  
• charset
• comprimir con (se puede comprimir el sql con gzip (sql.gz) o bzip2 (sql.bz2) 
• --host -h (host o la ip (sql remoto)
• usa  > para hacer la copia
• < para restaurar la copia
  

Siempre pedirá usuario y contraseña:

• -u  o la forma larga --user=USUARIO
  
• -p o la forma larga   --password=pass

 Recuerda que al usar el parámetro -p, la contraseña se escribirá a continuación sin añadir ningún espacio en blanco entre -p y la contraseña.

Todas las base de datos

mysqldump [opciones] --all-databases > fichero.sql

Ejemplos comprimir con gzip

mysqldump -u dbUser -p DBName | gzip > db.sql.gz

Con bzip2

mysqldump -u dbUser -p DBName | bzip2 >db.sql.bz2