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 waitPosition($db, $file, $position) { $MS = new MasterSlave(); $MS->setInstance($db); do { $thread_slave = $MS->isSlave(); foreach ($thread_slave as $thread) { $Relay_Master_Log_File = $thread['Relay_Master_Log_File']; $Exec_Master_Log_Pos = $thread['Exec_Master_Log_Pos']; } $sql = "SHOW SLAVE STATUS;"; $this->log($sql); if (!empty($thread['Last_Errno'])) { debug($thread); throw new \Exception('PMACLI-037 Error : Impossible to load data !'); } $tab = new Table(1); $tab->addHeader(array("Relay_Master_Log_File", "Exec_Master_Log_Pos")); $tab->addLine(array($Relay_Master_Log_File, $Exec_Master_Log_Pos)); echo $tab->display(); sleep(1); } while ($file != $Relay_Master_Log_File || $position != $Exec_Master_Log_Pos); }
/** * * @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; }