====== Mysql ====== mysql database base de datos ===== Instalación ===== * Cliente aptitude install mysql * Servidor aptitude install mysql-server * Cliente gráfico para realizar consultas...: aptitude install mysql-query-browser ===== Conectar ===== Poniendo la contraseña oculta: #mysql -u -p Poniendola en texto plano: #mysql -u -p 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 -h -p ===== Mostrar BBDD ===== mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | pingpong | +--------------------+ 5 rows in set (0.00 sec) ===== Permisos ===== **NOTA** si quieres que los cambios tomen efecto inmediatamente, ejecuta al final: flush privileges; * Crear un nuevo usuario (requiere estar validado como usuario 'root' de mysql) que pueda conectarse desde cualquier máquina: grant usage on *.* to 'usuario'@'%' identified by 'mi_clave'; * Igual que antes pero solo desde la máquina donde reside la base de datos mysql: grant usage on *.* to 'usuario'@'localhost' identified by 'mi_clave'; * Revocar lo anterior: revoke all on *.* from 'usuario'@'%'; * Dar todos los privilegios sobre una base de datos determinada a un usuario: grant all on mi_bd.* to 'usuario'@'%'; * Revocar lo anterior: revoke all on mi_bd.* from 'usuario'@'%'; * Permisos para backup (el usuario ya existe en este ejemplo): GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to backup@localhost; * Resetear la contrasenya de 'root': UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; FLUSH PRIVILEGES; Si se quiere ajustar mas: http://www.muck.net/59/mysql-minimum-permissions-for-backup-using-mysqldump ===== Mysqldump ===== ==== Volcado de base de datos ==== 1. Obtener motor de las tablas de las(s) bases de datos: DB="database_xxxx"; mysql -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = '$DB'" -u root -p 2. Tener una idea de tamanyo y tiempos: 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 En mi caso: +--------------------+----------------------+ | Data Base Name | Data Base Size in MB | +--------------------+----------------------+ | drupal | 6.78125000 | | information_schema | 0.00781250 | | mysql | 0.62780762 | | zabbix | 300.93750000 | +--------------------+----------------------+ Tiempos: | Realizar el volcado | 0m11.443s | | Importarlo | 17m41.754s | ^ Tamanyo (MB) ^ Tiempo realizar volcado ^ Tiempo importarlo ^ | ?? | 0m11.443s | 17m41.754s | | 33692.87 | 17m26.434s | ?? | | 16622.94 | 8'45" | 40'35" | +--------------------+----------------------+ | Data Base Name | Data Base Size in MB | +--------------------+----------------------+ | drupal | 6.78125000 | | information_schema | 0.00781250 | | mysql | 0.62780762 | | zabbix | 1477.12500000 | +--------------------+----------------------+ 4 rows in set (10.41 sec) Tiempos (estaba haciendo un snapshot a la vez, no se si afecto): | Realizar el volcado | real 2m58.529s | | Importarlo | | 3. Volcado: * MyISAM 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 --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 --routines ==== Usuario dedicado con permisos minimos para mysqlump ==== http://www.muck.net/59/mysql-minimum-permissions-for-backup-using-mysqldump GRANT SELECT, LOCK TABLES ON *.* TO 'mysqldumpuser'@'localhost' IDENTIFIED BY 'secret'; ==== Mysqldump sin contrasenya ==== 1. Crear archivo: vim ~/.my.cnf Con el siguiente contenido: [mysqldump] user=mysqldumpuser password=secret 2. Restringir permisos: chmod 0600 ~/.my.cnf ===== Crear esquema: BBDD y usuario con mismo nombre ===== CREATE USER 'omnicost.org'@'localhost' IDENTIFIED BY '***'; GRANT USAGE ON * . * TO 'omnicost.org'@'localhost' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; CREATE DATABASE IF NOT EXISTS `omnicost.org` ; GRANT ALL PRIVILEGES ON `omnicost.org` . * TO 'omnicost.org'@'localhost'; ===== Conectar desde PHP ===== '2010-10-04 15:30:00' and fecha<'2010-10-04 20:30:00'"); while($row = mysql_fetch_array($result)) { echo $row['consulta_tiempo'] . " " . $row['fecha']; echo "
"; } mysql_close($conn); ?> ===== Contraseña de root perdida resetear ===== http://www.cyberciti.biz/tips/recover-mysql-root-password.html ===== Replicación ===== Mysql sigue un sistema muy sencillo de replicación. El nodo maestro escribe todas las sentencias SQL en un log binario, y lleva un registro actualizado del nombre del log y de la posición que actualmente está escribiendo. El (o los) nodos esclavo consultan si el puntero a cambiado, y si es asi "consumen" esos cambios hasta que ambos nodos están sincronizados ==== Maestro - maestro ==== Equipos: ^ Host ^ IP ^ Notas ^ | mysql-2.dev.jj.com | 10.0.0.28 | Inicialmente master | | mysql-3.dev.jj.com | 10.0.0.29 | - | Por simplicidad se incluye entre parentesis (mysql-2.dev.jj.com) el nombre de la maquina donde se ejecuta el comando. 1. (mysql-2.dev.jj.com) Editamos el archivo de configuracion de MySQL: sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak; sudo vim /etc/mysql/my.cnf Y nos aseguramos que las siguientes opciones esten en la parte '[mysqld]' server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index log_slave_updates = 1 auto_increment_increment = 2 auto_increment_offset = 1 bind-address = 0.0.0.0 2. (mysql-2.dev.jj.com) Nos conectamos a mysql: mysql -u root -p Y creamos el usuario 'replication': grant replication slave on *.* to 'replication'@'%.dev.jj.com' identified by 'replication'; flush privileges; exit; 3. (mysql-2.dev.jj.com) Reiniciamos MySQL: sudo /etc/init.d/mysql-server restart 4. (mysql-3.dev.jj.com) Editamos el archivo: sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak; sudo vim /etc/mysql/my.cnf Y nos aseguramos que las siguientes opciones esten en la parte '[mysqld]' server-id = 2 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index log_slave_updates = 1 auto_increment_increment = 2 auto_increment_offset = 1 bind-address = 0.0.0.0 **OJO**: todo igual que el paso 1. excepto 'server-id', que ahora es '2' 5. (mysql-3.dev.jj.com) Nos conectamos a mysql: mysql -u root -p Y creamos el usuario 'replication': grant replication slave on *.* to 'replication'@'%.dev.jj.com' identified by 'replication'; flush privileges; exit; 6. (mysql-3.dev.jj.com) Reiniciamos MySQL: sudo /etc/init.d/mysql-server restart 7. (mysql-2.dev.jj.com) Nos conectamos a MySQL: mysql -u root -p Bloqueamos todas las tablas: FLUSH TABLES WITH READ LOCK; **OJO** mantener esta ventana abierta, pues si salimos (CTRL+c) el cerrojo se eliminara 8. (mysql-2.dev.jj.com) En otra sesion volcamos todas las bases de datos: mysqldump --opt --all-databases --add-drop-database > /mnt/no_data/all_db.sql -u root -p 9. (mysql-2.dev.jj.com) Volvemos a la sesion abierta en el paso 8. y mostramos el estado del nodo maestro: show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | | | +------------------+----------+--------------+------------------+ Tomamos nota de los valores y salimos, con lo que el cerrojo se elimina: CTRL+c 10. (mysql-3.dev.jj.com) Nos conectamos a MySQL: mysql -u root -p Bloqueamos todas las tablas: FLUSH TABLES WITH READ LOCK; **OJO** mantener esta ventana abierta, pues si salimos (CTRL+c) el cerrojo se eliminara 11. (mysql-3.dev.jj.com) En otra sesion volcamos todas las bases de datos: mysqldump --opt --all-databases --add-drop-database > /tmp/database-backup.sql -u root -p 12. (mysql-3.dev.jj.com) Volvemos a la sesion abierta en el paso 8. y mostramos el estado del nodo maestro: show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | | | +------------------+----------+--------------+------------------+ Tomamos nota de los valores y salimos, con lo que el cerrojo se elimina: CTRL+c 13. (mysql-2.dev.jj.com) Copiamos el volcado de las bases de datos a la otra maquina: scp /tmp/database-backup.sql mysql-3.dev.jj.com:/tmp/ 14. (mysql-3.dev.jj.com) Importamos el volcado de la base de datos del otro nodo: mysql -u root -p < /tmp/database-backup.sql 15. (mysql-3.dev.jj.com) Nos conectamos a mysql: mysql -u root -p Y configuramos el esclavo con los valores obtenidos en el paso 9: CHANGE MASTER TO master_host='mysql-2.dev.jj.com', master_user='replication', master_password='replication', master_log_file='mysql-bin.000001', master_log_pos=106; Si ya 'mysql-3.dev.jj.com' era esclavo de 'mysql-2.dev.jj.com' basta con actualizar el archivo y la posicion: CHANGE MASTER TO master_log_file='mysql-bin.000001', master_log_pos=106; 16. (mysql-3.dev.jj.com) Arrancamos el esclavo: start slave; 17. (mysql-3.dev.jj.com) Comprobamos: show slave status\G Si sale lo de abajo, ok: Slave_IO_State: Waiting for master to send event 18. (mysql-2.dev.jj.com) Nos conectamos a mysql: mysql -u root -p Y configuramos el esclavo con los valores obtenidos en el paso 12: CHANGE MASTER TO master_host='mysql-3.dev.jj.com', master_user='replication', master_password='replication', master_log_file='mysql-bin.000001', master_log_pos=107; Si ya 'mysql-2.dev.jj.com' era esclavo de 'mysql-3.dev.jj.com' basta con actualizar el archivo y la posicion: CHANGE MASTER TO master_log_file='mysql-bin.000001', master_log_pos=107; 19. (mysql-3.dev.jj.com) Arrancamos el esclavo: start slave; 20. (mysql-3.dev.jj.com) Comprobamos: show slave status\G Si sale lo de abajo, ok: Slave_IO_State: Waiting for master to send event 21. Probar creando y borrando por ejemplo bases de datos, arrancando y parando demoniso mysql... TODAS las bases de datos se tienen que replicar entre los dos nodos. ==== Varios ==== * Parar por completo la replicación 1. Parar replicación. mysql -u root -p stop slave; 2. Run "reset slave" to tell the slave server to forget it's position in the binary log retrieved from the master server reset slave; 3. Add "skip-slave-start" to my.cnf to prevent replication from starting when you restart MySQL sudo vim /etc/mysql/my.cnf ... skip-slave-start ... ===== Reparar replicacion rota ===== ==== Lenta (recomendada) ==== Asumo el siguiente escenario: | db_master | master | | db_slave | slave | 1. (db_master) Poner todas las bases de datos en modo solo lectura: mysql -u root -p FLUSH TABLES WITH READ LOCK; **AVISO**: NO cerrar esta ventana o las bases de datos volveran al modo normal, donde se puede escribir. 2. (db_master) Ver en que punto se ha quedado la escritura del log: show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.001178 | 81721737 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.06 sec) Anotar estos valores. 3. (db_master) Abrir otra consola y volcar las bases de datos que queramos replicar. En mi caso todas: 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. 4. (db_master) Cuando termine el volcado (paso 3) podemos devolver las bases de datos al modo escritura, tecleando en la consola del paso 1): exit; 5. (db_slave) Copiar el volcado: scp db_master:/tmp/all_db_dump.sql /tmp/ 6. (db_slave) Restaurar el volcado. Yo prefiero usar screen para no cerrar accidentalmente la ventana y tener que volver a iniciar el proceso: screen -S mysql time mysql -u root -p < /tmp/all_db_dump.sql CTRL + a + z Si queremos ir viendo si ha terminado el proceso el siguiente comando no tiene que mostrar nada: watch -n 5 'ps ax | grep mysql | grep root | grep -v watch' Para volver a la sesion de screen: screen -R mysql El proceso tarda menos de 50 minutos para restaurar un volcado de 863M. 7. (db_slave) Cuando termine el proceso de restauracion ajustamos el esclavo para que lea del punto exacto donde se quedo: mysql -u root -p CHANGE MASTER TO master_log_file='mysql-bin.001178', master_log_pos=50933406; 8. (db_slave) Arrancamos el esclavo: mysqladmin start-slave -u root -p 9. (db_slave) Comprobamos: mysql -e "show slave status\G" -u root -p | egrep "Slave_IO_State|Seconds_Behind_Master" Si todo va bien veremos algo asi: Slave_IO_State: Waiting for master to send event Seconds_Behind_Master: 1576 ==== Rapida (no recomendada) ==== Asumo el siguiente escenario: | db1 | servidor ok | | db2 | servidor cashcado | 1. (Opcional) Parar el envio de peticiones al servidor mysql cascado desde el balanceador de carga 2. (db1) Parar replicacion: mysql -u root -p stop slave; 3. (db2) Parar replicacion (de hecho esta rota): mysql -u root -p stop slave; Comprobar: show slave status\G 'Slave_IO_State' tiene que estar vacio: Slave_IO_State: 4. Obtener el punto en el que casco la replicacion: clear; sudo zgrep "We stopped" /var/log/syslog* | more Tenemos que ver una linea parecida a esta: Oct 15 12:18:54 mysql-2 mysqld: 121015 12:18:54 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000007' position 16995281 5. (Opcional) Volver a ese punto. En principio no es necesario, pero si hemos toqueteado si lo es: CHANGE MASTER TO master_log_file='mysql-bin.xxxxxx', master_log_pos=xxxxxx; (Creo que no es necesario todo el churro): CHANGE MASTER TO master_host='db1', master_user='usuario', master_password='contrasenya', master_log_file='mysql-bin.xxxxxx', master_log_pos=xxxxxx; Los valores de 'master_log_file' y 'master_log_pos' los sacamos del paso 4. 6. Aqui tenemos 2 opciones: manual o ignorando errores: 6.1.1. Manual. Vamos a parar (ya se hizo en el paso 3. pero por si las moscas), poner a 0 los errores y a arrancar de nuevo la replicacion: stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; 6.1.2. Comprobar: show slave status\G Si vemos que 'Last_Error' contiene un error: *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql-3.dev.jj.com Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000013 Read_Master_Log_Pos: 47129955 Relay_Log_File: mysql-relay-bin.000009 Relay_Log_Pos: 6130464 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '1121' for key 'PRIMARY'' on query. Default database: 'zabbix'. Query: 'insert into events (eventid,source,object,objectid,clock,ns,value,value_changed) values (1121,0,0,13467,1350144872,452367473,0,0)' Skip_Counter: 0 Exec_Master_Log_Pos: 6130319 Relay_Log_Space: 266234008 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '1121' for key 'PRIMARY'' on query. Default database: 'zabbix'. Query: 'insert into events (eventid,source,object,objectid,clock,ns,value,value_changed) values (1121,0,0,13467,1350144872,452367473,0,0)' 1 row in set (0.00 sec) Podemos volver a repetir los pasos 5 y 6, pero si hace mucho que tiempo que cashco, quiza es mejor saltar al metodo ignorar errores. Si en cambio vemos 'Last_Error' vacio y 'Seconds_Behind_Master' mas grande que 0 vamos bien: *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql-3.dev.jj.com Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000013 Read_Master_Log_Pos: 51066140 Relay_Log_File: mysql-relay-bin.000011 Relay_Log_Pos: 19243798 Relay_Master_Log_File: mysql-bin.000011 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 19243653 Relay_Log_Space: 195236846 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 99011 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) Debemos refrescar de vez en cuando (show master status\G), pero puede tardar incluso horas (hasta que 'Seconds_Behind_Master' este a 0) 6.2.1. Ignorar errores. Editamos: sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak sudo vim /etc/mysql/my.cnf Y anyadimos/editamos la siguiente linea: [mysqld] slave-skip-errors=1146,1062 Estos son los errores que nos aparecian una y otra vez en el paso 6.1.2 ('Last_Errno') 6.2.2. Reiniciamos mysql: sudo /etc/init.d/mysql restart 6.2.3. Paramos la replicacion: mysql -u root -p stop slave; 6.2.4. Ejecutar el paso 5. 6.2.5. Arrancar replicacion y comprobar: start slave; show slave status\G Seguir los consejos del paso 6.1.2. 7. (Opcional, solo si hemos llegado a 6.2.5.) Cuando 'Seconds_Behind_Master' este a 0 (ya tenemos las 2 bases de datos completamente sincronizadas) revertimos el archivo de configuracion: stop slave; sudo cp /etc/mysql/my.cnf.bak /etc/mysql/my.cnf sudo /etc/init.d/mysql restart mysql -u root -p start slave; show slave status\G 8. Cuando 'Seconds_Behind_Master' este a 0 (ya tenemos las 2 bases de datos completamente sincronizadas), arrancamos la sincronizacion en db1: mysql -u root -p start slave; show slave status\G 9. (Opcional) Arrancar de nuevo el envio de peticiones al servidor mysql cascado (db2) desde el balanceador de carga ===== Clonar usuarios ===== 1. Listar usuarios: mysql -u root -p use mysql; select Host,User from user; Salida: +--------------------+------------------+ | Host | User | +--------------------+------------------+ | %.dev.jj.com | replication | | %.dev.jj.com | usuario | | %.dev.jj.com | zabbix | | *.dev.jj.com | usuario | | 127.0.0.1 | root | | localhost | debian-sys-maint | | localhost | root | | localhost | zabbix | | localhost | zabbix_monitor | | mysql-1.dev.jj.com | root | +--------------------+------------------+ 10 rows in set (0.00 sec) 2. Mostrar los permisos del usuario que queremos clonar ('replication'@'%.dev.jj.com' en mi caso): show grants for "replication"@"%.dev.jj.com"; +-----------------------------------------------------------------------------------------------------------------------------------+ | Grants for replication@%.dev.jj.com | +-----------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.dev.jj.com' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxx' | +-----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 3. Copiar/pegar el mismo comando cambiando 'Host' y 'User'. Si se deja la contrasenya tal cual funcionara con la misma contrasenya, sino hay que usar la funcion MD5; GRANT REPLICATION SLAVE ON *.* TO 'nuevo_usuario'@'nuevo_host' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxx' ===== Obtener tablas mas pesadas ===== SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; ===== Especificar el juego de caracteres (charset) del servidor ===== http://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf8-in-my-cnf 1. Comprobar qué juego de caracteres tenemos: mysql -u root -p show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ En este caso la variable "**character_set_server**" no estaba a "utf8", y por mucho que la base de datos y la tabla lo estuviera, la aplicación web mostraba "P�gina" en lugar de "Página", aunque en las dos tablas, origen y destino, el campo estaba como "Página". 2. Editar: sudo cp /etc/mysql/my.conf /etc/mysql/my.conf.bak sudo vim /etc/mysql/my.conf Y añadir las siguientes lineas, justo debajo del bloque "[mysqld]": [mysqld] ... collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' character-set-server = utf8 3. Reiniciar mysql: sudo /etc/init.d/mysql restart 4. Repetir el paso 1. Ahora me muestra: +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ ===== Logging consultas ===== * Habilitar mysql -u root -p -e "set global general_log_file="/tmp/mysql.log"; mysql -u root -p -e "set global general_log = 'ON';" * Deshabilitar mysql -u root -p -e "set global general_log = 'OFF';" ===== Consultas utiles ===== * Listar el tamanyo de las tablas de una base de datos, las pesadas primero (ajustar variable "DB"): 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;" ===== Procedures ===== * Crear 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 /* 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( "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 ; * Listar SHOW PROCEDURE STATUS; SHOW FUNCTION STATUS; ===== Partitions ===== * Listar 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) */ 1 row in set (0.01 sec) ===== 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": 1. Editar archivo de configuración: 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 2. Y añadir en la sección "[mysqld]" la siguiente linea: ... [mysqld] sql-mode = "STRICT_TRANS_TABLES" 3. Reiniciar el servicio sudo service mysql restart 4. Verificarlo 4.1. Iniciar sesión como usuario "root" sudo mysql -u root 4.2. Ver el valor de la variable "sql_mode": SELECT @@SESSION.sql_mode; Valor esperado: +---------------------+ | @@SESSION.sql_mode | +---------------------+ | STRICT_TRANS_TABLES | +---------------------+ ===== Errores ===== ==== 1594 ==== Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. El log binario del maestro se ha corrompido, y la replicacion se ha parado. No hay una solicion magica, la idea es intentar saltar la parte corrupta, y saltar al siguiente punto en el cual el proceso de replicacion pueda continuar hasta sincronizarse totalmente con el maestro ('Seconds_Behind_Master' igual a 0). Asumo el siguiente escenario: | db1 | maestro | | db2 | esclavo | 1. (db2) Parar replicacion: mysql -u root -p stop slave; Comprobar: show slave status\G 'Slave_IO_State' tiene que estar vacio: Slave_IO_State: 2. (db2) Compruebo donde ha dejado de leer: show slave status\G Anotar los siguientes valores: Relay_Master_Log_File: mysql-bin.000069 Exec_Master_Log_Pos: 28899156 3. (db1) Vuelco el log binario MySQL a texto: sudo mysqlbinlog /var/log/mysql/mysql-bin.000069 > /tmp/mysql-bin.000069.txt Tras unos segundos deberia interrumpirse el proceso con un error: ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 177, event_type: 2 **MEJORAR** A partir de aqui el proceso es chapucero, necesita refinarse 4. (db1) Buscaremos en el archivo el valor de 'Exec_Master_Log_Pos' que obtuvimos en el paso 2, o el mas cercano, y el siguiente 'end_log_pos': cat /tmp/mysql-bin.000069.txt | grep "end_log_pos" | awk '{print $7}' | grep -A1 28899156 Si no encontramos nada, quitemos las decenas ('2889915' en lugar de '28899156'): cat /tmp/mysql-bin.000069.txt | grep "end_log_pos" | awk '{print $7}' | grep -A1 2889915 2 Posibilidades: 4.1.1. Encontremos algo asi: 28899156 28899231 4.1.2. (db2) Retomamos la replicacion en la posicion siguiente a la que falla: mysql -u root -p CHANGE master TO master_log_file='mysql-bin.000069',master_log_pos=28899231; 4.2.1. (db1) Hemos llegado al final de ese log binario. Nos aseguramos: cat /tmp/mysql-bin.000069.txt | grep "end_log_pos" | awk '{print $7}' | tail 28899590 28899771 28899952 28900133 28900318 28900502 28900690 28900878 28901060 28901237 Si el valor de 'Exec_Master_Log_Pos' cuando ejecutamos 'show slave status\G' es mayor que la ultima fila ('28901237' en este ejemplo) tenemos que saltar al siguiente log binario. 4.2.2. (db1) Para ello repito el paso 3. pero con el siguiente archivo ('mysql-bin.000070'): sudo mysqlbinlog /var/log/mysql/mysql-bin.000070 > /tmp/mysql-bin.000070.txt En este caso puede que no nos de error. Buena senyal, no esta corrupto. 4.2.3. (db1) Obtengo el primer valor de 'end_log_pos' en el archivo 'mysql-bin.000070': cat /tmp/mysql-bin.000070.txt | grep "end_log_pos" | awk '{print $7}' | head -n 1 106 4.2.4. (db2) Retomamos la replicacion en la posicion obtenida en el paso anterior: mysql -u root -p CHANGE master TO master_log_file='mysql-bin.000070',master_log_pos=106; 5. (db2) Arrancamos el esclavo: start slave; 6. (db2) Comprobamos: show slave status\G Opciones: 7.1.1. Todo va bien. El esclavo no encuentra ningun en el log binario del maestro: Seconds_Behind_Master: 109049 Ese numero ira decreciendo si ejecutamos de nuevo 'show slave status\G' 7.2.1. Error SQL no relacionado con corrupcion: Last_Error: Error 'Duplicate entry '1121' for key 'PRIMARY'' on query. Default database: 'zabbix'. Query: 'insert into events (eventid,source,object,objectid,clock,ns,value,value_changed) values (1121,0,0,13467,1350144872,452367473,0,0)' En este caso aconsejo intentar manualmente ignorar el error y volver a ver el status (db2): stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; Si es muy cansino, ignorar el tipo de error concreto ('Last_Errno') siguiendo los pasos 6.2.1. - 6.2.5. de [[http://wiki.legido.com/doku.php?id=informatica:linux:mysql:instalacion&s=mysql#reparar_replicacion_rota|reparar replicacion rota]] 7.3.1. Error SQL 1594. En este caso repetir los pasos 1 a 6 ==== Stopping MySQL database server: mysqld failed! ==== http://www.happysysadm.com/2011/01/stopping-mysql-database-server-mysqld.html Tras importar la tabla 'mysql' nos hemos cepillado las credenciales del usuario 'debian-sys-maint'. Solucion: sudo cat /etc/mysql/debian.cnf | grep password mysql -u root -p GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'xxxxx' WITH GRANT OPTION; flush privileges; exit sudo /etc/init.d/mysql restart ==== 145 - Table was marked as crashed and should be repaired ==== **/var/log/mysql/error.log** 131029 13:00:30 [ERROR] /usr/sbin/mysqld: Table './database_name/table_name' is marked as crashed and should be repaired Solucion: ssh -l user mysql.example.com sudo su myisamchk -r /var/lib/mysql/database_name/table_name.MYI Mas informacion: http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html http://dev.mysql.com/doc/refman/5.0/en/myisam-check.html 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 ==== mysql> select * from wallet; ERROR 1194 (HY000): Table 'wallet' is marked as crashed and should be repaired 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 | +------------------+-------+----------+-----------------------------------------------------+ 2 rows in set (0.01 sec) 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 | +------------------+--------+----------+--------------------------------------+ 2 rows in set (0.22 sec)