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 [2014/12/18 20:28] – 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 ====== | ||
- | ===== MySQL y datos CSV ===== | + | ===== Administración |
+ | ==== Scripts ==== | ||
+ | Script para listar las tablas fragmentadas y correr un optimize | ||
+ | <code bash optimise.sh> | ||
+ | #!/bin/sh | ||
+ | echo -n "MySQL username: " ; read username | ||
+ | echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo | ||
+ | mysql -u $username -p" | ||
+ | mysql -u $username -p" | ||
+ | if [ " | ||
+ | | ||
+ | echo " | ||
+ | mysql -u " | ||
+ | fi | ||
+ | done | ||
+ | done | ||
+ | </ | ||
+ | ===== Manejo de datos ===== | ||
+ | ==== MySQL y datos CSV ==== | ||
Importacion : | Importacion : | ||
<code sql> | <code sql> | ||
Línea 24: | Línea 42: | ||
mysql -u root -p cdr -B -e " | mysql -u root -p cdr -B -e " | ||
</ | </ | ||
- | + | ==== Unix Time ==== | |
- | ===== Unix Time ===== | + | |
<code sql> | <code sql> | ||
SELECT * , FROM_UNIXTIME( `dateTimeOrigination` ) AS Fecha | SELECT * , FROM_UNIXTIME( `dateTimeOrigination` ) AS Fecha | ||
Línea 31: | Línea 48: | ||
WHERE `finalCalledPartyNumber` = ' | WHERE `finalCalledPartyNumber` = ' | ||
</ | </ | ||
- | |||
<code sql> | <code sql> | ||
SELECT * , FROM_UNIXTIME( `dateTimeOrigination` ) AS fecha | SELECT * , FROM_UNIXTIME( `dateTimeOrigination` ) AS fecha | ||
Línea 39: | Línea 55: | ||
ORDER BY fecha | ORDER BY fecha | ||
</ | </ | ||
- | + | ==== MySQL y Wordpress ==== | |
- | + | ||
- | ===== MySQL y Wordpress ===== | + | |
<code sql> | <code sql> | ||
UPDATE wp_posts SET guid = replace(guid, | UPDATE wp_posts SET guid = replace(guid, | ||
Línea 53: | Línea 66: | ||
/*UPDATE wp_options SET option_value = replace(option_value, | /*UPDATE wp_options SET option_value = replace(option_value, | ||
UPDATE wp_options SET option_value = replace(option_value, | UPDATE wp_options SET option_value = replace(option_value, | ||
+ | </ | ||
+ | |||
+ | ==== 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.1418934504.txt.gz · Última modificación: 2014/12/18 20:28 por cayu