rds迁移到本地服务器
MySQL replication is one of the simplest replication methods that we ever experienced. We have done so many MySQL migrations across multiple Cloud platforms like AWS, GCP, and Azure. In AWS the MySQL migration to RDS is straight forward. We can use our favorite backup tools such as mysqldump, mydumper, Percona Xtrabackup(only for Aurora), and mysqlpump. Or we can use AWS DMS service to migrate the data. In GCP, we have very limited options in terms of using backup tools. We have to use native mysqldump which is always criticized as a Single Thread process and then all the databases should be dumped into a single backup file. Unfortunately, this approach is a more time taken process while dealing with TeraBytes of data.
M ySQL复制是我们经历过的最简单的复制方法之一。 我们已经在多个云平台(例如AWS，GCP和Azure)上完成了许多MySQL迁移。 在AWS中，MySQL可以直接迁移到RDS。 我们可以使用我们喜欢的备份工具，例如mysqldump ， mydumper ， Percona Xtrabackup (仅适用于Aurora)和mysqlpump 。 或者，我们可以使用AWS DMS服务来迁移数据。 在GCP中，就使用备份工具而言，我们的选择非常有限。 我们必须使用总是被批评为单线程进程的本地mysqldump，然后应将所有数据库转储到单个备份文件中。 不幸的是，这种方法在处理TeraBytes数据时需要花费更多时间。
tl;drWe wrote this blog for migrating the AWS RDS MySQL to CloudSQL MySQL. CloudSQL is our target. And the steps and workarounds are valid for CloudSQL migrations only. To migrate MySQL on VM, then test thoroughly and do it.Stick with mysqldump tool — CloudSQL will only accept the database dump file that is taken from the native MySQL Dump tool.
坚持使用mysqldump工具 -CloudSQL将仅接受从本机MySQL Dump工具获取的数据库转储文件。
1. The CloudSQL will not support running the Change Master command on a running CloudSQL instance. So we can’t setup the replication manually after spinning up the CloudSQL.
1. CloudSQL将不支持在正在运行的CloudSQL实例上运行Change Master命令。 因此，在旋转CloudSQL之后，我们无法手动设置复制。
2. They use GTID based replication. The design of the CloudSQL replication is first restore the dump and run purge GTID from that dump file. Then continue the replication from the information that we provided during the migration setup.
2.他们使用基于GTID的复制。 CloudSQL复制的设计是首先还原转储，然后从该转储文件中清除GTID。 然后，根据我们在迁移设置过程中提供的信息继续复制。
Dump file with Definer — To restore an object that contains definer, then the restoration user must be a root user. But in CloudSQL(even on AWS RDS) we’ll not get the actual root user. So while executing create statements with definer will fail.
使用定义程序转储文件 -要还原包含定义程序的对象，则还原用户必须是root用户。 但是在CloudSQL中(甚至在AWS RDS上)，我们不会获得实际的root用户。 因此，在使用definer执行create语句时将失败。
Dump file with Triggers — MySQL will skip the definer on stored procedures. But not on triggers. So while restoring the dump will fail due to permission issue. So we should dump the triggers alone and restore it after prompted the CloudSQL instance as Standalone.
带触发器的转储文件 -MySQL将跳过存储过程中的定义器。 但不是触发因素。 因此，在还原转储时，由于权限问题而将失败。 因此，我们应该单独转储触发器，并在将CloudSQL实例提示为Standalone后将其还原。
User accounts — Generally we’ll not take a dump of the MySQL database(system database). So the users will not be migrated. We can’t even create the users manually after the replication established(we can do it via console, but that will grant the highest privilege).
用户帐户 -通常，我们不会转储MySQL数据库(系统数据库)。 因此，不会迁移用户。 建立复制后，我们甚至无法手动创建用户(我们可以通过控制台进行操作，但这将授予最高权限)。
More time — This is the worst thing in CloudSQL migration. While migrating databases with a few terabytes using mysqldump will take many hours and the restoration also will take more time.
更多时间 -这是CloudSQL迁移中最糟糕的事情。 尽管使用mysqldump迁移数TB的数据库将花费很多时间，并且还原也将花费更多时间。
After analyzing all of these factors, we came up with some optimization on the CloudSQL replication process while dealing with a huge amount of data.
在分析了所有这些因素之后，我们在处理大量数据的同时对CloudSQL复制过程进行了一些优化。
One thing is very clear, we can’t do anything on the Restoration process which is completely taken care of by GCP. So we started researching one other challenge. And finally, we arrived to use one the MySQL’s backup utility called MySQLPUMP a multi-threaded backup tool with more control on what to backup and what objects we can skip.
一件事很清楚，在GCP完全负责的恢复过程中，我们无法做任何事情。 因此，我们开始研究另一个挑战。 最后，我们来到了一个名为MySQLPUMPMySQL备份实用程序， 它是一种多线程备份工具，可以更好地控制要备份的内容和可以跳过的对象。
Multi-Thread — Due to its multi-thread process, we can dump multiple tables at the same time. This is a huge time saver for us.
多线程 —由于其多线程过程，我们可以同时转储多个表。 对于我们来说，这可以节省大量时间。
GITD — This tool will not add binlog file name and position, but it’ll add the GTID that needs to be purged. CloudSQL will only support GTID based replication, so we need to worry about binlog details.
GITD —该工具不会添加binlog文件的名称和位置，但是会添加需要清除的GTID。 CloudSQL仅支持基于GTID的复制，因此我们需要担心binlog详细信息。
More Objects — As like native mysqldump, this will take the dump of all the database objects like tables, views, functions, stored procedures, triggers and events as well.
更多对象 -像本机mysqldump一样，这也将转储所有数据库对象，如表，视图，函数，存储过程，触发器和事件。
Users — This is one of the unique features. We can take the dump of all of our MySQL users. We have control over what users can we included or excluded.
用户 -这是独特功能之一。 我们可以处理所有MySQL用户的转储。 我们可以控制可以包含或排除哪些用户。
Object filters — Sometime we may have some backup tables or historical tables, or some unused stored procedures and events. This MySQLPUMP will have options to filter the objects. If we want to skip some backup tables that will never have writes, then we can use exclude-tables flags. This will speed up the whole process.
对象过滤器 -有时我们可能有一些备份表或历史表，或一些未使用的存储过程和事件。 该MySQLPUMP将具有用于过滤对象的选项。 如果我们想跳过一些永远不会有写操作的备份表，那么我们可以使用排除表 标志。 这将加快整个过程。
Yes, mysqlpump also has some limitations. We have to know them before using it for a production migration.
是的，mysqlpump也有一些限制。 在将其用于生产迁移之前，我们必须了解他们。
Are you kidding me? — [We read your mind 😄]Of course with AWS RDS, we can’t get a consistent backup with MySQLPUMP. Generally, to make a consistent backup for InnoDB tables, we use --single-transaction flag. But this will execute Flush Tables With READ LOCK on the Database server. In RDS, the master user doesn’t have permission to run this command. This is not documented.
你在跟我开玩笑吗？ — [我们已读懂您的想法😄]当然，对于AWS RDS，我们无法使用MySQLPUMP获得一致的备份。 通常，为了对InnoDB表进行一致的备份，我们使用--single-transaction标志。 但这将在数据库服务器上执行Flush Tables With READ LOCK 。 在RDS中，主用户无权运行此命令。 这没有记录。
mysql> Flush Tables With READ LOCK;ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)This mysqlpump has a flag called --skip-definer but this will not eliminate the definers on the triggers. So you’ll get the definer syntax in your dump file.
这个mysqlpump有一个名为--skip-definer definer的标志，但这不会消除触发器上的定义器。 因此，您将在转储文件中获得定义程序语法。
Still, it’ll help you to take the dump of all of your users. But if the host part is mentioned as per the AWS network and you have a different network range on GCP, then these users will not work. If you have % as the host, then its fine.
尽管如此，它将帮助您进行所有用户的转储。 但是，如果根据AWS网络提到主机部分，并且您在GCP上具有不同的网络范围，那么这些用户将无法使用。 如果您以%作为主机，则可以。
Perform the dump on the read replica will be the workaround for the consistency issue. Taking a dump on the Master node is not at all recommended. So create a read replica for your MYSQL instance and then stop the replication process. So there will not be any write activity, then we can start taking the dump without the --single-transaction flag.
在只读副本上执行转储将是解决一致性问题的方法。 完全不建议在主节点上进行转储。 因此，为您的MYSQL实例创建一个只读副本，然后停止复制过程。 这样就不会有任何写活动，然后我们就可以开始转储而没有--single-transaction标志了。
Unfortunately, we don’t have any MySQL tricks to skip the definer syntax from the dump file. So we decided to use sed command during the dump process and remove the definer.
不幸的是，我们没有MySQL技巧可以从转储文件中跳过定义器语法。 因此，我们决定在转储过程中使用sed命令并删除定义器。
For user accounts, We can’t do anything. But our suggestion is, once you promoted the instance, do the ALTER USER command and change those host names. Or don’t backup the users. Manually create them after promoting the CloudSQL.
对于用户帐户，我们无能为力。 但是我们的建议是，一旦提升了实例，请执行ALTER USER命令并更改那些主机名。 或不备份用户。 在升级CloudSQL之后手动创建它们。
Lets jump into a demo and use mysqlpump approach to migrate the database from AWS RDS to CloudSQL MySQL. But make sure you have the following prerequisites.
让我们进入一个演示并使用mysqlpump方法将数据库从AWS RDS迁移到CloudSQL MySQL。 但是，请确保您具有以下先决条件。
Enable GTID on RDS(MySQL 5.7.23 and later versions will support this, MySQL 8.0 already enabled with ON_PREMISSIVE mode)
在RDS上启用GTID (MySQL 5.7.23和更高版本将支持此功能，MySQL 8.0已通过ON_PREMISSIVE模式启用)
A proxy server. CloudSQL will not talk to AWS RDS endpoints. So create a port forwarding on an EC2 instance or GCP VM. It should in a public subnet. (At the moment, CloudSQL external replication will not support private communication). 代理服务器。 CloudSQL将不会与AWS RDS终端节点通信。 因此，请在EC2实例或GCP VM上创建端口转发。 它应该在公共子网中。 (目前，CloudSQL外部复制将不支持私有通信)。 Make sure the network connectivity between RDS to Proxy and Proxy to CloudSQL. 确保RDS到代理和Proxy到CloudSQL之间的网络连接。 A MySQL user with Replication permission. 具有复制权限MySQL用户。Make the binlog retention hours to minimum 24hrs(based on your database size feel free to increase it)
使binlog保留时间最少为24小时(根据您的数据库大小随意增加)
Install MySQL Client 5.7+ on the VM where you are going to run the dump command. 在要运行dump命令的VM上安装MySQL Client 5.7+。Also, we recommend the following this to make the dump process faster.
另外，我们建议遵循以下步骤以加快转储过程。
Increase the Read Replica instance size. 增加只读副本实例大小。Increase the --max-allowed-packet size, if possible tune some InnoDB IO parameters. (But take your DBA’s suggestion while modifying these variables)
增加--max-allowed-packet大小，如果可能的话，调整一些InnoDB IO参数。 (但是在修改这些变量时请考虑您的DBA的建议)
Take the dump from an EC2 instance that is launched on the same VPC where RDS is launched and use the same availability zone. 从启动RDS的同一VPC上启动的EC2实例中获取转储，并使用相同的可用区。 Attach SSD or PIOPS disk for the dump. 附加SSD或PIOPS磁盘进行转储。Run this following command on the RDS Read Replica.
在RDS只读副本上运行以下命令。
mysqlpump -h RDS-ENDPOINT \ -u RDS-MASTER-USER \ -p \ --default-parallelism=10 \ --databases db1 db2 db3 \ --events \ --triggers \ --routines \ --users \ --exclude-users=%sys,root,rdsadmin \ --skip-definer \ --default-character-set=utf8mb4 \ --set-gtid-purged=ON \ --max-allowed-packet=700M | sed -e 's/DEFINER=[^ ]*`//'> dump-file.sql.gz--default-parallelism — The number of threads to take the dump.
--default-parallelism —进行转储的线程数。
--exclude-user — While creating the CloudSQL it’ll create a default user with the name root and the host as % So we need to exclude this user from the dump and some default system users.
--exclude-user —创建CloudSQL时，它将创建一个默认用户，名称为root ，主机为%因此我们需要从转储中排除该用户和某些默认系统用户。
--skip-definer — To skip the definer
--skip-definer —跳过定义器
--max-allowed-packet — This is an optional flag.
--max-allowed-packet这是一个可选标志。
sed — If something is not skipped by the --skip-definer flag, this command will remove them all.
sed —如果--skip-definer标志未跳过某些内容，则此命令将全部删除它们。
If you took the dump on EC2 then you have multiple ways to upload it to GCS.
如果您在EC2上进行了转储，则可以通过多种方式将其上载到GCS。
Configure Gcloud CLI on EC2 在EC2上配置Gcloud CLI Upload the dump to S3 and use GCP transfer service to copy it to GCS(recommended) 将转储上传到S3，并使用GCP传输服务将其复制到GCS(推荐)Expand the Advanced Options.
展开高级选项。
1. If you have any jump host to access the CloudSQL, you can whitelist it’s IP.(you can do later also)
1.如果您有任何跳转主机来访问CloudSQL，则可以将其IP列入白名单。(您也可以稍后执行)
2. 📢Click on the Add item button to add the database flags and this is
2.📢单击添加项目按钮以添加数据库标志，这是
important. Enable log_bin_trust_function_creators and event_scheduler flags.
重要的 。 启用log_bin_trust_function_creators和event_scheduler标志。
3. Tune some InnoDB parameters for a better writing process. (Get your DBA’s help)
3.调整一些InnoDB参数以改善编写过程。 (获取您的DBA帮助)
Click on the create button and wait for a few mins. Then it’ll show you an outgoing IP address. This is IP that communicates with your ProxyVM for the replication. So whitelist this IP to access your proxy VM on the MySQL port. 单击创建按钮，然后等待几分钟。 然后它将为您显示一个传出IP地址。 这是与您的ProxyVM通信以进行复制的IP。 因此，将此IP列入白名单即可在MySQL端口上访问您的代理VM。That's it. Wait for some time, if your database is really huge in then you have to wait for a day or more. Because restoration will restore the tables one by one. Once its done, then you can reset the root user’s password on the CloudSQL console and login with that user to check the replication status.
而已。 等待一段时间，如果您的数据库确实很大，则必须等待一天或更长时间。 因为还原将一张一张地还原表。 完成后，您可以在CloudSQL控制台上重置root用户的密码，然后以该用户身份登录以检查复制状态。
Replication status 复制状态 Migrated Users 迁移的用户 Triggers are restored 触发器已恢复This is just an alternate approach to migrate the MySQL database to CloudSQL with MySQLPUMP. We don’t how much time it’ll save from the overall migration, but definitely it’ll save your from single thread backup, no need to dump and restore the triggers and users after promoting it as the master. If you already have done a large migration with MySQLPUMP or you are going to migrate after reading this blog, we are happy to hear your story, please comment on your experience.
这只是使用MySQLPUMP将MySQL数据库迁移到CloudSQL的另一种方法。 我们没有从整体迁移中节省多少时间，但是绝对可以从单线程备份中节省您的时间，在将其升级为主服务器后，无需转储并还原触发器和用户。 如果您已经使用MySQLPUMP进行了大型迁移，或者打算在阅读此博客后进行迁移，我们很高兴听到您的故事，请评论您的经历。
GTID on AWS RDS
AWS RDS上的GTID
GCP CloudSQL external replication
GCP CloudSQL外部复制
MYSQLPUMP — A technical overview from MyDBOPS and Percona
MYSQLPUMP — MyDBOPS和Percona的技术概述
MySQL Pump — Documentation
MySQL Pump —文档
翻译自: https://medium.com/searce/optimize-your-rds-mysql-to-gcp-cloudsql-migration-f39cb4b61161
rds迁移到本地服务器
相关资源：四史答题软件安装包exe