Linux – MySQL – Why would SHOW SLAVE HOSTS cause a binlog dump

linuxMySQLmysql-replication

We're getting loads of binlog files in our MySQL 5.0.x. We have a normal master/slave replication thing going with 1 master, 1 slave. Looking at /var/log/mysql.log, nearly 90% of the time the replicator connects and does a SHOW SLAVE HOSTS causes a bin log dump.

For example:

           7020 Query       SHOW SLAVE HOSTS
           7020 Binlog Dump Log: 'mysql-bin.029634'  Pos: 13273

However when I do a SHOW SLAVE HOSTS on the mysql myself, I get no results.

Occasionally when the replicator does a SHOW SLAVE HOSTS, mysql will hang for hours. I see nothing in the /var/log/syslog at the same time…

What's going on here? How can I debug this more?

For the record the MySQL master and slave servers are ubuntu dapper.

Best Answer

Some things to check:

Disk space for binlogs.
Check ifconfig for network errors. I've seen bad packets force replication off track.
check SHOW SLAVE STATUS and /etc/my.cnf to make sure that the MySQL server IDs didn't become the same. I've seen this happen and it can be terrible for replication.
The mysql log for trouble writing logs or tables. If they become owned by root, that could spell trouble.
Monitor your server temperatures using lm-sensors if you can. A failing fan will raise system temperature and prematurely degrade memory, disk, and raid controller performance. I've seen raid controllers fail more frequently than disks on hot systems.

I would also schedule downtime for the servers to run an CHECK TABLE; REPAIR TABLE; OPTIMIZE TABLE as appropriate. I've seen silence in MySQL logs where a table was dammaged and the error messages were getting ignored form the application.

I've seen bad memory corrupt tables. During scheduled downtime, run a physical memory test. Memory seats on the motherboard can also go bad, too.

I suspect you might have a network change that started this, and it could be something as irritating as a change to DNS lookups. Your /etc/resolv.conf might now be blank, for instance, and it could be failing to resolve your slave address if it's not an IP address.

Related Solutions

Mysql – thesql master-slave setup with synchronous replication

The normal MySQL replication is necessarily asynchronous, there are no distributed locks and the master is never blocked waiting for slaves. This has advantages and disadvantages.

DRBD + heartbeat is the standard solution to avoid data loss in the case of permanent loss of the master.

You can use DRBD + heartbeat underneath normal replication which allows for read-scaling or replicas off-site as well as very high durability.

As DRBD does synchronous replication at the block level, you need the network to have very low latency to get good performance (gigE is ok, custom low latency interconnects are better if you have very high performance requirements).

Mysql – thesql – SQL slave thread is not applying changes

There are some quirks to look out for in MySQL Replication

1) Use of replicate-do-db and replicate-ignore-db at the same time

There is flowchart on the page to show the order of processing. Personally, I do not use replicate-do-db and replicate-ignore-db at the same time. I use one or the other. If other slaves do not have this same issue, then rule this out.

2) Doing LOAD DATA INFILE

The way MySQL Replication handles it is appalling. Whenever a LOAD DATA INFILE is performed in a Master, the entire input file is deposited in the Master's binary logs. The slave gathers in the input file in its relay logs. The slave rematerializes the data file in the /tmp folder and then executes the LOAD DATA INFILE on the slave. This is not counted as repliction lag during this process. As a MySQL DBA, I know this works, but that's tacky !!!

3) Slave IO Thread Communication Breakdown

Sometimes, due to firewall changes, network routing, or some other networking anomaly, the Slave IO thread may simply stop getting entries to populate its relay logs. You may also want to check that the Slave IO thread is visible in the processlist of the Master. To make sure your slave's IO thread is alive, simply do the following on all slaves:

SHOW SLAVE STATUS\G

Watch for the Relay_Log_Space. It should be growing. If it stops growing, MySQL may simply freeze without an error for another crazy reason, which leads to suggestion #4.

4) Slave is Out of Diskspace

I wrote a post about how MySQL freezes when performing a MyISAM operation. MySQL uses MyISAM tables as temp tables. Check yout default tmp table directory (tmpdir variable in MySQL)

I hope these suggestions help !!!

Best Answer

Related Solutions

Mysql – thesql master-slave setup with synchronous replication

Mysql – thesql – SQL slave thread is not applying changes

Related Topic