网站导航
> 文章列表 > Mysql8主从同步

Mysql8主从同步

网友: 文章列表 2024-03-21 22:08:13

Mysql8主从同步

由于技术的不断更新，因此部分位置附上官方文档地址，以最新为准。

主从库复制

配置文件，Master添加server_id必须是1 到 (2^32)−1的整数。该值默认为1，也可通过设置变量形式修改SET GLOBAL server_id = 1;

server_id = 1

其它配置

log_bin=ON #写日志文件，默认开启
auto_increment_increment=1  # 逐渐自增步长
auto_increment_offset=1 # 主键自增起始位置
log_slave_updates=ON # 从其他主库同步的数据也记录二进制文件 8.0.23之前
log_replica_updates=ON # 从其他主库同步的数据也记录二进制文件 8.0.23之后
innodb_flush_log_at_trx_commit=1 #每提交一次事务同步一次二进制文件
sync_binlog=1 # 每提交一次事务同步一次二进制文件，0为不主动同步，默认1
skip_networking=OFF #默认off，允许TCP/IP连接

1.MySQL 8.0.23(不含)之前使用命令 CHANGE MASTER TO 原文地址

CHANGE MASTER TO option [, option] ... [ channel_option ]option: {MASTER_BIND = 'interface_name'| MASTER_HOST = 'host_name'| MASTER_USER = 'user_name'| MASTER_PASSWORD = 'password'| MASTER_PORT = port_num| PRIVILEGE_CHECKS_USER = {'account' | NULL}| REQUIRE_ROW_FORMAT = {0|1}| REQUIRE_TABLE_PRIMARY_KEY_CHECK = {STREAM | ON | OFF}| ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {OFF | LOCAL | uuid}| MASTER_LOG_FILE = 'source_log_name'| MASTER_LOG_POS = source_log_pos| MASTER_AUTO_POSITION = {0|1}| RELAY_LOG_FILE = 'relay_log_name'| RELAY_LOG_POS = relay_log_pos| MASTER_HEARTBEAT_PERIOD = interval| MASTER_CONNECT_RETRY = interval| MASTER_RETRY_COUNT = count| SOURCE_CONNECTION_AUTO_FAILOVER = {0|1}| MASTER_DELAY = interval| MASTER_COMPRESSION_ALGORITHMS = 'algorithm[,algorithm][,algorithm]'| MASTER_ZSTD_COMPRESSION_LEVEL = level| MASTER_SSL = {0|1}| MASTER_SSL_CA = 'ca_file_name'| MASTER_SSL_CAPATH = 'ca_directory_name'| MASTER_SSL_CERT = 'cert_file_name'| MASTER_SSL_CRL = 'crl_file_name'| MASTER_SSL_CRLPATH = 'crl_directory_name'| MASTER_SSL_KEY = 'key_file_name'| MASTER_SSL_CIPHER = 'cipher_list'| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}| MASTER_TLS_VERSION = 'protocol_list'| MASTER_TLS_CIPHERSUITES = 'ciphersuite_list'| MASTER_PUBLIC_KEY_PATH = 'key_file_name'| GET_MASTER_PUBLIC_KEY = {0|1}| NETWORK_NAMESPACE = 'namespace'| IGNORE_SERVER_IDS = (server_id_list),| GTID_ONLY = {0|1}
}channel_option:FOR CHANNEL channelserver_id_list:[server_id [, server_id] ... ]

简单的配置：

change master to 
master_host='x.x.x.x', # 主库地址
master_port= 3306, 
master_user='',
master_password='', 
master_log_file='mysql-bin.000001',  # 日志文件名
master_log_pos=157;   # 日志文件偏移量

master_log_file和master_log_pos要通过SHOW MASTER STATUS查看。
副本指定master地址之后使用命令start slave;开启同步。即可完成主从同步配置！

2.MySQL 8.0.23（含）之后 CHANGE REPLICATION SOURCE TO 原文地址

CHANGE REPLICATION SOURCE TO option [, option] ... [ channel_option ]option: {SOURCE_BIND = 'interface_name'| SOURCE_HOST = 'host_name'| SOURCE_USER = 'user_name'| SOURCE_PASSWORD = 'password'| SOURCE_PORT = port_num| PRIVILEGE_CHECKS_USER = {NULL | 'account'}| REQUIRE_ROW_FORMAT = {0|1}| REQUIRE_TABLE_PRIMARY_KEY_CHECK = {STREAM | ON | OFF | GENERATE}| ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {OFF | LOCAL | uuid}| SOURCE_LOG_FILE = 'source_log_name'| SOURCE_LOG_POS = source_log_pos| SOURCE_AUTO_POSITION = {0|1}| RELAY_LOG_FILE = 'relay_log_name'| RELAY_LOG_POS = relay_log_pos| SOURCE_HEARTBEAT_PERIOD = interval| SOURCE_CONNECT_RETRY = interval| SOURCE_RETRY_COUNT = count| SOURCE_CONNECTION_AUTO_FAILOVER = {0|1}| SOURCE_DELAY = interval| SOURCE_COMPRESSION_ALGORITHMS = 'algorithm[,algorithm][,algorithm]'| SOURCE_ZSTD_COMPRESSION_LEVEL = level| SOURCE_SSL = {0|1}| SOURCE_SSL_CA = 'ca_file_name'| SOURCE_SSL_CAPATH = 'ca_directory_name'| SOURCE_SSL_CERT = 'cert_file_name'| SOURCE_SSL_CRL = 'crl_file_name'| SOURCE_SSL_CRLPATH = 'crl_directory_name'| SOURCE_SSL_KEY = 'key_file_name'| SOURCE_SSL_CIPHER = 'cipher_list'| SOURCE_SSL_VERIFY_SERVER_CERT = {0|1}| SOURCE_TLS_VERSION = 'protocol_list'| SOURCE_TLS_CIPHERSUITES = 'ciphersuite_list'| SOURCE_PUBLIC_KEY_PATH = 'key_file_name'| GET_SOURCE_PUBLIC_KEY = {0|1}| NETWORK_NAMESPACE = 'namespace'| IGNORE_SERVER_IDS = (server_id_list),| GTID_ONLY = {0|1}
}channel_option:FOR CHANNEL channelserver_id_list:[server_id [, server_id] ... ]

简单的配置：

change replication source to
source_host='x.x.x.x', # 主库地址
source_port= 3306, 
source_user='',
source_password='', 
source_log_file='mysql-bin.000001',  # 日志文件名
source_log_pos=157;   # 日志文件偏移量

source_log_file和source_log_pos要通过SHOW MASTER STATUS查看。
副本指定master地址之后使用命令start replica;开启同步。即可完成主从同步配置！

查看binlog.000001日志文件内容show binlog events in 'binlog.000001'; ，原文地址

SHOW BINLOG EVENTS[IN 'log_name'][FROM pos][LIMIT [offset,] row_count]

从库配置主库相关信息后，开启同步命令MySQL 8.0.22及其之前 START SLAVE ，之后 START REPLICA，原文地址

START {SLAVE | REPLICA} [thread_types] [until_option] [connection_options] [channel_option]thread_types:[thread_type [, thread_type] ... ]thread_type:IO_THREAD | SQL_THREADuntil_option:UNTIL {   {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set|   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos|   SOURCE_LOG_FILE = 'log_name', SOURCE_LOG_POS = log_pos|   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos|   SQL_AFTER_MTS_GAPS  }connection_options:[USER='user_name'] [PASSWORD='user_pass'] [DEFAULT_AUTH='plugin_name'] [PLUGIN_DIR='plugin_dir']channel_option:FOR CHANNEL channelgtid_set:uuid_set [, uuid_set] ...| ''uuid_set:uuid:interval[:interval]...uuid:hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhhh:[0-9,A-F]interval:n[-n](n >= 1)

重置主库 RESET MASTER, 原文地址

RESET MASTER [TO binary_log_file_index_number]

重置从库状态(让副本忘记主库二进制日志文件中复制的位置)，MySQL 8.0.22及其之前RESET SLAVE，之后使用RESET REPLICA，原文地址

RESET {SLAVE | REPLICA} [ALL] [channel_option]channel_option:FOR CHANNEL channel

停止从库复制 STOP SLAVE | STOP REPLICA; 原文地址

STOP {SLAVE | REPLICA} [thread_types] [channel_option]thread_types:[thread_type [, thread_type] ... ]thread_type: IO_THREAD | SQL_THREADchannel_option:FOR CHANNEL channel

查看主库状态 SHOW MASTER STATUS； ，原文地址

查看从库状态 SHOW SLAVE | SHOW REPLICA，原文地址

SHOW {SLAVE | REPLICA} STATUS [FOR CHANNEL channel]

例：SHOW REPLICA STATUS\\G； \\G表示格式化
连接相关信息查看select * from mysql.slave_master_info
日志状态相关，原文地址

网络标签：

相关问题