基于二进制日志文件位置的复制配置概述
本节描述了基于二进制日志文件定位方法的MySQL服务器之间的复制，其中MySQL实例作为主(数据库更改的源)将更新和更改作为“事件”写入二 进制日志。根据记录的数据库变化，二进制日志中的信息以不同的日志格式存储。从节点被配置为从主节点读取二进制日志，并在从节点的本地 数据库上执行二进制日志中的事件。
每个从服务器接收二进制日志全部内容的副本。从服务器负责决定应该执行二进制日志中的哪些语句。除非您另行指定，否则主二进制日志中的 所有事件都在从二进制日志上执行。如果需要，可以将从服务器配置为只处理适用于特定数据库或表的事件。不能将主服务器配置为只记录某些事件
每个从服务器保存二进制日志坐标的记录:从服务器读取和处理的文件中的文件名和位置。这意味着可以将多个从服务器连接到主服务器，并执 行同一二进制日志的不同部分。因为从服务器控制这个过程，所以可以在不影响主服务器操作的情况下连接和断开各个从服务器。另外，由于每 个从服务器都在二进制日志中记录当前位置，因此有可能断开连接，重新连接，然后恢复处理。
主服务器和每个从服务器必须配置一个唯一的ID(使用server-id选项)。此外，每个从服务器必须配置有关主主机名、日志文件名和该文件中的 位置的信息。这些细节可以在从服务器上的MySQL会话中使用CHANGE MASTER TO的语句来控制。详细信息存储在从服务器的主信息存储库中，主 信息存储库可以是文件也可以是表。
设置基于二进制日志文件位置的复制
本节介绍如何设置MySQL服务器以使用基于二进制日志文件位置的复制。设置复制有许多不同的方法，具体使用哪种方法取决于设置复制的方式 ，以及主数据库中是否已经有数据。
有一些通用任务对所有设置都是通用的:
.在主服务器上，必须启用二进制日志记录并配置唯一的服务器ID。这可能需要重新启动服务器。
.在要连接到主服务器的每个从服务器上，必须配置唯一的服务器ID。这可能需要重新启动服务器。
.还可以为从服务器创建一个单独的用户，以便在与主服务器进行身份验证时读取二进制日志进行复制时使用。
.在创建数据快照或启动复制过程之前，应该在主服务器上记录二进制日志中的当前位置。在配置从服务器时需要这些信息，以便从服务器知道 在二进制日志的哪个位置开始执行事件。
.如果您已经在主服务器上有数据，并且希望使用它来同步从服务器，则需要创建数据快照将数据复制到从服务器上。您使用的存储引擎会影响 您创建快照的方式。在使用MyISAM时，必须停止处理主服务器上的语句以获得读锁，然后获取其当前二进制日志坐标并转储其数据，然后才允许 主服务器继续执行语句。如果不停止语句的执行，数据转储和主状态信息将不匹配，导致不一致。如果你使用的是InnoDB，你不需要一个读锁， 一个足够长的事务来传输数据快照就足够了。
.为从服务器配置连接到主服务器的设置，例如主机名、登录凭据、二进制日志文件名和位置。
注意:设置过程中的某些步骤需要SUPER权限。如果您没有此权限，则可能无法启用复制。
配置完基本选项后，选择您的场景:
.为不包含数据的主服务器和从服务器的新安装设置复制。
.使用现有MySQL服务器上的数据建立一个新的主服务器的复制。
.要向现有复制环境添加复制从节点。
设置复制主配置
要将主机配置为使用基于二进制日志文件位置的复制，必须启用二进制日志记录并建立唯一的服务器ID。如果还没有这样做，则需要重新启动服 务器。
必须在主服务器上启用二进制日志记录，因为二进制日志是将更改从主服务器复制到从服务器的基础。如果没有在使用log-bin选项的主机上启 用二进制日志记录，则无法进行复制。
复制组中的每个服务器必须配置唯一的服务器ID。该ID用于标识组内的各个服务器，必须是1 ~2的32次方?1之间的正整数。你如何组织和选择数 字是你的选择。
要配置二进制日志和服务器ID选项，请关闭MySQL服务器并编辑my.cnf或my.ini文件。在配置文件的[mysqld]部分中，添加log-bin和server-id 选项。如果这些选项已经存在，但是被注释掉了，取消这些选项的注释，并根据您的需要更改它们。例如，要使用日志文件名前缀mysql-bin启 用二进制日志记录，并配置服务器ID为1，使用以下行:
[mysqld] log-bin=mysql-bin server-id=1
进行更改后，重新启动服务器。
注意:
以下选项对该过程有影响:
.如果省略server-id(或将其显式设置为默认值0)，主服务器将拒绝来自从服务器的任何连接。
.为了在使用InnoDB事务的复制设置中获得最大的持久性和一致性，您应该在主my.cnf文件中使用innodb_flush_log_at_trx_commit=1和 sync_binlog=1。
.确保在复制主机上未启用跳过网络连接选项。如果禁用网络连接，则从端无法与主端通信，导致复制失败。
创建复制用户
每个从服务器使用一个MySQL用户名和密码连接到主服务器，所以在主服务器上必须有一个从服务器可以用来连接的用户帐户。任何帐户都可以 用于此操作，前提是该帐户已被授予REPLICATION SLAVE特权。您可以选择为每个从服务器创建一个不同的帐户，或者为每个从服务器使用相同 的帐户连接到主服务器。
虽然不必专门为复制创建帐户，但应该知道复制用户名和密码以明文形式存储在主信息存储库文件或表中。因此，您可能需要创建一个单独的帐 户，该帐户仅对复制过程具有特权，以尽量减少危及其他帐户的可能性。
要创建一个新帐户，请使用create USER。要向该帐户授予复制所需的特权，请使用grant语句。如果仅为复制目的创建帐户，则该帐户只需要 replication SLAVE特权。例如，要设置一个新用户repl，它可以从任何主机连接进行复制，请在主服务器上发出以下语句:
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; Query OK, 0 rows affected (0.01 sec) [root@localhost mysql]# mysql -h 10.13.13.50 -P 3306 -urepl -pslavepass mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> exit
获取复制主二进制日志坐标
要配置从服务器以在正确的点启动复制过程，您需要主服务器的二进制日志中的当前坐标。
如果master之前没有启用二进制日志，那么SHOW MASTER STATUS或mysqldump –master-data显示的日志文件名和位置值都是空的。在这种情况 下，你需要在稍后指定从服务器的日志文件和位置时使用空字符串(“)和4。
如果之前主服务器已经有二进制日志记录，可以使用下面的过程来获取主服务器的二进制日志坐标:
警告
这个过程使用了FLUSH TABLES WITH READ LOCK，阻塞了InnoDB表的COMMIT操作。
1.通过命令行客户端连接到主服务器，在主服务器上启动会话，并通过执行FLUSH TABLES WITH READ LOCK语句来清空所有表和阻塞写语句。
mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.01 sec)
警告
让发出FLUSH TABLES语句的客户端继续运行，这样读锁仍然有效。如果退出客户端，锁将被释放。
2.在主服务器上的另一个会话中，使用SHOW MASTER STATUS语句来确定当前二进制日志文件名和位置:
[root@localhost ~]# mysql -uroot -ptest12345 mysql mysql: [Warning] Using a password on the command line interface can be insecure. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW MASTER STATUS; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000183 | 326 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
“File”列显示日志文件的名称，“Position”列显示在文件中的位置。在此例中，二进制日志文件为binlog.000183，位置是326。记录这些值 。稍后在设置slave时需要它们。它们表示从服务器开始处理来自主服务器的新更新的复制坐标。
现在，您已经获得了所需的信息，使从服务器能够从正确位置的二进制日志开始读取，从而开始复制。
下一步取决于您是否在主服务器上已有数据。选择下列选项之一:
.如果在开始复制之前有需要与从服务器同步的现有数据，请保持客户端运行，以便锁保持在适当位置。这可以防止任何进一步的更改，以便复 制到从服务器的数据与主服务器保持同步。
.如果您正在建立一个新的主从复制组，您可以退出第一个会话以释放读锁
选择数据快照的方法
如果主数据库包含现有数据，则有必要将这些数据复制到每个从数据库。从主数据库转储数据有不同的方法。以下部分描述了可能的选项。
要选择适当的转储数据库的方法，请在以下选项中进行选择:
.使用mysqldump工具创建要复制的所有数据库的转储。这是推荐的方法，特别是在使用InnoDB时。
.如果数据库存储在二进制可移植文件中，则可以将原始数据文件复制到从服务器。这可能比使用mysqldump并在每个slave上导入文件更有效， 因为它跳过了在重播INSERT语句时更新索引的开销。对于InnoDB这样的存储引擎，不建议这样做。
使用mysqldump创建数据快照
使用mysqldump工具对现有主数据库中的数据创建快照。完成数据转储后，在启动复制进程之前将该数据导入从服务器。
下面的例子将所有数据库转储到一个名为dbdump.db的文件中，并包含–masterdata选项，它会自动在slave上添加CHANGE MASTER TO语句以启动 复制过程:
[root@localhost ~]# mysqldump -uroot -ptest12345 –all-databases –master-data > dbdump.db
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll dbdump.db
-rw-r–r–. 1 root root 4657877334 Jul 2 16:49 dbdump.db
[root@localhost ~]# du -sh dbdump.db
4.5G dbdump.db
注意:
如果不使用–master-data，则必须手动锁定单独会话中的所有表。
使用mysqldump工具可以从转储中排除某些数据库。如果要选择转储中包含哪些数据库，请不要使用–all-databases。选择以下选项之一:
.使用–ignore-table选项排除数据库中的所有表
.使用–atabases选项只命名那些您想要转储的数据库
要导入数据，可以将转储文件复制到从服务器，或者在远程连接到从服务器时从主服务器访问该文件。
[root@localhost ~]# mysql -h 10.13.13.43 -P 3306 -uroot -ptest1234 < dbdump.db mysql: [Warning] Using a password on the command line interface can be insecure.
使用原始数据文件创建数据快照
本节描述如何使用组成数据库的原始文件创建数据快照。对于使用具有复杂缓存或日志算法的存储引擎的表，使用这种方法需要额外的步骤来生 成完美的“时间点”快照:初始复制命令可能会忽略缓存信息和日志更新，即使您已经获得了全局读锁。存储引擎如何响应这取决于它的崩溃恢 复能力。
如果你使用的是InnoDB表，你可以使用MySQL企业版的mysqlbackup命令备份组件以生成一致性快照。该命令记录从机上要使用的快照对应的日志 名称和偏移量。
如果主服务器和从服务器的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选项。
复制不需要以下文件:
.mysql数据库相关文件。
.主信息存储库文件(如果使用)。
.主机的二进制日志文件。
.任何中继日志文件。
根据您是否使用InnoDB表，选择以下一种:
如果你使用的是InnoDB表，并且想要获得与原始数据快照最一致的结果，请在此过程中关闭主服务器，如下所示:
1.获取读锁并获取主库状态
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS;
2.在一个单独的会话中，关闭主服务器:
shell> mysqladmin shutdown
3.复制MySQL数据文件。下面的示例展示了实现此目的的常用方法。你只需要选择其中一个:
shell> tar cf /tmp/db.tar ./data shell> zip -r /tmp/db.zip ./data shell> rsync --recursive ./data /tmp/dbdata
4.重新启动主服务器。
如果您不使用InnoDB表，您可以从主服务器获取系统快照，而无需关闭服务器，如下所示:
1.获取读锁并获取主库状态
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS;
2.复制MySQL数据文件。下面的示例展示了实现此目的的常用方法。你只需要选择其中一个:
shell> tar cf /tmp/db.tar ./data shell> zip -r /tmp/db.zip ./data shell> rsync --recursive ./data /tmp/dbdata
3.在获取读锁的客户端，释放该锁:
mysql> UNLOCK TABLES;
创建了数据库的存档或副本之后，在启动从复制进程之前，将文件复制到每个从服务器。
设置复制从服务器
下面描述如何设置从服务器。在你继续之前，确保你有:
.用必要的配置属性配置MySQL主机。
.获取主库状态信息。
.在主服务器上，释放读锁。
mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
设置复制从服务器配置
每个复制从服务器必须有一个唯一的服务器ID。如果还没有这样做，从服务器设置的这一部分需要重新启动服务器。
如果从服务器ID尚未设置，或者当前值与您为主服务器选择的值冲突，请关闭从服务器并编辑配置文件的[mysqld]部分以指定唯一的服务器ID。 例如:
[root@localhost ~]# service mysqld stop Shutting down MySQL. SUCCESS! [mysql@localhost mysql]$ vi my.cnf [mysqld] server-id=2
进行更改后，重新启动服务器。
[root@localhost mysql]# service mysqld start Starting MySQL... SUCCESS!
如果要设置多个从服务器，则每个从服务器必须具有唯一的服务器id值，该值与主服务器和任何其他从服务器的服务器id值不同。
注意:
如果省略server-id(或将其显式设置为默认值0)，从服务器将拒绝连接到主服务器。
您不必在从服务器上启用二进制日志记录，就可以设置复制。但是，如果在从属服务器上启用二进制日志记录，则可以使用从属服务器的二进制 日志进行数据备份和崩溃恢复，还可以将从属服务器用作更复杂的复制拓扑的一部分。例如，这个从服务器充当其他从服务器的主库。
在从服务器上设置主配置
要设置从服务器与主服务器通信以进行复制，请使用必要的连接信息配置从服务器。为此，在从属服务器上执行以下语句，将选项值替换为与系 统相关的实际值:
mysql> CHANGE MASTER TO MASTER_HOST='10.13.13.50',MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_LOG_FILE='binlog.000183',MASTER_LOG_POS=326 ; Query OK, 0 rows affected, 2 warnings (0.14 sec)
注意:
复制不能使用Unix套接字文件。你必须能连接到主服务器MySQL服务器使用TCP/IP。CHANGE MASTER TO语句还有其他选项。例如，可以使用SSL设置安全复制。
启动从线程:
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
在执行此过程之后，从服务器连接到主服务器，并复制自拍摄快照以来在主服务器上发生的任何更新。
如果没有正确设置主服务器的server-id选项，则从服务器无法连接到主服务器。类似地，如果你没有为slave设置正确的server-id选项，你会 在slave的错误日志中得到以下错误:
Warning: You should set server-id to a non-0 value if master_host is set; we will force server id to 2, but this MySQL server will not act as a slave.
如果由于任何其他原因无法复制，您还可以在从属服务器的错误日志中找到错误消息。
从节点在其主服务器的信息存储库中存储了用户配置的关于主节点的信息。主服务器的信息存储库可以是文件的形式，也可以是表的形式，这由 –masterinfo- repository的值决定。当一个从节点使用–master-info-repository=FILE时，数据目录中会存储两个文件，分别名为 master.info和relay-log.info。相反，如果–master-info-repository=TABLE，则该信息保存在mysql数据库的master_slave_info表中。无论 哪种情况，都不要删除或编辑文件或表。始终使用CHANGE MASTER TO语句更改复制参数。从节点可以使用语句中指定的值自动更新状态文件。
注意:
主服务器的信息存储库的内容覆盖在命令行或my.cnf中指定的一些服务器选项。
一个主服务器的快照就可以满足多个从服务器的需求。要设置额外的从服务器，请使用相同的主服务器快照，并遵循上述过程的从服务器部分。
[root@localhost mysql]# pwd /mysqldata/mysql [root@localhost mysql]# ll master.info [root@localhost mysql]# ll relay-log.info -rw-r-----. 1 mysql mysql 62 Jul 4 17:50 relay-log.info
在主库中向表t9插入数据
mysql> select * from t9; Empty set (0.00 sec) mysql> desc t9; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c1 | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> insert into t9 values(1),(2),(3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from t9; +------+ | c1 | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)
在从库上执行查询看数据是否已经同步过来
mysql> select * from t9; +------+ | c1 | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.01 sec)
主库日志文件内容如下:
[root@localhost ~]# tail -f /msyqldata/mysql/mysql.err tail: cannot open ‘/msyqldata/mysql/mysql.err’ for reading: No such file or directory tail: no files remaining [root@localhost ~]# tail -f /mysqldata/mysql/mysql.err 2024-07-04T08:46:10.847512Z 9 [Warning] IP address '10.13.13.43' could not be resolved: Temporary failure in name resolution 2024-07-04T08:46:10.859391Z 9 [Note] Start binlog_dump to master_thread_id(9) slave_server(2), pos(binlog.000183, 326)
从库日志文件内容如下:
[root@localhost ~]# tail -f /msyqldata/mysql/mysql.err 2024-07-04T07:57:29.132449Z 0 [Note] InnoDB: Buffer pool(s) load completed at 240704 15:57:29 2024-07-04T08:03:48.366164Z 2 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='binlog.000183', master_log_pos= 326, master_bind=''. New state master_host='10.13.13.50', master_port= 3306, master_log_file='binlog.000183', master_log_pos= 326, master_bind=''. 2024-07-04T08:44:33.000217Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 1981125ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.) 2024-07-04T08:46:01.839437Z 3 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2024-07-04T08:46:01.840179Z 4 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0 2024-07-04T08:46:01.840283Z 4 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'binlog.000183' at position 326, relay log './localhost-relay-bin.000001' position: 4 2024-07-04T08:46:10.062847Z 3 [Note] Slave I/O thread for channel '': connected to master 'repl@10.13.13.50:3306',replication started in log 'binlog.000183' at position 326