我正在尝试在运行 MariaDB 10.1.7 64 位的两台服务器之间进行复制。我尝试复制的数据库非常庞大，有 520G，在禁用自动提交的情况下在从服务器上进行导出/导入需要 5 天时间。当我最初开始复制时，似乎从服务器将赶上进度。实际上，这看起来很有希望。遗憾的是，30 分钟后，当我再次检查从服务器时，我意识到它实际上落后了。一周后的晚上，当主服务器不那么忙时，它似乎真的又赶上了进度，但不幸的是，现在 Seconds_Behind_Master 已经快 1 周了。我将 slave_parallel_mode 设置为 proactive，在从服务器上我将 slave_parallel_threads 设置为 30，而在主服务器上将其设置为 10。innodb_flush_log_at_trx_commit 变量在从服务器上为 1，在主服务器上为 0。以下是主服务器的配置：
[client]
port = 3306
socket = /var/lib/mysql/mysqld.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysqld.sock
log-error=/var/log/mysql/logs/mysql.log
datadir=/RAID50/innodb/mysql/
init-connect = 'SET NAMES UTF8'
character-set-server = utf8
server_id=1
sync_binlog=1
back_log = 50
max_connections = 450
max_connect_errors = 9999999
table_cache = 4096
max_allowed_packet = 16M
binlog_cache_size = 1M
max_binlog_size = 100M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 128M
query_cache_limit = 4M
query_alloc_block_size = 16K
expire_logs_days=14
# Set the default table type
event_scheduler=ON
default-storage-engine=InnoDB
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 64M
log-bin=/binlog/mysql/master-bin
# binary logging format - mixed recommended
binlog_format=mixed
slow_query_log_file=/var/log/mysql/logs/slow-queries.log
slow_query_log=1
long_query_time=1
tmpdir = /tmp
slave_load_tmpdir = /var/log/mysql/tmpfs
innodb_additional_mem_pool_size = 32M
# This config file assumes a main memory of at least 8G
# innodb_buffer_pool_size = 6.5G # Default setting
innodb_buffer_pool_size = 8G
# Set this option if you would like the InnoDB tablespace files to be
# stored in another location. By default this is the MySQL datadir.
innodb_file_per_table = 1
innodb_data_home_dir = /RAID50/innodb/mysql/
innodb_file_format = Barracuda
innodb_data_file_path = ibdata1:156M;ibdata2:156M:autoextend
# innodb_data_file_path = /data1/innodb/mysql/
innodb_autoextend_increment=156M
# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
# innodb_thread_concurrency = 16
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
# make sure the log files are large enough that you don't hold up
# checkpoints when the logs rotate!
innodb_log_file_size = 200M
# Total number of files in the log group. A value of 2-3 is usually good
# enough.
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /var/log/mysql/innodb_logs
innodb_max_dirty_pages_pct = 80
# use directIO to bypass filesystem cache where possible
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 30
innodb_commit_concurrency=0
innodb_open_files=2000
[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
default-character-set = utf8
# Only allow UPDATEs and DELETEs that use keys.
#safe-updates
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 102400
这是从属设备的配置文件：
[mysqld]
datadir=/data1/mysql
socket=/var/lib/mysql/mysql.sock
# replication implementation
server-id=101
log-bin=mysql-bin
binlog_format=mixed
read-only=1
relay-log=mysql-relay-bin
log-slave-updates=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
init-connect = 'SET NAMES UTF8'
character-set-server = utf8
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_data_file_path = ibdata1:156M;ibdata2:156M:autoextend
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 80
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 30
innodb_commit_concurrency=0
innodb_open_files=2000
slave_parallel_threads = 10
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit = 102400
#
# include all files from the config directory
#
#!includedir /etc/my.cnf.d
以下是 SHOW SLAVE STATUS \G 的输出：
MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.11.25
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.005174
Read_Master_Log_Pos: 86423579
Relay_Log_File: mysql-relay-bin.000109
Relay_Log_Pos: 77202510
Relay_Master_Log_File: master-bin.005116
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: 1932
Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine
Skip_Counter: 0
Exec_Master_Log_Pos: 77202221
Relay_Log_Space: 6273918609
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: 580994
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1932
Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: aggressive
1 row in set (0.00 sec)
我确实知道无法从 mysql.gtid_slave_pos 加载复制 GTID 从属状态：表“mysql.gtid_slave_pos”在引擎中不存在，应该修复，但我不确定这是否是复制滞后的原因。有人知道我应该调整什么吗？从属服务器的 CPU 比主服务器好。
答案1
错误“mysql.gtid_slave_pos 不存在”阻止了从属服务器上的复制过程。您需要修复此错误，然后才能继续复制。
您可以尝试忽略此错误并查看会发生什么。使用从属服务器上的 mysql CLI 连接并输入以下内容：
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
答案2
您可以尝试以下步骤：
运行 mysql 升级..
mysql_upgrade -u 根 -p
如果出现类似错误，则应该全部修复
错误：指定为定义者的用户 ('appuser'@'%') 不存在错误：损坏阶段 3/6：运行'mysql_fix_privilege_tables' 错误 1813 (HY000)，第 64 行：表' mysql. innodb_table_stats' 的表空间存在。请在导入之前丢弃表空间。错误 1813 (HY000)，第 68 行：表' mysql. innodb_index_stats' 的表空间存在。请在导入之前丢弃表空间。错误 1813 (HY000)，第 136 行：表' mysql. gtid_slave_pos' 的表空间存在。请在导入之前丢弃表空间。错误 1146 (42S02)，位于第 581 行：表“mysql.innodb_index_stats”不存在错误 1146 (42S02)，位于第 584 行：表“mysql.innodb_table_stats”不存在严重错误：升级失败
然后备份所有文件\文件夹，从 mysql/mysql 文件夹中删除所有 .ibd 文件。
rm gtid_slave_pos.ibd rm innodb_index_stats.ibd innodb_table_stats.ibd
现在再次运行 mysql_upgrade -u root -p
并升级所有数据库并自动创建所有必需的文件。