此教程的早期版本由 Etel Sverdlov撰写。
介绍
在使用数据库时,有助于拥有多个数据副本,从而在数据库服务器中的一台失败时提供冗余性,并可以提高数据库的可用性、可扩展性和整体性能。
MySQL是一个关系数据库管理系统,是当今世界上最受欢迎的开源关系数据库,配备了一些内置的复制功能,允许您维护数据的多个副本。
本教程概述了如何在一个服务器上配置MySQL实例作为源数据库,然后在另一个服务器上配置MySQL实例以作为它的复制品。
<$>[注]
注:从历史上看,这种类型的数据库复制被称为主奴隶复制。在2020年7月发布的一篇博客文章中(https://mysqlhighavailability.com/mysql-terminology-updates/),MySQL团队承认了这种术语的消极起源,并宣布了更新数据库程序及其文档以使用更具包容性的语言的努力。
虽然MySQL的文档和该程序版本8中的许多命令已被更新,以代替将复制 topology中的服务器称为 source 和其 replicas,但仍然存在负面术语的地方。
美元
前提条件
要完成本指南,您将需要:
- 两个运行Ubuntu 20.04的服务器都应该有一个非根管理用户,具有
sudo特权和与UFW配置的防火墙。 遵循我们的 Ubuntu 20.04的初始服务器设置指南来设置这两个服务器 - MySQL安装在每个服务器上。
请注意,本指南所述的程序包括将 MySQL 安装在一个服务器上指定为 source database,然后将 MySQL 安装配置为其他服务器上的 replica。
1[environment second]
同样,必须在复制 MySQL 实例的服务器上运行的任何命令都将具有红色背景:
1[environment third]
最后,本教程包含关于如何将现有数据库中的数据从源迁移到复制件的可选说明,该过程涉及创建源数据库的截图,并将结果的文件复制到复制件。
理解MySQL中的复制
在MySQL中,复制涉及源数据库在一个或多个数据库中的数据中写下所做的每一个更改,在一个称为二进制日志的特殊文件中。复制实例初始化后,它创建了两个线程过程。第一个,称为 IO thread,连接到源MySQL实例,并读取二进制日志事件的行列,然后将它们复制到复制服务器上的本地文件中,称为 relay log。
最近版本的MySQL支持复制数据的两种方法,这些复制方法之间的差异与复制程序如何跟踪数据库事件的来源已经处理。
MySQL 将其传统的复制方法称为 binary log file position-based replication. 当您使用此方法将 MySQL 实例转换为复制时,您必须为其提供一组二进制日志坐标。
这些坐标很重要,因为复制件将收到其源的整个二进制日志的副本,如果没有正确的坐标,它们将开始复制在其内记录的每个数据库事件。
基于二进制日志文件位置的复制对于许多应用案例来说是可行的,但这种方法在更复杂的设置中可能会变得复杂,这导致了MySQL的更新的原生复制方法的发展,有时也被称为基于交易的复制。
基于交易的复制的机制类似于基于二进制日志文件的复制:每当数据库交易发生时,MySQL都会在二进制日志文件中与交易本身一起分配并记录该交易的GTID。
MySQL 的基于交易的复制具有与其传统复制方法相比的一些优点,例如,因为源和复制都保留了 GTID,如果源或复制都遇到他们处理过的 GTID 的交易,然后他们会跳过该交易。这有助于确保源和复制之间的一致性。此外,在基于交易的复制复制中,不需要知道下一个数据库事件的二进制日志坐标来处理。
请记住,这只是MySQL如何处理复制的一般解释;MySQL提供了许多选项,您可以调整以优化自己的复制设置。本指南概述了如何设置基于二进制日志文件位置的复制。
步骤 1 – 调整源服务器的防火墙
假设您遵循了 初始服务器设置指南的先决条件,您将在您的服务器上配置了使用 UFW 的防火墙,这将有助于保持您的两个服务器的安全性,但源的防火墙将阻止您复制 MySQL 实例的任何连接尝试。
要更改这一点,您需要包含一个 UFW 规则,该规则允许从您的复制品通过源的防火墙连接。
此特定命令允许任何源自复制服务器的 IP 地址的连接 - 由 replica_server_ip 表示 - 到 MySQL 的默认端口号, `3306:
1[environment second]
2sudo ufw allow from replica_server_ip to any port 3306
请确保将replica_server_ip替换为您的 replika 服务器的实际 IP 地址. 如果该规则被成功添加,您将看到以下输出:
1[environment second]
2[secondary_label Output]
3Rule added
之后,您不需要对复制件的防火墙规则进行任何更改,因为复制件服务器将不会接收任何接入连接,而向源MySQL服务器的输出连接不会被UFW阻止。
第2步:配置源数据库
为了让您的源MySQL数据库开始复制数据,您需要对其配置进行一些更改。
在Ubuntu 20.04,默认的MySQL服务器配置文件被命名为mysqld.cnf,可以在/etc/mysql/mysql.conf.d/目录中找到。
1[environment second]
2sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
在檔案中,尋找「bind-address」指令. 它預設會看起來像這樣:
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment second]
3. . .
4bind-address = 127.0.0.1
5. . .
「127.0.0.1」是代表 localhost的IPv4循环返回地址,并将此设置为bind-address指令的值,指示MySQL只听取在 localhost地址上的连接,换句话说,这个MySQL实例只能接受来自安装服务器的连接。
请记住,您正在将其他 MySQL 实例转换为此类实例的复制件,因此复制件必须能够读取任何新数据被写入源安装中。
将127.0.0.1替换为源服务器的IP地址,这样做后,bind-address指令将看起来像这样,而代替source_server_ip则是您自己的服务器的IP地址:
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment second]
3. . .
4bind-address = source_server_ip
5. . .
接下来,找到server-id指令,该指令定义了MySQL内部使用的标识符来区分复制设置中的服务器.复制环境中的每个服务器,包括源和所有复制品,必须有自己的独特server-id值。
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment second]
3. . .
4# server-id = 1
5. . .
您可以选择任何数字作为本指令的值,但请记住,该数字必须是唯一的,不能匹配您的复制组中的任何其他服务器-id。
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment second]
3. . .
4server-id = 1
5. . .
在server-id行下方,找到log_bin指令,这定义了MySQL的二进制日志文件的基本名称和位置。
当被评论时,由于此指令是默认的,二进制日志被禁用. 您的复制服务器必须读取源的二进制日志文件,以便它知道何时以及如何复制源的数据,所以不要评论此行,以便在源上启用二进制日志。
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment second]
3. . .
4log_bin = /var/log/mysql/mysql-bin.log
5. . .
最后,向下滚动到文件的底部,以找到评论的‘binlog_do_db’指令:
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment second]
3. . .
4# binlog_do_db = include_database_name
删除英镑标志以删除此行,并用您要复制的数据库名称替换include_database_name。本示例显示了指向名为db的数据库的binlog_do_db指令,但如果您在源中有想要复制的现有数据库,请使用其名称而不是db:
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment second]
3. . .
4binlog_do_db = db
<$>[注] 注:如果您想要复制多个数据库,您可以为您想要添加的每个数据库添加另一个‘binlog_do_db’指令。
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment second]
3. . .
4binlog_do_db = db
5binlog_do_db = db_1
6binlog_do_db = db_2
或者,您可以通过为每个数据库添加一个binlog_ignore_db指令来指定哪些MySQL数据库不应该复制:
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment second]
3. . .
4binlog_ignore_db = db_to_ignore
美元
执行这些更改后,保存并关闭文件. 如果您使用nano来编辑文件,请按CTRL + X,Y,然后按ENTER。
然后通过运行以下命令重新启动 MySQL 服务:
1[environment second]
2sudo systemctl restart mysql
有了这个 MySQL 实例,它已经准备好作为其他 MySQL 服务器将复制的源数据库工作了. 但是,在您可以配置复制之前,您还需要在源上执行几步,以确保您的复制 topology 正常工作。
步骤 3 – 创建复制用户
MySQL 复制环境中的每个复制件都以用户名和密码连接到源数据库,而复制件可以使用源数据库中存在的任何 MySQL 用户配置文件连接,并具有相应的特权,但本教程将概述如何为此目的创建专用用户。
首先,打开MySQL壳:
1[environment second]
2sudo mysql
<$>[注] 注:如果您配置了使用密码进行身份验证的专用MySQL用户,则可以使用以下命令连接到您的MySQL:
1[environment second]
2mysql -u sammy -p
用你的专用用户的名字代替sammy,并在被要求时输入该用户的密码。
请注意,在本指南中的一些操作,包括一些必须在复制服务器上执行的操作,需要更高级的权限。 因此,您可以更方便地作为管理员连接,因为您可以使用以前的sudo mysql命令。 但是,如果您希望在本指南中使用更少特权的MySQL用户,他们至少应该获得CREATE USER,RELOAD,REPLICATION CLIENT,REPLICATION SLAVE和REPLICATION_SLAVE_ADMIN的权限。
从提示,创建一个新的MySQL用户。下面的例子将创建一个名为 replica_user的用户,但你可以随心所欲地命名你的用户。
1[environment second]
2CREATE USER 'replica_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';
请注意,此命令规定 replica_user将使用mysql_native_password身份验证插件。可以使用MySQL的默认身份验证机制,即caching_sha2_password,但这需要在源和复制之间设置加密连接。这种类型的设置对于生产环境来说是最佳的,但配置加密连接超出了本教程的范围。
创建新用户后,给予他们相应的权限. 至少,MySQL复制用户必须具有REPLICATION SLAVE的权限:
1[environment second]
2GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'replica_server_ip';
随后,运行FLUSH PRIVILEGES命令是很好的做法,这将释放服务器由于前面的CREATE USER和GRANT声明而缓存的任何内存:
1[environment second]
2FLUSH PRIVILEGES;
有了这个,你已经完成了在源MySQL实例上设置复制用户的设置,但是, 不要离开MySQL壳. 现在把它打开,因为你将在下一步使用它来获取有关源数据库的二进制日志文件的一些重要信息。
步骤 4 – 从源获取二进制日志坐标
从《理解MySQL的复制》(#understanding-replication-in-mysql)部分记住,MySQL通过从源的二进制日志文件行列复制数据库事件并在复制中执行每个事件来实现复制,而在使用MySQL的二进制日志文件位置复制时,你必须为复制提供一组坐标,详细说明源的二进制日志文件的名称和该文件中的特定位置。
此步骤描述了如何获取源实例的当前二进制日志坐标,以便设置您的复制,以便从日志文件的最新点开始复制数据. 为了确保在您获取坐标时没有用户更改任何数据,这可能导致问题,您需要锁定数据库以防止任何客户在获取坐标时阅读或写入数据。
您还应该从上一步结束时保持源服务器的MySQL壳打开,从提示中运行以下命令,该命令将关闭源实例中的每个数据库中的所有开放表并锁定它们:
1[environment second]
2FLUSH TABLES WITH READ LOCK;
然后运行以下操作,返回源的二进制日志文件的当前状态信息:
1[environment second]
2SHOW MASTER STATUS;
您将在输出中看到类似于此示例的表:
1[environment second]
2[secondary_label Output]
3+------------------+----------+--------------+------------------+-------------------+
4| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
5+------------------+----------+--------------+------------------+-------------------+
6| mysql-bin.000001 | 899 | db | | |
7+------------------+----------+--------------+------------------+-------------------+
81 row in set (0.00 sec)
这是复制开始复制数据库事件的位置. 记录文件名称和位置值,因为您在启动复制时将需要这些值。
获取此信息后,您立即所做的取决于您的源数据库是否具有您想要迁移到复制品的现有数据。
如果您的源没有任何现有数据可以迁移
如果您的源 MySQL 实例是新安装的,或者没有您想要迁移到复制件的现有数据,您可以在此时解锁表:
1[environment second]
2UNLOCK TABLES;
如果您尚未这样做,您可以创建您选择复制的数据库,而您仍然有开放的MySQL壳,按照步骤 2中的示例,下列操作将创建名为db的数据库:
1[environment second]
2CREATE DATABASE db;
1[environment second]
2[secondary_label Output]
3Query OK, 1 row affected (0.01 sec)
然后,关闭 MySQL 壳:
1[environment second]
2exit
接下来,您可以转到 下一步。
如果您的来源有现有数据可迁移
如果您在源 MySQL 实例中有数据,您希望将其迁移到您的复制品中,您可以通过使用mysqldump实用程序创建数据库的快照。但是,您的数据库仍然应该目前被锁定。
解锁表可能会导致问题,因为这意味着客户端可以再次更改数据库中的数据,这可能导致数据快照和您刚刚获取的二进制日志坐标之间不匹配。
出于这个原因,您必须在本地计算机上打开一个新的终端窗口或选项卡,这样您就可以创建数据库截图而无需解锁MySQL。
从新终端窗口或标签中,打开另一个SSH会话到服务器托管您的 源MySQL实例:
1[environment local]
2ssh sammy@source_server_ip
然后,从新标签或窗口中,使用mysqldump导出您的数据库。下面的示例会从名为db的数据库中创建一个名为db.sql的垃圾文件,但请确保您包含自己的数据库的名称。
1[environment second]
2sudo mysqldump -u root db > db.sql
接下来,您可以关闭这个终端窗口或标签,然后返回您的第一个窗口,该窗口仍然应该打开MySQL壳。
1[environment second]
2UNLOCK TABLES;
然后你可以离开MySQL壳:
1[environment second]
2exit
假设您已在源服务器上配置了 SSH 密钥,并已将源的公共密钥添加到您的复制品的 authorized_keys 文件中,您可以安全地使用一个 scp 命令,如下:
1[environment second]
2scp db.sql sammy@replica_server_ip:/tmp/
请确保用您在复制服务器上创建的管理 Ubuntu 用户配置文件的名称代替sammy,并用复制服务器的 IP 地址代替replica_server_ip。
在将 snapshot 发送到复制服务器后,SSH 将其输入:
1[environment local]
2ssh sammy@replica_server_ip
然后打开 MySQL 壳:
1[environment third]
2sudo mysql
从提示中,创建您将从源复制的新数据库:
1[environment third]
2CREATE DATABASE db;
您不需要创建任何表格或加载此数据库以任何样本数据。所有这些都将被处理当您使用您刚刚创建的快照导入数据库时。
1[environment third]
2exit
然后导入数据库的快照:
1[environment third]
2sudo mysql db < /tmp/db.sql
您的复制件现在拥有来自源数据库的所有现有数据,您可以完成本指南的最后步骤来配置您的复制件服务器,以便开始复制对源数据库所做的新更改。
步骤 5 – 配置复制数据库
只剩下做的是更改复制件的配置,类似于你如何更改源的配置文件。
1[environment third]
2sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
如前所述,复制设置中的每一个MySQL实例都必须具有独特的服务器-id值,找到复制的服务器-id指令,放弃评论,并将其值更改为任何正整数,只要它与源的值不同:
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment third]
3server-id = 2
接下来,更新log_bin和binlog_do_db值,以便它们与您在源机配置文件中设置的值一致:
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment third]
3. . .
4log_bin = /var/log/mysql/mysql-bin.log
5. . .
6binlog_do_db = db
7. . .
最后,添加一个定义复制件的复制件日志文件位置的连接日志指令。
1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[environment third]
3. . .
4relay-log = /var/log/mysql/mysql-relay-bin.log
完成这些更改后,保存并关闭文件,然后在复制程序上重新启动MySQL以实现新配置:
1[environment third]
2sudo systemctl restart mysql
在重新启动mysql服务后,您终于准备好从源数据库开始复制数据。
步骤 6 – 启动和测试复制
在此时,您的两个MySQL实例都已完全配置为允许复制。 要开始复制来自源的数据,请在复制服务器上打开MySQL壳**:
1[environment third]
2sudo mysql
从提示程序中运行以下操作,该操作同时配置多个MySQL复制设置。运行此命令后,一旦您在该实例上启用复制,它将尝试使用以下用户名和密码连接到SOURCE_HOST后的IP地址,分别使用SOURCE_USER和SOURCE_PASSWORD。
同样,replica_user和password应该与您在步骤 2 中创建的复制用户一致;而mysql-bin.000001和899应该反映您在步骤 3 中获得的二进制日志坐标。
您可能想要在文本编辑器中输入此命令,然后在复制服务器上运行,以便更容易取代所有相关信息:
1[environment third]
2CHANGE REPLICATION SOURCE TO
3SOURCE_HOST='source_server_ip',
4SOURCE_USER='replica_user',
5SOURCE_PASSWORD='password',
6SOURCE_LOG_FILE='mysql-bin.000001',
7SOURCE_LOG_POS=899;
接下来,启用复制服务器:
1[environment third]
2START REPLICA;
如果您正确输入所有细节,此实例将开始复制对源中的db数据库所做的任何更改。
您可以通过执行以下操作来查看复制品的当前状态的详细信息. 此命令中的 \G 编辑器将文本重新排列,使其更易于阅读:
1[environment third]
2SHOW REPLICA STATUS\G;
此命令返回了大量的信息,可在故障排除时有所帮助:
1[environment third]
2[secondary_label Output]
3*************************** 1. row ***************************
4 Replica_IO_State: Waiting for master to send event
5 Source_Host: 138.197.3.190
6 Source_User: replica_user
7 Source_Port: 3306
8 Connect_Retry: 60
9 Source_Log_File: mysql-bin.000001
10 Read_Source_Log_Pos: 1273
11 Relay_Log_File: mysql-relay-bin.000003
12 Relay_Log_Pos: 729
13 Relay_Source_Log_File: mysql-bin.000001
14. . .
<$>[注] **注:如果您的复制件存在连接问题或复制意外停止,则可能源的二进制日志文件中的一个事件正在阻止复制。
1[environment third]
2SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
接下来,您需要重新开始复制:
1[environment third]
2START REPLICA;
此外,如果您需要停止复制,请注意,您可以在复制实例上执行以下操作:
1[environment third]
2STOP REPLICA;
美元
您的副本现在正在复制来自源的数据. 您对源数据库所做的任何更改都将反映在 MySQL 复制实例中。
首先,在您的源机上打开MySQL壳:
1[environment second]
2sudo mysql
选择您选择复制的数据库:
1[environment second]
2USE db;
然后在该数据库中创建表. 下面的 SQL 操作创建了一个名为 'example_table' 的表,其中有一个名为 'example_column' 的列:
1[environment second]
2CREATE TABLE example_table (
3example_column varchar(30)
4);
1[environment second]
2[secondary_label Output]
3Query OK, 0 rows affected (0.03 sec)
如果您想要,您也可以将一些样本数据添加到此表中:
1[environment second]
2INSERT INTO example_table VALUES
3('This is the first row'),
4('This is the second row'),
5('This is the third row');
1[environment second]
2[secondary_label Output]
3Query OK, 3 rows affected (0.03 sec)
4Records: 3 Duplicates: 0 Warnings: 0
创建表并可选地添加一些样本数据后,返回复制服务器的MySQL壳并选择复制数据库:
1[environment third]
2USE db;
然后运行显示表,列出所选数据库中的所有表:
1[environment third]
2SHOW TABLES;
如果复制工作正确,您将看到您刚刚添加到该命令输出中列出的源的表:
1[environment third]
2[secondary_label Output]
3+---------------+
4| Tables_in_db |
5+---------------+
6| example_table |
7+---------------+
81 row in set (0.00 sec)
此外,如果您已将一些样本数据添加到源表中,则可以检查该数据是否也被复制使用如下类型的查询:
1[environment third]
2SELECT * FROM example_table;
在SQL中,一个星座(*)是所有列的缩写,所以这个查询基本上告诉MySQL从example_table返回每个列。
1[environment third]
2[secondary_label Output]
3+------------------------+
4| example_column |
5+------------------------+
6| This is the first row |
7| This is the second row |
8| This is the third row |
9+------------------------+
103 rows in set (0.00 sec)
如果这些操作中的任何一个都无法返回您添加到源的示例表或数据,则可能在复制配置中的某个地方出现错误。在这种情况下,您可以运行显示复制状态\G操作来尝试查找问题的原因。
结论
完成本教程后,您将建立一个MySQL复制环境,该环境使用MySQL的二进制日志文件位置基于复制方法,具有一个源和一个复制。 但是,请记住,本教程中描述的程序仅代表 MySQL 中的复制配置的一种方式。 MySQL 提供了一些不同的复制选项,您可以使用它们来生成针对您的需求优化的复制环境。 还有许多第三方工具,例如 Galera Cluster,您可以使用它来扩展 MySQL 内置的复制功能。
如果您对 MySQL 中的具体复制功能有任何进一步的问题,我们鼓励您查看 MySQL 官方文档。