mysql database base de datos
aptitude install mysql
aptitude install mysql-server
aptitude install mysql-query-browser
Poniendo la contraseña oculta:
#mysql -u <usuario> -p
Poniendola en texto plano:
#mysql -u <usuario> -p <password>
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>
mysql> show databases;
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | pingpong | +--------------------+ 5 rows in set (0.00 sec)
NOTA si quieres que los cambios tomen efecto inmediatamente, ejecuta al final:
flush privileges;
grant usage on *.* to 'usuario'@'%' identified by 'mi_clave';
grant usage on *.* to 'usuario'@'localhost' identified by 'mi_clave';
revoke all on *.* from 'usuario'@'%';
grant all on mi_bd.* to 'usuario'@'%';
revoke all on mi_bd.* from 'usuario'@'%';
GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to backup@localhost;
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
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:
time mysqldump --routines --opt --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
--routines
http://www.muck.net/59/mysql-minimum-permissions-for-backup-using-mysqldump
GRANT SELECT, LOCK TABLES ON *.* TO 'mysqldumpuser'@'localhost' IDENTIFIED BY 'secret';
1. Crear archivo:
vim ~/.my.cnf
Con el siguiente contenido:
[mysqldump] user=mysqldumpuser password=secret
2. Restringir permisos:
chmod 0600 ~/.my.cnf
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';
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'esb';
mysql_select_db($dbname);
$result = mysql_query("SELECT * FROM produccio WHERE servicio='consulta_tiempo' and fecha>'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 "<br />";
}
mysql_close($conn);
?>
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
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.
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 ...
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
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
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'
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;
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/ | +--------------------------+----------------------------+
mysql -u root -p -e "set global general_log_file="/tmp/mysql.log"; mysql -u root -p -e "set global general_log = 'ON';"
mysql -u root -p -e "set global general_log = 'OFF';"
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;"
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 ;
SHOW PROCEDURE STATUS; SHOW FUNCTION STATUS;
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)
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 | +---------------------+
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 reparar replicacion rota
7.3.1. Error SQL 1594. En este caso repetir los pasos 1 a 6
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
/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
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)