Автоматическое или регулярное резервное копирование данных MySQL
Я хочу регулярно делать резервные копии некоторых таблиц в моей базе данных MySQL, используя <вставьте сюда любимый PHP-фреймворк> / обычный PHP / мой второй любимый язык. Я хочу, чтобы он был автоматизирован, чтобы резервное копирование могло быть восстановлено позднее, если что-то пойдет не так.
Я попытался выполнить запрос и сохранить результаты в файл. Закончился кодом, который выглядит примерно так.
$sql = 'SELECT * FROM my_table ORDER id DESC';
$result = mysqli_query( $connect, $sql );
if( mysqli_num_rows( $result ) > 0){
$output=fopen('/tmp/dumpfile.csv','w+');
/* loop through recordset and add that to the file */
while( $row = mysqli_fetch_array( $result ) ) {
fputcsv( $output, $row, ',', '"');
}
fclose( $output );
}
Я установил работу cron на своей локальной машине, чтобы попасть на веб-страницу с этим кодом. Я также попытался написать cronjob на сервере и запустить скрипт как CLI. Но это вызывает все виды проблем. Они включают
- Иногда данные не согласованы
- Файл выглядит усеченным
- Вывод не может быть импортирован в другую базу данных
- Иногда время ожидания сценария
Я также слышал о mysqldump, Я пытался запустить его с exec но это выдает ошибку.
Как я могу решить это?
Решение
CSV и ВЫБЕРИТЕ В OUTFILE
http://dev.mysql.com/doc/refman/5.7/en/select-into.html
SELECT … INTO OUTFILE записывает выбранные строки в файл. Колонка и
можно указать терминаторы строки для создания определенного выходного формата.
Вот полный пример:
SELECT * FROM my_table INTO OUTFILE '/tmp/my_table.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
Файл сохраняется на сервере, и выбранный путь должен быть доступен для записи. Хотя этот запрос может быть выполнен через PHP и веб-запрос, лучше всего выполнить его через консоль mysql.
Данные, которые экспортируются таким образом, могут быть импортированы в другую базу данных с помощью НАГРУЗКА ДАННЫХ ИНФИЛЬ
В то время как этот метод лучше перебирать набор результатов и сохранять в файл строка за строкой, это не так хорошо, как использовать ….
туздЫшпр
туздЫшпр во многих отношениях превосходит SELECT INTO OUTFILE, создание CSV — лишь одна из многих возможностей, которые может выполнять эта команда.
Клиентская утилита mysqldump выполняет логическое резервное копирование, создавая набор
операторов SQL, которые могут быть выполнены для воспроизведения оригинала
определения объектов базы данных и данные таблиц. Это сбрасывает один или несколько MySQL
базы данных для резервного копирования или передачи на другой сервер SQL. Mysqldump
Команда также может генерировать выходные данные в CSV, другой текст с разделителями или XML
формат.
В идеале mysqldump должен вызываться из вашей оболочки. Можно использовать Exec в php, чтобы запустить его, но поскольку создание дампа может занять много времени в зависимости от объема данных, а сценарии php обычно запускаются только в течение 30 секунд, вам потребуется запустить его как фоновый процесс.
mysqldump не без это справедливая доля проблем.
Он не предназначен для быстрого или масштабируемого решения для резервного копирования
значительные объемы данных. С большими размерами данных, даже если резервная копия
шаг занимает разумное время, восстановление данных может быть очень медленным
потому что воспроизведение операторов SQL требует дискового ввода-вывода для вставки,
создание индекса и так далее.
Классический пример увидеть этот вопрос: Сбой сервера при резервном копировании MySQL с использованием python где один mysqldump, кажется, запускается до того, как предыдущий закончил и сделал сайт полностью не отвечающим.
MySQL репликация
Репликация включает данные с одного сервера базы данных MySQL (мастер)
быть скопированным на один или несколько серверов баз данных MySQL (ведомых).
Репликация является асинхронной по умолчанию; рабы не должны быть
постоянно подключен для получения обновлений от мастера. В зависимости от
В конфигурации можно реплицировать все базы данных, выбранные
базы данных, или даже выбранные таблицы в базе данных.
таким образом копирование действует не так, как SELECT INTO OUTFILE или же msyqldump Идеально хранить данные в локальной копии почти в актуальном состоянии (сказал бы идеально синхронно, но есть нечто, называемое ведомой задержкой) С другой стороны, если вы используете запланированное задание для запуска mysqldump один раз каждые 24 часа. Представьте, что может произойти, если сервер рухнет через 23 часа?
Каждый раз, когда вы запускаете mysqldump, вы генерируете большой объем данных, продолжайте делать это регулярно, и вы обнаружите, что ваш жесткий диск заполнен или ваши счета за хранение файлов находятся на высоте. При репликации на сервер передаются только изменения (с использованием так называемого binlog)
XtraBackup
Альтернативой репликации является использование Percona XtraBackup.
Percona XtraBackup — это утилита горячего резервного копирования с открытым исходным кодом для MySQL.
серверы, которые не блокируют вашу базу данных во время резервного копирования.
Хотя по Percona, он совместим с Mysql и Mariadb. Он имеет возможность делать инкрементные резервные копии, отсутствие которых является самым большим ограничением mysqldump.
Другие решения
Я предлагаю получить резервную копию базы данных с помощью утилиты командной строки, используя файл сценария вместо сценария PHP.
Создайте файл my.ini для конфигурации магазина
сделать файл my.ini для имени пользователя и пароля по умолчанию в корневой директории пользователя. поэтому скрипт будет принимать имя пользователя, пароль и имя хоста из этого файла
[client]
user = <db_user_name>
password = <db_password>
host = <db_host>
Создайте sh файл с именем backup.sh
#!/bin/sh
#
# script for get backup everyday
#change directory to your backup directory
cd /path_of_your_directory
#get backup of database of applications
mysqldump <your_database_name> tmp_db.sql;
#compress it in zip file
zip app_database-$(date +%Y-%m-%d).sql.zip tmp_db.sql;
#remove sql file
rm -rf tmp_db.sql;
Разрешить исполняемому файлу sh.file
chmod +x backup.sh
Установить Cronjob
sh /<script_path>/backup.sh >/dev/null 2>&1
Это все
Удачи