2015年12月20日 星期日

資料庫備份工具

```php class DatabaseClone { public $link; public function __construct($host, $username, $password) { $this->link = mysqli_connect($host, $username, $password); mysqli_query($this->link, 'SET NAMES utf8'); mysqli_query($this->link, 'SET AUTOCOMMIT = 0'); mysqli_query($this->link, 'SET UNIQUE_CHECKS = 0'); mysqli_query($this->link, 'SET FOREIGN_KEY_CHECKS = 0'); } public function fetch($query) { $results = []; $query = mysqli_query($this->link, $query); while ($row = mysqli_fetch_assoc($query)) { $results[] = $row; } return $results; } public function copy($copyTo) { set_time_limit(-1); ini_set('memory_limit', -1); $query = mysqli_query($this->link, $sql = 'show databases'); while ($database = mysqli_fetch_assoc($query)) { $database = $database['Database']; if (in_array($database, ['mysql', 'information_schema', 'performance_schema'], true) === true) { continue; } $createDatabase = mysqli_fetch_assoc(mysqli_query($this->link, 'SHOW CREATE DATABASE `'.$database.'`')); mysqli_query($copyTo->link, $sql = 'DROP DATABASE IF EXISTS `'.$database.'`') or var_dump($sql); mysqli_query($copyTo->link, $sql = $createDatabase['Create Database']) or var_dump($sql); mysqli_select_db($this->link, $database); mysqli_select_db($copyTo->link, $database); $query2 = mysqli_query($this->link, $sql = 'show tables'); while ($table = mysqli_fetch_assoc($query2)) { $table = current($table); $createTable = mysqli_fetch_assoc(mysqli_query($this->link, 'SHOW CREATE TABLE `'.$table.'`')); mysqli_query($copyTo->link, $sql = $createTable['Create Table']) or var_dump($sql); $counts = current(mysqli_fetch_assoc(mysqli_query($this->link, 'SELECT COUNT(*) as counts FROM `'.$table.'`'))); $range = range(0, $counts - 1); $offset = 0; $limit = 200; $chunks = array_chunk($range, $limit); foreach ($chunks as $chunk) { $query3 = mysqli_query($this->link, $sql = 'SELECT * FROM `'.$table.'` LIMIT '.$limit.' OFFSET '.$offset) or var_dump($database, $sql); while ($row = mysqli_fetch_assoc($query3)) { $keys = []; $values = []; foreach ($row as $key => $value) { $keys[] = '`'.$key.'`'; $values[] = "'".addslashes($value)."'"; } mysqli_query($copyTo->link, $sql = 'INSERT INTO `'.$table.'` ('.implode(',', $keys).') VALUES ('.implode(',', $values).')') or var_dump($database, $sql); } $offset += count($chunk); } } } } } $database = new DatabaseClone('localhost:3306', 'root', ''); $database->copy(new DatabaseClone('localhost:3308', 'root', '')); ```