Автоматическое копирование базы данных MySQL на PHP

Дата публикации: 2013-11-20

Автоматическое копирование базы данных MySql на PHP

От автора: база данных является хранилищем важнейшей информации практически любого хорошего сайта, поэтому еще на этапе разработки сайта необходимо решить, как выполнять резервное копирование информации хранящейся в базе данных. Так как не выполняя данную операцию, однажды можно потерять весь контент сайта, и это может произойти совсем не по Вашей вине, а, к примеру, по техническим причинам на сервере. Поэтому в данном уроке мы с Вами рассмотрим создание небольшого скрипта, который будет выполнять автоматическое копирование содержимого Вашей базы данных.

Настройки для работы скрипта

Итак, у нас есть пустой файл index.php на локальном компьютере – и это будет основной файл будущего скрипта. Первым делом, давайте создадим файл config.php для хранения настроек, с вот таким содержимым:

<?php
//config
	define('HOST',"localhost");
	define('USER',"root");
	define('PASSWORD',"");
	define('DB',"my_bd");
	
	define('DIR_SQL','sql/');
	define('FOR_WRITE',10);
//config
?>

<?php

//config

define('HOST',"localhost");

define('USER',"root");

define('PASSWORD',"");

define('DB',"my_bd");

define('DIR_SQL','sql/');

define('FOR_WRITE',10);

//config

Настройками будут служить обычные константы PHP, и это четыре настройки для подключения к базе данных (путь к серверу, имя пользователя, пароль для пользователя, имя базы данных). Далее, создаем константу для хранения пути к папке, в которую будут сохраняться резервные копии. А также создадим еще одну вспомогательную настройку, в которой сохраним количество записей, которые одновременно будут записываться в текстовый файл. После этого давайте подключим данный файл в index.php:

header("Content-Type:text/html;charset=UTF-8");
include "config.php";

1 2	header("Content-Type:text/html;charset=UTF-8"); include "config.php";

Подключение к базе данных

В данном файле будут собраны все необходимые функции по созданию скрипта автоматического копирования содержимого базы данных. Поэтому создадим данный файл и добавим в него первую функцию:

function connect_db() {
	$db = mysqli_connect(HOST,USER,PASSWORD,DB);
	if(mysqli_connect_error($db)) {
		exit('NO connection');
	}
	
	return $db;
}

function connect_db() {

$db = mysqli_connect(HOST,USER,PASSWORD,DB);

if(mysqli_connect_error($db)) {

exit('NO connection');

}

return $db;

}

Как Вы видите, данная функция обеспечивает подключение к базе данных, используя настройки конфигурационного файла, и возвращает дискриптор открытого соединения (переменная $db). Сразу хочу отметить, что для работы с базой данных мы будем использовать расширение языка PHP mysqli, так как расширение mysql уже существенно устарело. Теперь данный файл необходимо подключить в файле index.php и вызвать на исполнение функцию connect_db():

include "functions.php";
$db = connect_db();

1 2	include "functions.php"; $db = connect_db();

Получение списка таблиц базы данных

Перед тем как начать писать логику работы скрипта, давайте посмотрим на фрагмент дампа базы данных, созданного скриптом phpMyAdmin. Напомню, для того, что бы создать резервную копию базы данных, при помощи phpMyAdmin, необходимо выбрать нужную базу данных, и нажать по вкладке Экспорт (Export):

Далее если выбрать быстрый способ экспорта – будет создан текстовый файл, с расширением sql, примерно вот такого содержания (посмотрим только фрагмент):

-- phpMyAdmin SQL Dump
-- version 3.5.1
-- //www.phpmyadmin.net
--
-- Хост: 127.0.0.1
-- Время создания: Окт 26 2013 г., 22:47
-- Версия сервера: 5.5.25
-- Версия PHP: 5.3.13

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- База данных: `my_bd`
--

-- --------------------------------------------------------

--
-- Структура таблицы `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `id_category` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name_category` varchar(255) NOT NULL,
  PRIMARY KEY (`id_category`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=58 ;

--
-- Дамп данных таблицы `category`
--

INSERT INTO `category` (`id_category`, `name_category`) VALUES
(1, 'Opel'),
(2, 'Mazda'),
(3, 'Ford'),
(5, 'Fiat'),
(56, 'fsdfsdfsdf'),
(57, 'fsdfsdfsdf');

-- --------------------------------------------------------

-- phpMyAdmin SQL Dump

-- version 3.5.1

-- //www.phpmyadmin.net

-- Хост: 127.0.0.1

-- Время создания: Окт 26 2013 г., 22:47

-- Версия сервера: 5.5.25

-- Версия PHP: 5.3.13

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

-- База данных: `my_bd`

-- --------------------------------------------------------

-- Структура таблицы `category`

CREATE TABLE IF NOT EXISTS `category` (

`id_category` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name_category` varchar(255) NOT NULL,

PRIMARY KEY (`id_category`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=58 ;

-- Дамп данных таблицы `category`

INSERT INTO `category` (`id_category`, `name_category`) VALUES

(1, 'Opel'),

(2, 'Mazda'),

(3, 'Ford'),

(5, 'Fiat'),

(56, 'fsdfsdfsdf'),

(57, 'fsdfsdfsdf');

-- --------------------------------------------------------

То есть, как Вы видите, данный фал содержит набор SQL запросов, для восстановления целостности информации базы данных. А именно: создание необходимых таблиц, наполнение их данными. Поэтому наш скрипт должен создавать аналогичный файл. Теперь, нам необходимо узнать какие таблицы содержатся в выбранной базе данных. Для этого создадим функцию get_tables() в файле functions.php:

function get_tables($db) {
	
	$sql = "SHOW TABLES";
	$result = mysqli_query($db,$sql);
	
	if(!$result) {
		exit(mysqli_error($db));
	}
	
	$tables = array();
	for($i = 0; $i < mysqli_num_rows($result); $i++) {
		$row = mysqli_fetch_row($result);
		$tables[] = $row[0];
	}
	
	return $tables;
	
}

function get_tables($db) {

$sql = "SHOW TABLES";

$result = mysqli_query($db,$sql);

if(!$result) {

exit(mysqli_error($db));

}

$tables = array();

for($i = 0; $i < mysqli_num_rows($result); $i++) {

$row = mysqli_fetch_row($result);

$tables[] = $row[0];

}

return $tables;

}

Данная функция принимает параметром дискриптор открытого соединения с базой данных. И выполняет простой SQL запрос (SHOW TABLES), тем самым, получая список всех таблиц выбранной базы данных. Далее в цикле проходим по результирующей таблице ($result) и формируем массив $tables – массив, в каждой ячейке которого, содержатся названия таблиц. Теперь в файле index.php вызовем на исполнение только что созданную функцию get_tables():

$tables = get_tables($db);

1	$tables = get_tables($db);

И посмотрим в браузере, что у нас получилось (при этом распечатаем массив $tables при помощи функции print_r()):

Как Вы видите, мы с Вами получили массив всех таблиц, содержащихся в выбранной базе данных. Теперь для каждой из этих таблиц, необходимо создать SQL запросы, которые будут их создавать и заполнять содержимым.

Функция создания дампа базы данных

Итак, приступаем к непосредственному созданию дампа базы данных. Для этого создаем в файле functions.php функцию get_dump():

function get_dump($db,$tables) { 

}

function get_dump($db,$tables) {

}

Обратите внимание – функция принимает два параметра – дискриптор открытого соединения с базой данных и массив таблиц, хранящихся в базе данных. Так как дамп базы данных это обычный текстовый файл, поэтому давайте создадим такой файл и откроем его для записи, используя функцию fopen() и запишем в него несколько строк:

$fp = fopen(DIR_SQL.time()."_dump.sql","w");
		
		$text = "-- SQL Dump
-- my_ version: 1.234
--
-- База дынных: `".DB."`
--
-- ---------------------------------------------------
-- ---------------------------------------------------
";
		fwrite($fp,$text);

$fp = fopen(DIR_SQL.time()."_dump.sql","w");

$text = "-- SQL Dump

-- my_ version: 1.234

-- База дынных: `".DB."`

-- ---------------------------------------------------

fwrite($fp,$text);

Режим работы функции fopen() выбираем w, то есть режим, при котором функция открывает файл только для записи, помещает указатель в начало файла и обрезает файл до нулевой длины. Если файл не существует — пробует его создать. Имя файла формируем следующим образом: DIR_SQL.time().»_dump.sql», то есть, указываем папку, где будут складываться дампы базы данных, затем получаем текущую метку времени, используя функцию time() и добавляем суффикс _dump. Ну и не забываем о расширении .sql.

Далее создадим переменную $text, которая будет использоваться нами для хранения временных фрагментов будущего SQL запроса. И запишем в файл первые несколько строк. Это будут комментарии (напомню, что комментарии на языке SQL обозначаются —), включающие в себя различную произвольную информацию – на Ваше усмотрение. Я добавил название, номер версии скрипта и имя базы данных, резервную копию которой мы создаем. Теперь необходимо в цикле пройтись по массиву $tables и для каждой таблицы создать необходимые SQL запросы, по ее созданию и восстановлению данных:

foreach($tables as $item) {

}

foreach($tables as $item) {

}

И дальнейшее кодирование ведем в цикле. Для начала, сохраним в файле еще несколько строк, отделяющие одну таблицу от другой и указывающие на название таблицы.

$text = "
-- 
-- Структура таблицы - ".$item."
--
";
		fwrite($fp,$text);

$text = "

-- Структура таблицы - ".$item."

fwrite($fp,$text);

Как Вы видите – это опять же комментарии. Теперь, нам необходимо создать запрос по созданию текущей таблицы, но перед этим давайте добавим вначале запрос, по удалению этой же таблицы, если она уже существует, а уже потом будем ее создавать:

$text = "";
$text .= "DROP TABLE IF EXISTS `".$item."`;";

1 2	$text = ""; $text .= "DROP TABLE IF EXISTS `".$item."`;";

То есть, обнуляем переменную $text и добавляем в нее строку с запросом по удалению таблицы. Название текущей таблицы содержится в переменной $item. Хочу подчеркнуть, что в конце строки с запросом, необходимо поставить точку с запятой.

Далее, нам необходимо создать таблицу, но как же узнать все параметры текущей таблицы (количество полей, атрибуты, типы данных полей и т.д)? Для этого необходимо выполнить еще один простой SQL запрос:

$sql = "SHOW CREATE TABLE ".$item;
			$result = mysqli_query($db, $sql);
			if(!$result) {
				exit(mysqli_error($db));
			}
			$row = mysqli_fetch_row($result);

$sql = "SHOW CREATE TABLE ".$item;

$result = mysqli_query($db, $sql);

if(!$result) {

exit(mysqli_error($db));

}

$row = mysqli_fetch_row($result);

Данный запрос вернет нам строку, в которой будет содержаться SQL запрос по созданию выбранной таблице, включающий в себя все необходимые параметры и атрибуты. Его мы сохраним в переменной $row. Далее добавим значение данной переменной к переменной $text и запишем в файл:

$text .= "\n".$row[1].";";
			fwrite($fp,$text);
			
			$text = "";

$text .= "\n".$row[1].";";

fwrite($fp,$text);

$text = "";

Теперь давайте добавим очередной разделитель в файл (комментарий), указывающий на то, что далее идет блок со вставкой данных в таблицу:

$text .=
"
--			
-- Dump BD - tables :".$item."
--
";

$text .=

-- Dump BD - tables :".$item."

Далее необходимо создать SQL запрос по вставке данных в базу данных. Так как нам необходимо заполнить все поля таблицы, мы используем следующий синтаксис, данного запроса:

INSERT INTO `category` VALUES ("1", "Opel"),(id,opel);

1	INSERT INTO `category` VALUES ("1", "Opel"),(id,opel);

Поэтому добавляем часть данного запроса в переменную $text и записываем в файл:

$text .= "\nINSERT INTO `".$item."` VALUES";
			fwrite($fp,$text);

1 2	$text .= "\nINSERT INTO `".$item."` VALUES"; fwrite($fp,$text);

Теперь нам нужно получить данные таблиц и закончить данный запрос. Для этого выполняем стандартный SQL запрос:

$sql2 = "SELECT * FROM ".$item."`";
			$result2 = mysqli_query($db,$sql2);
			if(!$result2) {
				exit(mysqli_error($db));
			}
			$text = "";

$sql2 = "SELECT * FROM ".$item."`";

$result2 = mysqli_query($db,$sql2);

if(!$result2) {

exit(mysqli_error($db));

}

$text = "";

Далее необходимо в цикле получить данные из результирующей таблицы и добавить в запрос, который в последствии и запишется в файл. Поэтому добавляем следующий код:

for($i = 0; $i < mysqli_num_rows($result2); $i++) {
				$row = mysqli_fetch_row($result2);
				
				if($i == 0) $text .= "(";
					else  $text .= ",(";
				
				foreach($row as $v) {
					$text .= "\"".mysqli_real_escape_string($db,$v)."\",";
				}
				$text = rtrim($text,",");	
				$text .= ")";
				
				if($i > FOR_WRITE) {
					fwrite($fp,$text);
					$text = "";
				}
				
			}

for($i = 0; $i < mysqli_num_rows($result2); $i++) {

$row = mysqli_fetch_row($result2);

if($i == 0) $text .= "(";

else $text .= ",(";

foreach($row as $v) {

$text .= "\"".mysqli_real_escape_string($db,$v)."\",";

}

$text = rtrim($text,",");

$text .= ")";

if($i > FOR_WRITE) {

fwrite($fp,$text);

$text = "";

}

Итак, для начала, в переменную $row получаем массив данных по каждой отдельной записи в таблице. Затем в зависимости, от того на какой итерации мы сейчас находимся, в переменную $text добавляем либо «(«, либо «,(«. Так как в соответствии с SQL запросом типа INSERT данные необходимо передавать в круглых скобках и разделять их запятой. Поэтому если текущая итерация – первая – мы открываем круглую скобку, а при последующих итерациях, перед открывающей круглой скобкой необходимо указывать запятую.

Далее формируем цикл foreach, в котором проходим по массиву $row и добавляем данные в переменную $text, разделяя их запятой. После цикла обработаем переменную $text, функцией rtrim(), то есть удалим лишнюю запятую в конце значения переменной $text. Далее добавляем в данную переменную закрывающую круглую скобку.

Затем если количество итераций превысило значение хранящиеся в константе FOR_WRITE – выполним запись переменной текст в файл. И сразу же очищаем переменную $text. Далее добавим следующий код:

$text .= ";\n";
fwrite($fp,$text);

1 2	$text .= ";\n"; fwrite($fp,$text);

То есть, добавляем символ точки с запятой, как окончание SQL запроса и производим запись значения переменной $text в файл. И в конце не забываем закрыть файл:

fclose($fp);

1	fclose($fp);

Теперь давайте приведу полный код функции get_dump():

function get_dump($db,$tables) {
	
	if(is_array($tables)) {
		$fp = fopen(DIR_SQL.time()."_dump.sql","w");
		
		$text = "-- SQL Dump
-- my_ version: 1.234
--
-- База дынных: `".DB."`
--
-- ---------------------------------------------------
-- ---------------------------------------------------
";
		fwrite($fp,$text);
		
		foreach($tables as $item) {
			
				$text = "
-- 
-- Структура таблицы - ".$item."
--
";
		fwrite($fp,$text);
			
			
			$text = "";
			$text .= "DROP TABLE IF EXISTS `".$item."`;";
			
			$sql = "SHOW CREATE TABLE ".$item;
			$result = mysqli_query($db, $sql);
			if(!$result) {
				exit(mysqli_error($db));
			}
			$row = mysqli_fetch_row($result);
			
			$text .= "\n".$row[1].";";
			fwrite($fp,$text);
			
			$text = "";
			$text .=
			"
--			
-- Dump BD - tables :".$item."
--
			";
			
			$text .= "\nINSERT INTO `".$item."` VALUES";
			fwrite($fp,$text);
			
			$sql2 = "SELECT * FROM ".$item."`";
			$result2 = mysqli_query($db,$sql2);
			if(!$result2) {
				exit(mysqli_error($db));
			}
			$text = "";
			
			for($i = 0; $i < mysqli_num_rows($result2); $i++) {
				$row = mysqli_fetch_row($result2);
				
				if($i == 0) $text .= "(";
					else  $text .= ",(";
				
				foreach($row as $v) {
					$text .= "\"".mysqli_real_escape_string($db,$v)."\",";
				}
				$text = rtrim($text,",");	
				$text .= ")";
				
				if($i > FOR_WRITE) {
					fwrite($fp,$text);
					$text = "";
				}
				
			}
			$text .= ";\n";
			fwrite($fp,$text);
			
			
		}
		fclose($fp);
	}
	
}

function get_dump($db,$tables) {

if(is_array($tables)) {

$fp = fopen(DIR_SQL.time()."_dump.sql","w");

$text = "-- SQL Dump

-- my_ version: 1.234

-- База дынных: `".DB."`

-- ---------------------------------------------------

fwrite($fp,$text);

foreach($tables as $item) {

$text = "

-- Структура таблицы - ".$item."

fwrite($fp,$text);

$text = "";

$text .= "DROP TABLE IF EXISTS `".$item."`;";

$sql = "SHOW CREATE TABLE ".$item;

$result = mysqli_query($db, $sql);

if(!$result) {

exit(mysqli_error($db));

}

$row = mysqli_fetch_row($result);

$text .= "\n".$row[1].";";

fwrite($fp,$text);

$text = "";

$text .=

-- Dump BD - tables :".$item."

$text .= "\nINSERT INTO `".$item."` VALUES";

fwrite($fp,$text);

$sql2 = "SELECT * FROM ".$item."`";

$result2 = mysqli_query($db,$sql2);

if(!$result2) {

exit(mysqli_error($db));

}

$text = "";

for($i = 0; $i < mysqli_num_rows($result2); $i++) {

$row = mysqli_fetch_row($result2);

if($i == 0) $text .= "(";

else $text .= ",(";

foreach($row as $v) {

$text .= "\"".mysqli_real_escape_string($db,$v)."\",";

}

$text = rtrim($text,",");

$text .= ")";

if($i > FOR_WRITE) {

fwrite($fp,$text);

$text = "";

}

$text .= ";\n";

fwrite($fp,$text);

}

fclose($fp);

}

Вот мы с Вами и закончили скрипт автоматического резервного копирования информации из базы данных. Конечно, его еще можно дорабатывать, но думаю, для Вас, это уже не составит труда. Так как принцип создания дампа базы данных средствами PHP, мы с Вами рассмотрели в данной уроке.

Всего Вам доброго, удачного кодирования! И увидимся в следующих уроках!

Метки: бэкап БД, дамп