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

Question

我的从MYSQL服务器已经停止了与以下状态输出的同步。有人能建议我解决这个问题的方法吗？数据库大约是400克，所以从零开始非常困难。MySQL版本在FreeBSD上为5.4.3-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

Answer 1

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

1. 如果希望复制该DB，则在与主数据库具有相同表结构的从服务器上创建它。根据您的情况，您可能需要做一些事情，以确保它是一致的。
2. 如果您不打算复制该数据库，那么向您的配置中添加一个“复制-忽略-db= cr_debug”并重新启动复制。这将导致它忽略日志中未处理部分中来自该DB的所有状态。

3)如果您有理由相信只有一条语句(或一小部分)可以转到该DB，那么实际上可以使用SET全局SQL_SLAVE_SKIP_COUNTER = 1跳过语句。不过，我不建议这样做，因为它非常手工，而且非常混乱。