如何配置 Azure Database for MySQL 的数据传入复制How to configure Azure Database for MySQL Data-in Replication

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

本文介绍如何通过配置源服务器和副本服务器在 Azure Database for MySQL 中设置数据传入复制This article describes how to set up Data-in Replication in Azure Database for MySQL by configuring the source and replica servers. 本文假设读者在 MySQL 服务器和数据库方面有一定的经验。This article assumes that you have some prior experience with MySQL servers and databases.

备注

本文包含对术语“从属”的引用,这是 Microsoft 不再使用的术语。This article contains references to the term slave, a term that Microsoft no longer uses. 在从软件中删除该术语后,我们会将其从本文中删除。When the term is removed from the software, we'll remove it from this article.

若要在 Azure Database for MySQL 服务中创建副本,数据传入复制需同步本地 MySQL 源服务器、虚拟机 (VM) 或云数据库服务中的数据。To create a replica in the Azure Database for MySQL service, Data-in Replication synchronizes data from a source MySQL server on-premises, in virtual machines (VMs), or in cloud database services. 数据传入复制以基于二进制日志 (binlog) 文件位置或基于 GTID 的从本机到 MySQL 的复制为基础。Data-in Replication is based on the binary log (binlog) file position-based or GTID-based replication native to MySQL. 若要了解有关 binlog 复制的详细信息,请参阅 MySQL binlog 复制概述To learn more about binlog replication, see the MySQL binlog replication overview.

在执行本文中的步骤之前,请查看数据传入复制的限制和要求Review the limitations and requirements of Data-in replication before performing the steps in this article.

创建要用作副本的 Azure Database for MySQL 单一服务器实例Create an Azure Database for MySQL Single Server instance to use as a replica

  1. 创建一个新的 Azure Database for MySQL 单一服务器实例(例如Create a new instance of Azure Database for MySQL Single Server (ex. “replica.mysql.database.chinacloudapi.cn”)。"replica.mysql.database.chinacloudapi.cn"). 请参阅使用 Azure 门户创建 Azure Database for MySQL 服务器,了解如何创建服务器。Refer to Create an Azure Database for MySQL server by using the Azure portal for server creation. 此服务器是数据传入复制的“副本”服务器。This server is the "replica" server for Data-in Replication.

    重要

    必须在“常规用途”或“内存优化”定价层中创建 Azure Database for MySQL 服务器,因为数据传入复制只在这些层中受支持。The Azure Database for MySQL server must be created in the General Purpose or Memory Optimized pricing tiers as data-in replication is only supported in these tiers.

  2. 创建相同的用户帐户和对应的特权。Create the same user accounts and corresponding privileges.

    用户帐户不会从源服务器复制到副本服务器。User accounts aren't replicated from the source server to the replica server. 如果打算为用户提供访问副本服务器的权限,则需在这个新创建的 Azure Database for MySQL 服务器上手动创建所有帐户和相应的特权。If you plan on providing users with access to the replica server, you need to create all accounts and corresponding privileges manually on this newly created Azure Database for MySQL server.

  3. 将源服务器的 IP 地址添加到副本的防火墙规则。Add the source server's IP address to the replica's firewall rules.

    使用 Azure 门户Azure CLI 更新防火墙规则。Update firewall rules using the Azure portal or Azure CLI.

  4. 可选 - 如果想要使用从源服务器到 Azure Database for MySQL 副本服务器的基于 GTID 的复制,则需要在 Azure Database for MySQL 服务器上启用以下服务器参数,如以下门户图像所示:Optional - If you wish to use GTID-based replication from the source server to the Azure Database for MySQL replica server, you'll need to enable the following server parameters on the Azure Database for MySQL server as shown in the portal image below:

    在 Azure Database for MySQL 服务器上启用 GTID

配置源 MySQL 服务器Configure the source MySQL server

以下步骤准备并配置本地或虚拟机中托管的 MySQL 服务器或其他云提供程序托管的数据库服务,以便向内复制数据。The following steps prepare and configure the MySQL server hosted on-premises, in a virtual machine, or database service hosted by other cloud providers for Data-in Replication. 该服务器是数据传入复制的“源”。This server is the "source" for Data-in replication.

  1. 请先查看源服务器要求,然后再继续。Review the source server requirements before proceeding.

  2. 请确保源服务器允许端口 3306 上的入站和出站流量,并且源服务器具有公共 IP 地址,DNS 可供公开访问,或者 DNS 具有完全限定的域名 (FQDN)。Ensure that the source server allows both inbound and outbound traffic on port 3306, and that it has a public IP address, the DNS is publicly accessible, or that it has a fully qualified domain name (FQDN).

    尝试从另一台计算机上托管的工具(如 MySQL 命令行)进行连接,以测试与源服务器的连接。Test connectivity to the source server by attempting to connect from a tool such as the MySQL command-line hosted on another machine.

    如果你的组织有严格的安全策略,并且不允许源服务器上的所有 IP 地址都能进行从 Azure 到源服务器的通信,那么你可能可以使用以下命令来确定 MySQL 服务器的 IP 地址。If your organization has strict security policies and won't allow all IP addresses on the source server to enable communication from Azure to your source server, you can potentially use the command below to determine the IP address of your MySQL server.

    1. 使用 MySQL 命令行之类的工具登录 Azure Database for MySQL 服务器。Sign in to your Azure Database for MySQL server using a tool such as the MySQL command line.

    2. 执行以下查询。Execute the following query.

      mysql> SELECT @@global.redirect_server_host;
      

      下面是一些示例输出:Below is some sample output:

      +-----------------------------------------------------------+
      | @@global.redirect_server_host                             |
      +-----------------------------------------------------------+
      | e299ae56f000.tr1830.chinaeast2-a.worker.database.chinacloudapi.cn |
       +-----------------------------------------------------------+
      
    3. 退出 MySQL 命令行。Exit from the MySQL command line.

    4. 若要获取 IP 地址,请在 ping 实用工具中执行以下命令:To get the IP address, execute the following command in the ping utility:

      ping <output of step 2b>
      

      例如: 。For example:

      C:\Users\testuser> ping e299ae56f000.tr1830.chinaeast2-a.worker.database.chinacloudapi.cn
      Pinging tr1830.chinaeast2-a.worker.database.chinacloudapi.cn (**11.11.111.111**) 56(84) bytes of data.
      
    5. 配置源服务器的防火墙规则,以便在端口 3306 上包括上一步的输出 IP 地址。Configure your source server's firewall rules to include the previous step's outputted IP address on port 3306.

      备注

      此 IP 地址可能因维护/部署操作而发生更改。This IP address may change due to maintenance / deployment operations. 这种连接方法仅适用于无法承受在 3306 端口上允许所有 IP 地址的客户。This method of connectivity is only for customers who cannot afford to allow all IP address on 3306 port.

  3. 启用二进制日志记录。Turn on binary logging.

    运行以下命令以检查是否已在源服务器上启用了二进制日志记录:Check to see if binary logging has been enabled on the source by running the following command:

    SHOW VARIABLES LIKE 'log_bin';
    

    如果返回了值为“ON”的变量 log_bin,则表示已在服务器上启用了二进制日志记录。If the variable log_bin is returned with the value "ON", binary logging is enabled on your server.

    如果 log_bin 返回的值为“OFF”,并且源服务器在本地或虚拟机上运行,而你可以访问配置文件 (my.cnf),则可以执行以下步骤:If log_bin is returned with the value "OFF" and your source server is running on-premises or on virtual machines where you can access the configuration file (my.cnf), you can follow the steps below:

    1. 在源服务器中找到 MySQL 配置文件 (my.cnf)。Locate your MySQL configuration file (my.cnf) in the source server. 例如:/etc/my.cnfFor example: /etc/my.cnf

    2. 打开配置文件进行编辑,并在文件中找到“mysqld”部分。Open the configuration file to edit it and locate mysqld section in the file.

    3. 在 mysqld 部分中,添加以下行:In the mysqld section, add following line:

      log-bin=mysql-bin.log
      
    4. 重新启动 MySQL 源服务器以使更改生效。Restart the MySQL source server for the changes to take effect.

    5. 重启服务器后,运行与之前相同的查询来验证是否已启用二进制日志记录:After the server is restarted, verify that binary logging is enabled by running the same query as before:

      SHOW VARIABLES LIKE 'log_bin';
      
  4. 配置源服务器设置。Configure the source server settings.

    “数据传入复制”要求参数 lower_case_table_names 在源服务器与副本服务器之间保持一致。Data-in Replication requires the parameter lower_case_table_names to be consistent between the source and replica servers. 在 Azure Database for MySQL 中,该参数默认为 1。This parameter is 1 by default in Azure Database for MySQL.

    SET GLOBAL lower_case_table_names = 1;
    

    可选 -如果想要使用基于 GTID 的复制,则需要检查源服务器上是否启用了 GTID。Optional - If you wish to use GTID-based replication, you'll need to check if GTID is enabled on the source server. 可对源 MySQL 服务器执行以下命令,查看 gtid_mode 是否为 ON。You can execute following command against your source MySQL server to see if gtid_mode is ON.

    show variables like 'gtid_mode';
    

    重要

    所有服务器都将 gtid_mode 设置为默认值 OFF。All servers have gtid_mode set to the default value OFF. 不需要专门在 MySQL 源服务器上启用 GTID 以设置数据传入复制。You don't need to enable GTID on the source MySQL server specifically to set up Data-in Replication. 如果已在源服务器上启用 GTID,则也可选择使用基于 GTID 的复制来设置 Azure Database for MySQL 单一服务器的数据传入复制。If GTID is already enabled on source server, you can optionally use GTID-based replication to set up Data-in Replication too with Azure Database for MySQL Single Server. 可使用基于文件的复制为所有服务器设置数据传入复制,而不必考虑源服务器上的 gitd_mode 配置。You can use file-based replication to set up data-in replication for all servers regardless of the gitd_mode configuration on the source server.

  5. 创建新的复制角色并设置权限。Create a new replication role and set up permission.

    在源服务器上创建一个配置有复制特权的用户帐户。Create a user account on the source server that is configured with replication privileges. 可通过 SQL 命令或 MySQL Workbench 等工具实现此目的。This can be done through SQL commands or a tool such as MySQL Workbench. 考虑是否打算使用 SSL 进行复制,因为这需要在创建用户时指定。Consider whether you plan on replicating with SSL, as this will need to be specified when creating the user. 请参阅 MySQL 文档,了解如何在源服务器上添加用户帐户Refer to the MySQL documentation to understand how to add user accounts on your source server.

    在以下命令中,创建的新复制角色可从任何计算机访问源服务器,而不仅仅可从托管源服务器本身的计算机进行访问。In the following commands, the new replication role created can access the source from any machine, not just the machine that hosts the source itself. 这可以通过在创建用户的命令中指定“syncuser@'%'”来完成。This is done by specifying "syncuser@'%'" in the create user command. 请参阅 MySQL 文档,详细了解如何指定帐户名称See the MySQL documentation to learn more about specifying account names.

    SQL 命令SQL Command

    使用 SSL 复制Replication with SSL

    如果所有用户连接都要求 SSL,请使用以下命令来创建用户:To require SSL for all user connections, use the following command to create a user:

    CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword';
    GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%' REQUIRE SSL;
    

    不使用 SSL 复制Replication without SSL

    如果并非所有连接都要求 SSL,请使用以下命令来创建用户:If SSL isn't required for all connections, use the following command to create a user:

    CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword';
    GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%';
    

    MySQL WorkbenchMySQL Workbench

    若要在 MySQL Workbench 中创建复制角色,请在“管理”面板中打开“用户和特权”面板,然后选择“添加帐户”。 To create the replication role in MySQL Workbench, open the Users and Privileges panel from the Management panel, and then select Add Account.

    用户和特权

    在“登录名称”字段中键入用户名。Type in the username into the Login Name field.

    同步用户

    选择“管理角色”面板,然后从“全局特权”列表中选择“复制从属实例”。 Select the Administrative Roles panel and then select Replication Slave from the list of Global Privileges. 然后,选择“应用”以创建复制角色。Then select Apply to create the replication role.

    复制从属实例

  6. 将源服务器设置为只读模式。Set the source server to read-only mode.

    在开始转储数据库之前,需将服务器置于只读模式。Before starting to dump out the database, the server needs to be placed in read-only mode. 在只读模式下,源服务器将无法处理任何写入事务。While in read-only mode, the source will be unable to process any write transactions. 评估对业务的影响,根据需要在非高峰时间计划只读窗口。Evaluate the impact to your business and schedule the read-only window in an off-peak time if necessary.

    FLUSH TABLES WITH READ LOCK;
    SET GLOBAL read_only = ON;
    
  7. 获取二进制日志文件名和偏移量。Get binary log file name and offset.

    运行 show master status 命令,确定当前的二进制日志文件名和偏移量。Run the show master status command to determine the current binary log file name and offset.

     show master status;
    

    结果应如下所示。The results should appear similar to the following. 请务必记下二进制文件名,以供在后续步骤中使用。Make sure to note the binary file name for use in later steps.

    主机状态结果

转储并还原源服务器Dump and restore the source server

  1. 确定要将哪些数据库和表复制到 Azure Database for MySQL 并从源服务器执行转储。Determine which databases and tables you want to replicate into Azure Database for MySQL and perform the dump from the source server.

    可以使用 mysqldump 从主服务器转储数据库。You can use mysqldump to dump databases from your primary server. 有关详细信息,请参阅转储和还原For details, refer to Dump & Restore. 不需转储 MySQL 库和测试库。It's unnecessary to dump the MySQL library and test library.

  2. 可选 -如果想要使用基于 gtid 的复制,则需要确定在主服务器执行的最后一个事务的 GTID。Optional - If you wish to use gtid-based replication, you'll need to identify the GTID of the last transaction executed at the primary. 可使用以下命令,记下在主服务器上执行的最后一个事务的 GTID。You can use the following command to note the GTID of the last transaction executed on the master server.

    show global variables like 'gtid_executed';
    
  3. 将源服务器设置为读/写模式。Set source server to read/write mode.

    转储数据库后,将 MySQL 源服务器改回读/写模式。After the database has been dumped, change the source MySQL server back to read/write mode.

    SET GLOBAL read_only = OFF;
    UNLOCK TABLES;
    
  4. 将转储文件还原到新服务器。Restore dump file to new server.

    将转储文件还原到在 Azure Database for MySQL 服务中创建的服务器。Restore the dump file to the server created in the Azure Database for MySQL service. 请参阅转储和还原,了解如何将转储文件还原到 MySQL 服务器。Refer to Dump & Restore for how to restore a dump file to a MySQL server. 如果转储文件较大,请将它上传到副本服务器所在区域的 Azure 中的虚拟机。If the dump file is large, upload it to a virtual machine in Azure within the same region as your replica server. 将它从虚拟机还原到 Azure Database for MySQL 服务器。Restore it to the Azure Database for MySQL server from the virtual machine.

  5. 可选 - 注意 Azure Database for MySQL 上已还原服务器的 GTID,确保它与主服务器相同。Optional - Note the GTID of the restored server on Azure Database for MySQL to ensure it is same as the primary server. 可使用以下命令,记下 Azure Database for MySQL 副本服务器上 GTID 清除值的 GTID。You can use the following command to note the GTID of the GTID purged value on the Azure Database for MySQL replica server. gtid_purged 值应与步骤 2 中记下的主服务器上的 gtid_executed 值相同,以使基于 GTID 的复制可工作。The value of gtid_purged should be same as gtid_executed on master noted in step 2 for GTID-based replication to work.

    show global variables like 'gtid_purged';
    
  1. 设置源服务器。Set the source server.

    所有数据传入复制功能都是通过存储过程完成的。All Data-in Replication functions are done by stored procedures. 可以在数据传入复制存储过程中找到所有过程。You can find all procedures at Data-in Replication Stored Procedures. 这些存储过程可以在 MySQL shell 或 MySQL Workbench 中运行。The stored procedures can be run in the MySQL shell or MySQL Workbench.

    若要链接两个服务器并启动复制,请在 Azure DB for MySQL 服务中登录到目标副本服务器,并将外部实例设置为源服务器。To link two servers and start replication, login to the target replica server in the Azure DB for MySQL service and set the external instance as the source server. 为此,可在 Azure DB for MySQL 服务器上使用 mysql.az_replication_change_master 存储过程。This is done by using the mysql.az_replication_change_master stored procedure on the Azure DB for MySQL server.

    CALL mysql.az_replication_change_master('<master_host>', '<master_user>', '<master_password>', <master_port>, '<master_log_file>', <master_log_pos>, '<master_ssl_ca>');
    

    可选 - 如果想要使用基于 gtid 的复制,则需要使用以下命令链接这两个服务器Optional - If you wish to use gtid-based replication,you will need to use the following command to link the two servers

    call mysql.az_replication_change_master_with_gtid('<master_host>', '<master_user>', '<master_password>', <master_port>, '<master_ssl_ca>');
    
    • master_host:源服务器的主机名master_host: hostname of the source server

    • master_user:源服务器的用户名master_user: username for the source server

    • master_password:源服务器的密码master_password: password for the source server

    • master_port:源服务器侦听连接的端口号。master_port: port number on which source server is listening for connections. (3306 是 MySQL 侦听的默认端口)(3306 is the default port on which MySQL is listening)

    • master_log_file:正在运行的 show master status 中的二进制日志文件名master_log_file: binary log file name from running show master status

    • master_log_pos:正在运行的 show master status 中的二进制日志位置master_log_pos: binary log position from running show master status

    • master_ssl_ca:CA 证书的上下文。master_ssl_ca: CA certificate's context. 如果不使用 SSL,请传入空字符串。If not using SSL, pass in empty string.

      建议以变量形式传入此参数。It's recommended to pass this parameter in as a variable. 有关详细信息,请参阅以下示例。For more information, see the following examples.

    备注

    如果源服务器托管在 Azure VM 中,请将“允许访问 Azure 服务”设置为“启用”,以允许源服务器和副本服务器相互通信。If the source server is hosted in an Azure VM, set "Allow access to Azure services" to "ON" to allow the source and replica servers to communicate with each other. 从“连接安全性”选项可更改此设置。This setting can be changed from the Connection security options. 有关详细信息,请参阅使用门户管理防火墙规则For more information, see Manage firewall rules using the portal .

    示例Examples

    使用 SSL 复制Replication with SSL

    运行以下 MySQL 命令创建变量 @certThe variable @cert is created by running the following MySQL commands:

    SET @cert = '-----BEGIN CERTIFICATE-----
    PLACE YOUR PUBLIC KEY CERTIFICATE'`S CONTEXT HERE
    -----END CERTIFICATE-----'
    

    在域“companya.com”中托管的源服务器与 Azure Database for MySQL 中托管的副本服务器之间设置了使用 SSL 进行复制的功能。Replication with SSL is set up between a source server hosted in the domain "companya.com" and a replica server hosted in Azure Database for MySQL. 将在副本上运行此存储过程。This stored procedure is run on the replica.

    CALL mysql.az_replication_change_master('master.companya.com', 'syncuser', 'P@ssword!', 3306, 'mysql-bin.000002', 120, @cert);
    

    不使用 SSL 复制Replication without SSL

    在域“companya.com”中托管的源服务器与 Azure Database for MySQL 中托管的副本服务器之间设置了不使用 SSL 进行复制的功能。Replication without SSL is set up between a source server hosted in the domain "companya.com" and a replica server hosted in Azure Database for MySQL. 将在副本上运行此存储过程。This stored procedure is run on the replica.

    CALL mysql.az_replication_change_master('master.companya.com', 'syncuser', 'P@ssword!', 3306, 'mysql-bin.000002', 120, '');
    
  2. 设置筛选。Set up filtering.

    如果要跳过从主副本复制某些表的操作,请更新副本服务器上的 replicate_wild_ignore_table 服务器参数。If you want to skip replicating some tables from your master, update the replicate_wild_ignore_table server parameter on your replica server. 可以使用逗号分隔的列表提供多个表模式。You can provide more than one table pattern using a comma-separated list.

    查看 MySQL 文档详细了解此参数。Review the MySQL documentation to learn more about this parameter.

    若要更新该参数,可以使用 Azure 门户Azure CLITo update the parameter, you can use the Azure portal or Azure CLI.

  3. 启动复制。Start replication.

    调用 mysql.az_replication_start 存储过程以启动复制。Call the mysql.az_replication_start stored procedure to start replication.

    CALL mysql.az_replication_start;
    
  4. 检查复制状态。Check replication status.

    在副本服务器上调用 show slave status 命令查看复制状态。Call the show slave status command on the replica server to view the replication status.

    show slave status;
    

    如果 Slave_IO_RunningSlave_SQL_Running 状态为“yes”,并且 Seconds_Behind_Master 的值为“0”,则表示复制正常运行。If the state of Slave_IO_Running and Slave_SQL_Running are "yes" and the value of Seconds_Behind_Master is "0", replication is working well. Seconds_Behind_Master 指示副本的陈旧状态。Seconds_Behind_Master indicates how late the replica is. 如果其值不为“0”,则表示副本正在处理更新。If the value isn't "0", it means that the replica is processing updates.

用于数据传入复制操作的其他有用的存储过程Other useful stored procedures for Data-in Replication operations

停止复制Stop replication

若要停止源服务器与副本服务器之间的复制,请使用以下存储过程:To stop replication between the source and replica server, use the following stored procedure:

CALL mysql.az_replication_stop;

删除复制关系Remove replication relationship

若要删除源服务器与副本服务器之间的关系,请使用以下存储过程:To remove the relationship between source and replica server, use the following stored procedure:

CALL mysql.az_replication_remove_master;

跳过复制错误Skip replication error

若要跳过复制错误并允许复制继续,请使用以下存储过程:To skip a replication error and allow replication to continue, use the following stored procedure:

CALL mysql.az_replication_skip_counter;

可选 - 如果想要使用基于 gtid 的复制,需使用以下存储过程以跳过事务Optional - If you wish to use gtid-based replication, use the following stored procedure to skip a transaction

call mysql. az_replication_skip_gtid_transaction(�<transaction_gtid>�)

该过程可跳过给定 GTID 的事务。The procedure can skip the transaction for the given GTID. 如果 GTID 格式不正确或已执行 GTID 事务,则该过程将无法执行。If the GTID format is not right or the GTID transaction has already been executed, the procedure will fail to execute. 可通过分析二进制日志来检查事务事件,从而确定事务的 GTID。The GTID for a transaction can be determined by parsing the binary log to check the transaction events. MySQL 提供了一个实用工具 mysqlbinlog,用于分析二进制日志并以文本格式显示其内容,该工具可用于识别事务的 GTID。MySQL provides a utility mysqlbinlog to parse binary logs and display their contents in text format, which can be used to identify GTID of the transaction.

若要跳过当前复制位置之后的下一个事务,使用以下命令来识别下一个事务的 GTID,如下所示。To skip the next transaction after the current replication position, use the following command to identify the GTID of next transaction as shown below.

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos][LIMIT [offset,] row_count]

显示二进制日志结果

后续步骤Next steps