TreeviewCopyright © aleen42 all right reserved, powered by aleen42

Mysql主从通过logbin

主从复制可以将一个MySQL数据库服务器(称为源)的数据复制到一个或多个MySQL数据库服务器(称为副本)。

复制默认是异步的;副本不需要永久连接才能从源接收更新。

根据配置的不同，可以复制数据库中的所有数据库、选定的数据库甚至选定的表。

主从复制的优点：

  1. 横向扩展解决方案：将负载分散到多个副本中，以提高性能。在此环境中，所有写和更新都必须在源服务器上进行。然而，读取可能发生在一个或多个副本上。该模型可以提高写的性能(因为源是专门用于更新的)，同时在不断增加的副本之间显著提高读取速度。
  2. 数据安全：因为副本可以暂停复制过程，所以可以在副本上运行备份服务，而不会损坏相应的源数据。
  3. 分析：可以在源上创建实时数据，而对信息的分析可以在副本上进行，而不会影响源的性能。
  4. 远程数据分发：可以使用复制来创建数据的本地副本供远程站点使用，而无需对源进行永久访问。

MySQL 8.0支持不同的复制方式。传统的方法基于从源的二进制日志复制事件，并要求日志文件及其位置在源和副本之间同步。基于全局事务标识符(gtid)的较新的方法是事务性的，因此不需要处理日志文件或这些文件中的位置，这大大简化了许多常见的复制任务。只要在源上提交的所有事务也应用于副本上，使用gtid的复制就可以保证源和副本之间的一致性。

MySQL中的复制支持不同类型的同步。最初的同步类型是单向异步复制，其中一个服务器作为源，而一个或多个其他服务器作为副本。在MySQL 8.0中，除了内置的异步复制外，还支持半同步复制。对于半同步复制，在返回执行事务的会话之前，在源块上执行提交操作，直到至少有一个副本确认它已经接收到事务的事件并将其记录下来。MySQL 8.0还支持延迟复制，这样副本就会故意落后于源文件至少一段指定的时间。

  • 如果需要实现同步复制的场景，建议使用NDB集群方式
  • 复制操作的使用方法取决于数据的存在和所使用的引擎类型

复制格式有两种核心类型：

  • 一种是基于语句的复制(SBR)，它复制整个SQL语句，
  • 一种是基于行的复制(RBR)，它只复制更改的行。
  • 一种混合基于复制(MBR)

复制是通过许多不同的选项和变量来控制的

配置主从

  1. 通过二进制日志文件位置设置两个或多个服务器进行复制
  2. 关于使用GTID事务设置两个或多个服务器进行复制

基于二进制日志文件位置的复制配置

基于二进制日志文件位置方法的MySQL服务器之间的复制，其中MySQL实例作为源(数据库更改发生的地方)将更新和更改作为事件写入二进制日志。根据所记录的数据库更改，二进制日志中的信息以不同的日志格式存储。副本被配置为从源读取二进制日志，并在副本的本地数据库上执行二进制日志中的事件。每个副本都接收二进制日志的全部内容的副本。副本负责决定应该执行二进制日志中的哪些语句。如果我们设置了同步的规则，否则源二进制日志中的所有事件都在副本上执行。如果需要，可以将副本配置为只处理应用于特定数据库或表的事件。

注意：不能将源配置为只记录某些事件。

每个副本都保留二进制日志坐标的记录:它从源读取和处理的文件中的文件名和位置。这意味着可以将多个副本连接到源，并执行同一二进制日志的不同部分。因为副本控制这个过程，各个副本可以与服务器连接或断开连接，而不会影响源的操作。另外，因为每个副本都记录二进制日志中的当前位置，所以副本可以断开连接，重新连接，然后恢复处理。

源和每个副本必须配置一个惟一的ID(使用服务器ID系统变量)。此外，每个副本都必须配置有关源的主机名、日志文件名以及该文件中的位置的信息。这些细节可以在MySQL会话中使用副本上的CHANGE REPLICATION SOURCE TO语句(从MySQL 8.0.23)或CHANGE MASTER TO语句(在MySQL 8.0.23之前)进行控制。详细信息存储在副本的连接元数据存储库中。

建立基于二进制日志文件位置的复制

相关步骤：

  • 在源上，必须确保启用二进制日志记录，并配置唯一的服务器ID，需要重启Mysql
  • 在连接到源的每个副本上，必须配置惟一的服务器ID，需要重启Mysql
  • 为副本创建一个单独的用户，以便在读取二进制日志进行复制时对源进行身份验证时使用
  • 在创建数据快照或启动复制过程之前，应该在源上将当前位置记录在二进制日志中。在配置副本时需要这些信息，以便副本知道在二进制日志中的何处开始执行事件。
  • 如果源上已经有数据，并且希望使用它来同步副本，则需要创建一个数据快照来将数据复制到副本。使用的存储引擎会影响创建快照的方式。在使用MyISAM时，必须停止处理源上的语句以获得读锁，然后获取其当前二进制日志坐标并转储其数据，然后才允许源继续执行语句。如果不停止语句的执行，则数据转储和源状态信息将不匹配，从而导致副本上的数据库不一致或损坏。如果使用InnoDB，你不需要读锁，一个足够长的事务就足够传输数据快照了。
  • 使用连接到源的设置配置副本，例如主机名、登录凭据和二进制日志文件名和位置
  • 根据系统的需要，在源和副本上实现特定于复制的安全措施

设置数据源库配置

要将源配置为使用基于二进制日志文件位置的复制，必须确保启用了二进制日志记录，并建立唯一的服务器ID.

复制拓扑中的每个服务器都必须配置唯一的服务器ID，可以使用服务器ID系统变量指定服务器ID。此服务器ID用于标识复制拓扑中的各个服务器，必须是1到(232)1之间的正整数.默认为1（Mysql 8.0）.可以通过下面命令进行设置：

SET GLOBAL server_id = 2;

注意，如果之前为服务器ID设置了0值(这是早期版本中的默认值)，则必须重新启动服务器，以使用新的非零服务器ID初始化源。否则，在更改服务器ID时不需要重新启动服务器，除非进行了其他需要重新启动服务器的配置更改.

源上需要二进制日志记录，因为二进制日志是将更改从源复制到其副本的基础。默认情况下启用二进制日志记录(日志库系统变量设置为ON)。log-bin选项告诉服务器为二进制日志文件使用什么基名。如果以前在源上使用——skip-log-bin选项禁用了二进制日志记录，则必须在不使用此选项的情况下重新启动服务器才能启用它。

为了在使用InnoDB的事务复制设置中获得最大的持久性和一致性，你应该在源的my.cnf文件中使用InnoDB flush log at trx commit=1和sync binlog=1。
请确保源端没有启用跳跃式组网系统变量。如果已禁用网络，则副本无法与源端通信，复制失败。

设置数据从库配置

每个副本必须有一个唯一的服务器ID，由服务器ID系统变量指定。如果要设置多个副本，则每个副本必须具有唯一的服务器id值，该值与源副本的值和任何其他副本的值不同。

在所有服务器上默认启用二进制日志记录。复制不需要启用二进制日志记录才能进行复制。但是，副本上的二进制日志记录意味着副本的二进制日志可用于数据备份和崩溃恢复。启用了二进制日志记录的副本也可以作为更复杂的复制拓扑的一部分使用。

mysql1 -》 mysql2 -》 mysql3

在这里，mysql1作为副本mysql2的源，mysql2作为副本mysql3的源。要实现这一点，mysql2必须既是源又是副本。从mysql1接收到的更新必须由mysql2记录到它的二进制日志中，以便传递给mysql3。除了二进制日志记录外，这个复制拓扑还需要启用系统变量log_replica_updates (从MySQL 8.0.26开始)或 log_slave_updates(在MySQL 8.0.26之前)。启用了副本更新后，副本将从源接收并由副本的SQL线程执行的更新写入副本自己的二进制日志。默认情况下，log_replica_updates 或log_slave_updates系统变量是启用的。

如果需要在副本上禁用二进制日志记录或副本更新日志记录，可以为副本指定--skip-log-bin和--log-replica-updates=OFF或--log-slave-updates=OFF选项。如果决定在副本上重新启用这些功能，请删除相关选项并重新启动服务器。

为复制创建一个用户

每个副本使用MySQL用户名和密码连接到源，因此源上必须有一个用户帐户，副本可以使用该帐户进行连接。用户名是由CHANGE REPLICATION SOURCE TO语句(从MySQL 8.0.23)或CHANGE MASTER TO语句(在MySQL 8.0.23之前)中的SOURCE user | MASTER user选项指定的。

任何帐户都可以用于此操作，只要它被授予了REPLICATION SLAVE特权。可以选择为每个副本创建不同的帐户，或者为每个副本使用相同的帐户连接到源。

虽然不必专门为复制创建帐户，但应该注意复制用户名和密码以纯文本的形式存储在副本的连接元数据存储库mysql中。因此，可能希望创建一个单独的帐户，该帐户只对复制过程具有特权，以尽量减少对其他帐户的损害。

要创建一个新帐户，使用create USER。要为该帐户授予复制所需的特权，请使用grant语句。如果仅为复制目的创建帐户，则该帐户只需要replication SLAVE权限

mysql> CREATE USER 'slave_test'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_test'@'%';

要使用使用缓存sha2密码插件进行身份验证的用户帐户连接到源端，您必须像17.3.1节“设置复制使用加密连接”中描述的那样建立一个安全连接，或者启用非加密连接以支持使用RSA密钥对交换密码。caching_sha2_password验证插件是MySQL 8.0创建的新用户的默认设置。如果创建或用于复制的用户帐户(由MASTER user选项指定)使用此身份验证插件，并且没有使用安全连接，必须启用基于RSA密钥对的密码交换，以成功连接。

获取复制源二进制日志 point

要配置副本以在正确的点启动复制进程，需要在其二进制日志中记录源的当前坐标

这个过程使用FLUSH TABLES WITH READ LOCK，它会阻塞InnoDB表的COMMIT操作。

复制必须在其中启动复制过程的源二进制日志坐标是该新文件的开始位置，该新文件是在复制的二进制日志索引文件中列出的文件之后的源上的下一个二进制日志文件。

  1. 通过命令行客户端连接到源上启动会话，并通过执行flush tables with READ LOCK语句刷新所有表和阻塞写语句
mysql> FLUSH TABLES WITH READ  LOCK;
  1. 在源上的另一个会话中，使用SHOW MASTER STATUS语句来确定当前二进制日志文件的名称和位置 File列显示日志文件的名称，Position列显示文件中的位置。设置副本时需要。表示复制坐标，副本应该从这个坐标开始处理来自源的新更新。

如果源已经在禁用二进制日志记录的情况下运行，则通过SHOW MASTER STATUS或mysqldump --master-data显示的日志文件名和位置值为空。在这种情况下，稍后指定源的二进制日志文件和位置时需要使用的值是空字符串(")和4。

  1. 如果在开始复制之前有需要与副本同步的现有数据，需要保持客户端运行，以便锁保持在适当的位置。这防止了任何进一步的更改，以便复制到副本的数据与源同步。
  2. 如果正在设置一个新的源和副本组合，您可以退出第一个会话来释放读锁

选择数据快照的方法

如果源数据库包含现有数据，则需要将该数据复制到每个副本。从源数据库中转储数据有不同的方法：

  1. 推荐的方法，特别是在使用InnoDB时,使用mysqldump工具创建要复制的所有数据库的转储。

要创建现有源数据库中数据的快照，使用mysqldump工具。完成数据转储之后，在开始复制过程之前，将该数据导入到副本中。

mysqldump --all-databases --master-data > dbdump.db

将所有数据库转储到一个名为dbdump.db的文件中，并包含--master-data选项，它自动追加CHANGE REPLICATION SOURCE TO| CHANGE MASTER TO语句需要在副本上启动复制过程

可以使用mysqldump工具从转储中排除某些数据库。如果希望选择转储中包含哪些数据库，请不要使用--all-databases。

选择以下选项之一:

  1. 使用--ignore-table选项排除数据库中的所有表。
  2. 只指定那些希望使用--databases选项转储的数据库的名称

如果不使用--master-data，则需要手动锁定单独会话中的所有表

  1. 如果数据库存储在二进制可移植文件中，则可以将原始数据文件复制到副本中。这比使用mysqldump并在每个副本上导入文件更有效，因为它跳过了在重放INSERT语句时更新索引的开销。对于InnoDB等存储引擎，不建议这样做。

如何使用组成数据库的原始文件创建数据快照。在使用具有复杂缓存或日志算法的存储引擎的表中使用此方法需要额外的步骤来生成完美的时间点快照:初始复制命令可能会遗漏缓存信息和日志更新，即使已经获得了全局读锁。存储引擎如何响应这取决于它的崩溃恢复能力.

如果使用InnoDB表，可以使用MySQL Enterprise Backup组件中的mysqlbackup命令生成一致的快照。此命令记录要用于副本的快照对应的日志名称和偏移量。MySQL企业备份是一个商业产品.

如果源和副本对 ft_stopword_file, ft_min_word_len,或ft_max_word_len 有不同的值，并且正在复制具有全文索引的表，则此方法也不能可靠地工作.

使用冷备份技术来获得一个可靠的InnoDB表的二进制快照:慢速关闭MySQL服务器，然后手动复制数据文件。

当MySQL数据文件存在于单个文件系统中时，要创建MyISAM表的原始数据快照，可以使用标准的文件复制工具(如cp或copy)、远程复制工具(如scp或rsync)、归档工具(如zip或tar)或文件系统快照工具(如dump)。如果只复制某些数据库，则只复制与这些表相关的文件。对于InnoDB，所有数据库中的所有表都存储在系统表空间文件中，除非启用了innodb_file_per_table 选项

复制以下文件不是必须的：

  1. 副本的连接元数据存储库文件master.info，如果使用;现在已不推荐使用此文件
  2. 源的二进制日志文件，除了二进制日志索引文件
  3. 任何中继日志文件。

如果使用InnoDB表，并且想通过原始数据快照获得最一致的结果，那么在这个过程中关闭源服务器。

  1. 获取读锁并获取源的状态

  2. 在单独的会话中，关闭源服务器

mysqladmin shutdown
  1. 复制MySQL数据文件
tar cf /tmp/db.tar ./data

zip -r /tmp/db.zip ./data

rsync --recursive ./data /tmp/dbdata
  1. 重启源服务器

如果你不使用InnoDB表，你可以在不关闭服务器的情况下从源获取系统的快照

  1. 获取读锁并获取源的状态

  2. 复制MySQL数据文件

tar cf /tmp/db.tar ./data

zip -r /tmp/db.zip ./data

rsync --recursive ./data /tmp/dbdata
  1. 在获取读锁的客户端上，释放锁
mysql> UNLOCK TABLES;
  1. 使用MySQL服务器的克隆插件将所有数据从一个现有的副本转移到一个克隆.

设置副本

设置副本之前，需要确认如下步骤：

  1. 为源配置了必要的配置属性

  2. 已获取源状态信息，或在为数据快照关闭期间生成的源二进制日志索引文件的副本

  3. 在源上释放读锁

mysql> UNLOCK TABLES;
  1. 在副本上，编辑MySQL配置
使用新源和副本设置复制

如果没有要导入的前一个数据库的快照，则配置副本从新的源启动复制

  1. 启动复制副本
  2. 在副本上执行CHANGE REPLICATION SOURCE TO | CHANGE MASTER TO语句来设置源配置

在每个副本上执行这些副本设置步骤：

  1. 如果正在设置新的数据库服务器，但有来自不同服务器的数据库的现有转储，希望将其加载到复制配置中，那么也可以使用此方法。通过将数据加载到新的源中，数据将自动复制到副本中。

  2. 如果正在使用来自另一个现有数据库服务器的数据设置新的复制环境以创建新的源，那么在新源上运行从该服务器生成的转储文件。数据库更新将自动传播到副本。

$> mysql -h source < fulldb.dump
设置复制与存在的数据

在使用现有数据建立复制时，请在开始复制之前将快照从源端转移到副本。将数据导入副本的过程取决于在源上创建数据快照的方式。

作为一种创建新副本的替代方法，MySQL Server的克隆插件可以用来将所有数据和复制设置从现有副本转移到克隆

  1. 如果使用MySQL Server的克隆插件从现有的副本创建一个克隆，数据已传输，否则，导入数据到副本，可以使用如下方式：

a. 如果使用mysqldump，启动复制服务器，确保复制不会通过使用--skip-slave-start选项启动，或者从MySQL 8.0.24中启动--skip-slave-start系统变量。然后导入转储文件。

$> mysql < fulldb.dump

​ b. 如果使用原始数据文件创建快照，请将数据文件解压缩到副本的数据目录中：

$> tar xvf dbdump.tar

可能需要设置文件的权限和所有权，以便副本服务器可以访问和修改它们。然后启动副本服务器，确保不使用--skip-slave-start选项或MySQL 8.0.24中的skip_slave_start系统变量启动复

  1. 使用来自源的复制坐标配置副本。这将告诉副本二进制日志文件以及需要在文件中开始复制的位置。另外，使用源的登录凭据和主机名配置副本。CHANGE REPLICATION SOURCE TO |CHANGE MASTER TO语句
  2. 通过发出Start REPLICA(或MySQL 8.0.22之前的Start SLAVE)语句启动复制线程。

执行此过程后，副本将连接到源，并复制自创建快照以来源上发生的所有更新。如果由于任何原因无法复制，则会向副本的错误日志发出错误消息.

副本使用其连接元数据存储库和应用程序元数据存储库中记录的信息来跟踪它处理了多少源的二进制日志。从MySQL 8.0开始，默认情况下，这些存储库是MySQL数据库中名为slave_master_info和slave_relay_log_info的表。切勿删除或编辑这些表，除非确切地知道自己在做什么，并完全理解其含义。即使在这种情况下，也最好使用CHANGE REPLICATION SOURCE TO | CHANGE MASTER TO语句来更改复制参数。副本使用语句中指定的值自动更新复制元数据存储库。

设置副本的源配置

若要设置副本以与复制源通信，请使用必要的连接信息配置副本。在副本上执行CHANGE REPLICATION SOURCE To语句(从MySQL 8.0.23)或CHANGE MASTER To语句(在MySQL 8.0.23之前)

mysql> CHANGE MASTER TO
->     MASTER_HOST='source_host_name',
->     MASTER_USER='replication_user_name',
->     MASTER_PASSWORD='replication_password',
->     MASTER_LOG_FILE='recorded_log_file_name',
->     MASTER_LOG_POS=recorded_log_position;

Or from MySQL 8.0.23:
mysql> CHANGE REPLICATION SOURCE TO
->     SOURCE_HOST='source_host_name',
->     SOURCE_USER='replication_user_name',
->     SOURCE_PASSWORD='replication_password',
->     SOURCE_LOG_FILE='recorded_log_file_name',
->     SOURCE_LOG_POS=recorded_log_position;

注意：复制不能使用Unix socket files，必须使用TCP/IP链接到源Mysql Server

CHANGE REPLICATION SOURCE TO | CHANGE MASTER TO语句还有其他选项。如：可以使用SSL设置安全复制。获取选项的完整列表，以及关于字符串值选项允许的最大长度的信息。

向复制环境中添加副本

可以在不停止源服务器的情况下向现有复制配置添加另一个副本。为此，可以通过复制现有副本的数据目录，并为新副本提供不同的服务器ID(这是用户指定的)和服务器UUID(这是在启动时生成的)来设置新的副本。

1.不进行克隆而复制现有副本

停止现有的副本并记录副本状态信息，特别是源二进制日志文件和中继日志文件的位置

mysql> STOP SLAVE;
mysql> SHOW SLAVE STATUS\G
Or from MySQL 8.0.22:
mysql> STOP REPLICA;
mysql> SHOW REPLICA STATUS\G
  1. 关闭现有复制副本
$> mysqladmin shutdown
  1. 将数据目录从现有副本复制到新副本，包括日志文件和中继日志文件。可以通过使用tar或WinZip创建归档文件，或通过使用cp或rsync等工具执行直接复制来实现这一点。

注意：

在复制之前，请验证与现有副本相关的所有文件实际上都存储在data目录中。例如，InnoDB系统表空间、撤消表空间和重做日志可能存储在另一个位置。InnoDB表空间文件和每表一个文件的表空间可能已经在其他目录中创建。副本的二进制日志和中继日志可能位于数据目录之外的它们自己的目录中。检查为现有副本设置的系统变量，查找已指定的任何替代路径。如果找到了，也复制这些目录。

在复制期间，如果复制元数据存储库使用了文件，确保还将这些文件从现有副本复制到新副本。如果存储库使用了表(这是MySQL 8.0的默认设置)，则表位于data目录中。

复制之后，从新副本上的数据目录的副本中删除auto.cnf文件，以便新副本以不同的生成的服务器UUID启动。服务器UUID不能重复。

071118 16:44:10 [Warning] Neither --relay-log nor --relay-log-index were used; so
replication may break when this MySQL server acts as a replica and has his hostname
changed!! Please use '--relay-log=new_replica_hostname-relay-bin' to avoid this problem.
071118 16:44:10 [ERROR] Failed to open the relay log './old_replica_hostname-relay-bin.003525'
(relay_log_pos 22940879)
071118 16:44:10 [ERROR] Could not find target log during relay log initialization
071118 16:44:10 [ERROR] Failed to initialize the master info structure

如果没有指定relay_log系统变量，就会出现这种情况，因为中继日志文件在文件名中包含主机名。如果不使用relay_log_index系统变量，中继日志索引文件也是如此。

要避免此问题，需要在新副本上使用与在现有副本上使用的相同的中继日志值。如果没有在现有副本上显式设置此选项，则使用现有副本hostname-relay-bin。如果不可能，则将现有副本的中继日志索引文件复制到新副本，并在新副本上设置relay_log_index系统变量，以匹配现有副本上使用的变量。如果没有在现有副本上显式设置此选项，则使用现有副本hostname-relay-bin.index。或者，如果在完成本节剩下的步骤之后已经尝试启动新的副本，并且遇到了类似于前面描述的错误，那么执行以下步骤:

  1. 如果还没有这样做，那么在新的副本上发出STOP REPLICA。如果已经再次启动了现有的副本，那么也要在现有的副本上发出STOP replica。

  2. 将现有副本的中继日志索引文件的内容复制到新副本的中继日志索引文件中，确保覆盖文件中已经存在的任何内容。

  3. 复制完成后，重新启动现有副本。

  4. 在新副本上，编辑配置并为新副本提供一个惟一的服务器ID(使用服务器ID系统变量)，该服务器ID不被源或任何现有副本使用。
  5. 启动新的复制服务器，通过指定--skip-slave-start选项，或从MySQL 8.0.24中指定跳过从启动系统变量，确保复制尚未启动。使用性能模式复制表或发出SHOW REPLICA STATUS命令来确认新副本与现有副本相比具有正确的设置。还要显示服务器ID和服务器UUID，并验证它们对于新副本是正确的和惟一的。
  6. 通过发出start replica语句启动副本线程。新的副本现在使用其连接元数据存储库中的信息来启动复制过程。

results matching ""

    No results matching ""