Background:
We have our main databases at a data centre which are all working fine. I wanted to add a slave server at our office for backup purposes. I have copied all of the data from an existing slave at the DC (/var/log/mysql,/var/lib/mysql,/etc/mysql/my.cnf) over to the office server. The office server also has the same spec as the slaves at the DC. 4 Core Processor, 4Gb of RAM and RAID 10 SSD drives.
Problem
The slave at the office is taking ages to catch up to the master and im struggling to see why. It definetly isn't comms as when I look in the /var/log/mysql folder I can see that the latest mysql relay file has upto the second data inside it although when I run mysql slave status it is still bust reading data from a relay log which has data from the day before.
When I run show processlist nothing seems to appear so it seems like the queries are getting executed quickly and also nothing appears in the slow query log.
I have used dd to test the speed of writing to the hard drive and it is taking 6 seconds to write a gig of data to the drive where the data centre is taking 5.5 so I can't see that this is a problem either.
Here is the contents of the my.cnf file. I have done the exact same things on cloud servers at rackspace and not run into this problem before.
Any help would be much appreciated.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
skip-slave-start
log-bin
expire_logs_days = 0
max_binlog_size = 100M
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
# Charset and Collation
character-set-server = utf8
collation-server = utf8_general_ci
event_scheduler = 0
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size = 256M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 8
sort_buffer_size = 2M
read_buffer_size = 128k
read_rnd_buffer_size = 256k
join_buffer_size = 128k
auto-increment-increment = 1
auto-increment-offset = 1
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 800
max_connect_errors = 10
concurrent_insert = 2
connect_timeout = 10
wait_timeout = 180
net_read_timeout = 30
net_write_timeout = 30
back_log = 128
table_cache = 128
table_open_cache = 128
tmp_table_size = 32M
max_heap_table_size = 32M
bulk_insert_buffer_size = 32M
open-files-limit = 1024
query_cache_limit = 1M
query_cache_size = 16M
log_slow_queries = /var/log/mysql/slow.log
long_query_time = 2
server-id = 100
binlog_format = statement
log-slave-updates = ON
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_cache_size = 32K
relay_log = /var/log/mysql/mysql-relay-bin.log
sync_binlog = 0
slave_compressed_protocol = 0
innodb_data_home_dir = /var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysql
innodb_file_per_table
innodb_table_locks = true
innodb_lock_wait_timeout = 60
innodb_thread_concurrency = 4
innodb_commit_concurrency = 4
innodb_support_xa = true
innodb_buffer_pool_size = 128M
innodb_log_file_size = 2000M
innodb_additional_mem_pool_size = 8M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[myisamchk]
key_buffer = 16M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 2147483648
myisam_repair_threads = 1
myisam-recover = BACKUP