Diferencias

Muestra las diferencias entre dos versiones de la página.

--- 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 ======
 ===== Administración =====
-Script para listas las tablas fragmentadas y correr un optimize
+==== Scripts ====
+Script para listar las tablas fragmentadas y correr un optimize
 <code bash optimise.sh>
 #!/bin/sh
@@ Línea 67: / Línea 68: @@
 </code>
+==== 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>
+<code sql>
+SELECT table_name AS "Table",
+ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
+FROM information_schema.TABLES
+WHERE table_schema = "database_name"
+ORDER BY (data_length + index_length) DESC;
+</code>
+==== Migrar una base a InnoDB ====
+<code>
+# mysql base < mig_innodb.sql
+</code>
+<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 'MyISAM' ORDER BY TABLE_NAME LIMIT 1;
+  IF @convertTable IS NULL THEN
+    LEAVE mainloop;
+  END IF;
+  SET @sqltext := CONCAT('ALTER TABLE `', DATABASE(), '`.`', @convertTable, '` ENGINE = INNODB');
+  PREPARE convertTables FROM @sqltext;
+  EXECUTE convertTables;
+  DEALLOCATE PREPARE convertTables;
+  SET @convertTable = NULL;
+END LOOP mainloop;
+END//
+DELIMITER ;
+CALL convertToInnodb();
+DROP PROCEDURE IF EXISTS convertToInnodb;
+</code>
+==== Comprimir una tabla ====
+<code sql>
+ALTER TABLE syslog key_block_size=8 row_format=compressed;
+</code>