Legido Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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 <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>

===== Mostrar BBDD =====
  mysql> show databases;

<code>
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| pingpong           |
+--------------------+
5 rows in set (0.00 sec)
</code>


===== 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:

<code>
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| drupal             |           6.78125000 |
| information_schema |           0.00781250 |
| mysql              |           0.62780762 |
| zabbix             |         300.93750000 |
+--------------------+----------------------+
</code>

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" |

<code>
+--------------------+----------------------+
| 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)
</code>

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
<code>
--routines
</code>
==== 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:

<code>
[mysqldump]
user=mysqldumpuser
password=secret
</code>

2. Restringir permisos:

  chmod 0600 ~/.my.cnf



===== Crear esquema: BBDD y usuario con mismo nombre =====

<code>
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';
</code>

===== Conectar desde PHP =====

<code>
<?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);
?>
</code>

===== 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]'

<code>
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
</code>

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]'

<code>
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
</code>

**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:

<code>
show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+
</code>

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:

<code>
show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
</code>

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:

<code>
show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.001178 | 81721737 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.06 sec)
</code>

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:

<code>
               Slave_IO_State: Waiting for master to send event
        Seconds_Behind_Master: 1576
</code>

==== 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:

<code>
*************************** 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)
</code>

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:

<code>
*************************** 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)
</code>

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:

<code>
+--------------------+------------------+
| 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)
</code>

2. Mostrar los permisos del usuario que queremos clonar ('replication'@'%.dev.jj.com' en mi caso):

<code>
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)
</code>

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 =====

<code>
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;
</code>

===== 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%';

<code>
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
</code>

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]":

<code>
[mysqld]
...
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
</code>

3. Reiniciar mysql:

  sudo /etc/init.d/mysql restart

4. Repetir el paso 1. Ahora me muestra:

<code>
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
</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
                /*
                   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 ;
</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) */
1 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":

1. 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>

2. Y añadir en la sección "[mysqld]" la siguiente linea:

<code>
...
[mysqld]
sql-mode = "STRICT_TRANS_TABLES"
</code>

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:

<code>
+---------------------+
| @@SESSION.sql_mode  |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
</code>


===== Errores =====

==== 1594 ====

<code>
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.
</code>

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:

<code>
cat /tmp/mysql-bin.000069.txt | grep "end_log_pos" | awk '{print $7}' | tail

28899590
28899771
28899952
28900133
28900318
28900502
28900690
28900878
28901060
28901237
</code>

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:

<code>	 
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
</code>

==== 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 ====

<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                                             |
+------------------+-------+----------+-----------------------------------------------------+
2 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                                   |
+------------------+--------+----------+--------------------------------------+
2 rows in set (0.22 sec)
</code>