Host = $host; $this->User = $user; $this->Password = $password; $this->Database = 'mysql'; parent::__construct(); } } class MySQLReplicationStatus { private $slave_status_query = "show slave status"; private $master_status_query = "show master status"; public $slave_master = ''; public $slave_user = ''; public $slave_master_port = ''; public $slave_log_file = ''; public $slave_position = ''; public $slave_sql_running = ''; public $slave_io_running = ''; public $slave_last_errno = ''; public $slave_last_error = ''; public $slave_seconds_behind = ''; public $using_gtid = ''; public $gtid_io_pos = ''; public $replicate_wild_table = ''; public $master_position = ''; public $master_log_file = ''; public $slave_of = ''; public $color = ''; public function __construct($host, $clusters) { $db = new DBx($clusters[$host]['user'], $clusters[$host]['password'], $clusters[$host]['ip']); $this->slave_of = $clusters[$host]['slave_of']; $this->color = $clusters[$host]['color']; if (!$db->query($this->slave_status_query)) { printf( "

Error from MySQL server %s: %s (%s) for query: %s

", $clusters[$host]['ip'], $db->Error, $db->Errno, $this->slave_status_query ); return false; } $db->next_record(); $this->slave_master = $db->f('Master_Host'); $this->slave_user = $db->f('Master_User'); $this->slave_master_port = $db->f('Master_Port'); $this->slave_log_file = $db->f('Master_Log_File'); $this->slave_position = $db->f('Read_Master_Log_Pos'); $this->slave_sql_running = $db->f('Slave_SQL_Running'); $this->slave_io_running = $db->f('Slave_IO_Running'); $this->slave_last_errno = $db->f('Last_Errno'); $this->slave_last_error = $db->f('Last_Error'); $this->slave_seconds_behind = $db->f('Seconds_Behind_Master'); $this->using_gtid = $db->f('Using_Gtid'); $this->gtid_io_pos = $db->f('Gtid_IO_Pos'); $this->replicate_wild_table = $db->f('Replicate_Wild_Do_Table'); if (!$db->query($this->master_status_query)) { printf( "

MySQL error: %s (%s) for query: %s

", $db->Error, $db->Errno, $this->master_status_query ); return; } $db->next_record(); $this->master_position = $db->f('Position'); $this->master_log_file = $db->f('File'); } } class ReplicationOverview { private $status = array(); private $cluster; private $repair = []; protected $clusters = []; public function __construct($clusters = array()) { $this->clusters = $clusters; $this->cluster = isset($_REQUEST['cluster']) ? $_REQUEST['cluster'] : ''; $this->repair['server_to_repair'] = isset($_REQUEST['server']) ? $_REQUEST['server'] : ''; $cluster_names=array_keys($this->clusters); if (!$this->cluster) { $this->cluster = $cluster_names[0]; } foreach (array_keys($this->clusters[$this->cluster]) as $key) { $this->status[$key] = new MySQLReplicationStatus($key, $this->clusters[$this->cluster]); } foreach (array_keys($this->clusters[$this->cluster]) as $key) { foreach (array_keys($this->clusters[$this->cluster]) as $key2) { if ($key == $key2) { continue; } if ($this->clusters[$this->cluster][$key2]['slave_of'] == $key) { if (!isset($this->is_master)) { $this->is_master = array(); } if (!array_key_exists($key, $this->is_master)) { $this->is_master[$key]=0; } $this->is_master[$key]++; } } } if ($this->repair['server_to_repair']) { $this->repair['master_server'] = $this->clusters[$this->cluster][$this->repair['server_to_repair']]['slave_of']; foreach (array_keys($this->clusters[$this->cluster]) as $key) { if ($this->repair['server_to_repair'] == $this->clusters[$this->cluster][$key]['slave_of']) { $this->repair['has_slave'] = $key; break; } } foreach (array_keys($this->clusters[$this->cluster]) as $key) { if ($this->clusters[$this->cluster][$key]['slave_of'] == $this->repair['master_server'] && $key != $this->repair['server_to_repair']) { $this->repair['snapshot_server'] = $key; break; } } if (!array_key_exists('snapshot_server', $this->repair)) { $this->repair['snapshot_server'] = $this->repair['master_server']; } if ($this->repair['snapshot_server'] != $this->repair['master_server']) { if (array_key_exists('active_master', $this->clusters[$this->cluster][$this->repair['snapshot_server']])) { $this->repair['snapshot_server'] = $this->repair['master_server']; } } } } public function showOverview() { print "

Available MySQL clusters: "; foreach (array_keys($this->clusters) as $key) { printf(" $key ", $_SERVER['PHP_SELF'], $key); } printf( " ", count($this->clusters[$this->cluster]), $this->cluster ); foreach (array_keys($this->clusters[$this->cluster]) as $key) { printf( '", $_SERVER['PHP_SELF'], urlencode($key), urlencode($this->cluster)); } else { printf("
Repair instructions", $_SERVER['PHP_SELF'], urlencode($key), urlencode($this->cluster)); } } print " "; print ""; foreach (array_keys($this->clusters[$this->cluster]) as $key) { print '"; } print ""; $this->printInstructions(); print "

%s
%s (%s)', $this->status[$key]->color, $key, $this->clusters[$this->cluster][$key]['ip'] ); if ($this->repair['server_to_repair'] != $key) { printf("
How to repair me
'; print ""; if ($this->status[$key]->slave_sql_running == 'No') { $sql_color="red"; } else { $sql_color="white"; } if ($this->status[$key]->slave_io_running == 'No') { $io_color="red"; } else { $io_color="white"; } if (array_key_exists($key, $this->is_master)) { printf(""); printf( "", $this->status[$key]->color, $this->status[$key]->master_log_file ); printf("", $this->status[$key]->master_position); } else { if (!$this->isPartialSlave($key)) { printf(""); printf( "", $this->status[$key]->master_log_file ); printf( "", $this->status[$key]->master_position ); } } if (!$this->isPartialSlave($key)) { printf("", $this->status[$key]->slave_of); } else { printf("", $this->status[$key]->slave_of); } printf("", $this->status[$key]->slave_master); printf("", $this->status[$key]->slave_master_port); printf( "", $this->status[$this->status[$key]->slave_of]->color, $this->status[$key]->slave_log_file ); if ($this->status[$key]->using_gtid != '') { printf("", $this->status[$key]->using_gtid); printf("", $this->status[$key]->gtid_io_pos); } if ($this->isPartialSlave($key)) { printf("", $this->status[$key]->replicate_wild_table); } printf("", $this->status[$key]->slave_position); printf("", $sql_color, $this->status[$key]->slave_sql_running); printf("", $io_color, $this->status[$key]->slave_io_running); printf("", $this->status[$key]->slave_seconds_behind); printf("", $this->status[$key]->slave_last_error); printf("", $this->status[$key]->slave_last_errno); print "
Master status
Log file%s
Position%s
Master status
Log file%s
Position%s
Slave of %s status
Partial slave of %s status
Master host%s
Master port%s
Log file%s
Using GTID%s
GTID IO Pos%s
Replicate Wild Do Table%s
Position%s
SQL thread%s
IO thread%s
Delay%s
Last error%s
Last errno%s
"; print "
"; } private function isPartialSlave($key) { return $this->status[$key]->replicate_wild_table != ''; } private function printStep($hostname, $instructions = '', $downtime = false) { $this->step++; print " $this->step "; foreach (array_keys($this->clusters[$this->cluster]) as $key) { if ($downtime && $key == $this->repair['master_server']) { $bgcolor="alert alert-error"; } if ($key==$hostname) { print "

$instructions
"; } else { if ($downtime && $key==$this->repair['master_server']) { print "

Downtime

"; } else { print ""; } } } print ""; } private function printInstructions() { if (!$this->repair['server_to_repair']) { return; } $text = sprintf( " The ssh public keys for user root must be shared between machines. Test scp as root between machines. Using %s to take snapshot.", $this->repair['snapshot_server'] ); $this->printStep($this->repair['master_server'], $text); if ($this->repair['snapshot_server'] != $this->repair['master_server']) { // lock master and wait for snapshot server to catch up with the master server $text = sprintf( " mysql -u root -p flush tables with read lock; show master status; # note file & position %s.file %s.pos # do not exit the mysql console", $this->repair['master_server'], $this->repair['master_server'] ); $this->printStep($this->repair['master_server'], $text); $text = sprintf( " mysql -u root -p show slave status; # wait until same position as %s slave stop;", $this->repair['master_server'] ); $this->printStep($this->repair['snapshot_server'], $text, true); $text = "unlock tables;"; $this->printStep($this->repair['master_server'], $text); } else { $text = sprintf( " Initial copy of the mysql files in bulk, this will lower the downtime at next step."); $this->printStep($this->repair['master_server'], $text); $text = sprintf("rsync -avzP --progress --delete /var/lib/mysql %s:/var/lib/", $this->repair['server_to_repair']); $this->printStep($this->repair['snapshot_server'], $text); $text = sprintf( " mysql -u root -p slave stop; show master status; # note file & possition %s.file %s.pos", $this->repair['snapshot_server'], $this->repair['snapshot_server'] ); $this->printStep($this->repair['snapshot_server'], $text); } $text = " /etc/init.d/monit stop /etc/init.d/mysql stop"; $this->printStep($this->repair['snapshot_server'], $text); $text = " OUTAGE starts here."; $this->printStep($this->repair['snapshot_server'], $text); $text = " /etc/init.d/monit stop /etc/init.d/mysql stop"; $this->printStep($this->repair['server_to_repair'], $text); $text = sprintf("rsync -avzP --delete --progress /var/lib/mysql %s:/var/lib/", $this->repair['server_to_repair']); $this->printStep($this->repair['snapshot_server'], $text); $text = " /etc/init.d/mysql start /etc/init.d/monit start"; $this->printStep($this->repair['snapshot_server'], $text); $text = " OUTAGE ends here."; $this->printStep($this->repair['snapshot_server'], $text); $text = sprintf( " (cd /var/lib/mysql/; rm *.info *relay-bin*) /etc/init.d/mysql start /etc/init.d/monit start mysql -u root -p show master status; # note %s.file %s.pos CHANGE MASTER TO MASTER_HOST='%s', MASTER_USER='%s', MASTER_PASSWORD='%s', MASTER_LOG_FILE='%s.file', MASTER_LOG_POS=%s.pos; slave start;", $this->repair['server_to_repair'], $this->repair['server_to_repair'], $this->clusters[$this->cluster][$this->repair['master_server']]['ip'], $this->clusters[$this->cluster][$this->repair['master_server']]['replication_user'], $this->clusters[$this->cluster][$this->repair['master_server']]['replication_password'], $this->repair['master_server'], $this->repair['master_server'] ); $this->printStep($this->repair['server_to_repair'], $text); $text=sprintf( " mysql -u root -p stop slave; CHANGE MASTER TO MASTER_HOST='%s', MASTER_USER='%s', MASTER_PASSWORD='%s', MASTER_LOG_FILE='%s.file', MASTER_LOG_POS=%s.pos; slave start;", $this->clusters[$this->cluster][$this->repair['server_to_repair']]['ip'], $this->clusters[$this->cluster][$this->repair['server_to_repair']]['replication_user'], $this->clusters[$this->cluster][$this->repair['server_to_repair']]['replication_password'], $this->repair['server_to_repair'], $this->repair['server_to_repair'] ); if ($this->clusters[$this->cluster][$this->repair['master_server']]['slave_of'] == $this->repair['server_to_repair']) { $this->printStep($this->repair['master_server'], $text); } } } ?>