Быстрый экспорт/импорт базы данных MySQL для Yii2

• Как делать импорт/экспорт БД прямо из админ-панели сайта?
  
• Как быстро перенести БД с локального компьютера на хостинг или наоборот?
• Как делать это в 1 клик?

Об этом и не только в данной статье.

Не знаю как вы, но я постоянно пользуюсь системой управления версиями файлов - Git. Над одним сайтом (например над этим) могу работать локально с двух компьютерах + выкладывать необходимые изменения на хостинг. То есть обмениваться файлами между 3-мя различными серверами. В принципе удобно, единственное чего мне не хватало так это удобства работы с базой данных MySQL, которую приходилось вручную экспортировать, потом заходить на хостинг, вводить свои данных для импорта...удалять таблицы перед импортом, а там еще ограничения внешних ключей..... В общем процесс не настолько простой и быстрый как хотелось бы. Можно конечно немного упростить, используя утилиту mysqldump в командной строке, но это все же не то.

После недолгих поисков, в сети был найден пример кода, который с помощью консольных команд делал практически то, что мне и нужно. Я решил, что удобнее делать это в админ-панели сайта, в следствии чего создал отдельный контроллер, модель и вид с виджетом GridView для оформления вывода в виде таблицы и сортировки списка дампов БД, а так же немного добавил функционал. Теперь дамп БД я могу хранить и легко переносить с помощью Git вместе с другими файлами, а все действия осуществлять прямо из админ-панели сайта нажатием одной,нужной кнопки.

Что будем иметь в результате. Вот вид панели для работы с БД, которая получится в итоге (конечно можно легко изменить):

Тут всего 3 кнопки:

• создать дамп БД;
• импортировать выбранный дамп;
• удалить выбранный дамп

То есть все выполняется в одно действие! После окончания выполнения появляется информативное окно.

Созданные файлы хранятся в папке db, которую я разместил в каталоге common. Прежде всего данную папку нужно создать. Переименовать или разместить ее в другом месте можно поменяв в контроллере значение переменной public $dumpPath

Создадим модель с основным функционалом (common\models\Db.php) :

<?php
namespace common\models;
use Yii;
use yii\base\Model;
use yii\helpers\FileHelper;
class Db extends Model{
    public function getFiles($files){
        //кол-во файлов сохраняем для использования в виджете
        Yii::$app->params['count_db'] = count($files);
        $arr = array();
        foreach($files as $key => $file){
            $arr[] = array('dump' =>$file);
        }
        $dataProvider = new \yii\data\ArrayDataProvider([
            'allModels' => $arr,
            'sort' => [
                'attributes' => ['dump'],
            ],
            'pagination' => [
                'pageSize' => 10,
            ],
        ]);
        return $dataProvider;
    }
    public function import($path) {
        if (file_exists($path)) {
            $path = \yii\helpers\Html::encode($path);
            $db = Yii::$app->getDb();
            if (!$db) {
                Yii::$app->session->setFlash('error', 'Нет подключения к базе данных.');
            } 
            //Экранируем скобку которая есть в пароле
            $db->password = str_replace("(","\(",$db->password);
            exec('mysql --host=' . $this->getDsnAttribute('host', $db->dsn) . ' --user=' . $db->username . ' --password=' . $db->password . ' ' . $this->getDsnAttribute('dbname', $db->dsn) . ' < ' . $path);
            Yii::$app->session->setFlash('success', 'Дамп ' . $path . ' успешно импортирован.');
        } else {
            Yii::$app->session->setFlash('error', 'Указанный путь не существует.');
        }
        return Yii::$app->response->redirect(['db/index']);
    }
    public function export($path = null) {
        $path = FileHelper::normalizePath(Yii::getAlias($path));
        if (file_exists($path)) {
            if (is_dir($path)) {
                if (!is_writable($path)) {
                    Yii::$app->session->setFlash('error', 'Дирректория не доступна для записи.');
                    return Yii::$app->response->redirect(['db/index']);
                } 
                $fileName = 'dump_' . date('d-m-Y_H-i-s') . '.sql';
                $filePath = $path . DIRECTORY_SEPARATOR . $fileName;
                $db = Yii::$app->getDb();
                if (!$db) {
                    Yii::$app->session->setFlash('error', 'Нет подключения к базе данных.');
                    return Yii::$app->response->redirect(['db/index']);
                } 
                //Экранируем скобку которая есть в пароле
                $db->password = str_replace("(","\(",$db->password);
                exec('mysqldump --host=' . $this->getDsnAttribute('host', $db->dsn) . ' --user=' . $db->username . ' --password=' . $db->password . ' ' . $this->getDsnAttribute('dbname', $db->dsn) . ' --skip-add-locks > ' . $filePath);
                Yii::$app->session->setFlash('success', 'Экспорт успешно завершен. Файл "'.$fileName.'" в папке ' . $path);
            } else {
                Yii::$app->session->setFlash('error', 'Путь должен быть папкой.');
            }
        } else {
            Yii::$app->session->setFlash('error', 'Указанный путь не существует.');
        }
        return Yii::$app->response->redirect(['db/index']);
    }
    //Возвращает название хоста (например localhost)
    private function getDsnAttribute($name, $dsn) {
        if (preg_match('/' . $name . '=([^;]*)/', $dsn, $match)) {
            return $match[1];
        } else {
            return null;
        }
    }
    public function delete($path) {
        if (file_exists($path)) {
            $path = \yii\helpers\Html::encode($path);
            unlink($path);
            Yii::$app->session->setFlash('success', 'Дамп БД удален.');
        } else {
            Yii::$app->session->setFlash('error', 'Указанный путь не существует.');
        }
        return Yii::$app->response->redirect(['db/index']);        
    }
}

Описание.

Одноименные методы выполняют соответствующие им функции. Например метод export() формирует строку такого плана:

mysqldump --host=localhost --user=vova01 --password=hRup233x%( vova_site.com --skip-add-locks > /home/vova01/public_html/site.com/common/db/dump_25-01-2017_20-43-08.sql

Из приложения Yii2 командой

Yii::$app->getDb();

получаем данные для работы с базой данных (в т.ч. логин и пароль). Далее используется утилита для работы с MySQL - mysqldump, которая экспортирует или импортирует дамп БД. У меня в пароле был символ "(", который пришлось экранировать, прежде чем все заработало. Ошибку я увидел выполнив вручную данную команду в адресной строке. То есть строка

$db->password = str_replace("(","\(",$db->password);

вам, скорее всего, не понадобится.

Название дампа БД, который получится в результате, задается в переменной $fileName:

$fileName = 'dump_' . date('d-m-Y_H-i-s') . '.sql';

Удобно иметь в названии дату и время. Кроме того, используемый виджет позволяет сортировать список файлов.

С помощью команды

Yii::$app->session->setFlash

в сессию сохраняются сообщения информирующие о процессе работы с БД. Они отображаются сразу после перезагрузки страницы. А перезагрузка осуществляется после выполнения данных методов, т.к. я разместил в конце перенаправление на единственный для данного контроллера файл вид (в методе db/index).

Для того, чтобы передать данные (кол-во дампов БД) в виджет вида, используем массив глобальных переменных для хранения:

Yii::$app->params['count_db'] = count($files);

Теперь нужно создать контроллер, связывающий модель и вид (backend\controllers\DbController.php):

<?php
namespace backend\controllers;
use Yii;
use yii\web\Controller;
use yii\helpers\FileHelper;
use common\models\Db;
class DbController extends Controller{
    //Путь к файлам БД по-умолчанию
    public $dumpPath = '@common/db/';
    public function actionIndex($path = null){
        //Получаем массива путей к файлам с дампом БД (.sql)         
        $path = FileHelper::normalizePath(Yii::getAlias($this->dumpPath));
        $files = FileHelper::findFiles($path, ['only' => ['*.sql'], 'recursive' => FALSE]);
        $model = new Db();
        //Метод формирует массив в нужный для виджета GridView формат с пагинацией
        $dataProvider = $model->getFiles($files); 
        return $this->render('index', [
            'dataProvider' => $dataProvider,
        ]);
    }
    public function actionImport($path) {
        $model = new Db();
        //Метод делает импорт дампа БД
        $model->import($path);
    }
    public function actionExport($path = null) {
        $path = $path ? : $this->dumpPath;
        $model = new Db();
        //Метод экспортирует данные из БД в указанную папку
        $model->export($path);
    }
    public function actionDelete($path) {
        $model = new Db();
        //Метод удаляет дамп БД
        $model->delete($path);
    }
}

В контроллере указываем путь где будут храниться файлы с дампами БД. По-умолчанию это "common/db".

В действии actionIndex() получаем список файлов с расширением .sql в виде массива. Свойство 'recursive' => FALSE значит - не искать в подпапках. Т.к. можно, как я, создать папку archive для хранения некоторых отдельных дампов или еще чего-нибудь. В таком случае (если используете git) стоит так же создать файл .gitignore с содержимым:

/archive

чтобы не переносить папку archive в удаленный репозиторий.

Чего еще не хватает в контроллере так это ограничения на осуществление работы с базой данной для пользователей. Добавим действие для того, чтобы работать с БД мог только админ:

//Доступ только для админа
public function beforeAction($action)
{
    if (parent::beforeAction($action)) {
        if (!\Yii::$app->user->can('admin')) {
            throw new \yii\web\ForbiddenHttpException('Доступ закрыт.');
        }
        return true;
    } else {
        return false;
    }    
}

Если не знаете как создать систему доступа, читайте Доступ к сайту на основе ролей (RBAC) в Yii2

Вид (backend\views\db\index.php):

<?php
 
use yii\helpers\Html;
use yii\grid\GridView;
 
$this->title = 'База данных';
$this->params['breadcrumbs'][] = $this->title;
?>
<div class="post-index">
 
    <h1><?= Html::encode($this->title) ?></h1>
    <br/>
    <p>
        <?= Html::a('Создать дамп БД (экспорт)', ['export'], ['class' => 'btn btn-success']) ?>
    </p>
    <br/>
 
<?= GridView::widget([
        'dataProvider' => $dataProvider,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],
            [
                'attribute' => 'dump',
                'format' => 'text',
                'label' => 'Путь к дампу БД',
            ],
            [
                'format'=>'raw',
                'value' => function($data,$id){
                    return Html::a('Импортировать в БД', \yii\helpers\Url::to(['db/import','path'=>$data['dump']]), ['title' => 'Импортировать','class' => 'btn btn-primary']);
                }
            ],
            [
                'format'=>'raw',
                //кнопку удаления выводим только если >1 дампа БД
                'value' => function($data,$id){
                    if(Yii::$app->params['count_db'] > 1){
                        return Html::a('Удалить', \yii\helpers\Url::to(['db/delete','path'=>$data['dump']]), ['title' => 'Удалить','class' => 'btn btn-danger']);
                    } else return false;
                }
            ],
        ],
    ]); ?>
 
</div>

В данном виде используется стандартный виджет Yii2 - GridView для вывода списка файлов в виде таблицы.

Экспорт начинается после нажатия ссылки в виде кнопки

<?= Html::a('Создать дамп БД (экспорт)', ['export'], ['class' => 'btn btn-success']) ?>

где "export"- действие текущего контроллера, а указанные классы придают ссылке форму кнопки используя стили Bootstrap.

Остальные ссылки выполняют импорт дампа и удаление. Причем последнее возможно только в случае если есть более 1-го файла, чтобы не удалить случайно все.