在 Azure Database for MariaDB 中配置数据传入复制Configure Data-in Replication in Azure Database for MariaDB

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

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

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

备注

如果主服务器的版本为 10.2 或以上,我们建议使用全局事务 ID 设置数据传入复制。If your master server is version 10.2 or newer, we recommend that you set up Data-in Replication by using Global Transaction ID.

创建用作副本的 MariaDB 服务器Create a MariaDB server to use as a replica

  1. 创建新的 Azure Database for MariaDB 服务器(例如 replica.mariadb.database.chinacloudapi.cn)。Create a new Azure Database for MariaDB server (for example, replica.mariadb.database.chinacloudapi.cn). 在数据传入复制中,该服务器为副本服务器。The server is the replica server in Data-in Replication.

    若要了解如何创建服务器,请参阅使用 Azure 门户创建 Azure Database for MariaDB 服务器To learn about server creation, see Create an Azure Database for MariaDB server by using the Azure portal.

    重要

    必须在“常规用途”或“内存优化”定价层中创建 Azure Database for MariaDB 服务器。You must create the Azure Database for MariaDB server in the General Purpose or Memory Optimized pricing tiers.

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

    用户帐户不会从主服务器复制到副本服务器。User accounts aren't replicated from the master server to the replica server. 若要为用户提供副本服务器的访问权限,必须在新建的 Azure Database for MariaDB 服务器上创建所有帐户和对应的特权。To provide user access to the replica server, you must manually create all accounts and corresponding privileges on the newly created Azure Database for MariaDB server.

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

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

配置主服务器Configure the master server

以下步骤准备并配置本地、VM 或云数据库服务中托管的 MariaDB 服务器,以实现数据传入复制。The following steps prepare and configure the MariaDB server hosted on-premises, in a VM, or in a cloud database service for Data-in Replication. 该 MariaDB 服务器是数据传入复制中的主服务器。The MariaDB server is the master in Data-in Replication.

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

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

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

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

    若要查看是否已在主服务器上启用二进制日志记录,请输入以下命令:To see if binary logging is enabled on the master, enter the following command:

    SHOW VARIABLES LIKE 'log_bin';
    

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

    如果 log_bin 返回了值 OFF,请编辑 my.cnf 文件,使 log_bin=ON 启用二进制日志记录。If log_bin returns the value OFF, edit the my.cnf file so that log_bin=ON turns on binary logging. 重启服务器,使更改生效。Restart the server to make the change take effect.

  3. 配置主服务器设置。Configure master server settings.

    数据传入复制要求参数 lower_case_table_names 在主服务器与副本服务器之间保持一致。Data-in Replication requires the parameter lower_case_table_names to be consistent between the master and replica servers. 在 Azure Database for MariaDB 中,lower_case_table_names 参数默认设置为 1The lower_case_table_names parameter is set to 1 by default in Azure Database for MariaDB.

    SET GLOBAL lower_case_table_names = 1;
    
  4. 创建新的复制角色并设置权限。Create a new replication role and set up permissions.

    在主服务器上创建一个配置有复制特权的用户帐户。Create a user account on the master server that's configured with replication privileges. 可以使用 SQL 命令或 MySQL Workbench 创建帐户。You can create an account by using SQL commands or MySQL Workbench. 如果你打算使用 SSL 进行复制,则必须在创建用户帐户时指定此设置。If you plan to replicate with SSL, you must specify this when you create the user account.

    若要了解如何在主服务器上添加用户帐户,请参阅 MariaDB 文档To learn how to add user accounts on your master server, see the MariaDB documentation.

    如果使用以下命令,则新的复制角色可从任何计算机访问主服务器,而不仅仅可从托管主服务器本身的计算机进行访问。By using the following commands, the new replication role can access the master from any machine, not just the machine that hosts the master itself. 若要进行这种访问,可在用于创建用户的命令中指定 syncuser@'%'For this access, specify syncuser@'%' in the command to create a user.

    若要详细了解如何指定帐户名称,请参阅 MariaDB 文档。To learn more about MariaDB documentation, see specifying account names.

    SQL 命令SQL command

    • 使用 SSL 进行复制Replication with SSL

      如果所有用户连接都要求 SSL,请输入以下命令来创建用户:To require SSL for all user connections, enter 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, enter 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, in the Management pane, select Users and Privileges. 然后选择“添加帐户”。Then select Add Account.

    用户和特权

    在“登录名”字段中输入用户名。Enter a username in the Login Name field.

    同步用户

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

    复制从属实例

  5. 将主服务器设置为只读模式。Set the master server to read-only mode.

    在开始转储数据库之前,必须将服务器置于只读模式。Before you dump a database, the server must be placed in read-only mode. 在只读模式下,主服务器无法处理任何写入事务。While in read-only mode, the master can't process any write transactions. 为帮助避免对业务造成影响,请将只读时段安排在非高峰期。To help avoid business impact, schedule the read-only window during an off-peak time.

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

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

    show master status;
    

    结果应类似于下表中的内容:The results should be similar to the following table:

    主机状态结果

    请记下二进制文件名,因为后面的步骤中需要用到。Note the binary file name, because it'll be used in later steps.

  7. 获取 GTID 位置(可选,使用 GTID 复制时需要用到)。Get the GTID position (optional, needed for replication with GTID).

    运行函数 BINLOG_GTID_POS 获取相应 binlog 文件名和偏移量的 GTID 位置。Run the function BINLOG_GTID_POS to get the GTID position for the corresponding binlog file name and offset.

    select BINLOG_GTID_POS('<binlog file name>', <binlog offset>);
    

转储并还原主服务器Dump and restore the master server

  1. 从主服务器转储所有数据库。Dump all the databases from the master server.

    使用 mysqldump 从主服务器转储所有数据库。Use mysqldump to dump all the databases from the master server. 不需要转储 MySQL 库和测试库。It isn't necessary to dump the MySQL library and test library.

    有关详细信息,请参阅转储和还原For more information, see Dump and restore.

  2. 将主服务器设置为读/写模式。Set the master server to read/write mode.

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

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

    将转储文件还原到 Azure Database for MariaDB 服务中创建的服务器。Restore the dump file to the server created in the Azure Database for MariaDB service. 请参阅转储和还原,了解如何将转储文件还原到 MariaDB 服务器。See Dump & Restore for how to restore a dump file to a MariaDB server.

    如果转储文件较大,请将它上传到副本服务器所在区域的 Azure 中的 VM。If the dump file is large, upload it to a VM in Azure within the same region as your replica server. 将转储文件从 VM 还原到 Azure Database for MariaDB 服务器。Restore it to the Azure Database for MariaDB server from the VM.

  1. 设置主服务器。Set the master 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 中运行。Stored procedures can be run in the MySQL shell or MySQL Workbench.

    若要链接两个服务器并启动复制,请在 Azure DB for MariaDB 服务中登录到目标副本服务器。To link two servers and start replication, sign in to the target replica server in the Azure DB for MariaDB service. 接下来,在 Azure DB for MariaDB 服务器上使用 mysql.az_replication_change_mastermysql.az_replication_change_master_with_gtid 存储过程,将外部实例设置为主服务器。Next, set the external instance as the master server by using the mysql.az_replication_change_master or mysql.az_replication_change_master_with_gtid stored procedure on the Azure DB for MariaDB server.

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

    or

    CALL mysql.az_replication_change_master_with_gtid('<master_host>', '<master_user>', '<master_password>', 3306, '<master_gtid_pos>', '<master_ssl_ca>');
    
    • master_host:主服务器的主机名master_host: hostname of the master server
    • master_user:主服务器的用户名master_user: username for the master server
    • master_password:主服务器的密码master_password: password for the master server
    • 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_gtid_pos:正在运行的 select BINLOG_GTID_POS('<binlog file name>', <binlog offset>); 中的 GTID 位置master_gtid_pos: GTID position from running select BINLOG_GTID_POS('<binlog file name>', <binlog offset>);
    • master_ssl_ca:CA 证书的上下文。master_ssl_ca: CA certificate's context. 如果未使用 SSL,请传入空字符串。*If you're not using SSL, pass in an empty string.*

    * 建议在 master_ssl_ca 参数中以变量形式传入此字符串。*We recommend passing in the master_ssl_ca parameter as a variable. 有关详细信息,请参阅以下示例。For more information, see the following examples.

    示例Examples

    • 使用 SSL 进行复制Replication with SSL

      运行以下命令以创建变量 @certCreate the variable @cert by running the following commands:

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

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

      CALL mysql.az_replication_change_master('master.companya.com', 'syncuser', 'P@ssword!', 3306, 'mariadb-bin.000016', 475, @cert);
      
    • 在不使用 SSL 的情况下进行复制Replication without SSL

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

      CALL mysql.az_replication_change_master('master.companya.com', 'syncuser', 'P@ssword!', 3306, 'mariadb-bin.000016', 475, '');
      
  2. 启动复制。Start replication.

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

    CALL mysql.az_replication_start;
    
  3. 检查复制状态。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 Slave_IO_Running and Slave_SQL_Running are in the state yes, and the value of Seconds_Behind_Master is 0, replication is working. Seconds_Behind_Master 指示副本的陈旧状态。Seconds_Behind_Master indicates how late the replica is. 如果值不是 0,则表示副本正在处理更新。If the value isn't 0, then the replica is processing updates.

  4. 更新相应的服务器变量,使数据传入复制更安全(仅当不使用 GTID 进行复制时才需要这样做)。Update the corresponding server variables to make data-in replication safer (required only for replication without GTID).

    由于 MariaDB 中的本机复制限制,在不使用 GTID 方案进行复制时,必须设置 sync_master_infosync_relay_log_info 变量。Because of a native replication limitation in MariaDB, you must set sync_master_info and sync_relay_log_info variables on replication without the GTID scenario.

    检查从属服务器的 sync_master_infosync_relay_log_info 变量,确保数据传入复制稳定,并将这些变量设置为 1Check your slave server's sync_master_info and sync_relay_log_info variables to make sure the data-in replication is stable, and set the variables to 1.

其他存储过程Other stored procedures

停止复制Stop replication

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

CALL mysql.az_replication_stop;

删除复制关系Remove the replication relationship

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

CALL mysql.az_replication_remove_master;

跳过复制错误Skip the replication error

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

CALL mysql.az_replication_skip_counter;

后续步骤Next steps

详细了解 Azure Database for MariaDB 的数据传入复制Learn more about Data-in Replication for Azure Database for MariaDB.