notas:bases_de_datos:mysql
Diferencias
Muestra las diferencias entre dos versiones de la página.
Ambos lados, revisión anteriorRevisión previaPróxima revisión | Revisión previa | ||
notas:bases_de_datos:mysql [2015/04/22 14:41] – cayu | notas:bases_de_datos:mysql [2021/01/21 17:47] (actual) – [Migrar una base a InnoDB] cayu | ||
---|---|---|---|
Línea 1: | Línea 1: | ||
====== Task MySQL ====== | ====== Task MySQL ====== | ||
===== Administración ===== | ===== Administración ===== | ||
- | Script para listas | + | ==== Scripts ==== |
+ | Script para listar | ||
<code bash optimise.sh> | <code bash optimise.sh> | ||
#!/bin/sh | #!/bin/sh | ||
Línea 67: | Línea 68: | ||
</ | </ | ||
+ | ==== Tamaños de bases y tablas ==== | ||
+ | <code sql> | ||
+ | SELECT table_schema "DB Name", | ||
+ | ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" | ||
+ | FROM information_schema.tables | ||
+ | GROUP BY table_schema; | ||
+ | </ | ||
+ | <code sql> | ||
+ | SELECT table_name AS " | ||
+ | ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" | ||
+ | FROM information_schema.TABLES | ||
+ | WHERE table_schema = " | ||
+ | ORDER BY (data_length + index_length) DESC; | ||
+ | </ | ||
+ | |||
+ | ==== Migrar una base a InnoDB ==== | ||
+ | < | ||
+ | # mysql base < mig_innodb.sql | ||
+ | </ | ||
+ | <code sql> | ||
+ | DROP PROCEDURE IF EXISTS convertToInnodb; | ||
+ | DELIMITER // | ||
+ | CREATE PROCEDURE convertToInnodb() | ||
+ | BEGIN | ||
+ | mainloop: LOOP | ||
+ | SELECT TABLE_NAME INTO @convertTable FROM information_schema.TABLES | ||
+ | WHERE `TABLE_SCHEMA` LIKE DATABASE() | ||
+ | AND `ENGINE` LIKE ' | ||
+ | IF @convertTable IS NULL THEN | ||
+ | LEAVE mainloop; | ||
+ | END IF; | ||
+ | SET @sqltext := CONCAT(' | ||
+ | PREPARE convertTables FROM @sqltext; | ||
+ | EXECUTE convertTables; | ||
+ | DEALLOCATE PREPARE convertTables; | ||
+ | SET @convertTable = NULL; | ||
+ | END LOOP mainloop; | ||
+ | |||
+ | END// | ||
+ | DELIMITER ; | ||
+ | |||
+ | CALL convertToInnodb(); | ||
+ | DROP PROCEDURE IF EXISTS convertToInnodb; | ||
+ | </ | ||
+ | ==== Comprimir una tabla ==== | ||
+ | <code sql> | ||
+ | ALTER TABLE syslog key_block_size=8 row_format=compressed; | ||
+ | </ |
notas/bases_de_datos/mysql.1429713661.txt.gz · Última modificación: 2015/04/22 14:41 por cayu