Differences

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

--- informatica:linux:mysql:instalacion [2014/03/17 11:39] – [145 - Table was marked as crashed and should be repaired] javi
+++ informatica:linux:mysql:instalacion [2017/10/20 09:52] (current) – [Errores] javi
@@ 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 79: / Line 95: @@
 . Obtener motor de las tablas de las(s) bases de datos:
-  mysql -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'DATABASE_NAME'" -u root -p
+  DB="database_xxxx"; mysql -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = '$DB'" -u root -p
 . 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
-  mysql -e "SELECT table_schema \"DATABASE_NAME\", 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
+<code>
+--routines
+</code>
 ==== Usuario dedicado con permisos minimos para mysqlump ====
@@ Line 462: / 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 834: / Line 856: @@
 +--------------------------+----------------------------+
 </code>
+===== Logging consultas =====
+  * Habilitar
+<code>
+mysql -u root -p -e "set global general_log_file="/tmp/mysql.log";
+mysql -u root -p -e "set global general_log = 'ON';"
+</code>
+  * Deshabilitar
+<code>
+mysql -u root -p -e "set global general_log = 'OFF';"
+</code>
+===== Consultas utiles =====
+  * Listar el tamanyo de las tablas de una base de datos, las pesadas primero (ajustar variable "DB"):
+<code>
+DB=zabbix; mysql -u root -p -e "SELECT table_name AS 'Tables',  round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB'  FROM information_schema.TABLES  WHERE table_schema = \"$DB\" ORDER BY (data_length + index_length) DESC;"
+</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>
+===== Strict Mode =====
+He conocido de su existencia a través de django:
+https://docs.djangoproject.com/en/1.11/ref/databases/#mysql-sql-mode
+Django recomienda habilitar el modo estricto, con la configuración "STRICT_TRANS_TABLES":
+. Editar archivo de configuración:
+<code>
+sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.bak
+sudo vim  /etc/mysql/mariadb.conf.d/50-server.cnf
+</code>
+. Y añadir en la sección "[mysqld]" la siguiente linea:
+<code>
+...
+[mysqld]
+sql-mode = "STRICT_TRANS_TABLES"
+</code>
+. Reiniciar el servicio
+  sudo service mysql restart
+. Verificarlo
+.1. Iniciar sesión como usuario "root"
+  sudo mysql -u root
+.2. Ver el valor de la variable "sql_mode":
+  SELECT @@SESSION.sql_mode;
+Valor esperado:
+<code>
++---------------------+
+| @@SESSION.sql_mode  |
++---------------------+
+| STRICT_TRANS_TABLES |
++---------------------+
+</code>
 ===== Errores =====
@@ Line 1003: / Line 1161: @@
 http://dev.mysql.com/doc/refman/5.0/en/check-table.html
+==== ERROR 1194 (HY000): Table 'wallet' is marked as crashed and should be repaired ====
+<code>
+mysql> select * from wallet;
+ERROR 1194 (HY000): Table 'wallet' is marked as crashed and should be repaired
+</code>
+<code>
+mysql> check table wallet;
++------------------+-------+----------+-----------------------------------------------------+
+| Table            | Op    | Msg_type | Msg_text                                            |
++------------------+-------+----------+-----------------------------------------------------+
+| passwords.wallet | check | error    | Size of datafile is: 14648         Should be: 15340 |
+| passwords.wallet | check | error    | Corrupt                                             |
++------------------+-------+----------+-----------------------------------------------------+
+rows in set (0.01 sec)
+</code>
+<code>
+mysql> repair table wallet;
++------------------+--------+----------+--------------------------------------+
+| Table            | Op     | Msg_type | Msg_text                             |
++------------------+--------+----------+--------------------------------------+
+| passwords.wallet | repair | warning  | Number of rows changed from 82 to 80 |
+| passwords.wallet | repair | status   | OK                                   |
++------------------+--------+----------+--------------------------------------+
+rows in set (0.22 sec)
+</code>