informatica:linux:mysql:instalacion
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
informatica:linux:mysql:instalacion [2014/04/14 13:43] – [Volcado de base de datos] javi | informatica:linux:mysql:instalacion [2015/04/13 20:19] – external edit 127.0.0.1 | ||
---|---|---|---|
Line 24: | Line 24: | ||
Poniendola en texto plano: | Poniendola en texto plano: | ||
#mysql -u < | #mysql -u < | ||
- | A otra máquina: | + | A otra máquina. Tiene que tener habilitado permitir conexiones de fuera. Para ello hay que modificar el fichero: |
+ | / | ||
+ | Y comentar la línea: | ||
+ | # bind-address | ||
+ | Luego ya nos podemos conectar con el comando: | ||
#mysql -u < | #mysql -u < | ||
- | |||
Line 83: | Line 86: | ||
2. Tener una idea de tamanyo y tiempos: | 2. Tener una idea de tamanyo y tiempos: | ||
- | Metricas sobre tiempo. Para el siguiente tamanyo: | + | Metricas sobre tiempo. Para el siguiente tamanyo |
+ | |||
+ | time mysql -e " | ||
- | DB=" | ||
En mi caso: | En mi caso: | ||
Line 107: | Line 111: | ||
| ?? | 0m11.443s | 17m41.754s | | | ?? | 0m11.443s | 17m41.754s | | ||
| 33692.87 | 17m26.434s | ?? | | | 33692.87 | 17m26.434s | ?? | | ||
+ | | 16622.94 | 8' | ||
< | < | ||
Line 129: | Line 134: | ||
* MyISAM | * MyISAM | ||
- | time mysqldump --opt --add-drop-database --databases database_one database_two | + | time mysqldump |
* InnoDB | * InnoDB | ||
- | time mysqldump --single-transaction --add-drop-database --databases database_one database_two | + | time mysqldump |
* Incluir triggers y functions | * Incluir triggers y functions | ||
Line 466: | Line 471: | ||
3. (db_master) Abrir otra consola y volcar las bases de datos que queramos replicar. En mi caso todas: | 3. (db_master) Abrir otra consola y volcar las bases de datos que queramos replicar. En mi caso todas: | ||
- | time mysqldump --opt --all-databases --add-drop-database > / | + | time mysqldump |
El proceso tarda menos de 5 minutos para generar un volcado de 863M. | El proceso tarda menos de 5 minutos para generar un volcado de 863M. | ||
Line 860: | Line 865: | ||
</ | </ | ||
+ | ===== Procedures ===== | ||
+ | * Crear | ||
+ | < | ||
+ | DELIMITER $$ | ||
+ | CREATE PROCEDURE `partition_create`(SCHEMANAME VARCHAR(64), | ||
+ | BEGIN | ||
+ | /* | ||
+ | | ||
+ | | ||
+ | | ||
+ | */ | ||
+ | /* | ||
+ | | ||
+ | */ | ||
+ | |||
+ | DECLARE RETROWS INT; | ||
+ | SELECT COUNT(1) INTO RETROWS | ||
+ | FROM information_schema.partitions | ||
+ | WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_description >= CLOCK; | ||
+ | |||
+ | IF RETROWS = 0 THEN | ||
+ | /* | ||
+ | 1. Print a message indicating that a partition was created. | ||
+ | 2. Create the SQL to create the partition. | ||
+ | 3. Execute the SQL from #2. | ||
+ | */ | ||
+ | SELECT CONCAT( " | ||
+ | SET @SQL = CONCAT( 'ALTER TABLE ', SCHEMANAME, ' | ||
+ | PREPARE STMT FROM @SQL; | ||
+ | EXECUTE STMT; | ||
+ | DEALLOCATE PREPARE STMT; | ||
+ | END IF; | ||
+ | END$$ | ||
+ | DELIMITER ; | ||
+ | </ | ||
+ | |||
+ | * Listar | ||
+ | < | ||
+ | SHOW PROCEDURE STATUS; | ||
+ | SHOW FUNCTION STATUS; | ||
+ | </ | ||
+ | |||
+ | ===== Partitions ===== | ||
+ | |||
+ | * Listar | ||
+ | < | ||
+ | SHOW CREATE TABLE zabbix2014.history\G | ||
+ | *************************** 1. row *************************** | ||
+ | | ||
+ | Create Table: CREATE TABLE `history` ( | ||
+ | `itemid` bigint(20) unsigned NOT NULL, | ||
+ | `clock` int(11) NOT NULL DEFAULT ' | ||
+ | `value` double(16, | ||
+ | `ns` int(11) NOT NULL DEFAULT ' | ||
+ | KEY `history_1` (`itemid`, | ||
+ | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | ||
+ | /*!50100 PARTITION BY RANGE (`clock`) | ||
+ | (PARTITION p201503130000 VALUES LESS THAN (1426287600) ENGINE = InnoDB, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | 1 row in set (0.01 sec) | ||
+ | </ | ||
===== Errores ===== | ===== Errores ===== | ||
informatica/linux/mysql/instalacion.txt · Last modified: 2017/10/20 09:52 by javi