ckeding
Goto Top

MySQL Slave DB größer als die vom Master

Hallo miteinander,

ich habe derzeit ein Problem welches ich nicht nachvollziehen kann.

Ich habe einen MySQL Master und einen MySQL Slave Server.

Ich Dumpe mit folgendem Befehle alle DBs vom Master.
/usr/local/mysql/bin/mysqldump --all-databases --allow-keywords --single-transaction --quick --flush-logs --flush-privileges --master-data=2 -r /root/mysqldump.sql

Mit folgenden Befehlen spiele ich den Dump im Slave ein.
mysql -h 127.0.0.1 -u root -P 3307
SET SESSION SQL_LOG_BIN=0;
source /root/mysqldump.sql;
exit

Bis hierhin ist alles wunderbar, allerdings sind einige Datenbanken nach dem einspielen doppelt so groß wie auf dem Master.

Bsp.
TEST DB auf Master = 10GB
TEST DB auf Slave = 20GB

Diese sind aber nicht immer doppelt so groß sondern haben manchmal auch gerne die dreifache Größe?

Auf anderen Servern besteht dieses Problem nicht.

Hier noch meine Master und Slave Konfig.

Master Konfig
[client]
port                            = 3306
socket                          = /var/run/mysqld/mysqld_master.sock
secure_auth                     = 0

[mysqld]
secure_auth                     = 0
basedir                         = /usr/local/mysql
datadir                         = /usr/local/mysql/data
lc-messages-dir                 = /usr/local/mysql/share
user                            = mysql
pid-file                        = /var/run/mysqld/mysqld_master.pid
plugin-load                     = auth_ldap.so
log-error                       = /var/log/mysql/mysqld_master.err
log_warnings                    = 2
general_log                     = 0
general_log_file                = /var/log/mysql/mysqld_master_general.log
slow_query_log                  = 0
long_query_time                 = 5
log_queries_not_using_indexes   = 1
slow_query_log_file             = /var/log/mysql/mysqld_master_slow.log
socket                          = /var/run/mysqld/mysqld_master.sock
wait_timeout                    = 900
bind-address                    = 0.0.0.0
port                            = 3306
old_passwords                   = 1
explicit_defaults_for_timestamp = 1
log_bin                         = /usr/local/mysql/bin_logs/master/mysql-bin
binlog_format                   = MIXED
expire_logs_days                = 1
server_id                       = 1
sync_binlog                     = 0
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 768M
innodb_buffer_pool_instances    = 32
innodb_log_file_size            = 64M
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 2
default-storage-engine          = innodb
innodb_file_format              = Barracuda
optimizer_search_depth  = 8
key_buffer_size         = 128M
sort_buffer_size        = 1M
read_buffer_size        = 1M
join_buffer_size        = 1M
read_rnd_buffer_size    = 2M
myisam_sort_buffer_size = 16M
tmp_table_size          = 16M
max_heap_table_size     = 64M
thread_cache_size       = 32
query_cache_size        = 64M
query_cache_limit       = 512K
query_cache_type        = 1
max_allowed_packet      = 64M
thread_stack            = 256K
max_connections         = 300
max_user_connections    = 300
thread_concurrency      = 4

[safe_mysqld]
err-log                 = /usr/local/mysql/mysqld_master.err

[mysqldump]
quick
max_allowed_packet      = 32M

[mysql]
no-auto-rehash

[isamchk]
key_buffer_size         = 32M
sort_buffer_size        = 32M
read_buffer             = 2M
write_buffer            = 2M

[myisamchk]
key_buffer_size         = 32M
sort_buffer_size        = 32M
read_buffer             = 2M
write_buffer            = 2M

[mysqlhotcopy]
interactive-timeout



Slave Konfig
[client]
port                            = 3307
socket                        = /var/run/mysqld/mysqld_slave.sock

[mysqld]
basedir                 = /usr/local/mysql
datadir                 = /usr/local/mysql/data_slave
lc-messages-dir         = /usr/local/mysql/share
user                    = mysql
pid-file                = /var/run/mysqld/mysqld_slave.pid
plugin-load             = auth_ldap.so
log-error               = /var/log/mysql/mysqld_slave.err
log_warnings            = 2
long_query_time         = 5
socket                  = /var/run/mysqld/mysqld_slave.sock
wait_timeout              = 900
bind-address            = 0.0.0.0
port                            = 3307
old_passwords           = 0
explicit_defaults_for_timestamp = 0
log_bin                 = /usr/local/mysql/bin_logs/slave/mysql-bin
binlog_format           = MIXED
server_id               = 2
sync_binlog             = 0
log-slave-updates       = 1
innodb_file_per_table   = 1
innodb_buffer_pool_size = 32M
default-storage-engine  = innodb
optimizer_search_depth  = 8
key_buffer_size         = 8M
sort_buffer_size        = 1M
read_buffer_size        = 1M
join_buffer_size        = 1M
read_rnd_buffer_size    = 2M
myisam_sort_buffer_size = 16M
tmp_table_size          = 16M
max_heap_table_size     = 64M
thread_cache_size       = 32
query_cache_size        = 64M
query_cache_limit       = 512K
query_cache_type        = 1
max_allowed_packet      = 64M
thread_stack            = 256K
max_connections         = 100
max_user_connections    = 100
thread_concurrency      = 4

[safe_mysqld]
err-log                 = /usr/local/mysql/mysqld_slave.log

[mysqldump]
quick
max_allowed_packet      = 32M

[mysql]
no-auto-rehash

[isamchk]
key_buffer_size         = 32M
sort_buffer_size        = 32M
read_buffer             = 2M
write_buffer            = 2M

[myisamchk]
key_buffer_size         = 32M
sort_buffer_size        = 32M
read_buffer             = 2M
write_buffer            = 2M

[mysqlhotcopy]
interactive-timeout



Kann mir jemand erklären wie das zustande kommt?

Content-Key: 291164

Url: https://administrator.de/contentid/291164

Ausgedruckt am: 29.03.2024 um 13:03 Uhr