0

Why are these files created? They fills up the disk on my slave server (master/slave replication)

#sql-ib219492-948768181.ibd

The slave is created via Percona xtrabackup from a master backup

xtrabackup --prepare --target-dir="$mysql_restore_dir"
xtrabackup --move-back --target-dir="$mysql_restore_dir"
rm -r $mysql_restore_dir/*
chown -R mysql.mysql $mysql_dir

mysql -u root -p$slave_pass -e "RESET MASTER;\
    SET GLOBAL gtid_purged='$gtid';\
    CHANGE MASTER TO MASTER_HOST='$master_host', MASTER_USER='repl', MASTER_PASSWORD='$master_pass', MASTER_AUTO_POSITION=1;\
    START SLAVE"

my.cnf

[mysqld]

# GENERAL #
user                           = mysql
port                           = 3306
default_storage_engine         = InnoDB
pid-file                       = /var/run/mysqld/mysqld.pid
socket                         = /var/run/mysqld/mysqld.sock

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
sql_mode                       = STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
sysdate_is_now                 = 1
symbolic_links                 = 0

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
server_id                      = 2
log_bin                        = /var/lib/mysql/mysql-bin
log_slave_updates
expire_logs_days               = 1
sync_binlog                    = 1
log_bin_trust_function_creators= 1
binlog_format                  = ROW
slave_sql_verify_checksum      = 1

# REPLICATION #
gtid_mode                      = 1
enforce_gtid_consistency
replicate_do_db                = mydb
#super_read_only                = 1

# CACHES AND LIMITS #
tmp_table_size                 = 64M
max_heap_table_size            = 64M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 4096

# INNODB #
innodb_flush_method                 = O_DIRECT
innodb_log_files_in_group           = 2
innodb_log_file_size                = 512M
innodb_log_buffer_size              = 64M
innodb_flush_log_at_trx_commit      = 2
innodb_file_per_table               = 1
innodb_buffer_pool_size             = 1G
innodb_buffer_pool_instances        = 1
innodb_autoinc_lock_mode            = 2

# LOGGING #
log_error                      = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log
long_query_time                = 1

basedir                        = /usr
tmpdir                         = /tmp
lc_messages_dir                = /usr/share/mysql
explicit_defaults_for_timestamp

SHOW PROCESSLIST returns

enter image description here

3
  • That looks like a temporary table. Could you provide the output of SHOW PROCESSLIST? – jynus Feb 20 at 22:09
  • @jynus have updated the question with the results of SHOW PROCESSLIST – clarkk 2 days ago
  • on which directory this file is created ? Can you post output of SHOW GLOBAL VARIABLES ? – JYOTI RAJAI 12 hours ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.