备份MySQL Amazon RDS [英] Backup MySQL Amazon RDS

问题描述

我正在尝试在AWS外部设置副本，并且master在AWS RDS上运行.而且我也不想在我的主人那里造成任何停机.因此，我设置了从属节点，现在我想备份当前在AWS上的数据库.

I am trying to setup Replica outside of AWS and master is running at AWS RDS. And I do not want any downtime at my master. So I setup my slave node and now I want to backup my current database which is at 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绑定时会出现错误

I tested it on my VM and it worked fine but when tying it with RDS it gives me error

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

是因为我没有超级用户特权，还是要解决此问题?请有人建议我.

Is it because i do not have super user privilege or how to I fix this problem? Please someone suggest me.

推荐答案

RDS甚至不允许主用户拥有SUPER特权，这是执行FLUSH TABLES WITH READ LOCK所必需的. (这是RDS的不幸限制).

RDS does not allow even the master user the SUPER privilege, and this is required in order to execute FLUSH TABLES WITH READ LOCK. (This is an unfortunate limitation of RDS).

--master-data选项将生成失败的语句，这当然是必须的，如果您希望能够了解开始备份的确切binlog坐标. FLUSH TABLES WITH READ LOCK获取所有表上的全局读取锁定，这允许mysqldump到START TRANSACTION WITH CONSISTENT SNAPSHOT(与--single-transaction相同)，然后允许SHOW MASTER STATUS获取二进制日志坐标，此后它释放全局读取锁定，因为它有一个事务，该事务将使可见数据保持与该日志位置一致的状态.

The failing statement is being generated by the --master-data option, which is, of course, necessary if you want to be able to learn the precise binlog coordinates where the backup begins. FLUSH TABLES WITH READ LOCK acquires a global read lock on all tables, which allows mysqldump to START TRANSACTION WITH CONSISTENT SNAPSHOT (as it does with --single-transaction) and then SHOW MASTER STATUS to obtain the binary log coordinates, after which it releases the global read lock because it has a transaction that will keep the visible data in a state consistent with that log position.

RDS通过拒绝SUPER特权并没有提供明显的解决方法来破坏此机制.

RDS breaks this mechanism by denying the SUPER privilege and providing no obvious workaround.

有一些骇人听闻的选项可以解决此问题，但没有一个可能特别吸引人:

There are some hacky options available to properly work around this, none of which may be particularly attractive:

• 在低流量期间执行备份.如果从开始备份到备份开始将数据写入输出文件或目标服务器之后，二进制日志坐标未更改(假设您使用的是--single-transaction)，那么它将起作用，因为您知道坐标未更改在进程运行时.

• do the backup during a period of low traffic. If the binlog coordinates have not changed between the time you start the backup and after the backup has begin writing data to the output file or destination server (assuming you used --single-transaction) then this will work because you know the coordinates didn't change while the process was running.

在开始备份之前立即观察主日志上binlog的位置，并将这些坐标与CHANGE MASTER TO一起使用.如果将主服务器的binlog_format设置为ROW，则这应该可以工作，尽管您可能不得不跳过一些初始错误，但随后不必再有任何错误.之所以可行，是因为基于行的复制具有确定性，如果尝试插入已经存在的内容或删除已经消失的内容，它将停止.克服错误后，您将位于真实的binlog坐标处，即一致快照实际开始的位置.

observe the binlog position on the master right before starting the backup, and use these coordinates with CHANGE MASTER TO. If your master's binlog_format is set to ROW then this should work, though you will likely have to skip past a few initial errors, but should not have to subsequently have any errors. This works because row-based replication is very deterministic and will stop if it tries to insert something that's already there or delete something that's already gone. Once past the errors, you will be at the true binlog coordinates where the consistent snapshot actually started.

与上一项相同，但是，在还原备份后，尝试使用mysqlbinlog --base64-output=decode-rows --verbose在获得的坐标处读取主站的binlog来确定正确的位置，然后检查新的从站以查看哪些事件必须在快照实际启动之前已经执行，并且必须使用以此方式确定的坐标CHANGE MASTER TO.

as in the previous item, but, after restoring the backup try to determine the correct position by using mysqlbinlog --base64-output=decode-rows --verbose to read the master's binlog at the coordinates you obtained, checking your new slave to see which of the events must have already been executed before the snapshot actually started, and using the coordinates determined this way to CHANGE MASTER TO.

使用外部进程在服务器上的每个表上获取读锁定，这将停止所有写操作；观察SHOW MASTER STATUS中的binlog位置已停止递增，开始备份并释放这些锁.

use an external process to obtain a read lock on each and every table on the server, which will stop all writes; observe that the binlog position from SHOW MASTER STATUS has stopped incrementing, start the backup, and release those locks.

如果您使用的不是最后一种方法，则必须进行表比较，以确保从服务器在运行后与主服务器相同，这一点尤其重要.如果您遇到后续的复制错误……那就不是.

If you use any of these approaches other than perhaps the last one, it's especially critical that you do table comparisons to be certain your slave is identical to the master once it is running. If you hit subsequent replication errors... then it wasn't.

也许最安全的选择-但也可能是最烦人的，因为似乎没有必要-始于创建RDS主文件的RDS只读副本.一旦启动并同步到主数据库，就可以通过执行RDS提供的存储过程CALL mysql.rds_stop_replication

Probably the safest option -- but also maybe the most annoying since it seems like it should not be necessary -- is to begin by creating an RDS read replica of your RDS master. Once it is up and synchronized to the master, you can stop replication on the RDS read replica by executing an RDS-provided stored procedure, CALL mysql.rds_stop_replication which was introduced in RDS 5.6.13 and 5.5.33 which doesn't require the SUPER privilege.

在RDS副本从属服务器停止的情况下，从RDS只读副本中获取mysqldump，该副本现在将在其上具有不变的数据集(作为一组特定的主坐标).将此备份还原到您的异地从站，然后将SHOW SLAVE STATUS Exec_Master_Log_Pos和Relay_Master_Log_File中的RDS读取副本的主坐标用作CHANGE MASTER TO坐标.

With the RDS replica slave stopped, take your mysqldump from the RDS read replica, which will now have an unchanging data set on it as of a specific set of master coordinates. Restore this backup to your off-site slave and then use the RDS read replica's master coordinates from SHOW SLAVE STATUS Exec_Master_Log_Pos and Relay_Master_Log_File as your CHANGE MASTER TO coordinates.

在Exec_Master_Log_Pos中在从属服务器上显示的值为下一个要处理的事务或事件，这正是您的新从属服务器开始在主服务器上读取的地方.

The value shown in Exec_Master_Log_Pos on a slave is the start of the next transaction or event to be processed, and that's exactly where your new slave needs to start reading on the master.

然后，一旦外部从站启动并运行，您就可以停用RDS只读副本.

Then you can decommission the RDS read replica once your external slave is up and running.

这篇关于备份MySQL Amazon RDS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！