Crear usuario con los mínimos privilegios para hacer backups con mysqldump

Consejo: Para tablas transaccionales como InnoDB la opción “–single-transaction” es la recomendada. Esta opción realiza un comando SQL BEGIN antes de volcar los datos del servidor. Es útil sólo con tablas transaccionales como InnoDB y BDB, ya que vuelca el estado consistente de la base de datos cuando se ejecuta BEGIN sin bloquear ninguna aplicación.

NOTA: Cuando use esta opción, debe tener en cuenta que sólo las tablas InnoDB se vuelcan en un estado consistente. Por ejemplo, cualquier tabla MyISAM o HEAP volcadas mientras se usa esta opción todavía pueden cambiar de estado.

mysqldump --single-transaction

Creación del usuario.

CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'usuario'@'localhost';

Consejo: Para tablas MyISAM (predeterminadas) se deben bloquear todas las tablas de todas las bases de datos durante el volcado para conseguir una copia donde las tablas sean lógicamente consistente entre ellas.

mysqldump --lock-all-tables

Creación del usuario.

CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'usuario'@'localhost';
GRANT LOCK TABLES ON *.* TO 'usuario'@'localhost';

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'base_de_datos'

Clonar la base de datos “basededatos” (sin incluir la tabla “tabla1”) de serverMYSQL en el host local

ssh root@serverMYSQl 'mysqldump -u XXX -pXXX --compress basededatos --ignore-table=basededatos.tabla1' | mysql -u XXX -pXXX basededatos

mysqldump -u XXX -p basededatos --ignore-table=basededatos.tabla1 --ignore-basededatos.tabla2

Error

mysqldump: Got error: 1017: Can't find file: './stash/AO_A0B856_WEB_HOOK_LISTENER_AO.frm' (errno: 13) when using LOCK TABLES

Solución 1: Uso de la opción –single-transaction

mysqldump --single-transaction -uroot -pXXXXXX stash > stash.sql

Solución 2: Otorgar permisos para bloquear las tablas al usuario que utilice mysqldump.

$ mysql -u root -p
mysql> GRANT SELECT,LOCK TABLES ON DBNAME.* TO 'username'@'localhost';

Error

mysqldump: Couldn't execute 'show create table `AO_A0B856_WEB_HOOK_LISTENER_AO`': Can't find file: './stash/AO_A0B856_WEB_HOOK_LISTENER_AO.frm' (errno: 13) (1017)

Solución: Solucionar un posible problema de permisos / propietario.

ls -lt /var/lib/mysql/stash/ | grep -i root
-rw-rw---- 1 root  root   8888 Jul 14 16:19 AO_A0B856_WEB_HOOK_LISTENER_AO.frm
-rw-rw---- 1 root  root   8712 Jul 14 16:18 AO_FB71B4_SSH_PUBLIC_KEY.frm
 
chown mysql:mysql /var/lib/mysql/stash/*

Error

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `PLUGINDATA` at row: 6

Solución: Utilizar la opción –max_allowed_packet

mysqldump --max_allowed_packet=100M --single-transaction --routines -uXXX -pXXX confluence > confluence.sql

Error al importar un backup sql

ERROR 2006 (HY000) at line XXX: MySQL server has gone away

Solución: Puede deberse a la opción max_allowed_packet, la cual se debe configurar en el fichero /etc/my.conf

[mysqld]
...
max_allowed_packet =100M