MySQL复制教程

最新推荐文章于 2024-09-01 13:47:06 发布

danpu0978

最新推荐文章于 2024-09-01 13:47:06 发布

阅读量219

点赞数

文章标签：数据库 python linux mysql java

1. MySQL复制简介

在大型企业系统中，数据一致性和备份至关重要。同样重要的是使用数据库副本在数据库服务器上分配负载。但是，维护备份或其他实例是一项艰巨的任务，尤其是当您尝试重复复制整个数据库时。 MySQL复制通过将主数据库的副本逐步创建到已配置的从数据库中，来帮助我们精确地解决此问题。 MySQL复制可用于始终如一地将主数据库复制到多个从数据库中。

此配置具有多个优点，例如：

从属数据库被增量更新，从而减少了创建副本时主数据库的负载。
数据库副本可用于繁重的后端操作。这样可以很好地保持master数据库的性能和响应时间。
从数据库可用于更快地将数据提供给世界各地。

2.设置MySQL服务器

为了设置MySQL复制，您需要至少设置两个单独的服务器。它们既可以存在于同一台机器上，但是可以位于不同的端口上，也可以完全位于不同的机器上。在本教程中，我们将使用两台不同的计算机，并通过端口3306进行简单的MySQL安装。也可以将同一台计算机与在同一台计算机上运行的多个服务器实例一起使用。但是，设置多个实例的过程可能看起来很复杂，因此这里不再讨论。您可以在此链接中探索该选项

下面列出了我将用于这些机器的IP：

主机：192.168.1.25
从站：192.168.1.26

上面列出的IP是我的本地设备IP。如果是远程MySQL服务器，则可以利用相关的公共IP地址。为了设置服务器，请按照我之前的教程中的说明进行操作。在两台机器上都安装了服务器之后，我们就可以继续了解和配置MySQL数据库复制。

3. MySQL复制的拓扑

在继续复制过程之前，详细了解MySQL复制确实非常重要。 MySQL复制配置有多种类型。无论使用哪种配置，复制背后的主要目标都保持不变–在后端执行数据库密集型操作期间，减少主服务器上的负载。每种配置之间的共同点是，配置为充当从属数据库的数据库对于连接到该数据库的外部用户而言始终是只读数据库。默认情况下，复制是一个异步过程，除非配置为其他方式。在下面列出的拓扑中，一种拓扑涉及同步数据复制。这样可以确保更好的数据一致性，但是会增加主数据的负担，因为它需要等待创建副本。

让我们详细讨论MySQL复制配置的类型：

3.1主从配置

在具有从站配置的主站中，数据从主站单向中继到其多个从站，并在那里终止。在这样的配置中，只有一个主数据库服务器配置为充当主服务器并为应用程序提供服务，而多个备份/从属服务器则负责数据密集型操作。下面显示了这种实现方式的典型描述。

奴隶大师

如上图所示，从服务器在主数据库上更新数据时不断接收数据。从站不负责将数据传输到其他从站。从主机到从机的数据传输过程是完全异步的。

3.2主站与中继从站配置

中继从站是一种配置，其中一个从站还负责将数据中继到其他连接的从站。在这样的配置中，主服务器将数据发送到有限数量的从服务器，而不是将副本数据提供给多个从服务器。进行此配置是为了减少主服务器上将数据转发到从属服务器上的负载。

带有中继从站的主站

这种配置对子从节点具有级联影响。如果主站和一个中继从站之间的链接变慢或断开了一会儿，那么它也将延迟相关子从站中的复制。因此，仅在复制延迟可接受的情况下才首选这种类型的复制。

3.3多主站循环配置

定制配置界面提供了多主配置，其中有两个主数据库被配置为在群集拓扑中彼此充当从属。也称为主动活动主配置的主服务器，当唯一的目标是维护数据库副本时，首选这种类型的配置。它为我们节省了为每个主数据库设置其他从属服务器的时间和金钱。

掌握主动大师

3.4具有备份主站配置的主站

上面讨论的拓扑是异步拓扑。在异步拓扑中，主数据库仅将数据发送到从属并恢复其操作。它不等待从站的反馈。因此，如果数据丢失或将数据写入磁盘时出现任何问题，master数据库将无法处理它。具有备用主站配置的主站可精确确保零数据丢失。在这种类型的配置中，主机以半同步方式与备份主机进行通信。半同步通信意味着主机等待，直到备份的主机数据库确认数据更新为止。接收到确认后，无论是否提交数据，主数据库都将恢复其处理。但是，这在很大程度上减少了数据丢失的机会。

主备备份

这样的配置对主数据库的性能有相当大的影响，因此，仅在数据丢失非常严重时才是首选配置。同样重要的是要注意，通信仅与备份主数据库半同步，而从服务器仍异步进行通信。

3.5多个主站–单从站配置

这是一种同类配置，其中从站被来自多个主站的数据轰炸。当唯一的目标是备份数据时，将实现这种配置。托管服务器通常会首选这种配置，以确保正常运行时间和数据一致性。只要不涉及冲突的表，这种配置就和第一个一样简单。下面显示了此配置的图示。

多主拓扑

4.配置服务器以进行MySQL复制

既然我们对可能的MySQL复制拓扑有了足够的了解，那么让我们继续进行复制服务器的配置。配置服务器以进行MySQL复制涉及为每个服务器配置唯一的ID，以其各自的角色配置主服务器和从服务器，并在主数据库上为从服务器提供相关权限。本节将详细介绍相关步骤。

为了配置服务器的ID，请从MySQL配置目录中打开my.cnf配置文件。它可以在Linux的/etc/my.cnf路径中找到，在MacOS的/usr/local/mysql/support-files/my.cnf路径中找到，对于Windows，可以在相关的基本目录中找到。如果找不到该文件，则可以在相关目录中创建一个。理想情况下，MySQL服务器应该能够将其接收。对于MacOS，如果您使用.dmg文件安装了MySQL服务器，则可能需要从系统偏好设置中选择配置文件。对于其他操作系统，MySQL服务器会自动检测配置文件。

拥有文件后，请确保文件中已经包含以下详细信息。

[mysqld]
log-bin=mysql-bin
binlog-do-db=mynewdb
server-id=1
innodb_flush_log_at_trx_commit=1

在以上属性中，我们感兴趣的一项是server-id 。如果多个服务器在同一台计算机上运行或正在配置主从配置，则此变量用于为服务器提供不同的ID。对于您正在使用的一台机器/服务器，将此ID更改为2。这将帮助复制引擎轻松识别数据流方向。完成参数更改并保存后，我们将有两个服务器实例–一个ID为1的服务器实例，另一个ID为2的服务器实例。重新启动ID已更新的MySQL服务器。现在，可以为复制配置服务器了。

现在，了解剩余的属性：

log-bin：此属性标识复制过程的日志的存储位置。

binlog-do-db：此属性标识将复制到从属数据库服务器上的数据库

innodb_flush_log_at_trx_commit：此属性用于在主服务器获得提交确认的同步设置的情况下配置日志刷新。如果是从属服务器，则在提交备份数据更新后将清除日志。

在客户端和服务器上都完成以下配置后，请在两个系统上重新启动MySQL服务。为了重新启动服务，请遵循以下提供的特定于操作系统的方法：

MacOSx：转到系统设置> MySQL。 停止正在运行的服务器实例。在“配置”选项卡中选择相关的配置文件，然后再次启动服务器。这应该使用配置的属性启动服务器。如果您在启动服务器时遇到问题，请确保正确保存了属性并且未使用该端口。

Linux：在所有Linux变体中，您可以使用以下命令重新启动服务器。只要每个数据库服务器有一个系统，该命令将很有用。

$ sudo service mysql restart

Windows：如果使用Windows，则需要停止MySQL56服务。为此，请打开Windows搜索并输入Services 。在“服务”窗口中，查找MySQL56（默认名称）服务。右键单击该服务，然后重新启动它，以确保更改得到反映。

5.启用主从复制

5.1配置主机

启用主从复制的第一步是创建一个用户，该用户可以出于复制目的访问主数据库。从机将使用该用户从主机读取数据以进行复制。为此，执行以下命令以创建一个名为rep_user的用户以进行复制。

$ GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'192.168.1.26' IDENTIFIED BY 'password';

上面的命令将新用户映射到从IP地址。因此，它只能由特定从站专用。这样可以确保正确记录日志以进行审核。执行上面的命令，然后执行下面的命令，以将授予中的更改写入。

$ FLUSH PRIVILEDGES

现在，我们已经允许从属数据库中的复制用户访问主数据库中的所有数据库，我们需要选择实际需要复制的数据库。选择要复制的数据库。

$ use mynewdb

为了允许我们还原更改，您需要备份其当前状态。为此，请使用以下命令锁定数据库。

$ FLUSH TABLES WITH READ LOCK;

上面的命令将刷新数据库上所有未决的请求并锁定修改。锁定后，请备份数据库的当前状态，并检查当前状态以了解复制的开始位置。要进行备份，请从命令提示符或终端执行以下命令。

$ mysqldump -u root -p --opt mynewdb > mynewdb.sql

请记住，这不是MySQL提示命令，因此需要在它之外执行。检查当前数据库状态以获得有关数据库当前位置的信息。

$ SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      104 | mynewdb      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

请注意此处的位置列的值，因为这对验证成功的主从配置很有用。现在我们有了必要的转储和有关位置的信息，现在可以解锁数据库了，我们可以开始进行从数据库配置了。要解锁表，请执行以下命令。

$ UNLOCK TABLES;

5.2从站数据库配置

从属数据库启动的第一步是创建一个与复制主数据库同名的数据库。创建数据库后，需要将数据库导入从数据库中，以覆盖数据库中可能已经存在的数据。为此，请执行以下自我说明性命令。

$ create database mynewdb;
$ exit

在MySQL终端外部执行以下import命令。

$ mysql -u root -p mynewdb < /path/to/mynewdb.sql

导入数据库并准备使用数据库后，我们需要从属服务器进行配置。要配置数据库，请在兼容的纯文本编辑器中打开配置文件。在配置文件中配置以下参数。如果您在同一台计算机上使用多个服务器，请按照本教程中的建议编辑相关的my2.cnf。

[mysqld]
log-bin=mysql-bin
binlog-do-db=mynewdb
server-id=2
relay-log=/path/to/mysql-relay-bin.log

保存更改并重新启动服务器，以确保更改得到反映。最后，是时候将主服务器配置为该从数据库的主服务器了。再次打开mysql提示符，然后输入以下命令，将从属DB配置为从上述位置– 104读取。

CHANGE MASTER TO MASTER_HOST='192.168.1.25',MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  104;

因此，位于192.168.1.25的数据库服务器将充当我的位于192.168.1.26的从属数据库服务器的主服务器。请注意，此处MASTER_LOG_POS=104属性确定了从复制开始读取的位置。
上面传递的凭据是在上面的主数据库配置过程中创建的复制用户的凭据。现在，随着对主数据库的更改，从服务器可以复制数据库了。剩下要做的就是在MySQL提示符下输入以下命令，以从服务器启动服务器。

$ START SLAVE

现在，从站正在读取数据以自动复制它。在任何时候，要检查读取状态，可以执行以下命令。

$ mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 931
               Relay_Log_File: slave-relay-bin.000056
                Relay_Log_Pos: 950
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 931
              Relay_Log_Space: 1365
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids: 0

上面命令中的\G是格式参数。它以每列一行一行的格式显示数据。如果从站启动时出现任何问题，则可能需要使用以下命令跳过该问题。

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

6.从多个从站采购数据

现在我们已经设置了单个源和单个从属，让我们尝试设置一些复杂的东西。需要将多源拓扑中的主服务器配置为使用以下任意一种：

全局交易标识符（GTID）
基于二进制日志位置的复制

要配置和使用全局事务标识符，启用gtid_mode 。要为每个主服务器配置不同的GTID，请使用以下命令。

CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='repl_user', MASTER_PORT=3306, MASTER_PASSWORD='password', \
MASTER_AUTO_POSITION = 104 FOR CHANNEL 'master-1';

要使用不同的GTID配置主服务器，只需将上述命令的值更改为其他命令即可。为了使用二进制日志位置参数配置多个主服务器，可以将以下命令与使用参数SHOW MASTER STATUS;获得的相关二进制日志参数一起使用SHOW MASTER STATUS;

$ CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='repl_user', MASTER_PORT=3306, MASTER_PASSWORD='password' \
MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=104 FOR CHANNEL 'master-1';

一旦完成此配置，主机就可以充当多个源。下一步是启动一个特定通道或多个通道的从站。为了启动服务器以进行特定的数据库复制，请使用以下代码启动从属数据库。

$ START SLAVE thread_types FOR CHANNEL master-1;

的thread_types可以选择按照需要的thread_types。有关相同内容的更多信息，您可以参考以下链接。要开始所有源的复制，请使用以下命令。

$ START SLAVE thread_types;

7.为单个主数据库配置多个从属

到目前为止，我们已经看到了两种不同拓扑的配置。一个是单主机单从机，另一个是多主机单从机。为了配置多从属单主拓扑，需要遵循非常简单的步骤。为了配置多个从站，您需要以下内容：

具有必要复制授权的其他从站的其他用户
具有不同服务器ID的从站

满足这些要求后，即可轻松配置其他从站。要添加新的复制用户，请使用以下命令和相应的服务器IP。

$ GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'password';

如果需要，用户名和密码可以不同。如果您打算创建大量从属服务器，建议创建一个全局访问用户，以避免重复创建用户的麻烦。可以使用以下命令创建全局访问用户。

$ GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'%' IDENTIFIED BY 'password';

上面的命令将创建一个复制用户，供正在配置的任何从属使用。因此，无需记住每个从站的用户配置。下一步是配置另一个server-id 。如以上各节所述，可以使用相应服务器的配置文件进行配置。

8.复制的优缺点

实现MySQL复制的优点包括：

减轻了主机上用于数据处理操作的负载
适用于数据库密集型应用程序的负载分配更流畅
安全一致的数据备份
数据的多个副本可以更快地在每个区域中提供数据

尽管复制有很多好处，但是这是一个应该谨慎进行的过程。如果不注意，以下是一些预期的负面影响。

增加用于将数据中继到从站的主站负载。当奴隶人数超过最佳限制时，这是一个负担
异步复制不提供100％的一致性。因此，仍不能保证完美的复制品。

9.结论

本文详细介绍了有关配置MySQL复制主服务器和从服务器的信息。复制中有多种可能的配置。拓扑的选择完全取决于您要执行的任务。如上所述，复制有很多好处。但是，必须仔细并根据需要实施复制。如果从服务器数量增加，MySQL复制会增加服务器的负载。因此，通过正确配置正确数量的从站，MySQL复制有助于减少主服务器上的负载并维护数据备份。

翻译自: https://www.javacodegeeks.com/2018/06/mysql-replication-tutorial.html

danpu0978

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL复制教程

1. MySQL复制简介在大型企业系统中，数据一致性和备份至关重要。同样重要的是使用数据库副本在数据库服务器上分配负载。但是，维护备份或其他实例是一项艰巨的任务，尤其是当您尝试重复复制整个数据库时。 MySQL复制通过将主数据库的副本逐步创建到已配置的从数据库中，来帮助我们精确地解决此问题。 MySQL复制可用于始终如一地将主数据库复制到多个从数据库中。此配置具有多个优点，例如...
复制链接

扫一扫