备份MySQL Amazon RDS

Question

备份MySQL Amazon RDS

21

我正在尝试在AWS之外设置副本，主节点正在运行AWS RDS。我不希望我的主节点有任何停机时间。因此，我设置了我的从节点，现在我想备份我的当前数据库，该数据库位于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

我在我的VM上进行了测试，一切正常，但是当我与RDS连接时，它给我报错。

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

这是因为我没有超级用户权限吗？还是有什么其他问题？请有经验的人给我建议。

- JavaGuy

看起来是权限问题。很可能根用户没有从您发送请求的IP授予权限。您可以尝试使用“SHOW GRANTS FOR 'root'@'%';”进行确认。还应该假设您已经删除了文章的密码。 - Evan Cordeiro

谢谢Evan。这些是我对root用户拥有的权限：

SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、RELOAD、PROCESS、REFERENCES、INDEX、ALTER、SHOW DATABASES、CREATE TEMPORARY TABLES、LOCK TABLES、EXECUTE、REPLICATION SLAVE、REPLICATION CLIENT、CREATE VIEW、SHOW VIEW、CREATE ROUTINE、ALTER ROUTINE、CREATE USER、EVENT

，我缺少哪些权限？有什么想法吗？ - JavaGuy

这些权限是针对'root'@'%'的，对吗？MySQL授权是基于'user'和'ip'的。实际上，一个root拥有GRANT ALL PRIVILEGES ON . TO 'root'@'%'可能是非默认的（我相信）。 - Evan Cordeiro

1

我无法更改Amazon RDS的权限，那么有没有其他解决这个问题的方法？ - JavaGuy

您确定您的RDS实例的主用户真的是“root”吗？您可以在AWS管理控制台中进行检查。 - David Levesque

我在设置RDS实例时创建了“root”用户，并获得了亚马逊提供的所有默认特权。但是，我卡在了默认权限上。我相信亚马逊不提供超级用户权限。你认为在RDS中可能有什么方法可以解决这个权限问题吗，David？ - JavaGuy

7个回答

7

感谢Michael，我认为最正确的解决方案并且是AWS推荐的方法是使用读复制作为源进行复制，如此处所述。

拥有RDS主服务器、RDS只读副本和一个准备好的MySQL实例后，获取外部从站的步骤如下：

1. 在主服务器上增加二进制日志保留时间。 mysql> CALL mysql.rds_set_configuration('binlog retention hours', 12); 2. 在只读副本上停止复制以避免备份期间的更改。 mysql> CALL mysql.rds_stop_replication; 3. 在只读副本上注释二进制日志状态（Master_Log_File和Read_Master_Log_Pos） mysql> SHOW SLAVE STATUS; 4. 在服务器实例上备份并导入它（使用Max建议的mydumper可以加快此过程）。 mysqldump -h RDS_READ_REPLICA_IP -u root -p YOUR_DATABASE > backup.sql mysql -u root -p YOUR_DATABASE < backup.sql 5. 在服务器实例上将其设置为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;

将MASTER_LOG_FILE和MASTER_LOG_POS替换为之前保存的Master_Log_File Read_Master_Log_Pos的值，还需要在RDS主服务器中有一个用户用于从服务器复制。 mysql> START SLAVE; 6. 在服务器实例上检查是否成功复制。 mysql> SHOW SLAVE STATUS; 7. 在RDS只读副本上恢复复制。 mysql> CALL mysql.rds_start_replication;

- Chemary

在第5步，为什么要使用 MASTER 实例而不是 SLAVE？此外，这是否可以通过 RDS → RDS 完成（我想使用 mysqldump 方法，因为我可以减小 ibdata 的大小）？ - Chris Muench

4

如果要获取RDS二进制日志的位置，可以使用mydumper和--lock-all-tables选项，它将使用LOCK TABLES ... READ仅获取日志坐标，然后释放它而不是使用FTWRL。

- Max Bube

1

Michael的回答非常有帮助，重点在于主要难点：你无法在RDS上授予所需的SUPER权限，因此无法使用--master-data标志，这会使事情变得更加容易。

我读到可能可以通过API创建或修改数据库参数组来解决此问题，但我认为使用RDS过程是更好的选择。

多层复制方法效果很好，并且可以包括RDS / VPC之外的层次，因此可以使用此方法从“Classic”EC2复制到VPC。

许多必要的功能仅在MySQL 5.5和5.6的后续版本中才有，并且我强烈建议您在复制堆栈中涉及的所有DB上运行相同的版本，因此您可能需要在所有这些操作之前升级旧的DB，这意味着还需要进行更多的乏味的复制等工作。

- Ben Walsh

0

我曾经遇到过类似的问题，一个快速的解决方法是：

创建一个EBS卷以获得额外的空间或扩展EC2上当前的EBS卷（如果您有额外的空间，则可以使用它）。
使用mysqldump命令而不带--master-data或--flush-data指令来生成完整（FULL）的数据库备份。

mysqldump -h hostname --routines -uadmin -p12344 test_db > filename.sql

admin是数据库名称，12344是密码

以上是备份单个数据库的方法，如果需要备份所有数据库，则需指定--all-databases并提及数据库名称。

创建一个Cron命令，每天运行一次，将自动生成备份文件。

请注意，如果您的数据库大小很大，这将产生额外的费用，因为它会创建一个完整的数据库备份。

希望这能帮到您。

- Qayed Johar

0

你需要：

1. 在 AWS 上创建一个读取副本。 2. 确保该实例正在追赶主服务器。 3. 停止复制并通过获取“log_file”和“log_position”参数来完成。

show slave status \G

4- 将数据库转储并使用步骤3中记录的参数在您自己的服务器上开始复制。

5- 启动从服务器。

详细说明在此处

- Eng7

-3

要么情况已经改变，自从@Michael - sqlbot的回应，要么这里存在误解（可能是我的问题）。

您可以使用COPY将csv文件导入rds，至少在postgres版本中是这样，您只需要使用FROM STDIN而不是直接命名文件，

这意味着您最终会像传递以下类似的内容：

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

- Ali

1

我认为您可能误解了问题。这是关于RDS for MySQL的，需要在一个外部的MySQL服务器上设置一个实时读取副本。设置副本需要备份，在单个、精确的时间点对服务器进行快照，将该快照恢复到另一台机器上，然后指示第二台服务器从主服务器开始执行复制事件，从备份被拍摄的精确点开始。 RDS的限制禁用了在该过程开始时获取简短全局锁的能力。 - Michael - sqlbot

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Michael - sqlbot · Accepted Answer

RDS 不允许即使是主用户也没有超级权限，而这种权限是执行"FLUSH TABLES WITH READ LOCK"所必需的。(这是 RDS 的一个不幸限制)

"--master-data"选项生成了失败的语句，如果你想知道备份开始时的精确 binlog 坐标，这个选项当然是必须的。"FLUSH TABLES WITH READ LOCK"会对所有表获取全局只读锁，这允许mysqldump使用"START TRANSACTION WITH CONSISTENT SNAPSHOT" (就像使用"--single-transaction"一样) 然后使用 "SHOW MASTER STATUS" 来获取二进制日志坐标, 然后释放全局锁，因为它有一个事务将保持可见数据与该日志位置的状态一致。

RDS通过拒绝超级权限并提供没有明显解决方法来破坏了这个机制。

有一些hacky选项可用于正确解决此问题，但它们可能都不是特别吸引人:

在低流量期间进行备份。如果在开始备份之前和备份开始写入输出文件或目标服务器(假设你使用了"--single-transaction")之后的时间内binlog坐标没有更改，则此方法可行，因为你知道在进程运行时坐标不会改变。
在备份之前观察主服务器上的binlog位置，并使用这些坐标进行"CHANGE MASTER TO"。如果你的主服务器的"binlog_format"设置为"ROW"，那么这应该可以工作，尽管你可能需要跳过一些初始错误，但不应该随后出现任何错误。这是行级复制非常确定性的结果，如果它试图插入已经存在的内容或删除已经消失的内容，它将停止。一旦通过了这些错误，您将得到一个真正的 binlog 坐标，即始终与一致快照开始时的坐标。

与上一项类似，但在恢复备份后，尝试使用mysqlbinlog --base64-output=decode-rows --verbose读取主服务器的二进制日志来确定正确的位置，并检查新的从服务器以确定哪些事件必须在快照实际开始之前已经执行，然后使用这种方式确定的坐标来进行CHANGE MASTER TO。

使用外部进程在服务器上获得对每个表的读锁，这将停止所有写入；观察来自SHOW MASTER STATUS的二进制日志位置是否停止增加，然后开始备份，并释放那些锁。

如果你使用除最后一种方法之外的任何方法，特别是要进行表比较，以确保从服务器在运行时与主服务器完全相同，如果遇到后续的复制错误... 那就不是了。

可能是最安全的选项-但也可能是最烦人的 因为它似乎不应该是必要的-是先创建一个RDS读副本作为RDS主库。一旦它与主服务器同步，您可以通过执行RDS提供的存储过程CALL mysql.rds_stop_replication（在RDS 5.6.13和5.5.33中引入）停止RDS读副本上的复制，这不需要SUPER权限。

将RDS读副本从服务器停止后，从RDS读副本获取您的mysqldump，它现在具有特定一组主坐标的不变数据集。将此备份还原到您的离线从服务器，然后使用RDS读副本的主坐标-SHOW SLAVE STATUS Exec_Master_Log_Pos和Relay_Master_Log_File-作为您的CHANGE MASTER TO坐标。

在从服务器上显示的 Exec_Master_Log_Pos 值是下一个要处理的事务或事件的起始点，这正是您的新从服务器需要在主服务器上开始阅读的地方。

然后，一旦您的外部从服务器正常运行，就可以停用 RDS 只读副本。