connect mysql amazon ec2

Backup MySQL Amazon RDS

Question

Estoy intentando configurar una réplica fuera de AWS y el maestro se ejecuta en AWS RDS. Y no quiero ningún tiempo de inactividad en mi maestro. Así que configuro mi nodo esclavo y ahora quiero hacer una copia de seguridad de mi base de datos actual que está en AWS.

mysqldump -h RDS ENDPOINT -u root -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 --all-databases > /root/dump.sql

Lo probé en mi VM y funcionó bien, pero al vincularlo con RDS me da un error

mysqldump: Couldn''t execute ''FLUSH TABLES WITH READ LOCK'': Access denied for user ''root''@''%'' (using password: YES) (1045)

¿Es porque no tengo privilegios de superusuario o cómo puedo solucionar este problema? Por favor que alguien me sugiera.

Answer 1

Gracias Michael, creo que la solución más correcta y recomendada por AWS es hacer la replicación utilizando una réplica de lectura como fuente, como se explica here .

Teniendo un maestro RDS, una réplica de lectura RDS y una instancia con MySQL listo, los pasos para obtener un esclavo externo son:

1. En el maestro, aumentar el período de retención binlog.

mysql> CALL mysql.rds_set_configuration(''binlog retention hours'', 12);

2. En la réplica de lectura, detenga la replicación para evitar cambios durante la copia de seguridad.

mysql> CALL mysql.rds_stop_replication;

3. En la réplica de lectura, anote el estado de binlog (Master_Log_File y Read_Master_Log_Pos)

mysql> SHOW SLAVE STATUS;

4. En la instancia del servidor, haga una copia de seguridad e impórtela (el uso de mydumper como sugiere Max puede acelerar el proceso).

mysqldump -h RDS_READ_REPLICA_IP -u root -p YOUR_DATABASE > backup.sql

mysql -u root -p YOUR_DATABASE < backup.sql

5. En la instancia del servidor, configúrelo como esclavo del maestro RDS.

mysql> CHANGE MASTER TO MASTER_HOST=''RDS_MASTER_IP'',MASTER_USER=''myrepladmin'', MASTER_PASSWORD=''pass'', MASTER_LOG_FILE=''mysql-bin-changelog.313534'', MASTER_LOG_POS=1097;

Reemplace MASTER_LOG_FILE y MASTER_LOG_POS a los valores de Master_Log_File Read_Master_Log_Pos que guardó anteriormente, también necesita un usuario en el maestro RDS para que lo utilice la replicación de esclavos.

mysql> START SLAVE;

6. En la instancia del servidor, compruebe si la replicación fue exitosa.

mysql> SHOW SLAVE STATUS;

7. En RDS leer réplica reanudar replicación. mysql> CALL mysql.rds_start_replication;

Answer 2

La respuesta de Michael es extremadamente útil y se enfoca en el punto de bloqueo principal: simplemente no puede OTORGAR el privilegio SUPER requerido en RDS, y por lo tanto no puede usar la marca --master-data que haría las cosas mucho más fáciles.

Leí que es posible solucionar esto creando o modificando un Grupo de parámetros de base de datos a través de la API, pero creo que usar los procedimientos RDS es una mejor opción.

Sin embargo, el enfoque de replicación de múltiples niveles funciona bien y puede incluir niveles fuera de RDS / VPC, por lo que es posible replicar desde EC2 "clásico" a VPC utilizando este método.

Gran parte de la funcionalidad necesaria está solo en las versiones posteriores de MySQL 5.5 y 5.6, y le recomiendo encarecidamente que ejecute la misma versión en todas las bases de datos involucradas en la pila de replicación, por lo que es posible que tenga que hacer una actualización de su antigua base de datos antes de todo. de esto, lo que significa aún más tedio y replicación y así sucesivamente.

Answer 3

O las cosas han cambiado, desde la respuesta de @Michael - sqlbot, o hay un malentendido aquí (podría ser de mi parte),

Puede usar COPY para importar un archivo csv en rds, al menos en la versión postgres, solo necesita usar FROM STDIN lugar de nombrar directamente el archivo,
lo que significa que terminas canalizando cosas como:

cat data.csv | psql postgresql://server:5432/mydb -U user -c "COPY /"mytable/" FROM STDIN DELIMITER '','' "

Answer 4

RDS no le permite al usuario maestro el privilegio SUPER , y esto es necesario para ejecutar FLUSH TABLES WITH READ LOCK . (Esta es una limitación desafortunada de RDS).

La declaración de --master-data genera mediante la opción --master-data , que, por supuesto, es necesaria si desea poder conocer las coordenadas precisas de binlog donde comienza la copia de seguridad. FLUSH TABLES WITH READ LOCK adquiere un bloqueo de lectura global en todas las tablas, lo que permite a mysqldump START TRANSACTION WITH CONSISTENT SNAPSHOT UNA START TRANSACTION WITH CONSISTENT SNAPSHOT FOTOGRAMA START TRANSACTION WITH CONSISTENT SNAPSHOT (como ocurre con --single-transaction ) y luego SHOW MASTER STATUS para obtener las coordenadas del registro binario, después de lo cual se libera el bloqueo de lectura global porque tiene una transacción que mantendrá los datos visibles en un estado coherente con esa posición de registro.

RDS rompe este mecanismo al negar el privilegio SUPER y no proporciona una solución obvia.

Hay algunas opciones de pirateo disponibles para solucionar esto, ninguna de las cuales puede ser particularmente atractiva:

• hacer la copia de seguridad durante un período de poco tráfico. Si las coordenadas de binlog no han cambiado entre el momento en que inicia la copia de seguridad y después de que la copia de seguridad comience a escribir datos en el archivo de salida o en el servidor de destino (suponiendo que utilizó --single-transaction ), esto funcionará porque sabe que las coordenadas no lo hicieron. t cambiar mientras el proceso se estaba ejecutando.

• observe la posición del binlog en el lado derecho del maestro antes de iniciar la copia de seguridad, y use estas coordenadas con CHANGE MASTER TO . Si el binlog_format su maestro está configurado en ROW , esto debería funcionar, aunque es probable que tenga que omitir algunos errores iniciales, pero no debería tener errores posteriores. Esto funciona porque la replicación basada en filas es muy determinista y se detendrá si intenta insertar algo que ya está allí o eliminar algo que ya se ha ido. Una vez que haya pasado los errores, estará en las verdaderas coordenadas de binlog, donde realmente comenzó la instantánea consistente.

• como en el elemento anterior, pero, después de restaurar la copia de seguridad, intente determinar la posición correcta utilizando mysqlbinlog --base64-output=decode-rows --verbose para leer el binlog del maestro en las coordenadas que obtuvo, verificando su nuevo esclavo para ver cuál de los eventos ya debe haberse ejecutado antes de que la instantánea se iniciara realmente, y usando las coordenadas determinadas de esta manera para CHANGE MASTER TO

• use un proceso externo para obtener un bloqueo de lectura en todas y cada una de las tablas del servidor, que detendrá todas las escrituras; observe que la posición binlog de SHOW MASTER STATUS ha dejado de aumentar, inicie la copia de seguridad y libere esos bloqueos.

Si utiliza alguno de estos enfoques, aparte de quizás el último, es especialmente importante que realice comparaciones de tablas para asegurarse de que su esclavo es idéntico al maestro una vez que se está ejecutando. Si golpeas errores de replicación subsecuentes ... entonces no fue así.

Probablemente la opción más segura, pero también la más molesta, ya que parece que no debería ser necesaria, es comenzar por crear una réplica de lectura RDS de su maestro RDS. Una vez que está activo y sincronizado con el maestro, puede detener la replicación en la réplica de lectura RDS ejecutando un procedimiento almacenado provisto por RDS, CALL mysql.rds_stop_replication que se introdujo en RDS 5.6.13 y 5.5.33, que no requiere la SUPER privilegio.

Con el esclavo de réplica RDS detenido, saque su mysqldump de la réplica de lectura RDS, que ahora tendrá un conjunto de datos invariable en un conjunto específico de coordenadas maestras. Restaure esta copia de seguridad en su esclavo Exec_Master_Log_Pos y luego use las coordenadas maestras de la réplica de lectura RDS de SHOW SLAVE STATUS Exec_Master_Log_Pos y Relay_Master_Log_File como sus coordenadas CHANGE MASTER TO .

El valor que se muestra en Exec_Master_Log_Pos en un esclavo es el inicio de la siguiente transacción o evento que se procesará , y ahí es exactamente donde su nuevo esclavo necesita comenzar a leer en el maestro.

Luego puede retirar la réplica de lectura de RDS una vez que su esclavo externo esté funcionando.

Answer 5

para la posición de RDS binlog puede usar mydumper con --lock-all-tables , usará LOCK TABLES ... READ para obtener las coordenadas de binlog y luego liberarlas en lugar de FTWRL .