MySQL slave only views

Executing

mysql> SHOW STATUS LIKE 'Slave_running';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | ON    |
+---------------+-------+
1 row in set (0.04 sec)

From the manual:

This is ON if this server is a replication slave that is connected to a replication master, and both the I/O and SQL threads are running; otherwise, it is OFF.

Caveat: If you're running MySQL prior to version 5.1.46 or 5.5.4, it's possible the slave IO thread could not be running, and the status variable indicate the slave is running. (MySQL Bug 51089)

As of MySQL 5.7, the above solution will not work by default. This is because many of the status variables from information_schema are being moved into performance_schema:

As of MySQL 5.7.6, the Performance Schema also contains system and status variable tables (see Section 23.9.13, “Performance Schema System Variable Tables”, and Section 23.9.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release. [src]

and

These Slave_xxx status variables become unavailable through SHOW STATUS:

• Slave_heartbeat_period
• Slave_last_heartbeat
• Slave_received_heartbeats
• Slave_retried_transactions
• Slave_running

Applications that use these status variables should be revised to obtain this information using the replication-related Performance Schema tables.

There are two options to get the slave_running status outside of SHOW SLAVE STATUS

1. The preferred method, as mentioned by the doc, is to update the application to pull from replication-connection-status.SERVICE_STATE and replication-applier-status.SERVICE_STATE in performance_schema:

// this is the SLAVE IO THREAD status
SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;

// this is the SLAVE SQL THREAD status
SELECT SERVICE_STATE FROM performance_schema.replication_applier_status;

NOTE: This is for single master replication. Multi-master replication should be in replication_applier_coordinator.SERVICE_STATE

2. The second option is to turn on show_compatibility_56. This is a temporary fix to get you by without modifying the application, but will go away in a future release.

If you have three or more Slaves, I have a nice suggestion: Use one of the Slaves as a Volunteer to be cloned.

Here is a Topology

MAS --+--> SLV1
      |
      +--> SLV2
      |
      +--> SLV0

Say you want to Spawn SLV3. You could use SLV0 as a Clone

• STEP01) Install MySQL Binaries on SLV3 (Same version as all other Slaves)
• STEP02) On SLV0, STOP SLAVE;
• STEP03) On SLV0, service mysql stop
• STEP04) scp -r SLV0:/etc/my.cnf SLV3:/etc/my.cnf
• STEP05) scp -r SLV0:/var/lib/mysql SLV3:/var/lib/mysql
• STEP06) On SLV0, service mysql start
• STEP07) On SLV3, chown -R mysql:mysql /var/lib/mysql
• STEP08) On SLV3, change server_id in /etc/my.cnf to be unique from all other Slaves
• STEP09) On SLV3, service mysql start

That's it.

This is the same paradigm followed by Percona XtraDB Cluster (PXC). When it comes to PXC, introducing a New Slave in PXC is as simple as adding the MasterIP to my.cnf and starting MySQL. All of the above steps are executed internally by PXC using Quorom Selection to choose which Slave becomes the Donor (a.k.a. Volunteer to be Cloned) as well as one of three methods for copying data (xtrabackup, rsync, mysqldump) This copying method is known as SST (State Snapshot Transfer).

If all of the Application-Level Data are stored in InnoDB only, you should look into using PXC. If you have a mix of InnoDB/MyISAM or all MyISAM, the above 9 steps are to be scripted by you.