public function switchMaster($servers)
{
$this->view = false;
$slave = $this->di['db']->sql(str_replace('-', '_', $servers[0]));
$master = $this->di['db']->sql(str_replace('-', '_', $servers[1]));
// first test that the master have log_slave_updates = ON
$log_slave_updates = $master->getVariables("log_slave_updates");
if ($log_slave_updates !== 'ON') {
throw new \Exception("[ERROR] PMACLI-001 : log_slave_updates must be unabled on new master : '" . $servers[1] . "'");
}
$MS = new MasterSlave();
$MS->setInstance($slave);
$thread_slave = $MS->isSlave();
$slave_master_host = array();
if ($thread_slave) {
foreach ($thread_slave as $thread) {
$conn = empty($thread['Connection_name']) ? '' : " '" . $thread['Connection_name'] . "'";
$slave_master_host[$thread['Master_Host']] = $conn;
}
} else {
throw new \Exception("[ERROR] PMACLI-002 : the server should configured as slave : '" . $servers[0] . "'");
}
$MS->setInstance($master);
$thread_master = $MS->isSlave();
$master_master_host = array();
if ($thread_master) {
foreach ($thread_master as $thread) {
$conn = empty($thread['Connection_name']) ? '' : " '" . $thread['Connection_name'] . "'";
$master_master_host[$thread['Master_Host']] = $conn;
}
} else {
throw new \Exception("[ERROR] PMACLI-002 : the server should configured as slave : '" . $servers[1] . "'");
}
$cmp = array_intersect_key($slave_master_host, $master_master_host);
if (count($cmp) !== 1) {
throw new \Exception("[ERROR] PMACLI-003 : the servers must have the same master");
}
$ip_of_master = array_keys($cmp)[0];
//stop du slave
$sql = "STOP SLAVE" . $slave_master_host[$ip_of_master] . ";";
echo $servers[0] . "> " . $sql . "\n";
$slave->sql_query($sql);
$MS->setInstance($slave);
$thread_slave = $MS->isSlave();
foreach ($thread_slave as $thread) {
if ($thread['Master_Host'] == $ip_of_master) {
$SLAVE_MASTER_LOG_FILE = $thread['Master_Log_File'];
$SLAVE_MASTER_LOG_POS = $thread['Exec_Master_Log_Pos'];
}
}
sleep(1);
//stop slave n°2 (future master)
$sql = "STOP SLAVE" . $master_master_host[$ip_of_master] . ";";
echo $servers[1] . "> " . $sql . "\n";
$master->sql_query($sql);
$MS->setInstance($master);
$thread_master = $MS->isSlave();
foreach ($thread_master as $thread) {
if ($thread['Master_Host'] == $ip_of_master) {
$MASTER_MASTER_LOG_FILE = $thread['Master_Log_File'];
$MASTER_MASTER_LOG_POS = $thread['Exec_Master_Log_Pos'];
}
}
if ($MASTER_MASTER_LOG_FILE !== $SLAVE_MASTER_LOG_FILE || $SLAVE_MASTER_LOG_POS > $MASTER_MASTER_LOG_POS) {
throw new \Exception("[ERROR] PMACLI-005 : Error the new master is behind the slave");
}
$tab = new Table(1);
$tab->addHeader(array("name", "IP", "port", "Connection_name", "Master_Log_File", "Master_Log_Pos"));
$param['master'] = $master->getParams();
$param['slave'] = $slave->getParams();
$tab->addLine(array($servers[0], $param['slave']['hostname'], empty($param['slave']['port']) ? 3306 : $param['slave']['port'], $master_master_host[$ip_of_master], $SLAVE_MASTER_LOG_FILE, $SLAVE_MASTER_LOG_POS));
$tab->addLine(array($servers[1], $param['master']['hostname'], empty($param['master']['port']) ? 3306 : $param['master']['port'], $slave_master_host[$ip_of_master], $MASTER_MASTER_LOG_FILE, $MASTER_MASTER_LOG_POS));
$msg = $tab->display();
echo $msg;
$sql = "SHOW MASTER STATUS";
echo $servers[1] . "> " . $sql . "\n";
$MS->setInstance($master);
$master_status = $MS->isMaster();
$tab2 = new Table(1);
$header = array();
$var = array();
foreach ($master_status as $key => $value) {
$header[] = $key;
$var[] = $value;
}
$master_file = $var[0];
$master_position = $var[1];
$tab2->addHeader($header);
$tab2->addLine($var);
echo $tab2->display();
$sql = "START SLAVE" . $slave_master_host[$ip_of_master] . " UNTIL MASTER_LOG_FILE='" . $MASTER_MASTER_LOG_FILE . "', MASTER_LOG_POS=" . $MASTER_MASTER_LOG_POS . ";";
echo $servers[0] . "> " . $sql . "\n";
$slave->sql_query($sql);
$MS->setInstance($slave);
do {
$thread_slave = $MS->isSlave();
foreach ($thread_slave as $thread) {
if ($thread['Master_Host'] == $ip_of_master) {
$SLAVE_MASTER_LOG_FILE = $thread['Master_Log_File'];
$SLAVE_MASTER_LOG_POS = $thread['Exec_Master_Log_Pos'];
}
}
$sql = "SHOW SLAVE" . $slave_master_host[$ip_of_master] . " STATUS;";
echo $servers[0] . "> " . $sql . "\n";
$tab3 = new Table(1);
$tab3->addHeader(array("Master_Log_File", "Exec_Master_Log_Pos"));
$tab3->addLine(array($SLAVE_MASTER_LOG_FILE, $SLAVE_MASTER_LOG_POS));
echo $tab3->display();
sleep(1);
} while ($MASTER_MASTER_LOG_POS != $SLAVE_MASTER_LOG_POS);
$sql = "STOP SLAVE" . $master_master_host[$ip_of_master] . ";";
echo $servers[0] . "> " . $sql . "\n";
$slave->sql_query($sql);
$sql = "CHANGE MASTER" . $slave_master_host[$ip_of_master] . " TO MASTER_HOST = '" . $param['master']['hostname'] . "', MASTER_LOG_FILE='" . $master_file . "', MASTER_LOG_POS=" . $master_position . ";";
echo $servers[0] . "> " . $sql . "\n";
$slave->sql_query($sql);
$sql = "START SLAVE" . $slave_master_host[$ip_of_master] . ";";
echo $servers[1] . "> " . $sql . "\n";
$master->sql_query($sql);
$sql = "START SLAVE" . $master_master_host[$ip_of_master] . ";";
echo $servers[0] . "> " . $sql . "\n";
$slave->sql_query($sql);
//$slave->sql_query($sql);
}
public function replication()
{
$this->layout_name = false;
$this->view = false;
$MS = new MasterSlave();
$tab = new Table();
$tab->addHeader(array("Top", "Server", "IP", "Version", "M", "S", "IO", "SQL", "Behind", "File", "Position", "Current file", "Position"));
$ip = array();
$masters = array();
$i = 0;
foreach ($this->di['db']->getAll() as $db) {
$i++;
$server_config = $this->di['db']->sql($db)->getParams();
$MS->setInstance($this->di['db']->sql($db));
$master = $MS->isMaster();
$slave = $MS->isSlave();
$is_master = $master ? "â– " : "";
$is_slave = $slave ? "â– " : "";
$sql = "SHOW GLOBAL VARIABLES LIKE 'version'";
$res = $this->di['db']->sql($db)->sql_query($sql);
$data = $this->di['db']->sql($db)->sql_fetch_array($res, MYSQLI_ASSOC);
if (strpos($data['Value'], "-")) {
$version = strstr($data['Value'], '-', true);
} else {
$version = $data['Value'];
}
if ($slave) {
$io = $slave['Slave_IO_Running'] === 'Yes' ? "OK" : Color::getColoredString("KO", "grey", "red", "bold");
$sql = $slave['Slave_SQL_Running'] === 'Yes' ? "OK" : Color::getColoredString("KO", "grey", "red", "bold");
$time_behind = $slave['Seconds_Behind_Master'];
} else {
$io = "";
$sql = "";
$time_behind = "";
}
$master_binlog = $slave ? $slave['Master_Log_File'] : "";
$master_postion = $slave ? $slave['Exec_Master_Log_Pos'] : "";
$file = $master ? $master['File'] : "";
$position = $master ? $master['Position'] : "";
$addr = !empty($server_config['port']) && is_numeric($server_config['port']) ? $server_config['hostname'] . ":" . $server_config['port'] : $server_config['hostname'];
$ip[$addr] = array($i, $db, $addr, $version, $is_master, $is_slave, $io, $sql, $time_behind, $file, $position, $master_binlog, $master_postion);
if ($slave) {
$masters[$slave['Master_Host']][] = $addr;
}
}
//debug($masters);
$color = "purple";
$i = 1;
$j = 0;
$colors = array("purple", "yellow", "blue", "cyan", "red", "green");
$nb_color = count($colors);
foreach ($masters as $master => $slaves) {
$color = $colors[$j];
$j++;
$j = $j % $nb_color;
$ip[$master][0] = $i;
$ip[$master][1] = Color::getColoredString("â– ", $color) . $ip[$master][1];
$tab->addLine($ip[$master]);
unset($ip[$master]);
foreach ($slaves as $id_slave => $slave) {
$i++;
$ip[$slave][0] = $i;
if ($cpt = count($masters[$master]) == 1) {
$ip[$slave][1] = Color::getColoredString("�■", $color) . $ip[$slave][1];
} else {
$ip[$slave][1] = Color::getColoredString("�■", $color) . $ip[$slave][1];
}
$tab->addLine($ip[$slave]);
unset($masters[$master][$id_slave]);
unset($ip[$slave]);
}
$i++;
}
foreach ($ip as $server) {
$server[1] = "â– " . $server[1];
$server[0] = $i;
$tab->addLine($server);
$i++;
}
/*
* $tab->addLine(array($i, $db, $addr, $version, $is_master, $is_slave, $io, $sql, $time_behind,
$file, $position, $master_binlog, $master_postion, "Log space"));
*/
/* $tab->addLine(array($server_name, $server_config['hostname'], $version, $is_master, $is_slave, $io, $sql, $time_behind,
$file, $position, $master_binlog,$master_postion, "Log space"));
*/
echo PHP_EOL . $tab->display();
//echo Color::printAll();
}
/**
*
* @author Aurélien LEQUOY <*****@*****.**>
* @license GNU/GPL
* @license http://opensource.org/licenses/GPL-3.0 GNU Public License
* @param void
* @return void
* @description connect to each MySQL server and get status of all replication thread to save in database
* @access public
* @package PmaCli
* @See Glial\Neuron\Controller\PmaCli\replicationDrawGraph
* @since 3.0 First time this was introduced.
* @version 3.0
*/
public function replicationUpdate()
{
$this->layout_name = false;
$this->view = false;
$default = $this->di['db']->sql(DB_DEFAULT);
$MS = new MasterSlave();
$ip = array();
$masters = array();
$i = 0;
/*
try {
$default->sql_query('SET AUTOCOMMIT=0;');
$default->sql_query('START TRANSACTION;');
*/
$sql = "DELETE FROM mysql_replication_stats";
$default->sql_query($sql);
$sql = "DELETE FROM `link__mysql_cluster__mysql_server`";
$default->sql_query($sql);
$sql = "DELETE FROM `mysql_replication_stats`";
$default->sql_query($sql);
$sql = "ALTER TABLE mysql_replication_stats AUTO_INCREMENT = 1";
$default->sql_query($sql);
$sql = "ALTER TABLE mysql_replication_thread AUTO_INCREMENT = 1";
$default->sql_query($sql);
$sql = "ALTER TABLE mysql_cluster AUTO_INCREMENT = 1";
$default->sql_query($sql);
$sql = "ALTER TABLE link__mysql_cluster__mysql_server AUTO_INCREMENT = 1";
$default->sql_query($sql);
$sql = "SELECT * FROM mysql_server";
$res50 = $default->sql_query($sql);
while ($ob50 = $default->sql_fetch_object($res50)) {
$db = $ob50->name;
$i++;
echo "[" . date("Y-m-d H:i:s") . "] Try to connect to : " . $db . "\n";
$server_config = $this->di['db']->getParam($db);
$server_on = 1;
$server_config['port'] = empty($server_config['port']) ? 3306 : $server_config['port'];
$dblink = $this->di['db']->sql($db);
if ($dblink->is_connected) {
$MS->setInstance($dblink);
$master = $MS->isMaster();
$slave = $MS->isSlave();
/*
$client = new \crodas\InfluxPHP\Client(
"url", 8086, "root", "root"
);
$influxDB = $client->mysqlmetrics;
$sql = "SELECT * FROM information_schema.GLOBAL_STATUS ORDER BY VARIABLE_NAME";
$global_status = $dblink->sql_fetch_yield($sql);
foreach ($global_status as $status) {
$value = (int) $status['VARIABLE_VALUE'];
$influxDB->insert(str_replace('_','-', $db) . "." . $status['VARIABLE_NAME'], ['value' => $value]);
}
*/
} else {
$server_on = 0;
$master = false;
$slave = false;
echo " server Mysql : " . $server_config['hostname'] . " is down\n";
}
$sql = "SELECT id FROM mysql_server WHERE name = '" . $db . "'";
//echo $sql . PHP_EOL;
$res = $default->sql_query($sql);
while ($ob = $default->sql_fetch_object($res)) {
$data = array();
$data['mysql_replication_stats']['id_mysql_server'] = $ob->id;
$data['mysql_replication_stats']['date'] = date("Y-m-d H:i:s");
$data['mysql_replication_stats']['ping'] = $server_on;
if ($server_on === 1) {
$sql = "SELECT now() as date_time";
$res = $dblink->sql_query($sql);
$date_time = $dblink->sql_fetch_object($res);
// can be empty ???????????
$this->clusterGalera($dblink);
$data['mysql_replication_stats']['version'] = $dblink->getServerType() . " : " . $dblink->getVersion();
$data['mysql_replication_stats']['date'] = $date_time->date_time;
$data['mysql_replication_stats']['is_master'] = $master ? 1 : 0;
$data['mysql_replication_stats']['is_slave'] = $slave ? 1 : 0;
$data['mysql_replication_stats']['uptime'] = $dblink->getStatus('Uptime') ? $dblink->getStatus('Uptime') : '-1';
$data['mysql_replication_stats']['time_zone'] = $dblink->getVariables('system_time_zone') ? $dblink->getVariables('system_time_zone') : '-1';
$data['mysql_replication_stats']['ping'] = 1;
$data['mysql_replication_stats']['last_sql_error'] = '';
$data['mysql_replication_stats']['binlog_format'] = $dblink->getVariables('binlog_format') ? $dblink->getVariables('binlog_format') : 'N/A';
$sql = "SHOW databases";
$dblist = array();
$res3 = $dblink->sql_query($sql);
while ($ob3 = $dblink->sql_fetch_object($res3)) {
$dblist[] = $ob3->Database;
}
$data['mysql_replication_stats']['databases'] = implode(',', $dblist);
if ($master) {
$data['mysql_replication_stats']['file'] = $master['File'];
$data['mysql_replication_stats']['position'] = $master['Position'];
}
}
$id_mysql_replication_stats = $default->sql_save($data);
if (!$id_mysql_replication_stats) {
debug($default->sql_error());
debug($data);
throw new \Exception("GLI-031 : Impossible to get id_mysql_replication_stats");
}
$thread = [];
if ($slave) {
foreach ($slave as $thread) {
$data = array();
$data['mysql_replication_thread']['id_mysql_replication_stats'] = $id_mysql_replication_stats;
$data['mysql_replication_thread']['relay_master_log_file'] = $thread['Relay_Master_Log_File'];
$data['mysql_replication_thread']['exec_master_log_pos'] = $thread['Exec_Master_Log_Pos'];
$data['mysql_replication_thread']['thread_io'] = $thread['Slave_IO_Running'] === 'Yes' ? 1 : 0;
$data['mysql_replication_thread']['thread_sql'] = $thread['Slave_SQL_Running'] === 'Yes' ? 1 : 0;
//only for MariaDB 10
if (version_compare($dblink->getVersion(), "10", ">=")) {
$data['mysql_replication_thread']['thread_name'] = $thread['Connection_name'];
}
$data['mysql_replication_thread']['time_behind'] = $thread['Seconds_Behind_Master'];
$data['mysql_replication_thread']['master_host'] = $thread['Master_Host'];
$data['mysql_replication_thread']['master_port'] = $thread['Master_Port'];
//suuport for mysql 5.0
$data['mysql_replication_thread']['last_sql_error'] = empty($thread['Last_SQL_Error']) ? $thread['Last_Error'] : $thread['Last_SQL_Error'];
$data['mysql_replication_thread']['last_io_error'] = empty($thread['Last_IO_Error']) ? $thread['Last_Error'] : $thread['Last_IO_Error'];
$data['mysql_replication_thread']['last_sql_errno'] = empty($thread['Last_SQL_Errno']) ? $thread['Last_Errno'] : $thread['Last_SQL_Errno'];
$data['mysql_replication_thread']['last_io_errno'] = empty($thread['Last_IO_Errno']) ? $thread['Last_Errno'] : $thread['Last_IO_Errno'];
$id_mysql_replication_thread = $default->sql_save($data);
if (!$id_mysql_replication_thread) {
debug($default->sql_error());
debug($data);
//throw new \Exception("GLI-032 : Impossible to save row in mysql_replication_thread");
} else {
$thread['id_mysql_replication_thread'] = $default->sql_insert_id();
}
}
}
$this->saveDatabase($dblink, $ob->id, $master, $thread);
}
}
$default->sql_query('COMMIT;');
/*
} catch (\Exception $ex) {
$default->sql_query('ROLLBACK;');
} */
}
function mysqldump($backup)
{
//$this->backup_dir = $this->backup_dir;
$MS = new MasterSlave();
$dumpoptions = " --quick --add-drop-table --default-character-set=utf8 --extended-insert ";
$db_to_backup = $this->di['db']->sql($backup['id_connection']);
$MS->setInstance($db_to_backup);
$server_config = $db_to_backup->getParams();
debug($backup['id_connection']);
debug($server_config);
$slave = $MS->isSlave();
$master = $MS->isMaster();
$userpassword = "******" . $server_config['hostname'] . " -P " . $backup['port'] . " -u " . $server_config['user'] . " -p" . $server_config['password'];
if ($slave) {
$stop_slave = "STOP SLAVE;";
//because option --dump-slave restart replication after made the dump
if ($db_to_backup->isMultiMaster()) {
$stop_slave = "STOP ALL SLAVES;";
}
$cmd = "mysql " . $userpassword . " -e '" . $stop_slave . ";'";
$this->cmd($cmd);
debug($slave);
$slave = $MS->isSlave();
$this->slave_data = json_encode($slave);
}
if ($master) {
debug($master);
$this->master_data = json_encode($master);
}
//$backup['path']
$file_name = $backup['db_name'] . "_" . date("Y-m-d_His") . "__" . $backup['db_name'] . ".sql";
$this->checkDirectory($this->backup_dir);
$extra = " ";
if ($master) {
$extra .= " --master-data=2 --single-transaction";
}
if ($slave) {
if (version_compare($version, "5.5.3", ">")) {
$extra .= " --dump-slave=2";
}
}
$this->di['db']->sql(DB_DEFAULT)->sql_close();
//$this->di['db']->sql(DB_DEFAULT);
//echo $mysql_dump . "\n";
Crypt::$key = CRYPT_KEY;
$mysql_ssh_login = Crypt::decrypt($backup['mysql_ssh_login']);
$mysql_ssh_password = Crypt::decrypt($backup['mysql_ssh_password']);
$nas_ssh_login = Crypt::decrypt($backup['nas_ssh_login']);
$nas_ssh_password = Crypt::decrypt($backup['nas_ssh_password']);
$pmauser = '******';
$pmapasswd = Crypt::decrypt(PMACONTROL_PASSWD);
echo "{$backup['ip']}, 22, {$pmauser}, {$pmapasswd});\n";
$ccc = new Ssh($backup['ip'], 22, $pmauser, $pmapasswd);
$ccc->connect();
$pwd = $ccc->exec('pwd');
debug($pwd);
$screen = $ccc->whereis("screen");
//$screen = "/usr/bin/screen";
$mysqldump = $ccc->whereis("mysqldump");
$cmd = $mysqldump . $userpassword . $dumpoptions . $extra . " " . $backup['db_name'] . " > " . $this->backup_dir . "/" . $file_name;
$id_backup = $backup['id_backup_database'];
echo "MYSQL_DUMP CMD : " . $cmd . "\n";
$ccc->exec("echo \"#!/bin/sh\n{$cmd}\" > " . $this->backup_dir . "/mysqldump.{$id_backup}.sh");
$ccc->exec("echo \"#!/bin/sh\n{$screen} -S backup_database_{$id_backup} -d -m " . $this->backup_dir . "/mysqldump.{$id_backup}.sh\n" . "{$screen} -list | grep backup_database_{$id_backup} | head -n1 | cut -f1 -d'.' | sed 's/\\s//g' > " . $this->backup_dir . "/pid.{$id_backup}.pid\n" . "\" > " . $this->backup_dir . "/backup.{$id_backup}.sh");
$ccc->exec("chmod +x " . $this->backup_dir . "/mysqldump." . $backup['id_backup_database'] . ".sh");
$ccc->exec("chmod +x " . $this->backup_dir . "/backup." . $backup['id_backup_database'] . ".sh");
$exec = $ccc->exec("sh " . $this->backup_dir . "/backup." . $backup['id_backup_database'] . ".sh");
$pid = trim($ccc->exec("cat " . $this->backup_dir . "/pid." . $backup['id_backup_database'] . ".pid"));
$waiting = ['/', '-', '\\', '|'];
$i = 0;
echo "backup in progress ... ";
do {
$i++;
$mod = $i % 4;
echo " " . $waiting[$mod];
echo "[2D";
sleep(1);
$nb_thread = $ccc->exec("ps -p {$pid} | grep {$pid} | wc -l");
switch (trim($nb_thread)) {
case "1":
$continue = true;
break;
case "0":
$continue = false;
break;
default:
throw new Exception("PMACTRL-085 : more than one thread ({$nb_thread}) have to audit code !");
break;
}
} while ($continue);
if (!strpos("dump-slave", $extra) && $slave) {
$start_slave = "START SLAVE;";
//because option --dump-slave restart replication after made the dump
if ($db_to_backup->isMultiMaster()) {
$start_slave = "START ALL SLAVES;";
}
$cmd = "mysql " . $userpassword . " -e '" . $start_slave . ";'";
$this->cmd($cmd);
}
$this->time_backup_end = microtime(true);
$full_path = $this->backup_dir . "/" . $file_name;
$file_gz = $this->backup_dir . "/" . $file_name . ".gz";
//get md5 of file
$this->md5_file = trim($ccc->exec("md5sum " . $this->backup_dir . "/" . $file_name . " | awk '{ print \$1 }'"));
//get size of file
$this->size_file = trim($ccc->exec("du -s " . $this->backup_dir . "/" . $file_name . " | awk '{ print \$1 }'"));
$cmd = "nice gzip -c " . $this->backup_dir . "/" . $file_name . ">" . $file_gz;
$ret = $ccc->exec($cmd);
//get md5 of file
$this->md5_gz = trim($ccc->exec("md5sum " . $this->backup_dir . "/" . $file_name . ".gz | awk '{ print \$1 }'"));
$this->size_gz = trim($ccc->exec("du -s " . $this->backup_dir . "/" . $file_name . ".gz | awk '{ print \$1 }'"));
$this->time_gzip = microtime(true);
//remove old backup
$grep = $ccc->whereis("grep");
$ls = $ccc->whereis("ls");
$sed = $ccc->whereis("sed");
$xargs = $ccc->whereis("xargs");
$rm = $ccc->whereis("rm");
$cmd = 'cd ' . $this->backup_dir . ' && ' . $ls . ' -t | ' . $grep . ' \'__' . $backup['db_name'] . '.sql\' | ' . $sed . ' -e \'1,2d\' | ' . $xargs . ' -d \'\\n\' ' . $rm . '' . "\n";
$ret = $ccc->exec($cmd);
$ret = $ccc->exec("ls " . $file_gz . " | wc -l");
if (trim($ret) === "1") {
return $file_gz;
}
throw new \Exception("PMACTRL-052 : file not found '" . $file_gz . "'");
echo "\n";
return false;
}