Mysql 691x3a

istración MySQL

Miguel Ángel Nieto <[email protected]> Irontec – Internet y Sistemas sobre GNU/Linux

Irontec – istración de MySQL

Instalación

2

Irontec – istración de MySQL

Instalación ●

La instalación puede realizarse de las siguientes formas: – – –

●

Código fuente Binarios Sistema de paquetes de tu distribución

Cada uno tiene sus ventajas e inconvenientes –

–

–

Código fuente es laborioso, pero te permite definir mejor las carácterísticas de tu servidor, activando y desactivando a tu gusto Los binarios vienen compilados y son más fáciles de actualizar. Si están compilados con icc se puede casi doblar el rendimiento Sistema de paquetes, facil de instalar y de actualizar

3

Irontec – istración de MySQL

Instalación

●

http://s.mysql.com/archives.php Para compilar en debian es necesario como mínimo: –

●

Como siempre ./configure && make && make install – – –

●

./configure –help ./configure –prefix=/usr/local/mysql ./configure –without­plugin­innodb

Tenemos que crear los s: – –

●

apt­get install build­essential libncurses5­dev

# groupadd mysql # add ­g mysql mysql

Creamos las bases de datos necesarias para funcionar – –

# cd scripts # ./mysql_install_db 4

Irontec – istración de MySQL

Instalación ●

Damos permisos a la carpeta: – – –

●

Copiamos el fichero de configuración y lo editamos en caso de que sea necesario: –

●

# chown ­R root /usr/local/mysql # chown ­R mysql /usr/local/mysql/var # chgrp ­R mysql /usr/local/mysq

#  ­files/my­medium.cnf /etc/my.cnf

Arrancamos MySQL :) –

# /usr/local/mysql/bin/mysqld_safe –=mysql

5

Irontec – istración de MySQL

Instalación ●

Los binarios podemos descargarlos de: http://dev.mysql.com/s/mysql/ # groupadd mysql # add ­g mysql mysql # cd /usr/local # tar ­xzf mysql­VERSION­OS.tar.gz # cd mysql # chown ­R mysql . # chgrp ­R mysql . # scripts/mysql_install_db ­­=mysql # chown ­R root . # chown ­R mysql data # bin/mysqld_safe ­­=mysql &

6

Irontec – istración de MySQL

Instalación ●

●

En Debian podemos hacer la instalación con apt-get :) ¡En cualquiera de los tres modos de instalación es necesario dar una contraseña a root!

7

Irontec – istración de MySQL

SandBox ●

Para crear una laboratorio de pruebas podemos: – – –

Montar equipos físicos e instalarlos (de locos). Montar máquinas virtuales. Usar ¡sandbox!

8

Irontec – istración de MySQL

SandBox ●

SandBox nos permite: – – –

– – – –

Montar sistemas de replicación Probar versiones nuevas de MySQL facilmente Manejar múltiples instancias de MySQL desde un único punto. Te olvidas de rmps, sources, debs... ¡tar.gz binario! Testear Testear Testear...

9

Irontec – istración de MySQL

SandBox ● ●

●

No es un producto oficial de MySQL. Está escrito el perl (puag!) y aún así funciona bien. http://mysqlsandbox.net/ Tendremos que descargar un paquete tar.gz de MySQL y Sandbox.

10

Irontec – istración de MySQL

Instalación ●

¡Como root!

root@shyris:~# tar ­xzf MySQL­Sandbox­3.0.05.tar.gz root@shyris:~# cd MySQL­Sandbox­3.0.05/ root@shyris:~/MySQL­Sandbox­3.0.05# perl Makefile.PL root@shyris:~/MySQL­Sandbox­3.0.05# make root@shyris:~/MySQL­Sandbox­3.0.05# make test root@shyris:~/MySQL­Sandbox­3.0.05# make install root@shyris:/usr/local/bin# ls low_level_make_sandbox        make_multiple_sandbox     make_sandbox                  make_sandbox_from_source  sb      test_sandbox make_multiple_custom_sandbox  make_replication_sandbox   make_sandbox_from_installed  msandbox                  sbtool 

11

Irontec – istración de MySQL

Uso de SandBox ●

Crear un sandbox con una única instancia de MySQL

punisher@shyris:~$ make_sandbox /home/punisher/MySQL/mysql­ 5.0.86­percona­highperf­b19.tar.gz  unpacking /home/punisher/MySQL/mysql­5.0.86­percona­ highperf­b19.tar.gz Executing low_level_make_sandbox  ­­basedir=/home/punisher/MySQL/5.0.86 \ ­­sandbox_directory=msb_5_0_86 \ ­­install_version=5.0 \ ­­sandbox_port=5086 \ ­­no_ver_after_name \ ­­my_clause=log­error=msandbox.err

12

Irontec – istración de MySQL

Uso de SandBox ●

Parar sandbox: –

●

Arrancar sandbox: –

●

use

Reiniciar sandbox: –

●

start

Utilizar sandbox: –

●

stop

restart

Limpiar el sandbox: –

clean

13

Irontec – istración de MySQL

Clientes ● ●

Existen distintos clientes para conectarse a la BBDD Clientes de consola – – – –

●

Gráficos – – –

●

Mysql Mysqldump Mysql Mysqlimport Mysql Mysql Query Browser MySQL Workbench

etc. 14

Irontec – istración de MySQL

Clientes ●

● ●

●

Mysql: el típico, nos permite conectarse tanto de forma local como remota y ejecutar sentencias SQL Mysqldump: permite hacer backups Mysql: crear/borrar bases de datos, cambiar contraseñas, ver el estado, variables, parar slaves, etc. Mysqlimport “frontend” para “LOAD DATA INFILE” Vienen ya incluidos en MySQL

15

Irontec – istración de MySQL

Clientes ●

MySQL

16

Irontec – istración de MySQL

Clientes ●

MySQL Query Browser

17

Irontec – istración de MySQL

Clientes ●

MySQL Workbench

18

Irontec – istración de MySQL

Ficheros de configuración

19

Irontec – istración de MySQL

Ficheros de configuración ●

Algunos parámetros de configuración se pueden pasar directamente al mysqld –

●

Otros parámetros que se pueden especificar: – – – –

●

mysqld –basedir /usr/local/mysql Habilitar/deshabilitar engines Opciones de rendimiento Logs …

Es más cómodo especificarlo en los ficheros de configuración

20

Irontec – istración de MySQL

Ficheros de configuración ●

Por defecto busca los ficheros en las siguientes ubicaciones: – – –

●

/etc/my.cnf $MYSQL_HOME/my.cnf ~/.my.cnf

Se puede añadir un fichero de configuración en el arranque con: –

defaults­extra­file

21

Irontec – istración de MySQL

Ficheros de configuración ●

●

El fichero se encuentra dividido en distintas secciones, por ejemplo [mysqld], [mysql], [mysqldump] Cada opción dentro de una categoría se aplica únicamente a dicha aplicación [mysql] prompt='mysql [\h] {\u} (\d) > ' [mysqld]                             = punisher port                            = 5140 socket                          =  /tmp/mysql_sandbox5140.sock [mysqldump] quick quote­names max_allowed_packet      = 100M 22

Irontec – istración de MySQL

Engines

23

Irontec – istración de MySQL

Engines

●

● ●

MySQL es modular, permite elegir entre diferentes engines para el almacenamiento de datos Los engines se aplican a tablas, no a bases de datos Puedes tener una base de datos con diferentes engines, dependiendo del tipo de datos o consultas que se hagan

24

Irontec – istración de MySQL

Tablas ● ●

● ●

Todas las tablas de MySQL tienen ciertas similitudes Las tablas tienen .frm como formato. Este fichero guarda la estructura de la tabla Independientemente del engine, tendremos un .frm A parte, podemos tener otros ficheros acompañando al .frm

25

Irontec – istración de MySQL

Engines ●

Al crear una tabla se puede indicar el engine: create table t (i INT) ENGINE = MyISAM;

●

Una vez que está creada, se puede cambiar el engine: alter table t ENGINE = MyISAM;

●

También podemos saber con que engine se ha creado una tabla Show create table t;

26

Irontec – istración de MySQL

Engines ●

¿De que engines tenemos soporte? show engines\G

mysql [localhost] {msandbox} (mysql) >  *************************** 1. row ***************************       Engine: InnoDB      : YES      Comment: s transactions, row­level locking, and foreign keys Transactions: YES           XA: YES   Savepoints: YES *************************** 2. row ***************************       Engine: MRG_MYISAM      : YES      Comment: Collection of identical MyISAM tables Transactions: NO           XA: NO   Savepoints: NO *************************** 3. row ***************************       Engine: BLACKHOLE      : YES      Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO           XA: NO   Savepoints: NO [...]

27

Irontec – istración de MySQL

MyISAM ● ●

●

●

MyISAM es el engine por defecto en MySQL Si no se indica lo contrario, todas las tablas se crearán con este engine Es obligatorio tener soporte para el engine en MySQL, ya que todas las tablas de la BBDD son MyISAM La tabla viene definida por tres ficheros: – – –

mitabla.frm mitabla.MYD mitabla.MYI

28

Irontec – istración de MySQL

MyISAM ●

● ● ● ●

● ● ●

Antes de usar MyISAM hay que conocer bien sus virtudes y defectos :) Soporta busquedas FULLTEXT Al escribir se hace uso de bloqueo de tabla Se puede usar para generar tablas MERGE No soporta, transacciones, integridad referencial o claves externas Suele ser el más rápido en lecturas No tiene cacheo de datos Se pueden comprimir tablas para ahorrar espacio http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html 29

Irontec – istración de MySQL

MyISAM ● ●

●

● ●

El bloque en MyISAM se hace a nivel de tabla No pueden ocurrir deadlocks (dos procesos se quedan esperando a que termine el otro) Soporta inserciones concurrentes siempre y cuando la tabla no tenga “agujeros” causados por el borrado de datos (optimize table) Las escrituras tienen prioridad sobre las lecturas El servidor intenta hacer las escrituras en el orden en que las recibe

30

Irontec – istración de MySQL

MyISAM ●

●

●

Si una tabla se está leyendo y llega una petición de escritura, al escritura tiene que esperar Si una tabla se está escribiendo y llega una petición de lectura, la lectura tiene que esperar Si hay escrituras pendientes, las lecturas que lleguén tendrán que ponerse a la cola

31

Irontec – istración de MySQL

MyISAM ●

Las prioridades pueden moficiarse haciendo useo de los schedulers: –

–

–

LOW PRIORITY: para las querys que escriben datos. La escritura se queda esperando a que terminen todas las lecturas, incluso las que llegan después. Se escribirá cuando no exista ninguna lectura pendiente. HIGH PRIORITY: para las querys de lectura. Las querys con este modificador se mueven al principio de la cola, por delante de otras escrituras y lecturas DELAYED: para querys INSERT y REPLACE. El servidor mete en buffer las filas y lo inserta cuando no se esté usando la tabla. Las delayed se insertan en bloques en lugar de una a una aumentando el rendimiento 32

Irontec – istración de MySQL

MyISAM ●

●

●

●

MyISAM puede almacenar las filas en tres formatos, fixed, dynamic y compressed. FIXED –

Todas las filas tienen el mismo tamaño

–

Ocupan más espacio

–

Se encuentran más rápido

DYNAMIC –

Usa un tamaño variable de datos (menos que fixed)

–

Las filas no se encuentran tan eficientemente

–

Puede haber más fragmentación

COMPRESSED –

Ocupan mucho menos espacio

–

Optimizado para consultas rápidas

–

Solo lectura 33

Irontec – istración de MySQL

MyISAM ●

Para conocer el formato en el que se encuetran las filas de una tabla: SHOW TABLE STATUS LIKE 't'\G

●

Crear tabla FIXED:

CREATE TABLE t (c CHAR(50)) ROW_FORMAT= FIXED;

●

Crear tabla DYNAMIC: CREATE TABLE t (c CHAR(50)) ROW_FORMAT= DYNAMIC;

34

Irontec – istración de MySQL

MyISAM ●

●

● ● ●

●

Una tabla MyISAM, fixed o dynamic, puede convertirse en tabla comprimida Se suelen comprimir aquellas que se usan para datos históricos y que nunca van a ser modificadas Son de solo lectura Se comprimen con la utilidad myisampack Se debe parar el servidor antes de comprimir los datos Se descomprimen con myisamchk --unpack

35

Irontec – istración de MySQL

MyISAM $ myisampack departments employees departments is too small to compress Compressing employees.MYD: (300024 records) ­ Calculating statistics ­ Compressing file 47.12%       to run myisamchk ­rq on compressed tables

$ myisamchk ­rq departments employees ­ check record delete­chain ­ recovering (with sort) MyISAM­table 'departments' Data records: 9 ­ Fixing index 1 ­ Fixing index 2            ­­­­­­­­­ ­ check record delete­chain ­ recovering (with sort) MyISAM­table 'employees' Data records: 300024 ­ Fixing index 1 36

Irontec – istración de MySQL

Merge ●

●

●

● ●

Una tabla merge es una colección de distintas tablas con la misma estructura Una query en la tabla merge se ejecuta en todas las tablas que la componen Puede ayudarnos a superar el tamaño máximo de una tabla MyISAM (256TB) Es mas lento, ya que tiene que leer múltiples tablas Aumenta el número de descriptores de ficheros abiertos requeridos

37

Irontec – istración de MySQL

Merge ●

Se suele utilizar para borrar millones de datos en un instante. Gracias a merge podemos borrar la tabla que lo compone en lugar de ir eliminando una a una cada fila mysql> CREATE TABLE A (name varchar(100))  ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE B (name varchar(100))  ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE TOTAL (name varchar(100))  ENGINE=MERGE UNION=(A,B) INSERT_METHOD=LAST;  Query OK, 0 rows affected (0.01 sec)

38

Irontec – istración de MySQL

Merge ● ●

●

●

●

El problema de bloqueo se acentúa más con Merge Cuando se bloquea para escritura, se bloquean todas las tablas que contiene Cuando se bloquea para lectura, se bloquean todas las tablas que contiene Añadir un registro en Merge puede traer como consecuencia el bloqueo de cientos de tablas Contra más tablas y mas datos, mas problemas de rendimiento y bloqueos

39

Irontec – istración de MySQL

Memory ●

Es una tabla en memoria, lo cual ya nos da sus pros y contras solo con el nombre – – –

– –

Es muy muy rápida Los datos no se guardan después del reinicio del servidor Usan la memoria RAM, por lo que no debe usarse para tablas grandes Usa bloqueo a nivel de tablas No puede tener columnas TEXT o BLOB

40

Irontec – istración de MySQL

Memory ●

Soporta dos típos de índices, HASH y BTREE: –

–

HASH: memory las usa por defecto. El algoritmo es muy rápido para comparaciones que usen índices únicos. Pero solo se puede usar para comparaciones = o <=> BTREE: es preferible para índices que se usarán con comparaciones disintas a las anteriores. Por ejemplo: id<1024 or id BETWEEN 4000 and 5000 CREATE TABLE T (i int) ENGINE=MEMORY;

41

Irontec – istración de MySQL

Federated ●

●

●

Te permite usar tablas de otro servidores MySQL remotos De esta forma el cliente no debe conectarse a otros servidores para acceder a los datos Al estar en un servidor remoto, las federated no soportan el bloqueo de tablas CREATE TABLE federated_table (     id     INT(20) NOT NULL AUTO_INCREMENT,     name   VARCHAR(32) NOT NULL DEFAULT '',     other  INT(20) NOT NULL DEFAULT '0',     PRIMARY KEY  (id),     INDEX name (name),     INDEX other_key (other) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://fed_@remote_host:9306/federated/tes t_table';

42

Irontec – istración de MySQL

Federated ●

●

●

●

Los engines que se conectan a un servidor externo para coger datos (como FEDERATED, NDB o SPIDER) tienen un funcionamiento poco optimo cuando las querys tienen condiciones En estos engines, la condición no se envia junto con la petición, por lo tanto el servidor remoto nos transmite TODAS las filas de la tabla para que nuestro MySQL local haga la búsqueda Para solucionarlo se debe implementar “CONDITION PUSHDOWN” ya sea mediante un parche o plugin Dependiendo del engine tendremos diferentes soluciones (o en algunos casos ninguna) 43

Irontec – istración de MySQL

InnoDB ●

InnoDB nos ofrece todo aquello de lo que MyISAM carece – – – – – – –

Claves externas Integridad referencial Bloqueo de filas en lugar de tablas Auto recuperación ante errores Transacciones y rollbacks Cacheo de índices y datos Etc...

http://dev.mysql.com/doc/refman/5.1/en/innodb.html

44

Irontec – istración de MySQL

InnoDB ●

¿Y que perdemos al pasarnos a InnoDB? – – – –

Capacides de busqueda FULLTEXT La posibilidad de comprimir las tablas No poder hacer uso de Merge “Sólo” 64 TB de datos por tabla

45

Irontec – istración de MySQL

InnoDB ●

InnoDB es un engine que cumple las características ACID: –

–

– –

Atomic: O todas las sentencias se ejectuan correctamente o se cancelan. Consistent: Es consistente cuando una transacción que comienza la deja en estado consistente al terminar. Isolated: Una transacción no afecta a las demás. Durable: Todos los cambios efectuados por una transacción se graban. No se pierden datos.

46

Irontec – istración de MySQL

InnoDB ●

Cuando se producen múltiples transacciones que afectan a los mismos datos, pueden darse los siguientes problemas: –

–

–

Dirty read: es una lectura de una transacción de datos no commiteados realizados por otra. ¡Si finalmente esta última no hace el commit, lo que hemos leido no sirve! Non-repeatable: ocurre cuando una transacción realiza la misma petición de datos dos veces recibiendo resultados distintos Phantom: ocurre cuando aparece una fila que no era visible en la query, ya que otra transacción la ha incluido durante el proceso de lectura

47

Irontec – istración de MySQL

InnoDB ●

InnoDB dispone de 4 niveles de aislamiento: –

–

–

–

READ UNCOMMITED: permite a una transacción ver los datos sin commitear de otra. Se pueden dar nonrepeatable reads, phantoms y dirty reads READ COMMITED: permite ver cambios de otras transacciones solo si están commiteados. Se pueden dar non-repeatable reads y phantoms REPETABLE READ: asegura que la misma SELECT ejecutada dos veces de los mismos valores. Las filas modificadas por una transacción no puden ser modificadas por otras. Se pueden dar phantoms SERIALIZE: separa completamente los efectos de unas transacciones sobre otras con la restricción de que las filas seleccionadas por una transacción no pueden ser accedidas por otras 48

Irontec – istración de MySQL

InnoDB ●

●

Por defecto InnoDB utiliza REPEATABLE READ para ser full ACID Para cambiar de uno a otro aislamiento de:

[mysqld] Transaction­isolation = READ­COMMITED

49

Irontec – istración de MySQL

InnoDB ●

●

●

●

●

InnoDB implementa la concurrencia en las transacciones siguiendo el modelo MVCC (multiversion concurrency control) Permite que cada transacción tenga una visión única de los datos (un snapshot) para tener una vista consistente con el paso del tiempo, sin tener en cuenta las modificaciones de otras transacciones Esto puede permitir que distintas transacciones vean datos diferentes de la misma tabla en un mismo momento Gracias a esto evitamos tener que bloquear siempre las filas. Menos overhead y más operaciones concurrentes En lecturas casi nunca se bloquearán las filas 50

Irontec – istración de MySQL

InnoDB ●

●

MVCC funciona añadiendo a cada fila dos valores adicionales que indican cuando fué creada y cuando fué eliminada Con esto InnoDB se asegura que al iniciar una transacción... – –

●

●

La fila existía antes de iniciar la transacción La fila no fué eliminada antes de iniciar la transacción

Con cada actualización de la fila, la transacción actualiza los valores asociados El multiversioning se utiliza con: – –

REPETABLE READ READ COMMITED 51

Irontec – istración de MySQL

InnoDB ●

●

●

Por defecto InnoDB viene habilitado con autocommit, cada query se commitea al instante Esto no nos permite hacer rollback ni agrupar querys en batch para mejorar el rendimiento ¿Y como desactivo el autocommit? – –

●

SET AUTOCOMIT = 0; START TRANSACTION; COMMIT;

Para habilitar el rollback podemos añadir un SAVEPOINT – –

SAVEPOINT nombre; ROLLBACK TO SAVEPOINT nombre;

52

Irontec – istración de MySQL

Mantenimiento de tablas

53

Irontec – istración de MySQL

Mantenimiento de tablas ●

●

●

Existen una serie de comandos que nos ayudar al mantenimiento de las tablas Algunos son tienen utilidad en unos engines pero no en otros Podemos: – – – –

Chequear Reparar Analizar Optimizar

54

Irontec – istración de MySQL

Mantenimiento de tablas ●

CHECK TABLE –

– – ●

Realizar un chequeo de integridad tanto de la estructura como del contenido de los datos Funciona tanto en MyISAM como InnoDB En MyISAM además actualiza las estadísticas de los índices

Su uso es el siguiente:

mysql > check table A; +­­­­­­­­­­­+­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ | Table     | Op    | Msg_type | Msg_text | +­­­­­­­­­­­+­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ | pruebas.A | check | status   | OK       | +­­­­­­­­­­­+­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ 1 row in set (0.00 sec)

55

Irontec – istración de MySQL

Mantenimiento de tablas ●

REPAIR TABLE – – –

Repara los errores que se puedan encontrar en una tabla Solo funciona con MyISAM Se puede habilitar el auto-reparado de tablas

[mysqld] myisam­recover=[Opciones] ­ Default: opciones por defecto ­ Backup: hace backup de las tablas que tenga que  cambiar ­ Force: fuerza la reparación aunque se puedan  perder datos ­ Quick: las tablas no fragmentadas se evitan

56

Irontec – istración de MySQL

Mantenimiento de tablas ●

Reparación en caliente

mysql > repair table A; +­­­­­­­­­­­+­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ | Table     | Op    | Msg_type | Msg_text | +­­­­­­­­­­­+­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ | pruebas.A | repair| status   | OK       | +­­­­­­­­­­­+­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ 1 row in set (0.00 sec)

57

Irontec – istración de MySQL

Mantenimiento de tablas ●

ANALYZE TABLE –

–

–

Actualiza las tablas con información acerca de la distribución de los valores de las claves en la tabla Esta información la usa el optimizador para elegir el mejor plan de ejecución Solo funciona en MyISAM

mysql > analyze table A; +­­­­­­­­­­­+­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ | Table     | Op    | Msg_type | Msg_text | +­­­­­­­­­­­+­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ | pruebas.A |analyze| status   | OK       | +­­­­­­­­­­­+­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ 1 row in set (0.00 sec)

58

Irontec – istración de MySQL

Mantenimiento de tablas ●

OPTIMIZE TABLE –

– –

Desfragmenta las tablas MyISAM, eliminando los huecos generados por múltiples updates y deletes Actualiza las estadísticas de los índices Se puede utilizar en InnoDB, pero no desfragmenta (ya que InnoDB no se fragmenta) Actualiza las estadísticas de índices y libera espacio relacionado con índices

mysql > optimize table A; +­­­­­­­­­­­+­­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ | Table     | Op     | Msg_type | Msg_text | +­­­­­­­­­­­+­­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ | pruebas.A |optimize| status   | OK       | +­­­­­­­­­­­+­­­­­­­­+­­­­­­­­­­+­­­­­­­­­­+ 1 row in set (0.00 sec) 59

Irontec – istración de MySQL

Mantenimiento de tablas ●

Existen herramientas externas de consola que también nos pueden ayudar con el mantenimiento – –

●

●

mysqlcheck myisamchk

Mysqlcheck envía las sentencias SQL necesarias para el mantenimiento. Por lo tanto, mysqld debe estár en funcionamiento Myisamchk no necesita de un servidor en funcionamiento, puede actuar directamente contra las tablas en el sistema de ficheros

60

Irontec – istración de MySQL

Mantenimiento de tablas ●

Mysqlcheck nos ofrece alguna ventaja al uso de las sentencias SQL directamente –

–

●

Podemos indicarle una BBDD para que haga operaciones de mantenimiento en todas sus tablas en lugar de indicar una a una Al ser un comando de consola, se puede crear una tarea programada

Ejemplo: – – – –

Mysqlcheck test (chequea BBDD test) Mysqlcheck test A (chequea tabla A de la BBDD test) Mysqlcheck –all-databases (chequea todo) --check, --repair, --analyze, --optimize 61

Irontec – istración de MySQL

Mantenimiento de tablas ●

● ● ●

●

Myisamchk permite realizar las mismas tareas que mysqlcheck Se debe hacer offline, con mysqld apagado Actua directamente sobre las tablas Es el último recurso cuando ni siquiera el servidor arranca (por ejemplo, tablas en la BBDD mysql corruptas) Como el propio nombre indica, solo funciona con tablas MyISAM

62

Irontec – istración de MySQL

s y permisos

63

Irontec – istración de MySQL

s y permisos ●

●

MySQL permite definir s y decidir que pueden hacer Los s siguen el siguiente formato: –

●

●

''@'host'

No es lo mismo irontec@localhost que [email protected] La primera parte es el nombre de con el que haremos , mientras que la segunda será la IP desde la cual nos conectamos

64

Irontec – istración de MySQL

s y permisos ●

Se puede crear un que se conecta desde cualquier ubicación: –

●

●

●

'irontec'@'%'

Los s y permisos se guardan dentro de las tablas y grant de la BBDD mysql Es posible insertar datos directamente mediante la consola de mysql, pero también es más lioso ¡No debe existir nunca un 'root'@'%', por seguridad!

65

Irontec – istración de MySQL

s y permisos ●

Para ver todos los permisos disponibles y una descripción: mysql > show privileges;

●

Nos muestra tres columnas: – – –

Privilege: es el nombre del permiso Context: contexto en el cual es posible aplicar el permiso Comment: breve explicación

66

Irontec – istración de MySQL

s y permisos ●

Permisos especiales: –

–

ALL y ALL PRIVILEGES da todos los permisos excepto GRANT OPTION USAGE no da ningún privilegio excepto el de conexión

67

Irontec – istración de MySQL

s y permisos ●

Los permisos pueden aplicarse en diferentes contextos: – – – – – – –

●

●

Server Databases Tables Functions Procedures Indexes File Access

Por ejemplo, no puedes dar permisos FILE a un sobre una tabla, es un permiso global o CREATE ROUTINE a nivel de índice Todos pueden aplicarse globalmente 68

Irontec – istración de MySQL

s y permisos ●

Para crear un se debe indicar tanto el como el host y su contraseña (no obligatorio):

mysql > create  'irontec'@'localhost'  IDENTIFIED BY '1234'; ●

Borrar un :

mysql > drop  'irontec'@'localhost'; ●

Renombrar un :

mysql > rename  'irontec'@'localhost' TO  'miguelangel'@'localhost'; 69

Irontec – istración de MySQL

s y permisos ●

Para cambiar una contraseña hay distintas formas: –

Para un en particular:

mysql > SET  for 'miguel'@'localhost' =  ('nueva'); –

Para tu :

mysql > SET  = ('nueva');

–

Mediante GRANT:

mysql > GRANT USAGE ON *.* TO  'miguel'@'localhost' IDENTIFIED BY 'nueva'; 70

Irontec – istración de MySQL

s y permisos ● ●

Para dar permisos se usa GRANT Importante: si el al que damos permisos no existe, lo crea

mysql > GRANT SELECT ON mysql.* TO  'miguel'@'localhost' IDENTIFIED BY 'test'; ●

●

Se pueden indicar múltiples permisos separándolos por comas Para aplicar a distintos contextos: – – – –

ON *.* ON db.* ON db.table ON db.routine 71

Irontec – istración de MySQL

s y permisos ●

Para eliminar un permiso haremos uso de REVOKE:

mysql > REVOKE SELECT ON mysql.* FROM  'miguel'@'localhost'; ●

El seguirá existiendo, pero sin el permiso indicado

mysql > show grants for 'miguel'@'localhost';

●

Si le borramos el último permiso, USAGE, no se podrá conectar

72

Irontec – istración de MySQL

Optimización de querys

73

Irontec – istración de MySQL

Optimización de querys ●

●

●

●

Hay que tener especial cuidado con las querys que se lanzan a una base de datos Si está mal construida puede degradar el rendimiento del servicio Es necesario detectar estas querys y reescribirlas completamente EXPLAIN nos ayudará a saber como se comportar internamente una query y cuales son sus deficiencias

74

Irontec – istración de MySQL

Optimización de querys ●

● ●

●

En primer lugar debemos detectar cuales son esas querys Para ello nos ayudaremos del log-slow En este log se irán escribiendo las sentencias SQL que tarden un tiempo superior al que le indiquemos Su activación no afecta al rendimiento como otros logs – – –

●

log­slow­queries=/var/log/mysql/slow­querys.log long_query_time=5 Log­queries­not­using­indexes

Log de querys lentas (superior a 5 segundos) así como querys sin índices :)

75

Irontec – istración de MySQL

Optimización de querys ●

●

El log puede crecer con el tiempo y leerlo con un editor de textos convertirse en un infierno No es necesario hacerte tu propio script, ya existen utilidades para dumpearlo /var/log/mysql# mysqldumpslow mysql­slow.log

●

Nos resumirá las querys lentas

76

Irontec – istración de MySQL

Optimización de querys ●

¿Cómo se ve en el log?

/var/log/mysql# cat mysql­slow.log # Query_time: 4.00s  Lock_time: 0  Rows_sent: 0   Rows_examined: 1380 SELECT * FROM foobar WHERE id="foo"; # Query_time: 2.50s  Lock_time: 0  Rows_sent: 0   Rows_examined: 1380 SELECT * FROM foobar WHERE id="bar";

77

Irontec – istración de MySQL

Optimización de querys ●

Ejemplo de mysqldumpslow:

/var/log/mysql# mysqldumpslow mysql­slow.log Count: 2  Time=4s (6.50s)  Lock=0.00s (0s)   Rows=1380 (2760) SELECT * FROM foobar WHERE id='S';

78

Irontec – istración de MySQL

Optimización de querys ●

Ya tenemos identificadas las querys lentas, ahora es necesario estudiarlas –

●

●

Mysql > explain [extended] SELECT [opciones]

Basicamente nos mostrará información del plan de ejecución del optimizador Sólo funciona con SELECT

79

Irontec – istración de MySQL

Optimización de querys ●

Problema:

explain SELECT * from City where  CountryCode='ESP'\G *************************** 1. row  ***************************            id: 1   select_type: SIMPLE         table: City          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 4079         Extra: Using where 1 row in set (0.00 sec) 80

Irontec – istración de MySQL

Optimización de querys ●

●

● ●

● ● ● ● ●

ID: identificador de la query, en caso de tener subquerys select_type: el tipo de select. Simple, primary, derived, union, subquery table: indica el nombre de la tabla utilizada type: tipo de para la query. system/const, eq_ref, ref, ref_or_null, index_merge, range, index, ALL possible_keys: lista de índices disponibles para usar key: índice utilizado key_len: número de bytes utilizados del índice rows: número de filas a leer extra: información extra de la query

81

Irontec – istración de MySQL

Optimización de querys ●

Type: – –

– – –

–

– –

–

system: la tabla tiene una única fila const: la tabla tiene una única fila coincidente con la query eq_ref: la busqueda por índices da una única fila ref: similar a eq_ref pero puede devolver más de una fila ref_or_null: igual que los anteriores, pero también busca valores NULL index_merge: el único tipo de que usa dos índices range: busqueda por rango index: busqueda completa por la tabla, pero de índices en lugar de los datos ALL: escaneo completo de la tabla. MAL

82

Irontec – istración de MySQL

Optimización de querys ●

Extra: –

–

–

–

Using index: se está usando un index, cogiendo el dato del índice en lugar de la tabla Using filesort: se ordenan las filas de forma manual en lugar de usar índices Using temporary: se ha usado una tabla temporal (en RAM o disco) para realizar la consulta Using where: el filtrado se hace fuera del engine

83

Irontec – istración de MySQL

Optimización de querys ●

Volvemos al problema :)

explain SELECT * from City where  CountryCode='ESP'\G *************************** 1. row  ***************************            id: 1   select_type: SIMPLE         table: City          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 4079         Extra: Using where 1 row in set (0.00 sec) 84

Irontec – istración de MySQL

Optimización de querys ● ● ●

●

El optimizador no encuentra índices Al no encontrar... no puede usar Como no hay índices, el tipo de búsqueda es ALL, se lee todas las filas ¿Como podríamos mejorarlo? – –

●

Añadiendo índices Optimizar la query añadiendo más detalles

Después de cada cambio hay que comprobar si existe mejora

85

Irontec – istración de MySQL

Optimización de querys ●

Añadiendo un índice: –

●

Mysql>alter table City add index(CountryCode); Query OK, 4079 rows affected (0.02 sec) Records: 4079  Duplicates: 0  Warnings: 0

Ejecutamos de nuevo la query con explain:            id: 1   select_type: SIMPLE         table: City          type: ref possible_keys: CountryCode           key: CountryCode       key_len: 3           ref: const          rows: 58         Extra: Using where 1 row in set (0.00 sec) 86

Irontec – istración de MySQL

Optimización de querys ●

Modificamos la query añadiendo más datos, haciéndola más concisa: explain SELECT * from City where  CountryCode='ESP' and District='Madrid'; – alter table City add index(District);            id: 1   select_type: SIMPLE         table: City          type: index_merge possible_keys: CountryCode,District           key: District,CountryCode       key_len: 20,3           ref: NULL          rows: 1         Extra: Using intersect(District,CountryCode);  Using where 1 row in set (0.00 sec) –

87

Irontec – istración de MySQL

Optimización de la base de datos

88

Irontec – istración de MySQL

Optimización de la base de datos ●

Cuando nos referimos a optimizar la base de datos, esto implica en primer lugar optimizar el servidor –

– –

●

RAM: contra más RAM, más índices y datos en memoria, por lo tanto más velocidad de respuesta U: nunca sobra la U y los cores ;) Disco duro: contra más rápido mejor. Últimamente se estan empezando a usar disco SSD para bases de datos

Una vez que tenemos el mejor equipo que podamos comprar, tocará optimizar la base de datos de acuerdo a dicho hardware

89

Irontec – istración de MySQL

Optimización de la base de datos ● ● ●

●

● ●

¿32 o 64 bits? Siempre que sea posible es recomendable 64 bits En sistemas GNU/Linux y Solaris de 32 bits, un solo proceso no puede usar más de 2GB. Por lo tanto da igual que tengamos 4, MySQL usará la mitad En algunos sistemas, según un parámetro de configuración del kernel, puede disponer de 3 GB, pero puede ser insuficiente En 64 bits no existe esa limitación Si tienes 32 bits, no configures MySQL para que use mas de 2GB, tendrás errores de falta de memoria memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections 90

Irontec – istración de MySQL

Optimización de la base de datos ●

●

Las optimizaciones también dependerán mucho del tipo de querys que ejecutemos Para tener una estadística mysql> show status like 'Com%';

91

Irontec – istración de MySQL

Optimización de la base de datos ●

Número máximo de conexiones (max_connections) –

– –

Son el número máximo de conexiones simultaneas que soportará el servidor Cada conexión en espera consume memoria Se debe ajustar a un número no muy superior a la media de nuestro servidor

mysql> show status like '%connections%'; +­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­+ | Variable_name        | Value   | +­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­+ | Connections          | 4651724 |  | Max_used_connections | 41      |  +­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­+ 2 rows in set (0.01 sec) 92

Irontec – istración de MySQL

Optimización de la base de datos ●

Cache de tablas (table_cache) –

–

–

●

Cada vez que mysql abre una tabla mantiene en cache información sobre ella para evitar reabrirlas Si se abren muchas y un necesita abrir otra nueva, una de las ya abiertas debe cerrarse No vale poner millones y millones, estamos limitados por los descriptores del sistema operativo

Si Open_tables está cercano a Table_cache y Opened_tables no para de aumentar, toca subir el table_cache mysql> show status like 'Open%tables%'; +­­­­­­­­­­­­­­­+­­­­­­­+ | Variable_name | Value | +­­­­­­­­­­­­­­­+­­­­­­­+ | Open_tables   | 1551  |  | Opened_tables | 0     |  +­­­­­­­­­­­­­­­+­­­­­­­+ 2 rows in set (0.02 sec)

93

Irontec – istración de MySQL

Optimización de la base de datos ●

Cacheo de índices en MyISAM (key_buffer_size) – –

–

–

Cachea los índices que lee de una tabla MyISAM Permite reutilizar los índices en lugar de leerlos continuamente, más rendimiento Para calcularlo: Ratio de fallo: Key_reads / Key_read_requests Eficiencia: 1 – (Key_reads / Key_read_requests) Los fallos tienen que estar lo más cerca posible de 0 y eficiencia de 1 mysql> show status like 'Key_read%'; +­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­+ | Variable_name     | Value     | +­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­+ | Key_read_requests | 150683856 |  | Key_reads         | 325197    |  +­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­+ 2 rows in set (0.01 sec)

94

Irontec – istración de MySQL

Optimización de la base de datos ●

Buffer de InnoDB (innodb_buffer_pool_size) –

–

Cache tanto los índices como los datos de una tabla InnoDB El valor por defecto es 8 megas, pero se recomienda entre el 50% y 80% de la memoria total del sistema

mysql> show variables like  'innodb_buffer_pool_size'; +­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­+ | Variable_name           | Value     | +­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­+ | innodb_buffer_pool_size | 838860800 |  +­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­+ 1 row in set (0.00 sec)

95

Irontec – istración de MySQL

Optimización de la base de datos ●

Cacheo de querys (query_cache_size) –

–

●

● ●

Nos permite almacenar en cache el resultado de una query select La cache solo se mantiene mientras los datos de la tabla no se modifiquen

query_cache_type: OFF desactivado, ON activado, DEMAND solo para SELECT SQL_CACHE query_cache_size: 0 es desactivado query_cache_limit: el tamaño máximo de una query que se puede almacenar

96

Irontec – istración de MySQL

Optimización de la base de datos ●

Midiendo la efectividad de la cache: –

–

–

●

Qcache_hits: numero de querys que no se han ejecutado por estar en cache Qcache_inserts: el número total de querys guardadas en la cache Qcache_lowmem_prunes: número de caches eliminadas por falta de memoria

Si hits es bajo e inserts alto... hay que aumentar la caché

97

Irontec – istración de MySQL

Optimización de la base de datos ●

Otros valores: –

–

–

–

read_buffer_size: cuando se lee una tabla lo leerá en bloques del tamaño que aquí indiquemos. Contra mas grande, más rápido la leerá read_rnd_buffer_size: por defecto coge el valor del anterior. Se usa para lecturas no secuenciales (como las requeridas por ORDER BY) sort_buffer_size: para las operaciones con ORDER o GROUP BY _buffer_size: para querys con s

98

Irontec – istración de MySQL

Optimización de tablas

99

Irontec – istración de MySQL

Optimización de tablas ●

Si las tablas aumentan mucho puede llegar un momento en el que los índices ocupen más que la propia RAM del sistema

ÍNDICES

RAM 100

Irontec – istración de MySQL

Optimización de tablas ●

¡Particionemos!

101

Irontec – istración de MySQL

Optimización de tablas ●

● ●

Si no queremos comprar más RAM, los índices se vuelven lentos Particionar se vuelve más rápido que los índices Será nuestra salvación cuando: – – – – –

Nos quedemos sin RAM Tenemos muchísimos datos Guardamos datos históricos Rotamos datos Datos crecientes

Es transparente, no tendremos que modificar las aplicaciones 102

Irontec – istración de MySQL

Optimización de tablas ●

Cosas a tener en cuenta antes de particionar: –

–

– – –

La columna que utilicemos para definir el rango de las particiones debe ser un INT, no se acepta cualquier otro valor Si tenemos una clave única o una primary key, esta debe usarse para particionar Como máximo se permiten 1024 particiones No se permiten claves externas No se permiten búsquedas FULL TEXT

103

Irontec – istración de MySQL

Optimización de tablas ●

El particionado se puede hacer: – –

–

–

Por rango: es el más sencillo, se divide en base a rangos Por listas: se divide en particiones en base a una lista de posibles valores Por hash: nos permite dividir los datos de forma equitativa entre todas las particiones. La expresión por la que separar los datos debe devolver un entero Por key: similar a hash. En lugar de indicar nosotros el valor del hash, lo hace el propio MySQL con md5 y (). Pueden usarse columnas que no sean entero

104

Irontec – istración de MySQL

Optimización de tablas CREATE TABLE employees (     id INT NOT NULL,     fname VARCHAR(30),     lname VARCHAR(30),     hired DATE NOT NULL DEFAULT '1970­01­01',     separated DATE NOT NULL DEFAULT '9999­12­ 31',     job_code INT NOT NULL,     store_id INT NOT NULL ) PARTITION BY RANGE (store_id) (     PARTITION p0 VALUES LESS THAN (6),     PARTITION p1 VALUES LESS THAN (11),     PARTITION p2 VALUES LESS THAN (16),     PARTITION p3 VALUES LESS THAN (21),     PARTITION p3 VALUES LESS THAN MAXVALUE ); 105

Irontec – istración de MySQL

Optimización de tablas CREATE TABLE employees (     id INT NOT NULL,     fname VARCHAR(30),     lname VARCHAR(30),     hired DATE NOT NULL DEFAULT '1970­01­01',     separated DATE NOT NULL DEFAULT '9999­12­ 31',     job_code INT,     store_id INT ) PARTITION BY LIST(store_id) (     PARTITION pNorth VALUES IN (3,5,6,9,17),     PARTITION pEast VALUES IN  (1,2,10,11,19,20),     PARTITION pWest VALUES IN  (4,12,13,14,18),     PARTITION pCentral VALUES IN (7,8,15,16) ); 106

Irontec – istración de MySQL

Optimización de tablas CREATE TABLE employees (     id INT NOT NULL,     fname VARCHAR(30),     lname VARCHAR(30),     hired DATE NOT NULL DEFAULT '1970­01­ 01',     separated DATE NOT NULL DEFAULT  '9999­12­31',     job_code INT,     store_id INT ) PARTITION BY HASH( YEAR(hired) ) PARTITIONS 4;

107

Irontec – istración de MySQL

Optimización de tablas

CREATE TABLE Employee (     emp_id INT,     fname VARCHAR(50),     lname VARCHAR(50),     store_id TINYINT   ) ENGINE=MyISAM   PARTITION BY KEY (lname)   PARTITIONS 4;

108

Irontec – istración de MySQL

Optimización de tablas ●

Existe un script que nos permite crearnos la query de creación de particiones http://forge.mysql.com/wiki/Partition_Helper ./partitions_helper ­­table=mytable ­­column=d  ­interval=year ­­start=2004­01­01 –end=2009­01­01 ./partitions_helper ­­table=mytable ­­column=d  ­­interval=month ­­start=2008­01­01 ­­end=2009­01­01 ./partitions_helper ­­table=mytable –column=prod_id  ­­interval=1000 ­­start=1 –end=10000

109

Irontec – istración de MySQL

Optimización de tablas ●

Para comprobar si las particiones funcionan y en que partición se encuentran nuestros datos: mysql [localhost] {msandbox} (employees) > explain  partitions select count(*) from salaries where from_date between '2000­01­01' and '2000­12­31'\G *************************** 1. row  ***************************            id: 1   select_type: SIMPLE         table: salaries    partitions: p016          type: index possible_keys: NULL           key: emp_no       key_len: 4           ref: NULL          rows: 2830488         Extra: Using where; Using index 1 row in set (0.00 sec)

110

Irontec – istración de MySQL

Optimización de tablas ●

Mantenimiento de tablas RANGE y LIST –

Se pueden añadir más particiones

ALTER TABLE gente ADD PARTITION (PARTITION p4 VALUES  LESS THAN (2010)); –

Se pueden eliminar particiones (borra datos) ALTER TABLE gente DROP PARTITION p4

–

Se reorganizar particiones

ALTER TABLE gente REORGANIZE PARTITION p0 INTO (     PARTITION s0 VALUES LESS THAN (1960),     PARTITION s1 VALUES LESS THAN (1970) );

111

Irontec – istración de MySQL

Optimización de tablas ●

Mantenimiento de tablas HASH y KEY –

Se pueden añadir más particiones ALTER TABLE gente ADD PARTITION PARTITIONS 2;

–

Se pueden reducir el número de particiones, DROP no funciona ALTER TABLE gente COALESCALE PARTITION 2;

●

Las particiones HASH o KEY se auto reorganizan según añadamos o eliminemos particiones

112

Irontec – istración de MySQL

Optimización de tablas ●

Reconstruir partición: –

●

Optimizar partición: –

●

Lee y almacena la distribución de índices

Reparar partición: –

●

Otra forma de solucionar la fragmentación. Reclama el espacio libre y reorganiza los datos

Analizar partición: –

●

Borrar los datos de la partición para volver a insertarlos. De esta forma solucionas el problema de fragmentado

Repara particiones corruptas

Chequear partición: –

Chequea una partición en busca de errores 113

Irontec – istración de MySQL

Optimización de tablas

ALTER TABLE gente REBUILD PARTITION p1; ALTER TABLE gente OPTIMIZE PARTITION p1; ALTER TABLE gente ANALYZE PARTITION p1; ALTER TABLE gente REPAIR PARTITION p1; ALTER TABLE gente CHECK PARTITION p1;

114

Irontec – istración de MySQL

Logs

115

Irontec – istración de MySQL

Logs ●

En MySQL tenemos 3 tipos de logs – – –

●

Slow: guardan las querys lentas Sql: guarda todas las querys que se ha recibido el servidor Binlog: guarda únicamente las querys que modifican datos

¿Para que nos pueden servir? – – –

Arreglar querys lentas o sin índices Debuggear a nivel SQL nuestra aplicación Replicación o recuperación de backup

116

Irontec – istración de MySQL

Logs ●

Slow Logs: –

Goto 75

117

Irontec – istración de MySQL

Logs ●

●

●

●

SQL log almacena todas las querys que llegan a nuestro servidor Se puede usar para debuggear nuestra aplicación antes de pasarla a producción En producción no es recomendable tenerlo habilitado, consume muchos recusos tanto de U como de disco duro Si este log está activado, el logrotate debe estar habilitado log=/var/log/mysql/mysql.log 118

Irontec – istración de MySQL

Logs ●

●

Los logs binarios almacenan los cambios que se realizan en la BBDD (update, insert, delete, alter, etc.) Nos sirven para: – –

●

●

Recuperación de backup Replicación

Es recomendable tenerlos habilitados, no consumen mucho y nos pueden salvar la vida En caso de replicación es obligatorio tenerlos habilitados

119

Irontec – istración de MySQL

Logs ●

Otras opciones: – – – – – – – – – – –

max_binlog_size (por defecto y máximo 1 GB) expire_logs_days sync_binlog (>0) replicate-do-db replicate-ignore-db binlog-do-db binlog-ignore-db replicate-do-table replicate-wild-do-table replicate-ignore-table replicate-wild-ignore-table 120

Irontec – istración de MySQL

Logs

121

Irontec – istración de MySQL

Logs ●

El diagrama para el logeo de tablas es demasiado grande y no entra ;) http://dev.mysql.com/doc/refman/5.0/en/replication-rules-table-options.html

●

Para rellenar la diapositiva pondré un dibujo:

No se quien es el autor :( 122

Irontec – istración de MySQL

Replicación y alta disponibilidad

123

Irontec – istración de MySQL

Arquitecturas de replicación ●

Tenemos varias formas de montar una arquitectura de replicación. – – –

●

Maestro-Maestro Maestro-Esclavo Circular

Según lo que se necesite, se monta una u otra.

124

Irontec – istración de MySQL

Limitaciones

●

●

●

Un esclavo solo puede tener un maestro. Por el contrario, un maestro múltiples esclavos. No es recomendable montar una replicación por WAN. La replicación es asíncrona y sensible a latencias. En un servidor esclavo esta prohibido escribir datos, solo se usarán selects. 125

Irontec – istración de MySQL

Maestro-Esclavo ● ●

Un maestro, múltiples esclavos. En el maestro se escribe, en el esclavo se lee.

126

Irontec – istración de MySQL

Maestro-Esclavo ●

Primero debemos configurar el maestro. Imprescindible: – – – –

Habilitar los logs binarios. Crear un que permita conectarse a los esclavos. Habilitar sync_binlog. Elegir un server-id.

log­bin=mysql­bin server­id=1 sync_binlog=1

127

Irontec – istración de MySQL

Maestro-Esclavo ●

Dar permisos de conexión a los eslavos y dumpeamos la BD: mysqldump BD ­­master­data=2 > dump_file;

mysql> grant replication slave on *.* to  'replication'@10.10.10.1 identified by 'slave'; mysql> grant replication slave on *.* to  'replication'@10.10.10.2 identified by 'slave';

128

Irontec – istración de MySQL

Maestro-Esclavo ●

Configuramos el eslavo: – – –

Seleccionamos un ID diferente para cada uno. Importamos la BD. Nos configuramos como esclavo de un maestro.

$mysql ­u root ­p < dump server­id=101 mysql> CHANGE MASTER TO MASTER_HOST = ‘10.10.10.100’,  MASTER_ = ‘replication’, MASTER_ = ‘slave’,  MASTER_LOG_FILE = ‘master_log_file’, MASTER_LOG_POS =  master_log_pos;

129

Irontec – istración de MySQL

Maestro-Esclavo ●

●

●

Master_log_pos y Master_log_file indican al esclavo desde que posición del log binario deben leer, de forma que no se repliquen datos que ya tenemos. Podemos sacarlo con un dump como ya hemos visto o con el comando show master status; El log binario debe estár habilitado :)

130

Irontec – istración de MySQL

Maestro-Esclavo ●

●

●

●

●

●

No se debe dejar al servidor la elección de cuando escribir los datos al disco duro. Si el servidor se cae sin que algunos datos se escriban en el log, es posible que estos se pierdan (dependerá del sistema de ficheros). sync_binlog por defecto es 0, que deja que el servidor decida cuando realizar la escritura al disco. Se recomienda un valor de 1, para que se fuerce la escritura. Esto también es lento, dependerá de los discos duros instalados. Si el servidor se cae, como mucho perderemos una transacción. 131

Irontec – istración de MySQL

Maestro-Esclavo ●

●

Para comprobar si la replicación es correcta tenemos el comando show slave status. Este nos tiene que mostrar lo siguiente:

[...] Slave_IO_Running: Yes Slave_SQL_Running: Yes [...] Seconds_Behind_Master: 0

Slave_IO_Running: Se encarga de conectarse al maestro para comprobar cambios Slave_SQL_Running: Se encarga de escribir las sentencias SQL. Seconds_Behind_Master: El lag en segundos entre el maestro y el esclavo. 132

Irontec – istración de MySQL

Maestro-Maestro ● ●

Lo que se escribe en uno se replica en el otro. Se puede escribir en los dos.

133

Irontec – istración de MySQL

Maestro-Maestro ●

●

●

La arquitectura maestro-maestro es igual a la maestro esclavo. Aquí los hosts realizan las dos tareas, maestro y esclavo al mismo tiempo. Esta arquitectura soporta como máximo dos hosts, ya que un esclavo solo puede tener como máximo un maestro. A es maestro de B. B es maestro de A. A es esclavo de B. B es esclavo de A.

134

Irontec – istración de MySQL

Maestro-Maestro ●

Se debe tener en cuenta, al igual que antes, lo siguiente: – – – –

●

Habilitar el log binario. Seleccionar un server-id. Establecer el valor de sync_binlog. Crear los s para la replicación.

El funcionamiento, opciones, monitorización, etc. es todo igual.

135

Irontec – istración de MySQL

Maestro-Maestro ●

●

Los auto-incrementales son el gran problema de este tipo de arquitectura. Si se realizan dos insert al mismo tiempo que incluya un campo autoincremental, podemos tener un problema de ID duplicado. A envía a B un artículo cuyo ID autoincremental es 3000, B envía un artículo diferente a A cuyo autoincremental es 3000 también. La replicación se rompe. – –

●

auto_increment_increment = 2 auto_increment_offset = 1

¿Cómo sería para el server B?

136

Irontec – istración de MySQL

Circular ●

●

Lo que se escribe en uno se replica en el siguiente, este en el siguiente, este en... A → B → C → D → A Es la menos recomendable. En realidad está casi prohibida también ;)

137

Irontec – istración de MySQL

Circular ●

●

●

●

●

Es una forma de disponer de más de dos servidores en arquitectura maestro-maestro. Contra más sean los hosts implicados, mayor el caos de su istración. A→B→C→D→E→A Si el host C se cae (por ejemplo) la replicación se rompe. Lo escrito en B no se replica, lo escrito en D se replica en todos menos en C, etc. Si se cae por ejemplo B y D, el caos es infinito. La solución es salir corriendo. A no ser que no exista otra solución, no se recomienda. 138

Irontec – istración de MySQL

Circular ●

Los logs que reciben los esclavos, deben logearlos en el log binario para enviarselo al siguiente en la cadena. Esto no es el funcionamiento por defecto, los que se recibe como esclavo no se logea. Para cambiarlo: –

●

En algún momento de la cadena nos llegarán nuestros propios logs. Para evitar formar un bucle: –

●

log­slave­updates

Replicate­same­server­id=0

También habrá que tener cuidado con los autoincrementales: – –

auto_increment_increment=4 auto_increment_offset=1

139

Irontec – istración de MySQL

Replicación rota

140

Irontec – istración de MySQL

Replicación rota ●

●

Es recomendable tener el error-log habilitado, ahí se guardará, entre otras cosas, cualquier error relacionado con la replicación. ¡EJEMPLO!

Sep 11 11:13:16 test2 mysqld[6776]: 090911 11:13:16  [ERROR] Slave: Error 'Table 't' already exists' on  query. Default database: 'mysql'. Query: 'CREATE  TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE  utf8_bin)', Error_code: 1050 Sep 11 11:13:16 test2 mysqld[6776]: 090911 11:13:16  [ERROR] Error running query, slave SQL thread  aborted. Fix the problem, and restart the slave SQL  thread with "SLAVE START". We stopped at log 'mysql­ bin.000003' position 421 141

Irontec – istración de MySQL

Replicación rota ● ●

Forma rápida y no tan buena de solucionarlo: Decirle al esclavo que ignore esa query y continue con la replicación: mysql> stop slave; Query OK, 0 rows affected (0.00  sec)  mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; Query  OK, 0 rows affected (0.00 sec)  mysql> start slave;

142

Irontec – istración de MySQL

Replicación rota ●

● ● ●

●

Forma buena de solucionarlo: http://www.maatkit.org/ Maatkit makes MySQL easier to manage. Lo de “easier” ponedlo entre muchas comillas. Son una colección de herramientas que nos puede ayudar en la istración de nuestro servidores, y en este caso en particular, en rescatar una replicación. Todas las utilidades: http://www.maatkit.org/doc/

143

Irontec – istración de MySQL

Replicación rota ●

Para saber si dos tables están sincronizadas, podemos sacar un checksum de estas y comparar: –

●

mk­table­checksum

Para sincronizar los datos de dos tablas: –

mk­table­sync

$ mk­table­checksum h=host1,u=,p= h=host2 $ mk­table­sync –execute  u=,p=,h=host1,D=db,t=tbl host2

144

Irontec – istración de MySQL

MMM ●

●

● ● ●

Cuando ya sabemos que queremos y lo montamos, llega el mantenimiento. Si tenemos un cluster de, por ejemplo, 2 maestros y 50 esclavos, comprobar el correcto funcionamiento es complicado. ¿Y si deseamos parar algún esclavo? ¿Y si deseamos parar algún maestro? Es necesario reducir el tiempo de parada de servicio al mínimo.

145

Irontec – istración de MySQL

MMM

146

Irontec – istración de MySQL

MMM ● ●

También el Perl. El mundo se ha vuelto loco... Características: – – – – –

Monitorización de la replicación Monitorización de los hosts Gestión del failover Balanceo de IPs entre nodos Gestión de grupos de escritura/lectura

147

Irontec – istración de MySQL

MMM ●

La alta disponibilidad se hace mediante el balanceo de Ips virtuales que saltarán de un servidor a otro en caso de ser necesario. –

–

Exclusivo: Una única IP para muchos hosts. Si el host que la tiene se cae se balancea a otro. Generalmente se usa en los nodos de escritura. Balanceado: Una IP por cada host. Si uno de los hosts se cae la IP se balancea a cualquier otro, pasando a tener dos IPs virtuales. Se usa para nodos en lectura.

148

Irontec – istración de MySQL

MMM ●

●

Se pueden meter los servidores dentro de dos roles, escritura y lectura. Escritura es obligatorio, mientras que el de lectura no tiene porque definirse. La diferencia es lógica: –

–

●

Nodos de escritura son aquellos en los que los datos se escribirán. Nodos de lectura son aquellos de los cuales se leerán datos.

Escritura (maestro) – Lectura (esclavo)

149

Irontec – istración de MySQL

MMM

●

http://mysql-mmm.org/ En el sistema de control se instalará: – –

●

En los nodos: – –

●

mysql-mmm-common_2.0.10-1_all.deb mysql-mmm-monitor_2.0.10-1_all.deb mysql-mmm-common_2.0.10-1_all.deb mysql-mmm-agent_2.0.10-1_all.deb

¡Junto con todas las dependencias!

150

Irontec – istración de MySQL

MMM ●

●

● ●

Los ficheros de configuración se guardan en /etc/mysqlmmm Todos tendrán un fichero llamado mmm_common.conf que será exactamente igual. El nodo de control mmm_mon.conf Los servidores de MySQL mmm_agent.conf

151

Irontec – istración de MySQL

MMM ● ●

mmm_common.conf Incluye la configuración de: – – – – – –

Los Hosts Sus Ips Los roles /Contraseña del agente /Contraseña para la replicación La interfaz de red en la que se trabaja

152

Irontec – istración de MySQL

MMM ● ●

mmm_mon.conf Incluye la configuración de: – – – – –

/Contraseña para la monitorización Ips a las que pingar Ruta de los binarios Ruta del PID Nivel de debug

153

Irontec – istración de MySQL

MMM ● ●

mmm_agent Incluye la configuración de: – – –

El nombre de este servidor Todo el mmm_common.conf Y nada más :P

154

Irontec – istración de MySQL

MMM

●

Como hemos visto, hay varios s y contraseñas definidos. Hay que crerlos en MySQL. GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'10.100.1.%'  IDENTIFIED BY 'RepMonitor'; GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO  'mmm_agent'@'10.100.1.%' IDENTIFIED BY 'RepAgent'; GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.100.1.%'  IDENTIFIED BY 'slave';

–

–

–

El monitor se usa para comprobar el estado de los servidores Mysql. El agent se usa para cambiar el read only mode, poner offline un equipo, ejecutar un change_master, etc. El replication slave... para replicación ;) 155

Irontec – istración de MySQL

MMM ●

Una vez puesto en marcha los servicios, se deben poner online los servidores desde el comando de control. MMM:~# mmm_control show   db1(10.100.1.1) master/AWAITING_RECOVERY. Roles:    db2(10.100.1.2) master/AWAITING_RECOVERY. Roles:    db3(10.100.1.3) slave/AWAITING_RECOVERY. Roles:    db4(10.100.1.4) slave/AWAITING_RECOVERY. Roles: MMM:~# mmm_control set_online db1 OK: State of 'db1' changed to ONLINE. Now you can wait  some time and check its new roles! MMM:~# mmm_control set_online db2 OK: State of 'db2' changed to ONLINE. Now you can wait  some time and check its new roles! MMM:~# mmm_control set_online db3 OK: State of 'db3' changed to ONLINE. Now you can wait  some time and check its new roles! MMM:~# mmm_control set_online db4 OK: State of 'db4' changed to ONLINE. Now you can wait  some time and check its new roles! 156

Irontec – istración de MySQL

MMM ●

¡Ejemplo! Parando un servidor en producción: MMM:~# mmm_control set_offline db1 OK: State of 'db1' changed to _OFFLINE. Now you can  wait some time and check all roles! MMM:~# mmm_control show   db1(10.100.1.1) master/_OFFLINE. Roles:    db2(10.100.1.2) master/ONLINE. Roles:  writer(10.100.1.10)   db3(10.100.1.3) slave/ONLINE. Roles: reader(10.100.1.12)   db4(10.100.1.4) slave/ONLINE. Roles: reader(10.100.1.11)

157

Irontec – istración de MySQL

MySQL Proxy

158

Irontec – istración de MySQL

MySQL Proxy ●

●

●

●

El balanceo de carga se puede hacer bien por hardware como por software. Existe un software creado para MySQL que nos puede “ayudar”. Para que te ayude debes saber LUA. http://forge.mysql.com/wiki/MySQL_Proxy Es un proxy que se pone entre el cliente y los servidores de MySQL.

159

Irontec – istración de MySQL

MySQL Proxy ●

Trae scripts de ejemplo, que entre otras cosas te permite: – – – – –

Reescribir queries. Balanceo de carga. Loggeo avanzado. Failover. Análisis de queries.

160

Irontec – istración de MySQL

MySQL Proxy ●

La instalación es sencilla, descargamos de: –

●

Descomprimimos en: –

● ●

http://dev.mysql.com/s/mysql-proxy/ /usr/local/mysql-proxy

Tenemos el binario en bin Tenemos scripts de ejemplo en share/doc/mysql-proxy/

161

Irontec – istración de MySQL

MySQL Proxy ●

●

●

Los parámetros mínimos a indicar son los servidores para los cuales hará de proxy Si no se indica nada más, actuará como balanceador de carga round-robin Proxy: mysql­proxy ­­proxy­address=10.10.0.123:4040 ­­proxy­backend­ addresses=127.0.0.1:18002 ­­proxy­backend­addresses=127.0.0.1:18001

●

Dos conexiones de ejemplo balanceadas:

mysql> show variables like '%host%'; +­­­­­­­­­­­­­­­+­­­­­­­­­+ | Variable_name | Value   | +­­­­­­­­­­­­­­­+­­­­­­­­­+ | hostname      | shyris  |  | report_host   | SBnode2 |  +­­­­­­­­­­­­­­­+­­­­­­­­­+ 2 rows in set (0,00 sec)

mysql> show variables like '%host%'; +­­­­­­­­­­­­­­­+­­­­­­­­­+ | Variable_name | Value   | +­­­­­­­­­­­­­­­+­­­­­­­­­+ | hostname      | shyris  |  | report_host   | SBnode1 |  +­­­­­­­­­­­­­­­+­­­­­­­­­+ 2 rows in set (0,00 sec) 162

Irontec – istración de MySQL

MySQL Proxy ● ●

Ya vienen una serie de scripts incluidos Algunas funciones del script: – – – –

●

Balancear lecturas entre nodos esclavos Separar lecturas/escrituras entre distintos nodos Reescribir querys …

Para hacer uso de un script se usa la opción: –

­­proxy­lua­script=

163

Irontec – istración de MySQL

MySQL Proxy ●

Ejemplo de reescritura de querys (template-rewrite): –

●

mysql­proxy ­­proxy­address=10.10.0.123:4040 ­­proxy­backend­ addresses=127.0.0.1:24155 –proxy­lua­ script=/usr/local/mysql­proxy/share/doc/mysql­ proxy/tutorial­rewrite.lua

Ahora podemos ejecutar en MySQL los comandos ls y who :)

164

Irontec – istración de MySQL

Otras soluciones de HA y LB ●

●

MMM o MySQL proxy son solo algunas de las soluciones de alta disponibilidad y balanceo de carga Hay muchas opciones, propias de MySQL o genericas del sistema operativo – – – – – –

MySQL Cluster DRBD LVS Heartbeat Flipper etc.

165

Irontec – istración de MySQL

Otras soluciones de HA y LB ● ●

Cada uno tiene sus puntos debiles y fuertes Antes de decirdirse... – – – – – – –

●

Documentarse Probar Probar Probar Documentarse Probar Y elegir!

Importante, que tenga una buena comunidad de s/desarrolladores detrás :)

166

Irontec – istración de MySQL

Backup

167

Irontec – istración de MySQL

Backup ● ●

●

●

Siempre es importante tener un buen backup Además de tenerlo, ¡debe funcionar cuando lo necesitemos! En MySQL no hay una única forma de realizar los backups, existen muchas herramientas Los backups pueden ser: – –

De texto plano Binarios

168

Irontec – istración de MySQL

Backup ● ● ●

● ●

Backup texto plano Estos backups son generalmente más lentos Generan un fichero con todas las secuencias SQL necesarias para recuperar una BBDD o tabla Son portables entre distintas arquitecturas El servidor de MySQL tiene que estar en funcionamiento mysqldump outfile

169

Irontec – istración de MySQL

Backup ● ● ●

● ●

Backups binarios Són más rápidos No son portables a diferentes arquitecturas, p.ej de Intel x86 a PowerPC El servicio puede estar parado En resumen, se trata de copiar los ficheros de las tablas a mano o con herramientas especializadas mysqlhotcopy xtrabackup ibbackup 170

Irontec – istración de MySQL

Backups ● ●

Outfile Es posible hacer backup desde el propio MySQL mysql> SELECT * INTO OUTFILE 'fichero' FROM tabla;

● ● ● ● ● ●

El fichero de salida no debe existir Vale para cualquier engine Requiere privilegio FILE No tiene muchas más opciones :) Válido tanto para un MySQL local como remoto Limitado pero funcional para un momento dado 171

Irontec – istración de MySQL

Backup ● ● ●

●

Mysqldump Puede hacer copia de BBDD completas, o tablas Puede ser local o remoto (almacenándose siempre localmente) Puedes guardar los datos en ficheros con campos separados por tabulador (el típico CSV)

172

Irontec – istración de MySQL

Backup ● ●

Mysqldump Dumpear una BD: –

●

Dumpear varias tablas de una BD: –

●

mysqldump miDB tabla1 tabla2 > fichero

Dumpar dos DB: –

●

mysqldump miBD > fichero

mysqldump –databases miDB tuDB > fichero

Dumpear todas las DB: –

mysqldump –all-databases > fichero

173

Irontec – istración de MySQL

Backup ● ●

mysqldump Por defecto se aplica la opción –opt, que incluye: –

–

–

–

– –

add-locks: Añade lock tables para las tablas dumpeadas (asi al recuperar ningun proceso interfiere en las tablas) create-options: Añade los statements CREATE TABLE, de forma que las crea y luego las llena de datos quick: Escribe en el dump segun va leyendo las filas. Bueno si tienes una tabla enorme extended-insert: En lugar de mil inserts, agrupa varios inserts en uno solo lock-tables: Bloquea las tablas antes hacer el backup disable-keys: Añade un alter table que deshabilita la actualización de los índices al recuperar el backup, esto hace que la recuperación seá mas rapida 174

Irontec – istración de MySQL

Backup ● ●

●

Backup binario de myisam Para hacer un backup binario de tablas myisam se deben copiar los ficheros .frm .MYD y .MYI El servidor debe estar parado o... en caso de estár encendido las tablas bloqueadas: mysql> use MyDB; mysql> LOCK TABLES MyTable READ; mysql> FLUSH TABLES MyTable; … backup mysql> UNLOCK TABLES;

175

Irontec – istración de MySQL

Backup ● ●

●

Backup binario de MyISAM Es un script en perl que puede hacer backup de tablas MyISAM Se conecta a MySQL, bloquea las tablas, las flushea y finalmente las copia mysqlhotbackup world /tmp/backup mysqlhotbackup world./City/ /tmp/backup

176

Irontec – istración de MySQL

Backup ● ● ●

Backup binario de InnoDB Es necesario tener el servidor parado Se debe copiar: – – – –

●

Los ficheros .frm Los tablespaces .ibd Los logs de InnoDB Los ficheros de configuración que tengas con opciones de InnoDB

Para hacerlo online existen soluciones comerciales (InnoDB Hot Backup) y soluciones libres (xtrabackup)

177

Irontec – istración de MySQL

Backup ● ●

● ●

●

XtraBackup Herramienta de backup desarrollado por Percona para hacer backup en vivo de tablas InnoDB http://www.percona.com/mysql/xtrabackup/ El binario xtrabackup por defecto solo soporta InnoDB Viene un script en Perl llamado innobackupex-1.5.1 que nos copiará todas las BBDD y tablas existentes (será como un frontend para XtraBackup) El binario XtraBackup por si sólo únicamente backupea el tablespace

178

Irontec – istración de MySQL

Backup ● ●

XtraBackup Hacer un backup innobackupex­1.5.1 /tmp/backup/

●

Preparar el backup (aplicar los logs de innodb creados durante el backup) innobackupex­1.5.1 ­­apply­log /tmp/backup/

●

●

¡Listo! En /tmp/backup tienes una carpeta con la fecha del backup que se puede copiar directamente a /var/lib/mysql ¡ATENCION! Los permisos no se mantienen, tienes que reaplicarlos

179

Irontec – istración de MySQL

Backup ● ●

● ●

Binary logs Si haces un backup todos los días a las 00:00 y la BBDD se rompe a las 08:00, has perdido 8 horas de datos Los logs binarios nos permiten recuperar esos datos Extraer a un .sql mysqlbinlog /var/log/mysql/bin.123456 /tmp/mysql_restore.sql

●

Recuperar todo el binlog mysqlbinlog /var/log/mysql/bin.123456 | mysql ­u root

●

Recuperar hasta una fecha/hora mysqlbinlog ­­stop­date="2005­04­20  9:59:59"/var/log/mysql/bin.123456 | mysql ­u root

●

Recuperar desde una fecha/hora mysqlbinlog ­­start­date="2005­04­20 10:01:00"  /var/log/mysql/bin.123456| mysql ­u root

180

Irontec – istración de MySQL

Se acabó

[email protected] miguel2angel http://miguelangelnieto.net

181

Irontec – istración de MySQL

Licencia Creative Commons 3.0

182