diff --git a/library/mysql_replication.php b/library/mysql_replication.php index 705b25d..804f235 100644 --- a/library/mysql_replication.php +++ b/library/mysql_replication.php @@ -1,410 +1,427 @@ Host = $host; $this->User = $user; $this->Password = $password; $this->Database = 'mysql'; parent::DB_Sql(); } } class MySQLReplicationStatus { private $slave_status_query = "show slave status"; private $master_status_query = "show master status"; public function MySQLReplicationStatus($host, $clusters) { $db = new DBx($clusters[$host]['user'], $clusters[$host]['password'], $clusters[$host]['ip']); $this->slave_master = ''; $this->slave_user = ''; $this->slave_master_port = ''; $this->slave_log_file = ''; $this->slave_position = ''; $this->slave_sql_running = ''; $this->slave_io_running = ''; $this->slave_last_errno = ''; $this->slave_last_error = ''; $this->slave_seconds_behind = ''; $this->using_gtid = ''; $this->gtid_io_pos = ''; $this->master_position = ''; $this->master_log_file = ''; $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'); 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(); public function ReplicationOverview($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)", $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 { printf(""); printf("", $this->status[$key]->master_log_file); printf("", $this->status[$key]->master_position); } 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); } 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
Master host%s
Master port%s
Log file%s
Using GTID%s
GTID IO Pos%s
Position%s
SQL thread%s
IO thread%s
Delay%s
Last error%s
Last errno%s
"; print "
"; } 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 /var/lib/mysql %s:/var/lib/", $this->repair['server_to_repair']); + $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); } } } ?>