MariaDB复制滞后

我试图让运行MariaDB 10.1.7 64位的两台服务器之间的复制工作。 我试图复制的数据库是巨大的，520G和具有禁用自动提交从站上的导出/导入需要5天。 当我最初开始复制时，似乎奴隶会赶上。 它实际上看起来很有希望。 可悲的是，30分钟后，我再次检查奴隶，我意识到，它实际上落后了。 在晚上的一个晚上，当主人不是那么忙的时候，看起来真的好像又回来了，不过现在Seconds_Behind_Master已经快一周了。 我将slave_parallel_mode设置为激进，在slave上我设置slave_parallel_threads为30，而在master上设置为10. innodb_flush_log_at_trx_commitvariables在slave上为1，在master上为0。 这是主人的configuration：

[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 

这是从站的configuration文件：

 [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。

• 如何禁用除MyISAM以外的所有MariaDB存储引擎？
• EXEC产生/ usr / libexec / mariadb-prepare-db-dir失败
• MySQL用户突然失去了一些权限
• Galera：在不locking整个服务器的情况下运行缓慢的ALTER
• HAProxy但仍然是单点故障

 mysql> STOP SLAVE; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> START SLAVE;