Differences

This shows you the differences between two versions of the page.

--- informatica:linux:mysql:instalacion [2014/04/14 13:43] – [Volcado de base de datos] javi
+++ informatica:linux:mysql:instalacion [2017/06/24 17:05] – jose
@@ Line 24: / Line 24: @@
 Poniendola en texto plano:
   #mysql -u <usuario> -p <password>
-A otra máquina:
+A otra máquina. Tiene que tener habilitado permitir conexiones de fuera. Para ello hay que modificar el fichero:
+  /etc/mysql/my.cnf
+Y comentar la línea:
+  # bind-address      = 192.168.1.103
+Luego ya nos podemos conectar con el comando:
   #mysql -u <usuario> -h <hostname> -p <password>
+===== Mostrar BBDD =====
+  mysql> show databases;
+<code>
++--------------------+
+| Database           |
++--------------------+
+| information_schema |
+| mysql              |
+| performance_schema |
+| phpmyadmin         |
+| pingpong           |
++--------------------+
+rows in set (0.00 sec)
+</code>
@@ Line 83: / Line 99: @@
 . Tener una idea de tamanyo y tiempos:
-Metricas sobre tiempo. Para el siguiente tamanyo:
+Metricas sobre tiempo. Para el siguiente tamanyo (tarda unos 35 segundos con BD grandes):
+  time mysql -e "SELECT table_schema \"DB\", SUM( data_length + index_length) / 1024 / 1024 \"Data Base Size in MB\" FROM information_schema.TABLES GROUP BY table_schema;" -u root -p
-  DB="database_xxxx"; mysql -e "SELECT table_schema \"DB\", SUM( data_length + index_length) / 1024 / 1024 \"Data Base Size in MB\" FROM information_schema.TABLES GROUP BY table_schema;" -u root -p
 En mi caso:
@@ Line 107: / Line 124: @@
 | ?? | 0m11.443s | 17m41.754s |
 | 33692.87 | 17m26.434s | ?? |
+| 16622.94 | 8'45" | 40'35" |
 <code>
@@ Line 129: / Line 147: @@
   * MyISAM
-  time mysqldump --opt --add-drop-database --databases database_one database_two  > ~/db_dump_$(date +%d-%m-%y).sql -u root -p
+  time mysqldump --routines --opt --add-drop-database --databases database_one database_two  > ~/db_dump_$(date +%d-%m-%y).sql -u root -p
   * InnoDB
-  time mysqldump --single-transaction --add-drop-database --databases database_one database_two  > ~/db_dump_$(date +%d-%m-%y).sql -u root -p
+  time mysqldump --routines --single-transaction --add-drop-database --databases database_one database_two  > ~/db_dump_$(date +%d-%m-%y).sql -u root -p
   * Incluir triggers y functions
@@ Line 466: / Line 484: @@
 . (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 > /tmp/all_db_dump.sql -u root -p
+  time mysqldump --routines --opt --all-databases --add-drop-database > /tmp/all_db_dump.sql -u root -p
 El proceso tarda menos de 5 minutos para generar un volcado de 863M.
@@ Line 860: / Line 878: @@
 </code>
+===== Procedures =====
+  * Crear
+<code>
+DELIMITER $$
+CREATE PROCEDURE `partition_create`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT)
+BEGIN
+        /*
+           SCHEMANAME = The DB schema in which to make changes
+           TABLENAME = The table with partitions to potentially delete
+           PARTITIONNAME = The name of the partition to create
+        */
+        /*
+           Verify that the partition does not already exist
+        */
+        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
+                /*
+. Print a message indicating that a partition was created.
+. Create the SQL to create the partition.
+. Execute the SQL from #2.
+                */
+                SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
+                SET @SQL = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
+                PREPARE STMT FROM @SQL;
+                EXECUTE STMT;
+                DEALLOCATE PREPARE STMT;
+        END IF;
+END$$
+DELIMITER ;
+</code>
+  * Listar
+<code>
+SHOW PROCEDURE STATUS;
+SHOW FUNCTION STATUS;
+</code>
+===== Partitions =====
+  * Listar
+<code>
+SHOW CREATE TABLE zabbix2014.history\G
+*************************** 1. row ***************************
+       Table: history
+Create Table: CREATE TABLE `history` (
+  `itemid` bigint(20) unsigned NOT NULL,
+  `clock` int(11) NOT NULL DEFAULT '0',
+  `value` double(16,4) NOT NULL DEFAULT '0.0000',
+  `ns` int(11) NOT NULL DEFAULT '0',
+  KEY `history_1` (`itemid`,`clock`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+/*!50100 PARTITION BY RANGE (`clock`)
+(PARTITION p201503130000 VALUES LESS THAN (1426287600) ENGINE = InnoDB,
+ PARTITION p201503140000 VALUES LESS THAN (1426374000) ENGINE = InnoDB,
+ PARTITION p201503150000 VALUES LESS THAN (1426460400) ENGINE = InnoDB,
+ PARTITION p201503160000 VALUES LESS THAN (1426546800) ENGINE = InnoDB,
+ PARTITION p201503170000 VALUES LESS THAN (1426633200) ENGINE = InnoDB,
+ PARTITION p201503180000 VALUES LESS THAN (1426719600) ENGINE = InnoDB,
+ PARTITION p201503190000 VALUES LESS THAN (1426806000) ENGINE = InnoDB) */
+row in set (0.01 sec)
+</code>
 ===== Errores =====