☰

解决从属 MySQL：等待从属 SQL 线程释放足够的中继日志空间

tag-icon • tag-icon mysql mysql-replication
解决从属 MySQL：等待从属 SQL 线程释放足够的中继日志空间

我的从属 MYSQL 服务器已停止同步，状态输出如下。有人能建议我采取什么措施来解决这个问题吗？数据库大约有 400G，因此从零开始非常困难。FreeBSD 上的 MySQL 版本是 5.5.43-37。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
           Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
              Master_Host: 192.168.1.23
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: bin-log.015603
      Read_Master_Log_Pos: 1030373074
           Relay_Log_File: relay-log.015629
            Relay_Log_Pos: 804489765
    Relay_Master_Log_File: bin-log.015592
         Slave_IO_Running: Yes
        Slave_SQL_Running: No
          Replicate_Do_DB:
      Replicate_Ignore_DB: cblog_n2
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table: cblog_n1.%
  Replicate_Wild_Ignore_Table:
               Last_Errno: 1049
               Last_Error: Error 'Unknown database 'cr_debug'' on query. Default database: 'cr_debug'. Query: 'CREATE DEFINER=`root`@`192.168.1.23` PROCEDURE `attach`(IN in_debug_id INTEGER)
SQL SECURITY INVOKER
BEGIN
  SET @debug_id = in_debug_id, @timeout = 5;
  DO GET_LOCK(CAST(@debug_id AS char), 5);
END'
             Skip_Counter: 0
      Exec_Master_Log_Pos: 804489621
          Relay_Log_Space: 10737418784
          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: NULL
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 1049
           Last_SQL_Error: Error 'Unknown database 'cr_debug'' on query. Default database: 'cr_debug'. Query: 'CREATE DEFINER=`root`@`192.168.1.23` PROCEDURE `attach`(IN in_debug_id INTEGER)
SQL SECURITY INVOKER
BEGIN
  SET @debug_id = in_debug_id, @timeout = 5;
  DO GET_LOCK(CAST(@debug_id AS char), 5);
END'
  Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
1 row in set (0.00 sec)

这是我的从属配置：

[client]
socket = /tmp/mysql.sock

[mysqld]
back_log = 512
socket = /tmp/mysql.sock
bind_address = 192.168.1.72     # don't allow remote connections
#tmpdir = /app/mysql/tmpdir
tmpdir = /var/tmp/mysqltmpdir
ft_min_word_len         =3
#core-file
#transaction-isolation = READ-UNCOMMITTED
#old_passwords           =1
connect_timeout         =10
#join_buffer_size        =8M
join_buffer_size        =4M
#key_buffer_size         =7560M
#key_buffer_size        = 25G
key_buffer_size = 5G
max_allowed_packet      =2M
max_connections         =1000
max_connect_errors      =30
myisam_sort_buffer_size =512M
#read_buffer_size        =4M
read_buffer_size        =2M
read_rnd_buffer_size    =4M
#sort_buffer_size        =6M
sort_buffer_size        =2M
#table_cache             =1024
#table_cache             =2024
table_cache             =4048
thread_cache_size       =256
thread_concurrency      =16
wait_timeout            =120
query_cache_size        =12M
max_tmp_tables          =256
#tmp_table_size          =512M
tmp_table_size          =1G
#max_heap_table_size     =512M
max_heap_table_size     =1G
interactive_timeout     =120
query_cache_limit       =8M
query_cache_type        =0
query_cache_size        =128M
#skip-innodb
skip-external-locking
skip-name-resolve
myisam-recover          =FORCE,BACKUP
concurrent_insert       =2
open-files              =30000
#delay_key_write         =OFF
#event_scheduler=ON
innodb_file_per_table
innodb_data_home_dir            = /app/mysql
innodb_data_file_path           = ibdata1:10M:autoextend
#innodb_buffer_pool_size         = 5560M
#innodb_buffer_pool_size         = 60G
#innodb_buffer_pool_size = 23G
innodb_buffer_pool_size = 110G
innodb_additional_mem_pool_size = 20M
#innodb_log_file_size            = 800M
innodb_log_file_size            = 1256M
#innodb_log_buffer_size          = 32M
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 0
#innodb_flush_log_at_trx_commit  = 2
innodb_lock_wait_timeout        = 50
innodb_table_locks              = 0
innodb_support_xa               = 0
innodb_flush_method             = O_DIRECT
innodb_thread_concurrency       = 16
skip-innodb_doublewrite
innodb_buffer_pool_restore_at_startup = 1

#innodb_force_recovery = 4

long_query_time                 = 5
slow-query-log = /var/log/mysql/slow.log
log_slow_queries = /var/log/mysql/slow.log
#log-queries-not-using-indexes

## Replication
#log_bin = mysql-bin
#server_id = 1
#binlog_do_db = cblog_n1
#binlog_format = mixed
#log_bin_trust_function_creators = 1

## Master config
#log_bin = /usr/local/mysql/bin-log
#server_id = 1
#expire_logs_days = 8
#binlog_format = mixed

## Slave config
server_id = 2
relay_log = /usr/local/mysql/relay-log
read_only = 1
relay_log_space_limit = 10G
replicate-ignore-db=cblog_n2
replicate-wild-do-table=cblog_n1.%

[mysqld_safe]
core_file_size=unlimited

答案1

由于未知的数据库 cr_debug，您的复制已停止。一旦您清除该问题，日志空间应该会自行解决。根据您想要的结果，有几种方法可以解决它。

  1. 如果您希望复制该数据库，则请在从服务器上创建与主服务器具有相同表结构的数据库。根据您的情况，您可能需要采取一些措施来确保其一致性。

  2. 如果您不打算复制该数据库，则请在配置中添加“replicate-ignore-db = cr_debug”并重新启动复制。这将导致它忽略日志未处理部分中来自该数据库的所有语句。

3）如果您有理由相信只有一个语句（或少数几个）进入该数据库，那么您实际上可以使用 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 跳过该语句。但我不建议这样做，因为它非常手动并且相当混乱。

相关内容